CS145 Assignment #2

Due Wednesday, Oct 14, 1998

Remember: the rules for deadlines and late homeworks are described in The First Homework.

Step 2 of Your PDA (Personal Database Application)

(a)
(15 pts.) Consider the ODL schema you designed for your PDA in Assignment #1. Please attach a copy of the schema. Use the method described in the text and in class for translating an ODL schema to relations to produce a set of relations for your database design. Please specify your relational schema using notation of Section 3.1.2, and underline key attributes. In cases where there are alternative mappings for ODL constructs (such as for sets and relationships), you may use whichever mapping you prefer.

(b)
(15 pts.) Consider the Entity-Relationship diagram you designed for your PDA in Assignment #1. Please attach a copy of the E/R diagram. Use the method for translating an E/R diagram to relations described in class and the text to produce a set of relations for your database design. Again, specify your relational schema using the notation of Section 3.1.2, and please be sure to underline key attributes.

(c)
(10 pts.) Which of the two relational schemas you obtained in parts (a) and (b) of this problem do you like better? 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, which should be set up early in October. 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. (10 pts.) This question is based on the following E/R diagram:

    It represents ``parts explosion, in which any part is either a basic part, or an assembly consisting of some number of one or more subparts. For instance, a bicycle might be described as an assembly consisting of one frame and two wheels, while a frame is a basic part and a wheel is an assembly consisting of one tire, one rim, and 48 spokes.

    (a)
    Convert the E/R diagram to relations, using the strategy described in the text for going from E/R to relations.

    (b)
    Your answer to (a) should have several relations whose schemas are the same or one is a subset of the other. Do you want to retain all these relations? Justify your answer. Note: there can be more than one right answer; we are more interested in your reasoning than in ``yes'' or ``no.''

  2. (10 pts.)
    (a)
    Convert the E/R diagram from Question 1 to an ODL description.

    (b)
    Then construct relations from your ODL, using the strategy for going from ODL to relations outlined in the text.

    (c)
    What differences, if any do you notice compared with your answer to 1(a)? Explain the differences or lack thereof.

  3. (10 pts.) This question is based on the following E/R diagram:

    The intent of ``semester(s)'' is that this attribute is a code indicating which quarters or semesters the course is offered at that university. For example, for the related pair ``CS145'' and ``Stanford,'' the ``semester(s)'' would be ``fall and spring quarters.''

    Convert this E/R diagram to relations, eliminating any redundant relations.

  4. (10 pts.) Suppose there is a relation with attributes (schema) ABCDE, and it is known that the only keys are ABC and CD (recall that a ``key'' must be minimal, in the sense that no proper subset of the key functionally determines all the attributes).

    (a)
    How many superkeys are there? Justify your answer.

    (b)
    Give two sets of functional dependencies that will make ABC and CD be the only keys. Your sets of FD's should each have the property that no proper subset of your FD's is equivalent to the full set; i.e., if you delete any FD, then the set of keys is something other than {ABC, CD}.

  5. (10 pts.) Consider a relation with schema ABCDEF and FD's: AB->C, CD->E, E->F, and F->A. One possible first decomposition step is to break this schema into ABC and ABDEF.

    (a)
    Give the set of FD's that hold for each of these two relations. You need not list every FD, but list enough so that all other FD's follow logically from the ones you give.

    (b)
    Are ABC and/or ABDEF in Boyce-Codd Normal Form? Justify your answer.

  6. (10 pts.) Consider a relation with schema ABC. There are six FD's of the form D->E, where D is one of A, B, or C, and E is another of them. If we pick two of these six FD's at random, what is the probability that the relation will be in BCNF? Justify your answer.