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, see Data Types in SQL Statements for a detailed description of data types.
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 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
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.
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.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40