Skip to content

GP3: Relational Mapping

Due: Monday, Sep 29th

Screenshot of dbdiagram.io
Image source: dbdiagram.io

Overview

For this assignment, you will create the relational design of your project database. Your design must be in DBML format stored in your GitHub repository along with the corresponding PDF. We recommend that you write the DBML code by hand, and that each team member design several tables.

Throughout the semester, you will likely make changes to your database design. You will need to update the DBML and PDF files when making changes in the coming weeks. That way, you will be able to see the history of changes on GitHub.

Instructions

As a team, decide who will be responsible for which entities/features of your proposed project. Then write DBML for the tables needed to implement those entities/features. We anticipate most projects will have about 10–12 tables (3–4 per student), but you are welcome to design more tables if needed.

Each table must have a note: that briefly describes the purpose of that table. Each column that is not self-explanatory must have a note: that explains the column. Ex: A column like FirstName is self-explanatory, but a column like MaxRepeat is not. See the Note Definition section of the DBML Docs for examples of notes.

Select an appropriate data type for each column. For examples, see Chapter 8 of PostgreSQL's docs. Make columns NOT NULL whenever possible. Each table must have a PRIMARY KEY. Most tables will also have FOREIGN KEY columns that reference another table's primary key.

Use dbdiagram.io to create your final diagram after merging contributions from each team member. Your combined DBML code should be free of errors. Arrange your tables neatly, especially if the "auto-arrange" feature does not work for your design. Before exporting your diagram as PDF, click the "highlight relationships" button (so that PK/FK columns are in light blue).

Finally, each team member must write two example queries for your database. The purpose of these queries is to illustrate how your design might work in SQL. Given that your project database does not yet exist, you won't be able to test your queries. (You will create the database and import sample data during HW4.) For now, do your best to write correct syntax and format your queries neatly.

Submission

The following files are required for GP2:

  • database/schema.dbml – source code of your database design
  • database/schema.pdf – diagram exported from dbdiagram.io
  • database/queries.sql – example queries (two per student)

You don't need to create the database on the server. After you receive feedback on your design, you will write create scripts for GP3. Please develop your solution incrementally. Ideally, each team member will make multiple commits to the repo during the week.