1D0-541 CIW v5 Database Design Specialist Questions and Answers

A system catalog containing user data

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

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

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

SELECT * FROM

Registration WHERE

Course_Code = #

SELECT * FROM

Registration WHERE

Course_Code LIKE _

SELECT* FROM

Registration WHERE

Course_Code LIKE %

SELECT * FROM

Registration WHERE

Course Code = %

SELECT *

FROM Dept1_Parts

EXCEPT

(SELECT Part_ID

FROM Dept2_Parts);

SELECT *

FROM Dept1_Parts

MINUS

(SELECT Part_ID

FROM Dept2_Parts);

SELECT *

FROM Dept1_Parts

DIFFERENCE

(SELECT Part_ID

FROM Dept2_Parts);

SELECT *

FROM Dept1_Parts

DIFFERENCE

(SELECT Part_ID

FROM Dept2_Parts);

Information Engineering (IE) data model

Corporate data model

Database requirements model

ERD model

Two records

Three records

Four records

Five records

UPDATE Sales_Rep_No

IN Orders

SET(Sales_Rep_No = 110

WHERE Sales_Rep_No = 108);

UPDATE Orders

SET Sales_Rep_No = 110

WHERE Sales_Rep_No = 108;

UPDATE Orders

SET Sales_Rep_No = 110;

UPDATE Orders

WHERE Sales_Rep_No = 108

SET Sales_Rep_No = 110;

Data Markup Language

Data Control Language

Data Formatting Language

Data Manipulation Language

Union and join

Selection and projection

Intersection and difference

Cartesian product and intersection

m:m

m:n

1:1

1:n

The ordering of attributes in a table is significant.

An attribute contains values from multiple domains.

An attribute name can be used only once per table.

An attribute name can be used only once in a relational database system with multiple tables.

Employee. Dept_ID

Dept_Mngr

Dept_Name

Department. Dept_ID

SELECT * FROM Customers

WHERE Satisfaction_Rate > 80

OR Sales_Office = Atlanta

SELECT * FROM Customers

WHERE Satisfaction_Rate < = 80

AND Sales_Office = Atlanta

SELECT * FROM Customers

WHERE Satisfaction_Rate > = 80;

SELECT * FROM Customers

WHERE Satisfaction_Rate > = 80

AND NOT Sales Office = Atlanta

Two-tier using thin client

Three-tier using fat client

Three-tier using thin client

Centralized mainframe with terminal client

No normal form

Second normal form

First normal form

Third normal form

DELETE * FROM Customers

WHERE Sales_Office = New York;

DELETE FROM Customers WHERE Sales_Office = ew

York?WHERE Sales_Office = ?ew York?

DELETE * FROM Customers WHERE Sales_Office = ew

York?WHERE Sales_Office = ?ew York?

DELETE FROM Customers WHERE Sales_Office NOT LIKE ew York? WHERESales_Office NOT LIKE ?ew York?

A set of normalized relations

A reviewed entity-relationship (ER) model

An entity-relationship (ER) model with no redundant data

A set of denormalized relations

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

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

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

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

Repetition of data, application program flexibility, and data centralization

Incompatibility of files, tabular data structures, and data dependence

Separation of data, repetition of data, and data independence

Application program inflexibility, data dependence, and separation of data

Entity

Domain

Primary key

Attribute group

1NF

1NF and 2NF

1NF, 2NF and 3NF

1NF, 2NF, 3NF and BCNF

SELECT Sales_Rep_No(108)

FROM Orders;

INSERT INTO Orders

VALUES(Sales_Rep_No = 108)

WHERE Sales_Rep_No = NULL;

SELECT'FROM Orders

WHERE Sales_Rep_No = 108;

SELECT'FROM Orders

WHERE Sales_Rep_No = ?08?

6

20

4

25

Activity_Fee is a determinant of Activity.

Activity_Fee is partially dependent on the key.

The table contains a transitive dependency.

Activity_Fee is a determinant of Activity and Student_ID.

A recursive relationship

A many-to-many relationship

A one-to-one relationship

A one-to-many relationship

Metadata

Data model

Table data

Metafile

Backups

User views

Encryption

Integrity controls

Union

Intersection

Natural join

Cartesian product

Union

Difference

Projection

Intersection

CREATE TABLE BUILDING (

Building_ID NOT NULL PRIMARY KEY,

Bldg_Name, Location, Room_Count);

CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY

KEY, Bldg_Name, Location, Room_Count, FOREIGN KEY

Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE BUILDING (Building_ID

INTEGER NOT NULL PRIMARY KEY, Bldg_Name

VARCHAR (20), Location VARCHAR (50),

CREATE TABLE BUILDING (Building_ID

INTEGER NOT NULL PRIMARY KEY, Bldg_Name

VARCHAR (20), Location VARCHAR (50),

Room_Count INTEGER CHECK (Room_Count >

Room_Count INTEGER CHECK (

-1 And Room_Count < 601));

Room_Count > -1 Or Room_Count < 601));

A composite key is a primary key that consists of the first two attributes of a relation.

A composite key is a primary or foreign key defined by its parent keys.

A composite key is a foreign key that consists of the same attributes as the primary key from a related table.

A composite key is a primary or foreign key that consists of two or more attributes of a relation.

The user accesses a database server using a terminal.

The user interface, data-processing logic, database access and data validation functions are performed on a mainframe server.

The user interface and data validation functions are performed by the client whereas the data-processing logic is performed on a server.

The user interface and data-processing logic are performed by the client whereas the server handles database access and data validation functions.

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

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

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

Modifications 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).

Data Query Language

Database Entity Language

Data Definition Language

Data Manipulation Language

One-to-many and recursive

Complex, recursive, and many-to-many

Redundant, recursive, and one-to-many

One-to-many and one-to-one