Relational Database Tables

Part 1 (12%)

1. Apply the normalization process for the information presented in each report, and normalize the information to tables that are in third normal form. Identify how the information may appear for each report in its first, second, and third normal forms.

2. Consolidate all of the normalized tables from all four reports, and include only one table per entity in your final answer. Clearly indicate the primary key, foreign keys (if any), and other attributes for each table. Present the information in the following format:

   Table Name	Primary Key	Foreign Keys (If any)	List of attributes

3. Draw an E-R Diagram to show the logical relationship between the above normalized tables. Clearly indicate the maximum cardinality for each relationship. (You are permitted to use the Crow’s foot notation or any other valid notations to represent the cardinality).

Part 2 (8%)

ABC ice-cream shop, purchases ice cream from two vendors. Over the years, ABC has developed good relationships with both vendors so that they allow ABC to pay them monthly for all purchases made during the previous month. The orders that are placed during the previous week are delivered on a weekly basis. ABC pays for the purchases with a check from the company’s main checking account.

4. Draw an E-R diagram (or an REA diagram), complete with cardinalities, for the expenditure cycle described above. (You are permitted to use the Crow’s foot notation or any other valid notations to represent the cardinality).

5. Develop a set of relational database tables to implement the E-R diagram (or the REA diagram) you developed in the previous question. Present the information in the following format:

 

Table Name	Primary Key	Foreign Keys (If any)	List of attributes