Viewpoint
Datahandling for students

Library Tutorial

The aim of this tutorial is to describe how to set up a simple but effective Library management system using relational techniques. In it you will create a table to store the books, a table to store the people using the library and a table to store who borrowed what and when. The relationship between the three tables is very simple but some time is spent creating difference layouts using the tables in different combinations. In particular you will create a layout from which books are checked out of the library, a layout to check them in again and a layout to search for books by title, Author, publisher etc.

This tutorial has been designed for both experienced Viewpoint users and people new to Viewpoint.

You can download the final library example file by clicking here, or follow the instructions below to create it yourself.

Structure of the database

The Books table will contain a record for each book in the library.
The Borrowers table will contain a record for each person who may borrow books from the library.

The Book Lending History table will contain a record for each occasion when a book is borrowed from the library.

In this context it is important to make a distinction between a book, which you will take to mean an individual copy, and a title, which is what the book is a copy of. For example, if the library has three copies of 'Roget's Thesaurus', they will all have the same ISBN (which is the international book code used to uniquely identify a title), but will have separate records in the books table.

So, the primary key of the books table cannot simply be the ISBN, as this would not be unique if the library had more than one copy of a given title. To overcome this, we use a formula field ('BookID') to combine the ISBN with the 'ID' field of the record, giving a unique primary key for each book that also contains the ISBN of the book.

In the case of the borrowers, each person is assigned a unique membership number, which is used as the primary key of this table. This allows for the situation where, for example, two John Smiths use the library - if the person's name were used as the primary key, you might run into difficulties.

The Book Lending History contains a foreign key 'BookID', indicating which book was borrowed, and another foreign key 'BorrowerNo', which indicates who borrowed the book. It also contains the date on which the book was borrowed, and the date it was returned (which is null if the book is still out).

Note: If you weren't interested in seeing the lending history of the books, it would be possible to dispense with the Book Lending History table and simply put the following fields into the Books table instead:

BorrowerNo (foreign key)

Date borrowed

When a book is borrowed, today's date is copied into the 'Date borrowed' field, and when it is returned, this field is set back to null. You can then list the outstanding books by searching for books where 'Date borrowed' is non-null.

Creating the Books Table

The first step is to create a new database with a table for the books in the Library.

A blank layout page appears with which to create your first table. This will be the database front page. Leave it blank for now so you can create a nice front page later. To create another layout click on Layout => New Layout from the menu and type in Add new book. Then click 'OK'. You should also name the new table by clicking Layout => Rename Table from the menu. Alter Table name to 'Books' and click 'OK'.

Now create some fields for the 'Books' table in the usual way, choosing the field type from the toolbar and then clicking on the layout background to create the fields.

Tip: Make sure that you don't have any fields selected before clicking on the field type you want, as this will alter the type of the selected fields rather than setting the type of the fields you will create next.

Create the following fields:

You need to put a check formula on the 'ISBN' field so that it only accepts correct ISBN codes (ones with 10 digits). To do this, select the 'ISBN' field and choose Field=>Options=>Check (or press Ctrl-Shift-C), and type:

len(@) = 1

Note that @ can be used to represent the "current field", in this case 'ISBN', so this is equivalent to len('ISBN') = 10

Tip: you can set your own error message by using this check formula instead:
If len(@) = 10 then True else Error ("ISBN codes must have 10 characters")

In a similar way give the 'BookID' field a formula by selecting it and clicking Field => Type =>Formula (or press Ctrl-Shift-G) and type:

'ISBN' + "/" + STR$('ID')

Give the 'Purchase price' field a display format by selecting it and choosing Field => Type => Real and selecting 2 decimal places, a £ sign before the number and a thousands separator (well, some books are pretty expensive these days!).

Make the fields 'Title', 'ISBN' and 'BookID' display in bold. A quick way to do this is to select the three fields whilst holding down the control keys and then press Control B.

Rearrange the fields as required.

To add a background colour to the layout, choose Arrange => Background colour from the menu and choose a colour for the layout. To add a frame to enclose the group of fields select the frame tool and drag out a frame. Click on Effect => Frame Style from the menu and choose a thin border. Unclick the transparent box in the colour tab so that the white body of the frame will show. In order to send this frame to the back of the question frames, choose Arrange => Send to back from the menu.

To give this layout a title click on the text tool and click on the background at the top of the layout and type the following heading: Office view of all Books. You can change the font by choosing Effect => Text font from the menu.

You can now put some data in your Books table. First save your layout and open Sheets view.

Add some book examples of your own, or download the Books tab file by clicking here.

Import the Books file as follows:

Choose File=>Import=>Import into file from the menu and open the books tab file.

A merge dialogue box will appear; click on merge to add ten new records to the Books table.

It is worth adding a few new books yourself: you will notice that the 'Purchase price' field automatically converts numbers into the pounds and pence format, and the 'BookID' field value will be automatically generated based on the 'ISBN' field value and the 'ID' field value.

When you create a new book record Viewpoint will check that the 'ISBN' field contains 10 characters. If it doesn't the record cannot be saved. This helps to prevent incorrect ISBN numbers being entered into the Books table.

Note: If some of your books or publications don't have ISBNs you will have to invent some unique numbers of your own, as this field needs to be filled in to allow each book to have a primary key value. To allow 8-character ISBNs to be used in this field (for other types of publications), you can alter the check formula to

len(@)=8 or len(@)=10

Creating the Borrowers Table

Create a new table to hold all the people who are allowed to use the library. Click on Layout => New Table and type Borrowers and click 'OK'. A new blank layout is opened to create a new table.

Note: Make sure you don't choose Layout=>New layout by mistake! This would create a new layout, but linked to the same table as the layout we were previously on, and would also copy the existing frames from the old layout, rather than starting with a blank screen.

Create the following text fields:

'Firstname'
'Surname'
'Address'
'Postcode'
'Telephone'
'Email'
'BorrowerNo'

Give the 'BorrowerNo' field a default formula by selecting it, clicking on Field => Options =>Default and typing

If 'ID' = NULL then NULL else "B" + STR$('ID')

Note: when you create a new record, the 'ID' field starts out NULL until you modify a field in the record. This means that any default formulae should be written so that if the 'ID' field is null, the formula also evaluates to null - otherwise it will not be recalculated when the 'ID' field is filled in.

Smarten up the layout so that you are happy with it. Also click on Layout => Rename Layout and type Add new Borrower.

You can now put some data in the Borrowers table. Add some names of your own or click here to download the Borrow tab file.

Import the Borrowers tab file as follows:

Choose File=>Import=>Import into file from the menu and open the Borrow tab file.

A merge dialogue box will appear; click on merge and to add twenty records to the Borrowers table.

Creating the Book Lending History Table

Having created a table of books and a table of borrowers you now need one further table which keeps track of which books have been borrowed by which people. Click on Layout => New Table and type in Book lending history and click 'OK'. Create the following text fields 'BookID', 'BorrowerNo', the date fields 'Date taken out', 'Date returned' and the formula fields 'Due to be returned' and 'Overdue'.

Select the field 'Date taken out' and choose Field => Options => Default from the menu (or press Ctrl-Shift-G) to give the field the following default formula:

If 'BorrowerNo' = null then null else today

Select the field 'Due to be returned' and choose Field => Type => Formula, from the menu to give the field the following formula:

'Date taken out' + 14 days

By selecting the field 'Overdue' and clicking on Field => Type => Formula, give the field the following formula:

Today > 'Due to be returned'

There is no need to smarten up this layout as we will delete it later. The purpose of the layout was to enable us to create the Book Lending History table. Each record in this table represents a borrower taking out a book, the borrower in question is given by the value of the field 'BorrowerNo', the book in question is given by the value of the field 'BookID'. It is important therefore that each person has a unique 'BorrowerNo' in the 'Borrower' table and similarly each book has a unique 'BookID' in the 'Book' table. In database language we say that 'BorrowerNo' is the primary key of the 'Borrower' table and 'BookID' is the primary key of the 'Book' table. It is very important to set up the primary keys in a database as it allows Viewpoint to look up the details of a book given just the value of the 'BookID' field, and similarly for the borrowers.

Setting up the relationships

To set the primary keys, go to Layout view and choose Query/Table => Relationships from the menu. Click on Add table and add the three tables in the list. Close the Add table dialogue box.

To make the 'BookID' field of the 'Books' table into its primary key, double-click on the field, which should then be displayed in bold. Similarly double-click on the 'BorrowerNo' field in the 'Borrower' table to make it the primary key.

At this stage it is also a good idea to set up the default joins between the various tables, which will be used later on by Viewpoint when you create the actual join queries that allow you to view data from related tables.

Drag the 'BookID' field in the 'Books' table onto the 'BookID' field in the 'Book lending history' table. You should see that a line has been drawn connecting the two fields together.

Note: You could equally well drag the BookID field from the Book Lending History table to the Books table - it makes no difference.

Double-click on this line to open the Edit Relationship dialogue box. Click on 'Enforce one to many' and click on 'OK' to confirm.

You will now see that there is a '1' at one end of the line (next to the Books table), and an infinity sign at the other (next to the Book Lending History table).

This indicates that one record in the Books table can match many records in the Book Lending History table, but each record in the Book Lending History table can match only one book (or none, if the BookID field is null).

It also means that you've enforced referential integrity between the two tables, which means that you're not allowed to enter a value into the 'BookID' field of the 'Borrower Lending History' table that doesn't match an existing record in the 'Books' table.

Repeat the process to set up the relationship between the Book Lending History and Borrowers tables:

Drag the BorrowerNo field from the Book Lending History table onto the BorrowerNo field in the Borrower table

Double-click on the join line that's created

Select 'enforce one to many' and click 'OK'

Click 'OK' to save the Relationships and return to editing the Book Lending History layout.

Creating the Books Out query

You should now define a very important subset of the 'Book lending history' table which tells us which books are out at the moment. This is precisely the set of records which have the 'Date returned' field value not filled in. To define this subset and give it a name choose Query/Table =>Edit => New query from the menu. Click in the Name box at the top and type Books Out, also type NULL into the 'Date returned' field. Click on Perform Search to return to the 'Book lending history' layout.

This layout will not be used to create records: instead a more useful layout which will allow you to create 'Book lending history' records will be used instead.

Creating the Books Check Out layout

Imagine Logo a borrower coming to the desk asking to take out a clutch of books: you want to be able to find their details using their BorrowerNo which would be found on their card. You could check to see how many books they have out already and whether the books are overdue or not. If all is well you want to use the BookID values found on the books to create some 'Book lending history' records to record all books out at present with that borrower.

Change to the 'Add new Borrower' layout, and choose Layout => New layout and type in Books check out.

This creates a copy of the 'Add new Borrower' layout which is a good start for the new layout you wish to create. Delete the 'Address', 'Postcode', Telephone' and 'Email' fields.

Note: that deleting these fields only removes them from the layout; they are still present in the table as can be seen if you click on the Field menu on the toolbar. If you wanted to delete the fields for good, including the data contained in them, you can select the fields and press Ctrl-Delete. Extreme caution should be taken before doing this.

Rearrange the fields so they are closer and make a few alterations to how this layout looks. Select the three remaining fields, choose Effect => Frame style from the menu and change the Frame fill colour. Select the titles only of the Firstname and Surname fields and press delete, being careful not to delete the fields themselves. Add some text at the top of the layout e.g. Borrowers Record.

The next task is to display a list of the books that each person has out at the moment.

Select the Create subview tool from the toolbox and drag out a large rectangle covering most of the page.

The subview dialogue appears automatically, with the 'Show records from' box set to '<unknown>' - if you click on this box you'll see a menu showing all three tables that have been added to the relationships view.

If you chose the 'Book lending history' table then you would see all the books that the borrower has ever taken out. What you want to choose is the set of books which the borrower has out at the moment, i.e. you want to choose the 'Books Out' subset. In order to view the 'Books Out' query in the subview you need to add it to the relationships. To do this, choose 'Define Relationships' from the menu.

In the relationships view, click on Add Table, select the Queries view at the bottom and double-click on the 'Books Out' query to add it to the relationships. Click on Close to close the Add Table dialogue. Position the Books Out query just under the 'Book lending history' table and drag a line between the 'BookID' fields of the 'Books' table and the 'Books Out' query and a line between the 'BorrowerNo' fields of the 'Borrowers' table and the 'Books Out' query.

Note: You'll find if you double-click on either of these two new lines that you won't be allowed to enforce integrity for these relationships. That's because you can only enforce integrity between main tables, whereas Books Out is a query.

Click OK to return to the subview dialogue box, click on the 'Show records from' box at the top and choose 'Books Out' from the list. Click OK to close the subview dialogue.

If you click on the Fields menu in the toolbox you will see that all the fields from the 'Books Out' query are available to add to our layout. If you want to be able to see the title of books as well as the BookID you need to add the 'Books' table to the join for our layout. To do this, choose Query/Table => Join, and add the 'Books' table to the join in the usual way.

Note: You are actually editing the query called 'Join for Books check out'. This query was created for you when you added the subview and it is this query which fetches the data you will see when you look at this layout in Sheets view. This query should not be confused with the relationships, which are not used to fetch data, but instead define integrity constraints and provide default join lines for queries you create in the future.

Click 'OK' to return to the layout and start adding fields into the subview. To do this you keep the Subview selected and choose the required fields from the Fields menu in the toolbar.

Note: If the subview isn't selected then the field will appear in the body of the layout not in the subview. If you try to drag a field from the body of the layout into the subview then a new summary field is created based on the dragged field. New fields appear in the subview after the existing fields, if there is no room in the subview for the extra field it will be added at the end but may not be seen! In order to find the field you may need to widen the page and then widen the subview until you can see the hidden field. To avoid doing this, make sure there is enough room in the subview before adding the new field.

Note: The names in the Field menu have the format 'tablename.fieldname' this is so that you can distinguish between two fields with the same name in different tables, i.e. 'Books.BookID' and 'Books Out.BookID'. This tutorial will only refer to the fields by their fully qualified name.

Add the following fields to the subview:

'Books Out.BookID'

'Books.Title

'Book Out.Date taken out'

'Books Out.Due to be returned'

'Books Out.Date returned'

'Books Out.Overdue'

Rearrange the fields as required so they can all be seen on the screen. Select the subview without selecting the field titles above the subview (by dragging a small rectangle over one edge of the subview), click on Effect => Frame style from the menu and select a thin border. You could also edit the font used for your question titles if desired.

Select the 'BorrowerNo' and 'BookID' fields and choose Field => Options => Values from the menu. Select the 'Choose a value from the database' option. This will allow you to use a drop down list of BorrowerNo values to navigate to different Borrowers and also to use a drop down list to select the books which are to be taken out. If you would rather select books by their title then you can also put a value list on the 'Title' field. In a real situation it is unlikely that the titles of the books would all be unique, this would make it impossible to know which book you were selecting. This is the reason you need to have a 'BookID' field in the database.

You are now ready to check out some books. To navigate to a borrower using their borrower number, click in the 'BorrowerNo' field and select a value from the list. You will be moved to that record. To select the books to be taken out, click on the 'BookID' field and choose a value from the list. To continue checking out more books simply click in the 'BookID' field below the last record in the subview and continue.

Note: When the 'BookID' value list is displayed you can type in the first few numbers of the BookID you want and the value list automatically scrolls to the nearest match. Pressing return will select this BookID. Alternatively you needn't open up the value list at all, but simply type in the full BookID.

Creating the Books Check Out layout

Imagine Logo someone comes along with a pile of books to return. It is not necessary to know who is returning them, you just want to find the record in the 'Books lending history' table and fill in the 'Date returned' field value with today's date. You also want to know if the book is overdue so that you can ask for a fine if necessary.

Go to the 'Books lending history' layout and choose Layout => New layout menu. Type Books check in to give you a new layout which is looking at the 'Books lending history' table.

You want to view as many books on the screen as possible and make a few other changes:

1. Choose Layout => Alter type => All sheets from the menu
2. Choose Arrange => Header / Footer => Main header from the menu
3. Press Ctrl-A then Delete to remove all fields from the layout
4. Using the Fields menu in the toolbox, bring back the 'BookID', 'Date returned' and 'Overdue' fields.

Viewpoint will automatically position the field frames just below the header, with the field titles just above.

Select the three fields and change the Frame fill colour to white in the usual way.

To see just the books which are out at the moment, choose Query/Table => Search type NULL into the 'Date returned' field and click on the Perform Search tool.

Note: This layout is attached to the 'Books lending history' table because you copied it from a layout which was. The query or table the layout is attached to is crucial as it determines the records that are shown. It also determines what fields can be put on the layout when you are designing it. if you click on Search, Sort, Join or SQL in Layout view, it has the effect of creating a query called 'Join for layoutname' and attaching it to your layout so that this layout will always show records from this query. For instance a quick way to permanently sort a layout on a given set of fields is to go to this layout in Layout view. Choose Query/Table => Sort from the menu and select the required sort order.

If you want to see the title of the book corresponding to the value of BookID then you need to add the ‘Books’ table to our query for the layout. Do this by choosing Query/Table =>Join from the menu and adding the table ‘Books’.

Note: When you add the table, the join between the table and the 'Books Out' query is created automatically. This is because you added this join into the relationships earlier. This is why it's a good idea to set up the relationships early on.

Click OK to return to the layout, add the ‘Title’ field and position it in a suitable place making it long enough to see a long book title.

Return to Sheets view and you will see a list of books which you took out when you were trying out the ‘Books check out’ layout. Click in the ‘Date returned’ field of a record and press ctrl-shift-d to fill in today's date. When you move onto another record you will see the previous one disappear as the ‘Date returned’ field is non null. The book has been returned.

Creating the Book Search layout

You have one crucial layout missing which is the one for the borrower to use in order to find a wanted book. You might want to search by title, by author, by publisher or a combination of these. Having found the book you may want to know what shelf it is on and whether the book is out at the moment.

Go to the ‘Add new book’ layout. Choose Layout => New layout from the menu and type Books search and click 'OK'. This gives you a new layout which is looking at the ‘Books’ table. You want to view as many books on the screen as possible so arrange the layout as desired.

1. Change the background colour to white
2. Select any field, then set its frame fill colour to white and the font size to 10
3. Choose Layout => Alter type => All Sheets from the menu
4. Choose Arrange => Header / Footer => Main header from the menu
5. Press Ctrl-A then Delete to remove all frames from the layout
6. Double-click at the top of the layout to enter background text and type Books Search, then click on the background to return to select mode.
7. Using the Fields menu in the toolbox, bring back the ‘BookID’, ‘Title’, ‘Author’, ‘Publisher’ and ‘Shelf code’ fields.

Note: It's easier here to set the desired text size before bringing the fields onto the layout, so you won't need to adjust the field frames after changing the text size.

For a given book you also want to know whether it is currently out and when it will be available.

This information is held in the ‘Books Out’ query, so choose the Query/Table => Join menu and add the query ‘Books Out’, then click 'OK' to return to the layout.

Create a formula field called ‘Out’ and give it the formula:

If 'Books Out.ID' = null then "No" else "Yes"

Note: To see how this formula works consider if the book is not out then there is no record in the 'Books Out' query with our 'BookID' value, hence the value of all the fields in the 'Books Out' query will be null and the formula above will return 'No'. On the other hand if the value of the 'Books Out.ID' field is not null then there must be a matching record in the 'Books Out' query and hence the book is out.

Position the 'Out' field next to the 'Shelf code' field (it may be easiest to remove it by selecting it and pressing Delete and then bringing it back from the Fields menu).

Finally add the 'Books Out.Due to be returned' field from the Fields menu so that if the book is out the borrower can see when it should be returned.

You can put value lists on the 'BookID' and 'Title' fields to allow the user to navigate to a record by using a drop down list.

Return to the Sheets View to see a list of all the books.

If you didn't return all the books when you tried out the 'Books check in' layout, these will show up as Out. The user can search for Books by title or any other criteria by choosing Query => Search from the menu and typing in the relevant fields.

Creating buttons to navigate the library database

To finish off the database, create a menu page on the first layout so that when the user first loads the database they are given a choice of layouts to go to at the click of a button. Also create a button on each layout which returns you to the menu page.

Go to the 'Main' layout:

Choose Layout => Edit layout from the menu

Choose Layout => Rename Layout from the menu and type Main Menu

Change the background colour of the layout

Give the layout a siutable title e.g. City Library

To create a button for navigating to the Books check out layout: Click on the frame icon on the tool bar and drag out a rectangle in the shape of a button. Double-click on the frame and type Check out Book.

Choose Effect => Alignment => Centre align from the menu.

Choose Effect => Frame Style from the menu.

Give the frame a Slab.Light SouthEast border.

Select a colour for the Frame Fill.

Make it non-transparent.

Click on 'Border' tab and reduce the scale to 50%.

Click OK to apply and close the dialogue.

Choose Edit => Edit Script from the menu (or press Ctrl-T) and type: On ClickLeft Layout ("Books check out").

Fortunately there is an easy way to create the other 4 buttons you need. Copy the original button, by dragging while holding down Shift, or copying and pasting using Ctrl-C/Ctrl-V.

Alter the text of the button so that you have the following five buttons:

Click on the pointer icon in the toolbox, select each button in turn and press Ctrl-T to alter the script.

You will also need a 'Main Menu' button, so the full set of button names and scripts is as follows:

Select the Main Menu button, press Ctrl-X to cut it onto the clipboard, then paste it to all the other layouts to allow them to return to the main menu.

Conclusion

You should now have a working library management database, and hopefully have learned something about the design process that's involved in creating a relational database.