CS386/586
Introduction to Databases – Fall 2009
Homework
7
In
this assignment you will develop a database application of your choice. This is to be done as an individual or in a
team of two. You are encouraged to select
an application that at least one of you is familiar with. This will allow one
of you to serve as a domain expert for your assignment. Or, if you are
able to arrange to talk to one or more outside domain experts, that would work
as well.
Your
application must be accessible via a browser.
You may use any HTML scripting language you wish; I will give a lecture
on PhP because I think it has the shortest learning curve.
You
will implement your relational database schema using a relational DBMS
(e.g., PostgreSQL, MySQL, ...) and load it with realistic data (that can
provide answers to the queries that you write).
You can get a PostgreSQL or a MySQL database from CAT – see http://www.cat.pdx.edu/web/database-services.html.
Here are the deliverables:
1. A high-level introduction to your application, in English (
one page). Grammar and spelling count
here and henceforth.
2. Three to five use cases for your application. Each use case
should have a name and should identify the actor(s) involved along with a numbered, step-by-step description of what the actor will accomplish (when
the use case follows the normal course of events and is successful). You
should list exceptions to the normal course of events, in a separate section at
the end of each use case. You will be
implementing three of these use cases. They should involve SQL SELECT, INSERT, and UPDATE statements, so you may want to plan
ahead, though we will not check for this in assignment 3.
NOTE: The above two
deliverables, and part of the next deliverable, were submitted as part of
Homework 4. You must submit them
again. They need not be the same as
those you submitted for Homework 4.
1. Two or three ER diagrams that you considered during the development of
your application. Each database ER diagram (in UML notation), must be accompanied by a description of the entities,
relationships and attributes if necessary to understand them. This is NOT the database schema, so it should
not include foreign keys. It should include
only entities and relationships and their attributes. Include cardinalities for both sides of each relationship set and give a name to each relationship set. Your ER diagrams
should each contain at least SIX entity sets altogether.
2. A short writeup discussing the advantages and disadvantages of each of your ER diagrams, including a
rationale for which diagram you chose to translate into a schema of tables and
implement.
3. Your relational database schema, which you
translated from your chosen ER design and implemented, in the form Table(att1
PRIMARY KEY, att2 FOREIGN KEY REFERENCES…). Include a listing of sample data for every table. Show enough sample data to give a clear idea of what's in your
database, but not more than 10 rows in any table. Show the sample data in tabular form. Try to use names for your tables that are
closely related to the corresponding entity/relationship names.
4. Your code should implement at least three
of your use cases. The three use cases,
taken together, should access at least FOUR different tables and should include at
least one SQL SELECT, INSERT, and UPDATE statements. The insert use case should insert data into
the main database and a select use case should display the inserted data. The update use case should update one field
at a time in the main database and a select use case should display the updated
data. When you list the use cases,
identify which ones demonstrate your SELECT, INSERT and UPDATE capabilities.
5.
A
demo of your application that demonstrates three use cases that you have
implemented. These three use cases
should be user friendly. In particular,
I should be able to understand what the application is, and what to do, by reading
what is on the screen. I should never
have to use the back-page arrow. There
should be at least one entry that checks for errors (date, numeric) and
recovers gracefully from error, in particular the user should not have to
re-enter data. Every entry except the
initial login and password should be clear to the user – dropdown menus are a
common way to handle this. Your app
should be beautiful and centered in each window.
I will schedule a time for you
to demo deliverable 7 to me. At that
time you can deliver hard copies of deliverables 1-5 to me.
If you are not sure about any
part of this project, see me early, like now.