Labour Day Special Limited Time 65% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: suredis

Examstrust Logo
examstrust mcafee
  1. Home
  2. CIW
  3. CIW Web Development Series
  4. 1D0-541 - CIW v5 Database Design Specialist

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

Page: 1 / 13
Total 128 questions
Get 1D0-541 Full Access Download 1D0-541 PDF

CIW v5 Database Design Specialist Questions and Answers

PDF + Testing Engine

  • Product Type: PDF + Testing Engine
$59.5  $169.99
Add to Cart

Testing Engine

  • Product Type: Testing Engine
$42  $119.99
Add to Cart

PDF Study Guide

  • Product Type: PDF Study Guide
$36.75  $104.99
Add to Cart
Question 1

Which type of entity must reference another entity for its data to be meaningful?

Options:

A.

Weak

B.

Strong

C.

Foreign

D.

Primary

Question 2

Consider the following table as well as the Dept1_Parts and Dept2_Parts relations shown in the exhibit: Which of the following relational algebraic expressions would result in the given table?

Options:

A.

Option A

B.

Option B

C.

Option C

D.

Option D

Question 3

Which of the following definitions applies to all types of databases?

Options:

A.

Data that is stored as tables

B.

Software that manipulates data

C.

Data that is stored in a structured manner

D.

Data records that are stored sequentially in a file

Question 4

Consider the relational database shown in the exhibit.

What is the foreign key in this database?

Options:

A.

Employee. Dept_ID

B.

Dept_Mngr

C.

Dept_Name

D.

Department. Dept_ID

Question 5

The creation of intermediate entities occurs during the logical database design phase for an enterprise. It is used to resolve which types of relationships?

Options:

A.

One-to-many and recursive

B.

Complex, recursive, and many-to-many

C.

Redundant, recursive, and one-to-many

D.

One-to-many and one-to-one

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?

Options:

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

Question 7

Which mechanism provides database users with controlled access to the database through the use of virtual tables?

Options:

A.

View

B.

Data dictionary

C.

Database control language

D.

Database management system

Question 8

Which of the following best describes the two-tier database architecture?

Options:

A.

The user accesses a database server using a terminal.

B.

The user interface, data-processing logic, database access and data validation functions are performed on a mainframe server.

C.

The user interface and data validation functions are performed by the client whereas the data-processing logic is performed on a server.

D.

The user interface and data-processing logic are performed by the client whereas the server handles database access and data validation functions.

Question 9

The exhibit shows a table called Recreation Relation that relates a unique student identification number and a sports activity with a fee for participating in that activity. The Student_ID and Activity columns in the table are used together as a composite key. Which statement about the relation is correct?

Options:

A.

Activity_Fee is a determinant of Activity.

B.

Activity_Fee is partially dependent on the key.

C.

The table contains a transitive dependency.

D.

Activity_Fee is a determinant of Activity and Student_ID.

Question 10

Which subset of Structured Query Language (SQL) is used to perform operations such as data retrieval or deletion on the data contained in a database?

Options:

A.

Data Control Language

B.

Data Definition Language

C.

Data Formatting Language

D.

Data Manipulation Language

Question 11

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

Options:

A.

S_Date and E_Date

B.

ProjJD

C.

ltem_Num and E_Date

D.

Proj_ID and Item_Num

Question 12

Consider the entity-relationship (ER) diagram shown in the exhibit. What do the characters at the ends of the connecting line indicate?

Options:

A.

Degree of a relation

B.

Cardinality of a relation

C.

Primary key of a relation

D.

Determinant of a relation

Question 13

Consider the Dept1_Parts and Dept2_Parts relations shown in the exhibit. Which of the following SQL statements would create a set difference of the two relations with the widest variety of Structured Query Language dialects?

Options:

A.

SELECT *FROM

Dept1_Parts

EXCEPT(SELECT

PartJD FROM

Dept2_Parts);

B.

SELECT * FROM

Dept1_Parts MINUS

(SELECT Part_ID

FROM Dept2_Parts);

C.

SELECT*FROM

Dept1_Parts

DIFFERENCE

(SELECT Part_ID

FROM Dept2_Parts);

D.

SELECT*FROM

Dept1_Parts WHERE

Part_ID NOT IN

(SELECT Part_ID FROM

Dept2_Parts);

Question 14

Consider the Recreation relation shown in the exhibit. You need to apply a SQL statement to the

Recreation relation that will return the following data:

Which SQL statement applied to the Recreation relation will return this data?

Options:

A.

SELECT Activity FROM Recreation;

B.

SELECT DISTINCT Activity FROM Recreation;

C.

SELECT Activity FROM Recreation

WHERE NOT LIKE Activity;

D.

SELECT Activity FROM Recreation

WHERE DISTINCT Activity;

Question 15

Consider the following relations shown in the exhibit. Which of the following SQL statements would return the Customers2 relation from the Customers relation?

Options:

A.

SELECT * FROM Customers

WHERE Satisfaction_Rate <= 80

OR Satisfaction_Rate >= 90;

B.

SELECT * FROM Customers WHERE

Satisfaction_Rate IN (80 AND 90);

C.

SELECT *FROM Customers

WHERE Satisfaction_Rate >= 80

AND Satisfaction_Rate <= 89;

D.

SELECT * FROM

Customers WHERE

Satisfaction_Rate

BETWEEN (80, 90);

Question 16

Consider the relations shown in the exhibit. Which of the following SQL statements would enter data from the Customers relation into the Atlanta_Customers relation?

Options:

A.

INSERT INTO Atlanta_Customers

VALUES( SELECT * FROM

Customer s WHERE Sales_Office =

Atlanta

B.

INSERT INTO Atlanta_Customers

SELECT * FROM Customers

WHERE Sales_Office = Atlanta

C.

INSERT INTO Atlanta_Customers SELECTCust_No,

Cust_Name, Satisfaction_Rate, Sales_Rep_No FROM Customers

WHERE Sales_Office = Atlanta

D.

INSERT INTO Atlanta_Customers SELECT Cust_No,

Cust_Name, Sales_Office, Sales_Rep_No FROM Customers

WHERE Sales_Office = Atlanta

Question 17

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

Options:

A.

Second normal form

B.

First normal form

C.

Boyce-Codd normal form

D.

Third normal form

E.

STUDENT( 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

F.

STUDENT( 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

G.

STUDENT( Student_Number: integer NOT NULL Name:

variable length character string length 20 NOT NULL)

Primary Key StudenMMumber 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

Question 18

Which process is used to prevent the current database operation from reading or writing a data item while that data item is being accessed by another operation?

Options:

A.

Lock

B.

Deadlock

C.

Time stamp

D.

Transaction

Load More 1D0-541 Questions
Page: 1 / 13
Total 128 questions
Get 1D0-541 Full Access Download 1D0-541 PDF