· 7 years ago · Oct 29, 2018, 12:12 PM
1/*
2 * this version of the employee table definitions (p 91 of Elmasri-Navathe 6th ed)
3 * uses the ALTER TABLE option so that foreign key declarations do not precede
4 * the relevant table definition
5 *
6 * Also, tabs were eliminated for easier copy/paste
7 *
8 * if things get wedged, foreign-key constraints can be DROPPED:
9 * alter table employee DROP foreign key dno;
10 *
11 */
12
13DROP SCHEMA IF EXISTS company;
14CREATE SCHEMA company COLLATE = utf8_general_ci;
15USE company;
16
17create table employee (
18 fname varchar(15) not null,
19 minit char,
20 lname varchar(15) not null,
21 ssn char(9) not null,
22 bdate DATE,
23 address varchar(30),
24 sex char,
25 salary decimal(10,2),
26 super_ssn char(9),
27 dno int not null,
28 primary key (ssn)
29) ;
30
31create table department (
32 dname varchar(15) not null,
33 dnumber int not null,
34 mgr_ssn char(9) not null,
35 mgr_start date,
36 primary key (dnumber),
37 unique (dname),
38 foreign key (mgr_ssn) references employee(ssn)
39) ;
40
41
42create table dept_locations (
43 dnumber int not null,
44 dlocation varchar(15) not null,
45 primary key (dnumber, dlocation),
46 foreign key (dnumber) references department(dnumber)
47) ;
48
49create table project (
50 pname varchar(15) not null,
51 pnumber int not null,
52 plocation varchar(15),
53 dnum int not null,
54 primary key (pnumber),
55 unique (pname),
56 foreign key (dnum) references department(dnumber)
57) ;
58
59create table works_on (
60 essn char(9) not null,
61 pno int not null,
62 hours decimal(3,1) ,
63 primary key (essn, pno),
64 foreign key (essn) references employee(ssn),
65 foreign key (pno) references project(pnumber)
66) ;
67
68create table dependent (
69 essn char(9) not null,
70 dependent_name varchar(15) not null,
71 sex char,
72 bdate date,
73 relationship varchar(8),
74 primary key (essn, dependent_name),
75 foreign key (essn) references employee (ssn)
76) ;
77
78
79/* set foreign_key_checks=0; */
80
81/* for INSERT INTO employee, note that we insert James Borg *first*,
82 * because he is the only employee with NULL super_ssn, and otherwise
83 * we violate the super_ssn foreign key constraint.
84 */
85
86INSERT INTO employee
87(fname, minit, lname, ssn, bdate, address, sex, salary, super_ssn, dno)
88values
89('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Stone, Houston TX', 'M', 55000, NULL, 1),
90('John', 'B', 'Smith', '123456789', '1965-01-09', '731 Fondren, Houston TX', 'M', 30000, '333445555', 5),
91('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss, Houston TX', 'M', 40000, '888665555', 5),
92('Alicia', 'J', 'Zelaya', '999887777', '1968-01-19', '3321 Castle, Spring TX', 'F', 25000, '987654321', 4),
93('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry, Bellaire TX', 'F', 43000, '888665555', 4),
94('Ramesh', 'K', 'Narayan', '666884444', '1962-09-15', '975 Fire Oak, Humble TX', 'M', 38000, '333445555', 5),
95('Joyce', 'A', 'English', '453453453', '1972-07-31', '5631 Rice, Houston TX', 'F', 25000, '333445555', 5),
96('Ahmad', 'V', 'Jabbar', '987987987', '1969-03-29', '980 Dallas, Houston TX', 'M', 25000, '987654321', 4);
97
98INSERT INTO department
99(dname, dnumber, mgr_ssn, mgr_start)
100values
101('Research', 5, '333445555', '1988-05-22'),
102('Administration', 4, '987654321', '1995-01-01'),
103('Headquarters', 1, '888665555', '1981-06-19');
104
105INSERT INTO dependent
106(essn, dependent_name, sex, bdate, relationship)
107values
108('333445555', 'Alice', 'F', '1986-04-05', 'daughter'),
109('333445555', 'Theodore', 'M', '1983-10-25', 'son'),
110('333445555', 'Joy', 'F', '1958-05-03', 'spouse'),
111('987654321', 'Abner', 'M', '1942-02-28', 'spouse'),
112('123456789', 'Michael', 'M', '1988-01-04', 'son'),
113('123456789', 'Alice', 'F', '1988-12-30', 'daughter'),
114('123456789', 'Elizabeth', 'F', '1967-05-05', 'spouse');
115
116INSERT INTO dept_locations
117(dnumber, dlocation)
118values
119(1, 'Houston'),
120(4,'Stafford'),
121(5, 'Bellaire'),
122(5, 'Sugarland'),
123(5, 'Houston');
124
125INSERT INTO project
126(pname, pnumber, plocation, dnum)
127values
128('ProductX', 1, 'Bellaire', 5),
129('ProductY', 2, 'Sugarland', 5),
130('ProductZ', 3, 'Houston', 5),
131('Computerization', 10, 'Stafford', 4),
132('Reorganization', 20, 'Houston', 1),
133('Newbenefits', 30, 'Stafford', 4);
134
135INSERT INTO works_on
136(essn, pno, hours)
137values
138('123456789', 1, 32.5),
139('123456789', 2, 7.5),
140('666884444', 3, 40.0),
141('453453453', 1, 20.0),
142('453453453', 2, 20.0),
143('333445555', 2, 10.0),
144('333445555', 3, 10.0),
145('333445555', 10, 10.0),
146('333445555', 20, 10.0),
147('999887777', 30, 30.0),
148('999887777', 10, 10.0),
149('987987987', 10, 35.0),
150('987987987', 30, 5.0),
151('987654321', 30, 20.0),
152('987654321', 20, 15.0),
153('888665555', 20, NULL);
154
155
156/* set foreign_key_checks=1; */
157
158alter table employee ADD foreign key (super_ssn) references employee(ssn);
159alter table employee ADD foreign key (dno) references department(dnumber);