Suppose each of the following update operations is applied directly to the datab
ID: 3667812 • Letter: S
Question
Suppose each of the following update operations is applied directly to the database state shown in Figure 3.6 (page 72). Discuss all the integrity constraints (in Figure 3.7) violated by each operation, if any, and the different ways of enforcing these constraint (each update statement is treated independently.)
a.) Insert <‘Robert’, ‘F’, ’Scott’, ‘943775543’, ‘1952-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’, ‘1998-10-01’> into DEPARTMENT.
d) Insert <‘677678989’, null, 40.0> into WORKS_ON.
e.) Insert <‘453453453’, ‘John’, ‘M’, ‘1970-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 MGRSSN and MGRSTARTDATE of the DEPARTMENT tuple with DNUMBER = 5 to ‘123456789’ and ‘1999-10-01’, respectively.
j.) Modify the SUPERSSN 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
IF YOU FOUND ANY ERRORS PLEASE DO COMMENT
a)
: INSERT INTO EMPLOYEE
VALUES ('Robert', 'F', 'Scott', '943775543', '21-JUN-42', '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', '01-OCT-88')
d)
INSERT INTO WORKS_ON
VALUES ('677678989', NULL, '40.0')
e)
INSERT INTO DEPENDENT
VALUES ('453453453', 'John', M, '12-DEC-60', '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 MGRSSN = '123456789', MGRSTARTDATE = '01-OCT-88'
WHERE DNUMBER= 5
j)
UPDATE EMPLOYEE
SET SUPERSSN = '943775543'
WHERE SSN= '999887777'
k)
UPDATE WORKS_ON
SET HOURS = '5.0'
WHERE ESSN= '999887777' AND PNO= 10