Examice

Exam 1D0-541 All QuestionsBrowse all questions from this exam

Question 30

Consider the Information Engineering diagram shown in the exhibit for a building management company. Referential integrity must be maintained such that a building cannot be deleted when it has residents. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name, Location and

Res_Name are all represented by variable-length strings with a maximum of 20 characters. Which SQL statement best implements the relations shown in this diagram?

CREATE TABLE BUILDING ( Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (20), Room_Count INTEGER ); CREATE TABLE RESIDENT ( R_ID NOT NULL PRIMARY KEY, Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL, FOREIGN KEY Building_ID REFERENCES RESIDENT (Building_ID) ON DELETE NO CHECK);

Correct Answer: B

To implement the relations efficiently and ensure referential integrity, the SQL statement must correctly define the foreign key relationship between the tables with the appropriate ON DELETE RESTRICT action to prevent deletion of a building if it has residents. The correct implementation involves creating the BUILDING table with a primary key on Building_ID and the RESIDENT table with a foreign key referencing the BUILDING table's Building_ID, with ON DELETE NO CHECK to maintain referential integrity and ON UPDATE CASCADE to handle updates. Therefore, the best option is to utilize the correct foreign key definition referencing the BUILDING table. Option B best meets these criteria.

Discussion

CatBug57

What's the difference?