New Year 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

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 2

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 3

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 4

Consider the table for an employee database shown in the exhibit. What is the degree of the table?

Options:

A.

25

B.

5

C.

4

D.

20

Question 5

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

Options:

A.

Parallelism

B.

Serialization

C.

Database control

D.

Concurrency control

Question 6

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 7

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

What is the output of this SQL statement?

Options:

A.

Option A

B.

Option B

C.

Option C

D.

Option D

Question 8

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 9

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 10

What is a virtual table?

Options:

A.

A virtual table is a relation created as the result of data manipulation; it exists only in computer memory, and is not a permanent part of the database.

B.

A virtual table is a relation stored in the database; it is used when multiple users access the same relation in a database.

C.

A virtual table is a relation derived from the database data dictionary; it contains metadata about a base relation.A virtual table is a relation derived from the database? data dictionary; it contains metadata about a base relation.

D.

A virtual table is a relation that consists of primary and foreign keys for a particular set of relations in a database.

Question 11

FROM Registration WHERE

Consider the following relational algebraic expression: Which of the following SQL Course_Code = 'A4343';

statements is equivalent to this relational algebraic expression?

Options:

A.

SELECT Sales_Rep_No(108)

FROM Orders;

B.

INSERT INTO Orders

VALUES(Sales_Rep_No = 108)

WHERE Sales_Rep_No = NULL;

C.

SELECT'FROM Orders

WHERE Sales_Rep_No = 108;

D.

SELECT'FROM Orders

WHERE Sales_Rep_No = ?08?

Question 12

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 13

Consider the following relational algebraic expression as well as the Employee and Department relations shown in the exhibit: Which of the following relations would result from the given relational algebraic expression?

Options:

A.

OptionA

B.

OptionB

C.

OptionC

D.

OptionD

Question 14

Consider the following relation definition:

STUDENT(

Student_Number: integer NOT NULL

Name: variable length character string length 20 NOT NULL)

Primary Key Student_Number

HOUSING(

Housing_ID: integer NOT NULL

Student_Number: integer NOT NULL

Building: variable length character string length 25 NOT NULL)

Primary Key Housing_ID

Foreign Key Student_Number References

STUDENT(Student_Number)

ON DELETE NO CHECK

ON UPDATE

Which integrity constraint is violated in this relation definition?

Options:

A.

Entity integrity

B.

Domain constraint

C.

Referential integrity

D.

Enterprise constraint

Question 15

Consider the Information Engineering diagram in the exhibit showing a conceptual data model of the relations BUILDING and RESIDENT. What is the next step in refining the data model?

Options:

A.

Create intermediate entities.

B.

Create a logical data model.

C.

Resolve many-to-manyrelationships.

D.

Identify and resolve complex relationships.

Question 16

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 17

To create a view, what are the minimal privileges that a user must have for the relations used to make the view?

Options:

A.

GRANT

B.

REVOKE

C.

SELECT

D.

CREATE VIEW

Question 18

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

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