Department of Information Technology

U23IT502 - Database Management Systems

Exercise 1

Manufacturer

A manufacturing company produces products. The following product information is stored:

Create an ERD to show how you would track this information.

Show entity names, primary keys, attributes for each entity, relationships between the entities
and cardinality.

Exercise 2

Car Dealership

Create an ERD for a car dealership. 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 mechanics, 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).
Exercise 3

Here is an ER diagram for a fictional university database:

The foreign key columns are not included in the tables - in this diagram, they are implied by the
relationships, e.g. the Unit.director column comes from the "directs" relationship.

Looking at the diagram and the table schemas, answer the following questions for yourself:

 Which relationships are mandatory or optional? (For example, must every unit have at
least one student enrolled?)
 Which relationships are one-one, one-many or many-many?
 How do the above affect the placement of foreign keys? For example, why is the foreign
key for "lecturer belongs to research group" on the Lecturer table?
Exercise 4

Draw an ER diagram for the following scenario.

The University of Bristol Hoverboard Society (HovSoc) wants to create a database to manage its
membership and events. Each member has a name, an optional student number, a contact e-mail
address and a hoverboard riding skill level (represented as an integer, minimum 0). We assume
that e-mail addresses are unique among members.

The committee consists of some of the members, each of which has a unique committee role. We
assume that committee roles do not change during the year and that each committee role must be
filled every year.

An event has a date, a name, a location, an optional description and an organiser who must be a
society member (not necessarily a committee member). An event is attended by a set of
members. There is never more than one event at the same location on the same date but event
names are not unique.

Exercise 5

A university registrar’s office maintains data about the following entities:

(a) courses, including number, title, credits, syllabus, and prerequisites;

(b) course offerings, including course number, year, semester, section number, instructor(s),
timings, and classroom;

(c) students, including student-id, name, and program; and

(d) instructors, including identification number, name, department, and title.

Further, the enrollment of students in courses and grades awarded to students in each course they
are enrolled for must be appropriately modeled. Construct an E-R diagram forthe registrar’s
office. Document all assumptions that you make about the mapping constraints.

Exercise 6

Design an E-R diagram for keeping track of the exploits of your favourite sports team. You
should store the matches played, the scores in each match, the players in each match and
individual player statistics for each match. Summary statistics should be modeled as derived
attributes.

Exercise 7

Consider the E-R diagram given below, which models an online bookstore. a. List the entity sets
and their primary keys. b. Suppose the bookstore adds music cassettes and compact disks to its
collection. The same music item may be present in cassette or compact disk format, with
differing prices. Extend the E-R diagram to model this addition, ignoring the effect on shopping
baskets. c. Now extend the E-R diagram, using generalization, to model the case where a
shopping basket may contain any combination of books, music cassettes, or compact disks.