INTRODUCTION

Whether you want to organise your educational records, maintain departmental

information, or co-ordinate class projects, Access provides the structure and
flexibility you need. You can store all kinds of information in an Access database.

Microsoft Access is a database program, which is used for record keeping and data
manipulation. Using Microsoft Access, you can manage all your information from a
single database file. Within the file, device your data into separate storage
containers called tables, view, add update table data using online forms, find and
retrieve just the data you want using queries, and analyse or print data in a specific
layout using reports.

What Is A Database?

Database is an organised collection of related information (data). Here the stress is

on the term organised.

The organised information or database serves as a base from which desired

information can be retrieved; many meaningful conclusions can be derived by
processing data.
To amplify the definition, we can say that a database is a collection of information
related to a particular subject or purpose, such as tracking customer orders or
maintaining a music collection.

How Does a Database Work?

A database consists of a collection of tables, queries, forms, and reports that you
use to manage and present data. These components are also called database
objects. When you build a database, you create as many of these objects as you
need and access stores them in one database file.

Each object you create in the database is dependent on other objects. That is, a
query, form or report draws information from table, so changing data in any of
these objects changes the data in all of these objects. Because the objects in a
database are related to one another, building a database requires careful planning.

To store your data, create one table for each type of information you track. To bring
the data from multiple tables, together in a query, form, or report, you define
relationships between the tables.
To find and retrieve just the data that meets conditions you specify, including data
from multiple tables, create a query. A query can also update or delete multiple
records at the sane time, and perform built-in or custom calculation on your data.

To easily view, enter, and change data directly in a table, create a form. When you
open a form Microsoft Access retrieves the data from one or more tables and
displays it on screen using the layout you choose in the form Wizard or using a
layout that you created from scratch.
To analyse your data or present it a certain way in print, create a report. For
example, you might print one report that groups data and calculates totals, and
another report with different data formatted for printing mailing labels.

To work with all the objects in a Microsoft Access database, use the database
window. Click a tab (for example, Tables) to view a list of the available objects of
that type. Using the buttons to the right of the list, you can open or modify existing
objects and create new ones.

Brief Description of the Components (items) of a Database

A database is made up of items, which work together, to make up a customised

program. These are:

 Tables
 Forms
 Reports
 Queries
 Modules
 Macros

Tables
A table is a collection of records or related records. It must be created or
defined. It is used to store database information.

Forms
A form is used as a medium for entering and editing records to the table. It is
usually linked with a table. You can also use a form to view one record at a time.
Reports
A report is an effective way to present your data in a printed format. Because
you have control over the size and appearance of everything on a report, you
can display the information the way you want to see it.

A report is usually the final document, which is printed and submitted to

interested persons.

Queries
A query is used to filter data so that you retrieve selected records or fields from
the database. It can also be used as a source of records for forms and reports.

Macros
A macro is used to automate tasks that are performed on a regular basis in a
database.

Modules
A module is used to automate a group of related procedures.

HOW TO DESIGN A DATABASE

 Before you use Microsoft Access to actually build the tables, forms, and other
objects that will make up your database, it is important to take time to design your
database. A good database design is the keystone to creating a database that does
your work effectively accurately, and efficiently.

Steps in Designing a Database

1. Determine the purpose of your database

The first step in designing a Microsoft Access database is to determine the
purpose of the database and how it’s to be used. You need to know what
information you want from the database. From that, you can determine what
subjects you need to store facts about (the tables) and what facts you need
to store about each subject (the field in the tables).

Talk to people who will use the database. Brainstorm about the questions you
would like the database to answer. Sketch out the reports you would like it to
produce. Gather the forms you currently use to record your data. Examine
well design databases similar to the one you are designing.

2. Determine the tables you need.

Determining the tables can be the trickiest step in the database design
process. That’s because the results you want from your database – The
reports you want to print, the forms you want to use, the questions you want
answered – don’t necessarily provide clues about the structure of the tables
that produce them.

You don’t need to design the tables using Microsoft Access. In fact, it may be
better to set out and rework your design on paper first. When you design
your tables, divide up pieces of information by keeping these fundamental
design principles in mind:

* A table should not contain duplicate information, and information

should not be duplicated between tables.

* When each piece of information is stored in only one table, you update
it in one place. This is more efficient, and also eliminates the possibility
of duplicate entries that contain different information. For example,
you would want to store each customer’s address and phone number
once, in one table.

* Each table should contain information about one subject.

* When each table contains facts about only one subject, you can
maintain information about each subject independently from other
subjects. For example, you would store customers’ addresses in a
different table from the customers’ orders, so that you could delete
one order and still maintain the customer information.

3. Determine the fields you need

Each table contains information about the same subject, and each field in a
table contains individual facts about the table’s subjects. For example, a
customer table may include company name, Address, city, state, and phone
numbers fields.
When sketching out the fields for each table, keep this in mind:

 Relate each field directly to the subject of the table.

 Don’t include derived or calculated data (data that is the result of an
expression)
 Include all the information you need.
 Store information in smallest logical parts (for example, first name and
last name, rather than name).

4. Identify fields with unique values

In order for Microsoft Access to connect information stored in separate tables,
for example, to connect a customer’s orders, each table in your database
must include a field or set a field that uniquely identifies each individual
record in a table. Such a field or set of fields is called a primary key.

5. Determine the relationships between tables

Now that you’ve divided your information into tables and identified primary
key fields, you need a way to tell Microsoft Access how to bring related
information back together again in meaningful ways. To do this, you define
relationships between tables.

6. Refine the design

After you have designed the tables, fields, and relationships you need, it’s
time to study the design and detect any flaws that might remain. It is easier
to change your database design now, rather than after you have filled the
tables with data.

Use Microsoft Access to create your tables, specify relationships between the
tables, and enter a few records of data in each table. See if you can use the
database to get the answers that you want. Create rough drafts of your forms
and reports and see if they show the data you expect. Look for unnecessary
duplications of data and eliminate them.

7. Enter data and create other database objects

When you are satisfied that the tables structures meet the design goals
described here, then it’s time to go ahead and add all your existing data to
the tables. You can then create any queries, forms, reports, macros, and
modules that you may want.

8. Use Microsoft Access analysis tools

Microsoft Access includes two tools that can help you to refine your database
design. The Table Analyser Wizard can analyse the design of one table at a
time, can propose new table structures and relationships if appropriate, and
can restructure tables if that makes sense.
 The Performance Analyser can analyse your entire database and make
recommendations and suggestions for improving it. The wizard can also
implement these recommendations and suggestions.

About relationships in an Access database

Why define relationships?

After you've set up different tables for each subject in your Microsoft Access database,
you need a way of telling Microsoft Access how to bring that information back
together again. The first step in this process is to define relationships between your
tables. After you've done that, you can create queries, forms, and reports to display
information from several tables at once. In the two tables we have if you needed to
know the salesman dealing with apples you would have to retrieve the information
from the salesman table.

How do relationships work?

In the above example, the fields in different tables must be coordinated so that they
show information about eg the apples salesman. This coordination is accomplished
with relationships between tables. A relationship works by matching data in key fields
usually a field with the same name in both tables. (Not always the case) In most
cases, these matching fields are the primary key from one table, which provides a
unique identifier for each record, and a foreign key in the other table. For example,
salesmen can be associated with products they're responsible for by creating a
relationship between the Salesmen table and the Products table using the
EmployeeID fields.

A one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-

many relationship, a record in Table A can have many matching records in Table B,
but a record in Table B has only one matching record in Table A.

A many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching

records in Table B, and a record in Table B can have many matching records in
Table A. This type of relationship is only possible by defining a third table (called a
junction table) whose primary key consists of two fields — the foreign keys from
both Tables A and B. A many-to-many relationship is really two one-to-many
relationships with a third table.

A one-to-one relationship
In a one-to-one relationship, each record in Table A can have only one matching
record in Table B, and each record in Table B can have only one matching record in
Table A. This type of relationship is not common, because most information related
in this way would be in one table.

Defining relationships

You define a relationship by adding the tables that you want to relate to the
Relationships window, and then dragging the key field from one table and dropping
it on the key field in the other table

The kind of relationship that Microsoft Access creates depends on how the related
fields are defined:
A one-to-many relationship is created if only one of the related fields is a primary
key or has a unique index.
A one-to-one relationship is created if both of the related fields are primary keys or
have unique indexes.
A many-to-many relationship is really two one-to-many relationships with a third
table whose primary key consists of two fields — the foreign keys from the two
other tables.

Note If you drag a field that isn't a primary key and doesn't have a unique index to another field
that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created.
In queries containing tables with an indeterminate relationship, Microsoft Access displays a
default join line between the tables, but referential integrity won't be enforced, and there's no
guarantee that records are unique in either table.

DATABASE CONTROLS
These are security measures to ensure security in databases

A number of controls are embedded into DBMS, these includes:

 Authorization - granting of privileges and ownership, authentication
 Views
 Backup and Recovery
 Checkpoints - the point of synchronization between database and
transactions log file. All buffers are force written to storage
 Integrity checks e.g. relationships, lookup tables, and validations.
 Encryption - coding of data by special algorithm that renders them
unreadable without decryption key
 Journaling - maintaining log file of all changes made
 Database Repair

Advantages Of The Database Systems

1. Centralized Control - Via the DBA it is possible to enforce centralized management and
control of data. This means that necessary modifications, which do not affect other
application changes, meet the data independence DBMS requirement.
2. Reduction of redundancies - Unnecessary duplication of data is avoided effectively
reducing total amount of data required, consequently the reduction of storage space. It also
eliminates extra processing necessary to trace the required data in a large mass of data. It
also eliminates inconsistencies. Any redundancies that exist in the DBMS are controlled and
the system ensures that his multiple copies are consistent.

3. Shared data - In a DBMS, sharing of data under its control by a number of application
programs and user is possible e.g. backups.

4. Integrity - Centralized control can also ensure that adequate checks are incorporated to the
DBMS provide data integrity. Data integrity means that the data contained in the database is
both accurate and consistent e.g. employee age must be between 28-25 years.

5. Security - Only authorized people must access confidential data. The DBA ensures that
proper access procedures are followed including proper authentication schemes process that
the DBMS and additional checks before permitting access to sensitive data. Different levels
of security can be implemented for various types of data or operations.

6. Conflict Resolution - The DBA is in a position to resolve conflicting resolve conflicting

requirements of various users and applications. It is by choosing the best file structure and
access method to get optimum performance for the response. This could be by classifying
applications into critical and less critical applications.

7. Data Independence - It involves both logical and physical independence logical data
independence indicates that the conceptual schemes can be changed without affecting the
existing external schemes. Physical data independence indicates that the physical storage
structures/devices used for storing the data would be changed without necessitating a change
in the conceptual view or any of the external use.

Disadvantages of Database Systems

1. Cost - in terms of:

 The DBMS - software

 Purchasing or developing S/W
 H/W
 Workspace (disks for storage)
 Migration (movement from tradition separate systems to an integrated one)

2. Centralization Problems

You would require adequate backup incase of failure

You would require increased severity of security breeches and disruption of operation of the
organization because of downtimes and failures.

3. Complexity of Backup and recovery