/* for big-company.
 */

--create table big_employee (
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)
) ;

--create table big_department (
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)
) ;


--create table big_project (
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)
) ;

--create table big_works_on (
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)
) ;

