Requirements:

1. Use ALTER TABLE statements to
   1. specify PK and FK in each table, and
   2. specify update operation options so that, if you delete/update an employees unique identifier, all his or her records of working on any projects are also deleted/updated, AND all his or her dependents record should reflect the change as well.
2. Come up with a few queries to test that the constraints have been implemented. You should have at least four tests:
   1. test that entity integrity constraint is in place after you specify PK
   2. test that referential integrity constraint is in place after you specify FK
   3. test that a delete operation is working as desired
   4. test that an modify option is working as desired

Deliverables:

• Use comment syntax /*comment*/, or # to label where one part ends and the other part starts.

SET sql_mode = ''; SET SQL_SAFE_UPDATES = 0;

DROP SCHEMA IF EXISTS company; CREATE SCHEMA company CHARACTER SET utf8 COLLATE utf8_unicode_ci; USE company;

CREATE TABLE employee ( fname varchar(15), minit varchar(1), lname varchar(15), ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), superssn char(9), dno integer );

CREATE TABLE department ( dname varchar(25), dnumber integer, mgrssn char(9), mgrstartdate date );

CREATE TABLE dept_locations ( dnumber integer, dlocation varchar(15) );

CREATE TABLE project ( pname varchar(25), pnumber integer, plocation varchar(15), dnum integer );

CREATE TABLE works_on ( essn char(10), pnumber integer, hours numeric(4,1) );

CREATE TABLE dependent ( essn char(10), dependent_name varchar(15), sex char, bdate date, relationship varchar(8) );

LOAD DATA INFILE '/usr/local/mysql/import/company/department.csv' INTO TABLE department FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';

LOAD DATA INFILE '/usr/local/mysql/import/company/employee.csv' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' (fname, minit, lname, ssn, bdate, address, sex, salary, @superssn, dno) SET superssn = nullif(@superssn,'');

LOAD DATA INFILE '/usr/local/mysql/import/company/dept_locations.csv' INTO TABLE dept_locations FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';

LOAD DATA INFILE '/usr/local/mysql/import/company/project.csv' INTO TABLE project FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';

LOAD DATA INFILE '/usr/local/mysql/import/company/works_on.csv' INTO TABLE works_on FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ' IGNORE 0 ROWS (essn, pnumber, @hours) SET hours = nullif(@hours,'');

LOAD DATA INFILE '/usr/local/mysql/import/company/dependent.csv' INTO TABLE dependent FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';

UPDATE works_on SET hours = NULL WHERE essn = 888665555;

select * from works_on;

ALTER TABLE department ADD PRIMARY KEY (dnumber); ALTER TABLE employee ADD PRIMARY KEY (ssn); ALTER TABLE project ADD PRIMARY KEY (pnumber);