Back Up Next

Chapter 3 *

Creating the Oracle Database *

Overview of Data Modeling and Database Design *

Stages of System Development *

Needs Assessment *

Database Design *

Application Development *

Performance Tuning *

Database Security *

Enhancements *

Exercises *

Basic Types of Data Relationships *

Exercises *

Relational Database Components *

Exercises *

Reading an Entity-Relationship Diagram *

From ERD to LDM *

Role of Ordinality *

Exercises *

Creating the Tables of an Oracle Database *

Creating a Table with Integrity Constraints *

Exercises *

Using Table Naming Conventions *

Keep Names Short and Descriptive *

Names for Child Tables Relate to Their Parent *

Foreign-key Columns Should Have the Same Name in Both Tables *

Names of Associated Objects Should Relate to the Table *

Avoid Quotes, Keywords, and Nonalphanumeric Characters *

Exercises *

Datatypes and Column Definitions *

Exercises *

Indexes Created by Constraints *

Exercises *

Creating One Table with Data from Another *

Exercises *

The Oracle Data Dictionary *

Available Dictionary Views *

Exercises *

Querying the Data Dictionary *

Exercises *

Manipulating Oracle Data *

Inserting New Rows into a Table *

Exercises *

Making Changes to Existing Row Data *

Exercises *

Deleting Data from the Oracle Database *

Exercises *

The Importance of Transaction Controls *

SET TRANSACTION *

COMMIT *

ROLLBACK *

SAVEPOINT *

Locks *

Exercises *

Chapter Summary *

Two-Minute Drill *

 

Chapter 3

Creating the Oracle Database

In this chapter, you will understand and demonstrate knowledge in the following areas:

Overview of data modeling and database design
Creating the tables of an Oracle database
The Oracle data dictionary
Manipulating Oracle data

At this point, you should feel comfortable with the idea of database usage via data selection. Now, in order to move forward on the road to becoming an Oracle DBA, you must immerse in the subject of Oracle database creation. This chapter will do just that. The topics covered in this chapter include data modeling, creating tables, the data dictionary, and data manipulation beyond select statements. With mastery of these topics, the user of an Oracle system moves more into the world of application development. Typically, it is the application developer who creates database objects and determines how users will access those objects in production environments. The DBA is then the person who is responsible for migrating developed objects into production and then managing the needs of production systems. This chapter will lay the foundation for discussion of Oracle database object creation and other advanced topics, so it is important to review this material carefully. The OCP Exam 1 will consist of test questions in this subject area worth 22 percent of the final score. With these thoughts in mind, move on now to the topic at hand.

Overview of Data Modeling and Database Design

In this section, you will cover the following topics related to data modeling:

The stages of system development
The basic types of data relationships
The relational database components
Reading an entity relationship diagram

Computer programs are the most animate of inanimate objects. Like the people who use, develop, and maintain them, software applications are dynamic creatures that are subject to the same constraints and realities as the very realities they try to model. Software applications are also subject to economic constraints, as any analyst who has spent months planning a project only to have the project’s funds pulled at the last minute will attest. In so attempting to model reality, software applications become reality.

Stages of System Development

The first part of the software development life cycle is generally the one that most people pay attention to. This period of development is followed by a production phase, which may or may not involve the creation of enhancements. As time goes on, the users and developers of the project attempt to incorporate features into the production system. After quite a long time, usually, advances in the industry or the emergence of system requirements that the original technology used for the system will cause the system’s use to wane, until finally the data from the system will be archived and the system itself retired. The steps involved in the software development lifecycle are as follows:

Needs assessment
Database design
Application development
Performance tuning
Enhancements

Needs Assessment

A database system begins as an idea in someone’s head. At this early stage in the game, a database application’s possibilities can seem endless—however, this stage is as wrought with danger as other stages in the model. Many questions should be answered by the end of this planning stage. The first question that can be asked about an application is—will this application support large-volume data entry, or is the fundamental point of this application to make data viewable to users? In many cases, the answer is both. By the end of needs assessment, the designer of an application should have a clear idea about the following questions:

Who will use the application?
What use will the application fill in the organization?
How do people plan on using the application?

Recent successes involving user-facilitated meetings show that the success of a project can often be improved with the early and frequent involvement of users on the project. Once the users’ needs have been assessed, there is an activity that takes place allowing the developers to determine what data and tools are available for use. In this phase of software development, the developers of a software application must assess many things, such as process flow for data within the system and the method by which to present data to the user both on the screen and in reports. Generally, a software application involving a database involves three parts, all of which should be planned before creating them. The three components of a database application generally consist of user interface, the database, and reports. For our purposes, we focus on database design.

Database Design

This activity in creating a database application lays the groundwork for success in supporting current and future needs of the application. To design a database requires two steps. The two steps of designing a database are

Creating an entity-relationship diagram
Translating an entity-relationship diagram into a logical data model

Creating an entity-relationship diagram and translating it into a logical data model is an involved process. The steps to execute this process, however, are important to ensure correct design of the tables that will support both the user interface and the reports. So, even though the users will interface with the database in a controlled manner via the application, it is still important for the success of the application to have a strong database design. More on this process will appear later in the discussion.

Application Development

Once the users’ needs are assessed and the database design in place, the building of the application logic can begin. Some components of the application that may be placed within the database include integrity constraints and triggers, stored procedures and/or packages, and SQL tuning features such as the shared pool and the SQL statement optimizer. Application development is often a task that involves stepwise refinement. As needs arise, or as hidden intricacies of a current process are uncovered, the application software that models business rules will undoubtedly grow complex. PL/SQL, the programming language of Oracle database packages, supports many constructs that allow for modularization and abstract datatypes, as well as other useful programming constructs that will simplify the logic used to represent complex business rules.

Performance Tuning

No application is harder to use than a slow one. As will be discussed in Unit IV, covering performance tuning and optimization, the source of most performance issues in applications using Oracle databases is the application code itself. The application developers should, wherever possible, explore alternative methods for providing the same data to the user interface or reports in order to find the method that performs best. This step may involve development of alternative blocks of code that pull the same data from the database and executing benchmark tests to compare performance. This step may also involve the maintenance of two different databases, or at the very least, the need to maintain and use different database configurations to determine which methods work best. A fuller discussion of what performance tuning for an application entails appears in Unit IV.

Database Security

The guarded usage of the database application created will ensure that its use is appropriate. Database security is an important factor in any database, allowing the developers and managers for the database system to handle large user populations, if necessary, and to limit database access to those users that require it. One key activity that should occur early on in the development of an application is the determining of levels of data access that will be afforded to each user or type of user in the system. At this early stage of the project, users should be divided into rough categories for the purpose of determining what data they need access to in order to perform their tasks. Furthermore, once general access and usage levels for various users are established, there are features within the Oracle database that allow the developer or the DBA to limit users to only their access level or to restrict their usage of the database to only what they need. Some key terms to know here are privileges and roles for managing user access, and resource profiles to manage system hardware usage. A more complete discussion of these features appears in Unit II.

Enhancements

Enhancements are often as important as the actual application in the minds of the users, because they represent an evolution of the business process that must be modeled by the application supporting that business process. However, in some ways developing enhancements is often riskier than developing the application itself. Some of the advantages of the initial application development, such as reduced production burden on the developers of the application, a structured project plan, funding, and management attention, are lost once the application sees its first few months of successful production life. When enhancements are requested, the developers often have to do double duty—they are both the enhancement developer who has to rework existing code and the analyst that has to handle the production issues of the application as they arise. However, these obstacles represent as much of an opportunity for success as they do for failure. Strong project management in these situations generally helps the enhancement development effort to succeed.

Exercises

  1. What are the stages of the software development life cycle?
  2. What important questions should be answered before the application is developed?

Basic Types of Data Relationships

The focus of this discussion is to present the areas of data modeling and database design. In order to model data, there must be relationships between the various components that make up a database design. These components are stored as data, while the relationships between data can be defined explicitly via the use of integrity constraints and/or database triggers that model business rules, or implicitly by the data manipulation statements that select data for viewing or populate the database with new data. The following list of data relationships will be discussed in this section:

Primary keys
Functional dependency
Foreign keys

One type of data relationship starts in the tables that comprise the Oracle database. So far, we have seen many tables containing data. One common element in all the tables seen is that they contain multiple columns that "hang" off of one main column, called a primary key. This primary key is the column that determines the uniqueness of every row in the database. In the primary key, there can be no duplicate value for any row in the entire table. Each column that is not part of the primary key is considered to be "functionally dependent" on the primary key. This term simply means that the dependent column stores data that relates directly to or modifies directly each individual row.

One other relationship to discuss in this section is the idea of a foreign key. This relationship is often referred to as a parent/child relationship because of where the data must appear in each table to create the foreign-key relationship. In the "child" table, the data can appear either as part of the primary key or as a functionally dependent column. However, in the "parent" table, the referenced column must appear in the primary key.

Exercises

  1. What are three types of data relationships?
  2. What is functional dependency?
  3. What is required of two tables in order for the tables to be related?

Relational Database Components

A relational database consists of many components, some of which already have been covered. These components include objects to store data, objects to aid in accessing data quickly, and objects to manage user access to data. Additionally, there are objects in the database that contain the code that is used to manipulate and change data, produce reports of data, and otherwise use data to produce the desired result. Some of the objects that are part of the relational database produced by Oracle that are used in the functions mentioned above are listed as follows:

Tables, views, and synonyms Used to store and access data
Indexes and the optimizer Used to speed access to data
Triggers and integrity constraints Used to maintain the validity of data entered
Privileges, roles, and profiles Used to manage database access and usage
Packages, procedures, and functions Used to code the applications that will use the database

A relational database works on principles of relational data within tables. The relational data models real-world business situations through the use of datasets called tables that can contain different elements or columns. These columns then are able to relate to other columns in other tables, or simply to the primary key via functional dependency.

Exercises

  1. What does a relational database model?
  2. What are the components of a relational database? How are they used?

Reading an Entity-Relationship Diagram

Every database starts out as an entity-relationship diagram. In order to model a business process, the developers of an application must first map out the different components of a system. This map of a business process is often referred to as the entity-relationship diagram, or ERD for short. The ERD consists of two different components, which are listed below.

Entity A person, place, thing, or idea involved in the business process flow
Relationship The ties that bind entities together

In order to understand the process of creating an ERD, an example will be presented. This example is of a business process used by employees of an organization to obtain reimbursement for expenses that they may have incurred on behalf of their employer. See Figure 3-1. Already, a few entities have emerged in the description of the application to be created, namely employee (a person), expenses (things), and the employer (a person or group of people). A relationship has also been identified, obtain reimbursement, or "pay," which is an activity.

Fig03-01.jpg (42550 bytes)

Figure 1: An entity-relationship diagram of the employee expense system

Often, a database application begins with looking at the process as it already exists. For this example, assume there is inefficiency in the current process. There may several different points of entry of data, and there is the possibility that copies will get lost. Finally, there is the turnaround lag in paying employees, during which time the employee is basically owed money by the employer. If there is a problem, the employee will not know about it for several weeks. On top of that, it may take several more weeks for the problem to be corrected. These reasons are enough to justify the need for a more automated process, and the ERD is the mechanism to model that process.

From ERD to LDM

An entity-relationship diagram is helpful to understand the process flow of data through the system. Once an entity-relationship diagram is created, the developer must then create a special diagram that models the data stored in a database to represent the entities and relationships in the ERD. The name of this special diagram is a logical data model, or LDM for short. The LDM will be used to display how all data relating to the business process being modeled is stored in the database. A logical data model consists of a diagrammatic representation of tables in a database. Some of the tables for the example are EMPLOYEE, EXPENSE, BANK_ACCOUNT, and PHONE_NUMBER.

The first step in creating a list of table columns is to determine what will be the unique characteristic of any row in the table. The unique identifier for all employees may be a social security number or some other unique integer assigned by the company to an employee for the term of that employee’s employment. Following the determination of the primary key is determining what items could be included in the EMPLOYEE table--the developer will need to determine what features about employees must be stored in the database. The determination about whether to incorporate data as a column into the table should rest on two conditions:

Is this data functionally dependent on the primary key?
Will there be only one copy of this data per appearance of the primary key?

Once these factors are determined, the designer will know if he or she should include the column in the table or whether the column should be used to define another table. In this example of defining the EMPLOYEE table, the designer may want to include a few different elements, such as the person’s name, hire date, age, spouse name, various telephone numbers, and supervisor’s name.

In the case of bank accounts, employees may have several, each with a set of corresponding information such as bank name and ABA routing number. The additional storage overhead makes it difficult to store all bank account information in the EMPLOYEE table. Data components that have no functional dependency to the other data in a table record should be placed in separate tables. The designer may create a separate table containing bank account information, called BANK_ACCOUNT. The primary key of this table may be the bank account number and the associated employee who owns the account. In addition, there may be several columns that share a common functional dependency on the primary key. One final point is that since the bank account does eventually get associated back to an employee, it is required that there be some method to associate the two tables—a foreign key.

TIP: Data normalization is the act breaking down column data to place in tables where each column in the table is functionally dependent on only one primary key. This process reduces data storage costs by eliminating redundancy and minimizes dependency of any column in the "normalized" database to only one primary key.

Role of Ordinality

Related to the discussion of foreign keys and table relationships is an aspect of data relationships relating to a special term called ordinality. This term represents two important features about a relationship between two tables. The ordinality of a table relationship is a message the designer uses to identify two facts about the relationship:

Is the relationship mandatory or optional for these objects?
Does one record in the table correspond to one or many records in the other table?

The ordinality of a table relationship contains two elements and is generally represented on the logical data model as an "ordered pair," usually (0,N) or (1,1), or (1,N), etc. In some cases, the relationship between two entities may not be required. Consider the following example of employees and expenses. This relationship works in two directions, from employees to expenses and from expenses to employees. In the direction of employees to expenses, the relationship is optional. That is to say, an employee need not have ever incurred expenses on behalf of the company. However, in the other direction, from expenses to employees, the relationship is mandatory because each and every expense submitted to the employee expense system will correspond to an employee. To answer the second question, in the direction of employees to expenses there is a one-to-many relationship, as each employee in the company may have submitted one or more expense reports in the course of their employment, or none at all. In contrast, on the other direction, each expense submitted will always have one and only one employee who submitted it, as shown in Figure 3-2.

Fig03-02.jpg (49964 bytes)

Figure 2: The logical data model for the employee expense system

In summary, the creation of quality data models is more a product of experience than formula. Even though there are many theories on data normalization, the process of it is fairly arbitrary. In fact, most database designers break the so-called "rules" of normalization constantly in an attempt to improve performance. As the saying goes, "normalize until it hurts, denormalize until it works."

Exercises

  1. What is an entity-relationship diagram and how is it read?
  2. What is a logical data model? Identify some methods used to translate an entity-relationship diagram into a data model.
  3. What is ordinality and how is it significant?

Creating the Tables of an Oracle Database

In this section, you will cover the following topics related to creating tables:

Creating tables with integrity constraints
Using table naming conventions
Available dictionary views
Indexes created by constraints
Creating one table with data from another

The next step in creating a database application is defining the database objects that will comprise the logical data model. A major component in this process is creating the tables. This discussion will explain the basic syntax required of developers and DBAs in order to produce the logical database object in Oracle known as tables. At this point, the developer should understand that the only material presented here is the syntax and semantics of creating the table and related database objects. In reality, the job of the DBA with respect to creating tables is much deeper than simply issuing the proper command syntax with a column listing. However, the efforts required of the DBA in terms of sizing a table and managing its usage are covered in the next unit on database administration. For now, the developer can focus on the requirements of table creation.

Creating a Table with Integrity Constraints

The basic creation of a table involves using the create table command. This statement is one of many database object creation statements known in Oracle as the data definition language, or DDL. Within SQL*Plus, the developer can issue the following command to create the EMPLOYEE table described in the previous section on data modeling. Tables created can contain integrity constraints, or rules that limit the type of data that can be placed in the table, row, or column. There are two types of integrity constraints—table constraints and column constraints. A column can have a table constraint on it, limiting the data that can be put into the table. The table constraints available in Oracle are primary keys and unique constraints. Column constraints limit the type of data that can be placed in a specific column. These constraints include foreign keys, check constraints and table constraints.

CREATE TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(25),
firstname VARCHAR2(25),
salary NUMBER(10,4),
CONSTRAINT pk_employee_01
PRIMARY KEY (empid));

What does this create table statement tell the developer about the table being created? First of all, there are several columns, each with a corresponding datatype, or a specification of the "type" of data that can be stored in this column. The types of data available in an Oracle database will be described shortly. There are four columns defined, which correspond to the four columns that the data modeling session from the previous discussion identified. Finally, the statement scratches the surface of data relationships by defining the EMPID column in the statement above to be the primary key. This definition means that data in the EMPID column can be used to identify every row in the table as a unique row.

The definition of a column as the primary key in a table produces a few noticeable effects within the database itself. The term "primary key" itself refers to a special designation for a constraint that says to Oracle, "don’t let any row insert a column value for EMPID that is NULL or that is the same as a column value that already exists for another row." There are some special methods Oracle will use to enforce this integrity constraint. Column values that are part of primary keys have the following conditions enforced on them. Any value in the column for any row must be unique. That is to say that no two rows in the EMPLOYEE table can have the same value for EMPID defined. Secondly, no row can define the value in a column as NULL if that column is part of the primary key. This means that no employee in the EMPLOYEE table can have a NULL value defined for EMPID.

TIP: Integrity constraints are rules that are defined on table columns that prevent anyone from placing inappropriate data in the column. There are five types of integrity constraints: primary key, foreign key, unique, NOT NULL, and check.

Take another moment to review the definition that was determined for the BANK_ACCOUNT table. Remember that the BANK_ACCOUNT table was supposed to have the BANK_ACCT_NO column be its primary key, because that column defines the data that is unique about each row in the table. However, remember also that there is a special relationship between the BANK_ACCOUNT table and the EMPLOYEE table.

CREATE TABLE bank_account
(bank_acct_no VARCHAR2(40),
empid NUMBER(10),
BANK_ROUTE_NO VARCHAR2(40),
BANK_NAME VARCHAR2(50),
CONSTRAINT pk_bank_acct_01
PRIMARY KEY (bank_acct_no),
CONSTRAINT fk_bank_acct_01
FOREIGN KEY (empid) REFERENCES employee (empid));

Notice that in addition to the definition of a primary-key constraint, this table also has a foreign-key constraint. The syntax for the definition allows the column to reference another table’s column, of either the same or a different name. In order for a foreign-key constraint to be valid, the columns in both tables must have exactly the same datatypes. A fuller discussion of datatypes and their significance will appear later in the chapter. The designation "foreign key" tells Oracle that the developer would like to create referential integrity between the EMPID column in the BANK_ACCOUNT table and the EMPLOYEE table. This fact prevents a column in the child table from containing a value that does not exist in the referenced column in the parent table.

An option that can be specified along with the foreign key relates to the deletion of data from the parent. If someone attempts to delete a row from the parent table that contains a referenced value from the child table, Oracle will block the deletion unless the on delete cascade option is specified in the foreign-key definition of the create table statement. When the on delete cascade option is used, Oracle will not only allow the user to delete a referenced record from the parent table, but the deletion will cascade into the child table as well.

CREATE TABLE bank_acct
(bank_acct_no VARCHAR2(40),
empid NUMBER(10),
BANK_ROUTE_NO VARCHAR2(40),
BANK_NAME VARCHAR2(50),
CONSTRAINT pk_bank_acct_01
PRIMARY KEY (bank_acct_no),
CONSTRAINT fk_bank_acct_01
FOREIGN KEY (empid) REFERENCES employee (empid)
ON DELETE CASCADE);

Other integrity constraints abound. There are five types of integrity constraints in all, including primary and foreign keys, unique constraints, NOT NULL constraints, and check constraints.

CREATE TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(25),
firstname VARCHAR2(25),
salary NUMBER(10,4),
home_phone number(15),
CONSTRAINT pk_employee_01
PRIMARY KEY (empid),
CONSTRAINT uk_employee_01
UNIQUE (home_phone));

The definition of a unique constraint on HOME_PHONE prevents anyone from defining a row that contains a phone number that is identical to the phone number of anyone else already in the table. There are two weaknesses in this definition. The first is that having a unique constraint on a home phone number makes it difficult to store records for employees who are spouses with the same telephone number, or who are roommates. Another point to be made about unique constraints, and foreign-key constraints for that matter, is that there is no data integrity enforced on data in a row that has NULL defined for its value. This is a special case scenario that applies only to NULL data in columns with foreign-key, unique, and check constraints defined on them.

TIP: Foreign-key, check, and unique integrity constraints for a column are not enforced on a row if the column data value for the row is NULL.

The final two types of constraints are NOT NULL constraints and check constraints. The first type of constraint identified prevents the data value defined by any row for the column to be NULL if the column has the NOT NULL constraint defined on it. By default, primary keys are defined to be NOT NULL. All other constraints are NULLable unless the developer explicitly defines the column to be NOT NULL.

CREATE TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(25) NOT NULL,
firstname VARCHAR2(25) NOT NULL,
salary NUMBER(10,4) CHECK(salary<500000),
home_phone number(15),
CONSTRAINT pk_employee_01
PRIMARY KEY (empid),
CONSTRAINT uk_employee_01
UNIQUE (home_phone));

Notice that in this table definition, there are three columns defined to be NOT NULL, including the primary key. The two others are the LASTNAME column and the FIRSTNAME column. When defined, the NOT NULL table constraint will be applied to the columns, preventing anyone from creating a row for this table that does not contain a first and last name for the employee.

Notice also that an additional constraint has been created on this table. The final integrity constraint that will be identified by this section is the check constraint. Check constraints allow Oracle to verify the validity of data being entered on a table against a set of constants. These constants act as valid values. If, for example, someone tries to create an employee row for the table defined above with a salary of $1,000,000 per year, Oracle will return an error message saying that the record data defined for the SALARY column has violated the check constraint for that column. Check constraints have a number of limitations, all centering around the fact that the constraint can only refer to a specific set of constant values or operations on those values. A check constraint cannot refer to another column or row in any table, including the one the constraint is defined on, and it cannot refer to special keywords that can have values in them, such as user, sysdate, or rowid (see TIP below). Thus, the check constraint in the table definition above is valid, but the one in the following excerpt from a table definition is not:

CREATE TABLE address
(…,
city check(city in (SELECT city FROM cities))
…);

TIP: There are some special keywords that contain information about certain database conditions. These keywords are user, sysdate, and rowid. The user keyword gives the username of the owner of the current session. The sysdate keyword gives the current date and time at the time the statement is issued. The rowid keyword gives the rowid of the row specified. These keywords cannot be used in conjunction with a NOT NULL constraint.

Exercises

  1. What command is used to create tables?
  2. What is an integrity constraint? What are the five types of integrity constraints?

Using Table Naming Conventions

There are many philosophies around the naming of variables, tables, columns, and other items in software that come from the early days of computing. More often than not, available memory and disk space was small on those early machines. As such, the names of variables in these environments was small. This cryptic method was born out of necessity. In many systems today, however, developers are not faced with that restriction. As a result, the names of variables, columns, and tables need not be bound by the naming rules of yesteryear. However, some standard for naming tables and columns still has value, if only for the sake of readability.

Keep Names Short and Descriptive

A naming convention used in the Oracle database may be compact, but someone viewing variables in the database for the first time should also have some idea of what the variable is supposed to represent. For example, using the name EMP_LN_FN_SAL instead of EMPLOYEE for the table created previously would not be as easily understood as simply calling the table EMPLOYEE, or even EMP.

Names for Child Tables Relate to Their Parent

In certain situations, the developers of an application may find themselves creating multiple tables to define a logical object. The developer may have a logical entity that is represented by several tables, which have a one-to many relationship among them. Consider the EXPENSE table, which was defined to hold the expense summaries that employees submit in order to generate a feed to the AP system. The developer could define a second table in conjunction with the EXPENSE table called EXPENSE_ITEM, which stores detail information about each expenses incurred. Both are descriptive names, and it is obvious by those names that there is some relationship between them.

Foreign-key Columns Should Have the Same Name in Both Tables

In the case of creating foreign-key relationships between columns in two different tables, it also helps if the referring and the referenced columns in both tables share the same name, making the potential existence of a foreign key a bit more obvious.

Names of Associated Objects Should Relate to the Table

Other naming conventions in the database related to tables include giving all integrity constraints, triggers, and indexes meaningful names that identify both the type of constraint created and the table to which the constraint belongs. Consider some of the names chosen in the previous examples. They include: PK_EMPLOYEE_01, which is a primary key (PK) on the EMPLOYEE table; or FK_EMPLOYEE_01, which is a foreign key defined for the EMPLOYEE table. The name of the foreign key includes reference to the table to which the foreign-key constraint belongs.

Avoid Quotes, Keywords, and Nonalphanumeric Characters

You can’t use quotes in the name of a database object. Nor can you use a nonalphanumeric character. This rule has three exceptions: the dollar sign ($), the underscore (_), and the hash mark (#). The dollar sign is most notable in the use for naming dynamic performance views. In general, the DBA should steer clear of its use. The underscore is useful for separating two words or abbreviations, such as EXPENSE_ITEM, or BANK_ACCOUNT.

Exercises

  1. Describe some table naming conventions.
  2. What should be included in the name of a table that has a referential integrity constraint with another table, in which the table referring to the other table is the child table?

Datatypes and Column Definitions

The usage of datatypes to identify the "type" of data a column can hold has been mentioned a few times so far. At this point, it is necessary to discuss the available datatypes in the Oracle database. A few obvious ones should come to mind, as there have already been several tables defined and discussed in the preceding few chapters. Loosely speaking, the "type" of data a column will allow that have been used so far are alphanumeric datatypes that store text strings such as CHAR and VARCHAR2, the NUMBER datatype that stores numeric data only, and the DATE datatype.

Here’s a list of datatypes and their descriptions:

VARCHAR2 Contains text strings of up to 2000 bytes
CHAR Contains text strings of up to 255 bytes
NUMBER Contains numeric data
DATE Contains date data
RAW Contains binary data of up to 2000 bytes
LONG Contains text data of up to 2 gigabytes
LONG RAW Contains binary data of up to 2 gigabytes
ROWID Contains disk location for table rows

Some other datatypes may not be so obvious. For example, the alphanumeric datatypes identified here are not one simple datatype, but two—a CHAR datatype and a VARCHAR2 datatype. Some people may ask, why does VARCHAR2 have the "2" on the end, and the reason is that there may be a VARCHAR datatype defined in future releases of Oracle; so, although VARCHAR and VARCHAR2 for now are synonymous, they may not be in the future. Both the CHAR and the VARCHAR2 variable datatypes can be defined to hold character strings, but there are some subtle differences. First, the CHAR datatype only supports character strings up to a length of 255 bytes in Oracle7 (2,000 for Oracle8), while the VARCHAR2 datatype supports character strings up to a length of 2,000 characters in Oracle7 (4,000 for Oracle8). Second, and perhaps most important, when Oracle stores data in a CHAR datatype, it will pad the value stored in the column up to the length of the column as declared by the table with blanks. In contrast, Oracle will not store padded blank spaces if the same value is stored in a column defined to be datatype VARCHAR2. To illustrate, if a column called LASTNAME was defined as CHAR(50) and the value someone attempted to store in it was "BRADY," the value Oracle would store would actually be "BRADY" with 45 blank spaces to the right of it. That same value stored in a column that was defined as datatype VARCHAR2 would be stored simply as "BRADY."

The NUMBER datatype that is used to store number data can be specified either to store integers or decimals with the addition of a parenthetical precision indicator. For example, if the value 49309.593 were stored in a column defined as datatype NUMBER with no precision identified, the number would be stored as shown: 49309.593. However, in a column defined to be datatype NUMBER(15,2), the same number would be stored as 49309.59, because the number specified after the comma in the parenthetical precision definition of the datatype represents the number of places to the right of the decimal point that will be stored. The number on the left of the comma shows the total width of allowed values stored in this column, including the two decimal places to the right of the decimal point. Finally, a column declared to be of type NUMBER(9) will not store any decimals at all. The number 49309.593 stored in a column defined in this way will appear as 49310, because Oracle automatically rounds up in cases where the value in the precision area that the declared datatype will not support is 5 or above.

Another type that was discussed in Chapter 2 is the DATE datatype, which stores date values in a special format internal to Oracle represented as the number of days since December 31, 4713 B.C.E., the beginning of the Julian date calendar. This datatype offers a great deal of flexibility to users who want to perform date manipulation operations, such as adding 30 days to a given date. In this case, all the user has to do is specify the column declared as a DATE datatype and use an arithmetic add operation to add the number of days. Of course, there are also numerous functions that handle date operations more complex than simple arithmetic. Another nice feature of Oracle’s method for date storage is that it is inherently millennium compliant.

Beyond these datatypes, there is an entire set of important options available to the developer and DBA with respect to type declaration. In Oracle7, these datatypes include LONG, RAW, LONG RAW, and MLSLABEL. RAW datatypes in Oracle store data in binary format up to 2,000 bytes long in version 7. It is useful to store graphics and sound files when used in conjunction with LONG to form the LONG RAW datatype, which can accommodate up to 2 gigabytes of data. The developer or DBA can declare columns to be of LONG datatype, which stores up to 2 gigabytes of alphanumeric text data. There can be only one column declared to be of type LONG in the database as of version 7. The entire operation of storing large blocks of data has been reworked significantly for Oracle8. For a full treatment of the options available in that version for large object data storage, refer to the last unit of this text, which discusses the new features of Oracle8 in conjunction with preparing for certification in that area.

Finally, the ROWID datatype is considered. This datatype stores information related to the disk location of table rows. Generally, no column should be created to store data in type ROWID, but this datatype supports the rowid virtual column associated with every table.

Exercises

  1. Name several different datatypes available in Oracle7.
  2. What are some of the differences between the CHAR and the VARCHAR2 datatype?
  3. How is data stored in the DATE datatype? What is the ROWID datatype?

Indexes Created by Constraints

Indexes are created in support of integrity constraints that enforce uniqueness. The two types of integrity constraints that enforce uniqueness are primary keys and unique constraints. Essentially, unique constraints in Oracle are the same as primary-key constraints, except for the fact that they allow NULL values. When the primary-key or the unique constraint is declared, the index that supports the uniqueness enforcement is also created, and all values in all columns are placed into the index.

The name of the index depends on the name given to the constraint. For example, the table definition statement below creates one index on the primary-key column EMPID. EMPID cannot then contain any NULL values or any duplicates. The name of the index is the same as the name given to the primary key. Thus, the name given to the index created to support uniqueness on the primary key for this table is called PK_EMPLOYEE_01. There are performance benefits associated with indexes that will be discussed in the next chapter, but for now it is sufficient to say that the creation of an index in conjunction with the definition of a primary key is a handy feature of table declaration in Oracle.

CREATE TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(25) NOT NULL,
firstname VARCHAR2(25) NOT NULL,
salary NUMBER(10,4) CHECK(salary<500000),
home_phone number(15),
CONSTRAINT pk_employee_01
PRIMARY KEY (empid),
CONSTRAINT uk_employee_01
UNIQUE (home_phone));

Another important case to consider is the unique constraint index. If the unique constraint is defined in the manner detailed in the code example above, then the name of the corresponding index in the database created automatically by Oracle to support enforcement of the uniqueness of the column will be UK_EMPLOYEE_01. However, there is another method for declaring a unique constraint on a column such that the index created will remain somewhat anonymous.

CREATE TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(25) NOT NULL,
firstname VARCHAR2(25) NOT NULL,
salary NUMBER(10,4) CHECK(salary<500000),
home_phone number(15) UNIQUE,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid));

The unique constraint created in this situation will have the same properties as the unique constraint created in the previous code example. It will also enforce uniqueness on that column just as well as the constraint defined in the previous example. The main difference, however, is the fact that the associated index with the unique constraint created as a result of executing the above code will have a relatively anonymous name associated with it. This name is usually something similar to SYSXXXXXXX, where XXXXXXX is equal to some long string of numbers designed to uniquely identify the index name.

In summary, indexes are used to support the enforcement of unique integrity constraints, such as the primary-key and the unique constraints. The associated indexes can either be named with something corresponding to the name given to the constraint if the constraint is explicitly named, or the constraint can be given a relatively anonymous name automatically by Oracle when the unique index is created. In Unit II, the text will discuss some sizing considerations associated with creating a table. It is important to bear in mind that with the creation of a table comes the creation of an associated primary-key index. The DBA should bear this fact in mind when creating a table.

TIP: When a table is created, an index corresponding to the primary key of the table is also created to enforce uniqueness and to speed performance on data selection that uses the primary key in the where clause of the select statement.

Exercises

  1. Identify two constraints that create indexes.
  2. What determines the name given to an index created automatically?
  3. What two purposes does the index serve in the enforcement of its associated constraint?

Creating One Table with Data from Another

The final area of discussion in this section on creating tables is one on how to create a table with prepopulated data. In most cases, when a developer or the DBA creates a table in Oracle, the table is empty—it has no data in it. Once created, the users or developers are then free to populate the table as long as proper access has been granted. However, there are some cases in which the developer can create a table that already has data in it. The general statement used to create tables in this manner is the create table as select statement.

CREATE TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(25) NOT NULL,
firstname VARCHAR2(25) NOT NULL,
salary NUMBER(10,4) CHECK(salary<500000),
home_phone number(15) UNIQUE,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid))
AS SELECT * FROM hrglobal.empl;

The final as select clause instructs Oracle to insert data into the table it just created from the HRGLOBAL.EMPL table specified. In order to use select *, the columns in the table from which data will be selected must be identical to the column specification made in the table just created. Alternately, an exact copy of a table can be made without declaring any columns at all with the code block shown following this paragraph:

CREATE TABLE employee
AS SELECT * FROM hrglobal.empl;

Finally, it is also possible for the developer or DBA to specify any option in the select statement that makes a copy of data that the developer could use in any other select statement in the database. This feature includes the specification of column concatenation, selecting only a limited number of columns, limiting the number of rows returned with the where clause, or even using arithmetic and other single-row operations to modify data in virtually any way available on other select statements.

Exercises

  1. How can a table be created with data already populated in it?
  2. What limits are there on the data that can be selected in creating a table from existing data?

The Oracle Data Dictionary

In this section, you will cover the following topics related to the Oracle data dictionary:

Available dictionary views
Querying the data dictionary

Few resources in the Oracle database are as useful as the Oracle data dictionary. Developers, DBAs, and users will find themselves referring to the data dictionary time and time again to resolve questions about object availability, roles and privileges, and performance. Whatever the perspective, Oracle has it all stored in the data dictionary. This discussion will introduce the major components of the data dictionary in the Oracle database, pointing out its features and the highlights in order to set groundwork for fuller discussions on the data dictionary in later chapters. It is worth having the major concepts related to the data dictionary down before moving on, as data dictionary views will be referred to in many other areas throughout the rest of the guide.

Available Dictionary Views

There are scores of dictionary tables available in the Oracle data dictionary, used to keep track of many of the database objects that have been discussed. The dictionary tells the user just about anything he or she needs to know about the database, including which objects can be seen by the user, which objects are available, the current performance status of the database, etc. There are a few basic facts about the data dictionary that the user should know. First, the Oracle data dictionary consists of tables where information about the database is stored. The SYS user in Oracle is the only user allowed to update those dictionary tables. It is not recommended that any other users update dictionary tables except to periodically update and purge records from the SYS.AUD$ table, which stores audit trail records.

Rather than having users manipulate the dictionary tables directly, Oracle has available several views on the dictionary tables through which users get a distilled look at the dictionary contents. A view is a database object loosely akin to the idea of a "virtual table." The data in a view is pulled from a real table by way of a select statement and stored in memory. Users can then select and even insert and update records from views. The views in the Oracle data dictionary allow users to see the available database objects to various depths, depending on their needs as users.

The views of the data dictionary are divided into three general categories to correspond to the depth of the database to which one is permitted to view. The three general categories of views are listed as follows, along with a general description of the objects the view will allow the user to see. The text in all caps at the beginning of each bullet below corresponds to text that is prefixed onto the name of the dictionary view categories in question.

USER_ These views typically allow the user to see all database objects in the view that are owned by the user accessing the view.
ALL_ These views typically allow the user to see all database objects in the view that are accessible to the user.
DBA_ This powerful set of views allows those who may access them to see all database objects that correspond to the view in the entire database.

The USER_ views are generally those views with the least scope. They only display a limited amount of information about the database objects that the user created in his or her own schema. One way that tables can be referred to is by their schema owner. For example, assume there is a database with a user named SPANKY. SPANKY creates some tables in his user schema, one of which is called PRODUCTS, and then grants access to those tables to another user on the database called ATHENA. User ATHENA can then refer to SPANKY’s tables as SPANKY.PRODUCTS, or SPANKY.tablename for a more general format. However, if user ATHENA attempts to look in the USER_TABLES view to gather more information about table PRODUCTS, she will find nothing in that view about it. Why? Because the table belongs to user SPANKY.

The next level of scope in dictionary views comes with the ALL_ view. The objects whose information is displayed in the ALL_ view correspond to any database object that the user can look at, change data in, or access in any way, shape, or form. In order for a user to be able to access a database object, one of three conditions must be true. Either the user herself must have created the object, or the user must have been granted access by the object owner to manipulate the object or data in the object, or the owner of the object must have granted access privileges on the object to the PUBLIC user. The PUBLIC user in the database is a special user who represents the access privileges every user has. Thus, when an object owner creates a table and grants access to the table to user PUBLIC, then every user in the database has access privileges to the table created.

The final category of data dictionary views available on the database is the DBA_ view. These views are incredibly handy for developers and DBAs to find out information about every database object corresponding to the information the view captures in the database. Thus, as mentioned earlier, the DBA_TABLES view displays information about every table in the database. At this point, the developer should note that this view allows the user to see objects in the database that the user may not even have permission to use. It can be a violation of security concerns to have certain users even aware of the existence of certain tables. If the DBA is ever in doubt about a user requesting to have access to the DBA_ views, then it is usually a good idea not to give it to them.

The name of each view has two components, which are the scope or depth to which the user will be able to see information about the object in the database (USER_, ALL_, DBA_), followed by the name of the object type itself. For example, information about tables in the database can be found in the USER_TABLES, ALL_TABLES, or DBA_TABLES views. Some other views that correspond to areas that have been discussed or will be discussed, along with some information about the contents of the view, are listed below:

USER_, ALL_, DBA_OBJECTS Gives information about various database objects
USER_, ALL_, DBA_TABLES Displays information about tables in the database
USER_, ALL_, DBA_INDEXES Displays information about indexes in the database
USER_, ALL_, DBA_VIEWS Displays information about tables in the database
USER_, ALL_, DBA_SEQUENCES Displays information about views in the database
USER_, ALL_, DBA_USERS Displays information about users in the database
USER_, ALL_, DBA_CONSTRAINTS Displays information about constraints in the database
USER_, ALL_, DBA_CONS_COLUMNS Displays information about table columns that have constraints in the database
USER_, ALL_, DBA_IND_COLUMNS Displays information about table columns that have indexes in the database

Exercises

  1. What is the data dictionary?
  2. What are the three categories of views that a user may access in the dictionary? How much information about the database is available in each view?
  3. Who owns the data dictionary? Are users allowed to access the tables of the dictionary directly? Why or why not?

Querying the Data Dictionary

The introduction to the views available in the data dictionary now will be used to present ways for the user to select data from the dictionary to understand better how useful the data dictionary is in Oracle. Consider first the need to get information about tables. For the purposes of this presentation, the ALL_ views will be used, except where noted. The first thing every user should learn how to do related to the data dictionary is to list the columns available in a table. A listing of the columns in a table can be obtained from the dictionary with the use of the describe command, often abbreviated as desc.

DESC spanky.products

NAME NULL? TYPE
----------------- ---------- ------------
PRODUCT# NOT NULL NUMBER(10)
PRODUCT_NAME NOT NULL VARCHAR2(35)
QUANTITY NUMBER(10)

The user can find out any information about the database tables that is available for their usage with the ALL_TABLES view. In order to apply the description of any of these views to its sibling in the USER_ or DBA_ family, substitute the scope "available to the user" with "created by the user" or "all those created in the database" for USER_ or DBA_, respectively. All tables displays information about who owns the table, where the table is stored in the database, and information about storage parameters that a table is using.

SELECT owner, table_name
FROM all_tables
WHERE owner = ‘SPANKY’;

Some of the other object views are similar to ALL_TABLES. For example, ALL_INDEXES contains information about the indexes on tables that are available to the user. Some of the information listed in this view corresponds to the features of the index, such as whether or not all values in the indexed column are unique. Other information in the view corresponds to the storage parameters of the index and where the index is stored.

SELECT owner, index_name, table_name, status
FROM all_indexes
WHERE owner = ‘SPANKY’;

The next data dictionary view represents a slight departure from the pattern above. The ALL_VIEWS data dictionary view gives information about all the views in the database available to the user. It lists the schema owner, the view name, and the query that was used to create the view. The column containing the text that created the view is stored in LONG format. To obtain data from this column of the view, the user may need to issue the set long command to set the formatting that SQL*Plus will use to display to a LONG column to something large enough to display the entire query used to create the view. Typically set long 5000 will suffice. More information about creating views in Oracle will be covered in the next chapter.

SET LONG 5000

SELECT owner, view_name, text
FROM all_views
WHERE owner = ‘SPANKY’;

The next view is the USER_USERS view. This view is used to tell the current user of the database more information about his or her environment. Contained in this view are the default locations where objects created by the user will be stored, along with the user profile this user will abide by. There are several other pieces of information that will be more useful in later discussions about users in the next chapter about user creation and in the next unit of this book, which discusses the management of users.

SELECT * FROM user_users;

The next few views discussed cover some more interesting material related to constraints. There are several views in this category that will be discussed, and the first one is the ALL_CONSTRAINTS view. This view is used to display information about the constraints that have been defined in the database. This view is particularly useful in determining the referenced column in cases where referential integrity constraints have been created on a table. This view gives the name of the constraint, the owner of the constraint, the name of the table the constraint is created on, and the name of the referenced table and column if the constraint created is a foreign key.

SELECT constraint_name, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE table_name = ‘PRODUCTS’ and owner = ‘SPANKY’;

The next view discussed also relates to constraints. The view is called ALL_CONS_COLUMNS, and it presents information about the columns that are incorporated into constraints on a table. For example, it is possible to create a primary key for a table that uses as its unique identifier two or more columns from the table. This definition of the primary key is sometimes referred to as a composite primary key. The ALL_CONS_COLUMNS view gives information about the columns that are in the primary key and which order they appear in the composite index.

SELECT constraint_name, table_name, column_name, column_position
FROM all_cons_columns
WHERE table_name = ‘PRODUCTS’ and owner = ‘SPANKY’;

The final dictionary view discussed in this section is related to the ALL_CONS_COLUMNS view above, but extends the scope of that view by providing information about all the indexed columns on the database.

SELECT index_name, table_name, column_name, column_position
FROM all_ind_columns
WHERE table_name = ‘PRODUCTS’ and owner = ‘SPANKY’;

Exercises

  1. Describe usage of object views. What purpose do the constraint views serve?
  2. What is a composite index?
  3. What purpose does the COLUMN_POSITION column serve in some of the dictionary views?

Manipulating Oracle Data

In this section, you will cover the following topics related to manipulating Oracle data:

Inserting new rows into a table
Making changes to existing row data
Deleting data from the Oracle database
The importance of transaction controls

In this section, an introduction to all forms of data change manipulation will be covered. The three types of data change manipulation that exist in the Oracle database include updating, deleting, and inserting data. These statements are collectively known as the data manipulation language of Oracle, or DML for short. Furthermore, a treatment of transaction processing will also be included. Transaction processing is a mechanism that the Oracle database provides in order to facilitate the act of changing data. Without transaction-processing mechanisms, the database would not be able to guarantee that the users would not overwrite one another’s changes midprocess, or select data that is in the process of being changed by another user.

Inserting New Rows into a Table

The first data change manipulation operation that will be discussed is the act of inserting new rows into a table. Once a table is created, there is no data in the table, with the one exception of creating a table populated by rows selected from another table. Even in this case, the data must come from somewhere. This somewhere is from users who enter data into the table via insert statements.

An insert statement has syntax different from a select statement. The general syntax for an insert statement is listed in the following code block, which defines several rows to be added to the PRODUCTS table owned by SPANKY. This table has three columns, titled PRODUCT#, PRODUCT_NAME, and QUANTITY. User SPANKY now wants to put some data in his table, so he executes the following statement designed to place one new row into the PRODUCTS table.

INSERT INTO products (product#,product_name, quantity)
VALUES (7848394, ‘KITTY LITTER’, 12);

Notice a few general rules of syntax in this statement. The insert statement has two parts, the first is one in which the table to receive the inserted row is defined, in conjunction with the columns of the table that will have the column values specified inserted into them. The second portion of the statement defines the actual data values that comprise the row to be added. This portion of the statement is denoted by use of the values keyword.

There are several variations Oracle is capable of handling in order to insert data on a table. For example, it is generally only required for the user to define explicit columns of the table in which to insert data when all columns of the table are not going to have data inserted into them. For example, if user SPANKY only wanted to define the product number and the name at the time the row was inserted, then SPANKY would be required to list the PRODUCT# and PRODUCT_NAME columns in the into clause of the insert statement. However, since he named column values for all columns in the table, the following statement would be just as acceptable for inserting the row into the PRODUCTS table:

INSERT INTO products
VALUES (7848394, ‘KITTY LITTER’, 12);

One important question to ask in this situation is how does Oracle know which column to populate with what data? Assume further about the table that the column datatypes are defined to be NUMBER for PRODUCT# and QUANTITY, and VARCHAR2 for PRODUCT_NAME. What prevents Oracle from placing the 12 in the PRODUCT# column? Again, as with the discussion of column positions in composite indexes as displayed by some of the views in the last section, position can matter in the Oracle database. The position of the data must correspond to the position of the column as it is created in the table. The user can determine the position of each column in a table by using the describe command. The order in which the columns are listed from the describe command is the same order that values should be placed in if the user would like to insert data into the table without explicitly naming the columns of the table.

Another variation of the insert theme is the ability insert has to populate a table using the data obtained from other tables using a select statement. This method of populating table data is similar to the method used by the create table as select statement, which was discussed earlier in the chapter. In this case, the values clause can be omitted entirely. Also, the rules regarding column position of the inserted data apply in this situation as well, meaning that if the user can select data for all columns of the table having data inserted into it, then the user need not name the columns in the insert into clause.

INSERT INTO products
(SELECT product#, product_name, quantity
FROM MASTER.PRODUCTS);

In order to put data into a table, a special privilege must be granted from the table owner to the user who needs to perform the insert. A more complete discussion of object privileges will appear in the next chapter. Note also that data cannot be inserted into a table that allows only read-only access to its data.

Exercises

  1. What statement is used to place new data into an Oracle table?
  2. What are the three options available with the statement that allows new data to be placed into Oracle tables?

Making Changes to Existing Row Data

Often, the data rows in a table will need to be changed. In order to make those changes, the update statement can be used. Updates can be made to any row in a database, except in two cases. Data that a user does not have enough access privileges to update cannot be updated by that user, and data that is READ-ONLY cannot be updated by any user. Data is updated by the user when an update statement is issued.

UPDATE spanky.products
SET quantity = 54
WHERE product# = 4959495;

Notice that the typical update statement has three clauses. The first is the actual update clause, where the table that will be updated is named. The second clause is the set clause. In the set clause, all columns that will be changed by the update statement are named, along with their new values. The list of columns in the set clause can be as long as there are columns in the table being updated. The final clause of the update statement is the where clause. The where clause in an update statement is the same as the where clause in a select statement. There are one or more comparison operations that determine which rows Oracle will update as a result of this statement being issued.

The update and set clauses are mandatory in an update statement. However, the where clause is not. Omitting the where clause in an update statement has the effect of applying the data change to every row that presently exists in the table. Consider the following code block that issues a data change without a where clause specified. The change made by this statement will therefore apply to every column in the table.

UPDATE spanky.products
SET quantity = 0;

Every operation that was possible in the where clauses of a select statement are possible in the where clauses of an update. The where clause in an update statement can have any type of comparison or range operation in it, and can even handle the use of the exists operation or other uses for subqueries.

Exercises

  1. What statement is used to change data in an Oracle table? What clauses in this statement are mandatory?
  2. When can a user not change data in a table?

Deleting Data from the Oracle Database

The removal of data from a database is as much a fact of life as putting the data there in the first place. Removal of database rows from tables is accomplished with the use of the delete statement in SQL*Plus. The syntax for usage of the delete statement is detailed in the following code block. Note that in the example below there is no way to delete data from selected columns in a row in the table; this act is accomplished with the update statement where the columns that are to be "deleted" are set to NULL by the update statement.

DELETE FROM spanky.products
WHERE product# = 4959394;

As in the case of database updates, delete statements use the where clause to help determine which rows are meant to be deleted. In the same way as an update or select statement, the where clause in a delete statement can contain any type of comparison operation, range operation, subquery, or any other operation acceptable for a where clause. In the same way as an update statement, if the where clause is left off the delete statement, then the deletion will be applied to all rows in the table.

Data deletion is a careful matter to undertake. It can be costly to replace data that has been deleted from the database, which is why the privilege to delete information should only be given out to those users who really should be able to delete records from a table. As in the case of updates, data cannot be deleted from a table by any user if that table is read-only.

Exercises

  1. What statement is used to remove data from an Oracle table? What clauses in this statement are mandatory?
  2. When can a user not remove data in a table?

The Importance of Transaction Controls

One of the first realities that a user of the Oracle database must understand is that a change to data made in the Oracle database is not saved immediately. Oracle allows for a period in which a user can specify several changes to database data before saving those changes to the database, and thus making the changes available to other users on the database. This period allotted to a user to make changes is called a transaction.

Transaction processing consists set of controls that allow a user issuing an insert, update, or delete statement to declare a beginning to the series of data change statements he or she will issue. When the user has completed making the changes to the database, the user can save the data to the database by explicitly ending the transaction. Alternately, if a mistake is made at any point during the transaction, the user can have the database discard the changes made on the database in favor of the way the data existed before the transaction began. This principle is known as statement-level read consistency, and it is a feature provided by Oracle as part of transaction-processing mechanisms.

Transactions are created with the usage of two different concepts in the Oracle database. The first concept is the set of commands that define the beginning, middle, and end of a transaction. These commands are listed in the following set of bullets. The second concept is that of special locking mechanisms designed to prevent more than one user from making a change to row information in a database at a time. Locks will be discussed after the transaction control commands are defined.

SET TRANSACTION Initiates the beginning of a transaction and sets key features
COMMIT Ends current transaction by saving database changes and starts new transaction
ROLLBACK Ends current transaction by discarding database changes
SAVEPOINT Defines "midpoint" for the transaction to allow partial rollbacks

SET TRANSACTION

This command is used to define the beginning of a transaction. If any change is made to the database after the set transaction command is issued but before the transaction is ended, all changes made will be considered part of that transaction. The set transaction command has several possible effects. The first effect that can be created by set transaction is that the transaction can be configured to use read-only access to the database. Alternately, the transaction can state to Oracle that it plans to read and write data on the database.

SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;

COMMIT

The commit statement in transaction processing represents the point in time where the user has made all the changes she wants to have logically grouped together, and since no mistakes have been made, the user is ready to save her work. The work keyword is an extraneous word in the commit syntax that is designed for readability. Issuing a commit statement also implicitly begins a new transaction on the database. It is important also to understand the implicit commit that occurs on the database when a user exits SQL*Plus or issues a DDL such as a create table statement.

COMMIT;
COMMIT WORK;

ROLLBACK

If the user has at any point issued a data change statement that produces a mistake, the user can discard the changes made to the database with the use of the rollback statement. After the rollback command is issued, a new transaction is started implicitly by the database session. In addition to rollbacks executed when the rollback statement is issued, there are implicit rollback statements conducted when a statement fails for any reason or if the user cancels a statement with the CTRL+C cancel command.

ROLLBACK;
ROLLBACK WORK;

SAVEPOINT

In some cases involving long transactions or transactions that involve many data changes, the user may not want to scrap all the change made simply because the last statement issued contains a mistake. Savepoints are special operations that allow the user to divide the work of a transaction into different segments. The user can execute rollbacks to the savepoint only, leaving prior changes intact. Savepoint usage is great for situations like this where part of the transaction needs to be recovered in an uncommitted transaction. At the point the rollback to savepoint A1 statement completes in the following code block, only changes made before the savepoint was defined are kept when the commit is issued.

UPDATE spanky.products
SET quantity = 55
WHERE product# = 59495;

SAVEPOINT A1;

UPDATE spanky.products
SET quantity = 504;

ROLLBACK TO SAVEPOINT A1;
COMMIT;

Locks

The final aspect of the Oracle database that allows the user to have transaction processing is a lock, the mechanism by which Oracle prevents data from being changed by more than one user at a time. There are several different types of locks, each with its own level of scope. Locks available on a database are categorized into table-level locks and row-level locks. A table-level lock makes it so that only the user holding the lock can change any piece of row data in the table, during which time no other users can make changes anywhere on the table. A row-level lock is one that allows the user the exclusive ability to change data in one or more rows of the table. However, any row in the table that is not held by the row-level lock can be changed by another user.

TIP: An update statement acquires a special row-level lock called a "row-exclusive" lock, which means that for the period of time the update statement is executing, no other user in the database can view OR change the data in the row. Another update statement, the select for update statement, acquires a more lenient lock called the "share row" lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes.

Exercises

  1. What is transaction processing?
  2. Identify the mechanisms that support transactions.

Chapter Summary

This chapter covered the foundational material for understanding the mechanics of creating an Oracle database. The material in this chapter corresponds to 22 percent of the test material in OCP Exam 1 and represents the foundation on which other exams will build. This understanding is required in order to move the casual user who understands material related to selecting data from an Oracle database to a full-fledged expert on the Oracle database server product. Understanding this material is crucial to understanding several areas in the rest of this guide, including the management of tables, using rollback segments, locks and the contention issues they often produce, and proper management of the data dictionary.

The first portion of this chapter discussed the concepts of data modeling. In order to create a database in Oracle, it is important that all stages of system development be executed carefully. Some of the stages covered include needs assessment, requirements definition, database design, application development, performance tuning, security enforcement, and enhancements development. The final stage in that life cycle is really a miniature version of the first several stages rolled into one. The needs assessment stage is a critical one. It is the period of time where the users of the system are identified, and the desired and required features of the system are documented. After needs assessment, a full list of requirements should be agreed upon and documented so as to avoid costly rework later. Once the requirements of the system are completely understood, the developers of the database portion of the application should model the business process required into an entity-relationship diagram, which consists of entities, or persons, places, things, or ideas involved in the process flow, and the relationships between each entity. This entity-relationship diagram will then be used to create a logical data model, or a pictorial diagram of the tables that will represent each entity and the referential integrity constraints that will represent each relationship. Ordinality is a key point here. Ordinality defines whether the relationship is mandatory for the entities partaking of the relationship, and the record-to-record correspondence of one record in a database. There are three types of record-to-record correspondence in the database—one-to-one, one-to-many, and many-to-many. A one-to-one correspondence means that one record of one table corresponds to one record in another. One-to-many correspondence means that one record from one table corresponds to many records of another table. Many-to-many correspondence means that several records from one table correspond to several records on another table.

Once the planning is complete, than developers and DBAs can move forward with the process of actually creating the database. The syntax for creating a table with column definitions and constraints is covered in this chapter. A table can be created with several different columns. The allowed datatypes for these columns in Oracle7 are VARCHAR2, CHAR, NUMBER, DATE, RAW, LONG, LONG RAW, MLSLABEL and ROWID. More datatypes are available in Oracle8. One or more of these columns is used to define the primary key, or element in each row that distinguishes one row of data from another in the table. A primary key is one type of integrity constraint. Another type of integrity constraint is the foreign key, which defines referential integrity on the table, creating table relationships and often modeling the relationships between entities from the entity-relationship diagram. Referential integrity produces a parent/child relationship between two tables. Sometimes it is useful to name tables according to conventions that have the child objects take on the name of the parent object as part of their own name. The three other constraints available on the database are unique, check, and NOT NULL. Unique constraints prevent duplicate non-NULL values from appearing in a column for two or more rows. Check constraints verify data in a column against a set of constants defined to be valid values. NOT NULL constraints prevent the entry of NULL data for a column on which the NOT NULL constraint is defined. Two of the five constraints create indexes to help enforce the integrity they are designed to enforce. Those two constraints are the ones designed to enforce uniqueness, the unique constraint and the primary key. Finally, a table is created with no data in it, except in the case of the create table as select. This statement allows the user to create a table with row data prepopulated from another table. All options available for regular select statements are available in this statement as well.

The next portion of this chapter discussed the Oracle data dictionary. The data dictionary contains information about all objects created in the database. It also contains a listing of available columns in each object created in the database. Information about table columns can be obtained using the describe command, followed by the name of the table the user wants to view the columns on. Information is kept in data dictionary tables about the objects created in Oracle, where they are stored, and performance statistics. However, the user will not usually access the tables of the data dictionary directly. Rather, the user generally will look at that data using data dictionary views. Data can be selected from views in the same way it can be selected from tables. No user is able to delete data from the data dictionary, because doing so could permanently damage the Oracle database. All tables and views in the Oracle data dictionary are owned by SYS.

Several data dictionary views are available to find out information about the objects discussed in this unit. Those views are divided into three general categories that correspond to the scope of data availability in the view. The USER_ views show information on objects owned by the user, the ALL_ views show information on all the objects accessible by the user, and the DBA_ views show information on all objects in the database. Data dictionary views are available on every type of object in the database, including indexes, constraints, tables, views, synonyms, sequences, and triggers. Additionally, information is available to help the user understand which columns are available in indexes or primary-key constraints. Several views exist to show the position of columns in composite indexes, which are indexes that contain several columns.

The remainder of the chapter discussed the usage of SQL statements for the purpose of changing data in a database. There are three types of data change statements available in the Oracle database. They are update, insert, and delete. The update statement allows the user to change row data that already exists in the database. The insert statement allows the user to add new row data records to the tables of a database. The delete statement allows the user to remove records from the database. The various data change operations are supported in Oracle with the usage of transaction-processing controls. There are several different aspects to transaction processing. These include the commands used to set the beginning, middle, and end of transactions, rollback segments designed to store uncommitted data changes, and the locking mechanisms that allow one and only one user at a time to make changes to the data in the database.

Two-Minute Drill

The stages of system development include needs assessment, requirements definition, database design, application development, performance tuning, database security enforcement, and enhancement development.
The basic types of data relationships in Oracle include primary keys and functional dependency within a table, and foreign-key constraints and referential integrity between two or more tables.
A relational database is composed of objects to store data, objects to manage access to data, and objects to improve performance on accessing data.
Within database planning, it is necessary to create an entity-relationship diagram that acts as a visual representation of the business process being modeled. The diagram consists of people, places, things, and ideas called entities, which are related to one another using activities or a process flow called relationships.
Once an entity-relationship diagram has been created for an application, it must be translated into a logical data model. The logical data model is a collection of tables that represent entities and referential integrity constraints that represent relationships.
A table can be created with five different types of integrity constraints: primary keys, foreign keys, unique constraints, NOT NULL constraints, and check constraints.
Referential integrity often creates a parent/child relationship between two tables, the parent being the referenced table and the child being the referring table. Often, a naming convention that requires child objects to adopt and extend the name of the parent table is useful in identifying these relationships.
The datatypes available in Oracle7 for creating columns in tables are CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG, LONG RAW, ROWID, and MLSLABEL.
Indexes are created automatically in conjunction with primary-key and unique constraints. These indexes are named after the constraint name given to the constraint in the definition of the table.
Tables are created without any data in them, except for tables created with the create table as select statement. These tables are created with data prepopulated from another table.
There is information available in the Oracle database to help users, developers, and DBAs know what objects exist in the Oracle database. The information is in the Oracle data dictionary.
To find the positional order of columns in a table, or what columns there are in table at all, the user can issue a describe command on that table. The Oracle data dictionary will then list all columns in the table being described.
Data dictionary views on database objects are divided into three categories: USER_, for objects created by or owned by the user; ALL_, for all objects accessible to the user; and DBA_, for all objects created of this type in the database.
New rows are inserted into a table with the insert statement. The user issuing the insert statement can insert one or more rows at a time with one statement.
Existing rows in a database table can be modified using the update statement. The update statement contains a where clause similar in function to the where clause of select statements.
Existing rows in a table can be deleted using the delete statement. The delete statement also contains a where clause similar in function to the where clause in update or select statements.
Transaction processing controls the change of data in an Oracle database.
Transaction controls include commands that identify the beginning, middle, and end of a transaction, locking mechanisms that prevent more than one user at a time from making changes in the database, and rollback segments that store uncommitted data changes made to the database.
Hosted by uCoz