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

Prof. Dongwon Lee, Penn State University

Modified from the PDA (Personal Database Application), 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 Microsoft SQL Server provided by IST Infrastructure. You will populate the database on your own, write interactive queries and modifications on the database, create programs that manipulate the database, and finally develop a simple (PHP-based) 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:

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 (use Word or VISIO for drawing). 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 specify key attributes by * symbol for each entities. Also, specify cardinalities and modalities for all relationships. In particular:
    1. 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!
    2. You should certainly include different kinds of relationships (e.g., unary, binary, ternary), cardinalities/modalities (1-1, 1-n, n-n), different kinds of data as your attributes (e.g., strings, integers, or even picture, etc.), and special types (e.g., weak entity, intersection data), etc. Try to demonstrate your understanding.
  3. Consult the PBL 3.1 AFV  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 10 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. Describe what kind of indexes you will need for five selected queries.

Part 2: Turn-In

  1. Your report should consist of at least the following:
    1. Cover page: List (1) project title, (2) section number, (3) project team name (i.e., A-H), and (4) member names
    2. Page 1: Highlevel description of your project
    3. Page 2: E/R diagram
    4. Page 3: Assumptions that you made
    5. Page 4: 10 queries
    6. Page 5: 5 Indexes
    7. Page 6: Short description of WHO did WHAT role in your team (all members must participate in the project)
  2. By due date (before class begins):
    1. Only the project leader drops the report at ANGEL once (on-line submission only).
    2. The remaining members still submit (empty submission) at ANGEL -- due to the limitations of ANGEL, to assign scores to all students, we need one submission per student. We will grade the team score based on the report that the leader submitted, and assign the SAME score to all team members.
    3. Feedback and project grading by the professor will be given at AGNEL (on-line) as well.