Question

A database needs to be developed that supports a supermarket.

(i) Design an entity-relationship diagram that captures, as far as possible, the requirements stated below. (Statements in brackets are additional explanations and not part of the requirements proper.) If you make any assumptions in your design, please write them down. Assumptions, however, must not contradict the requirements.

1. The supermarket sells products. Each product is uniquely identified by its product number. Moreover, a product has price. The supermarket also records how many items of each product are currently on stock.

2. A product can be on special offer. A special offer is valid for a period, which has a start date and an end date. A special offer refers to exactly one product. Two special offers for the same product cannot have overlapping periods. A special offer for a product specifies that a certain number of items of that product are sold at a certain price (e.g., 2 items for $1.99).

3. There is a card scheme for which customers can register. A registered customer holds a customer card. Each registered customer has a customer number, which is unique. For each registered customer the supermarket records the name and the address of the customer and the day when the customer joined the card scheme.

4. When a customer buys one or more items and pays for them, a purchase is being completed. A purchase is uniquely identified by the number of the checkout where the customer pays, together with the date and the time when the purchase is made.

5. Some purchases are made by registered customers.

6. A purchase consists of one or more line items. Within a given purchase, each line item is uniquely identified by its line number. Each line item refers to a unique product. (In fact, the line items correspond to the items of the purchase). Some line items have an additional comment, which is a string. (The comment may say that a special price applies to the product item because it is part of a special offer.)

[20]

Answer 1

ER Diagram:also known as Entity relationship diagram. It is a graphical representation of relationships between the tables or entities in a data base. it explains the logical structure of a database.

Now the ER diagram of a super market database is shown below.

Tables:

1.supplier

2.product

3.offer

4.customer

5.Registeredcustomer

6.department

7.employee