1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
CREATE DATABASE Company;
USE Company;
CREATE TABLE employee (
fname char(255),
minit char(255),
lname char(255),
ssn int(9) PRIMARY KEY,
bdate date,
address char(255),
sex bigint(255),
salary int(9),
super_ssn int(9),
dno int(9),
FOREIGN KEY (super_ssn) REFERENCES employee(ssn));
CREATE TABLE department (
dname char(255),
dnumber int(9) PRIMARY KEY,
mgr_ssn int(9),
mgr_start_date date,
FOREIGN KEY (mgr_ssn) REFERENCES employee(ssn));
ALTER TABLE employee ADD FOREIGN KEY (dno) REFERENCES department(dnumber);
CREATE TABLE dept_locations (
dnumber int(9),
dlocation char(255),
PRIMARY KEY (dnumber, dlocation),
FOREIGN KEY (dnumber) REFERENCES department(dnumber));
CREATE TABLE project (
pname char(255),
pnumber int(9) PRIMARY KEY,
plocation char(255),
dnum int(9),
FOREIGN KEY (dnum) REFERENCES department(dnumber));
CREATE TABLE works_on (
essn int(9),
pno int(9),
hours int(9),
PRIMARY KEY (essn, pno),
FOREIGN KEY (essn) REFERENCES employee(ssn),
FOREIGN KEY (pno) REFERENCES project(pnumber));
CREATE TABLE dependant (
essn int(9),
dependant_name char(255),
sex bigint(255),
bdate date,
relationship char(255),
PRIMARY KEY (essn, dependant_name),
FOREIGN KEY (essn) REFERENCES employee(ssn));
ALTER TABLE employee ADD partner_ssn int(9);
DROP DATABASE Company;
|
