The purpose of this assignment is to assess your ability to translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical model.
computer science
Description
The purpose of this assignment is to assess your ability to translate a relational data model into efficient database structures, including knowing when and how to denormalize the logical model. Continue with assignment 3-2 on “Identifying Keys and Functional Dependencies” where you did the following for the INVOICE table below: 1) Eliminated the repeating groups (there weren’t any), 2) identified the primary key or composite keys, and 3) identified all dependencies (partial functional and transitive) and drew a 1NF dependency diagram.
ACTION ITEMS
1. Answer the following from a 1NF perspective (before the partial functional dependencies are removed) [see section 6-1 in the textbook on data anomalies] (0-6 pts): a. What kind of insertion anomalies would exist? Give an example from this data. b. What kind of deletion anomalies? Give an example from this data. c. What kind of modification anomalies? Give an example from this data.
2. Covert to 2NF by removing any Partial Functional Dependencies (show as was done in figure 6.4 of the text using table structures, dependency diagrams and equations). Make sure you include the table names and relations in equation format as well as any transitive dependencies (properly labeled) as is shown in figure 6.4 of the text. (0-6 pts) NOTE: Make sure you underline all PKs in your dependency relations and that you name each relation and show its equation format. In addition, all FKs should be dot-underlined or italicized in these relations and in the Chen ER diagram (the textbook does NOT do this but you should as this was discussed and used in our Meet session).
3. Convert to 3NF by Removing Transitive Dependencies (show as was done in figure 6.5 of the text
using table structures, dependency diagrams and equations). Make sure you include the table names
and relations in equation format as is shown in figure 6.5 of the text. (0-6 pts)
NOTE: Make sure you underline all PKs in your dependency relations and that you name each
relation and show its equation format. In addition, all FKs should be dot-underlined or italicized in
these relations and in the Chen ER diagram. The relation names in 3NF should match the ERD
entity names.
