Final Project: Build a Database

Complete by: Monday 9 Dec., at class time.
See the bottom of the file for additional checkpoints, all due at class time.

This prompt was written by Dr. Rapp and lightly adapted by me for our class.

In this project, you will plan, design, and develop a database on a topic of your choice as if you were going to release it online for public use. Sample topics could include…

• Collecting your favorite recipes, the ingredients for dishes, the best farmers markets you’ve found, etc.
• Developing the backend for an online voting system (for a small group or organization)
• Tracking the movies, games, music, and/or books in your personal collection / wishlist

You may not build a database from a dataset of sports statistics or about professional athletes. You may not collect data about people without working with W&J’s Institutional Review Board. If you would like to collect data for your final project, please meet with me ASAP!

Your database must feature no less than eight tables, store a minimum of 25 attributes and 80 rows across all tables¹, and store ten reasonably distinct queries representing documented use-cases. Those queries must make use of the following operations:

• At least five queries with JOINs; two of these must use an OUTER JOIN
• At least three queries should use an aggregate function; one must use a GROUP BY
• Your queries should generally be ORDERed BY the use-case of the query
• At least five queries should use a WHERE clause with variables that your user can control
• At least two queries should combine three or more tables

You will also add a minimum of two trigger functions to automatically perform certain transactions within your system. You must use a minimum of three SQL keywords that were not covered in class.

You must provide all relevant documentation as a single PDF which you will name “README.pdf”, to make obvious to the users of your system that it is intended for them to read! The README should be a cohesive document with good flow and must feature your database’s purpose and classification, three user personas², a use-case diagram illustrating the actions that intended actors can perform, the complete data dictionary and entity relationship diagram documenting your database design, and instructions written for the person developing your interface detailing the function calls for the particular use-cases you’ve designed your system to support³.

You are expected to work on this project out of class until the due date of 1pm on Monday, December 9. You are welcome to work with your peers, with the PAL tutors, and with me on this project, but if you choose the same database as a friend you may not share queries, trigger functions, or new keywords unless given written permission from me. To ensure timely completion and prevent end-of-semester stress due to procrastination, I will require pieces of the project submission before the final due date. Here is the timeline for the final project:

• Monday, November 11: Email me your topic by 5pm

• Friday, November 15: Class will not be held to make time for 1:1 meetings, to which you will bring…

  – A draft of all of your user personas
  – A draft of your first pass at a use case diagram
  – A draft of your ERD if and only if the prior two are fully complete

• (Check-in) Monday, November 18: You should submit your in-progress UCD and ERD.

• (Check-in) Monday, December 2: You should submit your in-progress SQL file and README.

• Monday, December 9: Your complete project is due through Sakai by 1pm.

The following criteria will be followed to evaluate this assignment on a 100-pt scale:

• 40 pts: README is submitted as a single PDF and contains all of the following:

  – Description of the purpose / classification of the database
  – The three user personas which guided the development of your system
  – Use-case diagram with useful information about the actors / system functionality
  – Data Dictionary with useful information about the meaning of your data
  – Entity-relationship diagram with useful information about referential integrity
  – How-to instructions for the software developer working on your interface

• 50 pts: SQL file(s) containing your developed schema contain all of the following:

  – DDL build and connect a minimum of 8 tables and 25 attributes
  – DML to insert a minimum of 80 rows of data to your database
  – DDL/DML to store at least ten distinct queries meeting the requirements outlined
  – DDL to develop at least two trigger functions which have meaningful purpose to the system
  – At least three new SQL keywords beyond what was covered in class

• 10 pts: Database meets the intended purpose and is consistent with both the UCD and the ERD

Footnotes

1. If it is completely impossible to hit the requirements as indicated given the purpose of a database of your creation, you may ask for an exception to this rule. Any project seeking this exception must have been granted accommodation (through an email confirmation) prior to the Thanksgiving break.

2. See footnote 1!

3. As an example, this might include connecting a particular use-case to a particular function. As an example, one might write the following as an instruction: “Users are able to search for the phone number of a particular contact (e.g., John Marine). The function query_contact_phone takes two text arguments, representing the first and last name for the contact of interest. An example function call in SQL would look like: SELECT ∗ FROM query_contact_phone(’John’,’Marine’);”