CIW 1D0-541 Exam Practice Test Instant Access

Question 1

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?

ASELECT *
FROM Employee;

BSELECT ID, Last_Name
FROM Employee;
WHERE ID = 0004;

CSELECT *
FROM Employee
WHERE Dept_ID = 022;

DSELECT *
FROM Employee
WHERE Dept_ID = 022;

Answer : C

Question 2

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?

AEntity integrity

BDomain constraint

CReferential integrity

DEnterprise constraint

Answer : C

Question 3

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?

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

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

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

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

Answer : D

Question 4

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

What is the output of this SQL statement?

AOption A

BOption B

COption C

DOption D

Answer : C

Question 5

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?

ALock

BDeadlock

CTimestamp

DBatch

Answer : A

Question 6

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

What is the output of this SQL statement?

AOption A

BOption B

COption C

DOption D

Answer : D

Question 7

Your enterprise must decide whether to use a database management system. Which of the

following best describes the overall functionality of a DBMS?

AA DBMS provides the ability to control data access using Data Manipulation Language.

BA DBMS provides the ability to maintain databases while providing increased security for the
database.

CA DBMS allows users to access the database while allowing only the database administrator to
define user views.

DA DBMS provides the ability to define, create and maintain databases while providing
controlled access to databases.

Answer : D

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