Figure 14.2 (book): Sample database state for a simplified COMPANY relation DB
What (i) update, (ii) delete and (ii) insertion anomalies occur in the DEPARTMENT_PROJECT relation obtained by doing a cross product (used for natural joins but excluding project.Dinum) of the two relations DEPARTMENT and PROJECT of Fig 14.2 on page 463 of book? Explain with examples using this database, the DEPARTMENT_PROJECT relation schema and the functional dependencies with a state given below as Figures 4.1 and 4.2 EMPLOYEE Ename Bdate Address Dnumber Smith, John B Wong, Franklin T. 333445555 1955-12-08 638 Voss, Houston, TX Zelaya, Alicia J. Wallace, Jennifer S. 987654321 1941-06-20 291Berry, Bellaire, TX Narayan, Ramesh K. 6668844441962-09-15 975 Fire Oak, Humble, TX English, Joyce A.453453453 1972-07-31 5631 Rice, Houston, TX Jabbar, Ahmad V 9879879871969-03-29 980 Dallas, Houston, TX Borg, James E. DEPARTMENT 123456789 1965-01-09 731 Fondren, Houston, TX 9998877771968-07-19 3321 Castle, Spring, TX 4 4 888665555 1937-11-10 450 Stone, Houston, TX DEPT LOCATIONS Dnumber Dmgr_ssn Dnumber Dlocation Dname Research Administration Headquarters 333445555 987654321 888665555 Houston Stafford Bellaire Sugarland Houston 4 4 WORKS ON PROJECT Pnumber 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 Pname Pnumber Plocation Dnum ProductX ProductY ProductZ Computerization 10 Reorganization Newbenefits Bellaire Sugarland 5 Houston Stafford Houston Stafford 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 5 3 20 30 4 3 20 30 30 30 20 20 Fig 4.1: DEPARTMENT PROJECT DB schema suffering from update anomalies Dnumber Pnumber Dname Dmgr ssnPname Plocation FD1 FD2 FD3 Fig 4.2: A database state of the DEPARTMENT PROJECT DATABASE derived from Fig 14.2 DNUMBER PNUMBER DNAME DMGR SSN PNAME PLOCATION Research 333445555 Productz Houston Research 333445555 Computerize Stafford Research 333445555 Reorganize Houston Research 333445555 Nbenefits Stafford Research 333445555 ProductxBellair Research 333445555ProductY Sugarland Administration 987654321 ProductZHouston Administration 987654321 Computerize Stafford Administration 987654321 Reorganize Houston Administration 987654321 Nbenefits Stafford Administration 987654321 ProductX Bellair Administration 987654321ProductY Sugarland Headquarters 888665555ProductZ Houston Headquarters 888665555 Computerize Stafford Headquarters 888665555 Reorganize Houston Headquarters 888665555Nbenefits Stafford Headquarters 888665555 ProductX Bellair Headquarters 888665555 ProductY Sugarland 10 20 30 4 4 4 10 20 30 4 4 2 20 30 18 rows selected
