IST210 Project #2: Due Nov. 2 (Before Class Begins)

Prof. Dongwon Lee, Penn State University

1. Fix-up

  1. Fix up all errors that were pointed out in proj #1 - revise your ER design, associated assumptions, and queries and indexes.
  2. You are free to drop some queries out and add new queries into the 10 query list if you think you have better ones.
  3. NOTE that if any parts from proj #1 still contain errors because you fail to fix them up, you will lose points again. If you do not understand my comments, talk to me. I will help you.

2. ER to Relational Model

  1. Show relational schema that are to be generated from your ER design. In particular, for each table, you need to determine:
    1. Attribute name, data type, length, and nullable or not
    2. Determine a Primary Key -- note that primary key attribute can't be null
    3. Pay extra attention to those "Relationship" -- depending on whether they are unary/binary/ternary or cardinality/modality, the resultant table schema may vary.
    4. Optimize your relational schema by removing some unnecessary tables or merging multiples into one. If you have no such case, say so.
  2. Specify every Foreign Key and Referential integrity constraint (ie, delete-cascade or delete-restrict policy -- you can ignore delete-null policy).
  3. For each table, prepare 3-4 tuples in Excel and import them. Since you may need to modify your schema later, do not waste your effort on populating a lot of tuples -- just 3-4 tuples per table is enough for now. In proj #3, you need to populate more number of tuples per table.
  4. For "2" selected tables, show the screen-shots of contents of table so that we can check the import has succeeded or not.
  5. (Bonus 10 points) Look at Chapter 9 (SQL) ahead and implement "3" queries from your 10 query list. 3 queries must be types of select, project, and join (see explaination in Page 100-106 of Chapter 5). Run them in Query Analyzer and show the SQL query as well as results as screen-shot. For instance, if you have a table Foo, a query "SELECT * FROM Foo" is a type of select query, and a query "SELECT attr1, attr2 FROM Foo" is a type of project query. NOTE that you need to implement 3 of your suggested queries from the 10 query list.

3. Turn-In

  1. Your report should consist of 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. Part 1: FIXED highlevel description of your project
    3. Part 2: FIXED E/R diagram
    4. Part 3: FIXED assumptions that you made
    5. Part 4: FIXED 10 queries
    6. Part 5: FIXED 5 Indexes
    7. Part 6: Relational schema of all tables -- for each table, mention attribute name and type, then primary and foreign keys. Also, mention what referential integrity policy is used on what pair of attributes (delete-cascade or delete-restrict). If you optimized any tables, describe about those, and show the resultant tables.
    8. Part 7: Screen-shots of 2 selected tables showing their contents.
    9. Part 8 (Bonus): Screen-shots of 3 selested SQL queries and their results in Query Analyzer.
    10. Part 9: Short description of WHO did WHAT role in your team (all members must participate in the project)
  2. Drop to ANGEL:
    1. Like proj #1, the leader of each team drops the copy at ANGEL, and the rest of members drops a "blank" one at ANGEL
    2. This time, I'd like to also get a hard-copy  -- please bring one hard-copy to class.
Dongwon Lee
Last modified: Mon Oct 18 00:31:32 EDT 2004