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

Second normal form

First normal form

Boyce-Codd normal form

Third normal form

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

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

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

Theft

Privacy

Availability

Confidentiality

Durability

Consistency

Isolation

Atomicity

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

The intersection oftwo relations

ACartesian product of two relations

Arestricted Cartesian product of two relations

The Cartesian product oftwo union-compatible relations

Only an update anomaly would occur.

An insertion anomaly would occur.

A deletion anomaly would occur.

An update anomaly and a deletion anomaly would occur.

Weak

Strong

Foreign

Primary

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.

A recursive relationship

A many-to-many relationship

A one-to-one relationship

A one-to-many relationship

Data Query Language

Database Entity Language

Data Definition Language

Data Manipulation Language

SELECT Activity FROM Recreation;

SELECT DISTINCT Activity FROM Recreation;

SELECT Activity FROM Recreation

WHERE NOT LIKE Activity;

SELECT Activity FROM Recreation

WHERE DISTINCT Activity;

0002, James, Smith

0002, James, Smith, 10-25-76

First_Name, James, Last_Name, Smith

Emp_ID, 0002, First_Name, James, Last_Name, Smith

GRANT

REVOKE

SELECT

CREATE VIEW

SELECT *

FROM Employee;

SELECT ID, Last_Name

FROM Employee;

WHERE ID = 0004;

SELECT *

FROM Employee

WHERE Dept_ID = 022;

SELECT *

FROM Employee

WHERE Dept_ID = 022;

A normalized set of data applicable to a particular relation

A combination of attributes for a relation

A definition of permissible values for one or more attributes

A set of permissible values for one or more relations