CH6 Review Questions
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 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.
When is a table in 3NF?
when it is in 2NF and it contains no transitive dependencies.
When is a table in 2NF?
...when it is in 1NF and it includes no partial dependencies. However, a table in _____ may still have transitive dependencies, i.e., dependencies based on attributes that are not part of the primary key.
When is a table in BCNF?
...when it is in 3NF and every determinant in the table is a candidate key. For example, if the table is in 3NF and it contains a nonprime attribute that determines a prime attribute, the BCNF requirements are not met. This description clearly yields the following conclusions: If a table is in 3NF and it contains only one candidate key, 3NF and BCNF are equivalent. BCNF can be violated only if the table contains more than one candidate key. Putting it another way, there is no way that the BCNF requirement can be violated if there is only one candidate key.
Suppose that someone tells you that an attribute that is part of a composite primary key is also a candidate key. How would you respond to that statement?
This argument is incorrect if the composite PK contains no redundant attributes. If the composite primary key is properly defined, all of the attributes that compose it are required to identify the remaining attribute values. By definition, a candidate key is one that can be used to identify all of the remaining attributes, but it was not chosen to be a PK for some reason. In other words, a candidate key can serve as a primary key, but it was not chosen for that task for one reason or another. Clearly, a part of a proper ("minimal") composite PK cannot be used as a PK by itself. A candidate key can be described as a superkey without redundancies, that is, a minimal superkey. Using this distinction, note that a STUDENT table might contain the composite key.
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.)
Define and discuss the concept of transitive dependency
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.
What is a surrogate key, and when should you use one?
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.
What is normalization?
the process for assigning attributes to entities. Properly executed, the normalization process eliminates uncontrolled data redundancies, thus eliminating the data anomalies and the data integrity problems that are produced by such redundancies. does not eliminate data redundancy; instead, it produces the carefully controlled redundancy that lets us properly link database tables.
When is a table 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 ___________ 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.
What is a partial dependency? With what normal form is it associated?
when an attribute is dependent on only a portion of the primary key. This type of dependency is associated with 1NF.
