Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site



This schema contains a table named TITLES, as does the MIMER_STORE_MUSIC schema.


Table name
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; ....
Links an item to an entry in the KEYWORDS table.
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.

PSM Routines

PSM routine name
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.
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.
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.
Function to format an author's name into surname[,initial].
Function to insert a word into the KEYWORDS table and return the identifier with which the keyword is associated.
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.
Procedure to insert the base details for a book into the database, updating a number of tables.
Inserts against the DETAILS view.
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.
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.


View name
This view includes the PRODUCT_DETAILS view and is the equivalent of the DETAILS view in the MIMER_STORE_MUSIC schema.


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.


Access privileges on the view DETAILS are granted to MIMER_ADMIN_GROUP.

Execute privilege on the PSM routines SEARCH and TITLE_DETAILS is granted to MIMER_STORE_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.

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
Mimer SQL Documentation TOC PREV NEXT INDEX