QBF Exercise

Objectives:

1. Find out how to use Ingres QUERY facility;
2. Add suitable data to your Data Dictionary tables;
3. Determine the data validation limitations of QBF.
4. Enter data for the Library database using data from the Library Data Model.

In preparation

1. Select (f8) the Ingres/Menu TABLES utility.
2. Scroll down to the borrower table.
3. Invoke the Query function with the Tablefields option.
   Ingres creates a default form to manipulate the borrower table.

Querying data through QBF.

1. When Ingres shows the QBF - Execution Phase screen, select the Retrieve option.
   You are now running a simple (i.e. very small) QBF or Query By Forms application.

   To look at all the information currently in the table:

   1. Use the Go(f8) option.
   2. Use the tab, up and down cursor and return keys to move around the data. Consider what happens in each of the following cases:
      1. Press tab several times (at least 10) in a row watch where the cursor goes with each press.
      2. Now repeat this with the return key. How is this different?
      3. Scroll down a few records using the cursor keys and repeat the above steps. What happens now?

      You should now know how to:

      1. Show data that is off the screen (and note that Ingres can 'show' more data than will fit on one screen).
      2. Navigate around records.
      3. Return to the first record.

   To retrieve only some of the information in the table.

   QBF allows us to query specific aspects of the data held in a table rather than having to show it all.

   1. Use the query option to blank the screen. You can now provide QBF with an 'example' for the kind of information you want it to retrieve.
   2. For example, imagine you want to find all the borrrowers whose borrower number ends in '1' (bizzarre I know).
      1. type '*1' into the borrower number (Bor No) field. '*' is a QBF wild card which matches any number of any character.
      2. Now press Go(f8). Can you guess what you'll see?
   3. Use the query option to try other queries. You could specify conditions in more than one field, of give literal values to match, e.g. 'B0000011'. Experiment, have fun. SQL is case sensitive.

   Use End(f3) to back out of the Retrieve function.

Entering data through QBF.

1. Invoke the append option (again for the borrower table). This will allow us to add new data to the table. Ingres reuses the tablefield layout it created for the previous step.
2. We could first of all add a 'reasonable' set of data about a new borrower. To do this:
   1. Type B0000050 in the 'Bor No' field. Note that, as this data fills the field Ingres automatically jumps to the next field.
   2. Type your surname followed by your first initial in the 'Bor Name' field. You will now need to use the tab or return key to jump to the next field.
   3. Give youself a status of between 1 and 5. This is the number of books you can borrow. (Ingres jumps automatically).
   4. Make up an address and type it into the address fields. Use tab or return to jump to the next field.
   5. When you have filled in a 'Post Code' press tab. Note that the cursor stays on the same line. You can use tab or return to go through the fields again making any corrections you need to.
   6. Now when you get to the 'Post Code' field press return. This makes Ingres jump to the next line where it expects you to enter more data.
      • tab in the last field stays on the same record,
      • return in the last field goes to the next record.
   7. Now press Append(f8) to write your data to the database. Up to now your data has only existed on the screen. Once your append it to the table it is stored in the database.
   8. Back out of the append function using End(f3). Note that Ingres tells you how many rows were added to the table.

Modifying data through QBF.

1. Invoke the update option (again for the borrower table). This will allow us to modify, or change, existing data in a table. You can also add new data to the table. Ingres again reuses the tablefield layout it created for the previous step.
   The modify option builds on the querying and data entry features you have already seen.
2. First of all press Go(f8) to show all the records in the table. We've just done this step to show you that you could change all the data in the table.
3. Let's assume you want to change the address of borrower number 'B0000011', and add a new borrower (i.e. someone has moved and brought along a child to register).
   1. To retrieve the details for that person. Invoke the query option and type 'B0000011' (capital B, five zeros, and two ones). Then press Go(f8). This displays the record we are interested in.
   2. Change the address (Addr1) to '41, Rocky Road' by overtyping and deleting.
   3. Use the down cursor key to move to the next record and add suitable 'child' details.
   4. Use the Save(f5) key to save the changes you have made. Ingres provides a 'Changes saved' message, then reblanks the screen ready for a new query.

Experiment with QBF Appending, Retrieving, Updating (& Deleting, through the same option) data in your database.