CS145 Assignment #2

Due Thursday Oct. 21, 2004, 2:45PM

For this assignment, everyone needs an Oracle account. If you are registered for the class and have given AXESS a Stanford login name, that will be the name of your Oracle account. If you did not do so, but feel you warrant an Oracle account anyway, the CS145 staff has no control over the matter. You need to send mail to db @ stanford.edu and state your case. We will notify the class when your Oracle accounts are open. Until then, please do not assume something is wrong if you cannot execute the login procedure described in The On-line Oracle Guide.

Step 2 of Your PDA (Personal Database Application)

(a)

Please attach a copy of your E/R schema from PDA Part 1. If you have modified your design because of TA feedback (or any other reason), please hand in the modified design instead; the new design will not be graded but will be compared with your relational design.

(b)

Use the method for translating an E/R diagram to relations described in class and the text to produce a set of relations from your E/R design. Specify your relational schema using the notation of Section 3.1.2, and please be sure to underline key attributes.

(c)

Are there any flaws in the relational database schema you get from part (b)? Are there opportunities to combine relations without introducing redundancy? If so, indicate which, and if not, tell us there are none. Are there examples of non-BCNF relation schemas? If so, do you want to decompose them? For each opportunity to combine or decompose relations, decide whether or not to do so, and explain your reasoning briefly (e.g., tell us what queries you expect will be typical for your database, and tell how the design you pick facilitates them). Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer. You will be working with this schema quite a bit, so it's worth spending some time to make sure you're happy with it.

(d)

Login to Oracle, using your Oracle account. Use the The On-Line Oracle Guide to see how to login and change your password. Try some simple commands, such as help or table (relation) creation. No credit will be given, but it is important that people try logging in as soon as possible.

Don't forget to save a copy of your PDA for reference as you do Step 3 of the PDA.