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
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 |
| 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 |
|
| 5 | Wed 4/22 23:59 |
|
| 6 | Wed 4/29 23:59 |
|
| 7 | Fri 5/8 12:30 |
|
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 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.
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 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
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
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!!!
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.
$ YOURAMPDIR/bin/mysql --user=root --host=localhost --passwordYou will be prompted for your root password (which may be 'root', or something else - consult your AMP manual). If no root password has been set, leave the --password off the command line.
mysql> set password for 'root'@'localhost' = password('SECRETROOTPW');Note that the single quotes are part of the SQL syntax and must be typed as shown.
mysql> create database YOURDBNAME;
mysql> create user 'YOURDBUSERID'@'localhost' identified by 'YOURDBPASSWORD';
mysql> grant all on YOURDBNAME.* to 'YOURDBUSERID'@'localhost';
mysql> exit;
$ YOURAMPDIR/bin/mysql --user=YOURDBUSERID --host=localhost --password YOURDBNAMEAgain, you will be prompted for your user password, which you specified when you created the user. Because you'll probably connect to your database like this many times, you may want to put this line in a command shell script and call it "mydb".
mysql> source dbsetup-YOURLASTNAME.sql;or, from your system's command line interface,
$ mydb < dbsetup-YOURLASTNAME.sql
$ mydb --verbose < testqueries-YOURLASTNAME.sql > testqueriesout-YOURLASTNAME.txtand submit the files "dbsetup-YOURLASTNAME.sql" and "testqueriesout-YOURLASTNAME.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.