The MIMER_STORE_BOOK Schema
This schema contains a table named
TITLES, as does the
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_LISTin the form:
Links an item to an entry in the
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
Object privileges are granted on the
MIMER_STORE_BOOKschema tables to
Procedure to validate an ISBN.Uses an
INOUTparameter.Shows how to
INTEGERand 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
CASEstatements, 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
Function to insert a word into the
KEYWORDStable 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_NAMEfunction and then the
KEYWORD_IDfunction. Finally, creates a link between each name and the book in the
Procedure to insert the base details for a book into the database, updating a number of tables.Inserts against the
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.
This view includes the
PRODUCT_DETAILSview and is the equivalent of the
DETAILSview in the
There are two statement triggers defined against the
TITLEStable. They are designed to maintain the entries in the
KEYWORDStables. The update trigger is written to show how to use the
There is also an
INSTEAD OFtrigger defined on the
DETAILSview. This is used by the
ADD_TITLEprocedure to update the underlying tables on which the view is based. Note that the
INSTEAD OFtrigger 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
DETAILSare granted to
Execute privilege on the PSM routines
TITLE_DETAILSis granted to
Synonyms are created by
MIMER_STOREfor all tables in the
MIMER_STORE_BOOKschema. The synonyms are created with a
BOOK_prefix for the
Mimer Information Technology AB
Phone: +46 18 780 92 00