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

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

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

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

alter table dept_locations add primary key (dnumber, dlocation);

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

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

/* WORKS_ON */
create table works_on (essn number references employee (ssn), pno number
references project (pnumber), hours number);

alter table works_on add primary key (essn, pno);

/* DEPENDENT */
create table dependent (essn number references employee (ssn),
dependent_name char(10), sex char, bdate date, relationship char(10));

alter table dependent add primary key (essn, dependent_name);
