1. Suppose each of the following update operations is applied directly to the database

of Figure 5.6. Discuss all integrity constraints violated by each operation, if any.

(a) Insert

Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.

(b) Insert into PROJECT.

(c) Insert into DEPARTMENT.

(d) Insert into WORKS_ON.

(e) Insert into DEPENDENT.

(f) Delete the WORKS_ON tuples with ESSN= '333445555'.

(g) Delete the EMPLOYEE tuple with SSN= '987654321'.

(h) Delete the PROJECT tuple with PNAME= 'ProductX'.

(i) Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER=5 to '123456789' and '1988-10-01', respectively.

(j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to

'943775543'.

(k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and

PNO= 10 to '5.0'.

What constraints do questions A-K violate?

One possible database state for the COMPANY relational database schema. Dno 5 5 4 EMPLOYEE Fname Minit John B Franklin T Alicia J Jennifer S Ramesh Joyce A Ahmad V James E Lname Ssn Bdate Address Sex Salary Super_ssn Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Narayan666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 4 5 5 4 1 Dnumber Mgr_ssn DEPARTMENT Dname Research Administration Headquarters 5 333445555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 4 987654321 DEPT_LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston 1 888665555 WORKS ON Essn 123456789 Pno Hours Dnum Pnumber 1 1 32.5 5 123456789 2 7.5 2 5 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Newbenefits Plocation Bellaire Sugarland Houston Stafford Houston 666884444 3 40.0 3 5 453453453 1 20.0 10 4 453453453 2 20.0 20 1 333445555 2 10.0 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Sex Bdate 999887777 30 30.0 333445555 F 1986-04-05 999887777 10 10.0 333445555 M 987987987 10 35.0 333445555 F Dependent_name Alice Theodore Joy Abner Michael Alice Elizabeth 1983-10-25 1958-05-03 1942-02-28 Relationship Daughter Son Spouse Spouse Son Daughter Spouse 987987987 30 5.0 987654321 M 987654321 30 20.0 123456789 M 1988-01-04 987654321 15.0 123456789 F 1988-12-30 888665555 20 NULL 123456789 F 1967-05-05