Updating Rows within Nested Table Example

The new object-relational database contains an interesting pointer structure that permits a single cell in an entity to encapsulate a separate entity. In this way structures can be created where tables can be nested within other tables. This means that values in a single column inside a table can contain an entire table in an object-relational database. In turn, these child tables can have single column values that point to other tables.
This new data structure presents exciting possibilities for modeling aggregate objects, even though applications for it may not be obvious. Database designers can create a structure in C++ object-oriented databases, such as Objectivity, where an object contains a list of pointers. Each of these pointers refers to a separate list of pointers and these pointers point to other objects in the database. This structure is known as **char in the C programming language, which is known as a pointer to a pointer to a character.
This structure is implemented (beginning with Oracle 9i) with a store table. A store table is an internal table that is tightly linked to the parent table and the data storage characteristics of the parent table are inherited by the store table. These characteristics include the initial extent of the table as well as the size of any new extent.
A cell is defined as a pointer to a table in the highest level table and each column value within the column pointing to a whole table must contain a pointer to a table with exactly the same definition. In other words, every pointer within the column is restricted to pointing to tables with an identical definition. In practice, it may appear that each cell points to a whole table, but the object/relational databases actually implement this structure with the special store table.
A store table is essentially nothing more than an internal table with a fixed set of columns that is subordinate to the parent table. A simple example will illustrate the use of this data structure. Returning to the university database, the database has a many-to-many relationship between courses and student entities. A course has many students, and a student can take many courses. This relationship between students and courses would be implemented in a traditional relational system by creating a junction table between the student and course entities. The primary keys from the student and course tables would then be copied into the junction table. This table is called grade in our example, and the grade entity contains the student_ID and course_ID columns as foreign keys. Let us see how this could be implemented using pointers to whole tables. In a traditional relational implementation, to generate a class schedule for a student we would need to select the student row, join with the GRADE table, and finally join with the CLASS table, as follows:

SELECT
       student_full_name, 
       course_name, 
       course_date, 
       grade
 FROM
       STUDENT, GRADE, COURSE
 WHERE
       student_last_name = 'Burleeze'
       AND
       STUDENT.student_ID = GRADE.student_ID
       AND
       GRADE.course_ID = COURSE.course_ID;

CREATE TYPE
 student_list (student_full_name full_name,
 student_full_address   full_address,
 grade CHAR(1)); 

CREATE TYPE student_list_type AS TABLE OF student_list;

CREATE TABLE 
 COURSE (
  course_name           VARCHAR(20),
  dept_ID               NUMBER(4),
  credit_hrs            NUMBER(2),
  student_roster        student_list_type);

We see here that the student_roster column of the COURSE table contains a pointer to a table of TYPE student_list_type. While it appears that each distinct column value points to a whole table, the column actually points to a set of rows within the store table. The store table is common to all of the columns that contain this pointer structure. The store table also contains a special OID that points to the owner of the row in the parent table.