(8) Consider the database state for the Company' database in Question #7. We execute the following operations on that database state. Discuss ALL integrity constraints violated by each operation, and the ways to enforce these constraints. (10 points) (i) Delete the EMPLOYEE tuple with Ssn = '987654321'. (ii) Modify the Hours attribute of the WORKS_ON tuple with Essn = '999887777' and Pno = 10 to '5.0' (iii) Insert into DEPARTMENT (7) EMPLOYEE Fname Minit Lname San Bdate Address Sex Salary Super_san Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Franklin I Wong 333445555 1955-12-08 638 Voss, Houston, TX M40000 888665555 Alicia J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 Joyce English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TXM 25000 987654321 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 4 010 0101 4 4 DEPARTMENT Dname Research Administration Headquarters Dnumber 5 4 Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 DEPT_LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston 1 Pno Pnumber Dnum 1 1 5 2 2 3 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Newbenefits 3 Plocation Bellaire Sugarland Houston Stafford Houston Stafford 5 1 4 10 20 30 lala 1 4 WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 3 10 20 Hours 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.0 15.0 NULL Sex F 30 10 10 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent_name Alice Theodore Joy Abner Michael Alice Elizabeth M F M 30 30 20 20 Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse M | (8) Consider the database state for the Company' database in Question #7. We execute the following operations on that database state. Discuss ALL integrity constraints violated by each operation, and the ways to enforce these constraints. (10 points) (i) Delete the EMPLOYEE tuple with Ssn = '987654321'. (ii) Modify the Hours attribute of the WORKS_ON tuple with Essn = '999887777' and Pno = 10 to '5.0' (iii) Insert into DEPARTMENT (7) EMPLOYEE Fname Minit Lname San Bdate Address Sex Salary Super_san Dno John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Franklin I Wong 333445555 1955-12-08 638 Voss, Houston, TX M40000 888665555 Alicia J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 Joyce English 453453453 1972-07-31 5631 Rice, Houston, TXF 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TXM 25000 987654321 James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 4 010 0101 4 4 DEPARTMENT Dname Research Administration Headquarters Dnumber 5 4 Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 DEPT_LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston 1 Pno Pnumber Dnum 1 1 5 2 2 3 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Newbenefits 3 Plocation Bellaire Sugarland Houston Stafford Houston Stafford 5 1 4 10 20 30 lala 1 4 WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 3 10 20 Hours 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 10.0 30.0 10.0 35.0 5.0 20.0 15.0 NULL Sex F 30 10 10 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent_name Alice Theodore Joy Abner Michael Alice Elizabeth M F M 30 30 20 20 Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse M |
