· 8 years ago · Nov 20, 2017, 08:32 PM
1CREATE DATABASE IF NOT EXISTS CompanyDB;
2USE CompanyDB;
3
4drop table IF EXISTS EMPLOYEE;
5drop table IF EXISTS DEPARTMENT;
6drop table IF EXISTS DEPT_LOCATIONS;
7drop table IF EXISTS PROJECT;
8drop table IF EXISTS WORKS_ON;
9drop table IF EXISTS DEPENDENT;
10
11
12CREATE TABLE IF NOT EXISTS EMPLOYEE (
13 Fname VARCHAR(20) NOT NULL,
14 Minit CHAR,
15 Lname VARCHAR(20) NOT NULL,
16 Ssn CHAR(9) NOT NULL,
17 Bdate DATE,
18 Address VARCHAR(30),
19 Sex CHAR,
20 Salary DECIMAL(10, 2),
21 Super_ssn CHAR(9),
22 Dno INT,
23 PRIMARY KEY (Ssn),
24 FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)
25);
26
27CREATE TABLE IF NOT EXISTS DEPARTMENT (
28 Dname VARCHAR(15) NOT NULL,
29 Dnumber INT NOT NULL,
30 Mgr_ssn CHAR(9) NOT NULL,
31 Mgr_start_date DATE,
32 PRIMARY KEY(Dnumber),
33 UNIQUE(Dname),
34 FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
35);
36
37
38CREATE TABLE IF NOT EXISTS DEPT_LOCATIONS (
39 DNumber INT NOT NULL,
40 Dlocation VARCHAR(15) NOT NULL,
41 PRIMARY KEY(Dnumber, Dlocation),
42 FOREIGN KEY(Dnumber) REFERENCES DEPARTMENT(Dnumber)
43);
44
45CREATE TABLE IF NOT EXISTS PROJECT (
46 Pname VARCHAR(15) NOT NULL,
47 Pnumber INT NOT NULL,
48 Plocation VARCHAR(15),
49 Dnum INT NOT NULL,
50 PRIMARY KEY (Pnumber),
51 UNIQUE (Pname),
52 FOREIGN KEY(Dnum) REFERENCES DEPARTMENT(Dnumber)
53);
54
55
56CREATE TABLE IF NOT EXISTS WORKS_ON (
57 Essn CHAR(9) NOT NULL,
58 Pno INT NOT NULL,
59 Hours DECIMAL(3, 1) NOT NULL,
60 PRIMARY KEY(Essn, Pno),
61 FOREIGN KEY(Essn) REFERENCES EMPLOYEE(ssn),
62 FOREIGN KEY(Pno) REFERENCES PROJECT(Pnumber)
63);
64
65
66CREATE TABLE IF NOT EXISTS DEPENDENT (
67 Essn CHAR(9) NOT NULL,
68 DEPENDENT_name VARCHAR (15) NOT NULL,
69 Sex CHAR,
70 Bdate DATE,
71 Relationship VARCHAR(8),
72 PRIMARY KEY(Essn, DEPENDENT_name),
73 FOREIGN KEY(Essn) REFERENCES EMPLOYEE(Ssn)
74);
75
76
77
78INSERT INTO DEPARTMENT VALUES('Research','5','333445555','1988-05-22');
79INSERT INTO DEPARTMENT VALUES('Administration','4','987654321','1995-01-01');
80INSERT INTO DEPARTMENT VALUES('Headquarters','1','888665555','1981-06-19');
81
82INSERT INTO EMPLOYEE
83VALUES('James','E','Borg','888665555','1937-11-10','450 Stone, Houston, TX','M','55000','','1');
84INSERT INTO EMPLOYEE
85VALUES('Franklin','T','Wong','333445555','1955-12-08','638 Voss, Houston, TX','M','40000','888665555','5');
86INSERT INTO EMPLOYEE
87VALUES('Jennifer','S','Wallace','987654321','1941-06-20','291 Berry, Bellaire, TX','F','43000','888665555','4');
88INSERT INTO EMPLOYEE
89VALUES('John','B','Smith','123456789','1965-01-09','731 Fondren, Houston, TX','M','30000','333445555','5 ');
90INSERT INTO EMPLOYEE
91VALUES('Ramesh','K','Narayan','666884444','1962-09-15','975 Fire Oak, Humble, TX','M','38000','333445555','5');
92INSERT INTO EMPLOYEE
93VALUES('Joyce','A','English','453453453','1972-07-31','5631 Rice, Houston, TX','F','25000','333445555','5');
94INSERT INTO EMPLOYEE
95VALUES('Alicia','J','Zelaya','999887777','1968-01-19','3321 Castle, Spring, TX','F','25000','987654321','4');
96INSERT INTO EMPLOYEE
97VALUES('Ahmad','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston, TX','M','25000','987654321','4');
98
99
100INSERT INTO DEPT_LOCATIONS VALUES('1','Houston');
101INSERT INTO DEPT_LOCATIONS VALUES('4','Stafford');
102INSERT INTO DEPT_LOCATIONS VALUES('5','Bellaire');
103INSERT INTO DEPT_LOCATIONS VALUES('5','Sugarland');
104INSERT INTO DEPT_LOCATIONS VALUES('5','Houston');
105
106
107INSERT INTO PROJECT
108VALUES('ProductX','1','Bellaire','5');
109INSERT INTO PROJECT
110VALUES('ProductY','2','Sugarland','5');
111INSERT INTO PROJECT
112VALUES('ProductZ','3','Houston','5');
113INSERT INTO PROJECT
114VALUES('Computerization','10','Stafford','4');
115INSERT INTO PROJECT
116VALUES('Reorganization','20','Houston','1');
117INSERT INTO PROJECT
118VALUES('Newbenefits','30','Stafford','4');
119
120-- Insertion of sixteen WORKS_ON rows
121INSERT INTO WORKS_ON VALUES('123456789','1','32.5');
122INSERT INTO WORKS_ON VALUES('123456789','2','7.5');
123INSERT INTO WORKS_ON VALUES('666884444','3','40.0');
124INSERT INTO WORKS_ON VALUES('453453453','1','20.0');
125INSERT INTO WORKS_ON VALUES('453453453','2','20.0');
126INSERT INTO WORKS_ON VALUES('333445555','2','10.0');
127INSERT INTO WORKS_ON VALUES('333445555','3','10.0');
128INSERT INTO WORKS_ON VALUES('333445555','10','10.0');
129INSERT INTO WORKS_ON VALUES('333445555','20','10.0');
130INSERT INTO WORKS_ON VALUES('999887777','30','30.0');
131INSERT INTO WORKS_ON VALUES('999887777','10','10.0');
132INSERT INTO WORKS_ON VALUES('987987987','10','35.0');
133INSERT INTO WORKS_ON VALUES('987987987','30','5.0');
134INSERT INTO WORKS_ON VALUES('987654321','30','20.0');
135INSERT INTO WORKS_ON VALUES('987654321','20','15.0');
136INSERT INTO WORKS_ON VALUES('888665555','20','0.0');
137
138
139INSERT INTO DEPENDENT
140VALUES('333445555','Alice','F','1986-04-05','daughter');
141INSERT INTO DEPENDENT
142VALUES('333445555','Theodore','M','1983-10-25','son');
143INSERT INTO DEPENDENT
144VALUES('333445555','Joy','F','1958-05-03','spouse');
145INSERT INTO DEPENDENT
146VALUES('987654321','Abner','M','1942-02-28','spouse');
147INSERT INTO DEPENDENT
148VALUES('123456789','Michael','M','1988-01-04','son');
149INSERT INTO DEPENDENT
150VALUES('123456789','Alice','F','1988-12-30','daughter');
151INSERT INTO DEPENDENT
152VALUES('123456789','Elizabeth','F','1967-05-05','spouse');