CS145 Assignment #6

Due in class Thursday, Nov. 21, 2002

Optional: May be handed in by class time on Tuesday, Nov. 26 without penalty

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.

   For declarations, don't forget type bodies when there are methods. Also remember that the slash is needed to cause compilation.

   Submit two files: datatypes.sql and datatypes.log
   The first file should contain SQL statements declaring different data types. The second file should contain a recording of your sqlplus session showing successful execution of your SQL statements. Write brief descriptions of SQL statements as comments in the .sql file. Click here to learn how to write comments in Oracle.

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 two files: relations.sql and relations.log
   The first file should contain SQL statements to create and to populate relations. The second file should contain a recording of your sqlplus session showing successful compilation of your declarations and correct execution of your insertion statements. Write brief descriptions of SQL statements as comments in the .sql file.

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 and use of that table type's value in a FROM clause.
   • Following REF's.
   • Explicit derefrencing (using the DEREF operator).

   Submit two files: queries.sql and queries.log
   The first file should contain SQL queries over your relations. The second file should contain a recording of your sqlplus session showing successful execution of your queries. Write brief descriptions of SQL statements as comments in the .sql file.