ER Modeling Practice Exercise Solutions MBA 8473
PROBLEM 1. (This is just one way of doing this problem. Other versions are possible depending on what assumptions you make). ENTITIES: CAR VIN# ARRIVAL-DATE DEALER-PRICE PRE-OWNED SELL-TIME-STAMP. . . MODEL-TYPE START-DATE LAST RECALL. . . CUSTOMER-ID-NUMBER CUST 1 -F-NAME CUST 1 -L-NAME … 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. ) 1. VIN# provides unique identification 2. Year of make can be found from VIN# 3. PRE-OWNED is a yes/no attribute 1. 2. 3. 1. 2. MODEL-TYPE is unique We are going to capture only the latest 1. recall info Etc. etc. 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.
PROBLEM 1 continued: ER Model CUSTOMER 0 bought Is bought by Relationship related assumptions: 1. One customer can buy many cars over time 2. A car can be bought by one customer only 3. To be a customer one has to buy a car 4. A car can have only one model 5. Etc. SOLD-CAR Can belong to Has a MODEL
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
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-TYPE BETA-STAGE (all beta-stage types may not be used at a given point of time) SALES-AGENT PROMOTION (PROMOTION-TYPE+DATE is unique identifier) Hints for the relationships: Customer and Software – zero/many to zero/many Software-type 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 P&G 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
PROBLEM 3 continued: ER Model CUSTOMER Can buy 0 0 SOFTWARE Was a SOFTWARE-TYPE 0 Is assigned to Receives Can be in a 0 SALES-AGENT PROMOTIONS BETA-STAGE
Скачать презентацию ER Modeling Practice Exercise Solutions MBA 8473
2272b0a4baade5b22f6c20570f0c8fc0.ppt