Database

-Tables

Columns

Columns

>Mndexesj c

Stored Procedures

i Views J)

Tables

Relational databases store all their data in tables. A table is a data structure consisting of an unordered set of horizontal rows, each containing the same number of vertical columns. The intersection of an individual row and column is a field that contains a specific piece of information. Much of the power of relational databases comes from defining the relations among the tables.

InterBase stores information about metadata in special tables, called system tables. System tables have predefined columns that store information about the type of metadata in that table. All system tables begin with "RDB$". An example of a system table is RDB$RELATIONS, which stores information about each table in the database.

System tables have the same structure as user-defined tables and are stored in the same database as the user-defined tables. Because the metadata, user-defined tables, and data are all stored in the same database file, each database is a complete unit and can be easily transported between machines.

System tables can be modified like any other database tables. Unless you understand all the interrelationships between the system tables, however, modifying them directly may adversely affect other system tables and disrupt your database. For more information about system tables, see the Language Reference.

Columns

Creating a table mainly involves defining the columns in the table. The main attributes of a column include:

  • The name of the column
  • Data type of the column or the domain on which it is based
  • Whether or not the column is allowed to be NULL
  • Optional referential integrity constraints

Data Types

Data is stored in a predefined format called a data type. Data types can be classified into four categories: numeric, character, date, and BLOB. Numeric data types handle everything from integers to double-precision floating point values. Character data types hold strings of text. Date data types are used for storing date and time values. InterBase also supports arrays of these standard data types.

While numeric, character, and date are standard data types, the BLOB data type and arrays of standard data types deserve special mention.

Numeric Data Types

Numeric data types are: SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION, NUMERIC, and DECIMAL. Most of these correspond in size and precision to similar data types in C. For example, SMALLINT typically corresponds to a short in C, and DOUBLE PRECISION corresponds to a double. When comparing or assigning values of different numeric types, InterBase handles many conversions automatically. Others can be coerced using the CAST() function.

Character Data Types

Character data types are CHAR and VARCHAR. They allow strings of multiple characters to be stored in a column. CHAR and VARCHAR differ in the way extra characters are treated. The CHAR data type uses all characters up to the end of the array, but the VARCHAR data type is significant only to the first NULL character.

Date Data Types

The DATE data type is used to store date and time values. InterBase handles assignment and comparison between strings and dates. String values representing dates can be in a variety of formats, such as "12-1-94" and "December 1, 1994". Certain date constants are also supported, such as "TODAY" and "TOMORROW".

BLOB Data Types

InterBase supports a binary large object (BLOB) data type, that can hold data of unlimited size. The BLOB is an extension of the standard relational model, which ordinarily provides only for data types of fixed width.

The BLOB data type is analogous to a flat file because BLOB data can be stored in any format (for example, binary or ASCII). A BLOB, however, is not a separate file. BLOB data is stored in the database with all other data. Because BLOB columns often contain large, variable amounts of data, BLOB columns are stored and retrieved in segments.

Conversion of BLOB data to other data types in InterBase is not directly supported, but on some platforms, BLOB filters can translate BLOB data from one BLOB format to another.

Arrays of Data Types

InterBase supports arrays of all data types except BLOB. An array is a collection of values, or elements, each of the same data type. Individual array elements, blocks of contiguous elements, or the entire array can be accessed using standard SQL statements and API calls.

An array in InterBase can be up to 16 dimensions. Because InterBase arrays are multidimensional, you can store arrays as a whole in a single field, making accessing and retrieval fast and simple. An element of array data is referenced through the use of coordinates, or offsets, into the array.

Domains

In addition to explicitly stating the data type of columns, InterBase allows global column definitions, or domains, upon which column definitions can be based. A domain specifies a data type, and a set of column attributes and constraints. Subsequent table definitions can use the domain to define columns.

Referential Integrity Constraints

InterBase allows you to define referential integrity rules for a column, called referential integrity constraints. Integrity constraints govern column-to-table and table-to-table relationships and validate data entries. They are implemented through primary keys, foreign keys, and check constraints. Basically, a primary key is a column (or group of columns) that uniquely identifies a row in a table. A foreign key is a column whose value must match a value of a column in another table. A check constraint limits data entry to a specific range or set of values.

For example, an EMPLOYEE table could be defined to have a foreign key column named DEPT_NO that is defined to match the department number column in a DEPARTMENT table. This would ensure that each employee in the EMPLOYEE table is assigned to an existing department in the DEPARTMENT table.

For more information about referential integrity, see the Data Definition Guide.

Indexes

Indexes are mechanisms for improving the speed of data retrieval. An index identifies columns that can be used to retrieve and sort rows efficiently in the table. It provides a means to scan only a specific subset of the rows in a table, improving the speed of data access.

InterBase automatically defines unique indexes for a table's PRIMARY KEY and FOREIGN KEY constraints. For more information about indexes, see the Data Definition Guide.

Views

A view is a virtual table that is not physically stored in the database, but appears exactly like a "real" table. A view can contain data from one or more tables or other views and is used to store often-used queries or query sets in a database.

Views can also provide a limited means of security, because they can provide users access to a subset of available data while hiding other related and sensitive data. For more information about views, see the Data Definition Guide.

Stored Procedures

A stored procedure is a self-contained program written in InterBase procedure and trigger language, an extension of SQL. Stored procedures are part of a database's metadata. Stored procedures can receive input parameters from and return values to applications and can be executed explicitly from applications, or substituted for a table name in a SELECT statement.

Stored procedures provide:

  • Modular design: stored procedures can be shared by applications that access the same database, eliminating duplicate code, and reducing the size of applications.
  • Streamlined maintenance: when a procedure is updated, the changes are automatically reflected in all applications that use it without the need to recompile and relink them. They are compiled and optimized only once for each client.
  • Improved performance: especially for remote client access. Stored procedures are executed by the server, not the client, which reduces network traffic.

Triggers

A trigger is a self-contained routine associated with a table or view that automatically performs an action when a row in the table or view is inserted, updated, or deleted.

Triggers can provide:

  • Automatic enforcement of data restrictions to ensure that users enter only valid values into columns.
  • Reduced application maintenance, because changes to a trigger are automatically reflected in all applications that use the associated table without the need to recompile and relink them.
  • Automatic logging of changes to tables. An application can keep a running log of changes with a trigger that fires whenever a table is modified.
  • Event alerters in triggers can automatically notify applications of changes to the database.

When a trigger is invoked, it has immediate access to data being stored, modified, or erased. The trigger may also access data in other tables. Using the available data, you can design the trigger to:

  • Abort an operation, possibly with an error message.
  • Set values in the accessed record.
  • Insert, update, or delete rows in other tables.
  • Signal that an event has occurred using an event alerter.

CHAPTER 3

Was this article helpful?

0 0

Post a comment