CIW Exam 1D0-541 Topic 2 Question 98 Discussion

Actual exam question for CIW's 1D0-541 exam

Question #: 98
Topic #: 2

[All 1D0-541 Questions]

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?

ACREATE 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);

BCREATE 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 BUILDING (Building_ID)
ON DELETE NO CHECK
ON UPDATE CASCADE);

CCREATE 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 BUILDING (Building_ID)
ON DELETE NO CHECK
ON UPDATE CASCADE);

DCREATE 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 BUILDING (Building_ID)
ON DELETE NO CHECK
ON UPDATE CASCADE);

Show Suggested Answer

Suggested Answer: D

by Jerry at Sep 16, 2024, 07:12 AM

Limited Time Offer

25%

Off

Get Premium 1D0-541 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Olene

2 days ago

I think option D is the correct answer. The foreign key constraint on the Building_ID column in the RESIDENT table references the BUILDING table, ensuring referential integrity. The ON DELETE NO CHECK clause prevents deleting a building that has residents, and the ON UPDATE CASCADE clause ensures that changes to the Building_ID in the BUILDING table are propagated to the RESIDENT table.

upvoted 0 times

...