The aim of this tutorial is to describe how to set up a simple but effective Library management system using relational techniques. In it we create a table to store the books, a table to store the people using the library and a table to store who borrowed what when. The relationship between the 3 tables is very simple but some time is spent creating difference layouts using the tables in different combinations. In particular we 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 DataPower 1 users and people new to DataPower, and takes into account the differences between the Acorn, PC and Mac versions of DataPower.
You can download the final library example file by clicking here, or follow the instructions below to create it yourself.
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's important to make a distinction between a book, which we 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, this would not be allowed.
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 we 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)
When a book is borrowed, today's date is copied into the 'Date borrowed' field, and when it's returned, this field is set back to null. We can then list the outstanding books by searching for books where 'Date borrowed' is non-null.
Our first step is to create a new database with a table for the books in the Library.
Having loaded DataPower 2, create a new database [ PC/Mac: choose File=>New from the menu; Acorn: click on the iconbar icon ].
We are provided with a blank layout with which to create our first table, this will always be the layout which will be shown when the database is first loaded so we leave it blank for now so we can create a nice front page later. To create another layout click on Layout => New Layout and type in Add new book. We should also name our new table by clicking Layout => Rename Table and typing Books.
We 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.
Note: 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'll create next.
Create the following fields:
|Field name||Field type|
Next 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 => Option => Check (or press Ctrl-Shift-C), then type:
len(@) = 10
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")
Give the ‘BookID’ field a formula by selecting it and clicking Field => Type =>Formula (or pressing Ctrl-Shift-G) and typing:
'ISBN' + "/" + STR$('ID')
Give the ‘Purchase price’ field a display format by selecting it and clicking 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!).
We make the fields ‘Title’, ‘ISBN’ and ‘BookID’ display in bold by right clicking on the three fields to select them (or dragging a rectangle round them) and choosing Effect=>Bold (or pressing Ctrl-B).
Next, rearrange the fields so that they look like the ‘Add new book’ layout in the Library database supplied with the tutorial.
To smarten up the layout we click on Arrange => Background Colour and choose a light grey colour for the layout, then we click on the frames icon with no field icon selected and drag out a frame which is just larger than the group of fields. Select this frame and click on Effect => Frame Style and choose a thin border and unclick the transparent box so that the white body of the frame will show. Having done that we must send the frame to the back of the frames so we'll be able to click in the field frames later on: to do this, choose Arrange => Send to back (or press Ctrl-Shift-B).
Finally double-click on the background at the top of the layout and type Office view of all Books; change the font by choosing Effect => Text font and choosing a regular style.
Having created our Books table we can put some data into it, so save the layouts by clicking on OK and giving the database a name.
Next, download the Books tab file by clicking here. If your browser displays the text in a browser window, you can save it by choosing the "Save as HTML" option (don't use Save as plain text, as that would replace the tabs with spaces).
Then when it is saved, import the books as follows:
PC/Mac: Choose File / Import / Import into file, then double-click Books.txt
Acorn: Drag the file Books/txt onto the toolbox of the database window.
A merge dialogue box will come up; click on merge and you will notice that there are now 10 records in 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.
Note that the ‘ID’ field is automatically created by DataPower when you make the table and contains a unique integer value for each record which is created. You can’t add the ‘ID’ field to your layout in the layout editor: however, you can use its value in formula fields as we have done with the ‘BookID’ field.
If you want your copies numbered from 1, so the first copy's BookID ends in "/1", the second in "/2", and so on, you'll have to use a separate integer field to hold the copy number, and use that rather than the 'ID' field within your 'BookID' field. In this case you'll have to enter the copy numbers manually, although this field could default to 1 to ease the most common case.
When you save a new book record DataPower will check that the ‘ISBN’ field contains 10 characters. If it doesn’t the record cannot be saved, which 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'll have to invent some 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 ISSNs to be used in this field (for other types of publications), you can alter the check formula to len(@)=8 or len(@)=10
Next we create another table to hold all the people who are allowed to use the library. Click on Layout => New Table and type Borrowers. We are given a blank layout with which to create our 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 out blank.
Now create the following text fields:
‘Firstname’, ‘Surname’, ‘Address’, ‘Postcode’, ‘Telephone’, ‘Email’ and ‘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 it looks like the Library database example supplied, in the same way as we did in the section above. Also click on Layout => Rename Layout and type Add new Borrower.
Click on OK to save the layouts and return to browse mode.
Now we are ready to put some data in the Borrowers table, so download the Borrowers tab file by clicking here. If your browser displays the text in a browser window, you can save it by choosing the "Save as HTML" option (don't use Save as plain text, as that would replace the tabs with spaces).
Import the Borrowers tab file as follows:
PC/Mac: Choose File / Import / Import into file, then double-click Borrow.txt
Acorn: Drag the file Borrow/txt onto the toolbox of the database window.
A merge dialogue box will come up; click on merge and you will notice that there are now 20 records in the Borrowers table.
Having created a table of books and a table of borrowers we now need one further table which keeps track of which books have been borrowed by which people, so click on Layout => New Table and type in Book lending history . Then create the text fields ‘BookID’, ‘BorrowerNo’, the date fields ‘Date taken out’, ‘Date returned’ and the formula fields ‘Due to be returned’ and ‘Overdue’.
By selecting the field ‘Date taken out’ and clicking on Field => Options => Default (or pressing Ctrl-Shift-G), give the field the following default formula:
If 'BorrowerNo' = null then null else today
By selecting the field ‘Due to be returned’ and clicking on Field => Type => Formula, 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 as it allows DataPower to look up the details of a book given just the value of the ‘BookID’ field, and similarly for the borrowers.
To set the primary keys, choose Query/Table => Relationships, click on Add table and add the 3 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's also a good idea to set up the default joins between the various tables, which will be used later on by DataPower 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'll now see that there's 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.
Now repeat the process to set up the relationship between the Book Lending History and Borrowers tables:
Click OK to save the Relationships and return to editing the Book Lending History layout.
Before we go on to create some useful layouts we should 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 we click on Query/Table =>Edit => New query, click in the Name box at the top and type Books Out, also type NULL into the ‘Date returned’ field. Now click on OK and we are be back looking at the ‘Book lending history’ layout.
We won’t ever actually use this layout to create records: instead we make a more useful layout which will allow us to create ‘Book lending history’ records with a minimum of fuss.
Imagine a borrower coming to our desk asking to take out a clutch of books in her arms: we want to be able to find her details using her BorrowerNo which would be found on her card. Then we check to see how many books she has out already and whether they are overdue or not. If all is well we want to use the BookID values found on the books to create some ‘Book lending history’ records to indicate that she has taken out some more books. We now proceed to make a layout which will achieve these aims.
Change to the ‘Add new Borrower’ layout, click on 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 we wish to create. Delete the white background frame and 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 fields menu on the toolbar. If we wanted to delete the fields for good, including the data contained in them, we can select the fields and press Ctrl-Delete. Extreme caution should be taken before doing this.
Select the 3 remaining fields, click on Effect => Frame style and change the Frame fill colour to grey. Select the titles of the Firstname and Surname fields and press delete, being careful not to delete the fields themselves. Arrange the 3 fields suitably near the top of the page and change the text at the top to Borrowers Record.
Our next task is to display a list of the books that the person has out at the moment.
Select the subview icon from the toolbar 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 we chose the ‘Book lending history’ table then we would see all the books that the borrower has ever taken out. What we want to choose is the set of books which the borrower has out at the moment, i.e. we want to choose the ‘Books Out’ subset. In order to view the ‘Books Out’ query in the subview we need to add it to the relationships. To do this, choose "Define Relationships" from the menu you just opened by clicking on the "Show records from" box.
In the relationships view, click on Add Table, select the Queries tab at the bottom and double-click on the ‘Books Out’ query to add it to the relationships. Position the query just under the ‘Book lending history’ table and copy the links of that table, i.e. 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 base 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.
Now if we click on the fields menu we see that all the fields from the ‘Books Out’ query are available to add to our layout. If we want to be able to see the title of books as well as the BookID we 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 that what we are actually doing here is editing the query called ‘Join for Books check out’. This query was created for us when we added the subview and it is this query which fetches the data we will see when we look at this layout in browse mode. 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 we keep the Subview selected and click on fields in the fields menu
Note that if the subview isn’t selected then the field will appear in the body of the layout not in the subview. If one tries 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. So this should not be done when trying to put the field itself in the subview. New fields appear in the subview after the existing fields, if there is no room in the subview for the extra field then it still gets put at the end but it can’t be seen! In order to find the field we may need to widen the page and then widen the subview until we can see the hidden field. To avoid doing this, make sure there is enough room in the subview before adding the new field.
Note that the names in the field menu have the format ‘tablename.fieldname’ this is so that we can distinguish between two fields with the same name in different tables, i.e. ‘Books.BookID’ and ‘Books Out.BookID’. I will only refer to the fields by their fully qualified name if it is necessary to do so.
Add the following fields to the subview:
‘Books Out.BookID’, ‘Title’, ‘Date taken out’, ‘Due to be returned’, ‘Date returned’, ‘Overdue’
Select all the fields in the subview, click on Effect => Frame style and change the Frame fill colour to white. 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 and select a thin border. Finally select the 3 fields outside the subview and click on Effect =>Text font to change it to a 16 point bold style.
Select the ‘BorrowerNo’ and ‘BookID’ fields, click on Field => Options => Values and select the "Choose a value from the database" option. This will allow us 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 we were selecting. This is the reason we need to have a ‘BookID’ field in the database.
Another reason why it's useful to use the BookID field to look up books is that you can use a barcode reader to read the BookID of a book from a printed label on its spine.
Click on OK to save the layouts and we are in a position to start checking out books. To navigate to a borrower by using her borrower number click in the ‘BorrowerNo’ field and select a value from the list: we are moved to that record. To select the books which are 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 select a BookID value from the list.
Note that when the ‘BookID’ value list is displayed we can type in the first few numbers of the BookID we want and the value list automatically scrolls to the nearest match. Pressing return will select this BookID. Alternatively we needn’t open up the value list at all, but simply type in the full BookID that we want.
Now imagine that someone comes along with a group of books to return, it is not necessary to know who is returning them, we just want to find the record in the ‘Books lending history’ table and fill in the ‘Date returned’ field value with today’s date. We also want to know if the book is overdue so that we can ask for a fine if necessary.
Go to the ‘Books lending history’ layout, click on Layout => New layout and type Books check in, this gives us a new layout which is looking at the ‘Books lending history’ table.
We want to view as many books on the screen as possible so we create a spreadsheet layout as follows:
DataPower will automatically position the field frames just below the header, with the field titles just above.
Select the 3 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 (or press F6), type NULL into the ‘Date returned’ field and click OK.
Note this layout was attached to the ‘Books lending history’ table because we 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 we are designing it. When one clicks on Search, Sort, Join or SQL in edit layout mode it has the effect of creating a query called ‘Join for layoutname’ and attaching it to our 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 edit layout mode click on Query/Table => Sort and select the required sort order.
If we want to see the title of the book corresponding to the value of BookID then we need to add the ‘Books’ table to our query for the layout. Do this by clicking on Query / Table =>Join and adding the table ‘Books’.
Note that when we add the table the join between the table and the ‘Books Out’ query is created automatically, because we 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 the correct place making it long enough to see a long book title.
Click OK to save the layouts 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 todays 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.
We have one crucial layout missing which is the one for the borrower to use in order to find the book she wants. She might want to search by title, by author, by publisher or a combination of these. Having found the book she will want to know what shelf it is on and whether the book is out at the moment.
Go to the ‘Add new book’ layout, click on Layout => New layout and type Books search, this gives us a new layout which is looking at the ‘Books’ table. We want to view as many books on the screen as possible so we create a spreadsheet layout as we did earlier:
Note: It's easier here to set the desired text size before bringing the fields onto the layout, so we won't need to adjust the field frames after changing the text size.
For a given book we also want to know whether it's currently out and when it will be available.
This information is held in the ‘Books Out’ query, so click on Query/Table => Join 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"
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 bring on the ‘Due to be returned’ field from the field 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.
Click OK to save the layouts and you should 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 clicking on Query => Search and typing in the relevant fields.
To finish off the database we will 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 we will create a button on each layout which returns us to the menu page.
To create a button for navigating to the Books check out layout:
On ClickLeft Layout ("Books check out")
Phew! Fortunately there's an easy way to create the other 4 buttons we need:
Note: It's tempting to just press Ctrl-T while you're editing the button's text, but this does not work correctly - what happens is that when you start editing the formula, the caret moves into the formula editor window, and as a result the script ends up being associated with the layout background, rather than the button itself.
You'll also need a "Main Menu" button, so the full set of button names and scripts is as follows:
|Check out book||On ClickLeft Layout ("Books check out")|
|Check in book||On ClickLeft Layout ("Books check in")|
|Book search||On ClickLeft Layout ("Books search")|
|Add new book||On ClickLeft Layout ("Add new book")|
|Add new borrower||On ClickLeft Layout ("Add new borrower")|
|Main Menu||On ClickLeft Layout ("Main Menu")|
Select the Main Menu button, press Ctrl-X to cut it onto the clipboard, then paste it into the top-left of all the other layouts to allow them to return to the main menu.
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.
If you have any suggestions as to how we could improve the tutorial, or indeed DataPower itself, you can send them to email@example.com.