Database Modeling and Design – 4th Edition (2006)
Toby Teorey, Sam Lightstone, Tom Nadeau

Exercises with Solutions – Solutions Manual

ER and UML Conceptual Data Modeling

Problem 2-1

Draw a detailed ER diagram for an car rental agency database (e.g. Hertz), keeping track of current rental location of each car, its current condition and history of repairs, and customer information for a local office, expected return date, return location, car status (ready, being-repaired, currently-rented, being-cleaned). Select attributes from your intuition about the situation, and list them separately from the diagram, but associated with a particular entity or relationship in the ER model.

Solution to 2-1

Problem 2-2
Given the following assertions for a relational database that represents the current term enrollment at a large university, draw an ER diagram for this schema that takes into account all the assertions given. There are 2000 instructors, 4000 courses, and 30,000 students. Use as many ER constructs as you can to represent the true semantics of the problem.

Assertions:
An instructor may teach one or more courses in a given term (average is 2.0 courses).
An instructor must direct the research of at least one student (average = 2.5 students).
A course may have none, one, or two prerequisites (average = 1.5 prerequisites).
A course may exist even if no students are currently enrolled.
All courses are taught by exactly one instructor.
The average enrollment in a course is 30 students.
A student must select at least one course per term (average = 4.0 course selections).

Solution to 2-2

Problem 3-1

Draw UML class diagrams for a car rental agency database (e.g. Hertz), keeping track of current rental location of each car, its current condition and history of repairs, and customer information for a local office,