[Main Page]      [Literature]      [Software]      [People]      [Conferences]      [Courses]      [Web-DB]

Homework Assignment #3:    ER-Model (Advanced Constructs)

Exercise 3 (12 Points)

Suppose we want to develop a database for a grocery store chain. Since this is a large task, we will concentrate here on modelling the sales data, and especially the sales checks. Please develop an entity-relationship schema containing the following information:

• The chain has several stores. Every store has a unique number. We also need the address of each store. The address consists of street address, city, and ZIP-code.
• Each store has several check-outs. Every check-out has a number, however, this number is only unique within the store.
• Next, we want to model sales checks. Each sales check has a timestamp (date, hour, minute, second). Again, the timestamp is only unique for a single check-out.
• Some sales-checks are for known customers (because they used their customer card to get 1% of their total sales at the end of the year). We want to store the customer card number, the name and address for each such customer. The address consists of street address, city, and ZIP-code (as above). Some customers can use their customer card like a credit card. We have to store a credit limit for such customers.
• We want to store the product code (identifying number) of each ware/item in our store, plus a short description.
• Prices can change, and we want to keep historical data. For every product, there is a set of intervals (from date, to date) where a particular price was valid. The "to date" may be yet unkown if we do not plan to change the price in the immediate future.
• Finally, we need the information which products in which quantity are contained in a sales check.

Please specify keys and cardinalities. If you should feel that other constraints are needed, specify them in natural language. You may submit handwritten diagrams, but please make sure that everything is readable.

Due Date:

Stefan Brass (sbrass@sis.pitt.edu), January 26, 2000

Original URL: http://www2.sis.pitt.edu/~sbrass/db00_2/h3_ermod.html   [HTML 3.2 Checked]