- Figure 5 6 One Possible Database State For The Company Relational Database Scherna Employee Frame John Franklin Minit B 1 (79.78 KiB) Viewed 86 times
Figure 5.6 One possible database state for the
COMPANY relational database scherna. EMPLOYEE Frame John Franklin Minit B T J Alicia Jennifer Lname San Bdate Address Sex Salary Super_ssn Dno Smith 123456789 1965-01-08 731 Fondren, Houston, TX M , 30000 333445555 5 Wung 333445555 1955-12-06 638 Vuss, Houston, TX M 40000 888665555 5 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Wallacc 987654321 1941-06-20 201 Berry, Bellairc, TX F 43000 888666666 Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5 English 453453453 1972-07-31 5631 Rice, Houston, TX , F 25000 333445555 5 Jabbar 987987967 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 Borg 888665555 1937-11-10 150 Stone, Houston, TX M M 55000 NULL 1 4 S K A V E Ramesh Joyce Ahmad James 4 DEPARTMENT Dnumtxar 5 Dname Research Administration Headquarters Mgr_aan 333445555 987654321 Mgr_start_date 1988-05-22 1995-01-01 1901-06-19 4 DEPT_LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 5 Houston 1 000685555 PROJECT Pne Hours Pnumber Drum WORKS ON Essn 123456789 123456789 1 32.5 1 5 2 7.5 2 5 666884444 3 40.0 Pname Produulx Product Product Computerization Reorganization Newbenefits 3 Plocation Bellaire Sugarland Houston Stafford Houston Stafford 6 1 10 4 453453453 453453453 333445555 20.0 20.0 2 20 1 2 10.0 30 4 3 10.0 100 10 20 20 DEPENDENT Eben 10.0 90 30.0 Dependent_name Alice Theodore Joy Sex F M ΠΌ 10 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 10.0 10 Relationship Daughter Son Spouse Spouse Son F 35.0 5.0 Bdate 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988 01 04 1988-12-30 1967-05-05 333445556 333445555 333445555 987654321 123456789 123456789 123456789 30 Abrer M 30 20.0 Michael M 20 15.0 Alice F Daughter Spouse 20 NULL Elizabeth F 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 < 'Robert', 'F', 'Scott', '123456789', '1942-06-21', '2365 Newcastle Rd, Bellaire, TX', M. 58000, '345678912', 1 > into EMPLOYEE. (b) Insert < 'ProductA', 4, 'Bellaire', 6 > into PROJECT. (c) Insert < 'Production', 6, '9123335555', '1988-10-01'> into DEPARTMENT. (d) Insert <123456789, null, '20.0'> into WORKS ON.
(e) Insert < '453666453', 'John', M, '1960-12-10', 'SPOUSE'> into DEPENDENT. (f) Delete the WORKS ON tuples with ESSN= '123456789'. (9) Delete the EMPLOYEE tuple with SSN= '123456789'. (h) Delete the DEPARTMENT tuple with DNAME='Research'. {I} Modify the MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER=5 to '345678912' and '1988-10-01, respectively, (j) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= '999887777' to 123456789 {k) Modify the HOURS attribute of the WORKS_ON tuple with ESSN= '999887777' and PNO= 10 to '5.0'. 2. Consider the following six relations for an order-processing database application in a
company: CUSTOMER (Cust#, Cname, City) ORDER (Order#, Odate: Custi. Ord_Amt) ORDER ITEM (Order#, Item#, Oty) ITEM (Item#, Unit_price) SHIPMENT (Order#, Warehouse# Ship_date) WAREHOUSE (Warehouse# City) Ord_amt refers to total dollar amount of an order; Odate is the date the order was placed: Ship_date is the date an order is shipped from tho warchouso. Assume that an order can be shipped from several warehouses. Draw a diagram like slide 5-16 in Chapter 5-2 showing referential integrity constraints for this schema, stating any assumptions you make.