CIW 1D0-541 Exam Practice Test Instant Access

Question 1

Consider the following relational algebraic expression as well as the Dept1_Parts and

Dept2_Parts relations shown in the exhibit,

Which of the following relations would result from the given relational algebraic expression?

AOption A

BOption B

COption C

DOption D

Answer : C

Question 2

Consider the entity-relation (ER) diagram shown in the exhibit. When the logical database

design phase is completed, which of the following is a valid DBDL description of the base

relations for the ER diagram?

ASTUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL)
Primary Key Student_Number
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num

BSTUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL)
Primary Key Student_Number
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num
Foreign Key Class_Num References STUDENT

CSTUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL)
Primary Key Student_Number
STU_CLASS(
Student_Number: integer NOT NULL
Class_Num: integer NOT NULL)
Primary Key Student_Number
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num

DSTUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL)
Primary Key Student_Number
STU_CLASS(
Student_Number: integer NOT NULL
Class_Num: integer NOT NULL)
Primary Key Student_Number
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num

Answer : D

Question 3

Your database administrator has disallowed a group of users from making alterations to the

Employees table in your corporate database. The users, known as Group_2, previously had full

privileges with the Employees table. Which of the following SQL statements properly removes

any alteration privileges from Group_2?

AREVOKE
INSERT, UPDATE, DELETE
ON Employees
FROM Group_2;

BREVOKE
UPDATE
FOR Employees
FROM Group_2;

CREVOKE
INSERT, UPDATE, DELETE
ON Employees
TO Group_2;

DREVOKE
UPDATE, DELETE
FOR Employees
FROM Group_2;

Answer : A

Question 4

Consider the Information Engineering diagram in the exhibit showing the relations

BUILDING and RESIDENT. What is the relationship between BUILDING and RESIDENT?

A11

B1n

Cn:m

Dm:n

Answer : B

Question 5

The exhibit shows a table called Housing Relation. This table relates a unique student

identification number with a dormitory building and a room fee for that building. The key for the

Housing Relation is Student_ID. If each building charges only one fee and a student can live in

only one building, then this table is in which normal form?

A1NF

B1NF and 2NF

C1NF, 2NF, and 3NF

D1NF, 2NF, 3NF, and BCNF

Answer : B

Question 6

Consider the Recreation relation in the exhibit. A data operation that changes one of the tuples

for Student_ID 1003 must be performed. It is necessary to change one of the activities from

swimming to tennis. The Student_ID and Activity attributes make up the primary key for the

Recreation relation. All related information must be altered, as well. Which SQL statement or

statements would best accomplish this?

AUPDATE Recreation
SET Activity, Activity_Fee ('Tennis', 100)
WHERE Student_ID = 1003;

BUPDATE TABLE Recreation
SET ACTIVITY = 'Tennis', Activity_Fee = 100
WHERE Student_ID = 1003
AND Activity = Swimming;

CUPDATE Recreation
SET Activity = 'Tennis', Activity_Fee = 100
WHERE Student_ID = 1003
AND Activity = 'Swimming';

DDELETE Activity
FROM Recreation
WHERE Student_ID = 1003;
INSERT INTO Recreation
VALUES (1003, 'Tennis', 100);

Answer : C

Question 7

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

Answer : C

CIW v5 Database Design Specialist 1D0-541 Exam Practice Test