CS145 Assignment #6

Due Monday Nov. 23, 1998

Step 6 of Your PDA

1. (20 pts.) Declare some data types that could be used with the data of your PDA. You need to choose types such that:
   • There are at least two methods included among these types; not every type must have methods, however.
   • At least one type is suitable as a column type (abstract data type).
   • At least one type is suitable as a row type (type of a relation).
   • At least one type is a table type, defined from one of the other types you declared.

   Show your declarations (don't forget type bodies when there are methods) and the successful compilation of the declarations. Remember that the slash is needed to cause compilation.

2. (20 pts.) Declare some relations that could hold data associated with your PDA. Choose relations so that:
   • At least one relation has a column type defined in (1) for one of its attributes.
   • At least one relation should be of a row type that you defined in (1).
   • At least one relation should have a table type defined in (1) for one of its columns.
   • At least one reference type (REF) must be involved, either in your relation declarations or in the types from (1) themselves.

   Note that it is possible for one relation to satisfy all these conditions. Write INSERT statements to populate your new relations from the data in the original relations of your PDA. Show the correct compilation of your declaration and correct execution of your insertion statements.

3. (20 pts.) Write 4 or more queries on your relations from (2). Among these queries, you should demonstrate the following features at least once:
   • Use of methods.
   • Access of data within a column type.
   • Access of data within a table type.
   • Use of a table type's value in a FROM clause.
   • Following REF's.
   • Derefrencing.

Problem Set

Questions 1 - 4 are based on the following figure. As shown in the figure, each author has his/her name, affiliation and address. Each conference has its name, year and location, and the location includes the city, state and country. Each publication has its title, (one or many) authors, and conference in which this publication is published.



1. (8 pts.) Write an Oracle-8 declaration for the Conferences relation using a row type. The row type should include one method: Given one year, the method will return a value that indicates whether this conference is published before this year or not. Also use a column type to define the location attribute.

2. (4 pts.) Write an Oracle-8 declaration for the Publications relation. Use a nested table to specify that the authors attribute is a table of author objects. The conference attribute should be a reference to a conference object. Remember that we don't want to include all the data about authors in the nested relation for every one of their publications. How do we avoid the redundancy?

3. (4 pts.) Write an Oracle-8 query to find all the titles of publications that were published in the conferences held in 1998 in "Seattle."

4. (4 pts.) Write an Oracle-8 query to find all the names of Stanford authors who have publications in the conference "SIGMOD."

   3NF and 4NF

5. (6 pts.) Consider a relation with the schema ABCD with a set of functional dependencies: S = {ABC->D, BCD->A, A->B}. For which of the 8 subsets of these dependencies is the relation in 3NF but not BCNF?

6. (6 pts.) A schema is in BCNF (3NF, 4NF) if and only if every relation in this schema is in BCNF (3NF, 4NF). Consider the following two relational schemas:

   Schema 1: R(A,B,C)

   Schema 2: S(A,B), T(A,C)

   Suppose that the only dependencies that hold on the relations in these schemas are A ->>B, and A ->> C.

   (a)

   What is strongest form (BCNF, 3NF, 4NF or none of these) is Schema 1 in? Justify your answer.

   (b)

   What is strongest form (BCNF, 3NF, 4NF or none of these) is Schema 2 in? Justify your answer.

7. (8 pts.) Prove the correctness of the transitive rule of multivalued dependencies.