Schema files implementing inheritance proposal (datasore identity)

Michael Watzek Thu, 28 Jul 2005 06:04:52 -0700

Hi,

attached you find the schema files for datastore identity. Mainly, theyequal the ones for application identity except for the identity columns.


Please comment!

Regards,
Michael
--
-------------------------------------------------------------------
Michael Watzek                  [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED]        Buelowstr. 66
Tel.:  ++49/30/235 520 36       10783 Berlin - Germany
Fax.:  ++49/30/217 520 12       http://www.spree.de/
-------------------------------------------------------------------

-- SchemaType: datastore identity

-- Inheritance mapping: 
-- Person, Employee, and Insurance have inheritance strategy "new-table". 
-- PartTimeEmployee, FullTimeEmployee, MedicalInsurance, and DentalInsurance
-- have inheritance strategy "super-class".
-- See tables "persons", "employees", and "insuranceplans".

connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser';

CREATE SCHEMA datastoreidentity4;
SET SCHEMA datastoreidentity4;

-------------------------
-- company
-------------------------

ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK;
DROP TABLE insuranceplans;
DROP TABLE project_reviewer;
DROP TABLE project_member;
DROP TABLE employee_phoneno_type;
DROP TABLE employees;
DROP TABLE persons;
DROP TABLE projects;
DROP TABLE departments;
DROP TABLE companies;

CREATE TABLE companies (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    FOUNDEDDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE departments (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    EMP_OF_THE_MONTH INTEGER,
    COMPANYID INTEGER REFERENCES companies,
    CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE persons (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PERSONID INTEGER UNIQUE NOT NULL,
    FIRSTNAME VARCHAR(32) NOT NULL,
    LASTNAME VARCHAR(32) NOT NULL,
    MIDDLENAME VARCHAR(32),
    BIRTHDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE employees (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    HIREDATE VARCHAR(32),
    WEEKLYHOURS FLOAT,
    DEPARTMENT INTEGER REFERENCES departments,
    FUNDINGDEPT INTEGER REFERENCES departments,
    MANAGER INTEGER REFERENCES persons,
    MENTOR INTEGER REFERENCES persons,
    HRADVISOR INTEGER REFERENCES persons,
    WAGE FLOAT,
    SALARY FLOAT,
    DISCRIMINATOR varchar(64) NOT NULL,
    CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons 
(DATASTORE_IDENTITY)
);

CREATE TABLE insuranceplans (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    INSID INTEGER,
    CARRIER VARCHAR(64) NOT NULL,
    EMPLOYEE INTEGER REFERENCES persons,
    PLANTYPE VARCHAR(8),
    LIFETIME_ORTHO_BENEFIT DECIMAL,
    DISCRIMINATOR varchar(64) NOT NULL,
    CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE projects (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PROJID INTEGER UNIQUE NOT NULL,
    NAME VARCHAR(32) NOT NULL,
    BUDGET DECIMAL(11,2) NOT NULL,
    CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE project_reviewer (
    PROJID INTEGER NOT NULL,
    REVIEWER INTEGER NOT NULL
);

CREATE TABLE project_member (
    PROJID INTEGER REFERENCES projects NOT NULL,
    MEMBER INTEGER REFERENCES persons NOT NULL
);

CREATE TABLE employee_phoneno_type (
    EMPID INTEGER REFERENCES persons NOT NULL,
    PHONENO VARCHAR(16) NOT NULL,
    TYPE VARCHAR(16) NOT NULL
);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
        (PROJID) REFERENCES projects(PROJID);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_REV_FK FOREIGN KEY
        (REVIEWER) REFERENCES persons(PERSONID);

ALTER TABLE departments 
    ADD CONSTRAINT EMP_MO_FK FOREIGN KEY
        (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY);

disconnect;

-- SchemaType: datastore identity

-- Inheritance mapping: 
-- Separate table for each class in the inheritance hierarchy.
-- No tables for abstract classes.
-- Each table contains columns for all fields.
-- Managers, mentors, hradvisors, and employees of the month 
-- are fulltime employees.
-- Only fulltime employees can have insurances, can be project members,
-- and can be project reviewers.
-- Separate phone number type tables for persons, fulltime employees,
-- and parttime employees.
-- See tables "persons", "parttimeemployees", 
-- "fulltimeemployees", "medicalinsurance", "dentalinsurance",
-- "fulltime_employee_phoneno_type", and "parttime_employee_phoneno_type".

connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser';

CREATE SCHEMA datastoreidentity2;
SET SCHEMA datastoreidentity2;

-------------------------
-- company
-------------------------

ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK;
DROP TABLE dentalinsurance;
DROP TABLE medicalinsurance;
DROP TABLE project_reviewer;
DROP TABLE project_member;
DROP TABLE employee_phoneno_type;
DROP TABLE fulltime_employee_phoneno_type;
DROP TABLE parttime_employee_phoneno_type;
DROP TABLE fulltimeemployees;
DROP TABLE parttimeemployees;
DROP TABLE persons;
DROP TABLE projects;
DROP TABLE departments;
DROP TABLE companies;

CREATE TABLE companies (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    FOUNDEDDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE departments (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    EMP_OF_THE_MONTH INTEGER,
    COMPANYID INTEGER REFERENCES companies,
    CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE persons (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PERSONID INTEGER UNIQUE NOT NULL,
    FIRSTNAME VARCHAR(32) NOT NULL,
    LASTNAME VARCHAR(32) NOT NULL,
    MIDDLENAME VARCHAR(32),
    BIRTHDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE parttimeemployees (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PERSONID INTEGER UNIQUE NOT NULL,
    FIRSTNAME VARCHAR(32) NOT NULL,
    LASTNAME VARCHAR(32) NOT NULL,
    MIDDLENAME VARCHAR(32),
    BIRTHDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    HIREDATE VARCHAR(32),
    WEEKLYHOURS FLOAT,
    DEPARTMENT INTEGER REFERENCES departments,
    FUNDINGDEPT INTEGER REFERENCES departments,
    MANAGER INTEGER REFERENCES fulltimeemployees,
    MENTOR INTEGER REFERENCES fulltimeemployees,
    HRADVISOR INTEGER REFERENCES fulltimeemployees,
    WAGE FLOAT,
    CONSTRAINT PTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE fulltimeemployees (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PERSONID INTEGER UNIQUE NOT NULL,
    FIRSTNAME VARCHAR(32) NOT NULL,
    LASTNAME VARCHAR(32) NOT NULL,
    MIDDLENAME VARCHAR(32),
    BIRTHDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    HIREDATE VARCHAR(32),
    WEEKLYHOURS FLOAT,
    DEPARTMENT INTEGER REFERENCES departments,
    FUNDINGDEPT INTEGER REFERENCES departments,
    MANAGER INTEGER REFERENCES fulltimeemployees,
    MENTOR INTEGER REFERENCES fulltimeemployees,
    HRADVISOR INTEGER REFERENCES fulltimeemployees,
    SALARY FLOAT,
    CONSTRAINT FTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE medicalinsurance (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    INSID INTEGER,
    CARRIER VARCHAR(64) NOT NULL,
    EMPLOYEE INTEGER REFERENCES fulltimeemployees,
    PLANTYPE VARCHAR(8),
    CONSTRAINT MEDINS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE dentalinsurance (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    INSID INTEGER,
    CARRIER VARCHAR(64) NOT NULL,
    EMPLOYEE INTEGER REFERENCES fulltimeemployees,
    LIFETIME_ORTHO_BENEFIT DECIMAL,
    CONSTRAINT DENTINS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE projects (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PROJID INTEGER UNIQUE NOT NULL,
    NAME VARCHAR(32) NOT NULL,
    BUDGET DECIMAL(11,2) NOT NULL,
    CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE project_reviewer (
    PROJID INTEGER NOT NULL,
    REVIEWER INTEGER NOT NULL
);

CREATE TABLE project_member (
    PROJID INTEGER REFERENCES projects NOT NULL,
    MEMBER INTEGER REFERENCES fulltimeemployees NOT NULL
);

CREATE TABLE employee_phoneno_type (
    EMPID INTEGER REFERENCES persons NOT NULL,
    PHONENO VARCHAR(16) NOT NULL,
    TYPE VARCHAR(16) NOT NULL
);

CREATE TABLE fulltime_employee_phoneno_type (
    EMPID INTEGER REFERENCES fulltimeemployees NOT NULL,
    PHONENO VARCHAR(16) NOT NULL,
    TYPE VARCHAR(16) NOT NULL
);

CREATE TABLE parttime_employee_phoneno_type (
    EMPID INTEGER REFERENCES parttimeemployees NOT NULL,
    PHONENO VARCHAR(16) NOT NULL,
    TYPE VARCHAR(16) NOT NULL
);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
        (PROJID) REFERENCES projects(PROJID);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_REV_FK FOREIGN KEY
        (REVIEWER) REFERENCES fulltimeemployees(PERSONID);

ALTER TABLE departments 
    ADD CONSTRAINT EMP_MO_FK FOREIGN KEY
        (EMP_OF_THE_MONTH) REFERENCES fulltimeemployees(DATASTORE_IDENTITY);

disconnect;

-- SchemaType: datastore identity

-- Inheritance mapping: 
-- Person has inheritance strategy "new-table.
-- Employee has inheritance strategy "subclass-table".
-- PartTimeEmployee and FullTimeEmployee have inheritance strategy "new-table".
-- Insurance has inheritance strategy "subclass-table".
-- MedicalInsurance and DentalInsurance have inheritance strategy "new-table".
-- See tables "persons", "parttimeemployees", "fulltimeemployees",
-- "medicalinsurance" and "dentalinsurance".

connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser';

CREATE SCHEMA datastoreidentity3;
SET SCHEMA datastoreidentity3;

-------------------------
-- company
-------------------------

ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK;
DROP TABLE dentalinsurance;
DROP TABLE medicalinsurance;
DROP TABLE project_reviewer;
DROP TABLE project_member;
DROP TABLE employee_phoneno_type;
DROP TABLE fulltimeemployees;
DROP TABLE parttimeemployees;
DROP TABLE persons;
DROP TABLE projects;
DROP TABLE departments;
DROP TABLE companies;

CREATE TABLE companies (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    FOUNDEDDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE departments (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    EMP_OF_THE_MONTH INTEGER,
    COMPANYID INTEGER REFERENCES companies,
    CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE persons (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PERSONID INTEGER UNIQUE NOT NULL,
    FIRSTNAME VARCHAR(32) NOT NULL,
    LASTNAME VARCHAR(32) NOT NULL,
    MIDDLENAME VARCHAR(32),
    BIRTHDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE parttimeemployees (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    HIREDATE VARCHAR(32),
    WEEKLYHOURS FLOAT,
    DEPARTMENT INTEGER REFERENCES departments,
    FUNDINGDEPT INTEGER REFERENCES departments,
    MANAGER INTEGER REFERENCES persons,
    MENTOR INTEGER REFERENCES persons,
    HRADVISOR INTEGER REFERENCES persons,
    WAGE FLOAT,
    CONSTRAINT PTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT PTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons 
(DATASTORE_IDENTITY)
);

CREATE TABLE fulltimeemployees (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    HIREDATE VARCHAR(32),
    WEEKLYHOURS FLOAT,
    DEPARTMENT INTEGER REFERENCES departments,
    FUNDINGDEPT INTEGER REFERENCES departments,
    MANAGER INTEGER REFERENCES persons,
    MENTOR INTEGER REFERENCES persons,
    HRADVISOR INTEGER REFERENCES persons,
    SALARY FLOAT,
    CONSTRAINT FTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT FTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons 
(DATASTORE_IDENTITY)
);

CREATE TABLE medicalinsurance (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    INSID INTEGER,
    CARRIER VARCHAR(64) NOT NULL,
    EMPLOYEE INTEGER REFERENCES persons,
    PLANTYPE VARCHAR(8),
    CONSTRAINT MEDINS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE dentalinsurance (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    INSID INTEGER,
    CARRIER VARCHAR(64) NOT NULL,
    EMPLOYEE INTEGER REFERENCES persons,
    LIFETIME_ORTHO_BENEFIT DECIMAL,
    CONSTRAINT DENTINS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE projects (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PROJID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    BUDGET DECIMAL(11,2) NOT NULL,
    CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE project_reviewer (
    PROJID INTEGER NOT NULL,
    REVIEWER INTEGER NOT NULL
);

CREATE TABLE project_member (
    PROJID INTEGER REFERENCES projects NOT NULL,
    MEMBER INTEGER REFERENCES persons NOT NULL
);

CREATE TABLE employee_phoneno_type (
    EMPID INTEGER REFERENCES persons NOT NULL,
    PHONENO VARCHAR(16) NOT NULL,
    TYPE VARCHAR(16) NOT NULL
);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
        (PROJID) REFERENCES projects(PROJID);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_REV_FK FOREIGN KEY
        (REVIEWER) REFERENCES persons(PERSONID);

ALTER TABLE departments 
    ADD CONSTRAINT EMP_MO_FK FOREIGN KEY
        (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY);

disconnect;

-- SchemaType: datastore identity

-- Inheritance mapping: 
-- Separate table for each class in the inheritance hierarchy.
-- Each table contains columns for the declared fields.
-- See tables "persons", "employees", "parttimeemployees", 
-- "fulltimeemployees", "insuranceplans", "medicalinsurance", and 
"dentalinsurance".

connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser';

CREATE SCHEMA datastoreidentity1;
SET SCHEMA datastoreidentity1;

-------------------------
-- company
-------------------------

ALTER TABLE departments DROP CONSTRAINT EMP_MO_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_PROJ_FK;
ALTER TABLE project_reviewer DROP CONSTRAINT PR_REV_FK;
DROP TABLE dentalinsurance;
DROP TABLE medicalinsurance;
DROP TABLE insuranceplans;
DROP TABLE project_reviewer;
DROP TABLE project_member;
DROP TABLE employee_phoneno_type;
DROP TABLE fulltimeemployees;
DROP TABLE parttimeemployees;
DROP TABLE employees;
DROP TABLE persons;
DROP TABLE projects;
DROP TABLE departments;
DROP TABLE companies;

CREATE TABLE companies (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    FOUNDEDDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT COMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE departments (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ID INTEGER,
    NAME VARCHAR(32) NOT NULL,
    EMP_OF_THE_MONTH INTEGER,
    COMPANYID INTEGER REFERENCES companies,
    CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE persons (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PERSONID INTEGER UNIQUE NOT NULL,
    FIRSTNAME VARCHAR(32) NOT NULL,
    LASTNAME VARCHAR(32) NOT NULL,
    MIDDLENAME VARCHAR(32),
    BIRTHDATE VARCHAR(32) NOT NULL,
    ADDRID INTEGER,
    STREET VARCHAR(64),
    CITY VARCHAR(64),
    STATE CHAR(2),
    ZIPCODE CHAR(5),
    COUNTRY VARCHAR(64),
    CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE employees (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    HIREDATE VARCHAR(32),
    WEEKLYHOURS FLOAT,
    DEPARTMENT INTEGER REFERENCES departments,
    FUNDINGDEPT INTEGER REFERENCES departments,
    MANAGER INTEGER REFERENCES persons,
    MENTOR INTEGER REFERENCES persons,
    HRADVISOR INTEGER REFERENCES persons,
    CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons 
(DATASTORE_IDENTITY)
);

CREATE TABLE parttimeemployees (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    WAGE FLOAT,
    CONSTRAINT PTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT PTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons 
(DATASTORE_IDENTITY)
);

CREATE TABLE fulltimeemployees (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    SALARY FLOAT,
    CONSTRAINT FTEMPS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT FTEMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons 
(DATASTORE_IDENTITY)
);

CREATE TABLE insuranceplans (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    INSID INTEGER,
    CARRIER VARCHAR(64) NOT NULL,
    EMPLOYEE INTEGER REFERENCES persons,
    CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE medicalinsurance (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    PLANTYPE VARCHAR(8),
    CONSTRAINT MEDINS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT MEDINS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES 
insuranceplans (DATASTORE_IDENTITY)
);

CREATE TABLE dentalinsurance (
    DATASTORE_IDENTITY INTEGER NOT NULL,
    LIFETIME_ORTHO_BENEFIT DECIMAL,
    CONSTRAINT DENTINS_PK PRIMARY KEY (DATASTORE_IDENTITY),
    CONSTRAINT DENTINS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES 
insuranceplans (DATASTORE_IDENTITY)
);

CREATE TABLE projects (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PROJID INTEGER UNIQUE NOT NULL,
    NAME VARCHAR(32) NOT NULL,
    BUDGET DECIMAL(11,2) NOT NULL,
    CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);

CREATE TABLE project_reviewer (
    PROJID INTEGER NOT NULL,
    REVIEWER INTEGER NOT NULL
);

CREATE TABLE project_member (
    PROJID INTEGER REFERENCES projects NOT NULL,
    MEMBER INTEGER REFERENCES persons NOT NULL
);

CREATE TABLE employee_phoneno_type (
    EMPID INTEGER REFERENCES persons NOT NULL,
    PHONENO VARCHAR(16) NOT NULL,
    TYPE VARCHAR(16) NOT NULL
);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
        (PROJID) REFERENCES projects(PROJID);

ALTER TABLE project_reviewer 
    ADD CONSTRAINT PR_REV_FK FOREIGN KEY
        (REVIEWER) REFERENCES persons(PERSONID);

ALTER TABLE departments 
    ADD CONSTRAINT EMP_MO_FK FOREIGN KEY
        (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY);

disconnect;

Schema files implementing inheritance proposal (datasore identity)

Reply via email to