DROP TABLE DEPENDENT CASCADE CONSTRAINTS;

CREATE TABLE DEPENDENT (
       DEPENDENT_NAME       VARCHAR2(20) NOT NULL,
       ESSN                 CHAR(9) NOT NULL,
       SEX                  CHAR(1) NULL,
       BDATE                DATE NULL,
       RELATIONSHIP         VARCHAR2(8) NULL
);


ALTER TABLE DEPENDENT
       ADD  ( PRIMARY KEY (DEPENDENT_NAME, ESSN) ) ;


DROP TABLE WORKS_ON CASCADE CONSTRAINTS;

CREATE TABLE WORKS_ON (
       ESSN                 CHAR(9) NOT NULL,
       PNO                  INTEGER NOT NULL,
       HOURS                NUMBER(3,1) NULL
);


ALTER TABLE WORKS_ON
       ADD  ( PRIMARY KEY (ESSN, PNO) ) ;


DROP TABLE PROJECT CASCADE CONSTRAINTS;

CREATE TABLE PROJECT (
       PNUMBER              INTEGER NOT NULL,
       DNUMBER              INTEGER NOT NULL,
       PNAME                VARCHAR2(15) NOT NULL,
       PLOCATION            VARCHAR2(20) NULL,
       UNIQUE (
              PNAME
       )
);


ALTER TABLE PROJECT
       ADD  ( PRIMARY KEY (PNUMBER) ) ;


DROP TABLE DEPTARTMENT_LOCATION CASCADE CONSTRAINTS;

CREATE TABLE DEPTARTMENT_LOCATION (
       DLOCATION            VARCHAR2(20) NOT NULL,
       DNUMBER              INTEGER NOT NULL
);


ALTER TABLE DEPTARTMENT_LOCATION
       ADD  ( PRIMARY KEY (DLOCATION, DNUMBER) ) ;


DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE EMPLOYEE (
       SSN                  CHAR(9) NOT NULL,
       DNO                  INTEGER NOT NULL,
       FNAME                VARCHAR2(15) NULL,
       MINIT                CHAR(1) NULL,
       LNAME                VARCHAR2(20) NULL,
       BDATE                DATE NULL,
       ADDRESS              VARCHAR2(30) NULL,
       SEX                  CHAR(1) NULL,
       SALARY               NUMBER(10,2) NULL,
       SUPERSSN             CHAR(9) NULL
);

COMMENT ON COLUMN EMPLOYEE.SSN IS 'EMPLOYEE SOcial Security Number';
COMMENT ON COLUMN EMPLOYEE.SUPERSSN IS 'Supervisor Social Security Number';

ALTER TABLE EMPLOYEE
       ADD  ( PRIMARY KEY (SSN) ) ;


DROP TABLE DEPTARTMENT CASCADE CONSTRAINTS;

CREATE TABLE DEPTARTMENT (
       DNUMBER              INTEGER NOT NULL,
       DNAME                VARCHAR2(10) NOT NULL,
       MGRSS                VARCHAR2(9) NULL,
       MGRSTARTDATE         DATE NULL,
       UNIQUE (
              DNAME
       )
);


ALTER TABLE DEPTARTMENT
       ADD  ( PRIMARY KEY (DNUMBER) ) ;


ALTER TABLE DEPENDENT
       ADD  ( FOREIGN KEY (ESSN)
                             REFERENCES EMPLOYEE
                             ON DELETE CASCADE ) ;


ALTER TABLE WORKS_ON
       ADD  ( FOREIGN KEY (ESSN)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE WORKS_ON
       ADD  ( FOREIGN KEY (PNO)
                             REFERENCES PROJECT ) ;


ALTER TABLE PROJECT
       ADD  ( FOREIGN KEY (DNUMBER)
                             REFERENCES DEPTARTMENT ) ;


ALTER TABLE DEPTARTMENT_LOCATION
       ADD  ( FOREIGN KEY (DNUMBER)
                             REFERENCES DEPTARTMENT ) ;


ALTER TABLE EMPLOYEE
       ADD  ( FOREIGN KEY (SUPERSSN)
                             REFERENCES EMPLOYEE ) ;


ALTER TABLE EMPLOYEE
       ADD  ( FOREIGN KEY (DNO)
                             REFERENCES DEPTARTMENT ) ;