/* EMPLOYEE; foreign key referencing department is added later */
create table employee (fname char(10), minit char, lname char(10),
ssn numeric primary key, bdate smalldatetime, address char(30), sex char, 
salary numeric, superssn numeric references employee (ssn) null, dno numeric null)

/* DEPARTMENT */
create table department (dname char(20), dnumber numeric primary key,
mgrssn numeric references employee (ssn), mgrstartdate smalldatetime)

/* Foreign key from EMPLOYEE to DEPARTMENT */
alter table employee add foreign key (dno) references department (dnumber)

/* DEPT_LOCATIONS */
create table dept_locations (dnumber numeric references department (dnumber),
dlocation char(15))

alter table dept_locations add primary key (dnumber, dlocation)

/* PROJECT */
create table project (pname char(20), pnumber numeric primary key,
plocation char(15), dnum numeric)

alter table project add foreign key (dnum, plocation) references
	dept_locations (dnumber, dlocation)

/* WORKS_ON */
create table works_on (essn numeric references employee (ssn), pno numeric
references project (pnumber), hours numeric(18,1))

alter table works_on add primary key (essn, pno)

/* DEPENDENT */
create table dependent (essn numeric references employee (ssn),
dependent_name char(10), sex char, bdate smalldatetime, relationship char(10))

alter table dependent add primary key (essn, dependent_name)
