1 / 6

ER Modeling Practice Exercise Solutions

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 ... . ASSUMPTIONS:

efrem
Télécharger la présentation

ER Modeling Practice Exercise Solutions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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). ENTITIES: CAR VIN# ARRIVAL-DATE DEALER-PRICE PRE-OWNED SELL-TIME-STAMP ... • 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 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 CUST1-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 CUSTOMER bought SOLD-CAR 0 Is bought by Can belong to Has a • 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-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

  6. PROBLEM 3 continued: ER Model Can buy CUSTOMER SOFTWARE Was a SOFTWARE-TYPE 0 0 0 Receives Is assigned to Can be in a 0 PROMOTIONS BETA-STAGE SALES-AGENT

More Related