NOTE: State your assumptions in writing for better understanding of your ER diagrams.
The ER diagrams have to be created with PowerPoint, Word or some other drawing tool. They cannot be hand-drawn! Pictures of hand-written diagrams taken with your phone are not accepted.
Problem 1 (Car-Race Database) [50 Points]
Design an Entity-Relationship Diagram (ERD) for a car race database with the following requirements.
· We have cars, for each car we keep its VIN number (unique ID), engine type, color, make, and model
· We have drivers, for each driver we keep ID (unique), name, DoB, and age (derived attribute)
· Drivers use cars to enter races, each race has some attributes such as the race number (unique ID), race type, the number of rounds, and date.
· Each driver can enter many races and can use the same car or a different one in each race. Thus, the same car can participate in many races.
· In the design, we want to capture which car is used by which driver and in which race.
· We also need to capture the winner of each race (the driver who won the race) and the winning time (the time taken to finish and win the race).
· Cars may get into accidents. We need to capture the accident history of cars. For each accident, we need to capture the involved car(s), involved driver(s), and the race in which the accident took place.
Problem 2 (Mini-Store Database) [50 Points]
Design an ER diagram for a mini store that represents the following requirements.
· We have items, each item has a unique ItemID, price, type, and name.
· We have customers, each customer has unique CustID, name, and address.
· Each customer can make many transactions, in each transaction a customer may buy one or more items and for each item there is a quantity, e.g., customer 1 makes a transaction buying 3 milk bottles, and 2 canned meat, and 1 chocolate.
· We also need to capture the transaction date and the total price.
· Customers may return items that they have previously bought, e.g., customer 1 may return 2 milk bottles from the 3 ones he previously bought. In this return transaction, we need to keep track of which item has been returned, the quantity, the return time, and the original buying transaction.
o Note: Try to have good design that does not have redundant (un-needed) relationships. Redundancy will be penalized.
