/* this version of the employee table definitions (p 91 of Elmasri-Navathe 6th ed)
 * uses the ALTER TABLE option so that foreign key declarations do not precede
 * the relevant table definition
 *
 * Also, tabs were eliminated for easier copy/paste
 */

create table employee (
        fname             varchar(15)         not null,
        minit             char, 
        lname             varchar(15)         not null,
        ssn               char(9)             not null,
        bdate             DATE,
        address           varchar(30), 
        sex               char, 
        salary            decimal(10,2),
        super_ssn         char(9),
        dno               int                 not null,
    primary key (ssn),
    foreign key (super_ssn) references employee(ssn)
    -- foreign key (dno) references department(dnumber)
) engine = innodb;

create table department  (
        dname           varchar(15)     not null,
        dnumber         int             not null,
        mgr_ssn         char(9)         not null,
        mgr_start       date,
    primary key (dnumber),
    unique (dname),
    foreign key (mgr_ssn) references employee(ssn)
) engine = innodb;

create table dept_locations (
        dnumber          int                not null,
        dlocation        varchar(15)        not null,
    primary key (dnumber, dlocation),
    foreign key (dnumber) references department(dnumber)
) engine = innodb;

create table project (
        pname            varchar(15)        not null,
        pnumber          int                not null,
        plocation        varchar(15),
        dnum             int                not null,
    primary key (pnumber),
    unique (pname),
    foreign key (dnum) references department(dnumber)
) engine = innodb;

create table works_on (
        essn               char(9)            not null,
        pno                int                not null,
        hours              decimal(3,1)       not null,
    primary key (essn, pno),
    foreign key (essn) references employee(ssn),
    foreign key (pno) references project(pnumber)
) engine = innodb;

create table dependent (
        essn               char(9)            not null,
        dependent_name     varchar(15)        not null,
        sex                char,
        bdate              date,
        relationship       varchar(8),
    primary key (essn, dependent_name),
    foreign key (essn) references employee (ssn)
) engine = innodb;

alter table employee ADD foreign key (dno) references department(dnumber);