CIW v5 Database Design Specialist (1D0-541) Free Practice Test

Question 1

What is the highest normal form of the relation(s) shown in the exhibit?

A. Third normal form

B. First normal form

C. No normal form

D. Second normal form

Correct Answer: A

Question 2

Your company must choose which type of database to use for a new project. Which of the following lists three characteristics of file-based database systems?

A. Separation of data, repetition of data, and data independence

B. Incompatibility of files, tabular data structures, and data dependence

C. Application program inflexibility, data dependence, and separation of data

D. Repetition of data, application program flexibility, and data centralization

Correct Answer: C

Question 3

Consider the following relational algebraic expression as well as the Dept1_Parta and Dept2_Parts relations shown in the exhibit: Which of the following relations would result form the given relational algebraic expression?

A. D

B. A

C. B

D. C

Correct Answer: D

Question 4

The exhibit shows a relation for a company projects. Which candidate key(s) would best serve as the primary key for this relation?

A. ProjJD

B. S_Date and E_Date

C. Proj_ID and Item_Num

D. ltem_Num and E_Date

Correct Answer: C

Question 5

Consider the following SQL statement and the Orders relation shown in the exhibit:

What is the output of this SQL statement?

A. B.

B. D.

Correct Answer: B

Question 6

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?

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

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 RESIDENT (Building_ID)
ON DELETE NO CHECK);

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

Correct Answer: B

Question 7

Consider the following SQL statement and the Orders relation shown in the exhibit: How many records should be returned?

A. Five records

B. Three records

C. Two records

D. Four records

Correct Answer: D

Question 8

Consider the relation shown in the exhibit. Which of the following SQL statements would properly add information for a new employee?

A. INSERT INTOEmployee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0004, Tim, Bogart, 03-15-77);

B. INSERT INTOEmployee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0005, Tim, Bogart, 03-05-77);

C. INSERT INTO Employee VALUES(0005, Tim, Bogart, 03-15-77);

D. INSERT INTOEmployee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0005, Tim, Bogart, 03-05-77);

Correct Answer: D

Welcome to TestSimulate

CIW v5 Database Design Specialist (1D0-541) Free Practice Test