08-09-05 10:45 PM
Hi,
I discussed the inheritance proposal with Michael today and we came up
with a couple of questions I added below.
The following table describes the inheritance strategy setting for each
class for the 5 inheritance mappings. I abbreviated the inheritance
strategies: new -> new-table, super -> superclass-table, sub->
subclass-table.
Class Mapping0 Mapping1 Mapping2 Mapping3 Mapping4
-----------------------------------------------------------------
Person new new new new new
Employee super new ? sub new
FullTimeEmployee super new new new super
PartTimeEmployee super new new new super
Insurance new new ? sub new
DentalInsurance super new new new super
MedicalInsurance super new new new super
Questions:
- What is a reasonable inheritance strategy for the abstract classes
Employee and Insurance in Mapping2? The mapping does not define a table
for the abstract classes, so new-table does not make sense. I see two
options: either "subclass-table" or the abstract class does not define
an inheritance strategy at all.
- Does JDO require a inheritance element for classes in an inheritance
hierarchy? If not, is there a default? The same questions apply to the
strategy attribute inside of the inheritance element.
- Can I figure out whether inherited fields are mapped to the superclass
or subclass table by just looking at the inheritance strategy? I think
the answer is no, correct? So is it the existence of the nested <join>
elements that determines only declared fields are mapped? Mapping1 and
Mapping2 have this difference. Mapping1 maps only declared field where
Mapping2 uses tables for all the fields (including inherited fields) per
class.
Regards Michael
> Hi,
>
> in t-conference, Jul 8, Craig proposed 5 inheritance mappings that TCK
> should implement. Below, you find a description of each mapping.
>
> All mappings are based on the company model. The company model defines
> two inheritance hierarchies:
>
> Person Insurance
> | / \
> Employee DentalInsurance MedicalInsurance
> / \
> FullTimeEmployee PartTimeEmployee
>
> The proposal is to define a separate schema and separate ORM metadata
> for each mapping. Each mapping below specifies the ORM inheritance
> strategy for each class. Note, that the first mapping (Mapping 0) is
> already implemented by TCK:
>
> - Mapping 0 (implemented by TCK):
> The same table for all classes in the inheritance hierarchy. That table
> specifies a discriminator column, identiying the actual type of each row.
> Person and Insurance have inheritance strategy "new-table". Tables of
> these classes have dicriminator columns.
> All other classes have inheritance strategy "superclass-table".
>
> - Mapping 1:
> Separate table for each class in the inheritance hierarchy. Each table
> contains columns for the declared fields. There are no discriminator
> columns. Instead, there are FKs from subclass tables to superclass
> tables. The ORM metadata specifies nested <join> elements for all
> subclasses. All classes have inheritance strategy "new-table".
>
> - Mapping 2:
> Separate table for each class in the inheritance hierarchy. No tables
> for abstract classes Employee and Insurance. Each table contains columns
> for all fields including fields of superclasses.
> There are no FKs from subclass tables to superclass tables. The ORM
> metadata does not specifies nested <join> elements. There are no
> discriminator columns.
> All classes have inheritance strategy "new-table".
> Some additional constraints: 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.
>
> - Mapping 3:
> 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"
.
> The ORM metadata specifies nested <join> elements for classes
> PartTimeEmployee and FullTimeEmployee. Tables of these classes have FKs
> to table of class Person. There are no discriminator columns.
>
> - Mapping 4:
> Person, Employee, and Insurance have inheritance strategy "new-table".
> PartTimeEmployee, FullTimeEmployee, MedicalInsurance, and
> DentalInsurance have inheritance strategy "superclass-table".
> The ORM metadata specifies a nested <join> element for class Employee.
> The table of class Employee has a FK to the table of class Person.
> The tables of classes Employee and Insurance have discriminator columns.
>
> Attached you find schema files for each mapping. All attached schema
> files implement application identity. Note, that I did not attach the
> schema for "Mapping 0", as you find that in your workspace.
>
> Please review and comment!
>
> Regards,
> Michael
>
>
> ------------------------------------------------------------------------
>
> -- SchemaType: application identity
>
> -- Inheritance mapping:
> -- Person, Employee, and Insurance have inheritance strategy "new-table".
> -- PartTimeEmployee, FullTimeEmployee, MedicalInsurance, and DentalInsuran
ce
> -- have inheritance strategy "superclass-table".
> -- See tables "persons", "employees", and "insuranceplans".
>
> connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser
';
>
> CREATE SCHEMA applicationidentity4;
> SET SCHEMA applicationidentity4;
>
> -------------------------
> -- 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 (
> ID INTEGER NOT NULL,
> 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 (ID)
> );
>
> CREATE TABLE departments (
> ID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> EMP_OF_THE_MONTH INTEGER,
> COMPANYID INTEGER REFERENCES companies,
> CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
> );
>
> CREATE TABLE persons (
> PERSONID INTEGER 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 (PERSONID)
> );
>
> CREATE TABLE employees (
> PERSONID 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 (PERSONID),
> CONSTRAINT EMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID
)
> );
>
> CREATE TABLE insuranceplans (
> INSID INTEGER NOT NULL,
> CARRIER VARCHAR(64) NOT NULL,
> EMPLOYEE INTEGER REFERENCES persons,
> PLANTYPE VARCHAR(8),
> LIFETIME_ORTHO_BENEFIT DECIMAL(22,3),
> DISCRIMINATOR varchar(64) NOT NULL,
> CONSTRAINT INS_PK PRIMARY KEY (INSID)
> );
>
> CREATE TABLE projects (
> PROJID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> BUDGET DECIMAL(11,2) NOT NULL,
> CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
> );
>
> 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(PERSONID);
>
> disconnect;
>
>
> ------------------------------------------------------------------------
>
> -- SchemaType: application 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 applicationidentity2;
> SET SCHEMA applicationidentity2;
>
> -------------------------
> -- 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 parttimeemployees;
> DROP TABLE fulltimeemployees;
> DROP TABLE persons;
> DROP TABLE projects;
> DROP TABLE departments;
> DROP TABLE companies;
>
> CREATE TABLE companies (
> ID INTEGER NOT NULL,
> 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 (ID)
> );
>
> CREATE TABLE departments (
> ID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> EMP_OF_THE_MONTH INTEGER,
> COMPANYID INTEGER REFERENCES companies,
> CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
> );
>
> CREATE TABLE persons (
> PERSONID INTEGER 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 (PERSONID)
> );
>
> CREATE TABLE fulltimeemployees (
> PERSONID INTEGER 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 (PERSONID)
> );
>
> CREATE TABLE parttimeemployees (
> PERSONID INTEGER 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 (PERSONID)
> );
>
> CREATE TABLE medicalinsurance (
> INSID INTEGER NOT NULL,
> CARRIER VARCHAR(64) NOT NULL,
> EMPLOYEE INTEGER REFERENCES fulltimeemployees,
> PLANTYPE VARCHAR(8),
> CONSTRAINT MEDINS_PK PRIMARY KEY (INSID)
> );
>
> CREATE TABLE dentalinsurance (
> INSID INTEGER NOT NULL,
> CARRIER VARCHAR(64) NOT NULL,
> EMPLOYEE INTEGER REFERENCES fulltimeemployees,
> LIFETIME_ORTHO_BENEFIT DECIMAL(22,3),
> CONSTRAINT DENTINS_PK PRIMARY KEY (INSID)
> );
>
> CREATE TABLE projects (
> PROJID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> BUDGET DECIMAL(11,2) NOT NULL,
> CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
> );
>
> 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(PERSONID);
>
> disconnect;
>
>
> ------------------------------------------------------------------------
>
> -- SchemaType: application identity
>
> -- Inheritance mapping:
> -- Person has inheritance strategy "new-table".
> -- Employee has inheritance strategy "subclass-table".
> -- PartTimeEmployee and FullTimeEmployee have inheritance strategy "new-ta
ble".
> -- Insurance has inheritance strategy "subclass-table".
> -- MedicalInsurance and DentalInsurance have inheritance strategy "new-tab
le".
> -- See tables "persons", "parttimeemployees", "fulltimeemployees",
> -- "medicalinsurance" and "dentalinsurance".
>
> connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser
';
>
> CREATE SCHEMA applicationidentity3;
> SET SCHEMA applicationidentity3;
>
> -------------------------
> -- 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 (
> ID INTEGER NOT NULL,
> 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 (ID)
> );
>
> CREATE TABLE departments (
> ID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> EMP_OF_THE_MONTH INTEGER,
> COMPANYID INTEGER REFERENCES companies,
> CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
> );
>
> CREATE TABLE persons (
> PERSONID INTEGER 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 (PERSONID)
> );
>
> CREATE TABLE parttimeemployees (
> PERSONID 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 (PERSONID),
> CONSTRAINT PTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSON
ID)
> );
>
> CREATE TABLE fulltimeemployees (
> PERSONID 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 (PERSONID),
> CONSTRAINT FTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSON
ID)
> );
>
> CREATE TABLE medicalinsurance (
> INSID INTEGER NOT NULL,
> CARRIER VARCHAR(64) NOT NULL,
> EMPLOYEE INTEGER REFERENCES persons,
> PLANTYPE VARCHAR(8),
> CONSTRAINT MEDINS_PK PRIMARY KEY (INSID)
> );
>
> CREATE TABLE dentalinsurance (
> INSID INTEGER NOT NULL,
> CARRIER VARCHAR(64) NOT NULL,
> EMPLOYEE INTEGER REFERENCES persons,
> LIFETIME_ORTHO_BENEFIT DECIMAL(22,3),
> CONSTRAINT DENTINS_PK PRIMARY KEY (INSID)
> );
>
> CREATE TABLE projects (
> PROJID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> BUDGET DECIMAL(11,2) NOT NULL,
> CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
> );
>
> 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(PERSONID);
>
> disconnect;
>
>
> ------------------------------------------------------------------------
>
> -- SchemaType: application 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 "dentali
nsurance".
>
> connect 'jdbc:derby:jdotckdb;create=true' user 'tckuser' password 'tckuser
';
>
> CREATE SCHEMA applicationidentity1;
> SET SCHEMA applicationidentity1;
>
> -------------------------
> -- 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 (
> ID INTEGER NOT NULL,
> 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 (ID)
> );
>
> CREATE TABLE departments (
> ID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> EMP_OF_THE_MONTH INTEGER,
> COMPANYID INTEGER REFERENCES companies,
> CONSTRAINT DEPTS_PK PRIMARY KEY (ID)
> );
>
> CREATE TABLE persons (
> PERSONID INTEGER 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 (PERSONID)
> );
>
> CREATE TABLE employees (
> PERSONID 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 (PERSONID),
> CONSTRAINT EMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSONID
)
> );
>
> CREATE TABLE parttimeemployees (
> PERSONID INTEGER NOT NULL,
> WAGE FLOAT,
> CONSTRAINT PTEMPS_PK PRIMARY KEY (PERSONID),
> CONSTRAINT PTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSON
ID)
> );
>
> CREATE TABLE fulltimeemployees (
> PERSONID INTEGER NOT NULL,
> SALARY FLOAT,
> CONSTRAINT FTEMPS_PK PRIMARY KEY (PERSONID),
> CONSTRAINT FTEMPS_FK FOREIGN KEY (PERSONID) REFERENCES persons (PERSON
ID)
> );
>
> CREATE TABLE insuranceplans (
> INSID INTEGER NOT NULL,
> CARRIER VARCHAR(64) NOT NULL,
> EMPLOYEE INTEGER REFERENCES persons,
> CONSTRAINT INS_PK PRIMARY KEY (INSID)
> );
>
> CREATE TABLE medicalinsurance (
> INSID INTEGER NOT NULL,
> PLANTYPE VARCHAR(8),
> CONSTRAINT MEDINS_PK PRIMARY KEY (INSID),
> CONSTRAINT MEDINS_FK FOREIGN KEY (INSID) REFERENCES insuranceplans (IN
SID)
> );
>
> CREATE TABLE dentalinsurance (
> INSID INTEGER NOT NULL,
> LIFETIME_ORTHO_BENEFIT DECIMAL(22,3),
> CONSTRAINT DENTINS_PK PRIMARY KEY (INSID),
> CONSTRAINT DENTINS_FK FOREIGN KEY (INSID) REFERENCES insuranceplans (I
NSID)
> );
>
> CREATE TABLE projects (
> PROJID INTEGER NOT NULL,
> NAME VARCHAR(32) NOT NULL,
> BUDGET DECIMAL(11,2) NOT NULL,
> CONSTRAINT PROJS_PK PRIMARY KEY (PROJID)
> );
>
> 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(PERSONID);
>
> disconnect;
--
Michael Bouschen Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
[ Post a follow-up to this message ]
|
