Project #1: Due Oct. 11 (Before Class Begins)

PDA (Personal Database Application), Courtesy of J. Widom

Your IST210 team project will be to build a substantial database application for a real-world domain of your choosing. You will design a schema on paper, and then you will create an actual database using DB2 provided by PSU. You will populate the database on your own (either via an automated program that you write or manual data hunting), write interactive queries and modifications on the database, explore other aspects such as constraints and triggers (if possible), create programs that manipulate the database, and finally develop a simple Web front-end to your database application.

Your first step is to identify the domain you would like to manage with your database, and to construct an entity-relationship (E/R) diagram for the database. We suggest that you pick an application that you will enjoy working with, since you'll be stuck with it for the whole semester! It is especially nice if you pick an application where you can populate your database using real (as opposed to fabricate) data. Possible examples are:

Try to pick an application that is relatively substantial, but not too enormous. For example, your E/R design should have in the range of five or so entity sets, and a similar number of relationship sets. Note that this is a ballpark figure only! You should certainly include different kinds of relationships (e.g., many-one, many-many) and different kinds of data (strings, integers, or even picture, etc.), but your application need not necessarily require advanced features such as weak entity sets, "is-a" relationships, or roles (but "strongly encouraged").

Part 1: E/R Design

  1. Write a short (maximum 1 page) description of the database application you propose to work with throughout the course. Your description should be brief and relatively informal. If there are any unique or particularly difficult aspects of your proposed application, please point them out. Your description will be graded on suitability and conciseness.
  2. Draw an E/R diagram for your proposed database. List all your assumptions. Otherwise, we will grade your design based on the common sense, and you may lose points if ours and yours are different. Don't forget to underline key attributes for entity sets and include arrowheads indicating the proper cardinalities of relationship sets. If there are weak entity sets or "ISA" relationships, make sure to notate them appropriately.
  3. Consult the AFV StoryPBL in class, where various queries that the given design is trying to to answer. In your scenario, you should go through the similar thinking process, and mention 5-6 such queries that you have in mind. For instance, if this is the E-Commerce scenario, your query might be "List all recent PDAs matching condition this and that..." or "insert new customer with home address and user id, etc."
  4. Using the method for translating an E/R diagram to relations, produce a set of relations for your database design. As usual, please be sure to underline key attributes in your relations. If your E/R design has ISA relationship, pick one of the three available approaches, and convert accordingly.

Part 2: Turn-In

  1. First, turn in one Hard Copy of your project #1 report by due date.
  2. Second, create a project team web page under PSU (must be somewhere in the PSU web space to use DB2 later), and list (1) team code (e.g., Team A-I assigned in a class), (2) names of all members, and (3) add a link to your project #1 report in it (so that other students in this class can visit and take a look at other teams' work.)
  3. Third, email the URL to your web page to TAs.