CS3402 Tutorial 3:
1. Suppose each of the following Update operations is applied directly to the database below. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints:
(a) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
(b) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
(c) Insert < '677678989', null, '40.0' > into WORKS_ON.
(d) Delete the EMPLOYEE tuple with SSN= ‘987654321’.
(e) Delete the PROJECT tuple with PNAME= ‘ProductX’.
(f) Modify the SUPERSSN attribute of the EMPLOYEE tuple with SSN= ‘999887777’ to ‘943775543’.
Answers:
(a) 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.
(b) 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′.
(c) 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 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, or (iii) inserting a new EMPLOYEE tuple with SSN=’677678989′.
(d) Violates referential integrity because several tuples exist in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference the tuple being deleted from EMPLOYEE. We may enforce the constraint by: (i) rejecting the deletion, or (ii) deleting all tuples in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations whose values for ESSN, ESSN, MGRSSN, and SUPERSSN, respectively, is equal to’987654321′.
(e) Violates referential integrity because two tuples exist in the WORKS_ON relations that reference 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 value for PNO=1 (the value for the primary key PNUMBER for the tuple being deleted from PROJECT).
(f) Violates referential integrity because the new value of SUPERSSN=’943775543′ and there is no tuple in the EMPLOYEE relation with SSN=’943775543′. We may enforce the
constraint by: (i) rejecting the deletion, or (ii) inserting a new EMPLOYEE tuple with SSN=’943775543′.
2 Consider the relation REFRIG(MODEL#, YEAR, PRICE, MANUF_PLANT, COLOR), which is abbreviated as REFRIG(M, Y, P, U, C), and the following set of F of functional dependencies: F={M -> U, {M,Y} -> P, U -> C}. Evaluate each of the following as a candidate key for REFRIG, giving reasons why it can or cannot be a key: {M}, {M,Y}, {M,C}
Answers:
– {M} IS NOT a candidate key since it does not functionally determine attributes Y or P. {M}+={M,U,C}
– {M, Y} IS a super key since it functionally determines the remaining attributes P, U, and C. Also {M} and {Y} are not the superkey, so {M,Y} IS a candidate key.
i.e.
We have {M, Y}-> P, and M->U, by augmentation {M, Y}->U
Since U->C, by transitivity M->U, U->C, gives M->C; By augmentation {M, Y} ->C Thus {M, Y}+={M,Y, P, U,C} and {M, Y} can be a super key.
{M}+={M,U,C}, {M} is not super key.
{Y}+={Y}, {Y} is not super key.
So {M,Y} is the candidate key.
– {M, C} IS NOT a candidate key since it does not functionally determine attributes Y or P. {M,C}+={M,C,U}.