CH 6 Normalization

What is normalization?

Converting poorly structured tables into two or more well-structured tables Each table should only have 1 "theme"

How would you describe a condition in which one attribute is dependent on another attribute when neither attribute is part of the primary key?

This condition is known as a transitive dependency. A transitive dependency is a dependency of one nonprime attribute on another nonprime attribute. (The problem with transitive dependencies is that they still yield data anomalies.)

What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?

The most common anomalies considered when data redundancy exists are: update anomalies, addition anomalies, and deletion anomalies. All these can easily be avoided through data normalization. Data redundancy produces data integrity problems, caused by the fact that data entry failed to conform to the rule that all copies of redundant data must be identical.

Define and discuss the concept of transitive dependency.

Transitive dependency is a condition in which an attribute is dependent on another attribute that is not part of the primary key. This kind of dependency usually requires the decomposition of the table containing the transitive dependency. To remove a transitive dependency, the designer must perform the following actions: Place the attributes that create the transitive dependency in a separate table. Make sure that the new table's primary key attribute is the foreign key in the original table.

Why is a table whose primary key consists of a single attribute automatically in 2NF when it is in 1NF?

A dependency based on only a part of a composite primary key is called a partial dependency. Therefore, if the PK is a single attribute, there can be no partial dependencies.

What is a partial dependency? With what normal form is it associated?

A partial dependency exists when an attribute is dependent on only a portion of the primary key. This type of dependency is associated with 1NF.

What is a surrogate key, and when should you use one?

A surrogate 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.

When is a table in 1NF?

A table is in 1NF when all the key attributes are defined (no repeating groups in the table) and when all remaining attributes are dependent on the primary key. However, a table in 1NF still may contain partial dependencies, i.e., dependencies based on only part of the primary key and/or transitive dependencies that are based on a non-key attribute.

When is a table in 2NF?

A table is in 2NF when it is in 1NF and it includes no partial dependencies. However, a table in 2NF may still have transitive dependencies, i.e., dependencies based on attributes that are not part of the primary key.

When is a table in 3NF?

A table is in 3NF when it is in 2NF and it contains no transitive dependencies.

When is a table in BCNF?

A table is in Boyce-Codd Normal Form (BCNF) when it is in 3NF and every determinant in the table is a candidate key. There is no way that the BCNF requirement can be violated if there is only one candidate key.

A table is in ______ normal form when it is in ______, and there are no transitive dependencies.

A table is in third normal form (3NF) when it is in second normal form (2NF) and there are no transitive dependencies