Introduction to a RDBMS: Microsoft Access is an integral part of the MS Office suite that is the most popular and powerful Relational Database Management System (RDBMS). It has a graphical user interface (GUl) and is used to organize and manipulate a large volume of data efficiently. It contains data in the form of tables and provides the facility to create relationship between the using common fields

Features of MS Access:

1.      It provides the facility to break large information into small parts, so that it becomes easily accessible.

2.      Data redundancy is reduced, which in turn minimises data inconsistency. Data redundancy occurs when data is duplicated at multiple locations.

3.      It increases the efficiency, speed, and flexibility for searching and accessing information.

4.      Access facilitates the sharing of data.

5.      Different users can use the same database to extract data according to their needs.

Database objects

There are four main objects in a database.

·         Tables

·         Queries

·         Forms

·         Reports

Tables: Tables are used to store the data in the form of rows and columns. Every table has a finite number of columns but it can have infinite rows.

Elements of a Table

Fields: All the columns in a table are called Fields. In the following table, id, first_name, last_name, dob, active and insert date are the fields. A field dob will contain the date of birth only.

Records: The rows (tuples) in a table are called Records. A record contains the values for all the fields that belong to a single person or an object. For example, a collection of the fields such as id, first_name, last_name, dob, active and insert date for one student makes a single record.

Data: A set of characters that represents a valid value is known as Data. For example, 2, 'Sanjay, 'kumar’, 14/03/1977 are the data for some specific fields of the table.

 QUERIES

Queries are used to ask questions from the data base to retrieve records based on certain criteria. Queries are also used to perform actions, such as insert, delete, update etc., on the data depending upon the criteria specified by the user.

FORMS

A form has an attractive interface that accepts data from the user and inserts it in the corresponding table or query. Forms are also used to update any record in the table or query.

REPORTS

Reports are used to display the selected data in a printable format. These are used for decision-making and analyzing the data by the organizations.

 Starting MS Access

 To start MS Access, follow the path Start Button -> All Programs -> Microsoft Office -> Microsoft Access 2010. MS Access will be opened.

 Elements of MS Access Window:

 Backstage View: It contains information and commands that apply to the entire database. It can be opened by clicking on the File tab.

Quick Access Toolbar: It contains the buttons to access the commands that are frequently used.

Title Bar: It displays the name of the current document and application.

Navigation Pane: It displays a list of new or existing database objects, such as Tables, Queries, Forms, and Report. It can also be called the All Access Objects pane.

Documents Tab: It displays the opened database objects.

Ribbon: It is located just below the Title bar. It has two parts: Tabs and Groups.

 CREATING DATABASE

To create a database in MS Access, follow the given steps:

1.      Open Microsoft Access. The following screen will be opened.

2.      Click on the Blank database option under the Available Templates category.

3.      The Blank database task pane appears on the right side of the MS Access window.

4.      Specify the file name in the File Name text box.

5.      Set the location of the file by clicking on the Browse button which is next to the File Name text box.

6.      Click on the Create button. Database will be created with an empty table named as Table1.

CREATING TABLES

Tables are the basic building blocks of a database in which data is stored. Tables can be created by two methods:

1.      Using Design View

2.      Using Datasheet View

 1.      CREATING TABLES IN DESIGN VIEW

In the Design View, we can create a table by defining the field names and field types. To create the table, click on the Table Design option in the Tables group of the Create tab. A table will be created and opened in the Design View. In this view, the Object window consists of two panes i.e. Field Entry pane and Field Properties pane.

 Adding Fields in Design View

1.      Type the field name in the Field Name column and then press the Tab key to move to the Data Type column. By default, Data Type is Text data type. Change the data type as per field requirement (say Number)

2.      In the Description column, type a brief description for the respective field. The description text is displayed on the Status bar when we select that field in the Datasheet View.

3.      Select the Save button present on the Quick Access Toolbar or press CTRL+S.

A dialog box will be appeared. Type the required name of the table and click on OK.

1.      CREATING TABLES IN DATASHEET VIEW

In the Datasheet View, we can enter, edit, or update the data of the created tables while in design view, data can’t be entered. This View does not allow to design the table structure from scratch while in design view, Table structure can be designed from scratch.

To work in the Datasheet View, follow the given steps:

1.      Select the created table in the Design View. A table appears. Enter the data in the table like a spreadsheet.  

Or 

1.      Click on the Create tab and select the Table option in the Tables group.

2.      A table appears that has an empty row and the cursor is placed in the first empty cell in the Click to Add column.

3.      Access automatically creates the first field called ID that is designated as the table's primary key by default.

4.      The data type of the ID field is set to AutoNumber that means Access will automatically enter a sequential number in this field for each new record.

5.      Start typing to enter the data by selecting the empty field below the Click to Add column. Type "Sanjay Kumar" and then press the Tab key to move to the next field.

6.      The value '1' appears in the ID field automatically as ID has auto number data type. The name of the second column has been changed to field1 and Click to Add label has been moved to the third column.

7.      To change the name of Field1, double-click on it and typing the field name in its place.

8.      Enter the other information in the table in the similar manner. Thus, the table gets created.

NOTE

An asterisk(*) sign in the Record Selector indicates that the row is ready to receive a new record. Once we enter a record, the asterisk (*) sign changes to a pencil sign  and indicates to save the record. Click on it to save the record before moving on to the next.

DATA TYPE

 Every data is stored in a table in relational database. Each table consists of a number of fields and every field has its own set of properties (that describe the characteristics and behaviour of data) and its data type. Data type tells that what type of data can be entered into the field. For example, a field has number data type then the field can contain only numeric data. If we enter text value then it will convert it into zero(0). The commonly used data types in MS Access are:

Primary key is a constraint in the table in database to check that record in the table is unique. Data in the primary key must be unique and It can’t be NULL or left blank while entering data.

For example, consider a student table that contains the records of the students of a class. Id field can be set as a primary key, because ID is unique for each student. Those fields, where there are chances of duplicity, cannot be considered as the primary key.

To set a primary key field, follow the given steps:

·         In design view, click on the field that we want to set as the primary key.

·         Click on the Primary Key option in the Design tab OR Right-click on the field and select the Primary Key option from the menu.

·         The field will be set as the primary key, and an indication of small key symbol in the field selector column will be appeared.

·         Click on the Close button given on the Table window.

·         A message window will be displayed that gives a query to save the changes.

·         Click on the Yes button to save the changes.

Multiple Choice Questions:

1. The field Id, which access automatically creates is set to _______ data type.
1. Text       b. Number          c. Autonumber                 d. Memo
2. When you save a table , the table name will appear in the _____ Pane under the Tables  Group.
1. Navigation          b. Database        c. Table                 d. None of these
3. To quit the MS Access application, click on the File Tab and select the ______
1. Close                     b. Exit                    c. Quit                   d. None of these

Fill in the blanks

1. The _________ Database option facilitates you to create a database from scratch.
2. To save a database, you can select the Save icon present on the ________ toolbar.
3. While creating a database through the Blank Database template, Access automatically sets each field’s ______based on the data that you enter.

Answers:

Multiple Choice Questions

1. Autonumber
2. Navigation
3. Exit

Fill in the blanks

1. Blank
2. Quick Access
3. Data type