Skip to content

GP2: Schema Design

Due: Monday, Sep 30th

Screenshot of dbdiagram.io
Image source: dbdiagram.io

Overview

For this assignment, you will create the initial design of your project database. Your design must be in DBML format stored in your GitHub repository along with the corresponding PNG image. We recommend that you write the DBML code by hand, and that each team member design several tables. You are welcome (but not required) to use MySQL's Modeling Interface or pgAdmin's ERD Tool to draw portions of your model, export CREATE TABLE statements, and import into dbdiagram.io for further editing.

From now on, project deliverables will be committed via GitHub, not submitted via Gradescope. Throughout the semester, you will likely make changes to your database design. You will need to update the DBML and PNG 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 and Chapter 13 of MySQL'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 PNG, 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 GP3.) 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.png – diagram exported from dbdiagram.io
  • database/queries.sql – example queries (two per student)
  • README.md in root directory – updated proposal (if applicable)

Refer to the profs repository for examples of the above files.

You don't yet need to create the database on the server; you will do that for GP3, after you receive feedback on your design. Please develop your solution incrementally. Ideally, each team member will make multiple commits to the repository throughout the week.