Consider that the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 6.2 is changed to read as follows:

CONSTRAINT EMPSUPERFK
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
                                    ON DELETE CASCADE ON UPDATE CASCADE,

Answer the following questions:

a. What happens when the following command is run on the database state shown in Figure 5.6?

DELETE EMPLOYEE WHERE Lname = ‘Borg’

b. Is it better to CASCADE or SET NULL in case of EMPSUPERFK constraint ON DELETE?

Figure 6.2

Transcribed Image Text:

CREATE TABLE EMPLOYEE (... Dno INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (Ssn), CONSTRAINT EMPSUPERFK FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE); CREATE TABLE DEPARTMENT (.... Mgr_ssn CHAR(9) NOT NULL DEFAULT '888665555', CONSTRAINT DEPTPK PRIMARY KEY(Dnumber), CONSTRAINT DEPTSK UNIQUE (Dname), CONSTRAINT DEPTMGRFK FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE); Figure 6.2 Example illustrating CREATE TABLE DEPT LOCATIONS how default attribute values and referential integrity triggered actions are specified in SQL PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE);