/* 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;