-- $Id: elmasrinavathe_db.sql,v 1.4 2007/06/13 12:09:54 almhe Exp $
--
-- This creates the database used in the Elmasri, Navathe book.
--

create table endept_loc (dnumber int, dlocation varchar(9)) 
ENGINE=InnoDB;
insert into endept_loc values (1, 'Houston');
insert into endept_loc values (4, 'Stafford');
insert into endept_loc values (5, 'Bellaire');
insert into endept_loc values (5, 'Sugarland');
insert into endept_loc values (5, 'Houston');

alter table endept_loc add constraint pk_dept_loc primary key (dnumber, dlocation);
alter table endept_loc add constraint fk_deptloc_dept foreign key (dnumber) references endept(dnumber) on delete cascade;
create table enproj (pname varchar(15), pnumber int, 
plocation varchar(9), dnum int,
constraint pk_proj primary key (pnumber),
constraint fk_proj_deptloc foreign key (dnum, plocation) 
references endept_loc(dnumber, dlocation)) 
ENGINE=InnoDB;
insert into enproj values ('ProductX', 1, 'Bellaire', 5);
insert into enproj values ('ProductY', 2, 'Sugarland', 5);
insert into enproj values ('ProductZ', 3, 'Houston', 5);
insert into enproj values ('Computerization', 10, 'Stafford', 4);
insert into enproj values ('Reorganization', 20, 'Houston', 1);
insert into enproj values ('Newbenefits', 30, 'Stafford', 4);


create table enworks_on (
essn int,
pno int, 
hours decimal(3,1),
constraint fk_works_proj foreign key (pno) references enproj(pnumber) on delete cascade,
constraint fk_works_emp foreign key (essn) references enemp(ssn) on delete cascade,
constraint pk_workson primary key (essn, pno)) 
ENGINE=InnoDB;

insert into enworks_on values (123456789, 1, 32.5);
insert into enworks_on values (123456789, 2, 7.5);
insert into enworks_on values (666884444, 3, 40.0);
insert into enworks_on values (453453453, 1, 20);
insert into enworks_on values (453453453, 2, 20);
insert into enworks_on values (333445555, 2, 10);
insert into enworks_on values (333445555, 3, 10);
insert into enworks_on values (333445555, 10, 10);
insert into enworks_on values (333445555, 20, 10);
insert into enworks_on values (999887777, 30, 30);
insert into enworks_on values (999887777, 10, 10);
insert into enworks_on values (987987987, 10, 35);
insert into enworks_on values (987987987, 30, 5);
insert into enworks_on values (987654321, 30, 20);
insert into enworks_on values (987654321, 20, 15);
insert into enworks_on values (888665555, 20, NULL);

create table endependent (
essn int,
dependent_name varchar(9) default 'NN', 
sex varchar(1) check (sex in ('F', 'M')), 
bdate date /*default sysdate*/, 
relationship varchar(8) check (relationship in ('DAUGHTER', 'SON', 'SPOUSE')),
--constraint chk_depen_sex_relation 
--check ((relationship = 'DAUGHTER' and sex = 'F') 
--       or (relationship = 'SON' and sex = 'M') 
--       or (relationship = 'SPOUSE')),
constraint fk_depen_emp foreign key (essn) references enemp(ssn) on delete cascade,
constraint pk_dependent primary key (essn, dependent_name)) 
ENGINE=InnoDB;
insert into endependent values (333445555, 'Alice', 'F', '1986-04-05', 'DAUGHTER');
insert into endependent values (333445555, 'Theodore', 'M', '1983-10-25', 'SON');
insert into endependent values (333445555, 'Joy', 'F', '1958-05-03', 'SPOUSE');
insert into endependent values (987654321, 'Abner', 'M', '1942-02-28', 'SPOUSE');
insert into endependent values (123456789, 'Michael', 'M', '1988-01-04', 'SON');
insert into endependent values (123456789, 'Alice', 'F', '1988-12-30', 'DAUGHTER');
insert into endependent values (123456789, 'Elizabeth', 'F', '1967-05-05', 'SPOUSE');
commit;
