CREATE DATABASE COMPANY;
USE COMPANY;

CREATE TABLE EMPLOYEE (
  FNAME		VARCHAR(15)	NOT NULL,
  MINIT		CHAR, 
  LNAME		VARCHAR(15)	NOT NULL,
  SSN		CHAR(9)		NOT NULL,
  BDATE		DATE,
  ADDRESS	VARCHAR(30),
  SEX		CHAR,
  SALARY	DECIMAL(10,2),
  SUPERSSN	CHAR(9),
  DNO		INT		NOT NULL,
  PRIMARY KEY (SSN),
  FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN),
  FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
);

CREATE TABLE DEPARTMENT (
  DNAME		VARCHAR(15)	NOT NULL,
  DNUMBER	INT		NOT NULL,
  MGRSSN	CHAR(9)		NOT NULL,
  MGRSTARTDATE	DATE,
  PRIMARY	KEY (DNUMBER),
  UNIQUE(DNAME),
  FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
);

CREATE TABLE DEPT_LOCATIONS (
  DNUMBER	INT		NOT NULL,
  DLOCATION	VARCHAR(15)	NOT NULL,
  PRIMARY KEY (DNUMBER, DLOCATION),
  FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)
);

CREATE TABLE PROJECT (
  PNAME		VARCHAR(15)	NOT NULL,
  PNUMBER	INT		NOT NULL,
  PLOCATION	VARCHAR(15),
  DNUM		INT		NOT NULL,
  PRIMARY KEY (PNUMBER),
  UNIQUE(PNAME),
  FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER)
);

CREATE TABLE WORKS_ON (
  ESSN	CHAR(9)		NOT NULL,
  PNO	INT		NOT NULL,
  HOURS	DECIMAL(3,1)	NOT NULL,
  PRIMARY KEY(ESSN, PNO),
  FOREIGN KEY(ESSN) REFERENCES EMPLOYEE(SSN),
  FOREIGN KEY(PNO) REFERENCES PROJECT(PNUMBER)
);

CREATE TABLE DEPENDENT (
  ESSN			CHAR(9)		NOT NULL,
  DEPENDENT_NAME	VARCHAR(15)	NOT NULL,
  SEX			CHAR,
  BDATE			DATE,
  RELATIONSHIP		VARCHAR(8),
  PRIMARY KEY (ESSN, DEPENDENT_NAME),
  FOREIGN KEY(ESSN) REFERENCES EMPLOYEE(SSN)
);

INSERT INTO EMPLOYEE VALUES
('John', 'B', 'Smith', '123456789', '1965-01-09', 
'731 fondren, Houston, TX', 'M', 30000, '333445555', 5);
INSERT INTO EMPLOYEE VALUES
('Franklin', 'T', 'Wong', '333445555', '1955-12-08', 
'638 Voss, Houston, TX', 'M', 40000, '888665555', 5);
INSERT INTO EMPLOYEE VALUES
('Alicia', 'J', 'Zelaya', '999887777', '1968-01-19',
'3321 Castle, Spring, TX', 'F', 25000, '987654321', 4);
INSERT INTO EMPLOYEE VALUES
('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', 
'291 Berry, Bellaire, TX', 'F', 43000, '888665555', 4);
INSERT INTO EMPLOYEE VALUES
('Ramesh', 'K', 'Narayan', '666884444', '1962-09-15', 
'975, Fire Oak, Humble, TX', 'M', 38000, '333445555', 5);
INSERT INTO EMPLOYEE VALUES
('Joyce', 'A', 'English', '453453453', '1972-07-31', 
'6531 Rice, Houston, TX', 'F', 25000, '333445555', 5);
INSERT INTO EMPLOYEE VALUES
('Ahmad', 'V', 'Jabbar', '987987987', '1969-03-29', 
'980 Dallas, Houston, TX', 'M', 25000, '987654321', 4);
INSERT INTO EMPLOYEE VALUES
('James', 'E', 'Borg', '888665555', '1937-11-10', 
'450 Stone, Houston, TX', 'M', 55000, null, 1);

INSERT INTO DEPARTMENT VALUES ('Research', 5, '333445555', '1988-05-22');
INSERT INTO DEPARTMENT VALUES ('Administration', 4, '987654321', '1995-01-01');
INSERT INTO DEPARTMENT VALUES ('Headquarters', 1, '888665555', '1981-06-19');

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 DEPT_LOCATIONS VALUES (null, 'Houston');

INSERT INTO PROJECT VALUES ('ProductX', 1, 'Bellaire', 5);
INSERT INTO PROJECT VALUES ('ProductY', 2, 'Sugarland', 5);
INSERT INTO PROJECT VALUES ('ProductZ', 3, 'Houston', 4);
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', '1986-04-05', 'DAUGHTER');
INSERT INTO DEPENDENT VALUES ('333445555', 'Theodore', 'M', '1983-10-25', 'SON');
INSERT INTO DEPENDENT VALUES ('333445555', 'Joy', 'F', '1958-05-03', 'SPOUSE');
INSERT INTO DEPENDENT VALUES ('987654321', 'Abner', 'M', '1942-02-28', 'SPOUSE');
INSERT INTO DEPENDENT VALUES ('123456789', 'Michael', 'M', '1988-01-04', 'SON');
INSERT INTO DEPENDENT VALUES ('123456789', 'Alice', 'F', '1988-12-30', 'DAUGHTER');
INSERT INTO DEPENDENT VALUES ('123456789', 'Elizabeth', 'F', '1967-05-05', 'SPOUSE');