How do I write instructions as SQL code or make the changes as requested?

(b) Violates referential integrity because DNUM=2 and there is no tuple in the DEPARTMENT relation with DNUMBER=2. We may enforce the constraint by: (i) rejecting the insertion of the new PROJECT tuple, (ii) changing the value of DNUM in the new PROJECT tuple to an existing DNUMBER value in the DEPARTMENT relation, or (iii) inserting a new DEPARTMENT tuple with DNUMBER=2.

Violates referential integrity because several tuples exist in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference the tuple being deleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or (ii) deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equal to987654321.

Violates referential integrity because two tuples exist in the WORKS_ON relations that reference the tuple being deleted from PROJECT. We may enforce the constraint by: (i) rejecting the deletion, or (ii) deleting the tuples in the WORKS_ON relation whose value for PNO=1 (the value for the primary key NUMBER for the tuple being deleted from PROJECT). Please see current SQL CODE you can edit to write code and solve for above. create schema COMPANYRR; use COMPANYRR; SET foreign_key_checks = 0; DROP TABLE IF EXISTS employee; DROP TABLE IF EXISTS department; DROP TABLE IF EXISTS dept_locations; DROP TABLE IF EXISTS project; DROP TABLE IF EXISTS works_on; DROP TABLE IF EXISTS dependent; SET foreign_key_checks = 1;

create database COMPANY; use COMPANY;

CREATE TABLE EMPLOYEE ( fname varchar(15) DEFAULT NULL, minit varchar(1) DEFAULT NULL, lname varchar(15) DEFAULT NULL, ssn char(9) NOT NULL, bdate date DEFAULT NULL, address varchar(50) DEFAULT NULL, sex char DEFAULT NULL, salary decimal(10,2) DEFAULT NULL, superssn char(9) DEFAULT NULL, dno int(4) DEFAULT NULL, PRIMARY KEY (ssn)); INSERT INTO Employee VALUES ('John','B','Smith','123456789','1955-01-09','731 Fondren, Houston, TX','M',30000,'333445555',5); INSERT INTO Employee VALUES ('Alicia','J','Zelaya','999887777','1958-07-19','3321 Castle, Spring, TX','F',25000,'987654321',4); INSERT INTO Employee VALUES ('Ramesh','K','Narayan','666884444','1952-09-15','971 Fire Oak, Humble, TX','M',38000,'333445555',5); INSERT INTO Employee VALUES ('Joyce','A','English','453453453','1983-07-31','5631 Rice Oak, Houston, TX','F',25000,'333445555',5); INSERT INTO Employee VALUES ('Ahmad','V','Jabbar','987987987','1979-03-29','980 Dallas, Houston, TX','M',25000,'987654321',4); INSERT INTO Employee VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5); INSERT INTO Employee VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4); INSERT INTO Employee VALUES ('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1); ALTER TABLE Employee ADD ( FOREIGN KEY (dno) REFERENCES department(dnumber), FOREIGN KEY (superssn) REFERENCES employee(ssn) );

SELECT * FROM EMPLOYEE;

CREATE TABLE DEPARTMENT ( dname varchar(25) DEFAULT NULL, dnumber int(4) NOT NULL, mgrssn char(9) DEFAULT NULL, mgrstartdate date DEFAULT NULL, PRIMARY KEY (dnumber), FOREIGN KEY (mgrssn) REFERENCES employee(ssn)); INSERT INTO Department VALUES ('Research', 5, '333445555', '1978-05-22'); INSERT INTO Department VALUES ('Administration', 4, '987654321', '1985-01-01'); INSERT INTO Department VALUES ('Headquarters', 1, '888665555', '1971-06-19');

SELECT * FROM DEPARTMENT;

CREATE TABLE DEPT_LOCATIONS ( dnumber int(4) NOT NULL, dlocation varchar(15) NOT NULL, PRIMARY KEY (dnumber, dlocation), FOREIGN KEY (dnumber) REFERENCES department(dnumber));

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 (5,'Houston');

SELECT * FROM DEPT_LOCATIONS;

CREATE TABLE WORKS_ON ( essn char(9) NOT NULL, pno int(4) NOT NULL, hours decimal(4,1) DEFAULT NULL, PRIMARY KEY (essn, pno), FOREIGN KEY (essn) REFERENCES employee(ssn), FOREIGN KEY (pno) REFERENCES project(pnumber));

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);

SELECT * FROM WORKS_ON;

CREATE TABLE PROJECT ( pname varchar(25) DEFAULT NULL, pnumber int(4) NOT NULL, plocation varchar(15) DEFAULT NULL, dnum int(4) NOT NULL, PRIMARY KEY (pnumber), FOREIGN KEY (dnum) REFERENCES department(dnumber));

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);

SELECT * FROM PROJECT;

CREATE TABLE DEPENDENT( essn char(9) NOT NULL, dependent_name varchar(15) NOT NULL, sex char DEFAULT NULL, bdate date DEFAULT NULL, relationship varchar(8) DEFAULT NULL, PRIMARY KEY (essn, dependent_name), FOREIGN KEY (essn) REFERENCES employee(ssn));

INSERT INTO Dependent VALUES ('333445555','Alice','F','1996-03-01','Daughter'); INSERT INTO Dependent VALUES ('333445555','Theodore','M','1993-05-12','Son'); INSERT INTO Dependent VALUES ('333445555','Joy','F','1970-07-08','Spouse'); INSERT INTO Dependent VALUES ('987654321','Abner','M','1958-02-25','Spouse'); INSERT INTO Dependent VALUES ('123456789','Michael','M','1978-01-01','Son'); INSERT INTO Dependent VALUES ('123456789','Alice','F', '1978-12-08','Daughter'); INSERT INTO Dependent VALUES ('123456789','Elizabeth','F','1957-05-07','Spouse');

SELECT * FROM DEPENDENT;

INSERT INTO EMPLOYEE VALUES('Robert', 'F', 'Scott', '943775543', '1972-06-21', '2365 Newcastle Rd, Bellaire, TX', 'M', '58000', '888665555', '1'); SELECT * FROM EMPLOYEE;

INSERT INTO PROJECT VALUES('ProductA', 4, 'Bellaire', 2); SELECT * FROM PROJECT;

INSERT INTO DEPENDENT VALUES('453453453','John' ,'M', '1970-12-12', 'Spouse'); SELECT * FROM DEPENDENT;

select * from PROJECT where PNAME = 'ProductX'; delete from PROJECT where PNAME = 'ProductX'; select * from PROJECT where PNAME = 'ProductX';