/* 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. Data is loaded with the LOAD DATA
 * command, and then the foreign-key constraints are activated
 * It also specifies engine=innodb for MySQL
 *
 * Also, tabs were eliminated for easier copy/paste
 *
 * To load:
 *   source [thisfile]
 */

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;



# ---------------------------------------------------

set foreign_key_checks=0;

load data local infile '/home/pld/353/company/employee.text' into table employee;
load data local infile '/home/pld/353/company/department.text' into table department;
load data local infile '/home/pld/353/company/dept_locations.text' into table dept_locations;
load data local infile '/home/pld/353/company/project.text' into table project;
load data local infile '/home/pld/353/company/works_on.text' into table works_on;
load data local infile '/home/pld/353/company/dependent.text' into table dependent;

alter table employee ADD foreign key (dno) references department(dnumber);
set foreign_key_checks=1;