CertsTopics Logo

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

CIW 1D0-541 Dumps

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

CIW v5 Database Design Specialist Questions and Answers

Question 1

NULL) Primary Key Class_Num

Consider the Information Engineering diagram shown in the exhibit. Which DBDL definition best describes this diagram?

Options:

A.

BUILDING(Building_ID, Bldg_Name, Location, Room_Count)

Primary Key Building_ID RESIDENT(R_ID, Room_Num,

Res_Name, Building_ID) Primary Key R_ID

B.

BUILDING(Building_ID, Bldg_Name, Location, Room_Count)

Primary Key BUILDING RESIDENT(R_ID, Room_Num,

Res_Name, Building_ID) Primary Key RESIDENT

C.

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key BUILDING ForeignKey BUILDING(Building_ID) references RESIDENT(Building_ID) RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key RESIDENT

D.

BUILDING(Building_ID, Bldg_Name, Location, Room_Count)

Primary Key Building_ID RESIDENT(R_ID, Room_Num,

Res_Name, Building_ID) Primary Key R_ID Foreign Key

Building_ID references BUILDING(Building_ID)

Question 2

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

Options:

A.

Logical

B.

Physical

C.

Application

D.

Conceptual

Question 3

Which statement best describes a procedural data manipulation language command?

Options:

A.

It contains a query language for retrieving data.

B.

It can be used only to manipulate data through a SQL interface.

C.

The user is not required to know how the underlying data structures are implemented.

D.

It requires that the user know how the underlying data structures are implemented.

Question 4

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

What is the output of this SQL statement?

SELECT *

FROM Orders

WHERE NOT (Amount < 1000

AND Sales_Rep_No = 210);

Options:

A.

OptionA

B.

OptionB

C.

OptionC

D.

OptionD

Question 5

A theta-join can be viewed as:

Options:

A.

The intersection oftwo relations

B.

ACartesian product of two relations

C.

Arestricted Cartesian product of two relations

D.

The Cartesian product oftwo union-compatible relations

Question 6

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 7

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?

Options:

A.

Repetition of data, application program flexibility, and data centralization

B.

Incompatibility of files, tabular data structures, and data dependence

C.

Separation of data, repetition of data, and data independence

D.

Application program inflexibility, data dependence, and separation of data

Question 8

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

Options:

A.

Third normal form

B.

Second normal form

C.

No normal form

D.

First normal form

Question 9

Which subset of Structured Query Language (SQL) is used to create and name database entities?

Options:

A.

Data Query Language

B.

Database Entity Language

C.

Data Definition Language

D.

Data Manipulation Language

Question 10

Consider the relations shown in the exhibit. Due to restructuring, the sales department has been eliminated and the employees working in that department have been dismissed. All ID information is stored as integers. Which SQL statement would be used to return a relation with all information for the employees who have been dismissed?

Options:

A.

SELECT *

FROM Employee;

B.

SELECT ID, Last_Name

FROM Employee;

WHERE ID = 0004;

C.

SELECT *

FROM Employee

WHERE Dept_ID = 022;

D.

SELECT *

FROM Employee

WHERE Dept_ID = 022;

Question 11

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 12

Consider the following four database design activities:

1 - Design user views.

2 - Select a DBMS.

3 - Apply normalization.

4 - Determine entities.

Which choice shows the correct ordering of these activities, from first to last, by assigned numbers?

Options:

A.

1,2,3,4

B.

3,4,1,2

C.

4,3,1,2

D.

4,2,3,1

Question 13

Which characteristic is an advantage of a database management system?

Options:

A.

Data files are owned and maintained by the users.

B.

Database administration is simplified.

C.

A standard method can be used to access the database.

D.

Data is decentralized.

Question 14

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 15

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 16

Consider the Employee relation shown in the exhibit. A database manager wants to set up a view called Emp_Dept that allows users to find employees and their department ID numbers. Which SQL statement will accomplish this?

Options:

A.

CREATE VIEW Emp_Dept AS SELECT

Last_Name, First_Name, Dept_ID FROM

Employee;

B.

UPDATE VIEW Emp_Dept

AS SELECT * FROM

Employee;

C.

UPDATE VIEW Emp_Dept AS SELECT

Last_Name, First_Name, Dept_ID FROM

Employee;

D.

CREATE VIEW Emp_Dept

AS SELECT * FROM

Employee WHERE ID = 0001

AND ID = 0002 AND ID =

0003 AND ID = 0004;

Question 17

Which three pieces of information did E.F. Codd describe as necessary to retrieve a data value from a relational database?

Options:

A.

Attribute, domain, and tuple

B.

Entity, relation name, and domain

C.

Table name, primary key, and entity

D.

Attribute, relation name, and primary key

Question 18

Which statement best describes a candidate key?

Options:

A.

It is the primary key for an entity.

B.

It uniquely identifies every instance of an entity.

C.

One or more keys are joined together to form a composite key.

D.

One or more keys may be used to form a primary key.

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