Database Concepts
Some of the database terms used in this manual are defined in Definitions.
Definitions
The basic unit of information in a database is a column (or field). A column is an item of data with attributes such as name, type (for example, a character or an integer), and length. Examples of columns are: employee name; date of birth; social security number; inventory item code; or serial number. Other database systems or books may use other terms (such as attribute, entity, or field) for column.
A table (or record) is a named collection of related columns, which are stored and accessed as a unit. Other database systems or books may use other terms (such as record or file) for table. For example, a table named register in a checking account database may have the following columns:
date check number paid to amount
Each row (or occurrence) of a register table in the database contains a value for each of these columns. The definition of a table (made up by its columns) can be represented by a C structures.
A key (or index) is a column through which rapid or sorted access to a row is possible. In the register table, you might define check number as a key field, to allow quick retrieval of a register row through specification of a check number. The keys in RDM are implemented using a B-tree method, one of the most efficient techniques for implementing an index.
In a key scan operation, the keys in an index are read in the order they appear. Key scans are used to produce sorted listings of rows and for fast search operations requiring inspection of a large number of rows (for example, retrieving all checks entered between two dates).
Data relationships often exist between tables. For example, the checking account database may include budget categories. A second record type named budget could be defined with the following data columns:
budget code (a key column) category description monthly allocation balance
To associate a particular budget category with each register row, we added a budget code column to the register table. Whenever check information is entered into the register table, the related budget row can be located via the budget code, and the balance for that budget can be updated by the amount specified in the register row.
The schema is the conceptual definition of the content and organization of a database. A schema will include the definitions of all tables, with their columns, keys and relationships. The compiled form of the schema is called the catalog (or dictionary). The catalog will then be used to create the actual database image either in volatile RAM or on a persistent storage device like a disk drive. In RDM (and most other DBMS vendors) a Schema Language (or Database Definition Language [DDL]), is used to specify the database schema. A sample schema example below demonstrates the specification for the checking account database we have been discussing above. The specifics of the actual schema statements are explained in the Schema Creation section.
create table budget (
budget_code char(6) primary key,
category_desc char(48),
allocation double,
balance double
);
create table register (
check_no integer primary key,
check_date date,
budget_code char(6),
payee char(48),
amount double
);
A data model (or database model) is a conceptual representation of inter-table relationships. The relational database model establishes and maintains inter-table relationships through common data columns. For example, in the checking account example a common data column, budget_code, establishes the relationship between the budget table and the register table.
For a network database model, inter-table relationships are established using "references" to primary keys. The RDM implementation of the network database model enforces referential integrity. For example using the schema above, a budget_code entered into a register table row MUST exist in the budget table. Also, a row in the budget table cannot be deleted if that particular budget_code is being used by a register table row. Using this feature, the database engine performs the referential integrity work for the developer without additional "validation" code needing to be added to the application.
These models are discussed in the following sections. Since RDM supports both the relational and the network database models, you can combine the features of these models to meet the needs of your particular application.
The Relational Database Model
In the relational model, relationships between tables are usually established through common data fields. Recall from the initial checking account example that the relationship between the budget and register tables was formed by including in the register table a budget_code field to identify the budget category. The example schema above demonstrates a schema for a relational model database.
The Network Database Model
In the network database model, the relationships between record types are explicitly defined and directly maintained through sets. A set defines a one-to-many relationship between two record types. Examples of sets are:
- one baseball league has many teams
- one baseball team has many players
Sets are implemented with foreign key links to the row locations of the set members from the owners. The result is a network of interconnected tables.
The illustration below shows the set relationships for the baseball example. The boxes represent instances of the league, team, and player tables. The arrows represent the links that connect the related rows.
Going back to the checking account example we started with, the budget line item will have check register entries associated with it. Each budget item may have many register entries just as each baseball team may have many players.
create table budget (
budget_code char(6) primary key,
category_desc char(48),
allocation double,
balance double
);
create table register (
check_no integer primary key,
check_date date,
budget_code char(6) references budget,
payee char(48),
amount double
);
In the schema definition above, the relationship between the register table and the budget table is created by using a "references" clause that indicates to the RDM engine that the budget_code in the register table and the budget table refer to the same item. This reference establishes referential integrity between the two tables which means that the only budget_code values that can be entered into the register table are ones that already exist in the budget table.
The principal distinction between the relational and network models is that in the relational model, relationships are formed through common data columns between the related tables, while in the network model those relationships are enforced.