1. Winter will soon be here and with it, the plows on the streets. The table below presents data about snowplows, mechanics responsible for the plows, and the drivers of the plows. The plows are owned by the Boston Metropolitan Area Snow Removal Commission and the managers are BMASRC employees. They are, however, driven by drivers who are regularly employed by local landscaping companies. An hourly cost of usage has been determined for each plow, and in addition, there is an hourly rate that is paid to the driver. This rate depends on both the plow and the driver.

a) Identify the functional dependencies between the attributes

b) Convert the table into a relation (named PlowDrivers). In what normal form is this relation (justify your view)?

c) For the PlowDrivers relation, give specific examples of possible insertion, deletion, and modification anomalies. Can you find any current examples of problems with the data?

d) Create the set of relations needed to represent the data in 3NF.

2. A start-up is working on an on-line personal financial management system. The goal of the system is to provide the users an opportunity to obtain item-level purchase data from as many sources as possible in order to improve the accuracy of budget management and control activities (instead of only at the level of the total of each purchase). For example, lets assume a customer purchases three books from a major on-line bookseller. For most financial management software systems, the system only receives the total amount of the purchase from a bank or other financial institution. In the case of this start-up, the intent is to create a link between the financial transaction and the vendors system data so that the financial management system retrieves product details from the vendor. Now it will be easy for the customer to classify one book as self-help, the other one as a business expense and the third one as entertainment without having to resort to an analysis of receipts.

To provide this capability, the system maintains the following data regarding the transactions:

TransactionID, CustomerID, CustomerName, CustomerEmail, TransactionDate, TransactionTime, TransactionTotalAmount, TransactionTax, {ProductID, ProductDescription, ProductCategory, ProductManufacturerID, ManufacturerName, ProductListPrice, ProductPurchasePrice, ProductQuantity, TransactionProductTotal}

Sample data for this set of attributes is as follows:

823434434582, 2434254, Silver Patrick, psilver@mail.net, 9/2/2016, 10.28.34, $128.25, $12.94, {78234, Achieving Ones Fullest Potential, self-help, 145432, Brown and Gray, $29.95, $24.75, 1, $24.75 | 4782349, Programming Server-side Solutions with Python, Programming, 63453632, Green & Yellow, $47.95, $39.99, 2, $79.98 | 2342343, Murder at Eleven, fiction, 145432, Brown and Gray, $14.95, $12.50, 5, $62.50}.

Note that information regarding specific products is repeated multiple times in the sample data set and each repeated set is separated by the | symbol.

Based on the facts stated above,

a) Identify the functional dependencies between the attributes.

b) Identify the reasons why this set of data is not in 3NF and indicate the normal form (if any) it is in.

c) Including all intermediate stages, organize the attributes into a set of 3NF relations.

d) Draw an ER diagram based on the normalized relations.