Database Design

Purpose

The purpose of this assignment is to create an E-R diagram and translate it into a relational schema. With this assignment you will show that you have understood the concepts related to DB design and that you are able to put them in practice by creating your own example.

TASK 1

Create an E-R diagram containing:

• exactly 5 entities
• exactly 4 relationships including:
  • at least one 1-to-1 relationship
  • at least one 1-to-many relationship
  • at least one many-to-many relationship
  • the fourth relationship can have any cardinality

The diagram should be complete, including all attributes, cardinalities, identifiers etc.

TASK 2

Provide a short textual description of the scenario represented by your diagram. Explain the constructs used and choices made, including any assumptions; discuss the cardinalities you assigned.

TASK 3

Translate your diagram into the relational model; provide the schema of the tables used and, for each table, indicate the primary key and any foreign keys.

TASK 4

Write, in natural language, what you envisioned to be the 2 most frequently issued queries to your database (at least one of which should involve data from 2 or more tables).

TASK 5

Formulate in SQL the two queries you wrote in TASK 4.

RULES

• Each section and subsection of the assignment must be completed individually.
• The E-R diagram should be drawn using the notation presented in the lecture slides (you can use any drawing tool you prefer).
• The diagram (and associated descriptions) you create should be different from all examples provided in this module, including the scenario of the first assignment (MySQL assignment).