

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


