Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


The Mimer SQL Relational Database


A database is a collection of information organized so that storage, retrieval, and modification of the data is as efficient as possible.

The Mimer SQL database is relational, which means that the information in the database is presented to the user in the form of tables. The tables represent a logical description of the contents of the database which is independent of, and insulates the user from, the physical storage format of the data.

The Mimer SQL database includes the data dictionary which is a set of tables describing the organization of the database and is used primarily by the database management system itself.

The database, although located on a single physical platform, may be accessed from many distinct platforms, even at remote geographical locations (linked over a network through client/server support).

Commands are available for managing the connections to different databases, see Managing Database Connections, so the actual database being accessed may change during the course of an SQL session. At any one time, however, the database may be regarded as one single organized collection of information.

The Data Dictionary

The data dictionary contains information on all the objects stored in a Mimer SQL database and how they relate to one another.

The data dictionary stores information about:

Privileges
Schemas
Databanks
Sequences
Domains
Shadows
Functions
Synonyms
Idents
Tables
Indexes
Triggers
Modules
Views
Procedures

Mimer SQL Database Objects

The objects stored in a Mimer SQL database can be divided into the following groups:

Databanks

A databank is the physical file where a collection of tables is stored. A Mimer SQL database may include any number of databanks. There are two types of databanks: system and user.

System Databanks

System databanks contain system information used by the database manager. These databanks are defined when the system is created.

The system databanks are:

User Databanks

User databanks contain the user tables. These databanks are defined by the user(s) responsible for setting up the database. See the Mimer SQL Reference Manual, Chapter 4, Specifying the Location of User Databanks, for details concerning path names.

The division of tables between different user databanks is a physical file storage issue and does not affect the way the database contents are presented to the user. Except in special situations (such as when creating tables), databanks are completely invisible to the user.

Note: In Mimer SQL, backup and restore can be performed on a per-databank basis rather than on the entire database file base, see the Mimer SQL Systmen Management Handbook, chapter 5, Backing-up and Restoring Data for more information.

Idents

An ident is an authorization-id used to identify users, programs and groups. There are four types of ident in a Mimer SQL database: USER, OS_USER, PROGRAM and GROUP idents.

USER Idents

USER idents identify individual users who can connect to a Mimer SQL database.

A user's access to the database is protected by a password and is restricted by the specific privileges granted to the ident. USER idents are generally associated with specific physical individuals who are authorized to use the system.

OS_USER Idents

OS_USER idents are idents which reflect a user id defined by the operating system.

An OS_USER ident allows the user currently logged in to the operating system to access the Mimer SQL database without providing a user name or password.

For example: if the current operating system user is ALBERT and there is an OS_USER ident called ALBERT defined in Mimer SQL, ALBERT may start Mimer BSQL (for example) and connect directly to Mimer SQL simply by pressing <return> at the Username: prompt.

If an OS_USER ident is defined with a password in Mimer SQL, the ident may also connect to Mimer SQL in the same way as a USER ident (i.e. by providing the user name and password). An OS_USER ident may not have the same name as a USER ident in the database.

PROGRAM Idents

PROGRAM idents do not strictly connect to Mimer SQL, but they may be entered from within an application program by using the ENTER statement.

The ENTER statement may only be used by an ident who is already connected to a Mimer SQL database.

An ident is granted the privilege to enter a PROGRAM ident. A PROGRAM ident is set up to have certain privileges and these apply after the PROGRAM statement has been used. PROGRAM idents are generally associated with specific functions within the system, rather than with physical individuals.

The PROGRAM statement is used to return to the state of privileges and database access that existed before PROGRAM was used.

GROUP Idents

GROUP idents are collective identities used to define groups of user and/or program idents.

Any privileges granted to or revoked from a GROUP ident automatically apply to all members of the group. Any ident can be a member of as many groups as required, and a group can include any number of members.

GROUP idents provide a facility for organizing the privilege structure in the database system. All idents are automatically members of a logical group which is specified in Mimer SQL statements by using the keyword PUBLIC.

Schemas

A schema defines a local environment within which private database objects can be created. The ident creating the schema has the right to create objects in it and to drop objects from it.

When a USER, OS_USER or PROGRAM ident is created, a schema with the same name can also be automatically created and the created ident becomes the creator of the schema. This happens by default unless WITHOUT SCHEMA is specified in the CREATE IDENT statement.

When a private database object is created, the name for it can be specified in a fully qualified form which identifies the schema in which it is to be created. The names of objects must be unique within the schema to which they belong, according to the rules for the particular object-type.

If an unqualified name is specified for a private database object, a schema name equivalent to the name of the current ident is assumed.

Tables

Data in a relational database is logically organized in tables, which consist of horizontal rows and vertical columns.

Columns are identified by a column-name. Each row in a table contains data pertaining to a specific entry in the database. Each field, defined by the intersection of a row and a column, contains a single item of data.

For example, a table containing information about currencies may have columns for the currency code, name and exchange rate against the euro:

 CREATE TABLE currencies (
       code CHARACTER(3) PRIMARY KEY,
       currency CHARACTER(32) NOT NULL,
       exchange_rate DECIMAL(12, 4));
 

CURRENCIES

CODE
CURRENCY
EXCHANGE_RATE
AED
UAE Dirhams
 3.1030
AFA
Afghanis
 4092.0000
ALL
Leke
 122.3000
AMD
Armenian Drams
 -
ANG
Netherlands Antillian Guilders
 1.4890
AOA
Kwanza
 -
...
...
 ...

Each row in a table must have the same set of data items (one for each column in the table), but not all the items need to be filled in.

A column can have a default value defined (either as part of the column specification itself or by using a domain with a default value) and this is stored in a field where an explicit value for the data item has not been specified.

If no default value been defined for a column, the NULL value (which means the value is unknown) is stored when no data value is supplied.

A relational database is built up of several inter-dependent tables which can be joined together. Tables are joined by using related values that appear in one or more columns in each of the tables. Part of the flexibility of a relational database structure is the ability to add more tables to an existing database. A new table can relate to an existing database structure by having columns with data that relates to the data in columns of the existing tables. No alterations to the existing data structure are required.

All the fields in any one column contain the same data type with the same maximum length. See the Mimer SQL Reference Manual, Chapter 5, Data Types in SQL Statements, for a detailed description of data types supported by Mimer SQL.

Base Tables and Views

The logical representation of data in a Mimer SQL database is stored in tables (this is what the user sees, as distinct from the physical storage format which is transparent to the user).

The tables which store the data are referred to as base tables. Users can directly examine data in the base tables.

In addition, data may be presented using views, which are created from specific parts of one or more base tables or views. To the user, views may look that same as tables, but operations on views are actually performed on the underlying base tables.

Access privileges on views and their underlying base tables are completely independent of each other, so views provide a mechanism for setting up specific access to tables.

The essential difference between a table and a view is underlined by the action of the DROP command, which removes objects from the database. If a table is dropped, all data in the table is lost from the database and can only be recovered by redefining the table and re-entering the data. If a view is dropped, however, the table or tables on which the view is defined remain in the database, and no data is lost.

Data may, however, become inaccessible to a user who was allowed to access the view but who is not permitted to access the underlying base table(s).

Note: Since views are logical representations of tables, all operations requested on a view are actually performed on the underlying base table, so care must be taken when granting access privileges on views.
Such privileges may include the right to insert, update and delete information. As an example, deleting a row from a view will remove the entire row from the underlying base table and this may include table columns the user of the view had no privilege to access.

Restriction Views

Views may be created to simplify presentation of data to the user by including only some of the base table columns in the view or only by including selected rows from the base table. Views of this kind are called restriction views.

For example, a view may be created on the COUNTRIES table to include only the COUNTRY and CURRENCY_CODE columns:

 CREATE TABLE countries (
       code CHARACTER(2) PRIMARY KEY,
       country VARCHAR(48) NOT NULL,
       currency_code CHARACTER(3) NOT NULL);
 
 CREATE VIEW countries_view
    AS SELECT country, currency_code
    FROM countries;
 

COUNTRIES_VIEW
COUNTRY
CURRENCY_CODE
 Andorra
EUR
 United Arab Emirates
AED
 Afghanistan
AFA
 Antigua and Barbuda
XCD
 Anguilla
XCD
 Albania
ALL
 Armenia
AMD
 ...
...

Similarly, a view may be created to include only the rows in COUNTRIES where US dollars are used (CURRENCY_CODE = 'USD').

Join Views

Views may also be created to combine information from several tables - join views.

Join views can be used to present data in more natural or useful combinations than the base tables themselves provide (the optimal design of the base tables will have been governed by rules of relational database modeling).

Join views may also contain restriction conditions.

For example, the join view below presents the countries that use the euro. The CURRENCY_CODE in COUNTRIES_VIEW is linked with the CODE column in the CURRENCIES table and a restriction of CURRENCY = 'Euros' is applied:

 CREATE VIEW euro_countries
    AS SELECT country
    FROM countries_view JOIN currencies
       ON countries_view.currency_code = currencies.code
    WHERE currency = 'Euros';
 

EURO_COUNTRIES
COUNTRY
 Andorra
 Austria
 Belgium
 Finland
 France
 ...

Unique Constraints and Indexes

Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns. A table cannot contain two rows with the same primary key value. (If the primary key contains more than one column, the key value is the combined value of all the columns in the key. Individual columns in the key may contain duplicate values as long as the whole key value is unique).

Other columns may also be defined as UNIQUE. A unique column is also a key, because it may not contain duplicate values, and need not necessarily be part of the primary key.

Primary key and unique columns are automatically indexed to facilitate effective information retrieval.

Other columns or combinations of columns may be defined as a secondary index to improve performance in data retrieval. Secondary indexes are defined on a table after it has been created (using the CREATE INDEX statement).

An example of when a secondary index may be useful is when a search is regularly performed on a non-keyed column in a table with many rows, defining an index on the column may speed up the search. The search result is not affected by the index but the speed of the search is optimized.

It should be noted, however, that indexes create an overhead for update, delete and insert operations because the index must also be updated.

Indexes are internal structures which cannot be explicitly accessed by the user once created. An index will be used if the internal query optimization process determines it will improve the efficiency of a search.

SQL queries are automatically optimized when they are internally prepared for execution. The optimization process determines the most effective way to execute each query, which may or may not involve using an applicable index.

Stored Procedures

In Mimer SQL you can define functions, procedures and modules, collectively known as stored procedures.

Mimer SQL stored procedures enable you to define and use powerful functionality through the creation and execution of routines. By using stored procedures, you can move application logic from the client to the server, thereby reducing network traffic.

Stored procedures are stored in the data dictionary and you can invoke them when needed.

For a complete and detailed discussion of stored procedures, see Mimer SQL Reference Manual, Chapter 7, Functions and the Mimer SQL Programmer's Manual, chapter 12, Mimer SQL Stored Procedures.

Mimer SQL stored procedures are based on the ANSI standard for Persistent Stored Modules (PSM),

Routines - Functions and Procedures

The term routine is a collective term for functions and procedures. Functions are distinguished from procedures in that they return a single value and the parameters of a function are used for input only. A function is invoked by using it where a value expression would normally be used.

Mimer SQL supports standard procedures and also result set procedures, which are procedures capable of returning the row value(s) of a result-set.

Standard procedures are invoked directly by using the CALL statement and can pass values back to the calling environment through the procedure parameters.

In ESQL, result set procedures are invoked by declaring a cursor which includes the procedure call specification and by then using the FETCH statement to execute the procedure and return the row(s) of the result-set.

In interactive SQL, a result set procedure is invoked by using the CALL statement directly and the result-set values are presented in the same way as for a select returning more than one row.

The ident invoking a routine must have EXECUTE rights on it.

The creator of a routine must hold the appropriate access rights on any database objects referenced from within the routine. These access rights must be held for the life of the routine.

Routine names, like those of other private objects in the database, are qualified with the name of the schema to which they belong.

Modules

A module is simply a collection of routines. All the routines in a module are created when the module is created and belong to the same schema.

EXECUTE rights on the routines contained in a module are held on a per-routine basis, not on the module.

If a module is dropped, all the routines contained in the module are dropped.

Under certain circumstances a routine may be dropped because of the cascade effect of dropping some other database object. If such a routine is contained in a module, it is implicitly removed from the module and dropped. The other routines contained in the module remain unaffected.

In general, care should be taken when using DROP or REVOKE in connection with routines, modules or objects referenced from within routines because the cascade effects can often affect many other objects. See Dropping Objects from the Database and Recursive Effects of Revoking Privileges for details.

Triggers

A trigger defines a number of procedural SQL statements that are executed whenever a specified data manipulation statement is executed on the table or view on which the trigger has been created.

The trigger can be set up to execute AFTER, BEFORE or INSTEAD OF the data manipulation statement. Trigger execution can also be made conditional on a search condition specified as part of the trigger.

Triggers are described in detail in the Mimer SQL Programmer's Manual, chapter 13, Triggers.

Synonyms

A synonym is an alternative name for a table, view or another synonym. Synonyms can be created or dropped at any time.

A synonym cannot be created for a function, procedure or a module.

Using synonyms can be a convenient way to address tables that are contained in another schema.

For example, if a view called customer_details is contained in the schema called mimer_store, the full name of the view is mimer_store.customer_details.

This view may be referenced from the schema called mimer_store_book by its fully qualified name as given above.

Alternatively, a synonym may be created for the view in schema mimer_store_book, e.g. cust_details. Then the name cust_details can simply be used to refer to the view mimer_store.customer_details.

Note: The name cust_details is contained in schema mimer_store_book and can only be used in that context.

Shadows

Mimer SQL Shadowing is a separate product you can use to create and maintain one or more copies of a databank on different disks. Shadowing provides extra protection from the consequences of disk crashes, etc. Shadowing requires a separate license.

Read more in the Mimer SQL Systmen Management Handbook, chapter 9, Mimer SQL Shadowing.

Sequences

A sequence is a private database object that can provide a series of integer values. A sequence can be defined as unique or non-unique.

A sequence has an initial value, an increment step value and a maximum value defined when it is created (by using the CREATE SEQUENCE statement).

A unique sequence will generate a series of values that change by the increment value from the initial value to a value that does not exceed the maximum value. A unique sequence never generates the same value twice.

A non-unique sequence generates a series of values by starting at the initial value and proceeding in increment steps. If all values in a non-unique sequence has been exhausted, the sequence will start over again with the initial value.

A sequence is created with an undefined value initially.

It is possible to generate the next value in the integer series of a sequence by using the NEXT_VALUE OF sequence_name construct. This is used for the first time after the sequence has been created to establish the initial value defined for the sequence. Subsequent uses will add the increment step value to the value of the sequence and the result will be established as the current value of the sequence.

It is possible to get the value of a sequence by using the CURRENT_VALUE OF sequence_name construct. This construct cannot be used until the initial value has been established for the sequence (i.e. using it immediately after the sequence has been created will raise an error).

When the current value of a unique sequence is equal to the last value in the series it defines, NEXT_VALUE OF sequence_name will raise an error and the value of the sequence will remain unaltered.

If the sequence is non-unique, NEXT_VALUE OF sequence_name will always succeed. If the current value of the sequence is equal to the last value in the series it defines, the initial value of the sequence will be returned.

The value of CURRENT_VALUE OF sequence_name and NEXT_VALUE OF sequence_name can be used where a value-expression would normally be used. The value may also be used after the DEFAULT clause in the CREATE DOMAIN, CREATE TABLE and ALTER TABLE statements.

An ident must hold USAGE privilege on the sequence in order to use it.

If a sequence is dropped, with the CASCADE option in effect, all object referencing the sequence will also be dropped.

Examples:

A non-unique sequence with initial value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8, 1, 4, 7...

A unique sequence with initial value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8.

Note: It is possible that not every value in the series defined by the sequence will be generated. If a database server crash etc. occurs during the life of a sequence it is possible that some of the values in the series might be skipped.


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX