CIW 1D0-541 Exam - 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

Melita

4 months ago

Isn't it weird that you can delete a building with residents?

upvoted 0 times

...

Shakira

4 months ago

Totally agree with B, it maintains referential integrity well!

upvoted 0 times

...

Lillian

4 months ago

Wait, why is there "NO CHECK" in the foreign key? That seems off.

upvoted 0 times

...

Rusty

4 months ago

I think C is better because it has ON UPDATE CASCADE.

upvoted 0 times

...

Ronny

5 months ago

Option B looks solid with the foreign key reference.

upvoted 0 times

...

Desiree

5 months ago

I thought option C looked good, but I’m confused about the ON UPDATE CASCADE part. Does that really apply here?

upvoted 0 times

...

Vinnie

5 months ago

I practiced a similar question, and I feel like the ON DELETE NO CHECK part might be wrong in all options. Shouldn't it be ON DELETE RESTRICT instead?

upvoted 0 times

...

Leatha

5 months ago

I'm not entirely sure, but I remember something about referential integrity needing to prevent deletions when there are related records.

upvoted 0 times

...

Vincent

5 months ago

I think option B is the right choice because it correctly references the BUILDING table for the foreign key.

upvoted 0 times

...

Justine

5 months ago

This is a good test of my SQL knowledge. I'll review each option carefully and select the one that best implements the relations shown in the diagram.

upvoted 0 times

...

Alayna

5 months ago

Okay, I think I've got this. The key is to properly define the foreign key relationship between the RESIDENT and BUILDING tables to maintain referential integrity.

upvoted 0 times

...

Soledad

5 months ago

Hmm, I'm a bit confused by the referential integrity constraint. I need to make sure I understand how that affects the foreign key relationship between the tables.

upvoted 0 times

...

Doug

5 months ago

This looks like a straightforward database design question. I'll carefully read through the requirements and the diagram to determine the correct SQL statement.

upvoted 0 times

...

Cristy

6 months ago

I'm feeling confident about this one. The question provides clear instructions, and the diagram makes the database structure pretty straightforward.

upvoted 0 times

...

Kati

6 months ago

This one seems pretty straightforward. I'm pretty confident that the answer is Robert Koch, since he was known for his work identifying the anthrax bacteria.

upvoted 0 times

...

Lachelle

10 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

Sarah

8 months ago

Hmm, a building management company owning a pub would be a cool idea!

upvoted 0 times

...

Julio

9 months ago

C) 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

...

Ira

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

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

10 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

10 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

9 months ago

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

upvoted 0 times

...

Ronny

9 months ago

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

upvoted 0 times

...

Ronny

10 months ago

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

upvoted 0 times

...

Yasuko

11 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

10 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

11 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

11 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

11 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