Databases : Library Example

A library database is being set up. The database will be created with a relational database package.

Identifying The Files

The database needs to store information about three different things. These are the library's members, books and the loans it has made. The database designer has therefore identified these three files that the system will require :

• The members file will store details of all of the people who can borrow books.
• The books file will store details of all of the books that the library owns.
• The loans file will store details of which members are currently borrowing which books.

File Structure

For each of the three files the designer must now decide what information the file should contain. This will include the field names, types and lengths. A primary key must also be identified for each file.

Members File

The members file will store information about each borrower. The primary key field must be Membership Number as it is the only field which will uniquely identify each person. Member Name can not be a key field as two borrowers could have the same name.

Books File

The books file will store information about each book the library owns. The primary key field must be Copy Number which can uniquely identify each copy of each book.

The category of each book, e.g. "History", "Geography", "Science Fiction" will be coded as a three letter code e.g. "HIS" or "GEO" to reduce storage space and speed up data entry.

Loans File

The loans file will store details of who has which books on loan at the moment. When a book is borrowed a record will be created in the loans file which will store the Membership Number of the borrower, the Copy Number of the book and the date the book is due back. Copy Number must be the primary key field. Membership Number can not be a key as a borrower may borrow more than one book at a time.

Relationships

The following relationships exist between the three files :

• The Membership Number in the Members File is linked to the Membership Number in the Loans File.
• The Copy Number in the Books File is linked to the Copy Number in the Loans File.

These relationships can be used to find out information using the data in the database. For example to find out who has the book "100 Facts about Animals" the database software must :

• Look up the Copy Number of the book in the Books File.
• Look up the found Copy Number in the Loans File to locate the Membership Number of the person who has the book on loan. If no record exists for the Copy Number in the loans file then the book is not on loan.
• Look up the found Membership Number in the Members File to find the name of the member who has the book.

GCSE ICT Companion 04 - (C) P Meakin 2004