UNIVERSITY OF ROCHESTER

DEPARTMENT OF COMPUTER SCIENCE

CSC 290C - Intro to Database Systems

Term Project

Revised:  2009-05-02


You will demonstrate your mastery of the material taught in the CSC Intro to Database Systems 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 both retrieve and display information from the database, and 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 Links
1 Vlad Tatulescu Teams and Players in the NBA Web | Design 1 2 3 4 5 6 7
2 Bryan Jacobs Tag System Web | Design 1 2 3 4 5 6 7
3 Joseph Kachinsky Assassins Game database Web | Design 1 2 3 4 5 6 7
4 Robert Ramsay Video Game Database Web | Design 1 2 3 4 5 6 7
5 Gautam Sharma Supplemental Lecture Summaries Web | Design 1 2 3 4    6 7
6 Erica Hyman Television Shows Web | Design 1 2 3 4 5 6 7
7 Greg Hartman Marvel Comics & Comic Book Characters Web | Design 1                7


Project Schedule

Phase Due Description
1 Mon 3/2 16:45
2 Wed 3/18 23:59
3 Wed 4/1 23:59
4 Wed 4/15 23:59
  • Copy your current Project Design document to "dbsys-lastname-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 Wed 4/22 23:59
6 Wed 4/29 23:59
  • Copy your current Project Design document to "dbsys-lastname-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/8 12:30
  • Copy your current Project Design document to "dbsys-lastname-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
  • 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 12:30 precisely. You will have 20 minutes to do your presentation and live demonstration. (You would be smart to base your presentation on your project design document, with no more than 6 or 7 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 conditions (WHERE clauses). 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 4 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.
    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 MySQL, 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 MySQL 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
    Describe the ways you have augmented your database to incorporate additional features and capabilities, such as 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.


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.