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:

 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 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 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'):

 CREATE VIEW usd_countries_view
     AS SELECT coutry
     FROM countries
     WHERE 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 some kind of dollars. The CURRENCY_CODE in COUNTRIES_VIEW is linked with the CODE column in the CURRENCIES table, and a restriction of CURRENCY includes '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%';
 

dollar_countries

country	currency
American Samoa	US Dollars
Anguilla	East Caribbean Dollars
Antigua and Barbuda	East Caribbean Dollars
Australia	Australian Dollars
Bahamas	Bahamian Dollars
...	...

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se