
/* DEPARTMENT */
create table department (
	dname char(20), 
	dnumber numeric primary key,
	mgrssn numeric references employee (ssn), 
	mgrstartdate smalldatetime);
go

/* DEPT_LOCATIONS */
create table dept_locations (
	dnumber numeric not null references department (dnumber),
	dlocation char(15) not null)
go
alter table dept_locations add primary key (dnumber, dlocation)
go

/* PROJECT */
create table project (
	pname char(20), 
	pnumber numeric primary key,
	plocation char(15), 
	dnum numeric)
go
alter table project add foreign key (dnum, plocation) references dept_locations (dnumber, dlocation)
go

/* WORKS_ON */
create table works_on (
	essn numeric not null references employee (ssn), 
	pno numeric not null references project (pnumber), 
	hours numeric(18,1))
go
alter table works_on add primary key (essn, pno)
go

/* DEPENDENT */
create table dependent (
	essn numeric not null references employee (ssn),
	dependent_name char(10) not null, 
	sex char, 
	bdate smalldatetime, 
	relationship char(10))
go
alter table dependent add primary key (essn, dependent_name)
go

/* add all departments */
insert into department values ('Research', 5, 333445555, '22-MAY-1978')
insert into department values ('Administration', 4, 987654321, '01-JAN-1985')
insert into department values ('Headquarters', 1, 888665555, '19-JUN-1971')
go

/* add all dept_locations */
insert into dept_locations values (1, 'Houston')
insert into dept_locations values (4, 'Stafford')
insert into dept_locations values (5, 'Houston')
insert into dept_locations values (5, 'Bellaire')
insert into dept_locations values (5, 'Sugarland')
go

/* add all projects */
insert into project values ('ProductX', 1, 'Bellaire', 5)
insert into project values ('ProductY', 2, 'Sugarland', 5)
insert into project values ('ProductZ', 3, 'Houston', 5)
insert into project values ('Computerization', 10, 'Stafford', 4)
insert into project values ('Reorganization', 20, 'Houston', 1)
insert into project values ('Newbenefits', 30, 'Stafford', 4)
go

/* add all works_on tuples */
insert into works_on values (123456789, 1, 32.5)
insert into works_on values (123456789, 2, 7.5)
insert into works_on values (666884444, 3, 40)
insert into works_on values (453453453, 1, 20)
insert into works_on values (453453453, 2, 20)
insert into works_on values (333445555, 2, 10)
insert into works_on values (333445555, 3, 10)
insert into works_on values (333445555, 10, 10)
insert into works_on values (333445555, 20, 10)
insert into works_on values (999887777, 30, 30)
insert into works_on values (999887777, 10, 10)
insert into works_on values (987987987, 10, 35)
insert into works_on values (987987987, 30, 5)
insert into works_on values (987654321, 30, 20)
insert into works_on values (987654321, 20, 15)
go

/* add all dependents */
insert into dependent values (333445555, 'Alice', 'F', '05-APR-1976',
	'DAUGHTER')

insert into dependent values (333445555, 'Theodore', 'M', '25-OCT-1973',
	'SON')

insert into dependent values (333445555, 'Joy', 'F', '03-MAY-1948',
	'SPOUSE')

insert into dependent values (987654321, 'Abner', 'M', '29-FEB-1932',
	'SPOUSE')

insert into dependent values (123456789, 'Michael', 'M', '01-JAN-1978',
	'SON')

insert into dependent values (123456789, 'Alice', 'F', '31-DEC-1978',
	'DAUGHTER')

insert into dependent values (123456789, 'Elizabeth', 'F', '05-MAY-1951',
	'SPOUSE')
go
