The third stage of the project is the development of the logical database design and its implementation from the entity-relationship (ER) model. The relational schema should be fully normalized with documentation to show that it adheres to good database design.

Expected duration
4 hours
Deadline
2300 on Lesson 16
Points
50 points
Submission
Canvas

Learning Objectives

  • Create a normalized relational database schema from an entity-relationship (ER) model
  • Create relational tables using the CREATE TABLE SQL command
  • Populate a database with representative data using the INSERT SQL command

Help Policy

Authorized Resources
Any, except classmates working on other teams
Notes
Never copy another person’s work and submit it as your own.

You must document all help received from all sources, including the instructor and instructor-provided course materials (such as the textbook).

Assignment

This assignment has two parts:

  1. create a relational schema from your ER diagram and
  2. write SQL queries to create the relational schema and to populate it with representative data.

Each part is described in more detail below.

Relational Schema

You must develop a relational schema for your ER diagram. Provide a table with the following information for each relation:

Attribute
Name of attribute
Data Type
Data type, including length (if applicable)
Keys and Constraints
Identify primary and foreign keys and any constraints that exist on the allowed set of values

For example,

Attribute Data Type Keys and Constraints
id INTEGER PRIMARY KEY
name VARCHAR(255) NOT NULL

Include a brief prose description of each relation, especially if it does not correspond to an entity in your ER diagram. See the PostgreSQL tutorial for a complete example of the expected format to document your logical database design.

Your relational schema should be normalized to at least 3rd Normal Form (3NF). Although you need not list all the functional dependencies, ensure that the documentation for each table is sufficient to infer that the database satisfies 3NF (which may necessitate identifying the functional dependencies in some cases).

Create and Populate Database

Write SQL queries to create the relational schema (e.g., CREATE TABLE commands) that you previously defined. In other words, the table lists the attributes and data types, keys, and constraints for each relation is your logical database design; the SQL queries are the implementation of that design.

Be sure that your SQL queries reflect the data types, etc. previously described.

In addition, write SQL queries to populate the database (e.g., INSERT commands) with representative data. The amount of data should be sufficient to prove the feasibility of your design and implementation – i.e., there should be at least one, but preferably several, tuples in every relation although including more data is definitely preferable.

The SQL queries (data definition and data manipulation) should be executable – i.e., they should execute correctly against an “empty” database.

You will later use these SQL queries to create and populate the database for your web application. Of course, some changes to the database schema may be necessitated if there are any issues with the design, and your final project submission should contain additional real-world data.

Submission

Submit your relational schema and SQL queries using Canvas and include a copy of your ER diagram as a separate attachment. Include your documentation statement as part of your submission.

Grading

The following grading rubric will be used for this assignment:

Description Points
Relational Schema 25
Database Creation 15
Representative Data 10