The MIMER_STORE Schema
In this base schema most of the objects supported by Mimer SQL are introduced: collations, databanks, domains, idents, sequences, synonyms, tables and views. They are used to demonstrate some of the functionality that is available - constraints; primary, unique and foreign keys; secondary indices;
NOT NULL; default values; and column and table check clauses. The schema also includes examples of PSM routines and triggers.
When an ident, other than a
GROUP, is created, a schema of the same name is created by default. Objects in the
MIMER_STOREschema have to be created before other objects can reference them.
A databank is the physical file where a collection of tables is stored. There are three databanks defined in the example environment; the databank where the tables containing
BLOBswill be stored is defined with the
TRANSACTIONoption. The databanks have names that start with the prefix
There are a number of domains defined. In essence, a domain is a column data type specification that can be used in a number of table column definitions.
A number of different data types are introduced. The domains introduce the idea of consistency in the table definitions and demonstrate how to use check clauses, including the equivalent of
A sequence is an object that can provide a unique integer value. The sequences start with initial values other than the default to give the various sequences different number ranges; this is sometimes helpful when looking at data. The sequence names have a
_SEQsuffix to help to clarify when they are being used.
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.
A relational database is built up of several inter-dependent tables that can be joined together. Tables are joined by using related values that appear in one or more columns in each of the tables.
All the foundation tables are created under the
MIMER_STOREschema; these tables provide a reasonably complete set for an introduction to SQL.
COUNTRIESare used in many of the basic examples in this manual and contain entries for almost all countries and currencies, providing a moderate amount of data for any exercise.
Holds currency details.Introduces the use of domains (qualified with the schema name) and the decimal data type. Demonstrates that column constraints can include the primary key definition. Introduces the concept of
NOT NULLin column definitions.Uses the
CHARACTERdata type to show the difference compared to
CHARACTER VARYING.The table is created with the schema name explicitly included, after this example the default option is used.Note the table is not defined in a specific databank, the system determines which databank to use; all other tables are created in a named databank.Examples of creating comments on the table and columns follow this table creation; these are the only examples of comments.
Holds country details.Introduces the concept of naming constraints; as when naming objects, a consistent approach will give benefits. Includes a unique constraint.Introduces a foreign key definition linking the currency code to an entry in the
CURRENCIEStable. The column in the referenced table (
CURRENCIES) is explicitly named; in future this is only done if the columns are not defined as the primary key.
Product categories, i.e. Music and Books. A category of `Video' is included for course work.Shows the use of a collation in a column definition. A collation is a set of rules that determines how data is sorted and compared. Character data is sorted using collations that define the correct character sequence, with the capability to handle case-sensitivity and accents.In this table create statement the primary, unique and foreign keys are defined as table constraints. This is the style that has been adopted in the rest of the schema, the previous examples were to show the syntax possibilities; again a consistent approach will make it easier for others.
Product formats, e.g. Paperback, Audio CD.A domain could/should have been used for the
CATEGORY_IDcolumns in the
FORMATStable.There are two unique constraints, both defined on two columns.A
DISPLAY_ORDERcolumn has been included; the idea is that this can be used to display the different formats for a particular product in an order other than alphabetic.
Name of the record label or book publisher i.e. the organization that made the product.Shows the use of a column default value that refers to a sequence.
Name of the product, i.e. name of the album or title of the book. It may be that the same product name is the title of a book and an album.Until this point these referenced tables had been defined with the primary key as the identifier and the associated `name' as a unique key; this demonstrates the reverse.This table holds a Soundex value of the name. As we shall see later the name is processed before the Soundex value is taken. A secondary index is defined on this Soundex value.
An item may have an image associated with it; for example the image could be an album or book jacket cover.Note that this table is in the
MIMER_BLOBSdatabank and therefore changes are not logged.
This forms a link between a product name and the different formats in which it is available. For example an album may appear in a number of different formats: Audio CD, Cassette, DVD Audio and Vinyl.The table contains a number of attributes for the item, such as price and stock level. There is also a reorder level but that is an extension for the classroom (i.e. a trigger can be used to place an order).This table references a number of other tables and introduces the ability to specify what action to take when a referenced row is deleted. Both the
IMAGE_IDpermit a null value, which allows outer joins to be demonstrated.A date data type is introduced in
RELEASE_DATE. A secondary index is created on the date column allowing examples of how date ranges can be used.The European Article Numbering (EAN) code is a useful example; it is the barcode that appears on everything these days. It is a unique key in its own right but the table has
ITEM_IDas the primary key, making the
EAN_CODEa candidate key. Obviously the
EAN_CODEcould have been used in referencing tables but in general it is a mistake to use external identifiers to join tables; this conflicts with the use of the International codes in the
COUNTRIEStables but the data in these tables can be considered to be static.A property of an EAN code is that it is a 13-digit number that incorporates a check digit. A PSM function to validate the check digit is included later in this schema; an
ALTER TABLEstatement then uses the PSM routine in a check clause to validate the EAN code.
Personal details for store customers, whether they are on a mailing list or are Web users.Shows the use of
current_dateas a default value against the
REGISTEREDcolumn.The idea is that when customers order through the Web-site they will be prompted for their e-mail address and password; the table check clause makes sure that the e-mail and password are either both defined or neither is defined.Secondary indices are defined on the date of birth and post code columns, these are both useful when trying to identify a customer (e.g. when there are too many John Smiths to search through by name).
A record of when a customer placed an order.
What items went into an order and the price at the time that the order was placed.This is the first time that a multi-column primary key is used.
VALUEis an SQL reserved word; the definition shows how to force the use of a reserved word.
Contains the 100 most common words in the English language.
Note that the
ORDER_ITEMStables are grouped together in the
It could be argued that the
ITEMStable is not fully normalized - the
PRODUCER_IDis repeated for each different format. But if you think about it you will realize that there is no benefit in introducing another table, only a penalty.
MIMER_STOREschema also introduces some fairly simple PSM routines (defined in a module named
ROUTINES). These routines provide a basic introduction to functions and procedures; they demonstrate the general syntax of a number of PSM statements, including deterministic and access options.
Routines defined in a module cannot be modified without dropping the module, in which case all the routines in the module are dropped.
Note: The use of the `@' character which is used in BSQL to delimit SQL statements whose syntax involves use of the normal end-of-statement character `;' before the actual end of the statement. The `@' character may be used to delimit any statement; this is useful when dealing with large statements as the error reporting facility in BSQL shows more information in such cases.
You can debug PSM routines using Mimer SQL's Java-based graphic debugger. The debugger has support for watching variables, step-wise execution and setting breakpoints. You can debug procedures, functions and triggers.
Procedure that returns the age that a person is considered to be an adult.A slightly contrived procedure to introduce the in and out parameter types; also includes an interval data type and a case statement.
Function that processes a string making the first character of a word uppercase and the remainder of the word lowercase.
Function that takes a character string (
dd/mm/yy) and converts it to a date data type.Note the use of the like comparison.Introduce the concept of signaling, but this can be skipped over at this point.
Function that returns the check digit for an EAN.
Function to return just the variable component from a datetime or interval value. The value has to be cast to a character string in the call.Uses `
is not null'.
Function to show how
Function to process a character string, removing all punctuation characters. Also removes any of the words that are held in the table
STOP_WORDSby using the exists predicate.
Function to return the Soundex value of a character string after it has been processed by the
Function to form the recipient details, e.g. for a letter address - Mr J Smith.Note that the use of `
is not null' is redundant.
Function to create a formal or informal salutation based on the age of the person.Performs simple date arithmetic using the
Database load procedure to extract the words from a character string and insert them into the
Function to return the EAN code with the correct check digit.Example of large number arithmetic.
ITEMStable is altered to include a check clause using the
VALIDATE_EAN_CODEfunction to ensure that only valid EAN codes can be entered.
After the definition of the views (see next section for details) there is another PSM routine, it shows how to create a standalone procedure.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40