Chapter 6 Review Questions PDF

Preview

Summary

Download Chapter 6 Review Questions PDF

Description

ACIT 1630 Relational Database Design and SQL Review Questions Chapter 6

Answer each of the following questions labeling your answers clearly. Save your work in the Desire2Learn Lesson 5 Drop box.

1. What is normalization? Normalization is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. Normalization does not eliminate data redundancy; instead, it produces the carefully controlled redundancy that lets us properly link database tables.

2. 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. 3. When is a table in 2NF? A table is in second normal form (2NF) when it is in 1NF and includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key. (But it is possible for a table in 2NF to exhibit transitive dependency; that is, one or more attributes may be functionally dependent on nonkey attributes.) 4. When is a table in 3NF? A table is in 3NF when it is in 2NF and it contains no transitive dependencies. 5. What is a partial dependency? With what normal form is it associated? A partial dependency is a dependency that is based on only a part of a composite primary key. Partial dependencies are associated with the second normal form (2NF.) 6. 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 7. Define and discuss the concept of transitive dependency. A transitive dependency exists when one or more attributes may be functionally dependent on nonkey attributes. This dependency is associated with a table in second normal form (2NF.) 8.

How do you remove 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

9.

Remove the transitive dependency of the following table:

10. 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. 11. 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.

12. 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.)

13. Using the INVOICE table structure shown below, do the following: Attribute Name INV_NUM PROD_NUM SALE_DATE PROD_LABEL VEND_CODE VEND_NAME QUANT_SOLD PROD_PRICE

Sample Value Sample Value 211347 211347 AA-E3422QW QD-300932X 15-Jan-2016 15-Jan-2016 Rotary sander 0.25-in. drill bit 211 211 NeverFail, Inc. NeverFail, Inc. 1 8 $49.95 $3.45

Sample Value 211347 RU-995748G 15-Jan-2016 Band saw

Sample Value Sample Value 211348 211349 AA-E3422QW GH-778345P 15-Jan-2016 16-Jan-2016 Rotary sander Power drill

309 BeGood, Inc. 1 $39.99

211 NeverFail, Inc. 2 $49.95

157 ToughGo, Inc. 1 $87.75

a. Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.) 1NF (INV_NUM, PROD_NUM, SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, QUANT_SOLD, PROD_PRICE) Partial Dependencies: (INV_NUM → SALE_DATE) (PROD_NUM → PROD_LABEL, VEND_CODE, PROD_PRICE) Transitive Dependency: (VEND_CODE → VEND_NAME)

b. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. INVOICE (INV_NUM, SALE_DATE) PRODUCT (PROD_NUM, PROD_LABEL, VEND_CODE, PROD_PRICE) INV_LINE (INV_NUM, PROD_NUM, QUANT_SOLD) Transitive Dependency: (VEND_CODE → VEND_NAME)

c. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created. (INV_NUM, PROD_NUM, SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, QUANT_SOLD, PROD_PRICE)

d. Draw the Crow’s Foot ERD....