NCERT Textbook - Structuring Database for Accounting | SSC CGL Tier 2 - Study Material, Online Tests, Previous Year PDF Download

 Page 1


504 Accountancy
Structuring Database for Accounting
LEARNING OBJECTIVES
After studying this chapter ,
you will be able to :
• identify the resources
of MS ACCESS as
DBMS;
• explain basic concepts
of database system;
• express accounting
reality in the context of
Entity Relationship
(ER) Model;
• transform ER pre-
sentation of accounting
reality into database;
• developdatabase
design for computer-
ised system using
Relational Data Model;
• formulate basic queries
for retrieving account-
ing data and informa-
tion.
I
n the earlier chapters, you have already learnt
that accounting of transactions are documented
with vouchers. Let us consider a few accounting
transaction to understand as to how these vouchers
are used.
On April 01, 05 M/s Kshipra Computers
commences business with initial capital of
Rs.5,00,000, which is deposited into bank. Recall the
journal entry that is recorded using manual
accounting system. This journal entry has data
contents that are filled-up using a simple transaction
voucher, which is prepared by Smith and authorised
by Aditya.
Fig. 14.1 : A sample transaction voucher to document simple
transactions involving one debit and one credit
14
M/s Kshipra Computers
TRANSACTION VOUCHER
Voucher No: 01 Date: Apr. 01, 2005
Debit Account:  642001 Bank Account
Credit Account:  110001 Capital Account
Amount in Rs. :  5,00,000
Narration:  Commenced business by depositing initial
capital into bank
Authorised By: Aditya Prepared By Smith
Page 2


504 Accountancy
Structuring Database for Accounting
LEARNING OBJECTIVES
After studying this chapter ,
you will be able to :
• identify the resources
of MS ACCESS as
DBMS;
• explain basic concepts
of database system;
• express accounting
reality in the context of
Entity Relationship
(ER) Model;
• transform ER pre-
sentation of accounting
reality into database;
• developdatabase
design for computer-
ised system using
Relational Data Model;
• formulate basic queries
for retrieving account-
ing data and informa-
tion.
I
n the earlier chapters, you have already learnt
that accounting of transactions are documented
with vouchers. Let us consider a few accounting
transaction to understand as to how these vouchers
are used.
On April 01, 05 M/s Kshipra Computers
commences business with initial capital of
Rs.5,00,000, which is deposited into bank. Recall the
journal entry that is recorded using manual
accounting system. This journal entry has data
contents that are filled-up using a simple transaction
voucher, which is prepared by Smith and authorised
by Aditya.
Fig. 14.1 : A sample transaction voucher to document simple
transactions involving one debit and one credit
14
M/s Kshipra Computers
TRANSACTION VOUCHER
Voucher No: 01 Date: Apr. 01, 2005
Debit Account:  642001 Bank Account
Credit Account:  110001 Capital Account
Amount in Rs. :  5,00,000
Narration:  Commenced business by depositing initial
capital into bank
Authorised By: Aditya Prepared By Smith
505 Structuring Database for Accounting
The same transaction can also be documented using  a credit voucher that
is capable of recording multiple credits against one debit, as shown below:
Fig. 14.2 : A sample voucher for multiple credits against one debit
Now consider the following transaction :
On April 03-20005 M/s Kshipra Computers bought goods costing Rs.50,000
from M/s R.S. and Sons, paying Rs.2,000 as cartage to M/s Saini Transports.
This transaction involves multiple debits of accounts with one account being
credited. The debit voucher that is used to document this transaction appears
as follows :
Fig. 14.3 : A sample vouchers for multiple debits against one credit
CREDIT VOUCHER
Voucher No:  01                                                                     Date: April 01,2005
Debit Account:  642001 Bank Account M/s Kshipra Computers
Credit Accounts
S.No Code Name of Account Amount Narration
1 110001 Capital Account 5,00,000 Commenced Business
Total Amount 5,00,000
Authorised By: Aditya Prepared By Smith
DEBIT VOUCHER
Voucher No:  05                                                                     Date: April 03, 2005
Credit Account:  642001 Bank Account M/s Kshipra Computers
Debit Accounts
S.No Code Name of Account Amount Narration
1 711001 Purchases 50,000 Purchases from R.S & Sons
2. 711003 Carriage Inwards 2,000 Paid to M/s Saini Transports
Total Amount 52,000
Authorised By: Aditya Prepared By Smith
Page 3


504 Accountancy
Structuring Database for Accounting
LEARNING OBJECTIVES
After studying this chapter ,
you will be able to :
• identify the resources
of MS ACCESS as
DBMS;
• explain basic concepts
of database system;
• express accounting
reality in the context of
Entity Relationship
(ER) Model;
• transform ER pre-
sentation of accounting
reality into database;
• developdatabase
design for computer-
ised system using
Relational Data Model;
• formulate basic queries
for retrieving account-
ing data and informa-
tion.
I
n the earlier chapters, you have already learnt
that accounting of transactions are documented
with vouchers. Let us consider a few accounting
transaction to understand as to how these vouchers
are used.
On April 01, 05 M/s Kshipra Computers
commences business with initial capital of
Rs.5,00,000, which is deposited into bank. Recall the
journal entry that is recorded using manual
accounting system. This journal entry has data
contents that are filled-up using a simple transaction
voucher, which is prepared by Smith and authorised
by Aditya.
Fig. 14.1 : A sample transaction voucher to document simple
transactions involving one debit and one credit
14
M/s Kshipra Computers
TRANSACTION VOUCHER
Voucher No: 01 Date: Apr. 01, 2005
Debit Account:  642001 Bank Account
Credit Account:  110001 Capital Account
Amount in Rs. :  5,00,000
Narration:  Commenced business by depositing initial
capital into bank
Authorised By: Aditya Prepared By Smith
505 Structuring Database for Accounting
The same transaction can also be documented using  a credit voucher that
is capable of recording multiple credits against one debit, as shown below:
Fig. 14.2 : A sample voucher for multiple credits against one debit
Now consider the following transaction :
On April 03-20005 M/s Kshipra Computers bought goods costing Rs.50,000
from M/s R.S. and Sons, paying Rs.2,000 as cartage to M/s Saini Transports.
This transaction involves multiple debits of accounts with one account being
credited. The debit voucher that is used to document this transaction appears
as follows :
Fig. 14.3 : A sample vouchers for multiple debits against one credit
CREDIT VOUCHER
Voucher No:  01                                                                     Date: April 01,2005
Debit Account:  642001 Bank Account M/s Kshipra Computers
Credit Accounts
S.No Code Name of Account Amount Narration
1 110001 Capital Account 5,00,000 Commenced Business
Total Amount 5,00,000
Authorised By: Aditya Prepared By Smith
DEBIT VOUCHER
Voucher No:  05                                                                     Date: April 03, 2005
Credit Account:  642001 Bank Account M/s Kshipra Computers
Debit Accounts
S.No Code Name of Account Amount Narration
1 711001 Purchases 50,000 Purchases from R.S & Sons
2. 711003 Carriage Inwards 2,000 Paid to M/s Saini Transports
Total Amount 52,000
Authorised By: Aditya Prepared By Smith
506 Accountancy
The process of computerised accounting involves identifying, storing and
retrieving the data content of an accounting transaction. This requires a
mechanism to store such data content of vouchers in a manner that allows its
easy and convenient retrieval as and when required. This is achieved by designing
suitable database for accounting. Such a database consists of inter-related data
tables that are structured in a manner that ensures data consistency and
integrity. In this chapter we shall discuss the basic concepts of database system
of accounting.
14.1 Data Processing Cycle
In order to understand the dynamics of database design, let us understand the
data processing cycle in the context of accounting. Data processing involves the
technique of collecting, sorting, relating, interpreting and computing data items
in such a manner as to provide meaningful and useful information for decision-
making. The necessary steps involved in data processing cycle are data capturing,
inputing, processing and generating information available to the user. Data
processing cycle, when thought of in the context of accounting, requires a series
of steps that have been described below briefly :
(i) Source Documents : The first step is to capture accounting data from
transaction(s) so as to prepare a document, called voucher (as already
stated earlier), that expresses and documents an accounting transaction.
The relevant accounting data is set out in the voucher, the sample of which
is shown in figures 14.1 to 14.3. These documents are so designed as to
permit the recording of accounting data in a systematic manner.
(ii) Input of Data : The accounting data contained in vouchers is to be entered
in a computer’s storage device. This is achieved by using a pre-designed
Data Entry Form. This data entry form is designed in a manner that it is
similar to physical voucher document. The data entry form is designed
using software and it is made to appear on the computer monitor so that
the data is entered.
(iii) Data Storage : A suitable data storage structure is required to provide for
a blank data record as shown below:
Code Name Type
Page 4


504 Accountancy
Structuring Database for Accounting
LEARNING OBJECTIVES
After studying this chapter ,
you will be able to :
• identify the resources
of MS ACCESS as
DBMS;
• explain basic concepts
of database system;
• express accounting
reality in the context of
Entity Relationship
(ER) Model;
• transform ER pre-
sentation of accounting
reality into database;
• developdatabase
design for computer-
ised system using
Relational Data Model;
• formulate basic queries
for retrieving account-
ing data and informa-
tion.
I
n the earlier chapters, you have already learnt
that accounting of transactions are documented
with vouchers. Let us consider a few accounting
transaction to understand as to how these vouchers
are used.
On April 01, 05 M/s Kshipra Computers
commences business with initial capital of
Rs.5,00,000, which is deposited into bank. Recall the
journal entry that is recorded using manual
accounting system. This journal entry has data
contents that are filled-up using a simple transaction
voucher, which is prepared by Smith and authorised
by Aditya.
Fig. 14.1 : A sample transaction voucher to document simple
transactions involving one debit and one credit
14
M/s Kshipra Computers
TRANSACTION VOUCHER
Voucher No: 01 Date: Apr. 01, 2005
Debit Account:  642001 Bank Account
Credit Account:  110001 Capital Account
Amount in Rs. :  5,00,000
Narration:  Commenced business by depositing initial
capital into bank
Authorised By: Aditya Prepared By Smith
505 Structuring Database for Accounting
The same transaction can also be documented using  a credit voucher that
is capable of recording multiple credits against one debit, as shown below:
Fig. 14.2 : A sample voucher for multiple credits against one debit
Now consider the following transaction :
On April 03-20005 M/s Kshipra Computers bought goods costing Rs.50,000
from M/s R.S. and Sons, paying Rs.2,000 as cartage to M/s Saini Transports.
This transaction involves multiple debits of accounts with one account being
credited. The debit voucher that is used to document this transaction appears
as follows :
Fig. 14.3 : A sample vouchers for multiple debits against one credit
CREDIT VOUCHER
Voucher No:  01                                                                     Date: April 01,2005
Debit Account:  642001 Bank Account M/s Kshipra Computers
Credit Accounts
S.No Code Name of Account Amount Narration
1 110001 Capital Account 5,00,000 Commenced Business
Total Amount 5,00,000
Authorised By: Aditya Prepared By Smith
DEBIT VOUCHER
Voucher No:  05                                                                     Date: April 03, 2005
Credit Account:  642001 Bank Account M/s Kshipra Computers
Debit Accounts
S.No Code Name of Account Amount Narration
1 711001 Purchases 50,000 Purchases from R.S & Sons
2. 711003 Carriage Inwards 2,000 Paid to M/s Saini Transports
Total Amount 52,000
Authorised By: Aditya Prepared By Smith
506 Accountancy
The process of computerised accounting involves identifying, storing and
retrieving the data content of an accounting transaction. This requires a
mechanism to store such data content of vouchers in a manner that allows its
easy and convenient retrieval as and when required. This is achieved by designing
suitable database for accounting. Such a database consists of inter-related data
tables that are structured in a manner that ensures data consistency and
integrity. In this chapter we shall discuss the basic concepts of database system
of accounting.
14.1 Data Processing Cycle
In order to understand the dynamics of database design, let us understand the
data processing cycle in the context of accounting. Data processing involves the
technique of collecting, sorting, relating, interpreting and computing data items
in such a manner as to provide meaningful and useful information for decision-
making. The necessary steps involved in data processing cycle are data capturing,
inputing, processing and generating information available to the user. Data
processing cycle, when thought of in the context of accounting, requires a series
of steps that have been described below briefly :
(i) Source Documents : The first step is to capture accounting data from
transaction(s) so as to prepare a document, called voucher (as already
stated earlier), that expresses and documents an accounting transaction.
The relevant accounting data is set out in the voucher, the sample of which
is shown in figures 14.1 to 14.3. These documents are so designed as to
permit the recording of accounting data in a systematic manner.
(ii) Input of Data : The accounting data contained in vouchers is to be entered
in a computer’s storage device. This is achieved by using a pre-designed
Data Entry Form. This data entry form is designed in a manner that it is
similar to physical voucher document. The data entry form is designed
using software and it is made to appear on the computer monitor so that
the data is entered.
(iii) Data Storage : A suitable data storage structure is required to provide for
a blank data record as shown below:
Code Name Type
507 Structuring Database for Accounting
The above blank record that is used for storing the input of data pertaining
code of account, name of account and the category type to which it belongs
is shown below as :
Code Name Type
11001 Capital Account 4
711001 Purchases Account 1
Hypothetically, the category type 4 above refers to Liabilities and the
category type 1 indicates Expenses. The data storage structures (also called
data tables) are created as a part of structuring database for accounting.
(iv) Manipulation of Data : The stored data is manipulated for necessary
transformation to generate final reports. Such transformed data may be
stored separately and subsequently used for generating final reports.
Alternatively, the transformed data can be directly presented in the form
of a report.
(v) Output of Data : The accounting reports such as ledger, trial balance, etc.
are obtained in a pre-designed format by accessing the transformed data.
Now that you have understood the way data content is stored in structured
manner, we shall discuss how the data structures are designed in consonance
with the data content that emerges from accounting transactions.
14.2 Designing Database for Accounting
Both computerised and computer-based AIS require a definite data structure
for storing the accounting data. As already mentioned, the databases are used
for storing accounting data. The process of designing database (for accounting)
begins with a reality (or accounting reality) that is expressed using elements of a
conceptual data model. The process of designing a database for accounting is
best described through a flow chart (Figure : 14.4).
Reality : It refers to some aspect of real world situation, for which database is to
be designed. In the context of accounting, it is accounting reality that is to be
expressed with complete description.
ER Design : This is a formal blue print, with a pictorial presentation, in which
Entity Relationship (ER) Model concepts are used to represent description of
reality.
Relational Data Model : It is representational data model through which ER
design is transformed into inter-related data tables along with the restriction in
the form of rules that are specified to ensure the consistency and integrity of
stored data.
Page 5


504 Accountancy
Structuring Database for Accounting
LEARNING OBJECTIVES
After studying this chapter ,
you will be able to :
• identify the resources
of MS ACCESS as
DBMS;
• explain basic concepts
of database system;
• express accounting
reality in the context of
Entity Relationship
(ER) Model;
• transform ER pre-
sentation of accounting
reality into database;
• developdatabase
design for computer-
ised system using
Relational Data Model;
• formulate basic queries
for retrieving account-
ing data and informa-
tion.
I
n the earlier chapters, you have already learnt
that accounting of transactions are documented
with vouchers. Let us consider a few accounting
transaction to understand as to how these vouchers
are used.
On April 01, 05 M/s Kshipra Computers
commences business with initial capital of
Rs.5,00,000, which is deposited into bank. Recall the
journal entry that is recorded using manual
accounting system. This journal entry has data
contents that are filled-up using a simple transaction
voucher, which is prepared by Smith and authorised
by Aditya.
Fig. 14.1 : A sample transaction voucher to document simple
transactions involving one debit and one credit
14
M/s Kshipra Computers
TRANSACTION VOUCHER
Voucher No: 01 Date: Apr. 01, 2005
Debit Account:  642001 Bank Account
Credit Account:  110001 Capital Account
Amount in Rs. :  5,00,000
Narration:  Commenced business by depositing initial
capital into bank
Authorised By: Aditya Prepared By Smith
505 Structuring Database for Accounting
The same transaction can also be documented using  a credit voucher that
is capable of recording multiple credits against one debit, as shown below:
Fig. 14.2 : A sample voucher for multiple credits against one debit
Now consider the following transaction :
On April 03-20005 M/s Kshipra Computers bought goods costing Rs.50,000
from M/s R.S. and Sons, paying Rs.2,000 as cartage to M/s Saini Transports.
This transaction involves multiple debits of accounts with one account being
credited. The debit voucher that is used to document this transaction appears
as follows :
Fig. 14.3 : A sample vouchers for multiple debits against one credit
CREDIT VOUCHER
Voucher No:  01                                                                     Date: April 01,2005
Debit Account:  642001 Bank Account M/s Kshipra Computers
Credit Accounts
S.No Code Name of Account Amount Narration
1 110001 Capital Account 5,00,000 Commenced Business
Total Amount 5,00,000
Authorised By: Aditya Prepared By Smith
DEBIT VOUCHER
Voucher No:  05                                                                     Date: April 03, 2005
Credit Account:  642001 Bank Account M/s Kshipra Computers
Debit Accounts
S.No Code Name of Account Amount Narration
1 711001 Purchases 50,000 Purchases from R.S & Sons
2. 711003 Carriage Inwards 2,000 Paid to M/s Saini Transports
Total Amount 52,000
Authorised By: Aditya Prepared By Smith
506 Accountancy
The process of computerised accounting involves identifying, storing and
retrieving the data content of an accounting transaction. This requires a
mechanism to store such data content of vouchers in a manner that allows its
easy and convenient retrieval as and when required. This is achieved by designing
suitable database for accounting. Such a database consists of inter-related data
tables that are structured in a manner that ensures data consistency and
integrity. In this chapter we shall discuss the basic concepts of database system
of accounting.
14.1 Data Processing Cycle
In order to understand the dynamics of database design, let us understand the
data processing cycle in the context of accounting. Data processing involves the
technique of collecting, sorting, relating, interpreting and computing data items
in such a manner as to provide meaningful and useful information for decision-
making. The necessary steps involved in data processing cycle are data capturing,
inputing, processing and generating information available to the user. Data
processing cycle, when thought of in the context of accounting, requires a series
of steps that have been described below briefly :
(i) Source Documents : The first step is to capture accounting data from
transaction(s) so as to prepare a document, called voucher (as already
stated earlier), that expresses and documents an accounting transaction.
The relevant accounting data is set out in the voucher, the sample of which
is shown in figures 14.1 to 14.3. These documents are so designed as to
permit the recording of accounting data in a systematic manner.
(ii) Input of Data : The accounting data contained in vouchers is to be entered
in a computer’s storage device. This is achieved by using a pre-designed
Data Entry Form. This data entry form is designed in a manner that it is
similar to physical voucher document. The data entry form is designed
using software and it is made to appear on the computer monitor so that
the data is entered.
(iii) Data Storage : A suitable data storage structure is required to provide for
a blank data record as shown below:
Code Name Type
507 Structuring Database for Accounting
The above blank record that is used for storing the input of data pertaining
code of account, name of account and the category type to which it belongs
is shown below as :
Code Name Type
11001 Capital Account 4
711001 Purchases Account 1
Hypothetically, the category type 4 above refers to Liabilities and the
category type 1 indicates Expenses. The data storage structures (also called
data tables) are created as a part of structuring database for accounting.
(iv) Manipulation of Data : The stored data is manipulated for necessary
transformation to generate final reports. Such transformed data may be
stored separately and subsequently used for generating final reports.
Alternatively, the transformed data can be directly presented in the form
of a report.
(v) Output of Data : The accounting reports such as ledger, trial balance, etc.
are obtained in a pre-designed format by accessing the transformed data.
Now that you have understood the way data content is stored in structured
manner, we shall discuss how the data structures are designed in consonance
with the data content that emerges from accounting transactions.
14.2 Designing Database for Accounting
Both computerised and computer-based AIS require a definite data structure
for storing the accounting data. As already mentioned, the databases are used
for storing accounting data. The process of designing database (for accounting)
begins with a reality (or accounting reality) that is expressed using elements of a
conceptual data model. The process of designing a database for accounting is
best described through a flow chart (Figure : 14.4).
Reality : It refers to some aspect of real world situation, for which database is to
be designed. In the context of accounting, it is accounting reality that is to be
expressed with complete description.
ER Design : This is a formal blue print, with a pictorial presentation, in which
Entity Relationship (ER) Model concepts are used to represent description of
reality.
Relational Data Model : It is representational data model through which ER
design is transformed into inter-related data tables along with the restriction in
the form of rules that are specified to ensure the consistency and integrity of
stored data.
508 Accountancy
Fig. 14.4 : Flow Chart depicting the process of designing a database for accounting
Normalisation : This is process of refining a database design (that consists of
inter-related data tables) through which the possibility of duplicate or redundant
data items is reduced or eliminated.
Refinement : This is the outcome of the process of normalisation as mentioned
above. The final database design is arrived at after the process of normalisation
is completed.
14.3 Entity Relationship (ER) Model
It is a popular conceptual data model, which is mostly used in database-oriented
applications. The major elements of ER Model are entities, attributes, identifiers
and relationships that are used to express a reality for which a database is to be
designed. The model is best depicted with the help of ER symbols, the list and
description of which is shown in figure 14.5. While preparing an ER Diagram,
the following symbols are used to represent different types of entities, attributes,
identifiers and relationships :