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

1 months ago

I'm not sure about the ON DELETE NO CHECK clause. Shouldn't it be ON DELETE RESTRICT to prevent deleting a building with residents?

upvoted 0 times

Audra

26 days ago

That's a good point, ON DELETE CASCADE would ensure that the related residents are also deleted when a building is deleted.

upvoted 0 times

...

Audra

1 months ago

But wouldn't ON DELETE CASCADE be more appropriate to automatically delete the related residents when a building is deleted?

upvoted 0 times

...

Audra

1 months ago

I think you're right, ON DELETE RESTRICT would be a better option to prevent deleting a building with residents.

upvoted 0 times

...

2 months ago

I think the correct answer is option B.

upvoted 0 times

...