Suppose each of the following update operations is applied directly to the database of Figure 1 bellow, 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.
No constraint violations.
(b) Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.
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.
(c) Insert < 'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT.
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 the constraint by:
(i) rejecting the insertion
(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
DataBase Discussion ENG. GHADIR AL JARO
(ii) changing the value of MGRSSN to an existing SSN value in EMPLOYEE
(iii) inserting a new EMPLOYEE tuple with SSN='943775543'.
(d) Insert < '677678989', null, '40.0' > into WORKS_ON.
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'.
(e) Insert < '453453453', 'John', M, '12-DEC-60', 'SPOUSE' > into DEPENDENT. No constraint violations.
(f) Delete the WORKS_ON tuples with ESSN= '333445555'. No constraint violations.
(g) Delete the EMPLOYEE tuple with SSN= '987654321'. 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
(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'.
(h) Delete the PROJECT tuple with PNAME= 'ProductX'. 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
(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)