JoinDef Exercise

Objectives:

  1. Find out how to create a MASTER/DETAIL JoinDef.
  2. Learn how to edit the resulting default FORM for the JoinDef.
  3. Explore the 'Rules' that prevent update and/or deletion of records when using JoinDefs.
    1. Create a JoinDef that will show the borrower details and the details of any loans taken by particular borrowers.
      1. In Ingres/Menu Select f(8) the JoinDefs utility.
      2. Invoke the Create option to create a Blank JoinDef called borrower_loans.
      3. Enter the following details under
        Role, Table Name and Abbreviation:
        master, borrower, br
        detail, loan, l
        detail, copies, c
        detail, book, bk
        - leave the Table Field Format? flag set to YES and Go f(8) the Join Definition. Don't worry about the nullable/ non-nullable message.
      This has created a joindef which will show, for each borrower the books they have on loan. We've got borrower/loan as master detail, loan/copies as detail/detail, and finally copies/book as another detail/detail. We need to navigate through these tables to pick up appropriate information e.g. book title from book.
    2. Once you have done this, test it using the RETRIEVE option in QBF.
      1. Once the JoinDef has been created Ingres assumes you want to run it and drops you into the Execution phase of QBF for the JoinDef. You've already used QBF on single tables so it should be vaguely familiar to you.
      2. Select the Retrieve option.
      3. Go f(8) a blank query and use NextMaster(f8) to scroll through the borrowers.
        As you look at the form note how Ingres has dealt with the multiple tables and see what data it is showing compared to the table definitions.
      4. End(f3) the retrieve function.
      5. End(f3) the QBF Execution Phase
      6. Save(f5) the JoinDef, leaving the option fields as they are.
    3. Now create a JoinDef called 'book_details' that shows book details, along with the copy details and loan details for each book in the library. Test that your JoinDef works by using QBF.
      1. Blank the JoinDef Definition Form.
      2. Repeat the above steps for the specified tables.
      3. Save(f5) the JoinDef.
    4. Set the Rules that prevent the user from DELETING both MASTER and DETAIL rows in your JoinDef defined in 2).
      1. End(f3) the JoinDef Definition Form.
      2. From the JoinDefs Catalog select the book_details row and invoke the Edit option.
      3. From the Join Definition Form invoke the Rules option.
      4. Change all the Delete? flags from 'yes' to 'no'.
      5. End(f3) the JoinDef Update & Delete Rules option and Go f(8) the JoinDef again.
      6. Select the Update option.
      7. Go f(8) a blank query then try to Delete the first record.
        See what message Ingres gives you. We've now created a form that allows users to browse book details, but they cannot delete records, intentionally or otherwise (in an application we may provide this function somewhere else).

    Continue with trainer.

    This WWW material is copyright De Montfort University and may only be reproduced or used if this copyright notice is included.
    This page is maintained by John Skelton. Comments or suggestions for improving this material can be emailed to jas@dmu.ac.uk) and will be gratefully received.