· 8 years ago · Nov 20, 2017, 08:36 PM
1/* Company database SQL data definition.
2 Ordering of tables is important
3 since a table can reference tables that are defined before.
4 Since date type is not available in MS_SQL,
5 create a new user defined data type date as char(10).
6*/
7
8CREATE DATABASE IF NOT EXISTS CompanyDB;
9USE CompanyDB;
10
11-- If script is run twice in a row, the tables will be dropped and
12-- created again
13
14 drop table IF EXISTS DEPENDENT;
15 drop table IF EXISTS WORKS_ON;
16 drop table IF EXISTS PROJECT;
17 drop table IF EXISTS DEPT_LOCATIONS;
18 drop table IF EXISTS EMPLOYEE;
19 drop table IF EXISTS DEPARTMENT;
20
21
22-- create a new empty table DEPARTMENT
23create table DEPARTMENT
24( dname varchar(15) not null,
25 dnumber int not null,
26 mgrssn char(9) not null,
27 mgrstartdate char(10),
28 constraint deptpk
29 primary key (dnumber),
30 constraint deptsk
31 unique (dname)
32);
33
34-- create EMPLOYEE table
35create table EMPLOYEE
36( fname varchar(15) not null,
37 minit char,
38 lname varchar(15) not null,
39 ssn char(9) not null,
40 bdate char(10),
41 address varchar(30),
42 sex char,
43 salary decimal(10,2),
44 superssn char(9),
45 dno int not null,
46 constraint emppk
47 primary key (ssn),
48 constraint empdeptfk
49 foreign key (dno) references DEPARTMENT(dnumber)
50);
51
52-- create a new empty table DEPT_LOCATIONS
53create table DEPT_LOCATIONS
54( dnumber int not null,
55 dlocation varchar(15) not null,
56 constraint dlocpk
57 primary key (dnumber, dlocation),
58 constraint dlocdeptfk
59 foreign key (dnumber) references DEPARTMENT(dnumber)
60);
61
62-- create a new empty table PROJECT
63create table PROJECT
64( pname varchar(15) not null,
65 pnumber int not null,
66 plocation varchar(15),
67 dnum int not null,
68 constraint projpk
69 primary key (pnumber),
70 constraint projsk
71 unique (pname),
72 constraint projdeptfk
73 foreign key (dnum) references DEPARTMENT(dnumber)
74);
75
76-- create a new empty table WORKS_ON
77create table WORKS_ON
78( essn char(9) not null,
79 pno int not null,
80 hours decimal(3,1) not null,
81 constraint workpk
82 primary key (essn, pno),
83 constraint workempfk
84 foreign key (essn) references EMPLOYEE(ssn),
85 constraint workprojfk
86 foreign key (pno) references PROJECT(pnumber)
87);
88
89-- create a new empty table DEPENDENT
90create table DEPENDENT
91( essn char(9) not null,
92 DEPENDENT_name varchar(15) not null,
93 sex char,
94 bdate char(10),
95 relationship varchar(8),
96 constraint deppk
97 primary key (essn, DEPENDENT_name),
98 constraint depempfk
99 foreign key (essn) references EMPLOYEE(ssn)
100);
101
102
103-- Insertion of DEPARTMENT rows
104INSERT INTO DEPARTMENT
105VALUES('Research','5','333445555','1988-05-22');
106INSERT INTO DEPARTMENT
107VALUES('Administration','4','987654321','1995-01-01');
108INSERT INTO DEPARTMENT
109VALUES('Headquarters','1','888665555','1981-06-19');
110
111-- Insertion of EMPLOYEE rows
112INSERT INTO EMPLOYEE
113VALUES
114('James','E','Borg','888665555','1937-11-10','450 Stone, Houston, TX','M','55000','','1');
115INSERT INTO EMPLOYEE
116VALUES
117('Franklin','T','Wong','333445555','1955-12-08','638 Voss, Houston, TX','M','40000','888665555','5');
118INSERT INTO EMPLOYEE
119VALUES
120('Jennifer','S','Wallace','987654321','1941-06-20','291 Berry, Bellaire, TX','F','43000','888665555','4');
121INSERT INTO EMPLOYEE
122VALUES
123('John','B','Smith','123456789','1965-01-09','731 Fondren, Houston, TX','M','30000','333445555','5 ');
124INSERT INTO EMPLOYEE
125VALUES
126('Ramesh','K','Narayan','666884444','1962-09-15','975 Fire Oak, Humble, TX','M','38000','333445555','5');
127INSERT INTO EMPLOYEE
128VALUES
129('Joyce','A','English','453453453','1972-07-31','5631 Rice, Houston, TX','F','25000','333445555','5');
130INSERT INTO EMPLOYEE
131VALUES
132('Alicia','J','Zelaya','999887777','1968-01-19','3321 Castle, Spring, TX','F','25000','987654321','4');
133INSERT INTO EMPLOYEE
134VALUES
135('Ahmad','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston, TX','M','25000','987654321','4');
136
137-- Insertion of DEPT_LOCATIONS rows
138INSERT INTO DEPT_LOCATIONS
139VALUES
140('1','Houston');
141INSERT INTO DEPT_LOCATIONS
142VALUES
143('4','Stafford');
144INSERT INTO DEPT_LOCATIONS
145VALUES
146('5','Bellaire');
147INSERT INTO DEPT_LOCATIONS
148VALUES
149('5','Sugarland');
150INSERT INTO DEPT_LOCATIONS
151VALUES
152('5','Houston');
153
154-- Insertion of PROJECT rows
155INSERT INTO PROJECT
156VALUES
157('ProductX','1','Bellaire','5');
158INSERT INTO PROJECT
159VALUES
160('ProductY','2','Sugarland','5');
161INSERT INTO PROJECT
162VALUES
163('ProductZ','3','Houston','5');
164INSERT INTO PROJECT
165VALUES
166('Computerization','10','Stafford','4');
167INSERT INTO PROJECT
168VALUES
169('Reorganization','20','Houston','1');
170INSERT INTO PROJECT
171VALUES
172('Newbenefits','30','Stafford','4');
173
174-- Insertion of WORKS_ON rows
175INSERT INTO WORKS_ON
176VALUES
177('123456789','1','32.5');
178INSERT INTO WORKS_ON
179VALUES
180('123456789','2','7.5');
181INSERT INTO WORKS_ON
182VALUES
183('666884444','3','40.0');
184INSERT INTO WORKS_ON
185VALUES
186('453453453','1','20.0');
187INSERT INTO WORKS_ON
188VALUES
189('453453453','2','20.0');
190INSERT INTO WORKS_ON
191VALUES
192('333445555','2','10.0');
193INSERT INTO WORKS_ON
194VALUES
195('333445555','3','10.0');
196INSERT INTO WORKS_ON
197VALUES
198('333445555','10','10.0');
199INSERT INTO WORKS_ON
200VALUES
201('333445555','20','10.0');
202INSERT INTO WORKS_ON
203VALUES
204('999887777','30','30.0');
205INSERT INTO WORKS_ON
206VALUES
207('999887777','10','10.0');
208INSERT INTO WORKS_ON
209VALUES
210('987987987','10','35.0');
211INSERT INTO WORKS_ON
212VALUES
213('987987987','30','5.0');
214INSERT INTO WORKS_ON
215VALUES
216('987654321','30','20.0');
217INSERT INTO WORKS_ON
218VALUES
219('987654321','20','15.0');
220INSERT INTO WORKS_ON
221VALUES
222('888665555','20','0.0');
223
224-- Insertion of DEPENDENT rows
225INSERT INTO DEPENDENT
226VALUES
227('333445555','Alice','F','1986-04-05','daughter');
228INSERT INTO DEPENDENT
229VALUES
230('333445555','Theodore','M','1983-10-25','son');
231INSERT INTO DEPENDENT
232VALUES
233('333445555','Joy','F','1958-05-03','spouse');
234INSERT INTO DEPENDENT
235VALUES
236('987654321','Abner','M','1942-02-28','spouse');
237INSERT INTO DEPENDENT
238VALUES
239('123456789','Michael','M','1988-01-04','son');
240INSERT INTO DEPENDENT
241VALUES
242('123456789','Alice','F','1988-12-30','daughter');
243INSERT INTO DEPENDENT
244VALUES
245('123456789','Elizabeth','F','1967-05-05','spouse');
246
247/* End of Company database script */