--Credits to Antonia Tsiftsi--

ALTER TABLE EMPLOYEE drop constraint FK_EMP_DEPT;
ALTER TABLE EMPLOYEE drop constraint FK_EMP_EMP;

DROP TABLE WORKS_ON;
DROP TABLE PROJECT;
DROP TABLE DEPT_LOCATIONS;
DROP TABLE DEPENDENT;
DROP TABLE DEPARTMENT;
DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(20),
MINIT CHAR(1),
LNAME VARCHAR(20),
SSN NUMBER(10),
BDATE DATE,
ADDRESS VARCHAR(50),
SEX CHAR(1),
SALARY NUMBER(6),
SUPERSSN NUMBER(10),
DNO NUMBER(3),
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (SSN)
);
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(20),
DNUMBER NUMBER(3),
MGRSSN NUMBER(10),
MGRSTARTDATE DATE,
CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DNUMBER),
CONSTRAINT FK_DEPT_EMP FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
);
CREATE TABLE DEPENDENT
(
ESSN NUMBER(10),
DEPENDENT_NAME VARCHAR(20),
SEX CHAR(1),
BDATE DATE,
RELATIONSHIP VARCHAR(20),
CONSTRAINT PK_DEPENDENT PRIMARY KEY (ESSN, DEPENDENT_NAME),
CONSTRAINT FK_DEP_EMP FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)
);
CREATE TABLE DEPT_LOCATIONS
(
DNUMBER NUMBER(3),
DLOCATION VARCHAR(20),
CONSTRAINT PK_DEPT_LOC PRIMARY KEY (DNUMBER, DLOCATION),
CONSTRAINT FK_DEPT_LOC_DEPT FOREIGN KEY (DNUMBER) REFERENCES
DEPARTMENT(DNUMBER)
);
CREATE TABLE PROJECT
(
PNAME VARCHAR(20),
PNUMBER NUMBER(3),
PLOCATION VARCHAR(20),
DNUM NUMBER(3),
CONSTRAINT PK_PROJECT PRIMARY KEY (PNUMBER),
CONSTRAINT FK_PRO_DEPT FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER)
);
CREATE TABLE WORKS_ON

(
ESSN NUMBER(10),
PNO NUMBER(3),
HOURS NUMBER(2),
CONSTRAINT PK_WORKS_ON PRIMARY KEY (ESSN,PNO),
CONSTRAINT FK_WORKS_ON_PROJECT FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER),
CONSTRAINT FK_WORKS_ON_DEPT FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)
);

ALTER TABLE EMPLOYEE
ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER);
ALTER TABLE EMPLOYEE
ADD CONSTRAINT FK_EMP_EMP FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN);
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

commit;

INSERT INTO DEPARTMENT VALUES ('Research', 5, null, '1988-05-22');
INSERT INTO DEPARTMENT VALUES ('Administration', 4, null, '1995-01-01');
INSERT INTO DEPARTMENT VALUES ('Headquarters', 1, null, '1981-06-19');
INSERT INTO EMPLOYEE VALUES ('James', 'E', 'Bong', 888665555, '1937-11-10', '450 Stone, Houston,
TX', 'M', 55000, null, 1);
INSERT INTO EMPLOYEE VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston,
TX', 'M', 40000, 888665555, 5);
INSERT INTO EMPLOYEE VALUES ('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondron, Houston,
TX', 'M', 30000, 333445555,5);
INSERT INTO EMPLOYEE VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire,
TX', 'F', 43000, 888665555,4);
INSERT INTO EMPLOYEE VALUES ('Alicia', 'J', 'Zelaya', 999887777, '1968-07-19', '3321 Castle, Spring, TX',
'F', 25000, 987654321, 4);
INSERT INTO EMPLOYEE VALUES ('Ramesh', 'K', 'Narayan', 666884444, '1962-09-15', '975 Fire Oak,
Humble, TX', 'M', 38000, 333445555, 5);
INSERT INTO EMPLOYEE VALUES ('Joyce', 'A', 'English', 453453453, '1972-07-31', '5631 Rice, Houston,
TX', 'F', 25000, 333445555, 5);
INSERT INTO EMPLOYEE VALUES('Ahmad','V','Jabbar',987987987,'1969-03-29','980 Dallas Houston
TX','M',25000,987654321,4);
UPDATE DEPARTMENT SET MGRSSN = 333445555 WHERE dnumber=5;
UPDATE DEPARTMENT SET MGRSSN = 987654321 WHERE dnumber=4;
UPDATE DEPARTMENT SET MGRSSN = 888665555 WHERE dnumber=1;

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,'Sugarland');
insert into DEPT_LOCATIONS values(5,'Houston');
commit;
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 DEPENDENT values(333445555,'Alice','F','1986-04-05','DAUGHTER');
insert into DEPENDENT values(333445555,'Theodore','M','1983-10-25','SON');
insert into DEPENDENT values(333445555,'Joy','F','1958-05-03','SPOUSE');
insert into DEPENDENT values(987654321,'Abner','M','1942-02-28','SPOUSE');
insert into DEPENDENT values(123456789,'Michael','M','1988-01-04','SON');
insert into DEPENDENT values(123456789,'Alice','F','1988-12-30','DAUGHTER');
insert into DEPENDENT values(123456789,'Elizabeth','F','1967-05-05','SPOUSE');

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,null);