CS145 Assignment #2

Due Tuesday, Oct 16, 2001

For this assignment, everyone needs an Oracle account. You must sign up for the account through Class Registration by 5PM Tuesday October 9 --- no exceptions.

Step 2 of Your PDA (Personal Database Application)

(a)

Please attach a copy of your E/R schema from the PDA part of Assignment #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. We'll announce on cs145-all when the accounts have been set up, which should be at least several days before this assignment is due. Use the Oracle Introductory 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 there is a good chance your login will be recognized. We'll need time to handle login problems such as someone who thinks they are registered for the class and isn't.

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

Problem Set

1. In this problem you will explore the world of venture capital. Your job is to design a database that holds information about startup companies and venture capital funds that invest in these startups. You need to model the following types of information:

   • Every venture capital (VC) fund is identified by its name and number, e.g. "Kleiner-Perkins, VII." A VC fund also has a size which is the amount of money to be invested by the fund, and a closing date by which the fund must invest the money.

   • VC funds invest in two types of startup companies: stealth and private.

   • Every startup has a name, address, and belongs to exactly one industry. While, in general, there may be several companies with the same name, within each industry company names are unique.

   • Every industry has a unique name, market size, and consists of several sectors. Sectors have names and projected growths. Sector names are unique within an industry but not across industries.

   • Stealth companies operate in a "stealth mode" and have a buzz factor. A stealth company is financed by at most one VC fund since it's difficult to remain in stealth mode when several sets of lawyers get involved.

   • A private company has a website and a CEO. Every private company belongs to exactly one industry sector. For ethical reasons, VC funds invest in at most one company in any given industry sector.

   • Finally, stealth companies may be acquisition targets of private companies.

   (a)

   Draw the E/R diagram that represents, as faithfully as possible, the above information. Make sure to denote all keys, relationship multiplicities, and weak entity sets, if any.

   (b)

   Design a relational database schema that represents the above information as faithfully as possible. You should use the methods described in class to convert your E/R design to a relational schema, but you are responsible for making sure that the relations you choose are appropriate. If you are not sure that your E/R schema is correct, consult the course staff (and acknowledge any help you get, of course).

2. Consider a relation R(A,B,C,D,E) and the following functional dependencies:

    AB -> C
    AC -> D
    CDE -> AB
    BD -> ?
   

   In the last functional dependency, the single attribute on the right side has been hidden and replaced with "?".

   (a)

   Given that R has two keys of different sizes, find the hidden attribute and the two keys.

   (b)

   Is R in 3NF? If not, then show which of the three given dependencies violate 3NF.

   (c)

   Is R in BCNF? If not, then show which of the three given dependencies violate BCNF, decompose R into BCNF, and show all nontrivial dependencies that hold for the new relations in the decomposition.

3. Consider a relation R(A,B,C) and a functional dependency AB -> C.

   (a)

   Find an instance of R that satisfies the given dependency (AB->C) but does not satisfy any other nontrivial functional dependencies.

   (b)

   What is the minimal number of tuples in such an instance? Briefly, justify your answer.

4. Suppose we have a relation with schema ABC that satisfies the MVD A->->B.

   a)

   Can the relation have exactly three tuples? Explain why or why not. That is, give either a proof why not, if you believe it cannot have three tuples, or a counterexample, which in this case would be a relation with 3 tuples that satisfies the MVD.

   b)

   Can the relation have exactly two tuples that agree on A? Explain why or why not.

5. Suppose we have a relation with schema ABCD and the dependencies A->->B and B->C.

   a)

   What are the keys for ABCD? Remember not to list all superkeys; a key is minimal.

   b)

   Does the relation satisfy the MVD A->->C? Explain why or why not.

   c)

   Does the relation satisfy the FD A->C? Explain why or why not.

   d)

   Are there any 4NF violations? If so, what are they? Decompose the schema into 4NF schemas if so.