HP 3000 Manuals

Up and Running with ALLBASE/SQL

Chapter 6  Glossary of Terms in ALLBASE/SQL 


            

Ad Hoc Query 

Type of query that is issued for the needs of a particular moment.  It is
usually not stored for later use or built into an application.  Ad hoc
queries are important in the use of relational databases for decision
support.

Archive Logging 

Logging method that uses log files to roll back incomplete transactions
after a system failure and to roll forward from an earlier DBEnvironment
backup.  Uses a relatively large file or set of files to record all
activity that modifies databases from the point at which you do a backup
of the entire DBEvironment.  If the logs are intact following a hard
crash, you can recover a DBEnvironment from an earlier saved version.

Attribute 

A characteristic of a data element considered during database design.  As
you organize your data, you arrange it into categories that possess
similar attributes.  The categories are known as entities.

Authority 

Permission to access specific objects for specific purposes within an
ALLBASE/SQL DBEnvironment.  Three major types are SPECIAL authority,
TABLE authority, and RUN authority.

Authorization Group 

See Group.

Base Table 

Table upon which a view is based.

Class 

Special category of ALLBASE/SQL owner that is neither a particular
DBEUserID nor a group.  You do not explicitly create a class; you create
it implicitly by creating objects owned by it.  A class does not have
members like a group.  Objects owned by classes can be dropped or
modified only by a DBA. A class does not have a password associated with
it.

Clustering Index 

An index which attempts to locate new rows in physical proximity to other
rows with similar key values.  Valuable when a large number of inserts
follows a similar large number of delete operations.

Column 

Vertical division within a database table.  Analogous to a field in a
file.

Column Authorization 

Permission to update a specific column within a table.

Column List 

One or more columns specified as part of a query result.

Concurrency 

The ability of multiple users to access the same database files
simultaneously.  Concurrency is regulated by locking, which controls the
degree of concurrent access permitted--from exclusive read or write
access to shared read with concurrent updates.

Constraint 

A condition placed upon a column or table that requires values in the
column or table to meet certain conditions before a row can be inserted
or deleted.  Two types supported by ALLBASE/SQL are unique constraints
and referential constraints.

Cursor Stability (CS) 

An isolation level that guarantees that any data on the page you are
currently accessing cannot be updated by other users until you move off
that page.  This offers a greater degree of concurrency than Repeatable
Read, which is the default isolation level.

Data Analysis 

Study of raw data before building a database.  Concerns the kind of data
that is to be stored and how the data is to be used.

Database 

A structured arrangement of data elements designed for the easy selection
of information.  In ALLBASE/SQL, a database is a collection of tables,
views, and indexes having the same ownership in a DBEnvironment.  A
DBEnvironment may contain several databases.

Database Administrator (DBA) 

The individual with DBA authority who creates and maintains objects in a
DBEnvironment.  DBA authority permits the use of certain restricted SQL
and SQLUtil commands or options, and also confers co-ownership of all the
objects in a DBEnvironment.

Database Design 

The creation of a specific arrangement of data in tables or data sets
with an appropriate security structure.

Data Control Language 

The set of SQL commands that control access to data.  This includes the
ADD, REMOVE, GRANT, and REVOKE commands, as well as the commands to
create, manage, and drop authorization groups.  Also known as DCL.

Data Definition 

The process of creating and dropping database objects.

Data Definition Language 

The set of SQL commands that create and drop database objects.  This
includes the commands to create and remove DBEFileSets, DBEFiles, tables,
views, and indexes.  Also known as DDL.

Data Manipulation 

The process of access data within a database.

Data Manipulation Language 

The set of SQL commands that access data.  This includes the actions of
selecting data, inserting rows, updating columns, and deleting rows.
Also known as DML.

Data Type 

A kind of data that can be stored in database tables.  Valid types are
CHARACTER, VARCHAR, INTEGER, DECIMAL, FLOAT, DATE, TIME, DATETIME,
INTERVAL, BINARY, and VARBINARY. LONG varieties of BINARY and VARBINARY
are also available.

DBA Authority 

The most powerful authority within an ALLBASE/SQL DBEnvironment.
Includes the authority to create new objects, drop all existing objects,
and grant or revoke all authorities for other users.  DBA authority
implies co-ownership of all objects within the DBEnvironment.

DBCore 

A central component of ALLBASE/SQL that performs physical file access and
logging.  DBCore also provides concurrency control through the use of
isolation levels and locking.

DBECon File 

DBEnvironment Configuration File.  This contains startup parameters for
the DBEnvironment.  The contents of this file are initially determined at
the time you issue the START DBE NEW command.  You can modify some of
these parameters using SQLUtil, and you can override some of them with
the START DBE command.

DBECreator 

The individual who issues the START DBE NEW command.  Some maintenance
operations require you to be the DBECreator.

DBEFile 

File containing data or indexes or both.  A DBEFile of type TABLE can
only contain table data; a DBEFile of type INDEX can only contain index
data; a DBEFile of type MIXED can contain both table and index data.
DBEFiles are operating system files and are named according to the
conventions of the operating system.

DBEFileSet 

Logical grouping of DBEFiles.  You associate newly created DBEFiles with
a DBEFileSet, and you specify a DBEFileSet when you create a table.

DBEnvironment 

A collection of files containing one or more databases.  Files include
the DBECon file (which holds startup parameters and log file names);
DBEFile0, which contains the system catalog; and log files.  A
DBenvironment may also contain additional DBEFiles for table and index
data.  The DBEnvironment is the maximum scope of a transaction within
ALLBASE/SQL.

DBEUserID 

In HP-UX, a login name.  In MPE XL, a login name and account name joined
with the character '@'.  One type of owner of database objects.

Embedded SQL Program 

An application program incorporating SQL statements for programmatic
access to ALLBASE/SQL databases.  Each embedded SQL statement
begins with the keywords EXEC SQL. Embedded SQL programs are
preprocessed, then compiled before execution.  For most SQL commands, the
preprocessor stores a section, or runtime version of the command, in the
DBEnvironment.

Entity 

Basic subdivision of data elements in database design.  Each entity is a
thing or event about which information is kept in the database.  For each
entity, there is at least one attribute that uniquely identifies a data
element as belonging to the entity.

Explicit Locking 

Locking of tables in transactions by the use of the LOCK TABLE command.

Expression 

specifies a value.  The most common sources of values are columns in a
table or host variables in an application program.  Expressions are used
to identify columns or rows or to define new values for columns.

Foreign Key 

A column or columns in a table which have a relationship to a primary
column or columns in a different table such that the value must exist in
the primary key column before it can be inserted into the foreign key
column, and it must be deleted from all foreign key columns before it can
be deleted from the primary key column.

Group 

Authorization group.  Membership in a group is used to confer common
ownership or common authorization for other objects in the DBE. You
create a group explicitly, using the CREATE GROUP command, then you add
users to it.  You can then grant authorizations to the group or revoke
authorizations from the group.  You can also use the group name for the
ownership of database objects.

Hash Structure 

ALLBASE/SQL table containing rows that are stored in such a way as to
permit fast access to specific tuples by means of a hash function.  A
hash structure provides a method for quickly finding a row by calculating
its location based on the value of the hash key, which you specify when
you create the table.

Host Variable 

A variable in an application program that receives data from an
ALLBASE/SQL database (output host variable) or passes data to the
database from the program (input host variable).

Implicit Locking 

Locking of tables in transactions according to table type and isolation
level.  For example, PRIVATE tables are locked exclusively for all
access; PUBLIC tables are locked exclusively only for write operations.

Index 

A data structure that potentially speeds access to table data through the
use of an index scan.  The four types of index are:  unique, clustering,
unique and clustering, and non-unique and non-clustering.  An index is
created for one or more key columns in the table.

Index Scan 

A methoe of looking up each row in an index to find its location in the
data file, then accessing the row in the table.  This kind of access
requires the existence of a B-tree index, which you must create.  You do
not explicitly request an index scan.  Instead, SQLCore makes this choice
if the query optimizer decides that the use of an index is the best way
to access the data.

Integrity Constraint 

A device that ensures that a database contains only valid data.  Two
types are the referential constraint and the unique constraint.

ISQL 

The interactive interface to ALLBASE/SQL. ISQL is the tool you use for ad
hoc queries as well as for loading and unloading data and other database
administration tasks.

Isolation Level 

The degree of separation enforced between the transactions of different
users.  There are four levels:  Repeatable Read (RR), Cursor Stability
(CS), Read Committed (RC), and Read Uncommitted (RU). You specify an
isolation level in the BEGIN WORK command.

Join 

A query that accesses data from two or more ALLBASE/SQL tables at a time.
A join column is a column that occurs in both tables of a join (often it
is a key column) and contains similar values in both tables.

Key 

One or more columns on which an index, hash structure, or integrity
constraint are based.

Key Column 

A column which is indexed, or a column which participates in integrity
constraints as all or part of a PRIMARY or FOREIGN key.

Key Value 

The value contained in the columns of a key.  Key values are stored in
index pages along with pointers to the location of rows in data pages.

Locking 

A technique for concurrency control through which ALLBASE/SQL restricts
access to data by one individual when the data is being used by another.
Locks are of three types:  shared, exclusive, or shared with intent to
become exclusive.  Lock type is determined by the type of table being
accessed and by the kind of operation the user is performing.  Locks are
released when a transaction ends with a COMMIT WORK command.

Logging 

The use of log files to record operations that modify database files.
Logging is of two kinds:  nonarchive logging, and archive logging.  Both
kinds permit you to roll back incomplete transactions following a system
failure.  This maintains data integrity by backing out changes to the
database that were not committed.  Only archive logging allows you to
roll forward from an earlier version of a DBEnvironment by reapplying all
committed transactions up to a specific recovery time.

Message Catalog 

A file containing ALLBASE/SQL error and warning messages.  When a message
is displayed, its text comes from this file.  You can look up the meaning
of the message in the ALLBASE/SQL Message Manual.

Message File 

An error and warning file known as SQLMSG generated by a preprocessor
session.  It contains any errors generated during the preprocessing of an
embedded SQL program.

Modified Source File 

The file that results from using the preprocessor on an embedded SQL
source file.  The modified source file can then be compiled into an
executable program.

Module 

A group of sections stored in the DBEnvironment when an embedded SQL
program is preprocessed or when you use the PREPARE command in ISQL. The
sections are activated when the program is run or when the EXECUTE
command is issued in ISQL.

Native Language 

The language of the DBEnvironment or of specific CHAR and VARCHAR columns
in a table.  You specify the DBEnvironment's language in the START DBE
NEW command using the LANG= clause.  In table creation, you can use the
LANG= clause as part of a character column description.  The default
language is known in HP-UX as n-computer; in MPE XL, it is NATIVE-3000.

Nonarchive Logging 

The default logging method.  Uses log files to roll back (that is, undo)
incomplete transactions that were not committed at the time of a system
failure.

Normalization 

A formal process of adjusting table design in relational databases by
examining and adjusting the relationships among columns.

Object 

A structure created and stored in an ALLBASE/SQL DBEnvironment.  The most
common objects are tables, views, indexes, and groups.

Optimizer 

Component of SQLCore which chooses the access path in processing a query.
In optimization, ALLBASE/SQL chooses whether to use serial access to the
data, or whether to use an index or hash structure if they exist.  If
there is a choice among indexes, the optimizer calculates the best access
path.

Owner 

A DBEUserID, a group name, or a class name.  Ownership applies to
database objects such as tables, views, indexes, and authorization
groups.  The owner may drop the object or transfer it to some other
owner.

Predicate 

Part of query syntax that specifies a subset of rows to be returned in
the query result.  Predicates are introduced by the keyword WHERE, so
they are sometimes called WHERE clauses.

Predicates let you specify a range of values.  The comparison predicate
lets you compare a column value with a constant or host variable; the
LIKE predicate lets you compare a column value with a portion of a
character string; the BETWEEN predicate specifies a range of values for a
comparison.  Special predicates of various kinds let you search for rows
in more complex ways, including the use of subqueries.

Preprocessor 

A component of ALLBASE/SQL that converts an embedded SQL program into a
modified source file for input to a compiler in one of several
programming languages:  C, COBOL, FORTRAN, and Pascal.

Primary Key 

A column in a table defined so as to permit reference by foreign keys in
other tables.  A primary key also enforces uniqueness within the column.

Projection 

Relational operation that extracts a subset of columns from a table.

Query 

Request for information from database tables.  A typical example is a
SELECT statement.

Query Language 

A set of operators, expressions, and commands that let you manipulate a
database.  The query language of ALLBASE/SQL is SQL.

Query Result 

The rows retrieved by a SELECT statement.  Query results are also known
as result tables.

Read Committed (RC) 

An isolation level that guarantees only that data you read in a
transaction has been committed by some earlier transaction; that is, it
is not currently in the process of update by some other transaction at
the time you are reading it.  In practical terms, this means that another
transaction can update or delete the same row before your transaction is
over.  However, concurrency is greatly improved.

Read Uncommitted (RU) 

An isolation level that enforces no separation between your transaction
and those of others, because no locks are obtained for reads.  This level
permits dirty reads, that is, reading data from the data buffers that has
not and may never be written to the database at all.

Referential Constraint 

An integrity constraint that enforces a relationship between the rows of
two tables.  Any value you attempt to insert into a table that has a
referential constraint must either be NULL or be the same as a value in
the referenced table.

Relation 

See Table.

Relational Operations 

Ways of extracting data from relational tables.  The three primary
relational operations are selection, projection, and joining.

Relationship 

The meaningful interaction of entities in database design.  Relationships
may be one-to-one, one-to-many, or many-to-many.

Repeatable Read (RR) 

An isolation level that enforces the highest level of separation between
the transactions of different users.  This level guarantees that when you
re-read any data you have read previously in the same transaction, the
value seen in the second read will be the same as the value seen in the
first read.  In practical terms, this means that other users may not
update any data you have read at this isolation level until you COMMIT
WORK.

Result Table 

See Query Result.

Rollback Recovery 

A process by which ALLBASE/SQL backs out of incomplete transactions using
a log file.  If a DBEnvironment stops while some transactions are still
in progress, they must be undone the next time the DBEnvironment starts
up.

Rollforward Recovery 

A process by which ALLBASE/SQL reapplies transactions to a DBEnvironment
from a log file.  Rollforward recovery requires the use of archive
logging.

Row 

Horizontal division within a database table.  Analogous to a record in a
file.

Run Authority 

Permission to execute stored sections that perform ALLBASE/SQL queries or
other operations from an application program.  Required in addition to
permission at the operating system level to execute the application
itself.

Schema 

An ISQL command file containing commands to create a DBEnvironment and
the objects within it, such as DBEFileSets, DBEFiles, tables, views,
indexes, and authorities.  You can create a schema file with an editor,
or you can generate one from an existing DBEnvironment by using SQLGEN.

Also, a TurboIMAGE database definition which is the input to the
TurboIMAGE DBSCHEMA program.

Section 

An SQL command stored in the DBEnvironment for use at run time by an
application program.  When sections are valid, they can be executed
immediately by ALLBASE/SQL. When they are invalid, they must be
revalidated at run time before execution.

Serial Scan 

A method of reading sequentially from the start of a table until the row
is found.  Also called table or relation scan.  This is the default scan
method used to access rows in a table when indexes do not exist.  If
indexes do exist on a table, the optimizer chooses whether to perform an
index scan or a serial scan.

Special Authority 

Permission to use the DBEnvironment in particular ways.  CONNECT
authority lets you establish a user session.  RESOURCE authority lets you
create and drop objects such as tables, views, DBEFiles, etc.  DBA
authority gives you permission to perform all SQL and SQLUtil commands,
and it grants co-ownership of all objects in a DBEnvironment.

SQL 

See Structured Query Language.

SQLCore 

A central component of ALLBASE/SQL. SQLCore checks the syntax of commands
and prepares them for processing.  SQLCore also optimizes queries, that
is, chooses the best access path to the data.

SQLGEN 

A utility program for database administrators that generates the SQL
commands necessary to re-create all or part of a DBEnvironment.  The
output from SQLGEN is a command file (sometimes called a schema) that can
be used as input to ISQL in re-creating database objects.

SQLMigrate 

A utility program for database administrators that assists in migrating a
DBEnvironment from one version of ALLBASE/SQL to another without
unloading and reloading data.

SQLUtil 

A utility program for database administrators that assists with
DBEnvironment maintenance, backup, and recovery.  SQLUtil also lets you
modify the startup parameters for a DBEnvironment.

Structured Query Language 

A standard query language syntax defined by ANSI standards in the United
States nad X/OPEN standards in Europe.  The relational database query
language used by ALLBASE/SQL.

Subquery 

A query within another query.  An example is a subquery embedded in the
predicate of another query.  The result of the inner query is used to
evaluate the outer query.

SYSTEM 

A DBEFileSet created by ALLBASE/SQL when you issue the START DBE NEW
command.  The DBEFile known as DBEFile0 is associated with SYSTEM, which
is the DBEFileSet containing the system catalog.  You can add DBEFiles to
SYSTEM as you would to any other DBEFileSet.

Also, a special user associated with the system views in the system
catalog.

System Catalog 

A system-maintained database of tables and views owned by the special
user SYSTEM and containing information about all the objects in the
DBEnvironment.  Contains data about all the objects created in the
DBEnvironment.  Differs from the DBECon file, which contains startup
parameters, not object definitions.

System Table 

See System View.

System View 

A component view within the system catalog.  You can issue queries on the
views in the system catalog just as you would on ordinary database tables
to display information about the DBEnvironment.

Table 

Basic unit of data storage in a relational database.  Also known as a
relation.  Tables consist of rows and columns.  A result table is a query
result displayed in tabular form.

Table Authority 

Permission to use specific SQL commands on particular tables.  There are
several kinds of TABLE authority:  SELECT, INSERT, DELETE, UPDATE, and
INDEX. SELECT, INSERT, and DELETE let you operate on rows or sets of rows
in a table; UPDATE lets you modify specific rows or columns in a table;
and INDEX lets you create indexes on a table.

Transaction 

A unit of work in ALLBASE/SQL. Also, a unit of DBEnvironment logging and
recovery.  A transaction is started with a BEGIN WORK command and is
ended by a COMMIT WORK command.  The BEGIN WORK statement may be
implicitly issued by ALLBASE/SQL if no other transaction is current.

Unique Constraint 

An integrity constraint which requires that no two rows in a table have
the same values in a specified column or columns.

Unique Index 

An index which requires that no two rows in a table have the same key
value.

Validation 

The process by which ALLBASE/SQL marks a section valid in the system
catalog.  A section is marked valid if all the objects it refers to
exist, and if it has been optimized.  A valid section can be executed
immediately at run time with no further preparation.

View 

a table derived by placing a "window" over one or more tables.  The
derivation of a view is a SELECT command.  View names are governed by the
same rules as table names.

MPE/iX 5.0 Documentation