Homework 9: Project Milestone: Relational Schema Design

For this project milestone, you will create a complete relational schema diagram for your web application.

Of course, you should work with your project team.

Instructions

You should design your relational schema as an iterative process:

  1. Create a preliminary relational schema diagram based on your ER diagram
  2. Identify functional dependencies
  3. Use the functional dependencies to determine the schema’s normal form
  4. Decide whether to decompose the schema further
  5. Repeat the process until you cannot identify further improvements

Step 1: Relational Schema Diagram

Based on your ER diagram from Homework 8, create a relational schema diagram for your application. For full credit, you must incorporate my feedback on your ER diagram.

Your relational schema diagram must use standard notation and depict all:

An example relational schema diagram:

Figure 8.14

Step 2: Functional Dependencies

Next, list the functional dependencies in the canonical cover of your relation schema.

You may find it helpful to refer to both your relational schema and your ER diagram.

Step 3: Determine Normal Form

Next, determine which normal form your relation schema is in (1NF, 2NF, 3NF, BCNF, or 4NF).

Hint: Consider the candidate keys and functional dependencies for each relation.

Step 4: Decomposition

Does it make sense to decompose the schema further? If so, decompose your schema.

Hint: You must reach at least 3NF, since it is always possible to achieve a dependency preserving 3NF decomposition.

Step 5: Iterate

Perform this process iteratively, until there are no further opportunities for improvement.

Submit

Upload a PDF containing:

Only one team member should upload the assignment. Your submission will be graded for correctness and completeness.