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!
Project Design Document
Note: project numbers are allocated strictly in chronological order of registering -- no ranking of any kind is implied.
|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|
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.
|1||Tue 2/28 16:45||
|2||Wed 3/22 23:59||
|3||Sun 4/1 23:59||
|4||Wed 4/11 23:59||
|5||Sun 4/22 23:59||
|6||Sun 4/29 23:59||
|7||Fri 5/11 19:15||
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.
Mindful of the University's policy on plagiarism, I hereby solemnly declare that, aside from high level ideas, all project materials, including but not restricted to descriptions, diagrams, schemas, SQL statements and web pages and programs, will be developed uniquely, exclusively and independently by me.
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
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.
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,
Sample Queries - Relational
State the queries from Section 3 using relational algebra notation, given your schema from the previous Section.
Functional Dependencies 1
State all the functional dependencies that exist in your domain, given your initial schema.
Proof of Normal Form 1
Prove that your original schema is in Fourth Normal Form, or indicate why it isn't.
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.
Functional Dependencies 2
Show how all your previously identified functional dependencies are preserved in your final schema.
Proof of Normal Form 2
Prove that your final schema is in Fourth Normal Form.
Sample Queries - Relational
State the queries from Section 6 using relational algebra notation, given your final schema.
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
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.
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!
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.
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!!!
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.
Briefly describe what you will present and demonstrate. Include a URL to your presentation.
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!
$ YOURAPPDIR/bin/PostgreSQL --user=root --host=localhost --passwordYou will be prompted for your root password (which may be 'root', or something else - consult your APP manual). If no root password has been set, leave the --password off the command line.
PostgreSQL> \i dbsetup-yourbetawebuserid.sql;or, from your system's command line interface,
$ mydb < dbsetup-yourbetawebuserid.sql
$ mydb --verbose < testqueries-yourbetawebuserid.sql > testqueriesout-yourbetawebuserid.txtand submit the files "dbsetup-yourbetawebuserid.sql" and "testqueriesout-yourbetawebuserid.txt" with your project design document.
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.