For a second example we will look at a simple library system. This will have three files or, to use database terminology, tables. There will be one table for the borrowers, another for the items and a third for the loans. Analysis of the problem would identify two entities with a many:many relationship between them, namely borrowers and items borrowed. There are many borrowers who can borrow many items and each item can be borrowed by many borrowers. This analysis, however, leaves out the loan entity, which links borrowers to items. A single borrower can make many loans and each item can be loaned many times. Thus a many:many relationship is decomposed into two one:many relationships. It is not possible to implement directly a many:many relationship in a relational database and it will always be necessary to find a way of decomposing it.
The files we shall design will follow the relational model that we would naturally use if using a tool such as Access or a SQL product such as SQLite. The borrowers file/table is defined as follows:
type
TBorrower=record
BorrowerCode:string[5]; //primary key
Name:string[25];
Address1, Address2, Address3:string[20];
end;
TBorrowerFile=file of TBorrower;
The library items file/table:
type
Tlibraryitem=record
accessioncode:string[6]; //primary key
author, title:string[30];
onloan:char; //single character, Y/N
end;
Tlibraryitemsfile=file of Tlibraryitem;
And the loans file/table:
type
TItemLoan=record
accessioncode:string[6]; //primary key, foreign key to borrower file
borrowercode:string[5];
//primary key, foreign key to library item file
datedueback:TDateTime; //primary key; this basic
item does not fit into the borrower or item files
returned:string[1];
//single character, Y/N
end;
TItemLoanFile=file of TItemLoan;
Note the composite key for the loans file. The primary key uniquely identifies a record in a file and it follows that a particular primary key value can only occur just once in a table. If the primary key were the accession key alone then an item could only be borrowed once as the key could not appear in any other record. If the primary key were the borrower code then a borrower could borrow only one item as that key could not appear again. If the primary key were the composite of accession code and borrower code then the item could be borrowed just once by any borrower. Adding the date to accession code and borrower code means that an item can be borrowed by any borrower on more than one date (though it is not possible for the same borrower to borrow the same item more than once on one day!)
Any of these files can be created, populated and displayed in the same way as the persons file in the first example of sequential file processing. In this way we could build up a set of records for borrowers, items and loans. The new thing we can do with this example is to link the data in different files to produce lists of items borrowed by different borrowers.
A menu is provided with options to create new copies of each file.
When new records are created a list of the current records is displayed as a convenience to the user, so records are not added twice. There are other ways to do this such as checking for the presence of a field value. Using a database has a advantage here as a DBMS allows (and requires) the defining of a primary key in each table and prevents the user entering duplicates in this field. Using raw files requires the programmer to process primary keys with bespoke code.
Adding borrowers and library items is fairly straightforward, adding and returning loans is more complicated.
A listbox is used to display items on the form and the list is updated each time the form is activated:
procedure TForm1.FormActivate(Sender: TObject);
//display borrowers in file
var lbstring:string[50];
begin
listbox1.Clear;
assignfile(borrowerfile,'borrowers.dat'); //change file name & path to
suit
reset(borrowerfile);
while not eof (borrowerfile) do
begin
read(borrowerfile,borrower);
lbstring:=borrower.BorrowerCode+' '+borrower.name + ' ' +
borrower.address1 + ' ' + borrower.Address2 + ' ' + borrower.Address3;
listbox1.Items.Add(lbstring);
end;
end;
procedure TForm3.FormActivate(Sender: TObject);
//display library items in file
var lbstring:string[50];
onloanstring:string[10];
begin
listbox1.Clear;
assignfile(libraryitemsfile,'libraryitemsfile.dat'); //change file name &
path to suit
reset(libraryitemsfile);
while not eof (libraryitemsfile) do
begin
read(libraryitemsfile,libraryitem);
if libraryitem.onloan='Y' then onloanstring:='on loan' else
onloanstring:='in library';
lbstring:=libraryitem.accessioncode +' '+ libraryitem.author + ' ' +
libraryitem.title +' '+onloanstring;
listbox1.Items.Add(lbstring);
end;
end;
(Author's project has form2 as home page.)
When an item in the library is borrowed two things must happen:
These actions can be in response to a click of a button. Fines could be added as a further refinement later.
The first section of code adds a new loan record to the end of the loans file:
procedure TForm4.Button1Click(Sender: TObject);
//when item is loaned add new loan item and update
onloan field in library item
var
libraryitemsfile:TLibraryItemsFile;
libraryitem,updatedlibraryitem:TLibraryItem;
libraryitemsfilenew:TLibraryItemsFile;
libraryitemnew:TlibraryItem;
begin
if (edit1.Text='') then
showmessage('Enter an accession code')
else
begin
assignfile(loanitemsfile,'loanitemsfile.dat');
//change file name & path to suit
reset(loanitemsfile);
with loanitem do
begin
accessioncode:=edit1.Text;
borrowercode:=edit2.text;
datedueback:=datetimepicker1.date;
returned:='N';
end;
seek(loanitemsfile,filesize(loanitemsfile));
//find end of file and add loan details
write(loanitemsfile,loanitem); //add new record
closefile(loanitemsfile);
The borrower code and library item code might be more efficiently read using a bar code scanner. The second part of the code updates the library items file by setting the onloan field to 'Y'. The code follows the sequence:
//update item onloan field to Y
assignfile(libraryitemsfile,'libraryitemsfile.dat');
//change file name & path to suit
reset(libraryitemsfile);
assignfile(libraryitemsfilenew,'libraryitemsfilenew.dat');
rewrite(libraryitemsfilenew);
while not eof (libraryitemsfile) do
//copy & update file
begin
read(libraryitemsfile,libraryitem);
if libraryitem.accessioncode = loanitem.accessioncode then
begin
updatedlibraryitem:=libraryitem;
updatedlibraryitem.onloan:='Y';
write(libraryitemsfilenew,updatedlibraryitem);
end
else
write(libraryitemsfilenew,libraryitem);
end;
closefile(libraryitemsfile);
closefile(libraryitemsfilenew);
assignfile(libraryitemsfilenew,'libraryitemsfilenew.dat');
//change file name & path to suit
reset(libraryitemsfilenew);
assignfile(libraryitemsfile,'libraryitemsfile.dat');
rewrite(libraryitemsfile); //copy new file to old
while not eof(libraryitemsfilenew) do
begin
read(libraryitemsfilenew,libraryitemnew);
write(libraryitemsfile,updatedlibraryitem)
end;
end;
assignfile(libraryitemsfilenew,'libraryitemsfilenew.dat');
rewrite(libraryitemsfilenew); //erase new file for
later use
edit1.Clear;edit2.Clear;
edit1.SetFocus;
end;
When an item is returned two things must happen:
The code for this is similar to that for lending an item but the interface has been changed so that the user can select a borrower from a drop down list and then select items from a list of items on loan to the borrower chosen. This works fine with short lists but would not be practical with long lists of members and items. It is more likely that an edit box would be used along with a bar code reader to scan the borrower code and library item code.
When the form is activated the first list box is populated with the names of the borrowers:
procedure TForm6.FormActivate(Sender: TObject);
var lbstring1, lbstring2 : string[50];
lbcode:string[5];
begin
listbox1.clear;
listbox2.Clear;
assignfile(borrowerfile,'borrowers.dat'); //change file name & path to
suit
reset(borrowerfile);
while not eof (borrowerfile) do
begin
read(borrowerfile,borrower);
lbstring1:=borrower.BorrowerCode+' '+borrower.name + ' ' +
borrower.address1 + ' ' + borrower.Address2 + ' ' + borrower.Address3;
listbox1.Items.Add(lbstring1);
end;
end;
When a borrower in the list box is clicked the following code runs:
procedure TForm6.ListBox1Click(Sender: TObject);
var lbstring1:string[50];
lbstring2:string[11];
lbcode:string[5];
i,j:integer;
begin
listbox2.Clear;
i:=listbox1.ItemIndex;
lbstring1:=listbox1.Items[i];
for j:= 1 to 5 do
lbcode:=lbcode+lbstring1[j]; //extract
accession code from data in list box
assignfile(loanitemsfile,'loanitemsfile.dat');
reset(loanitemsfile);
while not eof (loanitemsfile) do
begin
read(loanitemsfile,loanitem);
if (loanitem.borrowercode = lbcode) and (loanitem.returned='N') then
begin
lbstring2:=loanitem.accessioncode + ' ' + loanitem.borrowercode;
listbox2.Items.Add(lbstring2);
//add item code & borrower code to listbox
end;
end;
closefile(loanitemsfile);
end;
The second list box shows a list of items on loan to the borrower the chosen from the first list box. When an item in the second list box is clicked two things happen:
The code here is similar to that for loaning an item.
procedure TForm6.ListBox2Click(Sender: TObject);
//return selected item
var lbaccessioncode:string[5];
lbstring:string[11];
i,j:integer;
libraryitemsfilenew:TLibraryItemsFile;
libraryitemnew, updatedlibraryitem:TlibraryItem;
loanitemsfilenew:TItemLoanFile;
updatedloanitem:TItemLoan;
begin
i:=listbox2.ItemIndex;
lbstring:=listbox2.Items[i];
//get item accession code from list box 2
for j:= 1 to 5 do
lbaccessioncode:=lbaccessioncode+lbstring[j];
assignfile(libraryitemsfile,'libraryitemsfile.dat'); //change file name &
path to suit
reset(libraryitemsfile);
assignfile(libraryitemsfilenew,'libraryitemsfilenew.dat');
rewrite(libraryitemsfilenew);
while not eof (libraryitemsfile) do //copy & update file
begin
read(libraryitemsfile,libraryitem);
if libraryitem.accessioncode = lbaccessioncode then
begin
showmessage('updating item record...');
updatedlibraryitem:=libraryitem;
updatedlibraryitem.onloan:='N';
write(libraryitemsfilenew,updatedlibraryitem);
end
else
write(libraryitemsfilenew,libraryitem);
end;
closefile(libraryitemsfile);
closefile(libraryitemsfilenew);
assignfile(libraryitemsfilenew,'libraryitemsfilenew.dat'); //change file
name & path to suit
reset(libraryitemsfilenew);
assignfile(libraryitemsfile,'libraryitemsfile.dat'); //
rewrite(libraryitemsfile); //copy new file to old
while not eof(libraryitemsfilenew) do
begin
read(libraryitemsfilenew,libraryitemnew);
write(libraryitemsfile,updatedlibraryitem)
end;
closefile(libraryitemsfile);
closefile(libraryitemsfilenew);
assignfile(libraryitemsfilenew,'libraryitemsfilenew.dat');
rewrite(libraryitemsfilenew);
closefile(libraryitemsfilenew);
//update loan item to show item returned
assignfile(loanitemsfile,'loanitemsfile.dat'); //change
file name & path to suit
reset(loanitemsfile);
assignfile(loanitemsfilenew,'loanitemsfilenew.dat');
rewrite(loanitemsfilenew); //copy & update file
while not eof (loanitemsfile) do
begin
read(loanitemsfile,loanitem);
if loanitem.accessioncode = lbaccessioncode then
begin
showmessage('updating loan record...');
updatedloanitem:=loanitem;
updatedloanitem.returned:='Y';
write(loanitemsfilenew,updatedloanitem);
end
else
write(loanitemsfilenew,loanitem);
end;
assignfile(loanitemsfilenew,'loanitemsfilenew.dat');
//change file name & path to suit
reset(loanitemsfilenew);
assignfile(loanitemsfile,'loanitemsfile.dat');
rewrite(loanitemsfile); //copy new file to
old
while not eof(loanitemsfilenew) do
begin
read(loanitemsfilenew,updatedloanitem);
write(loanitemsfile,updatedloanitem)
end;
closefile(loanitemsfile);
closefile(loanitemsfilenew);
assignfile(loanitemsfilenew,'loanitemsfilenew.dat');
rewrite(loanitemsfilenew);
closefile(loanitemsfilenew);
showmessage('Item returned');
end;
This shows the amount of file 'housekeeping' that has to take place in order to process changes in the data. Processing sequential files is seen to be quite tedious, though the routines can be copied and adapted to new situations. The use of database management tools and associated programming techniques simplifies these routines.
Producing a list of items on loan to one or more borrowers is quite complex as it requires links between different files via the primary and foreign key mechanism.
The method is:
open borrowers file
for each record in borrowers file
open loans file
for each record in loan file
if (borrower.code matches loan.borrowercode) and (the item has not been
returned) then
build a string to display them from borrower name and loanitem.code
The title and author of the item is in the library items file so a further link must be made:
open borrowers file
for each record in borrowers file
open loans file
for each record in loan file
if (borrower.code matches loan.borrowercode) and (the item has not been
returned) then
open items file
for each record in items file
if item.itemcode = loan.itemcode then
build a string to display them from borrower.name, item.title
The code:
procedure TForm5.FormActivate(Sender: TObject);
var lbstring:string[100];
begin
listbox1.Clear;
assignfile(borrowerfile,'borrowers.dat'); //change file name & path to
suit
reset(borrowerfile);
while not eof (borrowerfile) do
begin
read(borrowerfile,borrower);
assignfile(loanitemsfile,'loanitemsfile.dat');
reset(loanitemsfile);
while not eof(loanitemsfile) do
begin
read(loanitemsfile,loanitem);
if (borrower.BorrowerCode=loanitem.borrowercode) and (loanitem.returned='N')
then
begin //match between borrower
and loan item plus item not returned so open library items file
assignfile(libraryitemsfile,'libraryitemsfile.dat');
reset(libraryitemsfile);
while not eof(libraryitemsfile) do
begin
read(libraryitemsfile,libraryitem);
if libraryitem.accessioncode=loanitem.accessioncode
then
begin //match
between library item and loan item so display name, item and date due back
lbstring:= borrower.Name +' '+ libraryitem.title
+ ' ' + datetostr(loanitem.datedueback);
listbox1.Items.Add(lbstring);
end;
end;
closefile(libraryitemsfile);
end;
end;
closefile(loanitemsfile);
end;
closefile(borrowerfile);
end;
This shows that linking data files in Pascal code is complex and tedious. These operations are rather easier when a database is used in place of files, along with associated programming technqiues.
Notice how for each record in the borrowers file the whole of the loan items file is read in order to locate every possible item that borrower may have on loan. The loan file is closed and re-opened for each item in the borrowers file. Notice that for each loan item the library items file is opened and the file is searched to find the record that corresponds with the loan item.
