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.
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. |
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.
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.
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.
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