Final Project: Implementation

Getting Started

Template Files

To provide you some structure with which to start building your final project, we’ve provided you with three template files: DESIGN.md, schema.sql, and queries.sql. Start by downloading these template files and reading on, below!

Download the template files

Log into cs50.dev, click on your terminal window, and execute cd by itself. You should find that your terminal window’s prompt resembles the below:

$

Next execute

wget https://cdn.cs50.net/sql/2024/x/project/project.zip

in order to download a ZIP called project.zip into your codespace.

Then execute

unzip project.zip

to create a folder called project. You no longer need the ZIP file, so you can execute

rm project.zip

and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.

Now type

cd project

followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.

project/ $

If all was successful, you should execute

ls

and see DESIGN.md, schema.sql, and queries.sql—the three components of the final project.

Specification

Your final project should be composed of three files:

  • DESIGN.md, which is a rigorous design document describing your database’s purpose, scope, entities, relationships, optimizations, and limitations. The goal of the design document is to make your thinking visible. Your design document should include:
    • An entity relationship diagram for your database.
    • A video overview, no more than three minutes long.
  • schema.sql, which is an annotated set of CREATE TABLE, CREATE INDEX, CREATE VIEW, etc. statements that compose your database’s schema.
  • queries.sql, which is an annotated set of SELECT, INSERT, UPDATE, DELETE, etc. statements that users will commonly run on your database.

You will (intentionally!) not submit a database file. You should design your database without feeling constrained by needing lots of data to fill it.

The requirements for each of these components are described in more detail below.

DESIGN.md

Within DESIGN.md, write about your database’s purpose, scope, entities, relationships, optimizations, and limitations. Write for a technical audience that has taken a course such as CSCI E-151. To help you, the template DESIGN.md file includes sections with questions for you to answer.

DESIGN.md is a Markdown file, which allows you to easily format your document using Markdown syntax. If you’re new to the format, learn more at markdownguide.org.

Entity Relationship Diagram

Your DESIGN.md file should include an entity relationship diagram for your database. You can create your entity relationship diagram any way you’d like, but allow us to suggest a few!

  • If you’re the pencil-and-paper type, you can draw your diagram, take a picture, and upload it to your codespace.
  • If you’re digitally inclined, you can use a tool such as the Mermaid.js live editor. Mermaid.js is a toolkit via which you can create and export diagrams (including entity relationship diagrams!). See the documentation to learn the relevant syntax and see examples.
  • You’re also welcome to use any other software that helps you draw the types of shapes you’d like to draw.

When you have an image of your entity relationship diagram, you can embed it in your DESIGN.md file using the following syntax:

![IMAGE TITLE](FILENAME)

Where IMAGE TITLE and FILENAME are your image’s title (entirely up to you!) and its filename (e.g., diagram.jpg), respectively.

Video Overview

Create a short video (that’s no more than 3 minutes in length) in which you present your project to the world, as with slides, screenshots, voiceover, and/or live action. Your video should somehow include your project’s title, your name, your city and country, and any other details that you’d like to convey to viewers. See howtogeek.com/205742/how-to-record-your-windows-mac-linux-android-or-ios-screen for tips on how to make a “screencast,” though you’re welcome to use an actual camera. Upload your video to YouTube (or, if blocked in your country, a similar site) and take note of its URL; it’s fine to flag it as “unlisted,” but don’t flag it as “private.”

schema.sql

Your schema.sql file should include a set of SQL statements to define your database’s schema, annotated with brief SQL comments. Recall that you can use the following syntax to write a SQL comment:

-- This is a SQL comment

Your schema.sql file will likely contain CREATE TABLE, CREATE INDEX, and CREATE VIEW statements.

queries.sql

Your queries.sql file should include a set of SQL queries typically run on your database, annotated with brief SQL comments.

Your queries.sql file will likely contain SELECT, INSERT, UPDATE, and DELETE statements.

You do not need to fully populate your database—you need only show representative INSERT statements.

Assessment

Each component of the final project implementation is assessed on the rubric described below.

Video

3 Points Above Expectations

Succinct, interesting, produced with care. Videos in this category should:

  • Succinctly show most aspects of the final project implementation.
  • Anticipate some of the key questions one might have about the project.
  • Be of appropriate length for the project scope.

2 Points Good

This video demonstrates effort and thoughtfulness. It should:

  • Show most aspects of the final project.

1 Point Below Expectations

This video gets some points about the project across, but could be improved substantially with a bit more time. The video might:

  • Not show important aspects of the final project
  • Be of a length that doesn’t match the project scope.
Design Document

3 Points Above Expectations

DESIGN.md is easy to read and shows thoughtful consideration of the database’s design. After reading it, someone who knows about databases should be able to:

  • Clearly identify what is part of the database and what isn’t
  • Name the most important tasks the database needs to perform
  • Understand what data is stored in the database and agree that author has carefully considered their chosen fields, data types, and constraints
  • Agree that the entity relationship diagram is well-made, with good reasons given for the pictured relationships
  • Believe that the author has considered all relevant optimizations and justified their cost, if any
  • Fully grasp any limitations the database design may have

2 Points Good

DESIGN.md shows thoughtful consideration of the database’s design. After reading it, someone who knows about databases should be able to:

  • Identify what is part of the database and what isn’t
  • Name the tasks the database needs to perform
  • Understand what data is stored in the database and identify some justification for the chosen fields, data types, and constraints
  • Agree that the entity relationship diagram is well-made, with some rationale given for the pictured relationships
  • Believe that the author has considered some relevant optimizations
  • Understand most limitations the database design may have

1 Point Below Expectations

DESIGN.md is more or less complete. After reading it, someone who knows about databases may:

  • Still have questions about what is part of the database and what isn’t
  • Only be able to name some of the tasks the database needs to perform
  • Not fully understand what data is stored in the database and may not be able to identify justification for the chosen fields, data types, and constraints
  • Disagree that the entity relationship diagram is well-made, with some rationale given for the pictured relationships
  • Believe the author missed many important optimizations
  • Not be able to identify limitations of the database
Schema

3 Points Above Expectations

schema.sql is comprehensive and clearly annotated. schema.sql should:

  • Include all appropriate SQL statements needed to represent the entities in DESIGN.md, with each statement being well-designed.
  • Include all appropriate SQL statements needed to implement the optimizations described in DESIGN.md, with each statement being well-designed.

schema.sql may also (but doesn’t need to!) show the thoughtful inclusion of triggers, stored procedures, and other similar sets of statements.

2 Points Good

schema.sql is mostly comprehensive and annotated. schema.sql should:

  • Include most of the appropriate SQL statements needed to represent the entities in DESIGN.md.
  • Include most of the appropriate SQL statements needed to implement the optimizations described in DESIGN.md.

1 Point Below Expectations

schema.sql is included, but has opportunities for improvement. schema.sql may:

  • Not include most of the appropriate SQL statements needed to represent the entities in DESIGN.md.
  • Not include most of the appropriate SQL statements needed to implement the optimizations described in DESIGN.md.
Queries

3 Points Above Expectations

queries.sql is comprehensive and clearly annotated. queries.sql should:

  • Include all of the SQL statements needed to implement the functionality described in DESIGN.md, with each statement being well-designed.
  • Include especially illustrative examples that convey how the database is intended to be accessed.
  • Consider how the database is to be both read and written to, if applicable per DESIGN.md.

2 Points Good

queries.sql is mostly comprehensive and annotated. queries.sql should:

  • Include most of the SQL statements needed to implement the functionality described in DESIGN.md.
  • Include good examples that convey how the database is intended to be accessed.

1 Point Below Expectations

queries.sql is present, but has opportunities for improvement. queries.sql may:

  • Not include most of the SQL statements needed to implement the functionality described in DESIGN.md.
  • Not include illustrative examples that convey how the database is intended to be accessed.
Style

3 Points Above Expectations

Code is readable, consistent, and written with care. The submitted files must be:

  • Consistent in indentation, bracketing, spacing, and other style conventions, with no noticeable errors.
  • Thoroughly and appropriately commented.
  • Easy to understand without substantial prior knowledge and with minimal time.

2 Points Good

Code style was given thought and executed decently well. The submitted files must be:

  • Fairly consistent in indentation, bracketing, spacing, and other style conventions.
  • Decently commented.
  • Possible to understand without substantial prior knowledge.

1 Point Below Expectations

Code style reflects little effort. The submitted files might be:

  • Highly inconsistent in indentation, bracketing, spacing, and other style conventions.
  • Not commented.
  • Not possible to read without substantial prior knowledge or time.

How to Submit

  1. Go to CSCI E-151’s Gradescope page.
  2. Select Final Project: Implementation.
  3. Upload your DESIGN.md, schema.sql, and queries.sql files. Also be sure to include any other supporting files, such as your entity relationship diagram.
  4. Submit this debriefing form.