IST 210: Organization of Data

 	                  Spring 2009

                 Proj #2 (DUE: Mar. 31, 1 PM)

           Last Updated: Mon Mar  4 11:22:31 EDT 2009


NOTE: Team Assignment (also available in ANGEL)

Proj #2 has FOUR parts:

1. Fix-up

Fix up all errors that were pointed out in your proj #1. Revise your
ER design and associated assumptions, etc.  NOTE that if any parts
from Proj #2 still contain errors because you fail to fix them up, you
will lose points again. 

2. ER to Relational Model

Show relational schema that are to be generated from your ER
design. In particular, for each table, you need to determine:

- Attribute: name, data type, and nullable or not

- Primary Key (PK) -- note that primary key attribute can't be null
Pay extra attention to those "Relationship" -- depending on whether
they are unary/binary/ternary or cardinality/modality, the resultant
table schema may vary.

- Foreign Key (FK) and Referential integrity (RI) constraint (ie,
delete-cascade, delete-restrict, or delete-null policy).

3. Data Population

For each table, populate at least *FIVE* bogus tuples. Since you
may need to modify your schema later, do not waste your effort on
populating a lot of tuples. In Proj #3, you will need to populate more
number of tuples per table.

Using your tables and data, show three exampls SQL queries (see Chap
5) that demonstrate: SELECT, PROJECT, and JOIN.

4. Normalization

Show that ALL of your generated tables are in 3rd Normal Form.


Part 2: Turn-In of Proj #2 Report

Your report should consist of at least the following:

- On the cover page, list project team name (e.g., Team-01), and all
  member names

- FIXED ER and assumptions, and description as to why your ER can
answer 10 scenarios

- 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. For instance, 

Salesperson (Snumber:int, Sname:nchar(10), YearHire:year, OfficeRoom:nchar(2))

  PK: Snumber
  FK: OfficeRoom (refers to PK of Office table) 

- Show that all of your tables are in 3NF.

- A screen-shot of all tables (using Query Design Editor in SQL
Server). Note that all tables must be linked via foreign keys
correctly. If any table is isolated, then your schema population is
not correct.

- A screenshot of three example queries (ie, SELECT, PROJECT, and
JOIN) and their results returned

- Meeting log: a detailed log showing each meeting date and activity:

Date   Absent Members       Activity
2/3    John Doe             brainstorming
2/4    None                 initial ER sketch

Then, do *BOTH*:

1. Drop one copy of report to ANGEL
2. Turn in one hard-copy of the report to me

If your project team has some problems (eg, some members don't
come to project meetings), you need to inform me early so that I
can intervene. Otherwise, all team members share the identical
scores for the projects.