DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS DEPARTMENT;
DROP TABLE IF EXISTS DEPT_LOCATIONS;
DROP TABLE IF EXISTS PROJECT;
DROP TABLE IF EXISTS WORKS_ON;
DROP TABLE IF EXISTS DEPENDENT;


CREATE TABLE EMPLOYEE(
   FNAME VARCHAR(10) NOT NULL,
   MINIT VARCHAR(1),
   LNAME VARCHAR(10) NOT NULL,
   SSN INTEGER(9) PRIMARY KEY,
   BDATE DATE,
   ADDRESS VARCHAR(30),
   SEX ENUM('M','F'),
   SALARY REAL(7,2) UNSIGNED,
   SUPERSSN INTEGER(9),
   DNO INTEGER(1));

CREATE TABLE DEPARTMENT(
   DNAME VARCHAR(20) NOT NULL,
   DNUMBER INTEGER(1) PRIMARY KEY,
   MGRSSN INTEGER(9),
   MGRSTARTDATE DATE);

CREATE TABLE DEPT_LOCATIONS(
   DNUMBER INTEGER(1) NOT NULL,
   DLOCATION VARCHAR(20) NOT NULL,
   PRIMARY KEY (DNUMBER, DLOCATION));

CREATE TABLE PROJECT(
   PNAME VARCHAR(20),
   PNUMBER INTEGER PRIMARY KEY,
   PLOCATION VARCHAR(20),
   DNUM INTEGER(1));

CREATE TABLE WORKS_ON(
   ESSN INTEGER(9) NOT NULL,
   PNO INTEGER NOT NULL,
   HOURS REAL(3,1),
   PRIMARY KEY(ESSN, PNO));

CREATE TABLE DEPENDENT(
   ESSN INTEGER(9) NOT NULL,
   DEPENDENT_NAME VARCHAR(15),
   SEX ENUM('M','F'),
   BDATE DATE,
   RELATIONSHIP ENUM('DAUTHER', 'SON', 'SPOUSE'));


INSERT INTO EMPLOYEE VALUES('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5);
INSERT INTO EMPLOYEE VALUES('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5);
INSERT INTO EMPLOYEE VALUES('Alicia', 'J', 'Zelaya', 999887777, '1968-07-19', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4);
INSERT INTO EMPLOYEE VALUES('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 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);
INSERT INTO EMPLOYEE VALUES('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, null, 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');

INSERT INTO DEPARTMENT VALUES('Research', 5, 333445555, '1988-05-22');
INSERT INTO DEPARTMENT VALUES('Administration', 4, 987654321, '1995-01-01');
INSERT INTO DEPARTMENT VALUES('Headquarters', 1, 888665555, '1981-06-19');

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

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');

ALTER TABLE EMPLOYEE ADD INDEX DNO_IDX (DNO);
ALTER TABLE EMPLOYEE ADD CONSTRAINT DNO_FK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER);
ALTER TABLE EMPLOYEE ADD INDEX SUPERSSN_IDX (SUPERSSN);
ALTER TABLE EMPLOYEE ADD CONSTRAINT SUPERSSN_FK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN);

ALTER TABLE DEPARTMENT ADD INDEX MGRSSN_IDX (MGRSSN);
ALTER TABLE DEPARTMENT ADD CONSTRAINT MGRSSN_FK FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN);

ALTER TABLE DEPT_LOCATIONS ADD INDEX DNUMBER_IDX (DNUMBER);
ALTER TABLE DEPT_LOCATIONS ADD CONSTRAINT DNUMBER_FK FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER);

ALTER TABLE PROJECT ADD INDEX DNUM_IDX (DNUM);
ALTER TABLE PROJECT ADD CONSTRAINT DNUM_FK FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER);

ALTER TABLE WORKS_ON ADD INDEX PNO_IDX (PNO);
ALTER TABLE WORKS_ON ADD CONSTRAINT PNO_FK FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER);
ALTER TABLE WORKS_ON ADD INDEX ESSN_IDX1 (ESSN);
ALTER TABLE WORKS_ON ADD CONSTRAINT ESSN_FK1 FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN);

ALTER TABLE DEPENDENT ADD INDEX ESSN_IDX2 (ESSN);
ALTER TABLE DEPENDENT ADD CONSTRAINT ESSN_FK2 FOREIGN KEY(ESSN) REFERENCES EMPLOYEE(SSN);