Database Design using ERDPlus II

GEIT 3341 DATABASE I LAB 9

GEIT 3341 Database I

Lab 9

Database Design using ERDPlus II

Due Date:

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