Introduction

Database refers to the arrangement of data in a manner where it can be retrieved easily.

Microsoft Access is a database program that is used for storing all kinds of information

in the form of tables, queries, forms, reports, etc. It has many built-in features to assist

you in constructing and viewing the information stored in the database. Once the

information is stored in Microsoft Access database, it is easy to find, analyze and print.

Important Terms Related to Database

Let us learn about a few terms related to database.

Database A database is an integrated collection of logically-related records in the

form of tables. It offers an organized mechanism for storing, managing and retrieving

information related to a particular subject or purpose. For example, a database can be

created for schools, libraries, banks, etc.

Database Management System (DBMS): (A DBMS is a set of computer

programs that controls the creation, maintenance and use of the computerised database

by the user. Some popular DBMS software are MS Access, FoxPro, FoxBASE, etc.

Table: A table is a collection of related information in the form of rows and columns.

For example, Fig. 1.1 shows the structure of a School database where the tables

Student, Library and Staff store different pieces of information related to students, the

books in the library and the school staff.

Record: A record is one row of a table. It includes complete information arranged

horizontally in a table. For example, the Student table may contain the complete

Information of a student like Admission No., Student Name, Address, Phone Number

and Class.

Field: It is a column arranged vertically in a table that stores information of the same

type. For example, Admission No. is a field that stores only admission number and

Student Name stores only the names of the students.

Functions of DBMS

A DBMS performs these important functions to ensure the integrity and consistency of

data in the database.

Reduces Data Redundancy: Data redundancy means duplication of data. A

DBMS helps to remove duplicate data. For example, in a library the name of a book

may occur under the author's name and the ISBN number. This redundant data can

be removed using a DBMS.

Facilitates Sharing of Data: Different users can access and use the same

database as the format of the files stored. For example, the Admissions department

can use the School database for storing and retrieving Admission No. of the students

while the teachers can use the same database for retrieving Student Name.

Controls Data Inconsistency: In DBMS, the change in any one file is

automatically updated in all the related files. For example, in a library if there are

two records for a single book, one under the author's name and another under new

stock it might happen that the details are changed under one record. This may lead

to inconsistency as both the records for the same book would comprise of different

information. Such problems are easily dealt by DBMS.

Enforces Standards: In a DBMS, certain standards can be applied in data

representation. Here standards may relate to the naming of data, structure of data,

format of data, etc. For example, if name of student is a field in a table, it should be

consistent everywhere.

Ensures Data Security: The access to any record can be made protected in a

DBMS. The files can be accessed only by those who are authorized to do so

Maintains Integrity: A DBMS maintains integrity by keeping some constraints

when the data is entered. These constraints are the rules that are designed to keep data consistent and correct. They act like a check on the incoming data.

FACT FILE

A relational database management system (RDBMS) was proposed by

E.F Codd in 1970. It is a type of database where data is organized as related

tables. These databases are more powerful as relevant data can be extracted

and tables can be extended and modified from these without having to reorganize the

existing tables.

Database Objects

The following table (Table 1.1) identifies the database objects you can use while

creating a Microsoft Access 2010 database.

In the following table 1.1, Objects and their descriptions are given-

Table: It stores information in the form of rows (records) and columns (fields).

For example, one table could store a list of friends along with their details,

while another table could store their marks.

Queries: These are used for sorting, grouping or filtering data in the database. For example, a query might only display a list of students in class 7 out of all

the school students.

Forms: Customized screens to provide an easy way to enter and view data in a

table or query. For example, when you apply for admission in a school,

you fill out an online form. The data that you enter in the admission form

is stored inside the school's database.

Reports: These present the data from a table or query in printed format.

For example, teachers can create simple report of all the students opting

for fine arts as an elective subject.

ACTIVITY

A. Create a presentation on the topic database and its concepts. The slides should include:

1. Key features

2. History

3. Real-life usage

B. List out the various types of database programs available in the computer world along with their latest versions.

C. Think of a Hospital's database. What do you think should the table names be in this database? Mention different fields of information in the tables. Draw a structure of this database using a software program of your choice.

Creating Database in MS Access 2010

In MS Access 2010, a database can be created in 2 ways.

1. Using a Blank database

2. Using Sample templates

The extension of a

database file in MS

Access 2010 is .accdb.

Title Bar: It shows the name of the database created. The Minimize, Maximize/ Restore Down and Close buttons are also present in this area.

File menu: It is the button on the left of the Title Bar. It contains commands like

New, Open, Save and Print.

Quick Access Toolbar: It has some of the most frequently used commands in MS

Access like Save, Undo and Redo. The toolbar can be shifted below the Ribbon also, and more commands can be added to it.

Ribbon: It contains the commands that can be performed on different database

objects. These commands are classified under groups in different tabs.

Navigation Pane: It is the left pane of the window. Here, database objects in the

currently opened or freshly created databases are displayed.

Tabbed Document: The database objects appear in tabs unlike the overlapping

windows in the earlier versions.

Status Bar: It is present at the bottom window and displays the status and buttons to change the page views.

Data Types

The Data Type for every Field Name describes the form in which the data is accepted. Descriptions of some of the commonly used Data Type values that appear in the drop-down list are shown in Table 1.2.

AutoNumber:

It is an integer that automatically generates an increasing or

decreasing order of numbers when records are added or deleted. For

example, roll number of a student can be an AutoNumber.

Text:

It stores alphanumeric values that is, both numbers and letters.

Maximum 255 characters can be stored. For example, a product ID or

an address.

Memo:

It is used for lengthy text and numbers such as definitions or

descriptive notes. A maximum of 65536 characters are allowed.

Number:

It holds numeric data that is used for calculations. Both decimals and

non-decimal digits are allowed.

Date/Time:

It stores the date and time values in different formats.

Currency:

It specifies different currencies and displays them in different formats.

Yes/No:

This can have only one of the two values that is, True/False, Yes/No or On/Off.

Hyperlink:

It is a link to an Internet resource.

Primary Key:

Every table in the database must have at least one field that uniquely identifies each

record in the table. This field is known as primary key. This key should always have a

value that is not repeated for any other record.

For example, in any table for students, it is possible that there are two students having the same name. Here, you can assign Admission No. as the primary key because it can identify each student uniquely.

If the primary key is not assigned, it is automatically added as an ID at the time

of creating the table.

FACT FILE:

You can even have multiple primary keys for a table. However, these should

be unique values. To do so you must hold down the Ctrl key and then select

the row selector for each field.

Removing a Primary Key

To remove a primary key, select the required field and select the Primary Key option

in the Tools group of the Design tab. You can also select the field, right-click on it and

then select the Primary Key option from the shortcut menu. The key symbol appearing

next to the respective field will disappear.

Changing the View

MS Access 2010 provides different viewing options for working on the data. The Views

group of the Home tab provides four options in which users can view their data. Here

we will discuss two of them.

1. Datasheet View

3. Pivot Chart View

2. Pivot Table View

4. Design View

The Datasheet View and Design View are the two most commonly used views in MS

Access. The row and column format is seen in the Datasheet View where data can be

added. Descriptions like field names and data types can be added in the Design View.

It is also possible to switch from one view to another using the View drop-down list in

the Views group of the:

Datasheet tab while switching from Datasheet View to Design View.

Design tab while switching from the Design View to Datasheet View.

Creating Tables in MS Access 2010

MS Access 2010 provides two ways for creating a table-

1. Create a Table in Datasheet View

2. Create a table in design view.

Field Grid Pane: This pane is used to define the fields in the tables along with their

data types and an optional description of the field. You can change the data type of the

field in this view.

Field Properties Pane: You can give additional properties to the field name

using this pane. It is used to specify the field properties in detail such as field size and

validation.

Some of the commonly used options that can be filled in the Field Properties pane are

explained in Table 1.3.

Field Size:

It is used to set the maximum size for data stored in the field set

to the Text or Number data type.

New Values:

It is used to set the order of numbers in the fields either in an

increment or random order.

Format:

It allows you to display data in a format which is different from

the way it is actually stored. For example, you can choose a

predefined format or other symbols for creating a custom format

to define a currency.

Caption:

It is used to display an alternate name for the field to make it

more explanatory.

Indexed:

It speeds up sorting but may slow down the database.

Smart Tags:

It is used to add tags like date, telephone number, financial

symbol or person name in the field. Each tag is associated with

an action or a list of actions.

Text Align:

It is used for the alignment of the text entered in the field.

Modifying Tables and Their

Content::

Tables created in MS Access 2010 can be

modified in the Datasheet view. Data types can be changed and columns/fields can be added, deleted, moved and renamed.

The different groups in the Fields tab can be used to modify the table and its contents

(Fig. 1.16).

FACT FILE

A field name in the

database can have a

maximum of 64 characters

in upper, lower or mixed case; letters,

numbers and some special characters

can also be used. However, it cannot

have brackets and a period and it

cannot start with a blank

space.

ACTIVITY:

A query has been generated for a group of related tables in a database. Is it possible to

get a hard copy of the query? If yes, suggest how.

B. Create a table with the records of sports lovers in your class using MS Access 2010.

Use different descriptive fields such as Admission Number, Student Names and

Favorite Sports with appropriate data types. Set a unique primary key for the table.

After the table is created, practice the following.

1. Enter at least 10 records in the table.

2. Change the address of record 3 and favorite sport of record 6 in the table.

3. Delete the last record from the table.

4. Create a query based on the favoritism sport. For example, create a query to display

only cricket lovers.

5. Generate a report for the table.

6. Create a form and enter a new record into the table.

7. Save all changes made to the table.

8. Close the table and the database.

GLOSSARY:

Database It is an integrated collection of logically-related records in the form of tables.

DBMS It is a set of computer program that controls the creation, maintenance and the use of

database in the computer.

Database objects: These are various components of MS Access.

Data type: It is the format in which the data is accepted.

Field: It is a column arranged vertically in a table that stores information of the same type.

Form : It is a customized screen for viewing, entering, modifying and deleting data in a table

or a query.

Primary key: It uniquely identifies each record in the table.

Query: It is a question pertaining to the data with a specific answer to it.

Record: It includes complete information pertaining to a particular record arranged

horizontally in a table.

Report: It is the representation of data in a printed format.

Table: It is a collection of related information in the form of rows and columns.

1. Microsoft Access 2010 is a database program used for storing information

in the form of tables, queries, forms, reports, etc.

2. A DBMS is useful in the following aspects: reduces data redundancy,

facilitates file sharing, controls data inconsistency, enforces standards.

3. A new database can be created either using a Blank database or Sample

templates.

4. Tables can be created either in Datasheet View or Design View in MS Access 2010.

5. The Design View consists of two panes: Field Grid pane and Field Properties pane.

6. Queries are raised to retrieve specific information from a table or tables.

7. Forms provide different views for adding, deleting, formatting and designing the data.

8. Queries, forms and reports created for a table can be saved in a database.