Chapter 3: The Relational Data Model and Relational Database Constraints 3.11 - Suppose each of the following Update operations is applied directly to the database ofFigure 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', '21-JUN-42', '2365 Newcastle Rd,Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE.(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.(d) Insert < '677678989', null, '40.0' > into WORKS_ON.(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > 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 '01-OCT-88', 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' andPNO= 10 to '5.0'.Answers:(a) No constraint violations.(b) Violates referential integrity because DNUM=2 and there is no tuple in the DEPARTMENT relation with DNUMBER=2. We may enforce the constraint by: (i) rejecting the insertion of the new PROJECT tuple, (ii) changing the value of DNUM in the newPROJECT tuple to an existing DNUMBER value in the DEPARTMENT relation, or (iii)inserting a new DEPARTMENT tuple with DNUMBER=2.(c) Violates both the key constraint and referential integrity. Violates the key constraintbecause there already exists a DEPARTMENT tuple with DNUMBER=4. We may enforcethis constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBERin the new DEPARTMENT tuple to a value that does not violate the key constraint. Violatesreferential integrity because MGRSSN='943775543' and there is no tuple in theEMPLOYEE relation with SSN='943775543'. We may enforce the constraint by: (i)rejecting the insertion, (ii) changing the value of MGRSSN to an existing SSN value inEMPLOYEE, or (iii) inserting a new EMPLOYEE tuple with SSN='943775543'.(d) Violates both the entity integrity and referential integrity. Violates entity integritybecause PNO, which is part of the primary key of WORKS_ON, is null. We may enforcethis constraint by: (i) rejecting the insertion, or (ii) changing the value of PNO in thenew WORKS_ON tuple to a value of PNUMBER that exists in the PROJECT relation.Violates referential integrity because ESSN='677678989' and there is no tuple in theEMPLOYEE relation with SSN='677678989'. We may enforce the constraint by: (i)Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.1Chapter 3: The Relational Data Model and Relational Database Constraints rejecting the insertion, (ii) changing the value of ESSN to an existing SSN value inEMPLOYEE, or (iii) inserting a new EMPLOYEE tuple with SSN='677678989'.(e) No constraint violations.(f) No constraint violations.(g) Violates referential integrity because several tuples exist in the WORKS_ON,DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference the tuple beingdeleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or(ii) deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEErelations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equalto'987654321'.(h) Violates referential integrity because two tuples exist in the WORKS_ON relations thatreference the tuple being deleted from PROJECT. We may enforce the constraint by: (i)rejecting the deletion, or (ii) deleting the tuples in the WORKS_ON relation whose valuefor PNO=1 (the value for the primary key PNUMBER for the tuple being deleted fromPROJECT).(i) No constraint violations.(j) Violates referential integrity because the new value of SUPERSSN='943775543' andthere is no tuple in the EMPLOYEE relation with SSN='943775543'. We may enforce theconstraint by: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple withSSN='943775543'.(k) No constraint violations.3.13 - Consider the relation CLASS(Course#, Univ_Section#, InstructorName, Semester, BuildingCode, Room#, TimePeriod, Weekdays, CreditHours). This represents classes taughtin a university with unique Univ_Section#. Give what you think should be various candidate keys and write in your own words under what constraints each candidate key would be valid.Answer:Possible candidate keys include the following (Note: We assume that the values of theSemester attribute include the year; for example "Spring/94" or "Fall/93" could bevalues for Semester):1. {Semester, BuildingCode, Room#, TimePeriod, Weekdays} if the same room cannot beused at the same time by more than one course during a particular semester.2. {Univ_Section#} if it is unique across all semesters.3. {InstructorName, Semester} if an instructor can teach at most one course during eachsemester.4. If Univ_Section# is not unique, which is the case in many universities, we have toexamine the rules that the university uses for section numbering. For example, if thesections of a particular course during a particular semester are numbered 1, 2, 3, ...,then a candidate key would be {Course#, Univ_Section#, Semester}. If, on the otherhand, all sections (of any course) have unique numbers during a particular semesteronly, then the candidate key would be {Univ_Section#, Semester}.3.14 - Consider the following six relations for an order-processing database application in a company:Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley.2Chapter 3: The Relational Data Model and Relational Database Constraints CUSTOMER (Cust#, Cname, City)ORDER (Order#, Odate, Cust#, Ord_Amt)ORDER_ITEM (Order#, Item#, Qty)ITEM (Item#, Unit_price)SHIPMENT (Order#, Warehouse#, Ship_date)WAREHOUSE (Warehouse#, City)Here, 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 (or part of an order) is shipped from the warehouse. Assume that an order can be shipped from several warehouses. Specify the foreign keys for this schema, stating any assumptions you make. What other constraints can you think of for this database?Answer:Strictly speaking, a foreign key is a set of attributes, but when that set contains only oneattribute, then that attribute itself
View Full Document
Unlocking...