IBConsole - Introduction to Database Objects
Go Up to IBConsole
A database consists of a variety of database objects, such as tables, views, domains, stored procedures, and triggers. Database objects contain all the information about the structure of the database and the data. Because they encapsulate information about the data, database objects are referred to as metadata.
The following sections provide an overview of the InterBase database objects and concepts:
- Tables
- Columns
- Data Types
- Domains
- Referential Integrity Constraints
- Indexes
- Stored Procedures
- Triggers
- Generators
- Security
Contents
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 a complete discussion of tables, see the Data Definition Guide. For a complete description of the InterBase system tables and views, 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
For a complete discussion on columns, see the Data Definition Guide.
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.
While numeric, character, and date are standard data types, the BLOB data type deserves special mention.
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.
For a complete discussion of data types, see Data Types.
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.
For a complete discussion on domains, see the Data Definition Guide.
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 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, 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 PRIMARY KEY and FOREIGN KEY constraints of a table.
For a complete discussion of indexes, 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 the metadata of a database. 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.
- Secure access to data: you can design your stored procedures to access privileged data in ways you specify.
For a complete discussion of stored procedures, see the Data Definition Guide.
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.
When a database operation invokes a trigger, 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:
- Cancel an operation, possibly with an error message.
- Set values in the accessed record.
- Insert, update, or delete rows in other tables.
For a complete discussion of triggers, see the Data Definition Guide.
Generators
A generator is a mechanism that creates a unique, sequential number that is automatically inserted into a column by the database when SQL data manipulation operations such as INSERT or UPDATE occur. Generators are typically used to produce unique values that can be inserted into a column that is used as a PRIMARY KEY. Any number of generators can be defined for a database, as long as each generator as a unique name.
For a complete discussion of generators, see the Data Definition Guide.
Security
SQL security is controlled at the table level with access privileges, a list of operations that a user is allowed to perform on a given table or view. The GRANT statement assigns access privileges for a table or view to specified users or procedures. The REVOKE statement removes previously granted access privileges.
For a complete discussion of security, see the Operations Guide, Chapter 6: “Database Security.”