Question
Requirements: Use ALTER TABLE statements to specify PK and FK in each table, and specify update operation options so that, if you delete/update an employees
Requirements:
- Use ALTER TABLE statements to
- specify PK and FK in each table, and
- 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.
- Come up with a few queries to test that the constraints have been implemented. You should have at least four tests:
- test that entity integrity constraint is in place after you specify PK
- test that referential integrity constraint is in place after you specify FK
- test that a delete operation is working as desired
- 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);
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started