CREATE TABLE Employee(
	ssn VARCHAR(25) NOT NULL,
	fname VARCHAR(25),
	minit CHAR(1),
	lname VARCHAR(50),
	address VARCHAR(50),
	sex CHAR(1),
	salary INT,
	superssn VARCHAR(50),
	dno INT,
	PRIMARY KEY (ssn)
);

CREATE TABLE Department (
	dnumber    INT NOT NULL,
	dname      VARCHAR(25) NOT NULL,
	mgrssn     VARCHAR(25),
	PRIMARY KEY (dnumber)
);

ALTER TABLE Employee
       ADD FOREIGN KEY (dno)
       REFERENCES Department (dnumber);

ALTER TABLE Department
       ADD FOREIGN KEY (mgrssn)
       REFERENCES Employee (ssn);

CREATE TABLE Dept_Locations (
	dnumber INT NOT NULL,
	dlocation VARCHAR(50) NOT NULL,
	PRIMARY KEY (dnumber, dlocation)
);

ALTER TABLE Dept_Locations
	ADD FOREIGN KEY (dnumber)
	REFERENCES Department (dnumber);

CREATE TABLE Project(
	pnumber INT NOT NULL,
	pname VARCHAR(50),
	plocation VARCHAR(50),
	dnum INT,
	PRIMARY KEY (pnumber)
);

ALTER TABLE Project 
	ADD FOREIGN KEY (dnum)
	REFERENCES Department (dnumber);

CREATE TABLE Dependent(
	essn VARCHAR(50) NOT NULL,
	dependent_name VARCHAR(50) NOT NULL,
	sex CHAR(1),
	relationship VARCHAR(50),
	PRIMARY KEY (essn, dependent_name)
);

CREATE TABLE Works_On(
	essn VARCHAR(50) NOT NULL,
	pno INT NOT NULL,
	hours INT,
	PRIMARY KEY (essn, pno)
);

CREATE TABLE Employee_Trigger_Test
	(SSN VARCHAR(50) NOT NULL );

insert into Employee values ('123456789', 'John', 'B', 'Smith', 
	'731 Fondren, Houston TX', 'M', 30000.00, '333445555', null);
insert into Employee values ('333445555', 'Franklin', 'T', 'Wong', 
	'638 Voss, Houston TX', 'M', 40000, '888665555', null);
insert into Employee values ('999887777', 'Alicia', 'J', 'Zelaya',
	'3321 Castle, Spring TX', 'F', 25000, '987654321', null);
insert into Employee values ('987654321', 'Jennifer', 'S', 'Wallace',
	'291 Berry, Bellaire, TX', 'F', 43000, '888666555', null);
insert into Employee values ('666884444', 'Ramesh', 'K', 'Narayan',
	'975 Fire Oak, Humble TX', 'M', 38000, '333445555', null);
insert into Employee values ('453453453', 'Joyce', 'A', 'English',
	'5631 Rice, Houston TX', 'F', 25000, '333445555', null);
insert into Employee values ('987987987', 'Ahmad', 'V', 'Jabbar',
	'980 Dallas, Houston TX', 'M', 25000, '987654321', null);
insert into Employee values ('888665555', 'James', 'E', 'Borg',
	'450 Stone, Houston TX', 'M', 55000, null, null);

insert into Department values (5, 'Research', '333445555');
insert into Department values (4, 'Administration', '987654321');
insert into Department values (1, 'Headquarters', '888665555');

update Employee set dno = 5 where ssn = '123456789';
update Employee set dno = 5 where ssn = '333445555';
update Employee set dno = 4 where ssn = '999887777';
update Employee set dno = 4 where ssn = '987654321';
update Employee set dno = 5 where ssn = '666884444';
update Employee set dno = 5 where ssn = '453453453';
update Employee set dno = 4 where ssn = '987987987';
update Employee set dno = 1 where ssn = '888665555';

insert into dept_locations values (1, 'Houston');
insert into dept_locations values (4, 'Stafford');
insert into dept_locations values (5, 'Bellaire');
insert into dept_locations values (5, 'Sugarland');

insert into project values (1, 'ProductX', 'Bellaire', 5);
insert into project values (2, 'ProductY', 'Sugarland', 5);
insert into project values (3, 'ProductZ', 'Houston', 5);
insert into project values (10, 'Computerization', 'Stafford', 4);
insert into project values (20, 'Reorganization', 'Houston', 1);
insert into project values (30, 'Newbenefits', 'Stafford', 4);

insert into works_on values ('123456789', 1, 32);
insert into works_on values ('123456789', 2, 7);
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);
insert into works_on values ('888665555', 20, null);

insert into dependent values ('333445555', 'Alice', 'F', 'DAUGHTER');
insert into dependent values ('333445555', 'Theodore', 'M', 'SON');
insert into dependent values ('333445555', 'Joy', 'F', 'SPOUSE');
insert into dependent values ('987654321', 'Abner', 'M', 'SPOUSE');
insert into dependent values ('123456789', 'Michael', 'M', 'SON');
insert into dependent values ('123456789', 'Alice', 'F', 'DAUGHTER');
insert into dependent values ('123456789', 'Elizabeth', 'F', 'SPOUSE');
