4 Assignments

Learning Objectives

This chapter contains design assignments that let you practice what you learned in Chapter 2 and Chapter 3. Each assignment states which method is required:

ERD: start from the description and create an entity relationship diagram;
Normalization: start from the document or worksheet and normalize to third normal form;
Free choice: choose the method that best fits the information given, and explain your choice.

Approach

For each assignment, hand in at least:

an ERD in crow’s foot notation, with the minimum and maximum on both sides of each relationship;
a relational schema in the notation used in this book: primary keys underlined, foreign keys in italics;
a short list of the assumptions you made where the description is incomplete.

For a normalization assignment, also show each intermediate step (UNF, 1NF, 2NF, and 3NF), along with the functional dependencies that lead to a split.

Two methods, one design

For the free-choice assignments, it is instructive to use both methods: first create an ERD from the description, then normalize the sample document. Do both results match? If not, find out where the difference comes from.

The assignments are graded by level: ★ is a basic assignment, ★★ an intermediate one, and ★★★ a challenging one.

4.1 Fitness center

Method: ERD
Difficulty: ★

The FitPoint fitness center wants to store its membership records and class schedule in a database.

For each member, a member number, name, email address, and date of birth are recorded. Each member has one membership type: Basic, Flex, or Premium. For each membership type, a code, a name, and the monthly fee are recorded. Of course, many members have the same membership type.

The fitness center offers various kinds of group classes, such as spinning, yoga, and boot camp. For each class type, a code, a name, and the duration in minutes are recorded. A class type is scheduled several times a week. Each such class session gets a unique number and takes place on a certain date and start time, in one room, led by one trainer. For a room, the room number and capacity are known; for a trainer, a code and a name. A trainer can teach several class types.

Members reserve a spot in a class session through the app. Afterwards, the trainer records for each reservation whether the member actually attended.

Create an ERD with all entities, attributes, and relationships.
Convert the ERD into a relational schema.
From now on, the fitness center also wants to keep track of which memberships a member has had in the past, with the start and end dates. What changes in your design?

4.2 Coffee bar

Method: ERD
Difficulty: ★

A coffee bar has a number of regular customers. Customers can place several orders. Each order contains one or more items from the menu. These items are divided into categories: coffee, pastries, and lunch. Assume that prices are fixed. For each customer, the name, email address, and phone number are recorded, and for each order, the time it was placed.

Create an ERD and a relational schema with all entities, attributes, and relationships.
What must change in the design if prices are no longer fixed and can change?

4.3 Restaurant reservation system

Method: ERD
Difficulty: ★

A restaurant takes reservations from guests. A guest can make several reservations (on different dates). Each reservation is for one specific table. A table can, of course, be reserved many times (at different times). A table also has a limited number of seats and is in a certain location (patio, indoors, by a window).

Use the ERD method to create a relational design for this database.

4.4 Theater

Method: ERD
Difficulty: ★★★

The Crown City Theater has three auditoriums. For each auditorium, a number and a name are recorded. Each auditorium has fixed seats, identified by a row (A, B, C, …) and a seat number within that row. So seat 12 in row F exists in every auditorium. Each seat falls into one section, such as orchestra, mezzanine, or balcony.

The theater schedules productions by companies. For a company, a code, the name, and a contact person are known. A production has a number, a title, and a genre (drama, comedy, music, dance) and is created by one company. A production is staged one or more times. Each performance gets its own number and takes place on a certain date and curtain time in one auditorium.

The price of a ticket depends on the section of the seat and on the performance: an opening night, for example, is more expensive than a regular evening.

Customers (customer number, name, email address) make a reservation for one performance. Several seats can be booked in one reservation. The date of the reservation is recorded. A seat can, of course, be reserved only once for a given performance.

Create an ERD with all entities, attributes, and relationships. Pay close attention to the key of Seat.
Convert the ERD into a relational schema.
How does your design prevent the same seat from being sold twice for the same performance? Can this be achieved completely with primary keys, or is more needed?

4.5 Library

Method: ERD
Difficulty: ★★★

A library lends books to members. Each book has one author. An author may have written several books. A member can borrow several books at the same time. A book can be on loan to only one member at a time. The loan period is 3 weeks. If you return a book late, you currently have to pay a fine of $1 per week overdue.

Use the ERD method to create a relational design for this database.

Tip

Think carefully about how you record loans (you need 4 tables!).

4.6 Music streaming service

Method: ERD
Difficulty: ★★★

A music streaming service has users who can create playlists. Each playlist contains several songs and can be made public or private. A song can appear in several playlists. Each song is by one artist and lasts a certain number of seconds. An artist has several songs.

Use the ERD method to create a relational design for this database.

4.7 Project management

Method: ERD
Difficulty: ★★★

A company works on various projects. Each project has several tasks. Employees can work on several projects. Several employees can work on each task. Each task has a status (not started, in progress, completed). A project has one project manager (who is also an employee).

Note that an employee can be a project manager and can also work on tasks. This is an example of an employee who has more than one role.

Use the ERD method to create a relational design for this database.

4.8 Veterinary clinic

Method: Normalization
Difficulty: ★

A veterinary clinic currently keeps track of animal treatments in an Excel table, which looks like this.

The plan is to keep this data in a relational database. Normalize the data to third normal form. Show all intermediate steps.

4.9 Plumbing and heating work order

Method: Normalization
Difficulty: ★★

HeatWorks Inc., a plumbing and heating contractor, uses the following work order. The technicians fill it in after completing a job, and the office uses it to prepare the invoice.

Work order

Work order number: 26-0583
Date: April 14, 2026
Customer number: 3312
Name: H. Dixon
Address: 8 Linden Avenue
ZIP code: 01060
City: Northampton

Labor

Technician	Name	HourlyRate	Hours	Amount
M07	P. Walters	62.50	3.0	187.50
M11	S. Kaya	48.00	3.0	144.00

Materials

Item	Description	Price	Quantity	Amount
A2210	Thermostatic radiator valve	34.95	2	69.90
A0418	Radiator bleed valve	8.75	4	35.00
A3001	Copper elbow 1/2 in.	1.20	6	7.20

Subtotal: $443.60
Sales tax 6.25%: $27.73
Total: $471.33

Assume the following business rules:

the hourly rate depends on the technician (an apprentice is cheaper than a journeyman);
several technicians and several items can appear on one work order;
the price of an item is the current price from the price list.

Normalize the work order to third normal form. Show all intermediate steps.
The work order contains two repeating groups. Why is it a mistake to split them off together into one table?
Does the functional dependency ZIPCode → City hold? What does your answer mean for the design?

4.10 Driving school

Method: Normalization
Difficulty: ★★

Dalton Driving School plans its lessons in an Excel worksheet (Table 4.1).

Table 4.1: Lesson schedule of the driving school

Date	Time	StudentID	Student	Package	Instr	Instructor	LicensePlate	Make	Transmission
2026-04-06	09:00	L104	Öztürk	Basic 20	RB	Ron Bishop	7ABC123	Honda Civic	Manual
2026-04-06	10:00	L117	Fisher	Express 10	RB	Ron Bishop	7ABC123	Honda Civic	Manual
2026-04-06	09:00	L121	Kramer	Basic 20	AD	Anna Decker	8XYZ456	Toyota Corolla	Automatic
2026-04-07	09:00	L104	Öztürk	Basic 20	RB	Ron Bishop	7ABC123	Honda Civic	Manual
2026-04-07	11:00	L121	Kramer	Basic 20	AD	Anna Decker	8XYZ456	Toyota Corolla	Automatic

Assume the following business rules:

a lesson always lasts one hour and starts on the hour;
a student takes one lesson package;
each instructor always drives the same training car, and each car belongs to one instructor.

The worksheet has two possible keys. What are they? Explain why (Date, Time) alone is not enough.
Normalize the worksheet to third normal form, using one of the two keys from a.
Does it matter for the final result which key you chose in b? Explain.
For this worksheet, give an example of an update anomaly, an insert anomaly, and a delete anomaly.

4.11 Campground

Method: Free choice
Difficulty: ★★

Birch Hollow Campground rents out campsites and cabins. Each site has a number and is of one type: standard site, full hookup site (with its own water and sewer connection), or cabin. For a type, a code, a description, and the price per night are recorded. For each site, it is also recorded whether it has an electrical hookup.

For each guest, a guest number, name, email address, and country of origin are registered. A guest makes a reservation with an arrival and departure date and the number of people. Families traveling together often reserve several adjacent sites in one reservation.

Extras can be booked with a reservation, such as bike rental per day, a breakfast delivery service, or a dog fee. For each extra, a code, description, and price are known. For each reservation, the number of units of each extra booked is recorded.

After booking, the guest receives the following confirmation.

Reservation confirmation

Reservation number: 4127
Guest number: 881
Name: The Meyer family
Email: meyer@example.com
Country: Canada
Arrival: July 18, 2026
Departure: July 25, 2026 (7 nights)
Number of people: 6

Site	Type	Electric	Price per night	Amount
14	Full hookup site	yes	32.50	227.50
15	Full hookup site	yes	32.50	227.50

Extra	Description	Price	Quantity	Amount
E01	Bike rental per day	9.00	12	108.00
E04	Dog fee per night	3.50	7	24.50

Total: $587.50

Create a design in third normal form, with an ERD and a relational schema. Choose whether to start from the description or from the reservation confirmation, and explain why.
Check your design with the other method. Do the results match?
In high season, the price per night is higher than in low season. How do you adapt the design?

4.12 Staffing agency

Method: Free choice
Difficulty: ★★★

The staffing agency DataDirect places data analysts and developers with clients. A client gives an assignment, such as a data migration or building a dashboard. Each assignment has an assignment number and a description and is for one client. For a client, a client number and a name are known.

One or more employees work on an assignment, and an employee can work on several assignments at the same time. For an employee, an employee number, the name, and the branch office (Chicago, Denver, or Austin) are recorded. The hourly rate the client pays is agreed per assignment for each employee separately: within the same assignment, a senior is billed at a higher rate than a junior.

Every week, an employee submits a timesheet with the hours worked per assignment per day.

Timesheet

Employee number: 207
Name: S. Archer
Branch: Chicago
Week: 15 (April 6 through 10, 2026)

Assignment	Description	Client	ClientName	Mon	Tue	Wed	Thu	Fri
A-311	Data migration	C45	City of Evanston	8	8	4	0	0
A-318	BI reporting	C12	Vandam Logistics	0	0	4	8	8

Total hours: 40

Normalize the timesheet to third normal form. Note: the columns Mon through Fri together also form a repeating group.
The hourly rate does not appear on the timesheet. Extend your design so that the rate can be recorded. Which method helps you most here, and why?
Draw the complete ERD.
The agency wants to be able to create a monthly invoice per assignment showing the number of hours worked and the amount per employee. Show that this is possible with your design, without storing any amounts in the database.