SET DEFINE OFF;
SET SCAN OFF;

PURGE RECYCLEBIN;
DROP TABLE dependent cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE dept_locations cascade constraints;

PURGE RECYCLEBIN;

PURGE RECYCLEBIN;
DROP TABLE project cascade constraints;

PURGE RECYCLEBIN;
DROP TABLE works_on cascade constraints;

PURGE RECYCLEBIN;
COMMIT;


CREATE TABLE dependent(
	essn	char(9),
	dependent_name	varchar2(15),
	sex	char(1),
	bdate	date,
	relationship	varchar2(8));

INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('333445555','Alice','F',TO_DATE('1976-04-05','YYYY-MM-DD'),'Daughter');
COMMIT;
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('333445555','Theodore','M',TO_DATE('1973-10-25','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('333445555','Joy','F',TO_DATE('1948-05-03','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('987654321','Abner','M',TO_DATE('1932-02-29','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('123456789','Michael','M',TO_DATE('1978-01-01','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('123456789','Alice','F',TO_DATE('1978-12-31','YYYY-MM-DD'),'Daughter');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444400','Johnny','M',TO_DATE('1997-04-04','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444400','Tommy','M',TO_DATE('1999-06-07','YYYY-MM-DD'),'Son');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444401','Chris','M',TO_DATE('1969-04-19','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('444444402','Sam','M',TO_DATE('1964-02-14','YYYY-MM-DD'),'Spouse');
INSERT INTO dependent(essn,dependent_name,sex,bdate,relationship) VALUES ('123456789','Elizabeth','F',TO_DATE('1973-12-10','YYYY-MM-DD'),'Spouse');
COMMIT;


CREATE TABLE dept_locations(
	dnumber	integer,
	dlocation	varchar2(15));

INSERT INTO dept_locations(dnumber,dlocation) VALUES ('1','Houston');
COMMIT;
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('4','Stafford');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('5','Bellaire');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('5','Houston');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('5','Sugarland');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('6','Atlanta');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('6','Sacramento');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('7','Milwaukee');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Chicago');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Dallas');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Miami');
COMMIT;
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Philadephia');
INSERT INTO dept_locations(dnumber,dlocation) VALUES ('8','Seattle');
COMMIT;
COMMIT;
COMMIT;
COMMIT;
COMMIT;


CREATE TABLE project(
	pname	varchar2(50),
	pnumber	integer,
	plocation	varchar2(15),
	dnum	integer);

INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('ProductX','1','Bellaire','5');
COMMIT;
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('ProductY','2','Sugarland','5');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('ProductZ','3','Houston','5');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('Computerization','10','Stafford','4');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('Reorganization','20','Houston','1');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('Newbenefits','30','Stafford','4');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('DatabaseSystems','62','Birmingham','6');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('Middleware','63','Jackson','6');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('InkjetPrinters','91','Phoenix','7');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('LaserPrinters','92','LasVegas','7');
INSERT INTO project(pname,pnumber,plocation,dnum) VALUES ('OperatingSystems','61','Jacksonville','6');
COMMIT;


CREATE TABLE works_on(
	essn	char(9),
	pno	integer,
	hours	numeric);

INSERT INTO works_on(essn,pno,hours) VALUES ('123456789','2','7.5');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('666884444','3','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('453453453','1','20.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('453453453','2','20.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','2','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','3','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','10','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333445555','20','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('999887777','30','30.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('999887777','10','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('987987987','10','35.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('987987987','30','5.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('987654321','30','20.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('987654321','20','15.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('888665555','20','0.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111100','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111101','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111102','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('111111103','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222200','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222201','62','48.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('222222202','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222203','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222204','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('222222205','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333333300','63','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('333333301','63','46.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444400','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444401','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444402','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('444444403','91','40.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('555555500','92','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('555555501','92','44.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666603','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666604','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666605','92','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666606','91','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666607','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666608','62','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666609','63','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666610','61','40.0');
COMMIT;
INSERT INTO works_on(essn,pno,hours) VALUES ('666666611','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666612','61','40.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666613','61','30.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666613','62','10.0');
INSERT INTO works_on(essn,pno,hours) VALUES ('666666613','63','10.0');
ALTER TABLE dependent ADD PRIMARY KEY (essn,dependent_name);
ALTER TABLE dept_locations ADD PRIMARY KEY (dnumber,dlocation);
ALTER TABLE project ADD PRIMARY KEY (pnumber);
ALTER TABLE works_on ADD PRIMARY KEY (essn,pno);
ALTER TABLE dependent ADD CONSTRAINT dependent_essn_fkey FOREIGN KEY (essn) REFERENCES employee(ssn);
ALTER TABLE dept_locations ADD CONSTRAINT dept_locations_dnumber_fkey FOREIGN KEY (dnumber) REFERENCES department(dnumber);
ALTER TABLE project ADD CONSTRAINT project_dnum_fkey FOREIGN KEY (dnum) REFERENCES department(dnumber);
ALTER TABLE works_on ADD CONSTRAINT works_on_essn_fkey FOREIGN KEY (essn) REFERENCES employee(ssn);
ALTER TABLE works_on ADD CONSTRAINT works_on_pno_fkey FOREIGN KEY (pno) REFERENCES project(pnumber);
