Consider the following domain:
You are running a Fresh Juice business with multiple stores around the country, and you want to keep
the information for these stores in a relational database. The following is a list of that information:
• Stores: Each store has a city, phone number, and manager. There is only one store per city.
• Beverages: Each juice beverage has a name (e.g. 'Kiwi Lime'), and a number of calories for a
regular and large size. A large size always has 200 more calories than the regular size. Every store
should keep track of the number of inventory of each beverage (how much it has left in stock).
• Transactions: When a customer makes an order, that order should have a date, price, and an
indication of which loyalty card was used, if applicable. You can assume one beverage is ordered
per transaction, and we should know what that beverage was.
• Loyalty card: Customers can have a loyalty card if they like to go to your stores a lot. There
needs to be information on how many transactions a customer made with the card, and their
'home store' (the one they go to most frequently).
Continued on next page...
1
(a) Define a single relation for this domain that manages to store all of the required information
(just write it out R(...), no need for SQL definitions yet). There is not necessarily one correct
answer for this relation, but the information should be stored in a practically useful way. It is ok
to add attributes that aren't explicitly listed in the domain as long as they are useful.
(b) Write all of the functional dependencies for your relation that would be inferred by the description
of this domain. Do not include trivial or redundant FDs (find a minimal basis).
(c) Provide a useful instance of your relation that shows all three types of anomalies. Describe the
anomalies you have presented as they appear in your particular instance (give an example for
each of the three anomalies in your relation).
(d) Your relation will likely (read certainly) have some redundancy. Decompose your relation into a
set of relations without any BCNF violations. Write all of your steps in full and clearly show why
your relations do not violate BCNF.
(e) Explain how your new relations prevent the anomalies you pointed out in part (c).
