CBSE Notes for Class 8 Computer in Action – Introduction to Microsoft Access 2013
Supermarkets use computers for storing lists of items, their prices, quantities available and quantities sold. Have you ever wondered how they store, manage and quickly retrieve data about so many products? Your elders can book railway tickets online. Where is the data about trains, their timings and seat allocation stored? How is it that when a person books a ticket, the reserved and the available seats are automatically shown?
If a person is asked to memorise such records, it will be an almost impossible task. To deal with such problems, data is stored and managed in a computer using databases. Thesedays, large amounts of data are required to be stored at all the places such as hotels, hospitals, supermarkets, railway stations, airports and offices. In this chapter, we will learn more about database and its related terminologies.
DATABASE AND DBMS
A database is an organised collection of related data so that it can be easily accessed, managed and updated. A Database Management System (DBMS) is a software program that enables us to create, modify and extract data from a database. DBMS can be based on different data models such as network, hierarchical and relational.
A DBMS based on the relational data model stores data in the form of tables and is called Relational Database Management System (RDBMS). In this chapter, we will learn about Microsoft Access, which is an example of RDBMS. Some other examples of RDBMS Oracle and Microsoft SQL Server.
Advantages of a Database
Some of the advantages of storing data in a database as compared to files are listed here.
- Reduced Data Redundancy: The duplication of data is referred to as data redundancy. In contrast to non-database systems, which maintain multiple copies of the same data at different locations, DBMS stores data at a central place.
The user is not required to maintain multiple copies of the same data. Thus, DBMS prevents data duplication.
- Reduced Update on Errors and Increased Consistency: When the data is stored at multiple locations, there are chances that modifications are not carried out at all the places. Multiple-mismatching copies of the same data are known as data inconsistency. DBMS ensures data consistency by storing data at one place and ensuring that there is no duplication of data.
- Improved Data Access to Users: A DBMS stores data at a centralised location and facilitates sharing of data among multiple users according to their requirements. For example, users from all over the country access the database for booking railway tickets.
- Improved Data Security: One of the most important advantages of DBMS is data security. In DBMS, user IDs can be created with various levels of security.
Users have limited rights and permissions. Only authorised users can access the data. Some may have the privilege of changing the data while others can only view the data.
- Maintaining Standards: A DBMS ensures that the stored data follows the organisation’s own standards or national/ international standards. This ensures greater data integrity. This also aids in sharing data between different systems.
MICROSOFT ACCESS 2013
Microsoft Access is the. most popular RDBMS that comes as a part of the Microsoft Office suite. Access provides a graphical user interface for managing data. The databases created in Access 2013 are saved with the extension .accdb. Databases in Access 2013 are composed of four main objects—tables, queries, forms and reports. These objects allow us to enter, store, analyse and compile the data.
Let us learn more about these objects.
Objects in Access
The main objects that can be created in Access are as follows.
- Tables: They are used to store data in the form of rows (records) and columns (fields). A table is also referred to as a relation. We will learn more about tables in this chapter.
- Queries: Query let us find and work on the data resulting from one or multiple tables based on specified conditions.
- Forms: Forms provide a user interface that lets the users enter and change in the tables.
- Reports: If forms are for input, then reports are for output. Reports are used to display the data stored in database tables in a professional format for printing purposes. Figure 2.1 illustrates the relationship between the objects in Access.
To start Access, type Access 2013 in the Search box and click on Access 2013 option. The Microsoft Access start up window opens as shown in Figure 2.3.
CREATING A DATABASE
Follow the given steps to create a blank database in Access.
Step 1: Click on the Blank desktop database option.
Step 2: Type a name for the database in the File Name textbox.
Step 3: Click on the Browse button next to the File Name textbox and choose a location for the database.
Step 4: Click on the Create button. A blank database gets created. Access creates the database with an empty table named Table1. We will learn about working with tables after familiarising ourselves with the various components of the Access window shown in Figure 2.6.
COMPONENTS OF THE ACCESS WINDOW
Let us get familiar with the various components of the Access window (Fig. 2.7).
- Title Bar: The Title Bar appears on the top of the window and displays the name of the document on which you are currently working.
- Quick Access Toolbar : The Quick Access Toolbar is present on the top left corner of the Access window. It has buttons for commands that are used frequently. By default, the following buttons are present on it.
- Save button: to save your work
- Undo button: to undo the previous action/rollback the action done
- Redo button: to redo the action that was undone.
We can customise the Toolbar and add more buttons to it by clicking on the arrow next to the Redo button.
- Ribbon: The Ribbon contains multiple tabs, each with several groups of commands. Some tabs appear only when you work with certain objects such as Forms. These tabs are called contextual tabs.
- Navigation pane: The Navigation pane displays the names of all the objects in the database. The objects are grouped on the basis of their type, for example, Tables, Queries and so on. We can double-click on an object name in the Navigation Pane to open it. We can right-click on an object in the Navigation pane to perform various operations such as renaming and deleting an object.
- Navigation ButtonsThe Navigation buttons display the current record number in an object (in this example, Tables ► Employee) on which you are working. It has buttons to move back and forth between the various records.
- Object Tabs: The objects that you have opened in a database appear in a tabbed form. When you click on an object tab, the contents of that object are displayed in the Work Area. Click on the cross button (X) on the right end of the bar to close the object displayed in the current tab.
- Status Bar: The Status bar appears at the bottom of the window. The view buttonsare present on the right side of the Status bar. Usually two common buttons that are present are Datasheet view and Design view.
TABLES IN ACCESS
The tables are the building blocks of a database and are used for storing data. A table is made up of rows and columns. A database can have one or more tables.
Consider the following table that stores data about the employees in an organisation.Components of a Table
The important components of a table are:
1. Field or Attribute: The columns in a table are known as fields or A field is a named unit of information. A field stores one type of information about all the objects or items. Every field has a data type that determines the type of values that can be stored under it. For example, the above table has four fields or attributes. The field Ename stores text values and the field Esalary stores numeric values. Let us learn more about field data types.
Field Data types
The various data types available in Access are given in Table 2.2.
After setting the data type for a field, we can set its properties. Let us learn more about field properties.
After we have decided on the data type of the table, we can set field properties. The Field properties let us have more control on data that can be entered in a particular field. Some of the field properties are listed in Table 2.3.
2. Record: The rows in a table are known as records A record stores complete information about an object or an item. For example, Table Employee (Table 2.1) has four records. Each record contains the complete information about an employee.
3. Primary key:A primary key is a field or combination of fields that uniquely identifies the records in a table. A primary key field cannot have repetitive values and cannot be left blank. In Table 2.1, Eno field can be made the primary key as every employee has a unique employee number.
Views of a Table
We can work on a table in two views—Design view and Datasheet view. Let us learn more about them.
In the Design view of the table, you can enter the field names, their data types and description. You can also set the field properties. The Design view window is divided into two panes—
- Field Grid Pane: Field Grid
Pane is used for entering field names and their data types. You can also give an optional description about each field in this pane.
- Field Properties Pane: Field Properties Pane is used to set properties for the fields in the table.
You use the Datasheet view to enter data in the table. When you double-click the table name in the database window, the table opens in the Datasheet view.
Switching between Views
You can quickly switch from one view to another by following either of the two options:
- Click on the View option in the Views group on the Home tab to change to the desired view.
- Click on the Design viewor Datasheet viewbuttons at the bottom-right corner of the Status bar.
CREATING A TABLE
There are various ways to create tables in Access. In this chapter, you will be learning to create tables in the Design view. This view lets you design the structure of a table by specifying the field names, their data types and properties.
The steps to create a table in the Design view are:
Step 1: Click on the Create tab.
Step 2: Click on the Table Design option in the Tables group. A new table is inserted in the database and it opens in the Design View. A new tab named Design appears in the Ribbon.
Step 3: Enter the name for the field in the Field Name column.
Step 4: Enter the name for the field in the Field Name column.
Step 5: Use the Field Properties pane to set the properties for the fields.
Repeat steps 3 to 5 for all the fields in the table.
Step 6: Place the cursor on the field to be set as the Primary key and click onthe Primary Key option in the Tools group on the Design tab. You will notice that a key symbolappears next to the field.
Step 7: Click on the Save button on the Quick Access toolbar or Click on the File tab and choose the Save option. Save the table with an appropriate name.The table object is now added to the Navigation Pane.
ADDING RECORDS OR DATA IN A TABLE
After creating the table in the Design view, we can add records to it in the Datasheet view. The steps to enter records in a table are as follows:
Step 1: Open the table in the Datasheet view. (Double-click on the table in the Navigation Pane to open it in the Datasheet view)
Step 2: Click on the New option in Records group on the Home tab. A new record is inserted in the table.
Step 3: Type the values for various fields in a record. When you start entering data, a new record gets inserted automatically at the end of a table.
DELETING RECORDS IN A TABLE
Step 1: Open the table in the Datasheet view and follow these steps to delete a record. Select the record that needs to be deleted.
Step 2: Click on the Delete optionin the Records group on the Home tab.
SORTING RECORD IN A TABLE
Open the table in the Datasheet view and follow these steps to sort records.
Step 1: Select the field on the basis of which you want to sort the records.
Step 2: Click on the Ascending or Descending option in the Sort & filter group on the Home tab. The records get sorted.
MODIFYING THE TABLE DESIGN
We can make changes to the design of a table after it has been created. Let us learn how to insert and remove a field from a table.
Inserting a New Field
Open the table in Design view and follow the given steps to insert a field in the table.
Step 1: Place the cursor on the field before which you want to insert a new field.
Step 2: Click on the Insert Rows option in the Tools group on the Design tab. A new row is inserted. Enter the field name, data type and properties for the new field.
Deleting a Field
Open the table in Design view and follow the given steps to delete a field in the table.
Step 1: Click the field that has to be removed.
Step 2: Click on the Delete Rows option in the Tools group on the Design tab.
Step 3: Before permanently deleting a field, Access displays a warning box confirming whether you want to delete the field permanently. Click on the Yes button.
The field is deleted from the table.