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.
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 represent the database scenario.
Include an indication of the cardinality of relationships and
specify any mandatory relationship (total participation).
State any assumptions that you make.
- Convert the Santa's workshop E-R diagram (above) into
schema statements.
Identify primary keys and foreign keys.
- If toy_id is a key for the Toy relation, could it be a key for the Built_in relation?
CC-BY-NC-SA 4.0 license.