Assignment2a[1] - Pages 166-70: Exercises 5.11 Suppose that...

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

View Full Document Right Arrow Icon
Pages 166-70: Exercises 5.11 Suppose that each of the following update operations is applied directly to the database state shown in Figure 5.6. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints. a. None. b. This is a referential integrity violation because Dnum=2 doesn’t exist in the DEPARTMENT table relation with Dnumber=2. The constraint can be enforced to not allow a DNUM to be inserted that doesn’t exist in DEPARTMENT or inserting a Dnumber=2 into DEPARTMENT first. c. There is a referential integrity violation and a key constraint. DEPARTMENT table already has a Dnumber=4. The referential integrity violation happens because there isn’t an existing SSN in the EMPLOYEE table. The constraint can be enforced by not allowing the insertion or using a different number for Dnumber. As for the SSN violation, creating a tuple in the EMPLOYEE table for the SSN first would avoid the error. d. There is a referential integrity and an entity integrity violation. PNO is a primary key and can’t be NULL which is the entity violation. To enforce this we can reject the insertion or insert an existing PNO. Because ESSN=677678989 doesn’t’ exist in the EMPLOYEE table, this is a referential integrity violation. To enforce this we can reject the insertion or insert an existing value of ESSN from the EMPLOYEE table. e. None. f. None. g. This is a referential integrity violation because several tuples are referenced in other tables including EMPLOYEE, DEPARTMENT, WORKS_ON, and DEPENDENT relations. To enforce this we can reject the reject the deletion or delete the tuples from all the other relations first that are equal to 987654321.
Background image of page 1

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

View Full DocumentRight Arrow Icon
h. This is a referential integrity violation because there are two tuples that exist in the WORKS_ON table. To enforce this we can reject the reject the deletion or delete the tuples from WORKS_ON relation. i.
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.

Page1 / 10

Assignment2a[1] - Pages 166-70: Exercises 5.11 Suppose that...

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