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),
  sex      char,
  salary   decimal(10,2),
  superssn char(9),
  dno      int(4),
  primary key (ssn),
  foreign key (superssn) references employee(ssn)
 -- foreign key (dno) references department(dint)
);

DROP TABLE if exists department;
CREATE TABLE department (
  dname        varchar(25) not null,
  dint      int(4),
  mgrssn       char(9) not null, 
  mgrstartdate date,
  primary key (dint),
  unique (dname),
  foreign key (mgrssn) references employee(ssn)
);

ALTER TABLE employee ADD (
  foreign key (dno) references department(dint)
);

DROP TABLE if exists dept_locations;
CREATE TABLE dept_locations (
  dint   int(4),
  dlocation varchar(15), 
  primary key (dint,dlocation),
  foreign key (dint) references department(dint)
);

DROP TABLE if exists project;
CREATE TABLE project (
  pname      varchar(25) not null,
  pint    int(4),
  plocation  varchar(15),
  dnum       int(4) not null,
  primary key (pint),
  unique (pname),
  foreign key (dnum) references department(dint)
);

DROP TABLE if exists works_on;
CREATE TABLE works_on (
  essn   char(9),
  pno    int(4),
  hours  decimal(4,1),
  primary key (essn,pno),
  foreign key (essn) references employee(ssn),
  foreign key (pno) references project(pint)
);

DROP TABLE if exists dependent;
CREATE TABLE dependent (
  essn           char(9),
  dependent_name varchar(15),
  sex            char,
  bdate          date,
  relationship   varchar(8),
  primary key (essn,dependent_name),
  foreign key (essn) references employee(ssn)
);