0029-0032

Previous Table of Contents Next

Page 29

Figure 2.2.
Foreign keys that
reference a primary key
in another table as well
as a primary key in the
same table.

You typically design a relational database using the rules of normalization that dictate which attributes belong in which relations. There are five levels (or forms) of normalization to which a data model can comply . Of the five, most database designs minimally conform to the third normal form. This form serves to alleviate redundancy in the data model, requiring each atomic data element to appear once in the data model and be dependent on one and only one primary key. Employing a normalized data model protects against insert, update, and delete anomalies that can arise as a result of incorrectly defined relations.

Relational Algebra

The relational model defines the operations that are permitted on a relation or group of relations. There are unary and binary relational operators, each of which result in another relation. You should find these operations somewhat intuitive and very similar to those used with set operations. Table 2.1 describes the seven operators used to manipulate relational structures. Binary operator types indicate that the operation uses two relations as operands; unary operators require a single relation as an operand.

Table 2.1. Algebra operations of the relational model.

Operation Type Resulting relation
Union Binary Rows from the two relations are
combined, eliminating duplicate rows.
Intersection Binary Rows common to two relations.
 continues 

Page 30

Table 2.1. continued

Operation Type Resulting relation
Difference Binary Rows that exist in the first relation but not in
the second.
Projection Unary Rows that contain some of the columns from
the source relation.
Selection Unary Rows from the source relation that meet
query criteria.
Product Binary Concatenation of every row in one relation
with every row in another.
Join Binary Concatenation of rows from one relation
and related rows from another.

NOTE
The source relations used by UNION, INTERSECTION, and DIFFERENCE must have attribute lists that match in number and data type.

RDBMS Components

Two important pieces of an RDBMS architecture are the kernel, which is the software, and the data dictionary, which consists of the system-level data structures used by the kernel to manage the database.

The RDBMS Kernel

You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data access; its primary functions are storing, retrieving, and securing data. Like any operating system, Oracle7 manages and controls access to a given set of resources for concurrent database users. The subsystems of an RDBMS closely resemble those of a host operating system and tightly integrate with the host's services for machine-level access to resources such as memory, CPU, devices, and file structures. An RDBMS such as Oracle7 maintains its own list of authorized users and their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage; dispatches and schedules user requests ; and manages space usage within its tablespace structures. Figure 2.3 illustrates the primary subsystems of the Oracle7 kernel that manage the database.

Page 31

Figure 2.3.
An RDBMS and its
multiple subsystems.


The Data Dictionary

A fundamental difference between an RDBMS and other database and file systems is in the way that they access data. A RDBMS enables you to reference physical data in a more abstract, logical fashion, providing ease and flexibility in developing application code. Programs using an RDBMS access data through a database engine, creating independence from the actual data source and insulating applications from the details of the underlying physical data structures. Rather than accessing a customer number as bytes 1 through 10 of the customer record, an application simply refers to the attribute Customer Number. The RDBMS takes care of where the field is stored in the database. Consider the amount of programming modifications that you must make if you change a record structure in a file system-based application. For example, if you move the customer number from bytes 1 through 10 to bytes 11 through 20 to accommodate an additional field, all the programs that use the customer number would require modification. However, using an RDBMS, the application code would continue to reference the attribute by name rather than by record position, alleviating the need for any modifications.

This data independence is possible because of the RDBMS's data dictionary. The data dictionary stores meta-data (data about data) for all the objects that reside in the database. Oracle7's data dictionary is a set of tables and database objects that is stored in a special area of the database and maintained exclusively by the Oracle7 kernel. As shown in Figure 2.4, requests to read or update the database are processed by the Oracle7 kernel using the information in the data dictionary. The information in the data dictionary validates the existence of the objects, provides access to them, and maps the actual physical storage location.

Not only does the RDBMS take care of locating data, it determines an optimal access path to store or retrieve the data. Oracle7 uses sophisticated algorithms that enable you to retrieve information either for the best response for the first set of rows or for total throughput of all rows to be retrieved.

Page 32

Figure 2.4.
Access to application,
data through the
Oracle7 kernel and the
data dictionary.


Nonprocedural Data Access (SQL)

An RDBMS differentiates itself with its capability to process a set of data; other file systems and database models process data in a record-by-record fashion. You communicate with an RDBMS using Structured Query Language (SQL, pronounced "sequel"). SQL is a nonprocedural language that is designed specifically for data access operations on normalized relational database structures. The primary difference between SQL and other conventional programming languages is that SQL statements specify what data operations should be performed rather than how to perform them. For example, consider a procedure to give a salary increase to a particular department for each employee who had not received a raise within the past six months. The code segments in Figure 2.5 illustrate the solution to the problem using both procedural and nonprocedural methods .

Figure 2.5.
SQL programming
versus traditional
procedural program-
ming methods.

Although the example in Figure 2.5 illustrates a simplistic scenario, consider a more complex application and the amount of programming that is alleviated by using SQL for data access. By

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net