Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 10 Instructions

without comments

Lab #10: Oracle Assignment


The lab is designed to teach you how to work with Oracle’s object types, and teach you the concepts of generalization and specialization. Oracle creates object types that support collections with and without methods. Oracle object types without methods have a default constructor like tables, and they do not require the implementation of object bodies. Oracle object types with methods require you to implement object bodies, more or less, object types and bodies act like PL/SQL package specifications and bodies.

  • Learn how to create an object type with methods.
  • Learn how to implement an object body with methods.
  • Learn how to create subtypes.
  • Learn how to create overriding methods that implement generalized invocation, or polymorphism.
  • Learn how to store and use SQL to access polymorphic stored object types.

Business Scenario

Application programming solutions often require the ability to use object types and object subtypes. This lab explores Oracle’s implementation of objects in the database.

Oracle’s choice of implementing object types and subtypes was pioneering and expanded their product from a purely relational database management system (RDBMS) to a object-relational database management system (ORDBMS). There was one slight element that makes using methods of subtypes difficult in SQL. SQL relies on knowing all types before hand, which is formally Interface Definition Language (IDL).

The ability to create object types and subtypes lets you create a table with a single column that can hold an object type instance or any object subtype instances. This gives developers the ability to write complex data to a single source or column. This lets you create a column in a table with a column data type that’s polymorphic (able to assume different related forms). This is an ideal solution when you need to store logging information from different table structures, which is what you’ll do in this lab.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Students may want or need supplemental articles that let them review tips and techniques. The following is a function that checks for valid dates inside strings. You should run this type of check after you rule out a number and alphanumeric string.

There are two elements to this lab. One creates the base_t object type and a logger table. The logger table creates a logger_id column for the surrogate key column and a log_text column that holds a base_t object type. The other element of the lab creates two subtypes of the base_t object type. The subtypes extend the behavior of the base_t object type.

Lab Description

  1. [10 points] Create the base_t object type, a logger table, and logger_s sequence.

    The sections cover the detailed tasks of the lab:

  1. [15 points] Create the item_t and contact_t subtypes of the base_t object type. Then, test the column substitutability in the logger table.

    The sections cover the detailed tasks of the lab:

Written by michaelmclaughlin

November 14th, 2016 at 1:42 am

Posted in