Introduction
The Example Database described in this document is optionally created during the Mimer SQL installation process, or by executing the exload program:
exload
The main objective of the Example Database is to provide a teaching aid, both in the classroom and as the basis for examples in the manuals. As such it is not a total solution, although most of the data definition (DDL) statements are complete; it is intended that course work will add functionality. The PSM routines incorporate examples of search techniques that, hopefully, will give the student/reader ideas on options when performing searches on large databases.
This document reflects the order of the SQL statements that create the Example Database and explains and highlights some of the functionality included. The SQL statements to create the Example Database are found in the files called exdef.sql and exdat.sql, where a number of design decisions are made that are intended to lead to further course discussion. Perhaps the most contentious part of the design is the separation of various areas into different schema, this is done partly to illustrate what is possible although the schemas are also used to include additional levels of SQL functionality.
For a basic description of concepts and objects in Mimer SQL, please refer to the Introduction to database concepts in Mimer SQL.
Description
The Example Database is based on a store that sells music and books. An item can be available in various formats, for example a music album may be available in Audio CD, Cassette, DVD Audio, and Vinyl. The store also has a web site, through which users can place orders; the web site does not sell music on Vinyl – too many breakages in the post. SQL stored procedures (SQL/PSM) support various aspects of the business logic.
Ownership
The complete database is owned by a user ident, named MIMER_STORE. Alternatively, the owner could have been a program ident but as this is a Mimer SQL extension it can cause problems with third-party tools. Applications should not use the MIMER_STORE ident, all access should be provided through privileges.
The MIMER_STORE ident is granted the privilege to create databanks and idents.
Underneath MIMER_STORE there are three schemas, each one related to a specific area;
- MIMER_STORE_MUSIC – holds objects related to the Music Department of the shop,
- MIMER_STORE_BOOK – holds objects related to the Literature Department of the shop,
- and MIMER_STORE_WEB – holds objects related to the Web store.
It is possible to ignore the schemas other than MIMER_STORE; they can be viewed as a black box for the purposes of an introduction to SQL.
Idents
Apart from the MIMER_STORE ident there are three different idents to access the Example Database from, each one of them with different tasks to perform;
- MIMER_ADM – the administrator of the system,
- MIMER_WEB – the user for web-application access,
- and MIMER_USR – the ordinary user of the system.
Default passwords are used if not explicitly defined during the installation of the Example Database.
Naming
As it is likely that the Example Database will be installed on a Mimer SQL system where there are other existing schemas, the naming for the schemas are based around a MIMER_STORE prefix. This is unlikely to conflict with any existing schemas, but it does lead to schema names of the form MIMER_STORE_MUSIC; in a real environment this would probably be just MUSIC.
When naming objects, particularly columns, it is important to adopt a naming convention. There is no right convention but a consistent approach to naming will pay dividends in the long run. The columns used in table joins should have matching names, it makes life so much easier when writing join conditions. The view CUSTOMER_ADDRESSES shows the additional complexity to a join if column names are not kept consistent. Example Databank internal key columns have a _ID suffix; the exceptions to this are the CODE columns in the CURRENCIES and COUNTRIES tables (these hold international recognized codes). Columns not used in joins tend to have unique names, this has major benefits when typing select-lists; it is also useful in the examples as there is no confusion over which table a column belongs to.
The MIMER_STORE Schema
In the base schema MIMER_STORE 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.
There are three databanks defined in the Example Database; the databank where the 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 and 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.
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.
Note! The use of the ‘@’ character which is used in the BSQL program 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.
Some sequences are also introduced. They 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.
Tables within MIMER_STORE
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 the documents found at Mimer SQL Documentation page; these tables contain entries for almost all countries and currencies, providing a reasonable amount of data for any exercise.
The following picture gives an overview of the tables in the MIMER_STORE schema, including columns and foreign key relations:
Below are details for the tables introduced above:
- 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. No data has currently been loaded. 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 postcode 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.
PSM routines within MIMER_STORE
There are a number of PSM routines in the MIMER_STORE schema to show various aspects of the functionality that can be supported.
- 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.
- COMING_SOON
Result set procedure that returns the products for a particular category that will be released in the next month. Makes use of the PRODUCT_DETAILS view to demonstrate that a select against a view is treated the same as a table. Shows how to define a result set procedure and one form of the row data type. Introduces the concepts of cursors. The use of ‘get diagnostics’ avoids any real need to cover condition handlers at this stage.
In addition, the base schema MIMER_STORE includes two PSM routines that are outside the scope of an introduction to SQL.
- ORDER_ITEM
Procedure to associate an order for a quantity of a particular item against an order identifier. - BARCODE
Result set procedure that returns the book or music details fora given EAN. The intention would be to use this with a POS (Point of Sale) barcode reader.
Views within MIMER_STORE
There are a number of views in the MIMER_STORE schema to show various aspects of the functionality that can be supported.
- CUSTOMER_DETAILS
Based on the single table CUSTOMERS. Shows the simplest type of view – updateable if the user has the privilege. Allows a new customer to be added to the database. - SWEDISH_CUSTOMERS
Based on the previous view (i.e. views can be defined on views) but with a selection condition so that only Swedish customers are displayed. Uses a ‘with check option’ so that only Swedish customers can be operated on through this view. - CUSTOMER_ADDRESSES
Again based on the CUSTOMER_DETAILS view but also joined to the COUNTRIES table. Shows how PSM functions can be used from standard SQL statements, including applying predefined functions on the result. Demonstrates how to name the columns in a view definition. Introduces the coalesce expression. The join is slightly more complicated than it needs to be because the column names are not consistent. - PRODUCT_DETAILS
Forms a view across a number of tables, including an outer join on the PRODUCERS table. Note that the coalesce expression in the select list is named; all calculated columns in a view have to be named.
Triggers within MIMER_STORE
There are examples of two very simple statement triggers in the MIMER_STORE schema, both defined against the PRODUCTS table. They are designed to ensure that the value of the PRODUCT_SEARCH column is based on the product name. The insert trigger unconditionally performs an update of the PRODUCT_SEARCH column (setting it to the default value), which will force the update trigger to fire.
The update trigger checks whether the value in the PRODUCT_SEARCH column is equal to the value returned by the PRODUCT_SEARCH_CODE function when applied to the product name. If the value of PRODUCT_SEARCH is inconsistent with the product name then an update is performed; this will cause a recursive call of the update trigger. Note that an update statement causes the trigger to fire even when no rows are updated, hence the use of ‘if exists’.
The PRODUCT_SEARCH_CODE function returns the Soundex value of the product name after it has been processed by the INDEX_TEXT function, which basically involves stripping out any of the words that appear in the STOP_WORDS table (i.e. the 100 most common words in the English language). As an example, the album title ‘Same As It Ever Was’ is reduced to ‘Same Ever’ by the INDEX_TEXT function and the Soundex value returned by the PRODUCT_SEARCH_CODE function would then be ‘467900’. By using this function against a user input string it is possible to allow a limited level of fuzzy matching.
Idents within MIMER_STORE
There are a number of idents, both users and groups. All ident names have a MIMER_ prefix and group idents have a _GROUP suffix to aid with their identification.
The user ident MIMER_ADM can be thought of as an administrator, which is the reason why the user has been given the system privilege to create new idents and tables. Access and object privileges are not granted directly to the user but inherited through membership of MIMER_ADMIN_GROUP. This group of idents has a high level of privileges to manipulate the database tables and views but they do not have total uncontrolled access, only the MIMER_STORE ident has that. It should be possible to perform a lot of the course work from the MIMER_ADM ident.
The user MIMER_USR is granted membership of the MIMER_STORE_GROUP. As can be seen it is possible to grant a group membership of another group; in this case, the members of the MIMER_ADMIN_GROUP (now and in the future) are automatically granted whatever privileges are allocated to MIMER_STORE_GROUP. So when the execute privilege on the PSM procedure COMING_SOON is allocated to MIMER_STORE_GROUP it is automatically also granted to the members of MIMER_ADMIN_GROUP, which includes the user MIMER_ADM.
To simplify access when training, a complete set of synonyms are created for all tables and views against the MIMER_ADM user at the end of the SQL ‘script’.
The MIMER_STORE_MUSIC Schema
Schemas have been used to group objects related to a specific area and MIMER_STORE_MUSIC holds music related objects.
A CREATE SCHEMA statement is used to demonstrate that, within the statement, an object doesn’t have to be created before it can be referenced.
The DURATION domain introduces the INTERVAL data type.
Note that the CREATE SCHEMA statement includes grant object privilege statements as well as object definitions.
Tables within MIMER_STORE_MUSIC
The following tables are added for MIMER_STORE_MUSIC:
- ARTISTS
Holds artist names (e.g. Bruce Springsteen) with a Soundex value based on the name. Shows that objects in schema other than the current have to be qualified with the owning schema name. - TITLES
Links an artist name with an item. - TRACKS
Holds track details, e.g. title, length. - SAMPLES
Holds samples from the tracks. Again, not used at the moment.
Synonyms are created by MIMER_STORE for all tables and views in the MIMER_STORE_MUSIC schema. The synonym are created with a MUSIC_ prefix for the DETAILS, SEARCH and TITLES tables/views.
Views within MIMER_STORE_MUSIC
The following views are added for MIMER_STORE_MUSIC:
- DETAILS
This view includes the PRODUCT_DETAILS view and demonstrates a number of things:- Tables can be included from more than one schema.
- The use of a correlation name in the PRODUCT_DETAILS table reference.
- Restriction conditions can be applied so that different users would see different results, in this case the ident MIMER_WEB would not see Vinyl albums.
- One of the quirks in SQL – ITEM_ID has to be explicitly included in the select-list.
- SEARCH
This view is based on DETAILS but includes additional selection restrictions. Note that the AS clause is noise and may be omitted but it does add clarity. This view demonstrates both forms.
PSM routines within MIMER_STORE_MUSIC
The names in the PSM routines for this schema have been capitalized rather than having the parts separated by an underscore.
- AddTitle
Procedure to insert the base details for an album into the database, updating a number of tables. There are a number of checks on the input, for instance that the format is valid for the Music category and that the label exists in the PRODUCERS table – these introduce exception handlers. Uses another form of the row data type. Demonstrates the use of ‘current_value’ of a sequence. - AddTrack
Procedure to insert the track details for an EAN code. Demonstrates the use of SQLSTATE values in an exception handler. Introduces the ‘resignal’ statement and shows another option with ‘get diagnostics’. - ArtistName
Function to remove any leading definite or indefinite articles from a name. - ArtistSearchCode
Function to return the Soundex value of a character string after it has been processed by the ArtistName function. - Search
Result set procedure that searches the Music ‘database’ for matches based on the supplied title and artist details; a third parameter specifies the maximum number of rows to be returned (a value of zero suppresses this feature). Makes use of the SEARCH view. Includes the use of the DISPLAY_ORDER column in the FORMATS table to present the data in an order other than alphabetic. Each row is given a star rating that indicates the level of match (‘****’ = exact). The artist ‘Bruce Springsteen’ provides a number of matches. This procedure demonstrates the level of functionality that can be stored in the database. - TitleDetails
Result set procedure that returns music details for a given item identifier. Given the result from a search this would allow the user to ‘drill down’ into the displayed information. Illustrates how the compound statement label can be used to qualify a variable name. Shows that interval arithmetic can be performed. - TrackDetails
Result set procedure that returns any track details for a given item identifier. Usage is the same as for TitleDetails. Demonstrates the use of a user defined SQLSTATE.
Execute privilege on the PSM routines Search, TitleDetails and TrackDetails is granted to MIMER_STORE_GROUP.
Triggers within MIMER_STORE_MUSIC
There are two statement triggers in the MIMER_STORE_MUSIC schema, both defined against the ARTISTS table. They are designed to ensure that the value of the ARTIST_SEARCH column is based on the artist name. The same technique as used in the MIMER_STORE schema to force an unconditional update, is applied in the insert trigger.
The update trigger is written to show that it can contain the same level of functionality as any PSM routine; in this case it uses a cursor to process the updates.
The MIMER_STORE_BOOK Schema
This schema contains a table named TITLES, as does the MIMER_STORE_MUSIC schema.
Tables within MIMER_STORE_BOOK
The following tables are added for MIMER_STORE_BOOK:
- TITLES
Links an item with a list of authors and an ISBN. A book may have more than one author, the names are held as a list in the column AUTHORS_LIST in the form: surname, forenames; surname, forenames; … - AUTHORS
Links an item to an entry in the KEYWORDS table. - KEYWORDS
Holds each author in the form surname,[first initial]. An extension would be to categorize books and create a new table to form a link between an item and various categories, with the category being held in the KEYWORDS table.
Object privileges are granted on the MIMER_STORE_BOOK schema tables to MIMER_ADMIN_GROUP.
Synonyms are created by MIMER_STORE for all tables in the MIMER_STORE_BOOK schema. The synonyms are created with a BOOK_ prefix for the DETAILS view and TITLES table.
PSM routines within MIMER_STORE_BOOK
The following PSM routines are added for MIMER_STORE_BOOK:
- VALIDATE_ISBN
Procedure to validate an ISBN. Uses an INOUT parameter. Shows how to CAST to an INTEGER and trap any error to validate that a string is numeric. - FORMAT_ISBN
Function to format an ISBN (e.g. insert hyphens to separate the country, group, publisher, title identifiers). Demonstrates the use of CASE statements, including where there is not an ELSE. Uses a row data type to simplify coding. - AUTHORS
Function to return the first author from a list of authors; if there is more than one author then a mark of omission is included. - AUTHORS_NAME
Function to format an author’s name into surname[,initial]. - KEYWORD_ID
Function to insert a word into the KEYWORDS table and return the identifier with which the keyword is associated. - CATALOGUE_AUTHORS
Given the list of authors associated with a book, extracts each author, calls the AUTHORS_NAME function and then the KEYWORD_ID function. Finally creates a link between each name and the book in the AUTHORS table. - ADD_TITLE
Procedure to insert the base details for a book into the database, updating a number of tables. Inserts against the DETAILS view. - SEARCH
Result set procedure that searches the Book ‘database’ for matches based on the supplied title and author (surname, forename). The author ‘Christie, Agatha’ will provide a number of matches. Demonstrates a different method of searching. - TITLE_DETAILS
Result set procedure that returns book details for a given item identifier. Given the result from a search this would allow the user to ‘drill down’ into the displayed information.
Execute privilege on the PSM routines SEARCH and TITLE_DETAILS is granted to MIMER_STORE_GROUP.
Views within MIMER_STORE_BOOK
The following view is added for MIMER_STORE_BOOK:
- DETAILS
This view includes the PRODUCT_DETAILS view and is the equivalent of the DETAILS view in the MIMER_STORE_MUSIC schema.
Access privileges on the view DETAILS are granted to MIMER_ADMIN_GROUP.
Triggers with MIMER_STORE_BOOK
There are two statement triggers defined against the TITLES table. They are designed to maintain the entries in the AUTHORS and KEYWORDS tables. The update trigger is written to show how to use the OLD and NEW table aliases.
There is also an INSTEAD OF trigger defined on the DETAILS view. This is used by the ADD_TITLE procedure to update the underlying tables on which the view is based. Note that the INSTEAD OF trigger has to be defined before an insert statement against the view can be included, otherwise the join is not considered to be updateable.
The MIMER_STORE_WEB Schema
This schema provides some of the SQL functionality that would be required to create a web-based application to order items.
The ident name in the AUTHORIZATION clause is currently restricted to be the name of the current ident.
Tables within MIMER_STORE_WEB
The following table is added for MIMER_STORE_WEB:
- SESSIONS
Maps an external session identifier with an internal order identifier. Keeps track of the date/time that the ‘basket’ was last accessed.
A synonym is created by MIMER_STORE for the SESSIONS tables in the MIMER_STORE_WEB schema.
PSM routines within MIMER_STORE_WEB
The following PSM routines are added for MIMER_STORE_WEB:
- SESSION_EXPIRATION_PERIOD
Function that returns an interval data type that defines the period in which a basket should be accessed. - DELETE_BASKET
Procedure to delete a specified basket session; alternatively a session of ‘*’ will delete all ‘baskets’ that have expired. Note that the procedure deletes entries in the ORDERS table and relies on foreign key definitions in referencing tables to tidy up. - ORDER_ID
Function to return the order identifier associated with a specified session identifier. Raises an exception if the ‘basket’ hasn’t been used within the period specified by SESSION_EXPIRATION_PERIOD. - VALIDATE_BASKET
Function that uses the ORDER_ID function to validate that the basket is still active. Uses an exception handler to catch any SESSION_INVALID exception raised by ORDER_ID. The exception handler will call DELETE_BASKET to remove a basket that has expired. - ADD_TO_BASKET
Function to place an order for a quantity of a specific item. If the session identifier is provided, then the order is placed against the relevant order identifier; if the session identifier is blank, then a new basket is created. The function returns the current session identifier. - VIEW_BASKET
Result set procedure that lists the items ordered. Uses a GROUP BY clause. Calls the BARCODE procedure, which is itself a result set procedure. - VALIDATE_CUSTOMER
Function to identify a customer by their e-mail address and password. - PLACE_ORDER
Procedure to order the contents of the basket. The procedure takes two in parameter: session identifier and customer identifier. It returns an order number, total price in euros, the local currency for the customer and the price in that local currency.
Triggers within MIMER_STORE_WEB
There is a statement trigger that will fire after an update to the SESSIONS table. The trigger is designed to prevent any changes to the values of the SESSION_NO and ORDER_ID columns. This demonstrates that a trigger can be used to abort an SQL update operation.
Idents within MIMER_STORE_WEB
A new user ident (MIMER_WEB) is created to allow web-applications execute privilege on certain of the PSM routines.
The Web Idea
The basic idea behind a web application would be two tabs, one for Music and the other for Books.
The relevant SEARCH routine provides a list of matches. One of the details returned is the ITEM_ID. The user would then have the ability to drill down to display further information by using the procedures MIMER_STORE_MUSIC.TitleDetails and MIMER_STORE_MUSIC.TrackDetails for Music items and MIMER_STORE_BOOK.TITLE_DETAILS for Book items.
If an item is selected for purchase the quantity should be prompted for and the procedure MIMER_STORE_WEB.ADD_TO_BASKET should be used to order the item. A blank SESSION_ID defines a new session. Once a session has been created the basket can be viewed using MIMER_STORE_WEB.VIEW_BASKET.
When an order has been completed the user needs to be identified by their e-mail and password. This can be done by using the MIMER_STORE_WEB.VALIDATE_CUSTOMER procedure. Then a call should be made to MIMER_STORE_WEB.PLACE_ORDER.
See the Example 9 below that describes this scenario.
Data
The data is then inserted into the tables. The data load is based upon the contents of a file called exdat.sql. The base tables use simple INSERT statements but the Music and Book details are inserted using PSM routines.
After the data has been loaded then an UPDATE STATISTICS is run to gather the statistics for the optimizer.
Synonyms
A complete set of synonyms is created for the ident MIMER_ADM. This is to simply access to the tables in the classroom; the synonym can be used in place of the table-names that would have to be qualified with the schema-name.
Synonym Target ------------------- ------------------------------- ARTISTS MIMER_STORE_MUSIC.ARTISTS AUTHORS MIMER_STORE_BOOK.AUTHORS BOOK_DETAILS MIMER_STORE_BOOK.DETAILS BOOK_TITLES MIMER_STORE_BOOK.TITLES CATEGORIES MIMER_STORE.CATEGORIES COUNTRIES MIMER_STORE.COUNTRIES CURRENCIES MIMER_STORE.CURRENCIES CUSTOMERS MIMER_STORE.CUSTOMERS CUSTOMER_ADDRESSES MIMER_STORE.CUSTOMER_ADDRESSES CUSTOMER_DETAILS MIMER_STORE.CUSTOMER_DETAILS FORMATS MIMER_STORE.FORMATS IMAGES MIMER_STORE.IMAGES ITEMS MIMER_STORE.ITEMS KEYWORDS MIMER_STORE_BOOK.KEYWORDS MUSIC_DETAILS MIMER_STORE_MUSIC.DETAILS MUSIC_SEARCH MIMER_STORE_MUSIC.SEARCH MUSIC_TITLES MIMER_STORE_MUSIC.TITLES ORDERS MIMER_STORE.ORDERS ORDER_ITEMS MIMER_STORE.ORDER_ITEMS PRODUCERS MIMER_STORE.PRODUCERS PRODUCTS MIMER_STORE.PRODUCTS PRODUCT_DETAILS MIMER_STORE.PRODUCT_DETAILS SAMPLES MIMER_STORE_MUSIC.SAMPLES SESSIONS MIMER_STORE_WEB.SESSIONS STOP_WORDS MIMER_STORE.STOP_WORDS SWEDISH_CUSTOMERS MIMER_STORE.SWEDISH_CUSTOMERS TRACKS MIMER_STORE_MUSIC.TRACKS
Passwords
In order to protect the Example Database you should use the ALTER IDENT statement to change the passwords for the idents MIMER_STORE, MIMER_ADM and MIMER_USR. The initial passwords were defined as ‘GoodiesRUs’, ‘adm’ and ‘usr’ respectively (case is significant). The ident MIMER_STORE is the creator of the MIMER_ADM and MIMER_USR idents and has therefore the ability to change these passwords as well as its own.
The password for MIMER_WEB is ‘web’; it is not essential to change this password, as it will be used in whatever web-applications are developed and therefore there is the possibility that it will be visible anyway. What is important is that the MIMER_WEB ident only has very restricted access to the database, preferably only through PSM routines.
Examples
Below are examples that can be executed under ident MIMER_ADM.
Note! The examples below are verified with the exdef.sql and exdat.sql files provided with this article. (i.e. corresponding files distributed with an older version of Mimer SQL can be out of date).
Example 1 – Add another Label/Producer
If an item from a new producer or record label should be added to the database, the producer/label must be added first. See below how to insert the “Columbia” label (the producer identification number, PRODUCER_ID, is unique and automatically generated by the PRODUCER_ID_SEQ sequence).
The execution:
SQL>INSERT INTO producers(producer) VALUES ('Columbia');
SQL>SELECT * from producers WHERE producer = 'Columbia';
PRODUCER_ID PRODUCER
=========== ================================================
10240 Columbia
1 row found
SQL>
Example 2 – Add another CD
Use the AddTitle procedure to insert a new record. The lowest price that can be set is 4.99 according to a domain setting on PRICE. The formats that are available are found in the FORMATS table.
The procedures used and their parameters:
mimer_store_music.AddTitle('MusicTitle', 'RecordedBy', 'RecordLabel',
'Format', 'ReleaseDate', Price, NumberInStock,
ReorderLevel, EANCode);
mimer_store_music.AddTrack(EANCode, TrackNumber, 'TrackTitle', 'TrackTime');
The execution:
SQL>SELECT format FROM formats;
FORMAT
====================
Audio CD
Cassette
DVD Audio
Vinyl
Audio Cassette
Audio CD
Hardcover
Paperback
DVD Video
Video
10 rows found
SQL>CALL mimer_store_music.addtitle('Aerosmith''s Greatest Hits',
'Aerosmith', 'Columbia', 'Audio CD',
'01/05/1993', 5.00, 1, 0,
5099750143827);
SQL>CALL mimer_store_music.AddTrack(5099750143827,
1, 'Dream On','4:28');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
2, 'Same Old Song And Dance','3:01');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
3, 'Sweet Emotion','3:12');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
4, 'Walk This Way','3:31');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
5, 'Last Child','3:27');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
6, 'Back In The Saddle','4:38');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
7, 'Draw The Line','3:21');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
8, 'Kings And Queens','3:47');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
9, 'Come Together','3:45');
SQL>CALL mimer_store_music.AddTrack(5099750143827,
10, 'Remember (Walking in the Sand)','4:05');
SQL>
Example 3 – Search by artist
Get music items recorded by an artist whose name sounds like “Pruce Springstin”. Limit the result to one record. For that record, get the title and track details.
The procedures used and their parameters:
mimer_store_music.Search('MusicTitle', 'RecordedBy', MaxRows);
mimer_store_music.TitleDetails(ItemID);
mimer_store_music.TrackDetails(ItemID);
The execution:
SQL>CALL mimer_store_music.Search('', 'pruce springstin', 1);
TITLE
ARTIST FORMAT PRICE
ITEM_ID ARTIST_ID MATCH_LEVEL
===============================================================================
Born in the U.S.A.
Bruce Springsteen Audio CD 17.98
60091 50091 **
===
1 row found
SQL>CALL mimer_store_music.TitleDetails(60091);
TITLE
ARTIST
LABEL FORMAT
RELEASE_DATE PRICE PLAY_TIME IMAGE_ID ITEM_ID
=====================================================================
Born in the U.S.A.
Bruce Springsteen
Sony Audio CD
25/10/1990 17.98 46:19 - 60091
===
1 row found
SQL>CALL mimer_store_music.TrackDetails(60091);
TRACK_NO TITLE LENGTH SAMPLE_ID
======== ================================================ ====== ===========
1 Born In The U.S.A. 4:39 -
2 Cover Me 3:26 -
3 Darlington County 4:48 -
4 Working On The Highway 3:11 -
5 Downbound Train 3:35 -
6 I'm On Fire 2:36 -
7 No Surrender 4:00 -
8 Bobby Jean 3:46 -
9 I'm Goin' Down 3:29 -
10 Glory Days 4:15 -
11 Dancing In The Dark 4:01 -
12 My Hometown 4:33 -
12 rows found
SQL>
Example 4 – Search by record title
Get two record titles that contains a phrase that sounds like “gretest hits”.
The procedure used and its parameters:
mimer_store_music.Search('MusicTitle', 'RecordedBy', MaxRows);
The execution:
SQL>CALL mimer_store_music.Search('gretest hits', '', 2);
TITLE
ARTIST FORMAT PRICE
ITEM_ID ARTIST_ID MATCH_LEVEL
===============================================================================
All-Time Greatest Hits
Dean Martin Audio CD 9.98
60038 50038 **
===
Greatest Hits
2Pac Audio CD 24.98
60219 50219 **
===
2 rows found
SQL>
Example 5 – Search by author
Get all books written by Agatha Christie, and get the details for a specific title.
The procedures used and their parameters:
mimer_store_book.search('book_title', 'author');
mimer_store_book.title_details(item_id);
The execution:
SQL>CALL mimer_store_book.search('', 'CHRISTIE,AGATHA');
TITLE
AUTHORS_LIST
FORMAT PRICE ITEM_ID
===============================================================================
Black Coffee
Christie, Agatha; Moffat, John
Audio Cassette 13.00 60687
===
Black Coffee
Christie, Agatha; Osborne, Charles
Paperback 5.99 60686
===
Miss Marple Omnibus Volume 1
Christie, Agatha
Paperback 9.99 60830
===
Miss Marple Omnibus Volume 2
Christie, Agatha
Paperback 9.99 60831
===
Miss Marple Omnibus Volume 3
Christie, Agatha
Paperback 9.99 60832
===
Miss Marple: the Complete Short Stories
Christie, Agatha
Paperback 7.99 60833
===
The ABC Murders
Christie, Agatha
Hardcover 15.99 60932
===
The ABC Murders
Christie, Agatha
Paperback 4.99 60931
===
The Mousetrap
Christie, Agatha
Paperback 6.99 61006
===
The Murder at the Vicarage
Christie, Agatha
Hardcover 15.99 61009
===
The Murder at the Vicarage
Christie, Agatha
Paperback 4.99 61007
===
The Murder at the Vicarage
Christie, Agatha; Whitfield, June; Bakewell, Michael
Audio Cassette 9.99 61008
===
While the Light Lasts
Christie, Agatha
Paperback 5.99 61097
===
While the Light Lasts
Christie, Agatha
Audio Cassette 8.99 61098
===
Witness for the Prosecution
Christie, Agatha
Paperback 6.99 61104
===
15 rows found
SQL>CALL mimer_store_book.title_details(60687);
TITLE
AUTHORS_LIST
PUBLISHER ISBN
FORMAT RELEASE_DATE PRICE IMAGE_ID ITEM_ID
===============================================================================
Black Coffee
Christie, Agatha; Moffat, John
HarperCollins ISBN 0-00-105536-4
Audio Cassette 16/11/1998 13.00 - 60687
===
1 row found
SQL>
Example 6 – Search by book title
Get all book titles that contains something that sounds like ‘harie potar’.
The procedure used and its parameters:
mimer_store_book.search('book_title','author');
The execution:
SQL>CALL mimer_store_book.search('harie potar', '');
TITLE
AUTHORS_LIST
FORMAT PRICE ITEM_ID
===============================================================================
Harry Potter and the Chamber of Secrets
Rowling, J.K.
Hardcover 10.99 60769
===
Harry Potter and the Chamber of Secrets
Rowling, J.K.
Paperback 5.99 60770
===
Harry Potter and the Chamber of Secrets
Rowling, J.K.; Fry, Stephen
Audio CD 42.99 60771
===
Harry Potter and the Philosopher's Stone
Rowling, J.K.
Hardcover 10.99 60772
===
Harry Potter and the Philosopher's Stone
Rowling, J.K.
Paperback 5.99 60773
===
Harry Potter and the Philosopher's Stone
Rowling, J.K.; Fry, Stephen
Audio CD 37.99 60775
===
Harry Potter and the Philosopher's Stone
Rowling, J.K.; Fry, Stephen
Audio Cassette 21.99 60774
===
Harry Potter and the Prisoner of Azkaban
Rowling, J.K.
Hardcover 10.99 60776
===
Harry Potter and the Prisoner of Azkaban
Rowling, J.K.
Paperback 5.99 60777
===
Harry Potter and the Prisoner of Azkaban
Rowling, J.K.; Fry, Stephen
Audio CD 52.99 60778
===
10 rows found
SQL>
Example 7 – Get planned releases
Get all book titles that are planned to be released in the near future.
The procedure used and its parameters:
mimer_store.coming_soon('category');
The execution:
SQL>SELECT * FROM categories;
CATEGORY_ID CATEGORY
=========== ====================
1 Music
2 Books
3 Video
3 rows found
SQL>CALL mimer_store.coming_soon('books');
PRODUCT
PRODUCER FORMAT
RELEASE_DATE PRICE ITEM_ID
=====================================================================
The Future Foretold
Collins Paperback
11/11/2017 6.99 61110
===
The SQL Quiz Book
Compuprint Publishing Hardcover
15/11/2017 13.99 61109
===
2 rows found
SQL>
Example 8 – Get an item by EAN code
Get the item that is identified by the given EAN code.
The procedure used and its parameters:
mimer_store.barcode(EAN);
The execution:
SQL>CALL mimer_store.barcode(752725002127);
TITLE
CREATOR FORMAT PRICE
ITEM_ID
===============================================================================
71 Minutes of Faust
Faust Audio CD 19.98
60014
===
1 row found
SQL>
Example 9 – Place an order in the web shop
Insert a demo customer into the database. Add items to the shopping basket. View the basket content, verify the customer identity and place the order. Note that the session number seen and used in the example below will be different for each purchase session initiated.
The procedures used and their parameters:
mimer_store_web.add_to_basket('[session_no]', item_id, quantity);
mimer_store_web.view_basket('session_no');
mimer_store_web.place_order('session_no', customer_id,
:order_no', :total_in_euros,
:local_currency, :total_in_local_currency);
The execution:
SQL>INSERT INTO customers VALUES(
DEFAULT, 'Mr', 'Jones', 'Paul', date'1962-12-01', 'D Street 12', '',
'Uptown', '777', 'SE', 'paul.jones@d.se', 'pj', DEFAULT, DEFAULT);
SQL>SET :session_no = mimer_store_web.add_to_basket('', 60214, 2);
SESSION_NO
================
268-0001-000-002
1 row found
SQL>SET :session_no = mimer_store_web.add_to_basket('268-0001-000-002',60833,1);
SESSION_NO
================
268-0001-000-002
1 row found
SQL>call mimer_store_web.view_basket('268-0001-000-002');
TITLE
CREATOR FORMAT QUANTITY
PRICE
==============================================================================
Greatest Hits
Bruce Springsteen Audio CD 2
35.96
===
Miss Marple: the Complete Short Stories
Christie, Agatha Paperback 1
7.99
===
Total price (euros)
- - -
43.95
===
3 rows found
SQL>SET :customer_id = mimer_store_web.validate_customer('paul.jones@d.se','pj');
CUSTOMER_ID
===========
100001
1 row found
SQL>CALL mimer_store_web.place_order('268-0001-000-002', 100001,
:order_no, :euros, :currency, :cost);
ORDER_NO EUROS CURRENCY COST
================ ============ ================================ =================
02277-10000 43.95 Swedish Kronor 394.89
1 row found
SQL>
Links
Basics in Mimer SQL can be found in the article Introduction to database concepts in Mimer SQL.
The SQL statements to create the Example Database are found in a file called exdef.sql, that is combined with the data load statements are found in a file called exdat.sql.
For details and further information on Mimer SQL see the Mimer SQL Documentation set.
