ER%20Modeling%20Practice%20Exercise%20Solutions

1
ER Modeling Practice Exercise Solutions

• MBA 8473

2
PROBLEM 1. (This is just one way of doing this
problem. Other versions are possible depending on
what assumptions you make).

• ASSUMPTIONS
• (It will be beneficial to think about what a
  car means
• in this situation. Is it a car? Or, is it a
  sold-car? Etc.)
• VIN provides unique identification
• Year of make can be found from VIN
• PRE-OWNED is a yes/no attribute

ENTITIES CAR VIN ARRIVAL-DATE DEALER-PRI
CE PRE-OWNED SELL-TIME-STAMP ...
MODEL MODEL-TYPE START-DATE LAST
RECALL ...

• MODEL-TYPE is unique
• We are going to capture only the latest
• recall info
• Etc. etc.

CUSTOMER CUSTOMER-ID-NUMBER CUST1-F-NAME CU
ST1-L-NAME

• We do not want to use ss as unique identifier
• To be a customer in the database the person
• should have bought at least one car.
• Etc etc.

3
PROBLEM 1 continued
ER Model
bought
CUSTOMER
SOLD-CAR
0
Is bought by
Has a
Can belong to

• Relationship related assumptions
• One customer can buy many cars over time
• A car can be bought by one customer only
• To be a customer one has to buy a car
• A car can have only one model
• Etc. Etc.

MODEL
4
PROBLEM 2.Note that the ERD solution for problem
1 can meet most of the requirements of problem 2
except the fact that we do not need model info
for problem 2. Only kink in this problem was the
use of USED-CARS and NEW-CARS. In this particular
case it is convenient to capture that as an
attribute called PRE-OWNED (yes/no) like I
already did for problem 1. Another way to treat
this in the ERD will be to recognize two sub-type
entities viz., USED-CAR And NEW-CAR for a super
entity called CAR. Can be done like this
CAR
Is a
USED-CAR
NEW-CAR
5
PROBLEM 3. (This is just one way of doing this
problem. Other versions are possible depending
on what assumptions you make). Partial solution
only. ENTITIES CUSTOMER SOFTWARE SOFTWARE-TY
PE BETA-STAGE (all beta-stage types may not be
used at a given point of time) SALES-AGENT PROM
OTION (PROMOTION-TYPEDATE is unique
identifier) Hints for the relationships Customer
and Software zero/many to zero/many Software-ty
pe and Beta-stage one to one (assumption a
given software type can only be in a one
beta) Customer and Sales-agent (many to one)
See PG example from class notes. Promotions (are
sent to ) Customers Many to many. You should
be able to fill the rest from this! SOFTWARE and
SOFTWARETYPE one/one to one/one
6
PROBLEM 3 continued
ER Model
Can buy
CUSTOMER
SOFTWARE
SOFTWARE-TYPE
Was a
0
0
0
Receives
Can be in a
Is assigned to
0
PROMOTIONS
BETA-STAGE
SALES-AGENT