Description
This homework will focus on concepts related to the creation of a data model.
It is due on Thursday February 7 at 15:59PM and should be submitted electronically on the class Submitty site.
1. You have been hired to help develop a database for the sales system for a new store that will sell board
games. The system should support the following:
Inventory management (both what games are in stock for a given store location and what games
are available for order)
Customer management (for sales and marketing purposes)
Order management (orders that have been placed by customers, both for delivery and for pick-up
in a location)
(a) (12 points) Draw an Entity-Relationship diagram for your system.
(b) (8 points) Convert your Entity-Relationship diagram into a relational model
2. Given the relation StudentEnrollment(id, name, major, mailboxNumber, courseNumber, semester, professor),
abbreviated E(i, n, m, b, c, s, p) and the functional dependencies i → nm, ics → nmp, cs → p, and mb → i
(a) (2 points) What are the keys of the relation?
(b) (1 point) This relation is not in BCNF. Why not?
(c) (9 points) Decompose the relation into sub-relations that are in BCNF using the algorithm presented in class. Use the first violating FD above as the starting point for your first decomposition.
Make sure you’ve listed any FDs that hold for your sub-relations.
Page 2
(d) (2 points) Which of the original FDs isn’t preserved by your decomposition? How do you know?
(e) (6 points) Decompose the original relation into sub-relations in 3NF, using the synthesis algorithm
described in class.
Page 3