Database Management Chapter 5

determinant

Any column (or collection of columns) that determines another column

Functional Dependence

Column B is functionally dependent on column A Each value for A is associated with exactly one value of B A → B A functionally determines B

interrelation constraint

a condition that involves two or more relations

A(n) ____ is a column or collection of columns on which all columns in the table are functionally dependent.

candidate key

Alternate keys

candidate keys not chosen as primary key

An alternate key is a ____.

column that could be a primary key but was not chosen

Candidate key

column(s) on which all columns in table are functionally dependent

Repeating group

multiple entries for a single record

third normal form (3NF)

when it is in second normal form and the only determinants it contains are candidate keys

fourth normal form (4NF)

when it is in third normal form and there are no multivalued dependencies.

A table that is in first normal form is better than one that is in second normal form.

False

Functional dependencies can be determined by looking at sample data.

False

Tables that are in second normal form do not update anomalies.

False

The possibility of data dependence is one of the four categories of update anomalies.

False

Normalization process

Identifying potential problems, called update anomalies, in the design of a relational database Methods for correcting these problems

multivalued dependence

In a table with columns A, B, and C, there is a ____ of column B on column A when each value for A is associated with a specific collection of values for B and, further, this collection is independent of any values for C.

To convert a table to fourth normal form, split the third normal form table into separate tables, each containing the column that ____ the others.

Multidetermines

The ____ is a column (or collection of columns) A such that all other columns are functionally dependent on A and no subcollection of the columns in A has this property.

Primary key

Normalization Goals

Table in first normal form better than table not in first normal form Table in second normal form better than table in first normal form, and so on Goal: new collection of tables that is free of update anomalies

A column is a nonkey attribute when it is not part of the primary key.

True

Removal of repeating groups is the starting point in the quest to create tables that are as free of problems as possible.

True

The normalization process used to convert a relation or collection of relations to an equivalent collection of third normal form tables is a crucial part of the database design process.

True

The primary key in a table is a determinant but candidate keys are not determinants.

True

A table that contains a repeating group is called a(n) ____.

Unnormalized Relation

When is a Table in first normal form (1NF)?

When it does not contain repeating groups.

If there is more than one possible choice for the primary key, and one of the possibilities is chosen to be the primary key, the other choices are referred to as ____.

alternate keys

Partial dependencies

are dependencies only on a portion of the primary key

Dependency diagram

arrows indicate all functional dependencies Arrows above boxes: normal dependencies Arrows below boxes: partial dependencies

From all the ____ keys, one is chosen to be the primary key.

candidate Key

If the primary key of a table contains only a single column, the table is automatically in third normal form.

false

A table is in fourth normal form when it is in third normal form and there are no ____.

multivalued dependence

Second normal form can be defined as a table that is in first normal form but that contains no ____.

no nonkey column is dependent on only a portion of the primary key

Another name for a nonkey column is a ____.

nonkey attribute

A column is a nonkey column if it is ____.

not part of primary key

Nonkey column (nonkey attribute)

not part of primary key

update anomalies

potential problems (Update, Inconsistent data, Additions, and Deletions)

Partial dependencies are dependencies on only a portion of the ____.

primary key

The definition for ____ also defines a candidate key.

primary key

A table is in first normal form if it does not contain ____.

repeating groups

The conversion of an unnormalized table to first normal form requires the removal of ____.

repeating groups

Normal form

table has desirable properties First normal form (1NF) Second normal form (2NF) Third normal form (3NF) Fourth normal form (4NF)

By splitting relations to achieve third normal form tables, you create the need to express interrelation constraints.

true

Converting to third normal form always avoids the problems related to dependencies.

true

If B is functionally dependent on A, you can also say that B functionally determines A.

true

In a dependency diagram, the arrows below the boxes indicate the partial dependencies.

true

To correct update anomalies in a database, tables must be converted into various types of normal forms.

true

Potential problems in the design of a relational database are known as ____.

update anomalies

Second Normal Form

when it is in first normal form and no nonkey column is dependent on only a portion of the primary key.

____ contains a repeating group.

1NF

____ normal form has an additional condition that the only determinants the table contains are candidate keys.

3NF

If B (an attribute) is functionally dependent on A, we can also say that ____.

A functionally determines B

Unnormalized relation

A relation (table) that contains a repeating group

The fact that column B is functionally dependent on column A can be written as ____.

A--->B