PrepAway - Latest Free Exam Questions & Answers

Which SQL statement best implements the relations shown in this diagram?

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?

PrepAway - Latest Free Exam Questions & Answers

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

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

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

D.
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);

One Comment on “Which SQL statement best implements the relations shown in this diagram?


Leave a Reply