The MIMER_STORE_MUSIC Schema
Schemas have been used to group objects related to a specific area; note that all the schemas are owned by the ident
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.
CREATE SCHEMAstatement is used to demonstrate that, within the statement, an object doesn't have to be created before it can be referenced.
DURATIONdomain introduces the
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.
Links an artist name with an item.
Holds track details, e.g. title, length.
Holds samples from the tracks.
This view includes the
PRODUCT_DETAILSview and demonstrates a number of things:Tables can be included from more than one schema.The use of a correlation name in the
PRODUCT_DETAILStable reference.Restriction conditions can be applied so that different users would see different results, in this case the ident
MIMER_WEBwould not see vinyl albums.One of the quirks in SQL -
ITEM_IDhas to be explicitly included in the select-list.
This view is based on
DETAILSbut includes additional selection restrictions.Note that the
ASclause is noise and may be omitted but it does add clarity. This view demonstrates both forms.
Note that the
CREATE SCHEMAstatement includes grant object privilege statements as well as object definitions.
The names in the PSM routines for this schema have been capitalized rather than the parts separated by an underscore.
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
PRODUCERStable - these introduce exception handlers.Uses another form of the row data type.Demonstrates the use of
current_valueof a sequence.
Procedure to insert the track details for an EAN code.Demonstrates the use of SQLSTATE values in an exception handler.Introduces the
RESIGNALstatement and shows another option with
Function to remove any leading definite or indefinite articles from a name.
Function to return the Soundex value of a character string after it has been processed by the
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
SEARCHview. Includes the use of the
DISPLAY_ORDERcolumn in the
FORMATStable 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 placed in the database.
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.
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.
There are two statement triggers in the
MIMER_STORE_MUSICschema, both defined against the
ARTISTStable. They are designed to ensure that the value of the
ARTIST_SEARCHcolumn is based on the artist name. The same technique as used in the
MIMER_STOREschema 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.
Execute privilege on the PSM routines
TrackDetailsis granted to
Synonyms are created by
MIMER_STOREfor all tables and views in the
MIMER_STORE_MUSICschema. The synonyms are created with a
MUSIC_prefix for the
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40