CS4750 - Database Systems

Activity: DB Design and E-R diagram (Santa's workshops)

(no submission)

Purpose:

Apply the database design concepts to a scenario.
Draw an E-R diagram to represent a small database system.
Practice transforming E-R diagram to schema statements.
Get ready to work on homework assignment and course project
Prepare for exam 1

You may make a copy of a worksheet and complete this activity, type your answers in any text editor, or simply write on paper(s).

You may work alone or with 3-4 other students in this course.

The following database scenario includes partial information. You may make any reasonable assumptions, include appropriate constraints or restrictions, and come up with appropriate information (attributes) to maintain in a database. Remember to document your assumptions.

Imagine you are designing a database for a Santa's workshop. Santa has many Elves as helpers and he needs to design a database to keep track of them. The database needs to store information about the following:

Each elf has a unique badge number. Some elves may have the same name. Each elf is assigned to a specific workshop.
Some elves are supervisors. A supervisor normally supervises several elves. Some supervisors who have been recently promoted might not have been assigned any elves to supervise.
An elf can have at most one supervisor. Some elf may not yet have a supervisor.
Santa has many workshops. Each workshop has a location and a unique name.
Each workshop builds many kinds of toys and has at least one elf working in that workshop.
Each toy has a unique id. Some toys may have the same name. A toy is built in one or more workshops.
Each year, supervisors evaluate the elves they supervise.
Assume a single rating is given for each evaluation. Each evaluation has a date, the name of the supervisor, and a rating. Since there might be several instances with identical values of all three attributes, an evaluation must be associated with the correct elf instance to have meaning.

Draw an E-R diagram that represents the database scenario. Include an indication of the cardinality of relationships and specify any mandatory relationship (total participation). State any assumptions that you make.
[sample solution]

Convert the E-R diagram to tables, using schema statements

[sample solution]

Elf(badge, name, wname)   
    –- FK: wname references Workshop(wname)
is_supervised(badge, sbadge)   
    –- PK: badge, an elf can have at most one supervisor, 
 a supervisor can supervise many elves
    –- FK: badge references Elf(badge)
    –- FK: sbadge references Elf(badge)
Workshop(wname, location)
Toy(toy_id, toy_name)
Built_in(toy_id, wname)
    –- FK: toy_id references Toy(toy_id)
    –- FK: wname references Workshop(wname)
Evaluation(badge, rater, rate_date, rating)
    -- FK: badge references Elf(badge)
    -- FK: rater references Elf(badge)
    -- assume rating is of type INT

CC-BY-NC-SA 4.0 license.
Last updated 2026-01-25 17:05