· 5 years ago · May 20, 2020, 01:12 PM
1drop schema if exists Eksamen2019 cascade;
2create schema if not exists Eksamen2019;
3set search_path to Eksamen2019;
4
5create table DEPARTMENT
6(
7 Dname varchar(255),
8 Dnumber int NOT NULL,
9 Mgr_ssn int,
10 Mgr_start_date date,
11 PRIMARY KEY (Dnumber)
12);
13
14create table EMPLOYEE
15(
16 Fname varchar(30),
17 Minit char(1),
18 Lname varchar(30),
19 Ssn int Not Null ,
20 Bdate date,
21 Adress varchar(60),
22 Sex char(1),
23 Salary int,
24 Super_ssn int,
25 Dno int,
26 PRIMARY KEY (Ssn),
27 FOREIGN KEY (Dno) references DEPARTMENT (Dnumber),
28 FOREIGN KEY (Super_ssn) references EMPLOYEE (Ssn)
29);
30
31alter table DEPARTMENT ADD FOREIGN KEY (Mgr_ssn) references EMPLOYEE (Ssn);
32
33
34
35create table DEPT_LOCATIONS
36(
37 Dnumber int NOT NULL,
38 Dlocation varchar(255),
39 PRIMARY KEY (Dlocation,Dnumber),
40 FOREIGN KEY (Dnumber) references DEPARTMENT (Dnumber)
41);
42
43create table PROJECT
44(
45 Pname varchar(255),
46 Pnumber int NOT NULL,
47 Plocation varchar(255),
48 Dnum int ,
49 PRIMARY KEY (Pnumber),
50 FOREIGN KEY (Dnum) references DEPARTMENT (Dnumber)
51);
52
53create table WORKS_ON
54(
55 Essn int NOT NULL,
56 Pno int NOT NULL ,
57 Hours DECIMAL,
58 PRIMARY KEY (Essn,Pno),
59 FOREIGN KEY (Essn) references EMPLOYEE (Ssn),
60 FOREIGN KEY (Pno) references PROJECT (Pnumber)
61);
62
63create table DEPENDENT
64(
65 Essn int NOT NULL,
66 Dependent_name varchar(255) ,
67 Sex char(1),
68 Bdate date,
69 Relationship varchar(255),
70 PRIMARY KEY (Essn,Dependent_name),
71 FOREIGN KEY (Essn) references EMPLOYEE(Ssn)
72);
73
74insert into DEPARTMENT(dname, dnumber)
75values ('Research',5),
76 ('Administration', 4),
77 ('Headquarters', 1);
78
79
80insert into EMPLOYEE(fname, minit,Lname, ssn, bdate, adress, sex, salary, super_ssn, dno)
81values ('James','E','Borg',888665555,'1937-11-10','450 Stone, Houston, TX', 'M', 55000,null,1),
82 ('Franklin','T','Wong', 333445555,'1955-12-08','638 Voss, Houston, TX','M', 40000,888665555,5),
83 ('John','B','Smith',123456789,'1965-01-09','731 Fondren. Houston. TX','M',30000,333445555,5),
84 ('Jennifer','S', 'Wallace',987654321,'1941-06-20','291 Berry, Bellaire, TX', 'F', 43000,888665555,4),
85 ('Alicia', 'J','Zelanya',999887777,'1968-01-19','3321 CAstle, Spring, TX', 'F', 25000,987654321,4),
86 ('Ramesh','K','Narayan', 666884444,'1962-09-15','975 Fire Oak, Humble, TX', 'M',38000,333445555,5),
87 ('Ahmad', 'V','Jabbar',987987987,'1969-03-29','980, Dallas, Houston, TX','M',25000,987654321,4),
88 ('Joyce','A','English', 453453453,'1972-07-31','5631 Rice, Houston, TX','F',25000,333445555,5);
89
90update DEPARTMENT set mgr_ssn = 333445555, Mgr_start_date = '1988-05-22' where Dnumber = 5;
91update DEPARTMENT set mgr_ssn = 987654321, Mgr_start_date = '1995-01-01' where Dnumber = 4;
92update DEPARTMENT set mgr_ssn = 888665555, Mgr_start_date = '1981-06-19' where Dnumber = 1;
93
94insert into DEPT_LOCATIONS(dnumber, dlocation)
95values (1,'Houston'),
96 (4, 'Stafford'),
97 (5,'Bellaire'),
98 (5,'Sugerland'),
99 (5,'Houston');
100
101insert into PROJECT (Pname, Pnumber, Plocation, Dnum)
102values ('ProductX', 1,'Bellaire', 5),
103 ('ProductY', 2, 'Sugerland', 5),
104 ('ProductZ', 3, 'Houston', 5),
105 ('Computerization', 10, 'Stafford',4),
106 ('Reorganization', 20,'Houston',1),
107 ('Newbenefits', 30,'Stafford', 4);
108
109insert into WORKS_ON (Essn, Pno, Hours)
110values (123456789,1,32.5),
111 (123456789,2,7.5),
112 (666884444,3,40.0),
113 (453453453,1,20.0),
114 (453453453,2,20.0),
115 (333445555,2,10.0),
116 (333445555,3,10.0),
117 (333445555,10,10.0),
118 (333445555,20,10.0),
119 (999887777,30,30.0),
120 (999887777,10,10.0),
121 (987987987,10,35.0),
122 (987987987,30,5.0),
123 (987654321,30,20.0),
124 (987654321,20,15.0),
125 (888665555,20,null);
126
127insert into DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
128values (333445555,'Alice','F','1986-04-05','Daughter'),
129 (333445555,'Theodore','M','1983-10-25','Son'),
130 (333445555,'Joy','F','1958-05-03','Spouse'),
131 (987654321,'Abner','M','1942-02-28','Spouse'),
132 (123456789,'Michael','M','1988-01-04','Son'),
133 (123456789,'Alice','F','1988-12-30','Daughter'),
134 (123456789,'Elizabeth','F','1967-05-05','Spouse');