CREATE TABLE dept_locations (
  dnumber varchar(2) NOT NULL,
  dlocation varchar(20) NOT NULL,
  CONSTRAINT PRIMARY KEY (dnumber, dlocation),
  CONSTRAINT FOREIGN KEY (dnumber) REFERENCES department(dnumber)
);

CREATE TABLE project (
  pname varchar(20) NOT NULL,
  pnumber int(2),
  plocation varchar(20),
  dnum varchar(2),
  CONSTRAINT PRIMARY KEY (pnumber),
  CONSTRAINT UNIQUE (pname),
  CONSTRAINT FOREIGN KEY (dnum) REFERENCES department(dnumber)
);

CREATE TABLE works_on (
   essn varchar(9) NOT NULL,
   pno int(2) NOT NULL,
   hours double(5,2),
   CONSTRAINT PRIMARY KEY (essn, pno),
   CONSTRAINT FOREIGN KEY (essn) REFERENCES employee(ssn),
   CONSTRAINT FOREIGN KEY (pno) REFERENCES project(pnumber)
);

CREATE TABLE dependent (
   essn varchar(9) NOT NULL,
   dependent_name varchar(20) NOT NULL,
   sex char,
   bdate date,
   relationship varchar(10),
   CONSTRAINT PRIMARY KEY (essn, dependent_name),
   CONSTRAINT FOREIGN KEY (essn) REFERENCES employee(ssn)
);
 

ALTER TABLE employee ADD (
   CONSTRAINT FOREIGN KEY (dno) REFERENCES department(dnumber)
);

Populationg Tables
insert into department values
  ('Headquarters', 1, null, '1971-07-19');
insert into department values
  ('Administration', 4, null, '1985-01-01');
insert into department values
  ('Research', 5, null, '1978-05-22');

insert into employee values
   ('James', 'E', 'Borg', 888665555, '1927-10-10', '450 Stone, Houston, TX', 'M', 55000.00, null, 1);
insert into employee values
   ('Franklin', 'T', 'Wong', 333445555, '1945-12-08', '638 Voss, Houston, TX', 'M', 40000.00, 888665555, 5);
insert into employee values
   ('Jennifer', 'S', 'Wallace', 987654321, '1931-01-20', '291 Berry, Bellaire, TX', 'F', 43000.00, 888665555, 4);
insert into employee values
   ('Ahmad', 'V', 'Jabbar', 987987987, '1959-03-29', '980 Dallax, Houston, TX', 'M', 25000.00, 987654321, 4);
insert into employee values
   ('John', 'B', 'Smith', 123456789, '1955-01-09', '731 Fondren, Houston, TX', 'M', 30000.00, 333445555, 5);
insert into employee values
   ('Ramesh', 'K', 'Narayan', 666884444, '1952-09-15', '975 Fire Oak, Humble, TX', 'M', 38000.00, 333445555, 5);
insert into employee values
   ('Joyce', 'A', 'English', 453453453, '1962-07-31', '5631 Rice, Houston, TX', 'F', 25000.00, 333445555, 5);
insert into employee values
   ('Alicia', 'J', 'Zelaya', 999887777, '1958-07-19', '3321 Castle, Spring, TX', 'F', 25000.00, 987654321, 4);

update department
   set mgrssn = 333445555
   where dnumber = 5;

update department
   set mgrssn = 987654321
   where dnumber = 4;

update department
   set mgrssn = 888665555
   where dnumber = 1;

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
   (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
   (453453453, 1, 20);
insert into works_on values
   (453453453, 2, 20);
insert into works_on values
   (666884444, 3, 40);
insert into works_on values
   (987654321, 20, 15);
insert into works_on values
   (987654321, 30, 20);
insert into works_on values
   (987987987, 10, 35);
insert into works_on values
   (987987987, 30, 5);
insert into works_on values
   (999887777, 10, 10);
insert into works_on values
   (999887777, 30, 30);
insert into works_on values
   (888665555, 20, null);

insert into dependent values
   (123456789, 'Alice', 'F', '1978-12-31', 'Daughter');
insert into dependent values
   (123456789, 'Elizabeth', 'F', '1957-05-05', 'Spouse');
insert into dependent values
   (123456789, 'Michael', 'M', '1978-01-01', 'Son');
insert into dependent values
   (333445555, 'Alice', 'F', '1976-04-05', 'Daughter');
insert into dependent values
   (333445555, 'Joy', 'F', '1948-05-03', 'Spouse');
insert into dependent values
   (333445555, 'Theodore', 'M', '1973-09-25', 'Son');
insert into dependent values
   (987654321, 'Abner', 'M', '1932-02-29', 'Spouse');

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, 'Houston');
insert into dept_locations values
   (5, 'Sugarland');