New Year Special Limited Time Flat 70% Discount offer - Ends in 0d 00h 00m 00s - Coupon code: 70spcl

Activedumpsnet 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

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

CIW v5 Database Design Specialist Questions and Answers

Question 1

Which of the following best describes the ON DELETE NO ACTION referential integrity constraint?

Options:

A.

If a parent key is deleted, any child keys referenced by the parent key are automatically deleted.

B.

If a parent key is deleted, no test is made for referential integrity.

C.

If any child key references a parent key, the record containing the parent key cannot be deleted.

D.

If a parent key is deleted, all child keys are automatically set to a specified value.

Question 2

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 3

In which situation would the DBMS use a serial schedule to execute the transactions?

Options:

A.

Concurrent transactions read data from the same data structure.

B.

Concurrent transactions write data to different data structures.

C.

Concurrent transactions read or write the same data structure.

D.

Concurrent transactions read or write from different data structures.

Question 4

Which of the following is a characteristic of the three-tier database architecture?

Options:

A.

A Web browser is used as the application server.

B.

The application logic is centralized on a dedicated server.

C.

A thick client is used to perform business application logic functions locally.

D.

Database application logic and database functionality are integrated and reside on a common server.

Question 5

Which of the following describes two desirable characteristics of a primary key?

Options:

A.

A primary key should be a value that may be null and may change over time.

B.

A primary key should be a value that is not null and will never change.

C.

A primary key should consist of meaningful data and a value that can be changed if needed.

D.

A primary key should not consist of meaningful data and a value that can be changed if needed.

Question 6

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

Question # 6

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 7

Consider the following relation definitions:

STUDENT(

Student_Number: integer NOT NULL

Name: variable length character string length 20)

Primary Key Student_Number

HOUSING(

Housing_ID: integer NOT NULL

Student_Number: integer NOT NULL

Building: variable length character string length 25)

Primary Key Housing_ID

Foreign Key Student_Number References STUDENT(Student_Number)

ON DELETE NO ACTION

ON UPDATE CASCADE

What are the referential constraints for the relations defined in these relation definitions?

Options:

A.

There is no relationship between changes in STUDENT(Student_Number) and HOUSING(Student_Number).

B.

When STUDENT(Student_Number) is changed or deleted, this modification or deletion will automatically be reflected in HOUSING(Student_Number).

C.

Modifications to HOUSING(Student_Number) are automatically reflected in changes to STUDENT(Student_Number), but deletions are not permitted.

D.

Modifications to STUDENT(Student_Number) are automatically reflected in changes to HOUSING(Student_Number). For a deletion to occur fromSTUDENT(Student_Number), it must first occur in HOUSING(Student_Number).

Question 8

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 9

Which concurrency control method should be used only when conflicts between transactions rarely occur?

Options:

A.

Locking

B.

Time stamps

C.

Optimistic

D.

Serialization

Question 10

What is a relational database domain?

Options:

A.

A group of attributes

B.

A set of permissible tuple values

C.

A collection of related data items

D.

A set of permissible attribute values

Question 11

You enterprise must decide whether to use a database management system. Which of the following lists four advantages of using a DBMS?

Options:

A.

Management of data redundancy, increased data integrity, increased data dependence, and increased application program flexibility.

B.

Consistency of data, adherence to standards, managed concurrency, and increased software complexity.

C.

Increased data access, increased data backup and recovery, data sharing, and consistency of data.

D.

Increased datasecurity, increased data integrity, increased data independence, and decreased data separation.

Question 12

Consider the table shown in the exhibit. Which relational algebraic operation would return Row 3?

Question # 12

Options:

A.

Union

B.

Selection

C.

Projection

D.

Difference

Question 13

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?

Options:

A.

REVOKE INSERT,

UPDATE, DELETE ON

Employees FROM Group_2;

B.

REVOKE

UPDATE FOR

Employees

FROM Group_2;

C.

REVOKE INSERT,

UPDATE, DELETE ON

Employees FOR Group_2;

D.

REVOKE

UPDATE, DELETE

FOR Employees

FROM Group_2;

Question 14

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

Question # 14

Options:

A.

Third normal form

B.

Second normal form

C.

No normal form

D.

First normal form

Question 15

The exhibit shows a table called Activity Relation that relates a unique student identification number with a sports activity and a fee for participating in that activity. A student can participate in only one activity. The key for the relation is Student_ID. What consequence would occur if the tuple for Student_ID 1001 were removed?

Question # 15

Options:

A.

An update anomaly would occur.

B.

An insertion anomaly would occur.

C.

A deletion anomaly would occur.

D.

Both an insertion anomaly and a deletion anomaly would occur.

Question 16

Your enterprise has created a database and database application. The testing phase for the project has started. Which of the following best describes white-box testing of the projects software?

Options:

A.

The database designer tests the software because he or she is able to make necessary changes to the underlying code for the software.

B.

A user who has no knowledge of the softwares underlying code tests the software.

C.

Someone other than the database designer tests the software. This person has no access to the underlying code and attempts to use the software only in ways not considered by the software designers.

D.

A person tests the software and submits suggestions to the software's underlying code. This person is someone other than the database designer, but has access to the softwares underlying code.

Question 17

Which term describes the management of simultaneous transactions to prevent conflicts?

Options:

A.

Parallelism

B.

Serialization

C.

Database control

D.

Concurrency control

Question 18

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

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