Previous | Table of Contents | Next

Page 18

FIG. 2.3
Changes in the position
of records due to a field
addition.


The problems occur when you add a column to the file. This changes the starting position of the second and subsequent records. The application will no longer display the proper values because the application no longer knows where the records begin and end. To correct this problem, you need to change the software. This change is labor intensive and risky because errors can be introduced to the applications. In addition, you must restructure the data in the database file. To avoid this mess, developers tack on segments in different places, causing redundant data to exist within the database.

Because of these problems, relational databases swept the industry. Relational tables enable the developer to add a column to the table without any worry to the existing applications. Because relational databases are not position-oriented, none of the existing software needs to be rewritten. In addition, there is no restructuring of the database. This makes relational databases the database type of choice.

Database Components

Various components comprise a relational database management system such as Oracle7. This discussion begins with the objects that hold data. Database objects have a similarity to the devices that store manual records. In Oracle7, all database objects must have an owner. Ownership is determined through a user account or id. Before any database object can be built, the database administrator creates a user account or id. Database items are tied to the user id.

NOTE
I store records in three drawers in my desk. My desk is very much like a database. To begin with, the desk is mine. Because I am the owner, I can put index cards into it or rubber bands or any other objects I choose. You can think of the drawers in my desk as the database. Drawers separate the records in my desk. Oracle7 also has objects similar to my desk drawers. They are called tablespaces. Within the tablespaces, you can create datafiles. Datafiles are very similar to partitions in my desk drawer reserved to hold my index cards.

At the highest level, a database consists a number of tablespaces. Tablespaces reserve sections of the disk for record retention. Each tablespace contains one or more datafiles. The datafile has a defined amount of disk space. The size of the tablespace equals the aggregate size of the datafiles. Although the datafiles fill up with data and database objects, you can always increase the size of the tablespace by adding another datafile to the tablespace.

Page 19

One of the main objects within the tablespace is the table. A table is comparable to a set of index cards. Each card in the set is the same as a row or record in the table. You can also think of tables as spreadsheets. A spreadsheet contains one or more rows that have one of more columns of data. Figure 2.4 illustrates table columns and rows.

FIG. 2.4
Row and columns in a table.


The spreadsheet contains five records or rows, and each record contains three columns. Table columns are also called fields or items. Columns contain similar bits of information. Column A contains the employee last names. The row is used to relate the set of columns. All the data contained in the row pertain to one instance of the table entity. In row 4, each column contains information that pertains to employee "Harry Truman." None of the values in the record pertain to any of the other employees contained in the spreadsheet.

NOTE
One of the differences between the records in my desk drawer and records stored in an electronic database is that each of the items in the database has a unique name. Everything needs a name because you need a way to tell the database manager which record you want, and the database manager needs a way to identify the record you want.

The records illustrated in Figure 2.4 are part of a set of records contained in a table called Employee. The first column is called last_name, the second first_name, and the third employment_date. The table is contained in the tablespace Emp. You can obtain Truman's employment date by telling Oracle7 to open an Oracle session using an Oracle user id that has appropriate privileges, locate the Employee file in the Emp tablespace, obtain the fourth record, and display the employment_date column.

Tablespaces

Oracle7 uses several different kinds of tablespaces. The tablespace used in the preceding example is a data tablespace. It holds data and the objects used to control the data. Another tablespace is the system tablespace. This area is an area where Oracle7 stores information it needs to perform its functions. Normally, you won't be concerned with this area.

A third tablespace is the temporary tablespace. This is where Oracle stores temporary information it needs to record. A fourth tablespace is the tool tablespace. Oracle7 stores the database objects related to the various tools in this space. These tools consist of products such as Oracle Forms 4.5. A fifth tablespace is a rollback tablespace. When you make changes to a database, these changes are not made permanent until you issue a special command called commit. The transactions are saved in the temporary tablespace and the rollback tablespaces. Thus Oracle7 enables you to restore the database to its condition before the changes were made. This protects against erroneous changes. In addition, if you are using a program to modify data, and the program stops abnormally, all changes made by the program are rolled back.

Page 20

TIP
I get concerned with tablespaces under two circumstances. The first is when the tablespace gets filled and I can't save any additional data. This causes me to add a new datafile to the tablespace. The second and more common problem occurs when I am performing data conversion routines on large files. The temporary and rollback tablespaces have a tendency to fill up with these updates because they are not committed until the program is done executing.
The program stops abnormally when these tablespaces are filled. All the new changes are then lost. To avoid this situation, you may want to divide the data into smaller sections. This division enables you to commit the changes and free up space in the tablespaces.

Tables and Fields

Tables and fields are the most common database objects you will probably use. Tables are sets of records that contain various values called fields. When creating a table, you must include the names and characteristics of the fields in the definition.

There are three types of fields. The first type of data is called character. Character fields can hold alphanumeric values such as letters of the alphabet, numbers, or special characters. The second type of data is numeric. Numeric fields hold a number such as an integer or decimal. The third data type is date. This data type holds a calendar date. Dates are a special type of data. The value is stored in the database as a number and is displayed as a set of characters. An example of Oracle's default date display is "08-APR-51". Dates are stored in the table as a number because of the necessity to perform mathematical calculations using the fields. Table 2.1 contains a list of the data types used in table definitions.

Table 2.1Data Types

Data Type Description
char(len) Defines an alphanumeric field with a total fixed length of
len. The maximum length of a char field is 255 characters.
varchar2(len) Defines an alphanumeric field with a total variable length of
len. The maximum length of a varchar2 field is 2,000 characters.
number(len, prec) Defines a numeric field with a length of
len and a precision of prec. Precision indicates the number of decimal positions
the number may have. Monetary values generally contain a
precision of 2.
date Defines a date format field. This field can hold date
information that ranges from January 1, 4712 B.C. to December 31, 4712 A.D.

A second characteristic of a field is the length. Each field, except date fields, may contain a length specification. The number data type contains two settings: maximum length of the number, and amount of decimal positions or precision.

Previous | Table of Contents | Next