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 1. For each operation, Specify the updates using the SQL update commands.
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’.


Deliverables:
For each operation, Specify the updates using the SQL update commands.

Explanation / Answer

a. insert into EMPLOYEE VALUES (‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd, Bellaire, TX’, M, 58000, ‘888665555’, 1); b. insert into PROJECT VALUES (‘ProductA’, 4, ‘Bellaire’, 2); c. insert into DEPARTMENT VALUES (‘Production’, 4, ‘943775543’, ‘2007-10-01’); d. insert into WORKS_ON VALUES (‘677678989’, NULL, ‘40.0’); e. insert into DEPENDENT VALUES (‘453453453’, ‘John’, ‘M’, ‘1990-12-12’, ‘spouse’); f. delete from WORKS-ON where Essn = ‘333445555’; g. delete from EMPLOYEE where Ssn = ‘987654321’; h. delete from PROJECT where Pname = ‘ProductX’; i. update DEPARTMENT set mgr_ssn = '123456789', mgr_start_date = '2007-10-01' where Dnumber = 5; j. update EMPLOYEE set Super_ssn = '943775543' where Ssn = '999887777'; k. update WORKS_ON set Hours = '5.0' where Essn = '999887777' AND Pno = 10;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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