Home Work Suppose that each of the following Update operations is applied direct

Question

Home Work Suppose that each of the following Update operations is applied directly to the Company database state shown in Figure 3.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 Product A', 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 Product 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) No constraints will be violated with this query as this is the base table for all other. Query will be executed successfully and records will be updated in the table.

b) foreign key constraint will be violated because of this query if the dnum '2' is not in the table department. Because dnum column in this table references dnum in department table . So if there is no department with no 2 in department table then this query raises constraint violation other wise it will execute successfully.

c) if this query is executed after first insert query here then there will be no issues other intigrity constraint violation will be raised because of the Employee ssn no. If the Employee table contains any record with that ssn then also no issues will occur.

d) Constraint violation will be raised as the pno is defined as primary key in the table. Primary key value never be null. So this raises error.

e) this will be executed successfully and records will be updated without any problems.

f) This query will be executed successfully without any errors.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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