UNIVERSITY OF ROCHESTER

DEPARTMENT OF COMPUTER SCIENCE

CSC 296 - Database Systems

Term Project

Revised:  2012-05-12


You will demonstrate your mastery of the material taught in this course with a multi-phased term project. Below is a time table and a description of the project phases.

Each student will implement a non-trivial database to support a particular activity, event, or organization. The initial project phases will concentrate on the construction of the database; the final phases will add a web-based UI with sufficient power to retrieve and display information from the database, and to allow new data to be added to the database. Each student must implement a different database system - no duplicates. First come, first serve, so register your project domain with me as soon as you can!

Current Projects
Project Schedule
Project Design Document
Database Implementation
Project Ideas


Projects

Note: project numbers are allocated strictly in chronological order of registering -- no ranking of any kind is implied.

Nr Student Project Design
1 Adina The Cook's Assistant 1 2 3 4 5 6 7
2 Jason Unified Wishlist 1 2 3 4 5 6 7
3 Rachel UAA Women's Soccer League 1 2 3 4 5 6 7
4 Grant MVC File Organizer 1 2 3 4 5 6 7
5 James Event-Level Flow Cytometry Data  
6 Junhuan Course Registration and Grade Management System 1 2 3 4 5 6 7
7 Harry Open Tennis Database 1 7
8 Sam A Database for Video Game Developers 1 2 3 4 5 6 7
9 Yanshu Band Scheduling System 1 2 3 4 5
10 Piset PlayStation3 (PS3) Games Ordering System 1 2 3 4 5 6 7
11 Rintaro Stars & Planets 1 2 3 4 5 6 7
12 Aaron Crew Team Management System 1 7
13 Robert Airline Reservation System 1 2 3 4 5 6
14 Junlei Medical Equipment Management 1 2 3 4 5 6 7
15 Weiyang Classical Music CD online shopping website 1


Project Schedule

Note: Starting with Phase 3 submissions, a penalty will be applied to late submissions at a rate of 10% per 12 hours or part thereof.

Phase Due Description
1 Tue 2/28 16:45
2 Wed 3/22 23:59
3 Sun 4/1 23:59
4 Wed 4/11 23:59
  • Copy your current Project Design document to "dbsys-yourbetawebuserid-v4".
  • Update version 4 by revising Sections 1-12 given the feedback you received and make any changes or additions you deem appropriate.
  • Add the following Sections:
    1. Database Implementation Status
  • Submit, via email (PDF), the latest version of your project design document.
5 Sun 4/22 23:59
6 Sun 4/29 23:59
  • Copy your current Project Design document to "dbsys-yourbetawebuserid-v6".
  • Update version 6 by revising Sections 1-15 given the feedback you received and make any changes or additions you deem appropriate.
  • Add the following Sections:
    1. Web Site DB Modification Status
  • Submit, via email (PDF), the latest version of your project design document.
7 Fri 5/11 19:15
  • Copy your current Project Design document to "dbsys-yourbetawebuserid-v7".
  • Update version 7 by revising Sections 1-16 given the feedback you received and make any changes or additions you deem appropriate.
  • Add the following Sections:
    1. Additional Database Capabilities
    2. Additional Web Site Capabilities
    3. Project Demo
    4. MIDTERM II ALTERNATIVE
  • Submit the latest version of your project design document. Note that this submission must be via email (PDF) AND on paper in person by the deadline. You must be ready to present by 19:15 precisely. There will be 12 presentations, one every 15 minutes. You will have 10 minutes to do your presentation and live demonstration. Please make sure you have everything ready to go on your laptop. If you don't have a laptop, email your slides (with URLs that work!) to me by 18:30. (You would be smart to base your presentation on your project design document, with no more than 3 or 4 slides total.) The remaining 5 minutes will be used for questions and for switching students. The order of each presentation will be decided at random, at the beginning of each segment. All students are required to attend the full 3-hour session, as they will be peer reviewing the other projects.

    Peer Review Template


Your Project Design Document

This document will be an "evergreen" document. You will keep it up-to-date, and include it (in PDF form) with every project submission. If you have a way to indicate in the margins what is new or changed relative to the previous version, that would be appreciated, but it's not required.

Your project design document will have the following sections. Please be sure to use the same section labels and numbers as presented here! You are free to add subsections as you see fit.

  1. Preamble
    Provide the following information:
  2. Domain Description
    Provide a reasonably comprehensive description of the domain of the project -- what the system will, may and won't do. (Approx. 250 words) DO NOT DESIGN YOUR DATABASE HERE! See the Project Ideas section for examples.
     
  3. Sample Queries - English
    State at least 5 substantially different queries that your system will handle, in English, where "substantially" means, for instance, involving different objects in your domain, and significantly different criteria. At least three queries must involve several entities simultaneously.
     
  4. Entity-Relationship Diagram
    Create an Entity/Relationship Diagram of your project database. Be sure to start with some hand-drawn sketches. Minimum requirements: at least 5 entity sets, and at least 20 attributes (total), and enough relationships to connect each entity set to at least one other entity set. Be sure to indicate degree constraints (many-many, many-one, and one-one), open or closed arrows and multiplicity indicators where appropriate, and primary keys.
    Note: Do not give in to the temptation to design a super-sized mammoth database that does everything you might possibly be able to think of in your domain. Stick as closely to the minimum as you think is reasonable and complete all the work for this phase. Even if you have time and energy left over to do more, hold that thought until after the next phase of the project...

    When you are confident you have a fully qualified diagram, render your diagram using an E/R design tool, such as ConceptDraw. See also Wikipedia for some E/R Diagramming Tools.
    Note: you are NOT expected to purchase any tool to do this part! Many of these tools are available free for a 30-day trial.

    Here are some Stanford student examples of E/R diagrams: 1a, 2a, 3a.

  5. Relational Schema 1
    Convert your E/R diagram to a relational database schema, using the algorithm as described in Section 4.5+ of the textbook. You can use your favorite writing tool to do this (as long as it can underline key attributes).
    Note: Resist the temptation to do this first, or even simultaneously with the previous section. It will be so much easier and quicker to do this AFTER you have completed your E/R diagram.

    Here are the relational schemas corresponding to the student E/R examples above: 1b, 2b, 3b.

  6. Sample Queries - Relational algebra 1
    State the queries from Section 3 using relational algebra notation, given your schema from the previous Section.

  7. Functional Dependencies 1
    State all the functional dependencies that exist in your domain, given your initial schema.

  8. Proof of Normal Form 1
    Prove that your original schema is in Fourth Normal Form, or indicate why it isn't.

  9. Relational Schema 2
    If your initial schema is not in 4NF, follow (and document here) the process described in Chapter 3 to convert your initial schema into your final schema, given your functional dependencies.

  10. Functional Dependencies 2
    Show how all your previously identified functional dependencies are preserved in your final schema.

  11. Proof of Normal Form 2
    Prove that your final schema is in Fourth Normal Form.

  12. Sample Queries - Relational algebra 2
    State the queries from Section 6 using relational algebra notation, given your final schema.

  13. Database Implementation Status
    Build your database in PostgreSQL, as described in Database Implementation. Indicate that your database is live on the course DBMS server, and include each table definition (SQL) as well as the number of tuples in each table. (Do NOT include the data itself.)

    Also include the URLs (on the course web server) of three files, namely your

  14. Web Site DB Query Status 1

    Create a home page for your project (index.php) with the name of your system as the title. Add any other info as you see fit.
    Include the URL to this index page.

    Describe in this Section the contents and purpose of ONE web page that performs a simple query on your database and displays the results. The query is expected to be hard-wired into the page, but the results are dynamic, of course. (In other words, if you use the PostgreSQL monitor to change your DB, this web page should reflect the change, after refreshing.)
    Include the URL to this web page in this Section, and add a link to your project home page.
    Note: The importance is to get the content/behavior of the web page correct; do not worry about the esthetics!

  15. Web Site DB Query Status 2
    Describe the contents and purpose of a SECOND, substantially different web page that performs a complex query on your database (involving at least two tables) and displays the results.
    Describe the contents and purpose of a THIRD, substantially different web page that accepts a search string from the user and displays the results of a DB query using that search string.
    Name these pages in a way that makes sense in your domain.
    Include the URLs to these web pages.
    Same note applies as before!

  16. Web Site DB Modification Status
    Describe the contents and purpose of a FOURTH web page that allows the user to insert new information into the system, and a FIFTH web page that allows the user to update existing information in the system, and for both cases to display the results. (You'd be smart to design it so you can display the results of the insertion or update using the same mechanism used by the third page of the previous section.) Note also that your users do not need to (and probably don't want to) know about your table design. What you let them insert or update should make sense as a unit of information, and may be just part of a table, or may in fact span multiple tables.
    Include the URL to this web page.

  17. Additional Database Capabilities
    Add foreign key constraints to all your table schemas. Also describe the ways you have augmented your database to incorporate additional features and capabilities, such as value or other constraints or triggers. Be sure to update the earlier parts of your design document as appropriate!!!

  18. Additional Web Site Capabilities
    Describe the ways you have augmented your web site to incorporate additional features and capabilities.
    Include the URLs to the relevant web pages.

  19. Project Demo
    Briefly describe what you will present and demonstrate. Include a URL to your presentation.

  20. MIDTERM II ALTERNATIVE
    NOTE 1: Your Midterm II grade will be based on this section, or if this section is missing from your design document, will be copied from your Midterm I grade.
    NOTE 2: This section involves additional database capabilities ABOVE AND BEYOND the capabilities added in Section 17!

    1. It is very common in large systems to provide for an audit trail of changes to the DB. The best way to do this is to have a full history mechanism. A simpler but more limited facility adds two attributes to each relation, namely CreatedOn and ModifiedOn. These are set to the current date and time (timestamp) when a tuple is created and modified, respectively. (Real systems also have a CreatedBy and ModifiedBy which is set to the id of the current user.) It is important that the database takes care of setting these values, rather than the application code, so the setting must be done as part of the table schema or by use of triggers.
      Add this form of audit trail mechanism to all your entities.
       
    2. It is also common for large systems, where data grows over time, to have an archive capability that copies older entries to an archive database. Create a stored procedure that takes a cutoff date as a parameter and copies all tuples that haven't been touched (updated) since the cutoff date to the archive and then deletes the originals. Since you only have one database to work with, create shadow tables for your entities instead of an archive database, e.g., if you have a table Widgets, create a table ArchivedWidgets. You must do this using transactions to prevent inconsistencies in your DB due to unsafe deletions.
       
    3. Demonstrate these capabilities (audit trail and archiving) using an SQL script analogous to 'testqueries' and provide both the SQL script and the text file capturing the output of an execution of the script.


Database Implementation


Project Ideas

Here are a few suggestions for such a database system. You are free to come up with your own ideas, but you will have to provide sufficient detail in phase 1 for me to determine that your project is substantial enough. The suggestions below should give you a reasonable idea of the complexity you should aim for, and the kind of description you need to provide in Phase 1.

Obviously, there are lots more possibilities, such as movie, book, or song databases, election results, bird sightings, real estate properties for sale, football data, consumer product information (ratings?), etc. There's lots on the web, of course, for example here. Or you can do a Google search for database systems example projects in the .edu domain. Use your imagination!

Ideally, you pick a domain where lots of data is already available. This can make your database more interesting to use, and may make it easier to test various types of queries. However, the project challenge lies in the design of the database and various complex queries to extract and update information. As long as you have enough data to demonstrate the effectiveness of your design, you're ok.