Solution 4

September 25, 2005

Total score: 25 points (5 bonus points)

1. The key point of this excersice is to realize that Contract is a 3-ways relationship between three entities Suplier, Customer, and Product. This leads to the following E-R diagram:

Figure 1: E-R diagram for 3.9

Grade: 15 points; entity type with correct attributes and keys (4 points each), relationship type with correct attributes (3 points).

2. This excercise has several possible solutions. To solve the problem, we will need to identify the entities and relationships among them. Based on the problem statement, the following entities are obvious:

   • Supermarket
   • Product

and there should be a relationship between these two entities: Sale.

Next, we need to identify the attributes of the entities and the relationship. Product: Since products have name and categories, these will be its attributs. Name can be used as key since no two products should have the same name.

Supermarket: A supermarket can be uniquely identified by its address since there cannot be two supermarkets at the same location. We could have some ID (as a key) for supermarkets as well. supertmaket can be used as the key for this entity type

Sale: Oviously, we should have the information related to the sale such as quantity, price, and date.

The ER diagram that contains the above idea can be drawn accordingly. This diagram, however, does not represent the information such as “Each supermarket is in a city, which is in a state, which is in a region” and “time can be measured in days, months, ...”. This is because we simplify the statement and assumes that the above information can be extracted from the attribut address and date respectively. One can make this as complicated as below.

If you have the above ER diagram, you will receive the full mark of this problem (10 points)

Figure 2: E-R diagram for 4.10 – Simple solution

The information “each supermarket is in a city, which is in a state, which is in a region” can be represented by introducing a new entity type, say Location, which can be either a state, a city, or a region. To reflect the relationship between locations, a relationship part of is needed. Also, we need to associate Location with Supermarket through a relationship.

Creating the location entity type and identifying the additional relationships will earn 5 extra points.

Figure 3: E-R diagram for 4.10 – More complicated solution 3