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

Lachelle

1 months ago

Hmm, this question is making me thirsty. I wonder if there's a building management company that also owns a pub. That would be a great side gig!

upvoted 0 times

Ira

15 days ago

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

upvoted 0 times

...

Karl

18 days ago

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

upvoted 0 times

...

Whitley

1 months ago

I'm going with option D. It's the most concise and clear implementation of the relations shown in the diagram. As a bonus, the ON UPDATE CASCADE clause means I don't have to worry about manually updating references when a building's ID changes. Score one for the lazy programmer!

upvoted 0 times

...

Jeannine

1 months ago

This is a tricky one. I'm leaning towards option C since it's the same as option D, but with a more descriptive comment. The ON DELETE NO CHECK and ON UPDATE CASCADE clauses are important to maintain referential integrity.

upvoted 0 times

Ronny

1 days ago

I'm not sure, but I think option C is the safest bet to maintain referential integrity.

upvoted 0 times

...

Ronny

6 days ago

I think option D is also a good choice because it includes the necessary clauses for referential integrity.

upvoted 0 times

...

Ronny

16 days ago

I agree, option C seems like the best choice with the additional comment for clarity.

upvoted 0 times

...

Yasuko

2 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

1 months 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

2 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

2 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

...

Olene

2 months 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