HP 3000 Manuals

ALLBASE/SQL Reference Manual

ALLBASE/SQL Databases 

The largest unit in ALLBASE/SQL is the DBEnvironment, which can be seen
logically as a collection of database objects or physically as a group of
files.  Objects are database structures.

Logical Concepts 

Logically, the DBEnvironment is a structure which contains one or more
relational databases.  In ALLBASE/SQL, a database is a set of tables,
views, and other objects that have the same owner. 

The data in a relational database is organized in tables.  A table is a
two-dimensional structure of columns and rows: 

                             The Parts Table
     ----------------+------------------------------+----------
     PARTNUMBER      |PARTNAME                      |SALESPRICE
     ----------------+------------------------------+---------- _
     1123-P-01       |Central Processor             |    500.00  |
     1133-P-01       |Communication Processor       |    200.00  |- rows
     1143-P-01       |Video Processor               |    180.00 _|
           |                       |                       |
           +-----------------------+-----------------------+
                                   |
                                columns

Often a table is referred to as a relation, and a row as a tuple.  You
can also think of a row as a record, and a column as a field in a file,
or table.

A view is a table derived by placing a "window" over one or more tables
to let users or programs view only certain data.  A view derived from the
Parts table shown above might look like this:

                     The PartsID View
     ----------------+------------------------------
     PARTNUMBER      |PARTNAME
     ----------------+------------------------------
     1123-P-01       |Central Processor
     1133-P-01       |Communication Processor
     1143-P-01       |Video Processor

The owner of a table or view can be one of the three following entities:

   1.  Individual as identified by the DBEUserID, which is the logon
       name.  An individual who logs on as WOLFGANG.ACCOUNTNAME is known
       to ALLBASE/SQL as WOLFGANG@ACCOUNTNAME. 

   2.  Authorization group, a named collection of individuals or other
       groups.  Wolfgang might be part of a group named Managers.  A
       group must be created explicitly by using the CREATE GROUP
       statement. 

   3.  Class, a name that identifies a user-defined abstraction, such as
       a department or a function.  Wolfgang might use tables owned by a
       class called Marketing.  A class is created implicitly when you
       create objects that have a class name as owner name.

Refer to the chapter "Using ALLBASE/SQL" in this manual and to the
chapter "Logical Design" in the ALLBASE/SQL Database Administration Guide 
for additional information about authorization groups and classes.

To use data in a database, you need to specify the names of the tables
and views you need.  You must also specify the owner name associated with
the table or view unless you own it (or you have used the ISQL SET OWNER
command).  When accessing the Composers table, Wolfgang needs to specify
only Composers.  However, when accessing the quotas table, he needs to
specify Marketing.Quotas because Marketing owns the Quotas table.

You also need the proper authority to access data.  An authority is a
privilege given to a user to perform a specific database operation, such
as accessing certain tables and views and creating groups or tables.
ALLBASE/SQL uses authorities to safeguard databases from access by
unauthorized users.  In the example above, before Wolfgang can access the
Quotas table, he must be granted the authority to do so by the owner of
the table.

If you have been granted the proper authorization, you access databases
by first connecting to the DBEnvironment in which they reside:

     CONNECT TO 'DBEnvironmentName'

Physical Concepts 

Physically, the DBEnvironment is a collection of files for one or more
logical databases.

A DBEFile is an MPE XL file.  Most files in a DBEnvironment are DBEFiles.
Data in the tables of logical databases is stored in one or more
DBEFiles.  Indexes are also stored in DBEFiles; an index is a structure
that ALLBASE/SQL can use to quickly find data in a table.

A DBEFileSet is a collection of DBEFiles.  You associate physical storage
with a DBEFileSet by adding DBEFiles to the DBEFileSet.  Each DBEFileSet
can have more than one DBEFile, but a single DBEFile cannot contain data
for more than one DBEFileSet.

When you create a table, you can specify the DBEFileSet with which the
table and its indexes will be associated.  This causes physical storage
space for the table and indexes to be allocated from the DBEFiles
associated with the specified DBEFileSet.  Figure 1-2  illustrates the
relationships among tables, DBEFiles, and DBEFileSets. 


          Figure 1-2.  How Tables, DBEFiles, and DBEFileSets Are Related 

A DBEFileSet specifies the files that contain data for one or more tables
associated with the DBEFileSet.  These tables do not have to be in the
same database.  Figure 1-3  illustrates that, while a DBEFileSet can
contain data for all the tables in a database, a DBEFileSet can also
contain data for some of the tables in a database, or for tables in more
than one database.  Thus DBEFileSets offer a way to allocate data storage
independently of how users think about the data.


          Figure 1-3.  Databases and DBEFileSets 

A DBEnvironment, illustrated in Figure 1-3 , houses the DBEFiles for
one or more ALLBASE/SQL databases, plus the following, which contain
information for all databases in the DBEnvironment: 

   *   A DBECon file.  This file contains information about the
       DBEnvironment configuration, such as the size of various buffers
       and other startup parameters.  The name of the DBECon file is the
       same as the name of the DBEnvironment.

   *   A system catalog.  The system catalog is a collection of tables
       and views that contain data describing DBEnvironment structure and
       activity.  The parts of the system catalog necessary for
       DBEnvironment startup reside in a DBEFile known by default as
       DBEFile0.  All system catalog DBEFiles are associated with a
       DBEFileSet called SYSTEM.

   *   One or two log files.  A log file contains a log of DBEnvironment
       changes.  ALLBASE/SQL uses log files to undo (roll back) or redo
       (roll forward) changes made in the DBEnvironment.  The log files
       are known by default as DBELog1 and DBELog2.


          Figure 1-4.  Elements of an ALLBASE/SQL DBEnvironment 

Most database users need not be concerned with the physical aspects of
ALLBASE/SQL databases beyond knowing which DBEnvironment contains the
databases they want to access.

MPE/iX 5.5 Documentation