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.
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'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
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'
Creating the Books Table
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.
Create the following fields:
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),
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
If len(@) = 10 then True else Error("ISBN codes must have 10
Give the ‘BookID’ field a formula by selecting it and clicking Field
=> Type =>Formula (or pressing Ctrl-Shift-G) and typing:
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
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
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
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
Creating the Borrowers Table
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’
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
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
A merge dialogue box will come up; click on merge and you will notice
that there are now 20 records in the Borrowers table.
Creating the Book Lending History 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:
By selecting the field ‘Due to be returned’ and clicking on Field =>
Type => Formula, give the field the following formula
By selecting the field ‘Overdue’ and clicking on Field => Type =>
Formula, give the field the following formula
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.
Setting up the relationships
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
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.
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
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:
- Drag the BorrowerID field from the Book Lending History table onto
the BorrowerID 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
Creating the Books Out query
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.
Creating the Books check out layout
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.
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
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
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.
Creating the Books check in layout
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:
- Choose Layout => Alter type => All records
- Choose Arrange => Header / Footer => Main header
- Press Ctrl-A then Delete to remove all fields from the
- Using the fields menu in the toolbox, bring back the ‘BookID’, ‘Date
returned’ and ‘Overdue’ fields
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
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
Creating the Book search layout
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:
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
Create a formula field called ‘Out’ and give it the formula
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.
Creating buttons to navigate about the library database
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.
- Go to the ‘Untitled’ layout
- Click on Layout => Edit layout
- Choose Layout => Rename Layout and type Main Menu
- Change the background colour of the layout to grey
- Type City Library at the top in a 24 point regular style.
To create a button for navigating to the Books check out layout:
Phew! Fortunately there's an easy way to create the other 4 buttons
- 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 to the desired value
- Click on the pointer icon in the toolbox, select the button and press
Ctrl-T to alter the script
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")
||On ClickLeft Layout ("Books search")
|Add new book
||On ClickLeft Layout ("Add new book")
|Add new borrower
||On ClickLeft Layout ("Add new borrower")
||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.