Black Friday / Cyber Monday Special Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 70percent

Certensure Logo
  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

Demo: 18 questions
Total 128 questions
Get 1D0-541 Full Access Download 1D0-541 PDF

CIW v5 Database Design Specialist Questions and Answers

Question 1

For the Employee relation shown in the exhibit, which set of column value holds the complete tuple for the employee named James Smith?

Options:

A.

0002, James, Smith

B.

0002, James, Smith, 10-25-76

C.

First_Name, James, Last_Name, Smith

D.

Emp_ID, 0002, First_Name, James, Last_Name, Smith

Question 2

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 3

What improvement can be made to file-based databases to overcome their limitations?

Options:

A.

Implement a tabular structure.

B.

Gather files in a distributed repository.

C.

Use a hierarchical database file system.

D.

Tightly couple database structure to database application programs.

Question 4

Consider the Information Engineering diagram shown in the exhibit. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name and Res_Name are represented by variable-length strings with a maximum of 20 characters. Location can be up to 50 characters long, and no building has more than 600 rooms. Which SQL statement best implements the BUILDING relation shown in this diagram?

Options:

A.

CREATE TABLE BUILDING (

Building_ID NOT NULL PRIMARY KEY,

Bldg_Name, Location, Room_Count);

B.

CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY

KEY, Bldg_Name, Location, Room_Count, FOREIGN KEY

Building_ID REFERENCES BUILDING (Building_ID));

C.

CREATE TABLE BUILDING (Building_ID

INTEGER NOT NULL PRIMARY KEY,Bldg_Name

VARCHAR (20), Location VARCHAR (50),

D.

CREATE TABLE BUILDING (Building_ID

INTEGER NOT NULL PRIMARY KEY, Bldg_Name

VARCHAR (20), Location VARCHAR (50),

Room_Count INTEGER CHECK (Room_Count >

Room_Count INTEGER CHECK (

-1 And Room_Count < 601));

Room_Count > -1 Or Room_Count < 601));

Question 5

Your enterprise is involved in planning a database project. The exhibit shows the result of one phase of the database design life cycle. Which term best describes the diagram shown in the exhibit?

Options:

A.

Information Engineering (IE) data model

B.

Corporate data model

C.

Database requirements model

D.

ERD model

Question 6

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 7

Your enterprise is creating a relation (shown in the exhibit) that tracks parts and suppliers. Which situation would occur if new supplier information were entered in the relation before any information about specific parts?

Options:

A.

An update anomaly and an insertion anomaly would occur.

B.

An insertion anomaly would occur.

C.

A deletion anomaly would occur.

D.

A deletion anomaly and an update anomaly would occur.

Question 8

Which type of relational integrity is violated if a primary key in a database has a null value?

Options:

A.

Entity integrity

B.

Domain integrity

C.

Domain constraints

D.

Referential integrity

Question 9

Which of the following definitions best describes an entity?

Options:

A.

A relation

B.

Data about data

C.

Data stored in a table column

D.

An item about which information is stored

Question 10

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 11

Consider the relation shown in the exhibit. Which of the following SQL statements would return a relation that excludes all customers with a Satisfaction_Rate of less than or equal to 80 unless the Sales_Office is located in Atlanta?

Options:

A.

SELECT * FROM Customers

WHERE Satisfaction_Rate > 80

OR Sales_Office = Atlanta

B.

SELECT * FROM Customers

WHERE Satisfaction_Rate <= 80

AND Sales_Office = Atlanta

C.

SELECT * FROM Customers

WHERE Satisfaction_Rate >= 80;

D.

SELECT * FROM Customers

WHERE Satisfaction_Rate >= 80

AND NOT Sales Office = Atlanta

Question 12

In which phase of database design do you identify entities, attribute domains, and relationships?

Options:

A.

Logical

B.

Physical

C.

Application

D.

Conceptual

Question 13

Which statement is used to define a named group of related tables, views, domains and other database objects?

Options:

A.

CREATE

B.

CREATE TABLE

C.

CREATE DOMAIN

D.

CREATE SCHEMA

Question 14

What is the most important service provided by a database management system?

Options:

A.

Provides support for a data manipulation language

B.

Allows users to store data in a distributed data repository

C.

Provides support for data formatting language commands

D.

Translates procedural commands into non-procedural commands

Question 15

Which type of dependency occurs when one attribute of a composite key is removed and the dependency still exists?

Options:

A.

1:1

B.

Transitive

C.

Functional

D.

Partial functional

Question 16

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 Part_ID

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

DIFFERENCE

(SELECT Part_ID

FROM Dept2_Parts);

Question 17

A large enterprise uses a two-tier database architecture and runs complex database applications.

Which term best describes the client in this system?

Options:

A.

Fat client

B.

Enterprise client

C.

Thin client

D.

Terminal client

Question 18

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

Load More 1D0-541 Questions
Demo: 18 questions
Total 128 questions
Get 1D0-541 Full Access Download 1D0-541 PDF