RelationalDBDesign RelationalDBDesign 


Data Structures   «Prev 

Oracle offers a variety of

Oracle offers a variety of data structures to help create robust database systems. Oracle supports the full use of
  1. (BLOB) binary large objects ,
  2. nested tables,
  3. non first-normal-form table structures (VARRAY tables), and
  4. object-oriented table structures.
In addition, flat files are treated as if they were tables within the Oracle database.
It is a challenge to many Oracle professionals to know when to use these Oracle data model extensions. Here is a brief review of advanced Oracle topics and how they are used to design high-performance Oracle databases.
The ability of Oracle to support object types (sometimes called user-defined datatypes) has profound implications for Oracle design and implementation. User-defined datatypes will enable the database designer to:
  1. Create aggregate datatypes: Aggregate datatypes are datatypes that contain other datatypes. For example, you could create a type called FULL_ADDRESS that contains all of the subfields necessary for a complete mailing address.
  2. Nest user-defined datatypes: Datatypes can be placed within other user-defined datatypes to create data structures that can be easily reused within Oracle tables and PL/SQL.
    For example, you could create a datatype called CUSTOMER that contains a datatype called CUSTOMER_DEMOGRAPHICS, which in turn contains a datatype called JOB_HISTORY, and so on.
One of the user-defined data types in the Oracle object-relational model is a "pointer" data type. A pointer is a unique reference to a row in a relational table. The ability to store these row IDs inside a relational table extends the traditional relational model and enhances the ability of an object-relational database to establish relationships between tables. 

CREATE TYPE
jobhist
AS object(
employer_name NUMBER,
employer_phone VARCHAR(20),
sal NUMBER);
Create a data type called jobhist that contains the previous employers name and phone number, and the salary of the employee.


CREATE TYPE
  jobhist_arr
AS
  VARRAY(10) OF jobhist;
Next we create a type called jobhist_arr. This creates a repeating group that can repeat job histories up to 10 times.

CREATE TABLE
employee
(
  empno NUMBER,
  dname VARCHAR2(20),
  location VARCHAR(20),
  emp jobhist_arr
);
We create our employee table, specifying a column called emp. As we see, the emp colum is of the datatype jobhist_arr.