Information Technology Center

Microsoft Access - Introductory level

Access Lesson Three

DESCRIPTION:

This lesson teaches how a database can be used to store data in a library database system and introduces the use of primary key.

SPECIFIC OBJECTIVES:

At the end of the lesson students should be able to :

• Create a library database
• Add a table for books
• Add a table for users
• Use primary keys

PREREQUSITE KNOWLEDGE:

Before attempting this lesson you should know :

• The difference between data and information
• How to switch between design and datasheet views
• The different access data types
• How to modify the structure of a table

Microsoft Access - A library database system

3.0 Designing a database

There are a number of questions we must ask ourselves before designing a database. The most importatant question is :
What is the purpose of the database?

Other questions are :

1. What questions would you like the database to answer for you
2. What reports do you want the database to produce
3. Who will use the database
4. What training would staff need
5. How will the network be set up

3.1 What is a library database?

A library database management system is primarily a computerised collection of books, magazine and periodical data. Library staff maintain records of books and borrowers. By using such a system, library personnel are able to answer queries about the status of books more easily than if a manual system was being used.

Components of the system

A database server with very large secondary storage capacity will store the information which will be accessed by a number of laptop and desktop computers attached via a local area network with printers. Staff responsible for data entry use ergonomically designed keyboards, desks and chairs in well ventilated and well lit rooms.

3.2 Create a new database

1. Click on the START button
2. Click on PROGRAMS
3. Select Microsoft Access
4. Choose create a database from scratch
5. Provide a File name : dbLibrary
6. Specify a location from the options available

When a new database is created from scratch there are no objects in it. We must create a table before we can add data. We must examine the data to determine the structure needed to hold each data item.

Books table details

When we layout the data in tabular format it is easy for us to determine exactly what fields we need for each table. For our library database we will create our first table with eight fields.

From the tabular data we determine the names and data type of each field as follows :

3.3 Creating a new table : tblBooks

1. Book ID - text
2. ISDN number - text
3. Title - text
4. Author - text
5. Publisher - text
6. Category - text
7. Cost - currency
8. Purchased - date/time

Members table details

Each member is assigned a unique member ID.

From the tabular data we determine the field names and data type of each field as follows :

3.4 Creating a new table : tblMembers

1. Member ID - text
2. Surname - text
3. First - text- first name
4. dob - date/time - date of birth
5. Street Address - text
6. Town Address - text
7. Phone number - text
8. email - text

REMEMBER : Common data types

After you select a data type from the drop down list the 'Field Properties' pane appears at the bottom of the window. The data type text is selected by default. The most common data types are listed below.

3.5 Primary Key

A primary key is a selected field or set of fields that uniquely identifies a record in a table. A primary key is not always used but it is often a good idea to use a primary key in a table. :

In the members table the member ID field is to be used as the primary key field.

1. Open tblMembers in design view
2. Select the Member ID field
3. Click on the Primary Key Icon (key) on tool bar
4. OR choose Primary Key from the EDIT menu

Primary keys speeds up data storage and retrievel. They also enable a relationship between tables so that they can be joined.

Test Yourself