CS386 Introduction to Databases
Spring 2006 Quarter

Assignment 5 ¨C Database design and implementation

Due in two parts: Thursdays, 18 May & 25 May 2006, at the beginning of class (see below for what is due each day

You are encouraged to do this homework in groups of two or three people.  Each team should turn in one assignment paper, with all of your names on the paper. You should only talk to the instructor, the TA and your partners about this assignment. Exception: You may consult with anyone on details of getting a database system set up for your team. You may also post questions to the course mailing list, cs386@cs.pdx.edu.

Please turn in your completed assignments on paper. Put your last name, first name, the assignment number in that order in the first line of your assignment.  List last name and first name for partners on subsequent lines

Steps for this assignment:

  1. Describe the processing needs of an application that you are familiar with by drawing a use case diagram - indicating the main actors with their main use cases for the application.  Write brief descriptions of one or two paragraphs for at least three of the main use cases.  You are encouraged to select an application that at least one of your team members is familiar with. This will allow one (or more) of your team members to serve as a domain expert for your assignment.  Or, if you are able to arrange to talk to one or more domain experts that are not on your team, that would work as well.

 

  1. Develop several candidate database designs for your application.  Each of your database designs will be expressed in an Entity-Relationship Diagram.  You may use conventional or UML-like notation for your ER diagram.  (Note: the UML notation does not permit the representation of relationships that involve three or more entities.)  Your ER diagrams should be of moderate complexity, with roughly 7 to 10 entity sets.

 

  1. Consider the advantages and disadvantages of each design considering how well each supports the needs of your application (see Step 1, above) and also how likely they are to handle future needs of the application. Select one of these designs to translate to a relational database schema.

 

  1. Implement your relational database schema using a relational DBMS (e.g., PostgreSQL, MySQL, ...) and load it with sample data (that can provide answers to the queries that you write in Step 5, below).  If you would like to implement your solution on a PostgreSQL or MySQL database on PSU computers, you can have the CAT set up a database for you to access and modify.  See http://www.cat.pdx.edu/web/database-services.html for more information. (Note: Don¡¯t enter data into your database manually. Create a file or script and use that to load it ¨C it will be much easier if you have to reload because of a schema change or data corruption.)

 

  1. Write at least 3 SQL queries against your database, one for each use case in Step 1. Run these queries against your sample data.

Please turn in the following items (Items 1.-4. on 18 May; Items 5.-8. on 25 May)

  1. (10 points) A high-level introduction to your application, explaining the purpose of the (new or improved) database and associated software (one page write-up, at most).
  2. (10 points) The Use Case Diagram plus a write-up of three to five use cases for your application.  Each use case should identify the actor(s) involved along with a 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.
  3. (20 points) Two (or more) database designs.  Each database design consists of an ER diagram (in UML-like notation, if you prefer), plus a description of the entities, relationships and attributes plus a short write up describing the main features of the design.  This is NOT the relational database schema, so it should include only relationships and entities.  Include minimum and maximum cardinalities for both sides of each relationship set and a name for each relationship set.
  4. (10 points) A write-up discussing the advantages and disadvantages of your designs, including a rationale of which design you chose to translate into a relational schema and implement.
  5. (10 points) The SQL queries that you will use to support at least three of your use cases.
  6. (20 points) Your relational database schema, which you translated from your chosen design. Be sure that your relational schema is derived from your ER diagram using the techniques in the text and lectures. You can supply the schema in the form of CREATE TABLE statements, if you wish.
  7. (10 points) A listing of your sample data (show only up to 5 rows per table).
  8. (10 points) The answers to your SQL queries from part 5 (again, 5 rows maximum).