So far within QBF we have been restricted to manipulating data in ONE table at a time. This is unrealistic, as most of the time we would wish to manipulate data in TWO (or more) tables in one go. This can be achieved by the use of either JoinDefs or VIEWS.
Both JoinDefs and VIEWs join tables using columns of a common domain. A VIEW is a virtual table and is stored in a Tables Catalog. JoinDefs are stored in the JoinDefs Catalog. Both are available to QBF to maintain data on the database.
Great care must be taken to preserve REFERENTIAL INTEGRITY when maintaining data through JoinDefs or VIEWs.
A JoinDef is a predefined Join Definition , the most common type of which is a MASTER/DETAIL join. This represents a 1(Master):n(Detail) relationship, respectively, where the one row from the MASTER table is displayed as Simple-fields and several rows from the DETAIL table are displayed as rows in a Table-field; e.g. Publisher may represent the MASTER and Book the DETAIL.
A JoinDef may join up to ten MASTER/MASTER or ten DETAIL/DETAIL tables where the relationship degree between the MASTER tables and that between the DETAIL tables is 1:1. Only ONE MASTER/DETAIL join can be defined in a JoinDef, (i.e.1:n).
That sounded a bit heavy didn't it. Basically what it means is you could
have a master (say publisher), and also print some related information (say each
publisher has one editor-in-chief [I've just made that up]) - so we can join
those two together to show both sets of information. Then we include the
MASTER/DETAIL link - each publisher produces many books. Then we could also
join the detail (book) table to something, say each book has one author [again I
made that relationship up] so we can do a detail/detail link to show the author
of a book.
So - overall the join def could show a publisher plus the editor-in-chief
(master/master) plus all the books published by that publisher (master/detail),
plus the author of each book (detail/detail). Simple!
JoinDef exercise - do this now.
VIEWs are created using the SQL command CREATE VIEW. These can be considered to be external schemas in the ANSI SPARC 3 schema model, and can be used to restrict a User's access to the database. A VIEW may define a subset of a single table or may involve joining one or more tables.
VIEWs are extremely useful for retrieving data from the database, however great care must be taken when updating data through VIEWs. Updates using VIEWs based on more then one Table are not allowed. Updates are also not allowed on columns used in the WHERE clause of the VIEW definition.
VIEWs are created using the SQL command CREATE VIEW, executed from within the Ingres SQL facility.
Views exercise - do this now.
Continue with trainer.