1.2. Relational Database Concepts

At the heart of all the Oracle products discussed in the previous section is the concept of using a database to store, manipulate, retrieve, and secure important business data. The manner in which these three tasks are performed has varied throughout the history of computing. Some early database technologies used flat files or hierarchical file structures to store application data. Others used networks of connections between sets of data to store and locate information.

Oracle 10g does not use any of these techniques for storing or accessing data. Instead, all releases of Oracle's database products have used a relational model to store application data in the database. This relational model on which Oracle is built is based on the ground-breaking work of Dr. Edgar Codd, which was first published in 1970 in his paper "A Relational Model of Data for Large Shared Data Banks."

IBM Corporation was an early adopter of Dr. Codd's model and also helped to develop the computer language that is used to access all relational databases today—Structured Query Language (SQL). Using English-like commands, SQL users can easily interact with relational databases without having to write complex computer programs or needing to know where or how the data is physically stored on disk. Samples of SQL statements are used in examples throughout this book. In general, SQL commands are used to do the following:

• Display data stored in database tables using the SELECT command

• Add rows to tables using the INSERT command

• Remove rows from tables using the DELETE command

• Modify rows in tables using the UPDATE command

• Create, modify, or drop tables using the CREATE, ALTER, and DROP commands

• Grant or revoke user access to tables using the GRANT and REVOKE commands

• Control transactions using the COMMIT and ROLLBACK commands

Even though each of the previous commands is an SQL command, each type of SQL statement can be classified into one of four categories:

• Queries using the SELECT command.

• Statements using the CREATE, ALTER, or DROP command are classified as Data Definition Language (DDL) commands.

• Statements using the GRANT or REVOKE commands are classified as Data Control Language (DCL) commands.

• Statements using the INSERT, UPDATE, and DELETE commands are classified as Data Manipulation Language (DML) commands.

DML commands are used in transactions. A transaction begins with the first DML command that a user issues and ends when the user either makes their changes permanent by issuing a commit command or undoes their changes using the rollback command.

Most SQL statements, whether they are queries, DMLs, DDLs, or DCLs, are directed at data stored in one or more Oracle tables. The next section examines important Oracle table concepts in detail.

1.2.1. Rows, Columns, Tables, and Databases

At the heart of the relational model is the concept of a table. A table is composed of columns and rows. The intersection of a column and a row is called a field. The collection of tables that store business data are stored within the Oracle 10g database. Figure 1.1 shows an example of a table, a column, a row, and a field for a table called DEPT that stores department data.

The DEPT table in Figure 1.1 is composed of three columns (DEPTNO, DNAME, and LOC) and contains four rows. Each row contains all the relevant data for a single department. The field at the intersection of the DNAME column and the first row contains the value "Accounting". When a table is created, each column is assigned a name and a datatype. Many datatypes are available in Oracle 10g, but most simply designate whether a column is intended to store characters, numbers, or dates. You can use the following DDL statement to create the DEPT table shown in Figure 1.1.

SQL> create table DEPT
  2  (DEPTNO  number(2),
  3   DNAME   varchar2(14),
  4   LOC     varchar2(13));

Table created.

The DDL command creates a column called DEPTNO to store department numbers of as many as 2 digits, a column called DNAME to store department name data of as many as 14 characters, and a column called LOC to store department location data of as many as 13 characters. By specifying column datatypes in this manner, some basic data controls are automatically in place within the database. These controls prevent a user from storing incorrect data in a table. For example, attempting to insert a record that stores a word in a column that is set up to hold numeric values causes a SQL error. The following example shows an example of an INSERT statement that succeeds because all the data being inserted is of the correct datatype, and it shows another statement that fails because a character datatype was inserted into the numeric DEPTNO column:

SQL> insert into DEPT (DEPTNO, DNAME, LOC)
  2 values (50,'MANUFACTURING','MADISON'),

1 row created.

SQL> insert into DEPT (DEPTNO, DNAME, LOC)
  2 values ('SIX','SHIPPING','MILWAUKEE'),
values ('SIX','SHIPPING','MILWAUKEE')
        *
ERROR at line 2:
ORA-01722: invalid number

In addition to tables such as DEPT that store important business data, Oracle databases also contain system tables that store data about the database itself. Examples of the type of information in these system tables include the names of all the tables in the database, the column names and datatypes of those tables, the number of rows those tables contain, and security information about which users are allowed to access those tables. This "data about the database" is referred to as metadata. As a DBA, you will frequently use this metadata when performing your tasks.

The metadata tables, however, have rather cryptic names such as OBJ$, FILE$, X$KSMSP, and X$KWQSI with unusual column names such as DATAOBJ#, CRSCNWRP, KSMCHCOM, and KWQSINCO. To make it easier to use SQL to examine the contents of metadata tables, Oracle builds views on the tables. A view is similar to a table in that it is made up of columns and rows. However, a view is only a logical structure that contains no data of its own. Instead, a view is like a window that can be used to look at the contents of another table or tables. Views greatly simplify access to the metadata because the names of the views and the columns in them are much more intuitive than the metadata tables on which they are based. An Oracle 10g database contains two types of metadata views:

• Data dictionary views

• Dynamic performance views

Examples of both data dictionary and dynamic performance views are described in the next section.

1.2.1.1. Data Dictionary Views

Depending on which features are installed and configured, an Oracle 10g database can contain more than 1,300 data dictionary views. Data dictionary views have names that begin with DBA_, ALL_, and USER_.

The difference between the DBA_, ALL_, and USER_ views can be illustrated using the DBA_ TABLES data dictionary view as an example. The DBA_TABLES view shows information on all the tables in the database. The corresponding ALL_TABLES view, despite its name, shows only the tables that a particular database user owns or has access to. For example, if you were logged in to the database as a user named SCOTT, the ALL_TABLES view would show all the tables owned by the user SCOTT and the tables to which SCOTT has been granted access by other users. The USER_TABLES view shows only those objects owned by a user. If the user SCOTT were to examine the USER_TABLES view, only those tables he owns would be displayed. Figure 1.2 shows a graphical representation of the relationship between the DBA_, ALL_, and USER_ views.

Because the DBA_ views provide the broadest metadata information, they are generally the data dictionary views used by DBAs. Table 1.2 provides more examples of DBA_ data dictionary views.

Table 1.2. Examples of Data Dictionary Views

Dictionary View	Description
DBA_TABLES	Shows the names and physical storage information about all the tables in the database.
DBA_USERS	Shows information about all the users in the database.
DBA_VIEWS	Shows information about all the views in the database.
DBA_TAB_COLUMNS	Shows all the names and datatypes of the table columns in the database.

1.2.1.2. Dynamic Performance Views

Depending on which features are installed and configured, an Oracle 10g database can contain approximately 350 dynamic performance views. Most of these views have names that begin with V$. Table 1.3 describes a few of these dynamic performance views.

Table 1.3. Examples of Dynamic Performance Views

Dynamic Performance View	Description
V$DATABASE	Contains information about the database itself, such as the database name and when the database was created.
V$VERSION	Shows which software version the database is using.
V$OPTION	Displays which optional components are installed in the database.
V$SQL	Displays information about the SQL statements that database users have been issuing.

Although the contents of the DBA_ and V$ metadata views are similar, there are some important differences between the two types. Table 1.4 compares these two types.

Table 1.4. A Comparison of Data Dictionary and Dynamic Performance Views

Dictionary Views	Dynamic Performance Views
The DBA_ views usually have plural names (for example, DBA_DATA_FILES).	The names of the V$ views are generally singular (for example, V$DATAFILE).
The DBA_ views are available only when the database is open and running.	Some V$ views are available even when the database is not fully open and running.
The data contained in the DBA_ views is generally uppercase.	The data contained in the V$ views is usually lowercase.
The data contained in the DBA_ views is static and is not cleared when the database is shut down.	The V$ views contain dynamic statistical data that is lost each time the database is shut down.

Data dictionary views are useful for examining the relationships between tables and the rules defined for storing data in tables. These restrictions and relationships are examined in the next section.

1.2.2. Relationships and Constraints

Real-world Oracle databases are made up of hundreds or thousands of tables. To use these tables to more easily store and retrieve data, you can define rules about how the tables are related and how data should be stored in each table. These rules are referred to as constraints. A constraint allows the database designer to enforce business rules about the data stored in the database's tables and the relationships between tables. Table 1.5 describes the five types of constraints in an Oracle database.

Table 1.5. Types of Table Constraints

Constraint Type	Description
Not Null	A value must be supplied for this column, but values do not have to be unique.
Unique Key	Every value in this column must be unique, but null values are allowed.
Primary Key	Every value in the column must be unique and cannot be null.
Foreign Key	Every value in the column must match a value in another column in this table or some other table; otherwise, the value is null.
Check	The value entered in the table must match one of the specified values for this column.

For example, suppose your database contains a table called EMP that holds employee information. Table 1.6 shows the structure of the EMP table.

Table 1.6. The Structure of an EMP Table

Column Name	Column Description	Column Datatype
EMPNO	Employee ID number	Number
ENAME	Employee name	Character
JOB	Job title	Character
MGR	Manager's employee ID	Number
HIREDATE	Date employee was hired	Date
SAL	Employee's monthly salary	Number
COMM	Employee's commission amount	Number
DEPTNO	Employee's department number	Number

If the business has a rule that every employee must have an employee ID and that no two employee IDs can be the same, placing a primary key constraint on the EMPNO column of the EMP table enforces this rule. Any records inserted without an employee number, or with the same employee number as an existing employee, are rejected. Therefore, the EMPNO column is referred to as the primary key of the EMP table because the EMPNO value uniquely identifies each record in the EMP table.

A business rule might require that each employee be assigned to a valid department. To enforce this rule, you can define a foreign key constraint between the EMP and DEPT tables so that the DEPTNO value entered for every employee in the EMP table must have a matching DEPTNO in the DEPT table. This relationship is shown graphically in Figure 1.3.

In this example, the DEPTNO column of the EMP table is referred to as a foreign key because it has a relationship to the DEPTNO column in another (that is, foreign) table called DEPT. Designing database tables in this manner, so that the values in one table have a relationship to the values in another table, is referred to as referential integrity (RI). Referential integrity is generally enforced through the use of primary key and foreign key table constraints.

Sample DDL commands to create the DEPT and EMP tables with the primary and foreign key constraints that we've described are shown here:

SQL> alter table DEPT
  2  add constraint DEPT_PK
  3  primary key (DEPTNO);
Table altered.

SQL> create table EMP
  2  (empno    number(4) constraint EMP_PK primary key,
  3  ename    varchar2(10),
  4  job      varchar2(9),
  5  mgr      number(4),
  6  hiredate date,
  7  sal      number(7,2),
  8  comm     number(7,2),
  9  deptno   number(2) constraint EMP_PK_DEPTNO references DEPT(deptno)
 10  );

Table created.

Notice that because the DEPT table did not have a primary key defined when we originally created it, the ALTER command is used to create one. Once the relationship between the two tables is defined, the database enforces the relationship for every DML statement performed on those tables. The following example shows an INSERT into the EMP table that fails because the DEPT table has no corresponding department record; it also shows how the same INSERT succeeds after the proper foreign key record is present in the DEPT table:

SQL> insert into EMP (empno, ename, deptno)
  2  values (84,'JOHNSON',99);
insert into EMP (empno, ename, deptno)
*
ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.EMP_PK_DEPTNO) violated - parent key
     not found

SQL> insert into DEPT (deptno, dname, loc)
  2  values (99,'RESEARCH','FREEPORT'),

1 row created.

SQL> insert into EMP (empno, ename, deptno)
  2  values (84,'JOHNSON',99);

1 row created.

Referential integrity not only enforces the relationship rules while rows are added to a table, but also enforces those rules when rows are being deleted or updated as well. For example, if a user attempts to delete a department from the DEPT table, that department must not have any employees assigned to it; if it does, the primary key/foreign key relationship will not allow the delete. If the employees in that department are deleted first, the DELETE statement on the DEPT table succeeds. The following example demonstrates this behavior when a DELETE statement is issued on tables with referential constraints:

SQL> delete from DEPT
  2  where deptno = 99;
delete from DEPT
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_PK_DEPTNO) violated - child
     record found


SQL> delete from EMP
  2  where deptno = 99;

1 row deleted.

SQL> delete from DEPT
  2  where deptno = 99;

1 row deleted.

Constraints have a similar impact on UPDATE statements. If a department's number is updated, the database determines if there are employees in that department before allowing the update. If there are employees in that department, the UPDATE fails, because changing the department number will "orphan" these employees, leaving them without a valid department— which violates the business rule that the constraint was designed to enforce. The following example shows what happens when an UPDATE violates the RI rules in the database:

SQL> update DEPT
  2  set deptno = 1
  3  where deptno = 10;
update DEPT
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_PK_DEPTNO) violated - child
     record found

Constraints also prevent a user from removing a table that has a defined relationship to another table. The following example shows how RI impacts an attempt to use the SQL DROP command on the DEPT table:

SQL> drop table DEPT;
drop table DEPT
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

When two tables share a common column, such as when referential integrity constraints are defined on the columns between two tables, you can join those tables in a query and return rows from both tables simultaneously. The relationship between the two tables is defined in the WHERE clause of the query shown here.

SQL> select dname, ename
  2  from DEPT, EMP
  3  where DEPT.deptno = EMP.deptno;

DNAME         ENAME
------------- ---------------
ACCOUNTING    CLARK
ACCOUNTING     KING
ACCOUNTING    MILLER
RESEARCH      SMITH
RESEARCH      ADAMS
RESEARCH       FORD
RESEARCH      SCOTT
RESEARCH      JONES
SALES         ALLEN
SALES         BLAKE

SALES          MARTIN
SALES         JAMES
SALES          TURNER
SALES         WARD

14 rows selected.

This query joins the two tables on the common DEPTNO column. Because the DEPTNO column has the same name in both tables, each table's name is included in JOIN condition of the WHERE clause to explicitly tell Oracle how to perform the JOIN.

1.2.3. Other Segment Types

The previous section described a variety of SQL commands that can be used against tables in the database. However, tables are just one type of segment in an Oracle 10g database. A segment is defined as any entity that consumes physical storage space within the database. Some of the more common segment types are described in Table 1.7.

Table 1.7. Oracle Segment Types

Segment Type	Description
Table	Stores data in column and row structure.
Index	Improves the access to table data.
Rollback	Special segment used to maintain read consistency during user transactions and perform transaction recovery. Rollback segments are described in Chapter 8, "Managing Consistency and Concurrency."
Partition	Divides a table into smaller, more manageable pieces for performance purposes.

Each Oracle segment is made up of contiguous chunks of storage space in the database called extents. Every segment must have at least one extent, but can have as many as 2 billion extents.

Each extent is itself made up of a collection of smaller chunks of space called Oracle database blocks. The minimum size of an extent is five database blocks. The default size of these database blocks is set at database creation, but Oracle 10g databases can use multiple block sizes within one database. The common database block sizes are 2KB, 4KB, 8KB, and 16KB.

Each database block is in turn composed of one or more operating system blocks. The size of an operating system block depends on the operating system, but most are 512 bytes to 2KB in size. Figure 1.4 summarizes the relationship between the segments, extents, database blocks, and operating system blocks.

Figure 1.4 illustrates how the DEPT table is made up of four extents. Each of these extents is made up of eight database blocks, and each database block is made up of four operating system blocks.

Once a segment such as a table is created, SQL is used to interact with it. The ways in which SQL accesses tables are described in the following section.

1.2.4. Interacting with Segments

The most common way to interact with an Oracle database is through the use of SQL. The SQL statements might be typed within an Oracle query tool, dynamically generated using a webbased development or management tool, or entered using a programming language such as C++ or COBOL. You can also use Oracle's own procedural language to extend the functionality of SQL within the database. Each of these methods of interacting with the database is explained in the following sections.

1.2.4.1. Structured Query Language

In their simplest form, SQL statements can be constructed using either of Oracle's commandline SQL tools: SQL*Plus or iSQL*Plus. Both SQL*Plus and iSQL*Plus allow you to type SQL commands and pass them directly to the database for processing. As described in the previous section, there are four types of SQL commands:

• Queries created using SELECT statements

• DML commands created using INSERT, UPDATE, and DELETE statements

• DDL commands created using CREATE, ALTER, or DROP commands

• DCL commands created using GRANT and REVOKE commands

Short examples of each of these types of SQL statements will appear throughout the remainder of this chapter. However, most of the examples are of SELECT statements. SQL SELECT statements can be composed of many parts:

• The SELECT list, in which each of the columns you want to include in your output is specified. The SELECT clause is required in all queries. An * can be used in the SELECT clause if every column in a table is to be included in the query output.

• The FROM clause in which the name of the table or tables being queried is specified. The FROM clause is required in all queries.

• The WHERE clause in which the output of the query is further restricted by placing conditions on the rows that will be returned. The WHERE clause is optional. If it is not used, the query returns all rows from the table. When a query joins two or more tables, you can use the WHERE clause to define the JOIN condition.

• The GROUP BY clause, which allows you to group related rows of data to summarize their results.

• The HAVING clause, which, like the WHERE clause, is used to reduce the output of the query by limiting which rows are returned.

• The ORDER BY clause that sorts the query output in a specified order.

The following example shows an example of the parts of a SQL statement.

SQL> select dname, SUM(sal)
  2 from DEPT, EMP
  3 where DEPT.deptno=EMP.deptno
  4 group by dname
  5 having SUM(sal) > 10000
  6 order by SUM(sal);

DNAME       SUM(SAL)
----------- --------
RESEARCH       10875
SALES          10100

The query examples used throughout this book use each of these clauses. The following sections show how you can use SQL*Plus and iSQL*Plus to issue SQL statements.

1.2.4.1.1. Using SQL*Plus to Access a Database

To access an Oracle database using SQL*Plus, you must have the following:

• The SQL*Plus client software on your local computer or accessible on the host server via a remote logon or Telnet session

• A valid database user name and password

• The Oracle Net connection string of the database to which you will connect

Figure 1.5 shows a user connecting with the Windows version of the SQL*Plus client to a database called PROD.

Once connected to the database via SQL*Plus, you can issue SQL statements from the SQL prompt. Figure 1.6 shows a query that returns all the columns and rows from the DEPT table.

Notice that the SELECT statement shown in Figure 1.6 ends with a semicolon (;). All SQL commands in SQL*Plus end with either a semicolon or a forward slash (/).

Another useful command that you can use in SQL*Plus is DESCRIBE. The DESCRIBE command is not a standard SQL command, but an Oracle-specific SQL*Plus command. It displays the logical structure of a table. Figure 1.7 shows the DESCRIBE command being used on the EMP table.

1.2.4.1.2. Using iSQL*Plus to Access a Database

To access an Oracle database using the browser-based iSQL*Plus tool, you must have the following:

• A web browser

• The URL address to the host server running the iSQL*Plus website

• A valid database user name and password

• The Oracle Net connection string of the database to which you will connect

Figure 1.8 shows a user connecting the iSQL*Plus client to a database called PROD. The iSQL*Plus interface is composed of two windows. You enter SQL statements in the top window, click the Execute button, and the output from those statements is displayed in the bottom window. Just as in SQL*Plus, each iSQL*Plus statement can end with a semicolon or a forward slash (/). However, iSQL*Plus also allows you to execute SQL commands without specifying the semicolon or forward slash. Figure 1.9 shows a SELECT statement that displays two of the columns and all the rows from the EMP table.

1.2.4.2. Web-Based Management and Development Tools

In addition to iSQL*Plus, Oracle provides several other web-based tools for accessing and manipulating data in databases. Most of these tools do not require that the user construct their own SQL statements the way SQL*Plus or iSQL*Plus do. Instead, these tools either dynamically generate SQL code or use SQL code stored in the database to interact with the database. One example of this type of query tool is Oracle Discoverer. Discoverer is an end-user query tool that allows users to run predefined and ad hoc reports from their web browser simply by clicking the tables that they want to query. Oracle Forms and Reports also allows users to access databases using web-based forms and reports.

Another tool, Enterprise Manager (EM) Database Control, is Oracle's web-based database administration tool. EM Database Control dynamically produces SQL commands that are sent to the database based on the navigational choices that are made within EM Database Control. A portion of the Administration page of EM Database Control is shown in Figure 1.10.

Most EM Database Control pages have a Show SQL button. Clicking this button displays the full text of any SQL statements that have been generated as a result of the user's actions within EM Database Control. In this manner, you can use the Show SQL button to review the SQL statements that EM sends to the database when the user clicks the OK button for the current operation.

In addition to EM Database Control, you can install EM client software on your computer so that you can manage database tasks without using EM Database Control, if needed.

1.2.4.3. PL/SQL: Procedural Database Language

SQL is a powerful language for interacting with databases, but it does have some limitations. For example, SQL does not have very good mechanisms for condition testing, which would allow a SQL statement to execute if a given condition is true, but not execute if the condition is false. SQL also lacks looping capabilities, the ability to perform a specific SQL action for a specified number of times before stopping. Finally, SQL does not offer any exception-handling capabilities; all errors raised by SQL statements are returned directly to the user.

Oracle Procedural Language for SQL (PL/SQL) is the solution for all these limitations. PL/SQL is a powerful extension to SQL that not only adds condition testing, looping, and exception handling, but also allows developers to write application-specific functions, procedures, packages, and triggers. Table 1.8 describes each of these types of PL/SQL objects.

Table 1.8. Types of PL/SQL Objects

PL/SQL Object	Description
Anonymous Block	A block of PL/SQL code that is not stored in the database, but instead is embedded in a form, web page, or SQL script.
Procedure	A block of PL/SQL code that is stored in the database and performs a specific action.
Function	A block of PL/SQL code that is stored in the database and returns a value when called in a SQL statement.
Package	A collection of related procedures and/or functions that perform related functions.
Trigger	A block of PL/SQL code that runs whenever an INSERT, UPDATE, or DELETE activity occurs on a table. Can also be defined to run when certain database events occur.

1.2.4.4. Accessing the Database Using Java

Since its introduction in 1995, Java has emerged as a dominant development environment for web-based applications. The primary reason for Java's popularity is its operating system independence. Java programs can be developed on one operating system and then deployed on some other operating system without modification. This is made possible by running the Java programs in an operating system–specific engine called the Java Virtual Machine (JVM). In this way, the only part of the Java architecture that is operating system–specific is the JVM—not the programs themselves.

By incorporating a JVM directly in the database, Oracle 10g can store and execute compiled Java code natively. This not only greatly improves the performance of Java-based applications, but also allows developers to incorporate Java code directly into PL/SQL procedures, functions, and packages.

1.2.4.5. Using Oracle Programming Interfaces

In addition to SQL, web-based tools, PL/SQL, and Java, Oracle also provides the ability to integrate SQL commands and database connectivity into traditional programming languages such as C, C++, and COBOL. This integration is achieved by using the Oracle precompilers and the Oracle Call Interface (OCI).

Oracle precompilers allow programmers to incorporate calls to the database directly into their program code. Precompilers are available for third-generation programming languages such as C and COBOL. The Oracle C++ Call Interface (OCCI) is used with C++ to provide full database interaction with that development environment. The OCCI provides substantial programmatic support for database security and password management, access to Oracle datatypes and objectrelational features, management of distributed database transactions, and globalization features.