-
Notifications
You must be signed in to change notification settings - Fork 1
/
create database company
253 lines (217 loc) · 13.8 KB
/
create database company
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
################################################################################################################
This SQL script will create the database called Company in your MySQL or any other DBMS framework.
It also builds all the database objects, in accordance with the following book:
Book information
Introduction to Databse systems
by
Ramez Elmasri and Shamkant Navate
This is a fairly good database and is handy if you are following this book, because a lot of examples are based
out of this database design. So you can practice off it.
Page 71-72 of the book explains this database.
www.srayan.tk
################################################################################################################
CREATE DATABASE Company ;
#Creating Company Schema
USE Company;
#--DROP TABLE DEPARTMENT;
CREATE TABLE DEPARTMENT (
dname varchar(25) not null,
dnumber int not null,
mgrssn char(9) not null,
mgrstartdate date,
CONSTRAINT pk_Department primary key (dnumber),
CONSTRAINT uk_dname UNIQUE (dname)
);
#DROP TABLE EMPLOYEE;'
CREATE TABLE EMPLOYEE (
fname varchar(15) not null,
minit varchar(1),
lname varchar(15) not null,
ssn char(9),
bdate date,
address varchar(50),
sex char,
salary decimal(10,2),
superssn char(9),
dno int,
CONSTRAINT pk_employee primary key (ssn),
#'--CONSTRAINT fk_employee_employee foreign key (superssn) references EMPLOYEE(ssn), -- Constraint Will be added later to prevent cyclic referential itegrity violation'
CONSTRAINT fk_employee_department foreign key (dno) references DEPARTMENT(dnumber)
);
#'--DROP TABLE DEPENDENT;'
CREATE TABLE DEPENDENT (
essn char(9),
dependent_name varchar(15),
sex char,
bdate date,
relationship varchar(8),
CONSTRAINT pk_essn_dependent_name primary key (essn,dependent_name),
CONSTRAINT fk_dependent_employee foreign key (essn) references EMPLOYEE(ssn)
);
#'--DROP TABLE DEPT_LOCATIONS;'
CREATE TABLE DEPT_LOCATIONS (
dnumber int,
dlocation varchar(15),
CONSTRAINT pk_dept_locations primary key (dnumber,dlocation),
CONSTRAINT fk_deptlocations_department foreign key (dnumber) references DEPARTMENT(dnumber)
);
#'--DROP TABLE PROJECT;'
CREATE TABLE PROJECT (
pname varchar(25) not null,
pnumber int,
plocation varchar(15),
dnum int not null,
CONSTRAINT ok_project primary key (pnumber),
CONSTRAINT uc_pnumber unique (pname),
CONSTRAINT fk_project_department foreign key (dnum) references DEPARTMENT(dnumber)
);
#'--DROP TABLE WORKS_ON;'
CREATE TABLE WORKS_ON (
essn char(9),
pno int,
hours decimal(4,1),
CONSTRAINT pk_worksOn primary key (essn,pno),
CONSTRAINT fk_workson_employee foreign key (essn) references EMPLOYEE(ssn),
CONSTRAINT fk_workson_project foreign key (pno) references PROJECT(pnumber)
);
#'--Insert Statements'
use Company;
INSERT INTO DEPARTMENT VALUES ('Research','5','333445555','1978-05-22');
INSERT INTO DEPARTMENT VALUES ('Administration','4','987654321','1985-01-01');
INSERT INTO DEPARTMENT VALUES ('Headquarters','1','888665555','1971-06-19');
INSERT INTO DEPARTMENT VALUES ('Software','6','111111100','1999-05-15');
INSERT INTO DEPARTMENT VALUES ('Hardware','7','444444400','1998-05-15');
INSERT INTO DEPARTMENT VALUES ('Sales','8','555555500','1997-01-01');
INSERT INTO EMPLOYEE VALUES ('Bob','B','Bender','666666600','1968-04-17','8794 Garfield, Chicago, IL','M','96000.00',NULL,'8' );
INSERT INTO EMPLOYEE VALUES ('Kim','C','Grace','333333300','1970-10-23','6677 Mills Ave, Sacramento, CA','F','79000.00',NULL,'6');
INSERT INTO EMPLOYEE VALUES ('James','E','Borg','888665555','1927-11-10','450 Stone, Houston, TX','M','55000.00',NULL,'1');
INSERT INTO EMPLOYEE VALUES ('Alex','D','Freed','444444400','1950-10-09','4333 Pillsbury, Milwaukee, WI','M','89000.00',NULL,'7');
INSERT INTO EMPLOYEE VALUES ('Evan','E','Wallis','222222200','1958-01-16','134 Pelham, Milwaukee, WI','M','92000.00',NULL,'7');
INSERT INTO EMPLOYEE VALUES ('Jared','D','James','111111100','1966-10-10','123 Peachtree, Atlanta, GA','M','85000.00',NULL,'6');
INSERT INTO EMPLOYEE VALUES ('John','C','James','555555500','1975-06-30','7676 Bloomington, Sacramento, CA','M','81000.00',NULL,'6');
INSERT INTO EMPLOYEE VALUES ('Andy','C','Vile','222222202','1944-06-21','1967 Jordan, Milwaukee, WI','M','53000.00','222222200','7');
INSERT INTO EMPLOYEE VALUES ('Brad','C','Knight','111111103','1968-02-13','176 Main St., Atlanta, GA','M','44000.00','111111100','6');
INSERT INTO EMPLOYEE VALUES ('Josh','U','Zell','222222201','1954-05-22','266 McGrady, Milwaukee, WI','M','56000.00','222222200','7');
INSERT INTO EMPLOYEE VALUES ('Justin','n','Mark','111111102','1966-01-12','2342 May, Atlanta, GA','M','40000.00','111111100','6');
INSERT INTO EMPLOYEE VALUES ('Jon','C','Jones','111111101','1967-11-14','111 Allgood, Atlanta, GA','M','45000.00','111111100','6');
INSERT INTO EMPLOYEE VALUES ('Ahmad','V','Jabbar','987987987','1959-03-29','980 Dallas, Houston, TX','M','25000.00','987654321','4');
INSERT INTO EMPLOYEE VALUES ('Joyce','A','English','453453453','1962-07-31','5631 Rice, Houston, TX','F','25000.00','333445555','5');
INSERT INTO EMPLOYEE VALUES ('Ramesh','K','Narayan','666884444','1952-09-15','971 Fire Oak, Humble, TX','M','38000.00','333445555','5');
INSERT INTO EMPLOYEE VALUES ('Alicia','J','Zelaya','999887777','1958-07-19','3321 Castle, Spring, TX','F','25000.00','987654321','4');
INSERT INTO EMPLOYEE VALUES ('John','B','Smith','123456789','1955-01-09','731 Fondren, Houston, TX','M','30000.00','333445555','5');
INSERT INTO EMPLOYEE VALUES ('Jennifer','S','Wallace','987654321','1931-06-20','291 Berry, Bellaire, TX','F','43000.00','888665555','4');
INSERT INTO EMPLOYEE VALUES ('Franklin','T','Wong','333445555','1945-12-08','638 Voss, Houston, TX','M','40000.00','888665555','5');
INSERT INTO EMPLOYEE VALUES ('Tom','G','Brand','222222203','1966-12-16','112 Third St, Milwaukee, WI','M','62500.00','222222200','7');
INSERT INTO EMPLOYEE VALUES ('Jenny','F','Vos','222222204','1967-11-11','263 Mayberry, Milwaukee, WI','F','61000.00','222222201','7');
INSERT INTO EMPLOYEE VALUES ('Chris','A','Carter','222222205','1960-03-21','565 Jordan, Milwaukee, WI','F','43000.00','222222201','7');
INSERT INTO EMPLOYEE VALUES ('Jeff','H','Chase','333333301','1970-01-07','145 Bradbury, Sacramento, CA','M','44000.00','333333300','6');
INSERT INTO EMPLOYEE VALUES ('Bonnie','S','Bays','444444401','1956-06-19','111 Hollow, Milwaukee, WI','F','70000.00','444444400','7');
INSERT INTO EMPLOYEE VALUES ('Alec','C','Best','444444402','1966-06-18','233 Solid, Milwaukee, WI','M','60000.00','444444400','7');
INSERT INTO EMPLOYEE VALUES ('Sam','S','Snedden','444444403','1977-07-31','987 Windy St, Milwaukee, WI','M','48000.00','444444400','7');
INSERT INTO EMPLOYEE VALUES ('Nandita','K','Ball','555555501','1969-04-16','222 Howard, Sacramento, CA','M','62000.00','555555500','6');
INSERT INTO EMPLOYEE VALUES ('Jill','J','Jarvis','666666601','1966-01-14','6234 Lincoln, Chicago, IL','F','36000.00','666666600','8');
INSERT INTO EMPLOYEE VALUES ('Kate','W','King','666666602','1966-04-16','1976 Boone Trace, Chicago, IL','F','44000.00','666666600','8');
INSERT INTO EMPLOYEE VALUES ('Lyle','G','Leslie','666666603','1963-06-09','417 Hancock Ave, Chicago, IL','M','41000.00','666666601','8');
INSERT INTO EMPLOYEE VALUES ('Billie','J','King','666666604','1960-01-01','556 Washington, Chicago, IL','F','38000.00','666666603','8');
INSERT INTO EMPLOYEE VALUES ('Jon','A','Kramer','666666605','1964-08-22','1988 Windy Creek, Seattle, WA','M','41500.00','666666603','8');
INSERT INTO EMPLOYEE VALUES ('Ray','H','King','666666606','1949-08-16','213 Delk Road, Seattle, WA','M','44500.00','666666604','8');
INSERT INTO EMPLOYEE VALUES ('Gerald','D','Small','666666607','1962-05-15','122 Ball Street, Dallas, TX','M','29000.00','666666602','8');
INSERT INTO EMPLOYEE VALUES ('Arnold','A','Head','666666608','1967-05-19','233 Spring St, Dallas, TX','M','33000.00','666666602','8');
INSERT INTO EMPLOYEE VALUES ('Helga','C','Pataki','666666609','1969-03-11','101 Holyoke St, Dallas, TX','F','32000.00','666666602','8');
INSERT INTO EMPLOYEE VALUES ('Naveen','B','Drew','666666610','1970-05-23','198 Elm St, Philadelphia, PA','M','34000.00','666666607','8');
INSERT INTO EMPLOYEE VALUES ('Carl','E','Reedy','666666611','1977-06-21','213 Ball St, Philadelphia, PA','M','32000.00','666666610','8');
INSERT INTO EMPLOYEE VALUES ('Sammy','G','Hall','666666612','1970-01-11','433 Main Street, Miami, FL','M','37000.00','666666611','8');
INSERT INTO EMPLOYEE VALUES ('Red','A','Bacher','666666613','1980-05-21','196 Elm Street, Miami, FL','M','33500.00','666666612','8');
INSERT INTO DEPENDENT VALUES ('333445555','Alice','F','1976-04-05','Daughter');
INSERT INTO DEPENDENT VALUES ('333445555','Theodore','M','1973-10-25','Son');
INSERT INTO DEPENDENT VALUES ('333445555','Joy','F','1948-05-03','Spouse');
INSERT INTO DEPENDENT VALUES ('987654321','Abner','M','1932-02-29','Spouse');
INSERT INTO DEPENDENT VALUES ('123456789','Michael','M','1978-01-01','Son');
INSERT INTO DEPENDENT VALUES ('123456789','Alice','F','1978-12-31','Daughter');
INSERT INTO DEPENDENT VALUES ('123456789','Elizabeth','F',Null,'Spouse'); -- Converted to Null as '0000-00-00' is an invalid date in SQL SERVER
INSERT INTO DEPENDENT VALUES ('444444400','Johnny','M','1997-04-04','Son');
INSERT INTO DEPENDENT VALUES ('444444400','Tommy','M','1999-06-07','Son');
INSERT INTO DEPENDENT VALUES ('444444401','Chris','M','1969-04-19','Spouse');
INSERT INTO DEPENDENT VALUES ('444444402','Sam','M','1964-02-14','Spouse');
INSERT INTO DEPT_LOCATIONS VALUES ('1','Houston');
INSERT INTO DEPT_LOCATIONS VALUES ('4','Stafford');
INSERT INTO DEPT_LOCATIONS VALUES ('5','Bellaire');
INSERT INTO DEPT_LOCATIONS VALUES ('5','Houston');
INSERT INTO DEPT_LOCATIONS VALUES ('5','Sugarland');
INSERT INTO DEPT_LOCATIONS VALUES ('6','Atlanta');
INSERT INTO DEPT_LOCATIONS VALUES ('6','Sacramento');
INSERT INTO DEPT_LOCATIONS VALUES ('7','Milwaukee');
INSERT INTO DEPT_LOCATIONS VALUES ('8','Chicago');
INSERT INTO DEPT_LOCATIONS VALUES ('8','Dallas');
INSERT INTO DEPT_LOCATIONS VALUES ('8','Miami');
INSERT INTO DEPT_LOCATIONS VALUES ('8','Philadephia');
INSERT INTO DEPT_LOCATIONS VALUES ('8','Seattle');
INSERT INTO PROJECT VALUES ('ProductX','1','Bellaire','5');
INSERT INTO PROJECT VALUES ('ProductY','2','Sugarland','5');
INSERT INTO PROJECT VALUES ('ProductZ','3','Houston','5');
INSERT INTO PROJECT VALUES ('Computerization','10','Stafford','4');
INSERT INTO PROJECT VALUES ('Reorganization','20','Houston','1');
INSERT INTO PROJECT VALUES ('Newbenefits','30','Stafford','4');
INSERT INTO PROJECT VALUES ('OperatingSystems','61','Jacksonville','6');
INSERT INTO PROJECT VALUES ('DatabaseSystems','62','Birmingham','6');
INSERT INTO PROJECT VALUES ('Middleware','63','Jackson','6');
INSERT INTO PROJECT VALUES ('InkjetPrinters','91','Phoenix','7');
INSERT INTO PROJECT VALUES ('LaserPrinters','92','LasVegas','7');
INSERT INTO WORKS_ON VALUES ('123456789','1','32.5');
INSERT INTO WORKS_ON VALUES ('123456789','2','7.5');
INSERT INTO WORKS_ON VALUES ('666884444','3','40.0');
INSERT INTO WORKS_ON VALUES ('453453453','1','20.0');
INSERT INTO WORKS_ON VALUES ('453453453','2','20.0');
INSERT INTO WORKS_ON VALUES ('333445555','2','10.0');
INSERT INTO WORKS_ON VALUES ('333445555','3','10.0');
INSERT INTO WORKS_ON VALUES ('333445555','10','10.0');
INSERT INTO WORKS_ON VALUES ('333445555','20','10.0');
INSERT INTO WORKS_ON VALUES ('999887777','30','30.0');
INSERT INTO WORKS_ON VALUES ('999887777','10','10.0');
INSERT INTO WORKS_ON VALUES ('987987987','10','35.0');
INSERT INTO WORKS_ON VALUES ('987987987','30','5.0');
INSERT INTO WORKS_ON VALUES ('987654321','30','20.0');
INSERT INTO WORKS_ON VALUES ('987654321','20','15.0');
INSERT INTO WORKS_ON VALUES ('888665555','20','0.0');
INSERT INTO WORKS_ON VALUES ('111111100','61','40.0');
INSERT INTO WORKS_ON VALUES ('111111101','61','40.0');
INSERT INTO WORKS_ON VALUES ('111111102','61','40.0');
INSERT INTO WORKS_ON VALUES ('111111103','61','40.0');
INSERT INTO WORKS_ON VALUES ('222222200','62','40.0');
INSERT INTO WORKS_ON VALUES ('222222201','62','48.0');
INSERT INTO WORKS_ON VALUES ('222222202','62','40.0');
INSERT INTO WORKS_ON VALUES ('222222203','62','40.0');
INSERT INTO WORKS_ON VALUES ('222222204','62','40.0');
INSERT INTO WORKS_ON VALUES ('222222205','62','40.0');
INSERT INTO WORKS_ON VALUES ('333333300','63','40.0');
INSERT INTO WORKS_ON VALUES ('333333301','63','46.0');
INSERT INTO WORKS_ON VALUES ('444444400','91','40.0');
INSERT INTO WORKS_ON VALUES ('444444401','91','40.0');
INSERT INTO WORKS_ON VALUES ('444444402','91','40.0');
INSERT INTO WORKS_ON VALUES ('444444403','91','40.0');
INSERT INTO WORKS_ON VALUES ('555555500','92','40.0');
INSERT INTO WORKS_ON VALUES ('555555501','92','44.0');
INSERT INTO WORKS_ON VALUES ('666666601','91','40.0');
INSERT INTO WORKS_ON VALUES ('666666603','91','40.0');
INSERT INTO WORKS_ON VALUES ('666666604','91','40.0');
INSERT INTO WORKS_ON VALUES ('666666605','92','40.0');
INSERT INTO WORKS_ON VALUES ('666666606','91','40.0');
INSERT INTO WORKS_ON VALUES ('666666607','61','40.0');
INSERT INTO WORKS_ON VALUES ('666666608','62','40.0');
INSERT INTO WORKS_ON VALUES ('666666609','63','40.0');
INSERT INTO WORKS_ON VALUES ('666666610','61','40.0');
INSERT INTO WORKS_ON VALUES ('666666611','61','40.0');
INSERT INTO WORKS_ON VALUES ('666666612','61','40.0');
INSERT INTO WORKS_ON VALUES ('666666613','61','30.0');
INSERT INTO WORKS_ON VALUES ('666666613','62','10.0');
INSERT INTO WORKS_ON VALUES ('666666613','63','10.0');
#'--Adding constraint after loading data into system'
Alter table EMPLOYEE
ADD CONSTRAINT fk_employee_employee foreign key (superssn) references EMPLOYEE(ssn);
#'--Select Staements for all the tables'
SELECT * FROM DEPARTMENT;
SELECT * FROM DEPENDENT;
SELECT * FROM DEPT_LOCATIONS;
SELECT * FROM EMPLOYEE;
SELECT * FROM PROJECT;
SELECT * FROM WORKS_ON;