

create table Project (
PName varchar(20) not null,
PNumber int not null,
PLocation varchar(20) not null,
DNum int not null,
primary key (PNumber),
foreign key (DNum) references Department);

create table Works_On (
ESSN int not null,
PNo int not null,
Hours decimal(3, 1),
primary key(ESSN, PNo),
foreign key (ESSN) references Employee,
foreign key (PNo) references Project);

create table Dependent (
ESSN int not null,
Dependent_Name varchar(20) not null,
Sex char(1) not null,
Check (Sex in ('M', 'F')),
BDate date not null,
Relationship varchar(20) not null,
primary key(ESSN, Dependent_Name),
foreign key (ESSN) references Employee);

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);

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.0 );
insert into Works_On values (453453453, 1, 20.0 );
insert into Works_On values (453453453, 2, 20.0 );
insert into Works_On values (333445555, 2, 10.0 );
insert into Works_On values (333445555, 3, 10.0 );
insert into Works_On values (333445555, 10, 10.0 );
insert into Works_On values (333445555, 20, 10.0 );
insert into Works_On values (999887777, 30, 30.0 );
insert into Works_On values (999887777, 10, 10.0 );
insert into Works_On values (987987987, 10, 35.0 );
insert into Works_On values (987987987, 30, 5.0 );
insert into Works_On values (987654321, 30, 20.0 );
insert into Works_On values (987654321, 20, 15.0 );
insert into Works_On values (888665555, 20, null );

insert into Dependent values (333445555, 'Alice', 'F', to_date('05-APR-1976', 'DD-MON-YYYY'), 'DAUGHTER');
insert into Dependent values (333445555, 'Theodore', 'M', to_date('25-OCT-1973','DD-MON-YYYY'), 'SON');
insert into Dependent values (333445555, 'Joy', 'F', to_date('03-MAY-1948','DD-MON-YYYY'), 'SPOUSE');
insert into Dependent values (987654321, 'Abner', 'M', to_date('29-FEB-1932','DD-MON-YYYY'), 'SPOUSE');
insert into Dependent values (123456789, 'Michael', 'M', to_date('01-JAN-1978','DD-MON-YYYY'), 'SON');
insert into Dependent values (123456789, 'Alice', 'F', to_date('31-DEC-1978','DD-MON-YYYY'), 'DAUGHTER');
insert into Dependent values (123456789, 'Elizabeth', 'F', to_date('05-MAY-1957','DD-MON-YYYY'), 'SPOUSE');
