New Year Sale Special Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: xmas50

CIW 1D0-541 - CIW v5 Database Design Specialist

CIW 1D0-541 Premium Access     Download Demo    
Page: 3 / 4
Total 124 questions

With regard to databases, what is normalization?

A.

The process of reducing the cardinality of a relation

B.

The process of organizing and refining relations

C.

The process of duplicating data to reduce the number of tables

D.

The process of limiting data stored in a table to a specific range of values

Using the Customer and Sales_Rep relations shown in the exhibit, you must determine a relational algebraic expression that will result in the following relation: Which of the following relational algebraic expressions would result in this relation? Using the Customer and Sales_Rep relations shown in the exhibit, you must determine a relational algebraic expression that will result in the following relation: Which of the following relational algebraic expressions would result in this relation?

A.

Customer X Sales_Rep

B.

(Sales_Rep.Sales_Rep_No = Customer.Sales_Rep_No (Sales_Rep X Customer))

C.

(Customer X Sales_Rep) л Sales_Rep.Sales_Rep_No = Customer.Sales_Rep_No

D.

Customer.Sales_Rep_No = Sales.sales_Rep_No (Customer X Sales_Rep)

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?

A.

Information Engineering (IE) data model

B.

Corporate data model

C.

Database requirements model

D.

ERD model

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

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.

Consider the relational database shown in the exhibit.

What is the foreign key in this database?

A.

Employee. Dept_ID

B.

Dept_Mngr

C.

Dept_Name

D.

Department. Dept_ID

What is a data dictionary?

A.

A system catalog containing user data

B.

An area of the database that is directly accessible by the user

C.

Data that is stored in tables and is only accessible by the DBMS

D.

Metadata that is stored in tables and is only accessible by the DBMS

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?

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

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

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

Which relational algebraic operation is used to select specific columns (attributes) from a relation?

A.

Union

B.

Difference

C.

Projection

D.

Intersection

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?

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