CSE 5330/7330Activity – Sept 11, 2014 - Solution3.11 Suppose that each of the following Update operations is applied directly to the database state shown in Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints.a. Insert <Robert, 'F', 'Scott', '943775543', '1972-06-21', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555',1> into EMPLOYEE. Okayb. Insert <'ProductA', 4, 'Bellaire', 2> into PROJECT. Dnum 2 does not exist in DEPENDENT as required by the foreign key constraint.c. Insert <'Production', 4, '943775543', '2007-10-01'> into DEPARTMENT. Dnumber 4 already exists in table, this would cause a duplicate, so the row not addedd. Insert <'677678989', NULL, '40.0'> into WORKS_ON. The primary key is a composite of Essn and Pno, null values are not allowed in keyse. Insert <'453453453', 'John', 'M', '1990-12-12', 'spouse'> into DEPENDENT. Okayf. Delete the WORKS_ON tuples with Essn = '333445555'. Okay, deletes 4 rowsg. Delete the EMPLOYEE tuple with Ssn = '987654321'. This starts a cascading action, also need to delete rows in DEPENDENT, delete the row in DEPARTMENT, which would really cause other problems with deletes in all tables since this person is a department manager! Delete option could be: cascade, do nothing, or set null. h. Delete the PROJECT tuple with Pname = 'ProductX'. Deleting a project, should cascade to the WORKS_ON table to enforce the foreign key reference.i. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to '123456789' and '2007-10-01', respectively. Okayj. Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn ='999887777' to '943775543'.Okay, this is using the data that was inserted in the first insert above. k. Modify the Hours attribute of the WORKS_ON tuple with Essn = '999887777' and Pno = 10 to '5.0'.Okay, example of using a composite key.create table EMPLOYEE ( Fname char (20), Minit char (1), Lname char (20), SSN char (9) primary key, Bdate char(10), Address Char (40), Sex Char (1), Salary integer, Super_ssn char(9), Dno integer, foreign key (dno) references Department (Dnumber ) deferrable initially deferred, foreign key (super_ssn) references Employee (ssn) deferrable initiallydeferred );create table Department ( Dname char (20), Dnumber integer primary key, Mgr_ssn char(9), mgr_start_date char(10), foreign key (mgr_ssn) references employee (SSN) );Create table Dept_Locations ( Dnumber integer, Dlocation char (20), primary key (dnumber, dlocation), foreign key (dnumber) references Department (dnumber) );Create table Project ( Pname char (20), Pnumber integer primary key, Plocation char(20), Dnum integer, foreign key (dnum) references Department(Dnumber) );Create table works_on ( Essn char (9), Pno integer, hours float, primary key (Essn, Pno), foreign key (essn) references Employee (ssn), foreign key (pno) references Project (pnumber) );Create table Dependent ( Essn char (9), Dependent_Name char(20), Sex Char(1), Bdate char(10), Relationship char(10), check (relationship="Son" or relationship="Daughter" or relationship="Spouse"), 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","5631 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 (5,"Houston");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 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","1941-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");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);insert into WORKS_ON values ("453453453",1,20);insert into WORKS_ON values ("453453453",2,20);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 ("999887777",30,30);insert into WORKS_ON values ("999887777",10,10);insert into WORKS_ON values ("987987987",10,35);insert into WORKS_ON values ("987987987",30,5);insert into WORKS_ON values ("987654321",30,20);insert into WORKS_ON values ("987654321",20,15);insert into WORKS_ON values
View Full Document
Unlocking...