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_LABEL, PROD_PRICE, VEND_CODE, VEND_NAME

For this assignment, assume the following three (3) relations to start: o inv_num-> sale_date (partial dependency) o prod_num-> prod_label, prod_price, vend_code, vend_name (partial dependency) o inv_num, prod_num-> quant_sold (full dependency)

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.

4. Draw EITHER a Crow's Foot ERD or a Chen ERD for this relation in 3NF (including connectivites, cardinalities and attributes).

5. Denormalize at least one of your relations/tables (show this with diagrams by going back to previous relations/tables in either 2NF or 3NF). Is this a more efficient design? Why or why not? Remember that denormalization may not be a more efficient design based on disk space but it may assist the users data entry or assist in access speed.