Skip to content

GP2: Relational Mapping

Due: Monday, April 1st

Hotel Madison guest room
Image source: hotelmadison.com

Required Files

In this assignment, you will create the relational model based on the conceptual model you developed in GP1. You may approach this task by using the pgAdmin ERD Tool to draw your model and then generate the code. Or you may take the approach of writing the SQL DDL to create the database and generate a diagram from the code. Whichever approach you choose, these are the files required for GP2:

  • create.sql: This script should include all the CREATE TABLE SQL statements. You much choose appropriate data types for each table column, as well as decide which columns are NOT NULL and/or UNIQUE. Before each table, include a DROP TABLE statement, and write a short description of the table in a COMMENT.

  • alter.sql: This script should include ALTER TABLE statements to add all of the PRIMARY KEY and FOREIGN KEY constraints to each of the tables. Think about the indexes your database might need beyond the primary key indexes, and include CREATE INDEX statements in this script as well.

  • eer_model.png: The latest version of your ER model (started in GP1). The relational model must accurately reflect your group's conceptual model. It is possible, and in fact very likely, that you will find that you want to make some changes to the conceptual design while you are working on the relational model. If so, you must update the ER model to match the relational model.

  • rel_model.pgerd: Use the Postgres ERD tool to create (or reverse engineer) a diagram of the relational model. Arrange the tables manually so the diagram is easy to read and understand.

  • README.md: see instructions below.

Submission

From now on, project deliverables will be committed via GitHub, not submitted via Gradescope. The preceding files should be pushed to your GitHub repository, in a folder named database, where they can be updated as needed throughout the project lifetime. Make sure you also run the scripts to create the tables in your group's database on the data.cs.jmu.edu server.

In the database folder, create a README.md file with the following subsections (use ## in Markdown to begin a subsection):

  1. Write a brief explanation of each script file (for future reference).
  2. Record the commands you ran in the terminal to build your database.
  3. Document any changes you made to the conceptual model while developing the relational model. (This is more of a reflection while working on GP2, not something you would need maintain over time.)
  4. Explain every part of the relational model where you made a decision – that is, every detail that was not strictly dictated by an ER-to-relational mapping rule. For example, how any subclass relationships were handled and why, the decisions you made about derived attributes, and so forth.

Example Files

Based on the VetDB example from class.