It has long been argued that relational databases should be able to contain repeating data items. Until recently,
C. J. Date and E. F. Codd ,
the creators of the relational database model, expressed concern about the viability of non-first normal form tables.
Relational Database Model
Here is the formal definition of a relational database management system.
While working at IBM I was involved in technical planning and design for the IBM products 1) SQL/DS and 2) DB2.
In addition, I was also involved with Edgar F. Codd's relational model for database management.
Database Management Systems
The methodology presented on this site for relational (DBMSs) Database Management Systems has been tried and tested over the years in both industrial and academic environments. It consists of three main phases:
database design. The first phase starts with the production of a conceptual data model that is independent of all physical considerations.
This model is then refined in the second phase into a logical data model by removing constructs that cannot be represented in relational systems. In the third phase, the logical data model is translated into a physical design for the target DBMS. The physical design phase considers the storage structures and access methods required for efficient and secure access to the database on secondary storage.The methodology in each phase is presented as a series of steps. For the inexperienced designer, it is expected that the steps will be followed in the order described, and guidelines are provided throughout to help with this process.
For the experienced designer, the methodology can be less prescriptive, acting more as a framework or checklist.
E. F. Codd
Dr. E. F. Codd, the inventor of the relational database model, developed a list of criteria that a relational database must meet. This list is often referred to as Codd's Rules. Chris Date's more basic definition and Codd's twelve-rule test for relational systems form a general definition described below.
To be considered fully relational, a database management system must:
Maintain a logical definition of data separate from its physical storage characteristics
Represent all information in the database in table form
Use one, high-level language for structure, query, and modification of database information to handle communications with the database (most use SQL)
Support the main relational operations, including selection of rows and columns and the join operation, and set operations, union, intersection, division, and difference
Support views to allow the user to create other ways of looking at data using virtual tables
Provide a means of differentiating between null or unknown values and a blank or zero
Support mechanisms for managing integrity, permissions, transactions, and data recovery
Constructing a Table with repeating Values
Prior to Oracle, there was no way to construct a table with repeating values.
The Oracle designer had to create a subordinate table and use a JOIN to get the information.
Oracle has recognized that there are cases where it is better to allow repeating items in a table.
The series of images below describes how the use of repeating groups or VARRAYS allows us to dramatically improve the performance of Oracle queries because subordinate tables are no longer required in order to represent a one-to-many relationship.
Oracle offers a variety of data structures to help create robust database systems. Oracle supports the full use of
(BLOB) binary large objects,
non first-normal-form table structures (VARRAY tables), and
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:
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.
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.
This technique is very important to Oracle performance. Prior to Oracle, repeating groups had to be isolated into a JOBHIST table,
and an SQL JOIN operation was required to access the job history for an employee.
Advantages of using tables with VARRAYs
Now that Oracle provides the ability to allow repeating groups in a table, we have:
Condensed a one-to-many data relationship into a single table, and can retrieve employee information and job history in a singleI/O
Avoided the requirement to create a separate table and to join multiple tables to get repeating values
The next lesson wraps up this module.
Create varray - Exercise
Before you move on to the next lesson, click the Exercise link below to test your knowledge of VARRAYs.
Create varray - Exercise