Database Design Using ERDPlus II
Objective(s) Target CLO(s) Reference
To practice Entity Relationship (ER) 1 Instructor Demo
modeling of Chapter 12 by using a Chapter 12
tool called ERDPlus to
1) Graphically create E-R diagrams
based on business rules. AND
2) Generate the database by
automatically generating
complete DDL scripts.
ID
Name
Section
GEIT 3341 DATABASE I LAB 9
Instructions:
Use the ERDPlus tool to create an ER for a car dealership. The dealership
sells both new and used cars. Base your design on the following business
rules:
– A salesperson is identified by a salesperson 9 alphanumeric character id and has a first and last name.
– A salesperson may sell many cars, but each car is sold by only one salesperson.
– A car is identified by a 5 alphanumeric character id and has a 17 alphanumeric character serial number, make, model, color, and year.
– A customer is identified by a 5 alphanumeric character id, and has first and last
name, phone number, address, and a 6 alphanumeric character postal code.
– A customer may buy many cars, but each car is bought by only one customer.
– A salesperson writes a single invoice for each car he or she sells.
– A customer gets an invoice for each car he or she buys.
– An invoice is identified by an invoice 5 alphanumeric character id, invoice date and an invoice total.
– A customer may come in just to have his or her car serviced; that is, a customer need not buy a car to be classified as a customer.
– When a customer takes one or more cars in for repair or service, one service ticket is written for each car.
– A service ticket is identified by a 5 alphanumeric character id, date received, comments, and date returned back to customer.
– The car dealership maintains a service history for each of the cars serviced. The service records are referenced by the car’s serial number.
– A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars.
GEIT 3341 DATABASE I LAB 9
– A mechanic is identified by a 5 alphanumeric character id, and has a first and last name.
– A car that is serviced may or may not need parts (e.g., adjusting a carburetor or cleaning a fuel injector nozzle does not require providing new parts).
– A part is identified by a 5 alphanumeric character id, and has a name and price.
Your diagram should look like the one shown in Figure 1 below:
Figure 1: ER Diagram
What you need to hand in:
1. Using the Export Image… option of MENU (see Figure 2), generate an image for the
diagram (which will be in a PNG format) and hand in this image. (8 points) 2. Using the Generate SQL option (see Figure 3), generate the DDL, click on the Copy
button, copy this script into a Notepad/Word file and hand in this file. (2 points)
Figure 2: Export Image Option
GEIT 3341 DATABASE I LAB 9
Figure 3: Generate SQL Option
