create table Employee (
FName varchar(20) not null,
MInit char,
LName varchar(20) not null,
SSN int not null,
BDate date not null,
Address varchar(30) not null,
Sex char(1) not null check (Sex in ('M', 'F')),
Salary int not null,
SuperSSN int,
DNo int not null,
primary key (SSN),
foreign key (SuperSSN) references Employee);

create table Department (
DName varchar(20) not null,
DNumber int not null,
MgrSSN int not null,
MgrStartDate date not null,
primary key (DNumber),
foreign key (MgrSSN) references Employee);

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 Employee values ('James', 'E', 'Borg', 888665555, to_date('10-NOV-1927','DD-MON-YYYY'), '450 Stone, Houston, TX', 'M', 55000, Null, 1 );
insert into Employee values ('Jennifer', 'S', 'Wallace', 987654321, to_date('20-JUN-1931','DD-MON-YYYY'), '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4 );
insert into Employee values ('Franklin', 'T', 'Wong', 333445555, to_date('08-DEC-1945','DD-MON-YYYY'), '638 Voss, Houston, TX', 'M', 40000, 888665555, 5 );
insert into Employee values ('Alicia', 'J', 'Zelaya', 999887777, to_date('19-JUL-1958','DD-MON-YYYY'), '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4 );
insert into Employee values ('Ahmad', 'V', 'Jabbar', 987987987, to_date('29-MAR-1959','DD-MON-YYYY'), '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4 );
insert into Employee values ('John', 'B', 'Smith', 123456789, to_date('09-JAN-1955','DD-MON-YYYY'), '731 Fondren, Houston, TX', 'M',  30000, 333445555, 5);
insert into Employee values ('Ramesh', 'K', 'Narayan', 666884444, to_date('15-SEP-1952','DD-MON-YYYY'), '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5 );
insert into Employee values ('Joyce', 'A', 'English', 453453453, to_date('31-JUL-1962','DD-MON-YYYY'), '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5 );

insert into Department values ('Research', 5, 333445555, to_date('22-MAY-1978', 'DD-MON-YYYY') );
insert into Department values ('Administration', 4, 987654321, to_date('01-JAN-1985', 'DD-MON-YYYY') );
insert into Department values ('Headquarters', 1, 888665555, to_date('19-JUN-1971', 'DD-MON-YYYY'));

alter table Employee add constraint ValidDept foreign key (DNo) references Department;

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