Introduction to database objects in Mimer SQL
This paper provides a general introduction to the basic concepts of Mimer SQL databases and Mimer SQL objects.
For details on the descriptions given herein, e.g. for complete command references, please use the Mimer SQL Documentation Set that is available using html navigation, or as a PDF-file.
Mimer SQL is a relational database system. This means that the information in the database is handled as 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. Relational database system also include support for a number of data integrity facilities, which can be used to maintain consistency and correctness of the data in the database.
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.
The Mimer SQL database includes a 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 data dictionary contains information on both database objects and privileges.
Mimer SQL database objects can be divided into the following groups; Private objects and System objects.
Private objects belong to a schema and their names are local to a schema. So, two different schemas may contain an object with the same name.
Private objects are fully identified by their qualified name, which is the name of the schema to which they belong and the name of the object in the following form: schema.object. Conflicts arising from the use of the same object name in two different schemas are avoided when the qualified name is used. If a private object name is specified without explicit reference to its schema, it is assumed to belong to a schema with the same name as the current ident.
The private objects in a Mimer SQL database are:
System objects are global to the database and their names must be unique for each object type since they are global and therefore common to all users.
A system object is owned by the ident that created it and only the creator of the object can drop it.
The system objects in a Mimer SQL database are:
A vital aspect of a Mimer SQL database is data integrity. Data integrity means that the data in the database is complete and consistent both at its creation and at all times during use.
Mimer SQL has five built-in facilities that ensure the data integrity in the database:
These features should be used whenever possible to protect the integrity of the database, guaranteeing that incorrect or inconsistent data is not entered into it. By applying data integrity constraints through the database management system, the responsibility of ensuring the data integrity of the database is moved from the users of the database to the database designer.
Privileges control users access to database objects and the operations they can perform in the database.
USER and PROGRAM idents are protected by a password, which must be given together with the correct ident name in order for a user to gain access to the database or to enter a program ident.
Passwords are stored in encrypted form in the data dictionary and cannot be read by any ident, including the system administrator. A password may only be changed by the ident to which it belongs or by the creator of the ident.
A set privileges define the operations each ident is permitted to perform. There are three classes of privileges in a Mimer SQL database:
- access privileges
- object privileges
- system privileges
Access privileges are used to define access to the contents of the database, i.e. the rights to retrieve data from tables or views, delete data, insert new rows, update data and to refer to table columns as foreign key references.
Object privileges are used to control membership in group idents, the right to invoke functions and procedures, the right to enter program idents, the right to create new tables in a specified databank and the right to use a domain or sequence.
The creator of an object is automatically granted full privileges on that object, which implies the following:
The creator of...
- a group is automatically a member of the group
- a function or procedure may execute the function or procedure
- a program ident may enter the program ident
- a schema may create objects in and drop objects from the schema
- a databank may create tables in the databank
- a table has all access rights on the table
- a domain may use that domain
- a sequence may use that sequence
The creator of an object generally has the right to grant any of these privileges to other users.In the case of functions and procedures, this actually depends on the creator's access rights on objects referenced from within the routine.
System privileges are used to control the right to perform backup and restore operations, the right to execute the UPDATE STATISTICS statement as well as the right to create new databanks, idents, schemas and to manage shadows.
System privileges are granted to the system administrator when the system is installed and may be granted by the administrator to other idents in the database. As a general rule, system privileges should be granted to a restricted group of users.
Note! An ident who is given the privilege to create new idents is also able to create new schemas.
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.
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 has been defined for a column, the NULL value is stored when no data value is supplied (the way the NULL value is displayed depends on the application in Mimer BSQL the minus sign is used).
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 type of information and are of the same physical length. This length and type of information is defined by a data type.
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 in views, which are created from specific parts of one or more base tables. To the user, views may look the 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.
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.
Views may also be created to combine information from several tables, so called 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.
A columns or a combination 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).
A secondary index may be useful when, for example, a search is regularly performed on a non-keyed column in a table with many rows, then 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 is not necessary to create secondary indexes for primary keys, foreign keys or unique constraints. Primary keys are effectively and more efficiently indexed by the physical tables themselves. Secondary indexes for foreign key constraints and unique constraints are created automatically.
It should also be noted, however, that indexes create an overhead for update, delete and insert operations because both table and indexes must be updated. Indexes are internal structures which cannot be explicitly accessed by the user once created.
There is no guarantee that the presence of an index will actually improve performance because the decision to use it or not is made by the internal query optimization process. SQL queries are automatically optimized when they are prepared for execution. The optimization process determines the most effective way to execute the query and in some cases optimal query execution may not actually involve using an index.
A synonym is an alternative name for a table, view or another synonym. Synonyms cannot be created for functions, procedures or modules.
Synonyms can be created or dropped at any time.
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 that in this case the name cust_details is contained in schema mimer_store_book and can only be used in that context.
A databank is the physical file where tables are stored. A Mimer SQL database can contain any number of databanks. There are two types of databank, system and user.
System databanks contain system information used by the database manager. These databanks are defined when the system is created. The system databanks are:
- SYSDB, containing the data dictionary tables
- TRANSDB, used for transaction handling
- LOGDB, used for transaction logging
- SQLDB, used for temporary storage of internal work tables.
User databanks contain the user tables. These databanks are defined by the user(s) responsible for setting up the database. 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.
The Shadowing functionality is used to create and maintain one or more copies of a databank, so called shadows, on different disks. Shadowing provides extra protection from the consequences of disk crashes, etc.
Shadowing requires a separate license.
An ident is an authorization-id used to identify users. There are four types of idents in a Mimer SQL database: USER, OS_USER, PROGRAM and GROUP idents.
- USER idents identify individual users who can connect to a Mimer SQL database. A USER ident'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 allow a user currently logged in to the operating system to access the Mimer SQL database without providing a username 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 the Mimer BSQL program (for example) and connect directly to Mimer SQL simply by pressing 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 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 ENTER statement has been used.
PROGRAM idents are generally associated with specific functions within the system, rather than with physical individuals.
The LEAVE statement is used to return to the state of privileges and database access that existed before ENTER was used.
- GROUP idents are collective identities used to define groups of USER, OS_USER and/or PROGRAM idents.
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.
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.
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 created automatically 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.
A precompiled statement, or executable statement, is a named query that can be executed by using this name. The query must be a DML-statement, i.e. DELETE, INSERT, SELECT or UPDATE, or a SET or CALL statement.
When the statement is created a compiled version of the query is stored in the data dictionary.
Executable statements are primarily intended for use in mobile and embedded environments in which no SQL compiler is available due to limited memory resources.
Functions and Procedures
In Mimer SQL you can define functions, procedures and modules (see the next section, Modules), which are 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.
Mimer SQL stored procedures are based on the ISO standard for Persistent Stored Modules (PSM).
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 embedded SQL, 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.
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.
Note! 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.
A trigger defines a procedural SQL statement that is 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.
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 unique sequence generate unique values. If all values between the initial value and the maximum value has been used, the sequence becomes exhausted and can not be used any more.
A non-unique sequence will generate its series of values repeatedly.
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 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 function. When this function is used for the first time after the sequence has been created, it establishes the initial value for the sequence. Subsequent uses will establish the next value in the series of integer values of the sequence as the current value of the sequence.
It is possible to get the current value of a sequence that has been initialized by using the CURRENT_VALUE function. This function cannot be used until the initial value has been established for the sequence (by using NEXT_VALUE for the first time). Th current value for a sequence is maintained per connection.
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 objects referencing the sequence will also be dropped.
A collation, also known as a collating sequence, is a database object containing a set of rules that determines how character strings are compared, searched and alphabetically sorted. The rules in the collation determine whether one character string is less than, equal to or greater than another. A collation also determines how case-sensitivity and accents are handled.
You can specify a different collation for ordering characters when you create or alter a table or create a domain. If you have specified a collation for a column, the collation is used implicitly in SQL statements.
You only need to explicitly use a collation clause in SQL statements if you want to override the default collation or the collation you specified when creating or altering the table or creating the domain.
The default collation for a character string depends on the data type and the corresponding character set.
For character data, Mimer SQL uses the character set ISO 8859-1, which is referred to as ISO8BIT in SQL. This character set is also known as the Latin-1 character set. By default, character data is sorted in the numerical order of the code values according to the ISO 8859-1 character set. This collation is named ISO8BIT in SQL, just as the character set.
For national character data, Mimer SQL uses the Unicode character set, see www.unicode.org for more information. The character set UNICODE is referred to as UTF32 in SQL. The default collation for national character data is called UCS_BASIC in SQL. UCS_BASIC is a collation in which the ordering is determined entirely by the Unicode scalar values of the characters in the strings being sorted.
Mimer SQL has built-in collations for sorting in many languages, including over 50 different European languages. For a complete list of supported languages, please see the Collation Charts page. Most languages have built-in support and the remaining can easily be added to your running Mimer SQL system whenever needed with a simple CREATE COLLATION statement in SQL.
For further details, please see the article Character data, Unicode and Collations.
Primary Key and Unique Constraints
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.
The columns of the primary key may not contain NULL (this is one of the requirements of a strictly relational database), but it is allowed for columns of a unique constraint.
Values in primary key columns can be updated if the table involved is stored in a databank with the TRANS or LOG option.
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).
A secondary index may be useful when, for example, a search is regularly performed on a non-keyed column in a table with many rows, then 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.
There is no guarantee that the presence of an index will actually improve performance because the decision to use it or not is made by the internal query optimization process. SQL queries are automatically optimized when they are internally prepared for execution. The optimization process determines the most effective way to execute the query and in some cases optimal query execution may not actually involve using an index.
Foreign Key Constraints
A foreign key is one or more columns in a table defined as cross-referencing the primary key or a unique key of another table.
Data entered into the foreign key must either exist in the key that it cross-references or be NULL. This maintains referential integrity in the database, ensuring that a table can only contain data that already exists in the selected key of the referenced table.
As a consequence of this, a key value that is cross-referenced by a foreign key of another table must not be removed from the table to which it belongs by an update or delete operation if this ultimately violates the referential constraint.
The DELETE rule defined for the referential constraint provides a mechanism for adjusting the values in a foreign key in a way that may permit a cross-referenced key value to effectively be removed.
Note! The referential integrity constraints are effectively checked at the end of an INSERT, DELETE or UPDATE statement.
Foreign key relationships are defined when a table is created using the CREATE TABLE statement and can be added to an existing table by using theALTER TABLE statement.
The cross-referenced table must exist prior to the declaration of foreign keys on that table, unless the cross-referenced and referencing tables are the same.
If foreign key relationships are defined for tables in a CREATE SCHEMA statement, it is possible to reference a table that will not be created until later in the CREATE SCHEMA statement.
Note! Both the table containing the foreign key and the cross-referenced table must be stored in a databank with either the TRANS or LOG option.
Check constraints may be specified in table and domain definitions to make sure that the values in a column conform to certain conditions.
Each column in a table holds data of a single data type and length, specified when the column is created or altered. The data type and length may be specified explicitly, e.g. CHARACTER(20) or INTEGER(5), or through the use of domains, which can give more uniform control over the data that will be accepted in the column.
A domain definition consists of a data type and length specification with optional check conditions and default value. Data which falls outside the constraints defined by the check conditions is not accepted in a column which is defined using the domain.
A column defined using a domain for which a default value is defined will automatically receive that value if row data is entered without a value being explicitly specified for the column.
In order for an ident to create a table containing columns whose data type is defined through the use of a domain, the ident must first have been granted USAGE rights on the domain.
Check Options in View Definitions
You can maintain view integrity by including a check option in the view definition. This causes data entered through the view to be checked against the view definition. If the data conflicts with the conditions in the view definition, it is rejected.
For details and further information on Mimer SQL use the Mimer SQL Documentation Set; html navigation, or PDF-file.
To continue from here, an example database is delivered with Mimer SQL, see The Example Database for a description.
Last updated: 2008-10-27