Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


The MIMER_STORE_BOOK Schema


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

Tables

Table name
Description
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.

PSM Routines

PSM routine name
Description
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.

Views

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

Triggers

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.

Idents

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
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX