Database Application

Every organization maintains data for its business, employees and finance. This data need to be
maintained in such a manner that it is easily available and can be presented in desired formats.
The data also needs to be updated regularly.
Traditionally data were maintained in paper files. The following are the disadvantages of manual
data maintenance by using paper files:
1. Data redundancy
2. Slow data access
3. Security problems
4. Integrity problems
The advent of database management system paved a way to overcome these disadvantages.

Introduction to Database

What is Database?
A database is a collection of related data that is organized, so that it can easily be accessed,
managed and updated.
For example, a database can contain data about all the students in a school, data about telephone
numbers in a directory, data about all products in a manufacturing company, or data about
weather conditions in an area or a city (say Addis Ababa).
Microsoft Office Access 2007 is one of the database creation and management programs.
It is a Database Management System (DBMS) included with Microsoft Office suite. Interacting
with a DBMS occurs when one performs daily routine activities, for example, withdrawing cash
from the ATM, or purchasing tickets using a credit card.
Uses of Database
A database stores information in an organized way, and makes it easy to get information in and
out. In today’s world databases are used by websites such as Google.com and Yahoo.com, and in
places such as banks, libraries and hospitals.
The common areas where data bases are used are:
1. Government: The government uses a database to store records of people’s data for different
purposes, for example, census, income tax payments, etc.
2. Business: Today the business world depends on databases round the clock. Inventory, order
processing, payroll, accounting, shipping and transportation routing are often tracked within a
main database that keeps the organization/company functioning.
3. Educational Institutions: From elementary schools/colleges to universities, educational
institutions use the database to keep track of students’ data. Specialized database packages for
schools/colleges and universities are also available.
4. Non-Profit Organizations: Most of the charities and other non-profit groups use a database
for keeping track of donations, volunteers, hours served in the community, clients helped and
other information related to the organization.
5. Household and Family Management: The database is very useful in household and family
management for many individuals and families. Many individuals/families use a database to

1
keep track of family birthdays, bills and expenses within a home; addresses of friends and
relatives; movie/DVD collections; and other lists.
6. Everyday Life: Some departmental stores keep your information stored in a customer
database. When you make a purchase, the sales clerk asks for your address or customer account
number. These collections of data are used to send mailings of special offers, discounts and other
deals.
Common Database Applications
Databases are used in many applications, covering almost the entire range of computer software.
Databases are the preferred method of storage for large multiuser applications, where
coordination between many users is needed. Many electronic mail programs and personal
organizers are based on standard database technology.
Some examples of database applications are:
1. Computerized library systems.
2. Automated Teller Machines (ATMs).
3. Flight reservation systems.
4. Computerized inventory systems.
Any application that involves storing and maintaining a large amount of data in an organized
manner can be set up as an Access database. For example, customers and invoices, suppliers and
purchases, inventory and orders, etc.
Database terminology
Let us discuss some of the useful terms related to database:
Table: A table in a database is a predefined format of rows and columns that define an entity. It
is also called a relation. For example, the STUDENT table shown
Table 1.1: STUDENT Table
ID Stud Name Father Name Sex Age Phone Number

1 Abraham Nigatu Male 13 222222

2 Beza Yasi Female 12 111111
3 Hana Fuad Female 13 555555
4 Shibru Engda Male 14 444444
5 Maru Tariku Male 13 333333
Record: A database record consists of one set of tuples in a table. In other words, a row of
information in a table is called a record. For example, Table 4.1 has five records.
Field: A field is a single unit of data stored as part of a database record. Each record is made up
of one or more fields, which correspond to the columns in a database table. Fields are often
called attributes. For example, Table 4.1 has six fields.
Data Type: A data type specifies the type of data that a column in a table represents in a
database. For example, numeric, character or text, date, logical, etc. For example, in Table 4.1
Stud Name is of text type.
Primary Key: Every table should contain a field that uniquely identifies each record stored in it.
This field is called the primary key of the table. Primary keys may consist of a single field or
multiple fields in combination. For example, in Table 4.1, the student’s unique ID number would
be a good choice for a primary key. The Stud Name would not be a good choice, as there is
always the chance that more than one student might have the same name.
Designing a Database Structure

2
Before you start creating a database, it is essential to create a good database design. Planning the
design structure is important, as an improper design will cause changing the design structure. A
good database design ensures that the database is easy to maintain and store data.
To plan and design a database, the points to be considered are:
1. The purpose of the database.
2. The number of tables and the type of information each table will contain.
3. Columns that will form each table.
4. Relationship between tables.
Open an Existing Database
Suppose you are having MS-Access 2007 installed on your computer. There are different
methods you can use to open existing Access 2007 databases. You can open databases from
Windows Explorer or from within Access. You can open multiple databases at once, and you can
also create desktop shortcuts that open database objects directly.
To open an existing database from Access, perform the following steps:
1. Click Start → All Programs → Microsoft Office → Microsoft Office Access 2007 (see
Fig. 1.2).

2. Click the Microsoft Office Button , and then click Open.

3. Click a shortcut in the Open dialog box, or in the Look in box, click the drive or folder that
contains the database that you want to open.
4. In the folder list, browse to the folder that contains the database.
5. When you find the database, double-click the database to open it in the default mode specified
in the Access Options dialog box or the mode that was set by an administrative policy.

Overview of a Table
A database table contains data about a particular subject (see the table). In a table the data is
arranged in rows and columns. To get the most flexibility out of a database; the data needs to be
organized into tables so that redundancies do not occur. For example, if you are storing

3
information about students, each student should only need to be entered once in a table that is
set-up just to hold student data. Each row in a table is referred to as a record. Records are where
the individual pieces of information are stored. Each record consists of one or more fields. Fields
correspond to the columns in the table. Fields must have a certain data type, whether it is text,
date or time, umber, or some other type.
Creating a Table
One must plan and design a table properly for a new database before creating it. You can use
Design view to create an Access table. Using Design view is the preferred method for creating a
table because it provides you with the most options and enables you to precisely define your
table. In addition to selecting a data type, you can set the other available options in Design view.
Suppose we have to create Table
Perform the following steps:
1. Start Ms-Access and activate the Create tab. Click Table Design in the Tables group

2. Access changes to Design view and the Table Tools become available.
3. Type the field name in the Field Name field.
4. Press the Tab key.
5. Click the down-arrow that appears when you click in the Data Type field and then select a
data type.
6. Click Primary Key if the column you created is a primary key. A small key appears next to the
field name.
7. Press the Tab key.
8. Type a description. The description is optional.
9. Press the Tab key. Access moves to the Field Name field.
10. Repeat steps 3 through 9 until you have created all of your fields

4
12. After finishing the task, click the Save button on the Quick Access Toolbar.
Access saves the table unless you are saving for the first time. If you are saving
for the first time, the Save As dialog box appears. Type the name you want to
give your table. Here, we have saved the
table as STUDENT (see Fig).
13. Click OK. Access saves the table.

Add, Edit, Delete and Sort Records

After creating an Access table, you can add, edit, delete and sort records in it.
Before you can perform an operation on a record, you must select it. To select a
record, click the blank area to the left of the row. To select several records, click
the blank area to the left of a row and then drag. To select the entire table, click
the Select All button in the upper-left corner of the table.
In Datasheet view, you can select the field or fields as:
To select one field, click it’s header.
To select more than one adjacent fields, click the header of one, hold down the
Shift key, and then click the header of another. The two fields and any fields in
between are selected.
In Design view, you can select the field or fields as:
To Select one field, click the row selector for the field you want.

5
To select more than one field, hold down Ctrl key and then click the row selector
for each field.
Adding Records
Adding a new record to an existing Access 2007 database is quite simple. In
fact, being able to modify the database in this way is one of the reasons why
Access is so accessible and easy to use. To add record(s) in a table, perform the
following steps:
1. Open the table. Here, we have opened the STUDENT table. Notice that the
table contains an empty record at the bottom of the table’s data with the word
(NEW) in the first column.
2. Click on that cell in the table and then press the TAB key on your keyboard.
3. Type the new information you want in that field and then press TAB again.
Now type the information you want in that cell and continue in this way until you
wish to add records to the table
(see Fig.)

Editing Records
Data stored as records in tables can be modified at any point in time.
To make changes to a record in a table, perform the following steps:
1. Open the table having the record(s) you want to edit.
2. Place the cursor in the required cell.
3. Remove the existing value.
4. Enter the new data.
The value in the field will be changed. Make other changes (if any) and save the
table.
Deleting Records
You can delete record(s) from tables if it/they is/are not required.
To delete a record from a table, perform the following steps:
1. Open the table and select the record you want to delete.
2. Activate the Home tab.
3. Click Delete in the Records group. A prompt appears.
4. Click the Yes button.
The selected record will be deleted. Similarly delete the other records (if you
wish) and save the table.
Sorting Records

6
Sorting is arranging records in a table, so that the records can be retrieved
quickly and easily. By sorting, you can put a column of information in
alphabetical, numerical, or date order. You can sort in ascending order
(alphabetical from A to Z, lowest number to highest number, earliest date to
latest date) or descending order (alphabetical from Z to A, highest number to
lowest number, latest date to earliest date). You can also sort within a sort.
To sort records in a table, perform the following steps:
1. Open the table. Here, we have opened the STUDENT table
2. Click the column label for the column (here, Phone Number) you want to sort.
3. Activate the Home tab.
4. Click the Ascending or Descending button in the Sort & Filter group. Here, we
have selected the Ascending button.
5. Access sorts the column in ascending (see Fig. ).

Overview of Query
Queries are one of the most important features of database management
system. The process of accessing the database and retrieving data
selectively is known as querying. Queries often serve as the record source
for forms and reports. Query is a database object, which is used for
extracting data from one or more tables based on specific conditions. The set
of records returned by a query is known as Dynaset.
Query Types
MS-Access supports many types of queries. The major categories are:
1. Select Queries: Retrieve records or summaries (totals) across records.
Also includes crosstabulations.
2. Make Table Queries: Similar to Select Queries but results are placed in a
new table.
3. Append Queries: Similar to Select Queries but results are added to an
existing table.
4. Update Queries: Modify data in a table.
5. Delete Queries: Records are deleted from a table.
Select queries are the most common queries and can be used for viewing as
well as a data source
for forms, reports, controls, and other queries. The other queries create or
change data and are known collectively as Action queries.
Creating a Query

7
When you want to build a query for your database, there are two ways to
create it.
1. Either use the Query Wizard, or
2. Use the Design View.
Creating a Query using Query Wizard
To create a query using Query Wizard, perform the following steps:
1. Start MS-Access 2007. Click the Create tab on the Ribbon. Click Query
Wizard button in the Othergroup (see Fig.).

2. When the New Query window appears, make sure Simple Query Wizard is
selected

Then click the button to open the Simple Query Wizard.

3. Choose table. Here, the Student table is already selected in the Tables/Queries list box (see
Fig.). Now you have to select the fields that you wish to display in query results datasheet.
4. Now double-click Stud Name in the Available Fields list. Stud Name should appear in the
Selected Fields column (see Fig.)

8
5. Click the > button. This should add the Father Name field to the Selected

Fields

6. Double-click Phone Number. This should add the Phone Number field to the
Selected Fields list

(see Fig.)

9
7. Click the next button twice.

8. When the final screen appears, type: Select Query (see Fig.)

9. Click the button. The query is automatically saved and executed. It should
look as shown
in Fig.

10
Creating a Query in Design View
To create a query using Design View, perform the following steps:
1. Start MS-Access 2007. Click the Create tab on the Ribbon. Click Query
Design button in the Other group (see Fig.)

Access displays the Design View and the Show Table dialog box

2. In the Show Table dialog box, select the table that contains the fields you want to display in
the query datasheet (here, we select STUDENT table), and then click the dialog box’s Add
button.
3. Click the Close button in the Show Table dialog box.
4. Double-click a field name in the top pane to display the field name in the bottom pane-the
design grid. Repeat this process to include any additional fields (see Fig. ).

11
Click the View button in Views group on Home tab to see the datasheet having the data selected
with your query (see Fig.).
6. Click Save button on the Quick Access Toolbar or press Ctrl + S. Type a name for the query in
the Save As box (see Fig.)

7. Click OK.

Overview of a Form
A form is a database object that you can use to enter, edit, or display data from a table or a query.
Forms are sometimes called “data entry screens”. You can create a database without using forms
by simply editing your data in the table datasheets. However, most database users prefer to use
forms for viewing, entering, and editing data in the tables.
12
Forms provide an easy-to-use format for working with the data, and you can also add functional
elements, such as command buttons, to them. You can program the buttons to determine which
data appears on the form, open other forms or reports, or perform a variety of other tasks. For
example, you might have a form named “Student Form” in which you work with student data.
The Student Form might have a button which opens a help form where you can enter various
types of help for that student (say financial help).
Forms also allow you to control how other users interact with the data in the database. For
example, you can create a form that shows only certain fields and allows only certain operations
to be performed. This helps protect data and to ensure that the data is entered properly.
Creating a Form
MS-Access 2007 gives you new tools (available in the Forms group on the Create tab) to help
you create forms quickly, and provides new form types and features that improve the usability of
your database (see Fig.).

Different Methods of Creating a Form

In Access 2007, forms can be created using the following tools:
Form Tool: It creates a form with a single mouse-click. When you use this tool, all the fields
from the underlying data source are placed on the form. You can begin using the new form
immediately, or you can modify it in Layout view or Design view to better suit your needs.
Split Form Tool: It shows two views for a form, with the bottom half displaying the form using
Form view while the same form displays in the top half using Datasheet view. The two views are
synchronized so that scrolling one view causes the other view to scroll to the same location.
Multiple Items Tool: When you create a form by using the Simple Form tool, the form that
Access creates displays a single record at a time. If you want a form that displays multiple
records but is more customizable than a datasheet, you can use the Multiple Items tool.
Form Wizard: It provides more choices for the form’s design than the Form tool. In the Form
Wizard, available in More Forms, the user is guided through a series of dialog boxes to generate
a form, including selecting the fields to be included and the form layout.
Blank Form Tool: You can use it to build a form if the wizard or the form-building tools do not
fit your needs. This can be a very quick way to build a form, especially if you plan to put only a
few fields on your form.

Creating a form using the Form tool

We assume that you have already created a table (or a query based on one or
more tables), and that you want to build a form to view or manipulate the data.
To create a new form using the Form tool, perform the following steps:

13
1. Start Access. In the Navigation Pane (at the left of the database), double-click
the table that contains the fields for your form. Here, we select STUDENT table.
2. On the Create tab, in the Forms group, click Form (see Fig.)

Access creates the form and displays it in Layout View (see Fig.)

14
2. Click Save button in Quick Access Toolbar and enter a new name for the
form (see Fig.)

3. Click OK.

15