Elm04_05 - Chapter 5: The Relational Data Model and...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Chapter 5: The Relational Data Model and Relational Database Constraints CHAPTER 5: THE RELATIONAL DATA MODEL AND RELATIONAL DATABASE CONSTRAINTS Answers to Selected Exercises 5.10 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, 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' and PNO= 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 new PROJECT 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 constraint because there already exists a DEPARTMENT tuple with DNUMBER=4. We may enforce this constraint by: (i) rejecting the insertion, or (ii) changing the value of DNUMBER in the new DEPARTMENT tuple to a value that does not violate the key constraint. Violates referential integrity because MGRSSN='943775543' and there is no tuple in the EMPLOYEE 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 in EMPLOYEE, or (iii) inserting a new EMPLOYEE tuple with SSN='943775543'. Pre-Publication Material: This is draft manuscript yet to be copy edited or paged. Copyright AWL2004 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Chapter 5: The Relational Data Model and Relational Database Constraints (d) Violates both the entity integrity and referential integrity. Violates entity integrity because PNO, which is part of the primary key of WORKS_ON, is null. We may enforce this constraint by: (i) rejecting the insertion, or (ii) changing the value of PNO in the new 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 the
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 03/21/2010 for the course CS 145 taught by Professor Markjan during the Spring '10 term at Abilene Christian University.

Page1 / 7

Elm04_05 - Chapter 5: The Relational Data Model and...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online