IST210 Project #2: Due Mar. 31, 2:30PM
Prof. Dongwon Lee, Penn State University
1. Fix-up
- Fix up all errors that were pointed out in proj #1 - revise your
ER design, associated assumptions, and queries. Proj
- You are free to drop some queries out and add new queries into
the 10 query list if you think you have better ones.
- 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
- 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
- Determine a 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.
- Specify every Foreign Key (FK) and Referential integrity
constraint
(ie, delete-cascade, delete-restrict, or delete-null policy).
- 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.
- For "TWO"
selected
tables, show the screen-shots of contents of table so that we can check
the data population has succeeded or not.
- (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
- Your report should consist of
the following:
- Cover page: List (1) project title,
(2) section number, (3) project team number, and (4) member
names
- Part 1: FIXED highlevel description
of
your project
- Part 2: FIXED E/R diagram
- Part 3: FIXED assumptions that you
made
- Part 4: FIXED 10 queries
- 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).
- 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)
- Part 8 (Bonus): Screen-shots of
THREE
selected SQL queries and their results.
- Part 9: Short description of WHO did
WHAT role in your team (all members must participate in the project)
- Drop to ANGEL:
- Like proj #1, only one copyt per team should be dropped to ANGEL
- This time, I'd like to also get one hard-copy
-- please
bring one hard-copy to class on due date