Chapter 6 solutions PDF

Preview

Summary

Answers to Review Questions What is normalization? Normalization is 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 ...

Description

Chapter 6 Normalization of Database Tables

Answers to Review Questions 1. What is normalization? Normalization is 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. 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 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. 4. When is a table in 3NF? A table is in 3NF when it is in 2NF and it contains no transitive dependencies. 5. 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. 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. (Reference the text's Figure 6.8 to support this discussion.)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.

183

Chapter 6 Normalization of Database Tables 6. Given the dependency diagram shown in Figure Q6.6, answer items 6a-6c:

FIGURE Q5.6 Dependency Diagram for Question 6

C1

C2

C3

C4

C5

a. Identify and discuss each of the indicated dependencies. C1  C2 represents a partial dependency, because C2 depends only on C1, rather than on the entire primary key composed of C1 and C3. C4  C5 represents a transitive dependency, because C5 depends on an attribute (C4) that is not part of a primary key. C1, C3  C2, C4, C5 represents a set of proper functional dependencies, because C2, C4, and C5 depend on the primary key composed of C1 and C3. b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table. The normalization results are shown in Figure Q6.6b.

Figure Q6.6b The Dependency Diagram for Question 6b Table 1

C1

Primary key: C1 Foreign key: None Normal form: 3NF

C2

Table 2 C1

C3

C4

C5

Primary key: C1 + C3 Foreign key: C1 (to Table 1) Normal form: 2NF, because the table exhibits the transitive dependencies C4 C5

184

Chapter 6 Normalization of Database Tables c. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table. The normalization results are shown in Figure Q6.6c.

Figure Q6.6c The Dependency Diagram for Question 6c

C1

C1

C3

C4

C2

Table 1 Primary key: C1 Foreign key: None Normal form: 3NF

C4

Table 2 Primary key: C1 + C3 Foreign key: C1 (to Table 1) C4 (to Table 3) Normal form: 3NF

C5

Table 3 Primary key: C4 Foreign key: None Normal form: 3NF

7. The dependency diagram in Figure Q6.7 indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book.

Figure Q6.7 Book royalty dependency diagram

a. Based on the dependency diagram, create a database whose tables are at least in 2NF, showing the dependency diagram for each table.

185

Chapter 6 Normalization of Database Tables The normalization results are shown in Figure Q6.7a.

Figure Q6.7a The 2NF normalization results for Question 7a.

b. Create a database whose tables are at least in 3NF, showing the dependency diagram for each table. The normalization results are shown in Figure Q6.7a.

Figure Q6.7b The 3NF normalization results for Question 7b.

186

Chapter 6 Normalization of Database Tables

8. The dependency diagram in Figure Q6.8 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table.

Figure Q6.8 Prescription dependency diagram

187

Chapter 6 Normalization of Database Tables The normalization results are shown in Figure Q6.8a.

Figure Q6.8a The 2NF normalization results for Question 8.

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

188

Chapter 6 Normalization of Database Tables 11. 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. Figure Q6.9 shows an example of a transitive dependency removal.

Figure Q6.9 Transitive Dependency Removal Original table

INV_NUM

INV_DATE

INV_AMOUNT

CUS_NUM

CUS_ADDRESS

CUS_PHONE

Transitive Dependencies

INV_NUM

INV_DATE

INV_AMOUNT

CUS_NUM New Tables

CUS_NUM

CUS_ADDRESS CUS_PHONE

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

189

Chapter 6 Normalization of Database Tables 13. 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. 14. 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.) 15. 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. More formally, you learned in Chapter 3, ―The Relational Database Model,‖ Section 3.2, that 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 STU_NUM, STU_LNAME This composite key is a superkey, but it is not a candidate key because STU_NUM by itself is a candidate key! The combination STU_LNAME, STU_FNAME, STU_INIT, STU_PHONE might also be a candidate key, as long as you discount the possibility that two students share the same last name, first name, initial, and phone number. If the student’s Social Security number had been included as one of the attributes in the STUDENT table—perhaps named STU_SOCSECNUM—both it and STU_NUM would have been candidate keys because either one would uniquely identify each student. In that case, the selection of STU_NUM as the primary key would be driven by the designer’s choice or by end-user requirements. Note, incidentally, that a primary key is a superkey as well as a candidate key. 16. A table is in ___ 3rd___ normal form when it is in ___ 2nd normal form___ and there are no transitive dependencies. (See the discussion in Section 5.3.3, ―Conversion to Third Normal Form.‖

190

Chapter 6 Normalization of Database Tables

Problem Solutions 1. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a dependency diagram that is in at least 3NF. An initial dependency diagram depicting only the primary key dependencies is shown in Figure P6.1a below.

Figure P6.1a Initial dependency diagram for Problem 1.

There are no composite keys being used, therefore, by definition, there is not an issue with partial dependencies and the entities are already in 2NF. Based on the descriptions of the attributes, it appears that the patient name, phone number, and address can be determined by the patient id number. Therefore, the following transitive dependency can be determined. App_PatientID  (App_Name, App_Phone, App_Street, App_City, App_State, App_Zip) As discussed in the chapter, ZIP_Codes can be used to determine a city and state; therefore, we also have the transitive dependency: App_Zip  App_City, App_State Figure P6.1b depicts the dependency diagram with these transitive dependencies included.

Figure P6.1b Revised dependency diagram for Problem 1.

191

Chapter 6 Normalization of Database Tables

Since the first transitive dependency completely encloses the second transitive dependency, it is appropriate to resolve the first transitive dependency before resolving the second. Figure P6.1c shows the results of resolving the first transitive dependency.

Figure P6.1c Resolving the first transitive dependency

Finally, the second and final transitive dependency can now be resolved as shown in the final dependency diagram in Figure P6.1d.

Figure P6.1d Final dependency diagram for Problem 1

192

Chapter 6 Normalization of Database Tables

Note that at this time we have resolved all of the transitive dependencies. Decisions on whether or not to denormalize, and perhaps not remove the final transitive dependency, have yet to be made. Also, the structures have not yet had the benefit of additional design modifications such as achieving proper naming conventions for the attributes in the new tables. However, creating the fully normalized structures is an important set toward making informed decisions about the compromises in the design that we may choose to make. 2. Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.2 into a dependency diagram that is in at least 3NF. An initial dependency diagram depicting only the primary key dependencies is shown in Figure P6.2a below.

Figure P6.2a Initial dependency diagram for Problem 2.

193

Chapter 6 Normalization of Database Tables

Based on the descriptions of the attributes given, the following partial dependency can be determined: Pres_SessionNum  (Pres_Date, Pres_Room) Also, the following transitive dependencies can be determined: Pres_AuthorID  (Pres_FName, Pres_LName) Figure P6.2b shows the revised dependency diagram including the partial and transitive dependencies.

Figure P6.2b Revised dependency diagram for Problem 2

Resolving the partial dependency to achieve 2NF yields the dependency diagram shown in Figure P6.2c.

194

Chapter 6 Normalization of Database Tables

Figure P6.2c 2NF dependency diagram for Problem 2

Finally, the transitive dependency is resolved to achieve the 3NF solution shown in the final dependency diagram in Figure P6.2d.

Figure P6.2d Final dependency diagram for Problem 2

195

Chapter 6 Normalization of Database Tables

196

Chapter 6 Normalization of Database Tables

3. Using the INVOICE table structure shown in Table P6.3, do the following:

Table P6.3 Sample INVOICE Records Attribute Name INV_NUM PROD_NUM SALE_DATE PROD_LABEL VEND_CODE VEND_NAME QUANT_SOLD PROD_PRICE

Sample Value 211347 AA-E3422QW 15-Jan-2014 Rotary sander

Sample Value 211347 QD-300932X 15-Jan-2014 0.25-in. drill bit 211 211 NeverFail, Inc. NeverFail, Inc. 1 8 $49.95 $3.45

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

Sample Value 211348 AA-E3422QW 15-Jan-2014 Rotary sander

Sample Value 211349 GH-778345P 16-Jan-2014 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.) The solutions to both problems (3a and 3b) are shown in Figure P6.3a.

NOTE We have combined the solutions to Problems 3a and 3b to let you illustrate the start of the normalization process within a single PowerPoint slide. Students generally seem to have an easier time understanding the normalization process if they can compare the normal forms directly. We will continue to use this technique for several of the initial normalization decompositions … if the available PowerPoint slide space permits it.

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.

NOTE You can assume that any given product is supplied by a single vendor but a vendor can supply many products. Therefore, it is proper to conclude that the following dependency exists: PROD_NUM → PROD_DESCRIPTION, PROD_PRICE, VEND_CODE, VEND_NAME (Hint: Your actions should produce three dependency diagrams.)

197

Chapter 6 Normalization of Database Tables

Figure P6.3a The Dependency Diagrams for Problems 3a and 3b

198

Chapter 6 Normalization of Database Tables

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. To illustrate the effect of Problem 3's complete decomposition, we have shown Problem 3a's dependency diagram again in Figure P6.3c.

Figure P6.3c The Dependency Diagram for Problem 3c

199

Chapter 6 Normalization of Database Tables d. Draw the Crow’s Foot ERD.

NOTE Emphasize that, because the dependency diagrams cannot show the nature (1:1, 1:M, M:N) of the relationships, the ER Diagrams remain crucial to the design effort. Complex design is impossible to produce successfully without some form of modeling, be it ER, Semantic Object Modeling, or some other modeling methodology. Yet, as the preceding decompositions demonstrate, the dependency diagrams are a valuable addition to the designer's toolbox. (Normalization is likely to suggest the existence of entities that may not have been considered during the modeling process.) And, if information or transaction management issues require the existence of attributes that create other than 3NF or BCNF conditions, the proper dependency diagrams will at least force awareness of these conditions. The invoicing ERD, accompanied by its relational diagram, is shown in Figure P6.3d. (The relational diagram only includes the critical PK and FK components, plus a few sample attributes, for space considerations.)

Figure P6.3d The Invoicing ERD and Its (Partial) Relational Diagram

Crow’s Foot Invoicing ERD

Invoicing Relational Diagram, Sample Attributes INVOICE INV_NUM INV_DATE

LINE 1

M

INV_NUM

PRODUCT 1 M

1

PROD_NUM

PROD_NUM

PROD_DESCRIPTION

NUM_SOLD

PROD_PRICE VEND_CODE

200

VENDOR VEND_CODE VEND_NAME

M

Chapter 6 Normalization of Database Tables

4. Using the STUDENT table structure shown in Table P6.4, do the following:

Table P6.4 Sample STUDENT Records Attribute Name

Sample Value Sample Value Sample Value Sample Value Sample Value

STU_NUM STU_LNAME STU_MAJOR DEPT_CODE DEPT_NAME DEPT_PHONE COLLEGE_NAME ADVISOR_LNAME ADVISOR_OFFICE ADVISOR_BLDG ADVISOR_PHONE STU_GPA STU_HOURS STU_CLASS

211343 Stephanos Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 3.87 75 Junior

200128 Smith Accounting ACCT Accounting 4356 Business Admin Grastrand T201 Torre Building 2115 2.78 45 Sophomore

199876 Jones Marketing MKTG Marketing 4378 Business Admin Gentry T228 Torre Building 2123 2.31 117 Senior

199876 Ortiz Marketing MKTG Marketing 4378 Business Admin Tillery T356 Torre Building 2159 3.45 113 Senior

223456 McKulski Statistics MATH Mathematics 3420 Arts & Sciences Chen J331 Jones Building 3209 3.58 87 Junior

a. Write the relational schema, draw its dependency diagram, and identify all dependencies, including all transitive dependencies. The dependency diagram for problem 4a is shown in Figure P6.4a.

Figure P6.4a The Dependency Diagram for Problem 4a

201

Chapter 6 Normalization of Database Tables

STU_NUM STU_LNAME

STU_MAJOR DEPT_CODE DEPT_NAME DEPT_PHONE COLLEGE_NAME

Transitive Dependencies

ADV_LASTNAME ADV_OFFICE ADV_BUILDING ADV_PHONE STU_CLASS STU_GPA STU_HOURS

Transitive Dependency

Transitive Dependency

Note 1: The ADV_LASTNAME is not a determinant of ADV_OFFICE or ADV_PHONE, because there are (potentially) many advisors who have the same last name. Note 2: If a department has only one ...