create table department (
dname char(20),
dnumber number(1),  
mgrssn number(9),   
mgrstartdate date, 
primary key (dnumber), 
unique (dname));    


create table employee (
fname char(10),
lname char(20),
ssn number(9),  
bdate date,
address char(30),
sex char(1),
salary number(5),
superssn number(9),
dno number(1), 
primary key (ssn), 
foreign key (dno) references department(dnumber)); 


create table project (
pname char(20),
pnumber number(2),  
plocation char(20),
dnum number(1), 
primary key (pnumber), 
unique (pname), 
foreign key (dnum) references department(dnumber)); 


create table works_on (
essn number(9),  
pno number(2), 
hours number (5,1),  
primary key (essn, pno), 
foreign key (essn) references employee(ssn),
foreign key (pno) references project(pnumber)); 


create table dependent (
essn number(9),  
dependent_name char(10),
sex char(1),
bdate date,
relationship char(30),
primary key (essn, dependent_name),
foreign key (essn) references employee(ssn)); 


create table dept_locations (
dnumber number(1),
dlocation char (15),
primary key (dnumber, dlocation), 
foreign key (dnumber) references department(dnumber));


alter table department 
 add constraint depemp foreign key (mgrssn) references employee(ssn)
     initially deferred; 

alter table employee   
 add constraint empemp foreign key (superssn) references employee(ssn)
     initially deferred;