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