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:CREATE TABLE currencies ( code CHARACTER(3) PRIMARY KEY, currency CHARACTER(32) NOT NULL, exchange_rate DECIMAL(12, 4));
Netherlands Antillian Guilders
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 if an explicit value 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 data type with the same maximum length. See the Mimer SQL Reference Manual, 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 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
DROPcommand, 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.
For example, a view may be created on the
COUNTRIEStable to include only the
CURRENCY_CODEcolumns: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;
Similarly, a view may be created to include only the rows in
COUNTRIESwhere US dollars are used (
CURRENCY_CODE = 'USD'):CREATE VIEW usd_countries_view AS SELECT coutry FROM countries WHERE currency_code = 'USD';
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 some kind of dollars. The
COUNTRIES_VIEWis linked with the
CODEcolumn in the
CURRENCIEStable, and a restriction of
'dollar'is applied:CREATE VIEW dollar_countries AS SELECT country, currency FROM countries_view JOIN currencies ON countries_view.currency_code = currencies.code WHERE lower(currency) like '%dollar%';
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40