Database Systems Chapter 5 (Lecture) Normalization

A table that has all key attributes defined, no repeating groups, and an identified primary key is said to be in ________.

1NF

All relational tables satisfy the ______ requirements.

1NF

A table that is in 2NF and contains no transitive dependencies is said to be in ________.

3NF

A table where every determinant is a candidate key is said to be in ________.

BCNF

________ yields increase performance in a database system.

denormalization

A ________ key is an artificial PK introduced by the designer with the purpose of simplifying the assignment of primary keys to tables.

surrogate

____ keys are usually numeric, they are often automatically generated by the DBMS, they are free of semantic content (they have no special meaning), and they are usually hidden from the end users.

surrogate

A 2NF table can exhibit ________ dependency.

transitive

Assume you have the following table: |JOB_CODE | JOB_DESCRIPTION | JOB_CHG_HOUR | | 511 | Programmer | $35.75 | | 512 | Programmer | $35.75 | How might you eliminate the possibility of duplicate entries for JOB_DESCRIPTION?

use a unique index for JOB_DESCRIPTION

Unnormalized tables yield no simple strategies for creating virtual tables, which are also known as ________.

views

From a structural point of view, 2NF is better than ________.

1NF

Assume the following table definition and dependencies. How many tables would be produced by converting to BCNF? TABLE(STU_ID, STAFF_ID, CLASS_CODE, ENROLL_GRADE) STU_ID + STAFF_ID --> CLASS_CODE, ENROLL_GRADE CLASS_CODE --> STAFF_ID

2

A table that is in 1NF and includes no partial dependencies is said to be in ________.

2NF

A table whose primary key consists of only a single attribute is automatically in ________ when it is in 1NF.

2NF

Given the following table definition, how many tables would be produced by the conversion to 2NF? 1NF (PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOURS, ASSIGN_HOURS) with PROJ_NUM & EMP_NUM AS PRIMARY KEY IDENTIFIERS

3

A table in ________ contains no transitive dependencies.

3NF

A table where all attributes are dependent on the primary key and are independent of each other, and no row contains two or more multivalued facts about an entity, is said to be in ________.

3NF

Assume the following table definition and dependencies. In which normal form is this table? TABLE(A, B, C, D) A + B --> C, D C --> B

3NF

For most business database design purposes, ________ is as high as you need to go in the normalization process.

3NF

Most designers consider Boyce-Codd normal form (BCNF) as a special case of ________.

3NF

Tables in ________ will perform suitably in business transactional databases.

3NF

Which of the following normal forms is mostly of theoretical interest?

DKNF

Which of the following would be the best name for a column representing the charges per hour in a table named JOB?

JOB_CHG_HOUR

Assume you have the following table definition: 1NF (PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOURS, HOURS). Identify a transitive dependency.

JOB_CLASS --> CHG_HOURS

In a normalized database, what is represented by each table?

a single subject

A table that displays data redundancies yields ________.

anomalies

Improving ________ leads to more flexible queries.

atomicity

The normalization process involves assigning ________ to tables based on the concept of determination.

attributes

BCNF can be violated only if the table contains more than one ________ key.

candidate

When a table contains only one ________ key, the 3NF and the BCNF are equivalent.

candidate

A ________ key makes it more difficult to write search routines.

composite primary

The conflicts between design efficiency, information requirements, and performance are often resolved through ________.

compromises that may include denormalization

In an ideal (database design) world, the level of desired granularity is determined at the ________ design or at the requirements gathering phase.

conceptual

Data redundancy produces ________.

data integrity problems

Lower normalization forms occur (and are even required) in specialized databases known as _______.

data warehouses

Storing a(n) ________ attribute in a table makes it easy to write the application software to produce the desired results.

derived

A ________ is any attribute whose value determines other values within a row.

determinant

In BCNF, every ________ in a table is a candidate key.

determinant

Dependencies can be depicted with the help of a dependency ________.

diagram

In a real-world environment, we must strike a balance between design integrity and ________.

flexibility

____ refers to the level of detail represented by the values stored in a table's row.

granularity

A table is in fourth normal form (4NF) if ______.

it is in 3NF and has no independent multivalued dependencies

Another name for a prime attribute is a(n) ________ attribute.

key

When designing a database you should _______.

make sure entities are in normal form before table structures are created

A(n) ________ attribute is not part of a key.

nonprime

Identification of the ________ will let you know where you are in the normalization process.

normal form

Dependencies based on only a part of a composite primary key are called ________ dependencies.

partial

An attribute that is part of a key is known as a(n) ________ attribute.

prime

The ________ model views the data as part of a table or collection of tables in which all key values must be identified.

relational

To implement a surrogate key, MS Access uses an AutoNumber data type, MS SQL Server uses an identity column, and Oracle uses a(n) ________ object.

sequence