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
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 |
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 |
|
| 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.
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.
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.
Sample Queries - Relational
algebra 1
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
algebra 2
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
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.
Project Demo
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.