exampledb

DROP TABLE IF EXISTS department;
CREATE TABLE department (
  dname        VARCHAR(25) NOT NULL,
  dno          INTEGER,
  mgrssn       CHAR(9) NOT NULL,
  mgrstartdate DATE,
  PRIMARY KEY (dno),
  UNIQUE (dname)
);


DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
  fname    VARCHAR(15) NOT NULL,
  minit    VARCHAR(1),
  lname    VARCHAR(15) NOT NULL,
  ssn      CHAR(9),
  bdate    DATE,
  address  VARCHAR(50),
  gender      CHAR,
  salary   DECIMAL(10,2),
  superssn CHAR(9),
  dno      INTEGER,
  PRIMARY KEY (ssn)
);


DROP TABLE IF EXISTS project;
CREATE TABLE project (
  pname      VARCHAR(25) NOT NULL,
  pno        INTEGER,
  plocation  VARCHAR(15),
  dno        INTEGER NOT NULL,
  PRIMARY KEY (pno),
  UNIQUE (pname)
);


DROP TABLE IF EXISTS dept_locations;
CREATE TABLE dept_locations (
  dno       INTEGER,
  dlocation VARCHAR(15),
  PRIMARY KEY (dno,dlocation)
);


DROP TABLE IF EXISTS dependent;
CREATE TABLE dependent (
  essn           CHAR(9),
  depname VARCHAR(15),
  gender            CHAR,
  bdate          DATE,
  relationship   VARCHAR(8),
  PRIMARY KEY (essn,depname)
);


DROP TABLE IF EXISTS works_on;
CREATE TABLE works_on (
  ssn   CHAR(9),
  pno    INTEGER,
  hours  DECIMAL(4,1),
  PRIMARY KEY (ssn,pno)
);

ALTER TABLE employee ADD CONSTRAINT fke FOREIGN KEY(dno) REFERENCES department(dno);
ALTER TABLE employee ADD CONSTRAINT fkessn FOREIGN KEY(superssn) REFERENCES employee(ssn);
ALTER TABLE project ADD CONSTRAINT fkpno FOREIGN KEY(dno) REFERENCES department(dno);
ALTER TABLE dept_locations ADD CONSTRAINT fkdeptpk FOREIGN KEY(dno) REFERENCES department(dno);
ALTER TABLE dependent ADD CONSTRAINT fkdepefk FOREIGN KEY(essn) REFERENCES employee(ssn);
ALTER TABLE works_on ADD CONSTRAINT fkwonem FOREIGN KEY(ssn) REFERENCES employee(ssn);
ALTER TABLE works_on ADD CONSTRAINT fkwonpr FOREIGN KEY(pno) REFERENCES project(pno);