CS145 Assignment #6

Due Tuesday, Nov. 20, 2001

Step 6 of Your PDA

1. 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.

   Submit files showing 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. 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, but it is up to you how many new relations you choose to create. Write INSERT statements to populate your new relations from the data in the original relations of your PDA. Submit files showing the correct compilation of your declaration and correct execution of your insertion statements.

3. 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.
   • Explicit derefrencing (using the DEREF operator).

   Sumbit files giving your queries and their correct execution.

Problem Set

Dish (food, cuisine, calories)
Ingredients (food, ingredient, quantity)
Calories (ingredient, quantity, calories)

1. Sketch, in C or C++ with embedded SQL, a function that begins by zeroing the calories of all foods in Dish. Then, examine the Ingredients tuples, and for each, find the number of calories contributed to the food by the ingredient. Recall that you must look up the ingredient in Calories, and scale the calories by considering the quantities in both relations Ingredients and Calories. Finally, increment the calories in Dish by the calculated number of calories for this ingredient of this food.

2. Repeat Problem (1), but use CLI instead of embedded SQL. Again, a sketch giving the important database operations is sufficient. Note: The first three problems call for a lot of the same SQL statements. You can refer to SQL from problem (1) in Problems (2) and (3). We just want to make sure you know where the SQL goes for each style of programming.

3. Repeat Problem (1) using Java and JDBC. The same comments as in Problem (2) apply here.

4. Make a suitable declaration of object types for the three relations. All attributes can be strings or numbers, except:
   • cuisine must be of an object type that gives the name of the cuisine and the country of origin, e.g., (Szechuan, China).

   • Ingredients.ingredient must be reference to the appropriate Calories tuple.

   You should use the SQL standard, but Oracle's dialect is satisfactory if you wish to debug your answers.

5. Declare the three relations to have the appropriate object types.

6. Write a standard SQL query that finds the sum of the calories of all the ingredients in all the foods of all the cuisines of China. However, you must do so without accessing Dish.calories, which we may suppose (as in HW4) is something to be computed by examining Ingredients and Calories.