X66bMrpV

1DROP TABLE if exists department;
2CREATE TABLE department (
3  dname        varchar(25) not null,
4  dno          integer,
5  mgrssn       char(9) not null, 
6  mgrstartdate date,
7  primary key (dno),
8  unique (dname)
9);
10
11
12DROP TABLE if exists employee;
13CREATE TABLE employee (
14  fname    varchar(15) not null, 
15  minit    varchar(1),
16  lname    varchar(15) not null,
17  ssn      char(9),
18  bdate    date,
19  address  varchar(50),
20  gender      char,
21  salary   decimal(10,2),
22  superssn char(9),
23  dno      integer,
24  primary key (ssn)
25);
26
27
28DROP TABLE if exists project;
29CREATE TABLE project (
30  pname      varchar(25) not null,
31  pno        integer,
32  plocation  varchar(15),
33  dno        integer not null,
34  primary key (pno),
35  unique (pname)
36);
37
38
39DROP TABLE if exists dept_locations;
40CREATE TABLE dept_locations (
41  dno       integer,
42  dlocation varchar(15), 
43  primary key (dno,dlocation)
44);
45
46
47DROP TABLE if exists dependent;
48CREATE TABLE dependent (
49  essn           char(9),
50  depname varchar(15),
51  gender            char,
52  bdate          date,
53  relationship   varchar(8),
54  primary key (essn,depname)
55);
56
57
58DROP TABLE if exists works_on;
59CREATE TABLE works_on (
60  ssn   char(9),
61  pno    integer,
62  hours  decimal(4,1),
63  primary key (ssn,pno)
64);
65
66ALTER TABLE employee ADD CONSTRAINT fke FOREIGN KEY(dno) REFERENCES department(dno);	
67ALTER TABLE employee ADD CONSTRAINT fkessn FOREIGN KEY(superssn) REFERENCES employee(ssn);
68ALTER TABLE project ADD CONSTRAINT fkpno FOREIGN KEY(dno) REFERENCES department(dno);	
69ALTER TABLE dept_locations ADD CONSTRAINT fkdeptpk FOREIGN KEY(dno) REFERENCES department(dno);	
70ALTER TABLE dependent ADD CONSTRAINT fkdepefk FOREIGN KEY(essn) REFERENCES employee(ssn);	
71ALTER TABLE works_on ADD CONSTRAINT fkwonem FOREIGN KEY(ssn) REFERENCES employee(ssn);	
72ALTER TABLE works_on ADD CONSTRAINT fkwonpr FOREIGN KEY(pno) REFERENCES project(pno);