IST210 Project #2: Due Mar. 31, 2:30PM

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. Proj
  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, and nullable or not
    2. Determine a Primary Key (PK) -- 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.
  2. Specify every Foreign Key (FK) and Referential integrity constraint (ie, delete-cascade, delete-restrict, or delete-null policy).
  3. For each table, populate 3-4 tuples. 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 "TWO" selected tables, show the screen-shots of contents of table so that we can check the data population has succeeded or not.
  5. (Bonus 10 points) Look at Chapter 9 (SQL) ahead and implement "THREE" queries from your 10 query list in SQL. 3 queries must be types of SELECT, PROJECT, and JOIN (see explaination in Page 100-106 of Chapter 5), respectively. Run three queries and show the results as screen-shot. For instance, if you have a table Foo, a query "SELECT * FROM Foo WHERE some-conditions" 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 THREE 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 number, 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 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). 
    7. Part 7: Screen-shots of TWO selected tables showing their contents (do not show contents of all of your tables -- it's too much for me to check)
    8. Part 8 (Bonus): Screen-shots of THREE selected SQL queries and their results.
    9. 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, only one copyt per team should be dropped to ANGEL
    2. This time, I'd like to also get one hard-copy  -- please bring one hard-copy to class on due date