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.