NCERT Textbook - Accounting Database for Database Management System | Accountancy Class 11 - Commerce PDF Download

 Page 1


554 Accountancy
I
n chapter 14, you have learnt about the
fundamentals of creating a database design in
the context of accounting system. This chapter deals
with the basics of MS Access for implementing the
databases and specifically deals with implementa-
tion of accounting databases, the design of which
has been shown, described and discussed in chapter
14 as Model-I and Model-II. The accounting
database design has been discussed below in terms
of its implementation modalities in the context of
MS Access.
15.1 MS Access and its Components
It is one of the popularly used Database Management
System (DBMS) to create, store and manage
database. It is also popularly  called ACCESS.
Every component that is created using Access
is an object and several such similar objects
constitute a class. Access is functionally available
with the following seven-object classes.  Each of
these object classes is capable of creating their
respective object replicas.
• Tables : This object class allows a database
designer to create the data tables with their
respective fieldnames, data types and properties.
• Queries : This object class is meant to create the
SQL compatible query statement with or without
the help of Graphic User Interface (GUI) to define
tables, store data and retrieve both data and
information.
LEARNING OBJECTIVES
After studying this
chapter, you will be able
to :
• identify the resources
of MS ACCESS as
DBMS;
• create data tables
described in a data-
base design and set
relationship among
these tables;
• explain the ACCESS
basics and procedures
to create forms using
ACCESS;
• describe and create
voucher forms in
consonance with diffe-
rent database designs;
• identify information
requirement of reports
for querying databases;
• formulate and imple-
ment queries for retri-
eving data and inform-
ation for presentation
in accounting reports ;
and
• implement the process
in ACCESS for genera-
ting accounting reports
by using accounting
information queries.
15
Accounting System Using
Database Management System
Page 2


554 Accountancy
I
n chapter 14, you have learnt about the
fundamentals of creating a database design in
the context of accounting system. This chapter deals
with the basics of MS Access for implementing the
databases and specifically deals with implementa-
tion of accounting databases, the design of which
has been shown, described and discussed in chapter
14 as Model-I and Model-II. The accounting
database design has been discussed below in terms
of its implementation modalities in the context of
MS Access.
15.1 MS Access and its Components
It is one of the popularly used Database Management
System (DBMS) to create, store and manage
database. It is also popularly  called ACCESS.
Every component that is created using Access
is an object and several such similar objects
constitute a class. Access is functionally available
with the following seven-object classes.  Each of
these object classes is capable of creating their
respective object replicas.
• Tables : This object class allows a database
designer to create the data tables with their
respective fieldnames, data types and properties.
• Queries : This object class is meant to create the
SQL compatible query statement with or without
the help of Graphic User Interface (GUI) to define
tables, store data and retrieve both data and
information.
LEARNING OBJECTIVES
After studying this
chapter, you will be able
to :
• identify the resources
of MS ACCESS as
DBMS;
• create data tables
described in a data-
base design and set
relationship among
these tables;
• explain the ACCESS
basics and procedures
to create forms using
ACCESS;
• describe and create
voucher forms in
consonance with diffe-
rent database designs;
• identify information
requirement of reports
for querying databases;
• formulate and imple-
ment queries for retri-
eving data and inform-
ation for presentation
in accounting reports ;
and
• implement the process
in ACCESS for genera-
ting accounting reports
by using accounting
information queries.
15
Accounting System Using
Database Management System
555 Accounting System using DBMS
• Forms : This object class allows the designer to create an appropriate user
interface to formally interact with the back end database, defined by the
tables and queries.
• Reports: This object class is used to create various reports, the source of
information content of which is based on tables, queries or both. Such
reports are designed in Access according to the requirement of end-user.
• Pages : This object class is meant to create Data Access Pages, which can
be posted on a Web site of an organisation using Internet or sent via
e-mail to someone of the organisation’s network.
• Macros : In macro programming, the objects using individual instructions
called macro-oriented actions are manipulated. A Macro is a list of macro-
oriented actions that run as a unit. Access provides for such Macro
programming.
Fig. 15.1 : An example of database window to work in Access
• Modules : These are the foundations of any application and allow the
designer to create a set of programming instructions, called functions or
sub-routines that can be used throughout the application.
Page 3


554 Accountancy
I
n chapter 14, you have learnt about the
fundamentals of creating a database design in
the context of accounting system. This chapter deals
with the basics of MS Access for implementing the
databases and specifically deals with implementa-
tion of accounting databases, the design of which
has been shown, described and discussed in chapter
14 as Model-I and Model-II. The accounting
database design has been discussed below in terms
of its implementation modalities in the context of
MS Access.
15.1 MS Access and its Components
It is one of the popularly used Database Management
System (DBMS) to create, store and manage
database. It is also popularly  called ACCESS.
Every component that is created using Access
is an object and several such similar objects
constitute a class. Access is functionally available
with the following seven-object classes.  Each of
these object classes is capable of creating their
respective object replicas.
• Tables : This object class allows a database
designer to create the data tables with their
respective fieldnames, data types and properties.
• Queries : This object class is meant to create the
SQL compatible query statement with or without
the help of Graphic User Interface (GUI) to define
tables, store data and retrieve both data and
information.
LEARNING OBJECTIVES
After studying this
chapter, you will be able
to :
• identify the resources
of MS ACCESS as
DBMS;
• create data tables
described in a data-
base design and set
relationship among
these tables;
• explain the ACCESS
basics and procedures
to create forms using
ACCESS;
• describe and create
voucher forms in
consonance with diffe-
rent database designs;
• identify information
requirement of reports
for querying databases;
• formulate and imple-
ment queries for retri-
eving data and inform-
ation for presentation
in accounting reports ;
and
• implement the process
in ACCESS for genera-
ting accounting reports
by using accounting
information queries.
15
Accounting System Using
Database Management System
555 Accounting System using DBMS
• Forms : This object class allows the designer to create an appropriate user
interface to formally interact with the back end database, defined by the
tables and queries.
• Reports: This object class is used to create various reports, the source of
information content of which is based on tables, queries or both. Such
reports are designed in Access according to the requirement of end-user.
• Pages : This object class is meant to create Data Access Pages, which can
be posted on a Web site of an organisation using Internet or sent via
e-mail to someone of the organisation’s network.
• Macros : In macro programming, the objects using individual instructions
called macro-oriented actions are manipulated. A Macro is a list of macro-
oriented actions that run as a unit. Access provides for such Macro
programming.
Fig. 15.1 : An example of database window to work in Access
• Modules : These are the foundations of any application and allow the
designer to create a set of programming instructions, called functions or
sub-routines that can be used throughout the application.
556 Accountancy
The functions return a value while subroutines do not return any value.
Access provides for creating such modules.
Each of these object classes is contained in the named database file of
Access with MDB extension. Whenever this file is opened, a database window,
as shown on next page, opens with all the above object classes available on
the left hand side. As and when the specific objects are created or designed,
they get listed on right hand side of this window against each of these object
classes.
Box 1
Capabilities of MS Access
Access has certain capabilities, which bring it closer to an ideal Database
Management System. These capabilities are :
• Storing the data in an organised manner.
• Enforcing data integrity constraints.
• Representing complex relationship among data.
• Providing for persistent storage of database objects.
• Restricting unauthorised access to database.
• Allowing fast retrieval of data with or without processing by using SQL.
• Flexibility to create multiple user interfaces.
• Providing for data sharing and multi-user transaction processing.
• Supporting multiple views of data and information.
15.1.1 Access Basics for Creating a Database
When a new database is created from the scratch, there is complete control
over the database objects, their properties and the relationships. In order to
create a new database without the help of database wizard (that is an
automated process in Access), the following steps are required :
(i) Open Access Window to choose blank Access database and click OK
button.
(ii) Access responds by displaying File New Database dialog box, which
prompts the designer to enter a file name and a location for the database.
This must be followed by clicking Create button.
(iii) If the task pane is not open, choose File from menu bar and click at
new to open the task pane to create a new database.
15.1.2 Creating of Tables in Access
The creation of tables in Access requires the following steps and understanding
of the components of table object.
Page 4


554 Accountancy
I
n chapter 14, you have learnt about the
fundamentals of creating a database design in
the context of accounting system. This chapter deals
with the basics of MS Access for implementing the
databases and specifically deals with implementa-
tion of accounting databases, the design of which
has been shown, described and discussed in chapter
14 as Model-I and Model-II. The accounting
database design has been discussed below in terms
of its implementation modalities in the context of
MS Access.
15.1 MS Access and its Components
It is one of the popularly used Database Management
System (DBMS) to create, store and manage
database. It is also popularly  called ACCESS.
Every component that is created using Access
is an object and several such similar objects
constitute a class. Access is functionally available
with the following seven-object classes.  Each of
these object classes is capable of creating their
respective object replicas.
• Tables : This object class allows a database
designer to create the data tables with their
respective fieldnames, data types and properties.
• Queries : This object class is meant to create the
SQL compatible query statement with or without
the help of Graphic User Interface (GUI) to define
tables, store data and retrieve both data and
information.
LEARNING OBJECTIVES
After studying this
chapter, you will be able
to :
• identify the resources
of MS ACCESS as
DBMS;
• create data tables
described in a data-
base design and set
relationship among
these tables;
• explain the ACCESS
basics and procedures
to create forms using
ACCESS;
• describe and create
voucher forms in
consonance with diffe-
rent database designs;
• identify information
requirement of reports
for querying databases;
• formulate and imple-
ment queries for retri-
eving data and inform-
ation for presentation
in accounting reports ;
and
• implement the process
in ACCESS for genera-
ting accounting reports
by using accounting
information queries.
15
Accounting System Using
Database Management System
555 Accounting System using DBMS
• Forms : This object class allows the designer to create an appropriate user
interface to formally interact with the back end database, defined by the
tables and queries.
• Reports: This object class is used to create various reports, the source of
information content of which is based on tables, queries or both. Such
reports are designed in Access according to the requirement of end-user.
• Pages : This object class is meant to create Data Access Pages, which can
be posted on a Web site of an organisation using Internet or sent via
e-mail to someone of the organisation’s network.
• Macros : In macro programming, the objects using individual instructions
called macro-oriented actions are manipulated. A Macro is a list of macro-
oriented actions that run as a unit. Access provides for such Macro
programming.
Fig. 15.1 : An example of database window to work in Access
• Modules : These are the foundations of any application and allow the
designer to create a set of programming instructions, called functions or
sub-routines that can be used throughout the application.
556 Accountancy
The functions return a value while subroutines do not return any value.
Access provides for creating such modules.
Each of these object classes is contained in the named database file of
Access with MDB extension. Whenever this file is opened, a database window,
as shown on next page, opens with all the above object classes available on
the left hand side. As and when the specific objects are created or designed,
they get listed on right hand side of this window against each of these object
classes.
Box 1
Capabilities of MS Access
Access has certain capabilities, which bring it closer to an ideal Database
Management System. These capabilities are :
• Storing the data in an organised manner.
• Enforcing data integrity constraints.
• Representing complex relationship among data.
• Providing for persistent storage of database objects.
• Restricting unauthorised access to database.
• Allowing fast retrieval of data with or without processing by using SQL.
• Flexibility to create multiple user interfaces.
• Providing for data sharing and multi-user transaction processing.
• Supporting multiple views of data and information.
15.1.1 Access Basics for Creating a Database
When a new database is created from the scratch, there is complete control
over the database objects, their properties and the relationships. In order to
create a new database without the help of database wizard (that is an
automated process in Access), the following steps are required :
(i) Open Access Window to choose blank Access database and click OK
button.
(ii) Access responds by displaying File New Database dialog box, which
prompts the designer to enter a file name and a location for the database.
This must be followed by clicking Create button.
(iii) If the task pane is not open, choose File from menu bar and click at
new to open the task pane to create a new database.
15.1.2 Creating of Tables in Access
The creation of tables in Access requires the following steps and understanding
of the components of table object.
557 Accounting System using DBMS
Click at Tables object of Access, followed by double click at create table by
design view. This results in providing a table window, the upper part of
which has three columns: Field Name, Data Type and Description. It is meant
to define the schema of a table being created. Each of its rows corresponds to
a column of the table being created. Two primary properties of the column of
a table are its field name and data type.
(a) Field name : refers to column name of the table being created. The name of
the column should be a string of contiguous characters. The Field name is
meant to define the name of column to be created, followed by data type of
such column.  The designer can optionally provide description of the column
also. Once the data type is defined, the designer can further specify the
properties of each column in the lower part of the Table window.
(b) Data Types : Access supports different data types, the details of which are
as given below :
• Text : It is used for a string of characters: words or numbers that are
not to be used in any arithmetic calculations. The maximum length for
a text field is 255 characters. It is the default data type because of
being used most frequently.
• Memo : It is used for storing comments and is capable of accommodating
65,536 characters. But a field with this data type is not amenable to
sorting or filtering of data records.
• Number : It is meant to store numbers, which could be integers (-32768
to 32767), long integers (–2,147,483,648 to 2,147,483,647), bytes
( 0 – 255), single (to store values with decimal point up to a certain
limit), double (to store values in decimal point with greater magnitude
and more precision) or decimal types.
• Date/Time : It is used to store dates, times or a combination of both.
• Currency : It is used for storing numbers in terms of Dollars, Rupees or
other Currencies.
• AutoNumber : It is a numeric data automatically entered by Access. It
is of particular importance in a situation where none of the fields
individually or a set of fields as a combination in a table is unique.
• Yes/No : It is to declare a logical field which may have only one of the two
opposite values alternatively given as: Yes or No, On or Off, True or False.
• OLE Object : OLE stands for Object Linking and Embedding. It refers to
an object that could be a photograph, bar code image or another
document created in another software application.
• Hyperlink : This data type is meant to store a Universal Resource Locator
(URL) and e-mail addresses.
(c) Properties : Once the data type of a column is specified, Access allows the
designer to define the properties of each column. These properties are of
two types General and Look up.
Page 5


554 Accountancy
I
n chapter 14, you have learnt about the
fundamentals of creating a database design in
the context of accounting system. This chapter deals
with the basics of MS Access for implementing the
databases and specifically deals with implementa-
tion of accounting databases, the design of which
has been shown, described and discussed in chapter
14 as Model-I and Model-II. The accounting
database design has been discussed below in terms
of its implementation modalities in the context of
MS Access.
15.1 MS Access and its Components
It is one of the popularly used Database Management
System (DBMS) to create, store and manage
database. It is also popularly  called ACCESS.
Every component that is created using Access
is an object and several such similar objects
constitute a class. Access is functionally available
with the following seven-object classes.  Each of
these object classes is capable of creating their
respective object replicas.
• Tables : This object class allows a database
designer to create the data tables with their
respective fieldnames, data types and properties.
• Queries : This object class is meant to create the
SQL compatible query statement with or without
the help of Graphic User Interface (GUI) to define
tables, store data and retrieve both data and
information.
LEARNING OBJECTIVES
After studying this
chapter, you will be able
to :
• identify the resources
of MS ACCESS as
DBMS;
• create data tables
described in a data-
base design and set
relationship among
these tables;
• explain the ACCESS
basics and procedures
to create forms using
ACCESS;
• describe and create
voucher forms in
consonance with diffe-
rent database designs;
• identify information
requirement of reports
for querying databases;
• formulate and imple-
ment queries for retri-
eving data and inform-
ation for presentation
in accounting reports ;
and
• implement the process
in ACCESS for genera-
ting accounting reports
by using accounting
information queries.
15
Accounting System Using
Database Management System
555 Accounting System using DBMS
• Forms : This object class allows the designer to create an appropriate user
interface to formally interact with the back end database, defined by the
tables and queries.
• Reports: This object class is used to create various reports, the source of
information content of which is based on tables, queries or both. Such
reports are designed in Access according to the requirement of end-user.
• Pages : This object class is meant to create Data Access Pages, which can
be posted on a Web site of an organisation using Internet or sent via
e-mail to someone of the organisation’s network.
• Macros : In macro programming, the objects using individual instructions
called macro-oriented actions are manipulated. A Macro is a list of macro-
oriented actions that run as a unit. Access provides for such Macro
programming.
Fig. 15.1 : An example of database window to work in Access
• Modules : These are the foundations of any application and allow the
designer to create a set of programming instructions, called functions or
sub-routines that can be used throughout the application.
556 Accountancy
The functions return a value while subroutines do not return any value.
Access provides for creating such modules.
Each of these object classes is contained in the named database file of
Access with MDB extension. Whenever this file is opened, a database window,
as shown on next page, opens with all the above object classes available on
the left hand side. As and when the specific objects are created or designed,
they get listed on right hand side of this window against each of these object
classes.
Box 1
Capabilities of MS Access
Access has certain capabilities, which bring it closer to an ideal Database
Management System. These capabilities are :
• Storing the data in an organised manner.
• Enforcing data integrity constraints.
• Representing complex relationship among data.
• Providing for persistent storage of database objects.
• Restricting unauthorised access to database.
• Allowing fast retrieval of data with or without processing by using SQL.
• Flexibility to create multiple user interfaces.
• Providing for data sharing and multi-user transaction processing.
• Supporting multiple views of data and information.
15.1.1 Access Basics for Creating a Database
When a new database is created from the scratch, there is complete control
over the database objects, their properties and the relationships. In order to
create a new database without the help of database wizard (that is an
automated process in Access), the following steps are required :
(i) Open Access Window to choose blank Access database and click OK
button.
(ii) Access responds by displaying File New Database dialog box, which
prompts the designer to enter a file name and a location for the database.
This must be followed by clicking Create button.
(iii) If the task pane is not open, choose File from menu bar and click at
new to open the task pane to create a new database.
15.1.2 Creating of Tables in Access
The creation of tables in Access requires the following steps and understanding
of the components of table object.
557 Accounting System using DBMS
Click at Tables object of Access, followed by double click at create table by
design view. This results in providing a table window, the upper part of
which has three columns: Field Name, Data Type and Description. It is meant
to define the schema of a table being created. Each of its rows corresponds to
a column of the table being created. Two primary properties of the column of
a table are its field name and data type.
(a) Field name : refers to column name of the table being created. The name of
the column should be a string of contiguous characters. The Field name is
meant to define the name of column to be created, followed by data type of
such column.  The designer can optionally provide description of the column
also. Once the data type is defined, the designer can further specify the
properties of each column in the lower part of the Table window.
(b) Data Types : Access supports different data types, the details of which are
as given below :
• Text : It is used for a string of characters: words or numbers that are
not to be used in any arithmetic calculations. The maximum length for
a text field is 255 characters. It is the default data type because of
being used most frequently.
• Memo : It is used for storing comments and is capable of accommodating
65,536 characters. But a field with this data type is not amenable to
sorting or filtering of data records.
• Number : It is meant to store numbers, which could be integers (-32768
to 32767), long integers (–2,147,483,648 to 2,147,483,647), bytes
( 0 – 255), single (to store values with decimal point up to a certain
limit), double (to store values in decimal point with greater magnitude
and more precision) or decimal types.
• Date/Time : It is used to store dates, times or a combination of both.
• Currency : It is used for storing numbers in terms of Dollars, Rupees or
other Currencies.
• AutoNumber : It is a numeric data automatically entered by Access. It
is of particular importance in a situation where none of the fields
individually or a set of fields as a combination in a table is unique.
• Yes/No : It is to declare a logical field which may have only one of the two
opposite values alternatively given as: Yes or No, On or Off, True or False.
• OLE Object : OLE stands for Object Linking and Embedding. It refers to
an object that could be a photograph, bar code image or another
document created in another software application.
• Hyperlink : This data type is meant to store a Universal Resource Locator
(URL) and e-mail addresses.
(c) Properties : Once the data type of a column is specified, Access allows the
designer to define the properties of each column. These properties are of
two types General and Look up.
558 Accountancy
 (i) General : In the context of text data type the general properties are :
• Field Size : This property, in case of text fields, refers to the maximum
number of characters allowed in the column. The same property, in
case of numbers, refers to the type of numbers being stored as per
requirements.
• Format : It is meant to indicate as to how the field’s contents are
displayed. There are standard types of formats to choose from.
• Decimal places property : It applies to single, double or decimal types
of numbers.
• Input mask : Formats for data entry that include placeholders and
punctuations are called input masks. It works only for text and date
type of fields. It is of particular importance when the accounting codes
being used in the system are formatted with hyphens.
• Caption : It is a label used for the field in datasheet view and on the
Forms and reports. If the caption property is set to blank, the field
name becomes the default caption and is used to label the field.
• Default Value : It is used for specifying a value for new entries of data
records. While entering the data item, the operator can always over
write the default value. The default value should be the most frequently
entered value in the field.
• Validation Rule and Text : Validation means checking of data to eliminate
incorrect entries. Validation criteria can be specified for this property.
If the data so entered does not satisfy the validation criteria, the
validation text gets displayed.
• Required and Indexed : The Required property must be provided a logical
value Yes or No. When a field’s required property is set to Yes, a user
must enter data in the field before saving the record. A value of No
implies that the data entry in the field is optional. In other words, a
null value is also acceptable to the database.   Indexing a field results
in speeding up sorting, searching and filtering of records on that field.
Primary key field is always indexed. For a single field primary key,
Access sets the Required property to Yes and the Indexed property to
Yes (No duplicates) because a primary key by definition must have
unique values without null entries.
• Allow-Zero Length :  This property is available only for text fields. Setting
it to Yes/No determines whether a text string with zero length is a
valid entry or not.
(ii) Look up : The look up feature is used by a field to find its values in another
table, query or from a fixed list of values. A list of valid values can be
displayed using a list box or combo box. Text box is the default display
control of look up. Look up is created in case of a field, which is foreign