Practical 05: Entity-Relationship Diagram (ERD)

Note

Ensure that you have gone through Lecture 5: Structuring System Data Requirements before attempting this practical.

Theroretical Questions

1. State whether there is a one-to-one, one-to-many, or many-to-many relationship for the following pairs of related entities. Use the notation introduced in the text to draw a diagram for each of the relationships.

   • Employee and Project
   • Student and Course
   • Course and Section
   • Customer and Order
   • Patient and Bed

2. Distinguish the following types of data models:

   • Conceptual Data Model
   • Logical Data Model
   • Physical Data Model

Hands-On Tasks

Task 1

Car Dealership System

The dealership sells both new and used cars, and it operates a service facility. Base your design on the following business rules:

• A salesperson may sell many cars, but each car is sold by only one salesperson.
• 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.
• 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.
• 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 mechnaics, and each mechanic may work on many cars.
• 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).

Construct an Entity-Relationship Diagram (ERD) for the given system.

---

SUBMISSION

Complete the given practical and submit it as your lecture attendance for Week 6.