Question

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.

1. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.
2. Insert <‘ProductA’, 4, ‘Bellaire’, 2> into PROJECT.
3. Insert <‘677678989’, NULL, ‘40.0’> into WORKS_ON.
4. Delete the WORKS_ON tuples with Essn = ‘333445555’.
5. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively.
6. Modify the Hours attribute of the WORKS_ON tuple with Essn = ‘999887777’ and Pno = 10 to ‘5.0’.

Answer 1

Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.

Ans: No constraint violations

Insert <‘ProductA’, 4, ‘Bellaire’, 2> into PROJECT.

Ans: 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

(iii) inserting a new DEPARTMENT tuple with DNUMBER=2.

Insert <‘677678989’, NULL, ‘40.0’> into WORKS_ON.

Ans:  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 the constraint by:

(i) rejecting the insertion

(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 EMPLOYEE relation with SSN='677678989'. We may enforce the constraint by:

(i) rejecting the insertion

(ii) changing the value of ESSN to an existing SSN value in EMPLOYEE

(iii) inserting a new EMPLOYEE tuple with SSN='677678989'.

Delete the WORKS_ON tuples with Essn = ‘333445555’.

Ans: No constraint violations

Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively.

Ans: No constraint violations

Modify the Hours attribute of the WORKS_ON tuple with Essn = ‘999887777’ and Pno = 10 to ‘5.0’.

Ans: No constraint violations

Let me know if you have any doubts or if you need anything to change. 

If you are satisfied with the solution, please leave a +ve feedback : ) Let me know for any help with any other questions.

Thank You!
===========================================================================