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_STORE schema 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 BLOBs will be stored is defined with the TRANS option. The databanks have names that start with the prefix MIMER_.
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 NOT NULL.
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 _SEQ suffix 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_STORE schema; these tables provide a reasonably complete set for an introduction to SQL.
The tables CURRENCIES and COUNTRIES are 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.
CURRENCIES 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 NULL in column definitions.Uses the CHARACTER data 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. COUNTRIES 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 CURRENCIES table. 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. CATEGORIES 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. FORMATS Product formats, e.g. Paperback, Audio CD.A domain could/should have been used for the CATEGORY_ID columns in the CATEGORIES and FORMATS table.There are two unique constraints, both defined on two columns.A DISPLAY_ORDER column 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. PRODUCERS 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. PRODUCTS 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. IMAGES 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_BLOBS databank and therefore changes are not logged. ITEMS 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 (in euros) 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 PRODUCER_ID and IMAGE_ID permit 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_ID as the primary key, making the EAN_CODE a candidate key. Obviously the EAN_CODE could 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 CURRENCIES and COUNTRIES tables 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 TABLE statement then uses the PSM routine in a check clause to validate the EAN code. CUSTOMERS Personal details for store customers, whether they are on a mailing list or are Web users.Shows the use of current_date as a default value against the REGISTERED column.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). ORDERS A record of when a customer placed an order. ORDER_ITEMS 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.VALUE is an SQL reserved word; the definition shows how to force the use of a reserved word. STOP_WORDS Contains the 100 most common words in the English language.
Note that the CUSTOMERS, ORDERS and ORDER_ITEMS tables are grouped together in the MIMER_ORDERS databank.
It could be argued that the ITEMS table is not fully normalized - the PRODUCER_ID is 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.
The MIMER_STORE schema 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.
AGE_OF_ADULT 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. CAPITALIZE Function that processes a string making the first character of a word uppercase and the remainder of the word lowercase. CAST_TO_DATE 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. EAN_CHECK_DIGIT Function that returns the check digit for an EAN. EXTRACT_DATETIME 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'. EXTRACT_DATE Function to show how EXTRACT_DATETIME is used. INDEX_TEXT Function to process a character string, removing all punctuation characters. Also removes any of the words that are held in the table STOP_WORDS by using the exists predicate. PRODUCT_SEARCH_CODE Function to return the Soundex value of a character string after it has been processed by the INDEX_TEXT function. RECIPIENT 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. SALUTATION Function to create a formal or informal salutation based on the age of the person.Performs simple date arithmetic using the AGE_OF_ADULT function. STOP_WORDS Database load procedure to extract the words from a character string and insert them into the STOP_WORDS table. VALIDATE_EAN_CODE Function to return the EAN code with the correct check digit.Example of large number arithmetic.
The ITEMS table is altered to include a check clause using the VALIDATE_EAN_CODE function 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