Suppose that each of the following Update operations is applied directly to the

Question

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. Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle
Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.
b. Insert <‘ProductA’, 4, ‘Bellaire’, 2> into PROJECT.
c. Insert <‘Production’, 4, ‘943775543’, ‘2007-10-01’> into DEPARTMENT.
d. Insert <‘677678989’, NULL, ‘40.0’> into WORKS_ON.
e. Insert <‘453453453’, ‘John’, ‘M’, ‘1990-12-12’, ‘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 Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with
Dnumber = 5 to ‘123456789’ and ‘2007-10-01’, respectively.
j. Modify the Super_ssn 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’.

Explanation / Answer

a) Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle
Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.
Ans: No constraint violations.

b). Insert < 'ProductA', 4, 'Bellaire', 2 > into PROJECT.

With this statemment there is violates referential integrity. Here DNUM=2 and as per given there is no tuple that is in
relation with DNUMBER=2.
We may avoid constraint by:

c)With this updating operation, there are some violates occure with referential integrity and as well as key constraint.
Because we contain DEPARTMENT tuple with DNUMBER=4.

d)With this updating operation,there are some Violates with entity integrity as well as referential integrity.
It Violates entity integrity because of primary key PNO, part of WORKS_ON, is null.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.