📊
📈
📉
💼
💰
← Back to Dashboard
Font Size:

1. Introduction

A database is an organised collection of related data, and a Database Management System (DBMS) is the software that manages, stores, retrieves and protects this data. In an accounting context, the DBMS stores all the accounting data such as ledgers, vouchers, customers, suppliers and inventory in a structured form. An accounting system built on a DBMS allows multiple users to access the same data simultaneously, keeps the data consistent and secure, and produces reliable reports without duplication. This chapter explains the concepts of database, DBMS, its advantages and limitations, and the structure of an accounting system using a DBMS.

The most widely used model of a database is the relational database, in which data is stored in tables consisting of rows and columns. The relational model uses keys to link the tables so that information from different tables can be combined. In accounting, for example, the vouchers table, the ledger table and the accounts table are linked through common fields. This chapter also explains concepts such as primary keys, foreign keys, querying and the advantages of the relational database over file-based systems.

2. Database and DBMS

A database is a well-organised collection of data that can be accessed, managed and updated easily. Examples are a library catalogue, a customer list and a database of vouchers. A Database Management System (DBMS) is the software that creates, maintains and manipulates the database. It provides the tools to enter, store, retrieve, update and organise data and ensures that data is shared among users in a controlled way.

Common examples of DBMS software are MySQL, Oracle, Microsoft Access and SQL Server. These systems support the storage of the accounting data of a business and allow the accountants to query the data and generate reports.

3. Advantages of a DBMS in Accounting

  1. Reduction of redundancy: the same data is not stored repeatedly, saving storage space and preventing inconsistency.
  2. Data consistency: the data is always up to date and accurate because all users see the same version.
  3. Data sharing: many users can access the same database at the same time.
  4. Data security: access can be restricted through passwords and user rights.
  5. Data integrity: rules ensure that the data entered is valid and correct.
  6. Easy retrieval and reporting: queries can be run to extract information quickly and reports can be generated automatically.
  7. Backup and recovery: data can be restored in case of failure.
  8. Centralised control: the database administrator can control the whole data centrally.

4. Limitations of a DBMS

  1. The cost of hardware, software and skilled staff is high.
  2. The system is complex and requires training.
  3. If the system fails, the entire accounting work may stop.
  4. Data may be lost or corrupted if the database is not properly maintained and backed up.
  5. The performance may slow down when the number of users is large.

5. The Relational Database Model

The relational database model stores data in the form of tables, also called relations. Each table is made up of rows (records or tuples) and columns (fields or attributes). For example, a customer table may have the fields customer ID, name and address. The tables are related to one another through keys.

Primary Key

A primary key is a field or combination of fields that uniquely identifies each record in a table. For example, the customer ID in the customer table is the primary key because no two customers can have the same ID.

Foreign Key

A foreign key is a field in one table that refers to the primary key of another table, creating a link between the two tables. For example, the account number in the vouchers table may be a foreign key referring to the accounts table.

Query

A query is a request for data from the database, usually written in a language such as SQL. For example, a query can extract all the sales made to a particular customer.

6. Accounting System using a Relational DBMS

An accounting system based on a relational DBMS stores the accounting data in tables such as:

  1. Accounts table: contains the account number, account name and the type of account (asset, liability, capital, income or expense).
  2. Vouchers table: contains the voucher number, date, account debited, account credited and the amount.
  3. Customers table: contains the customer ID, name and address.
  4. Suppliers table: contains the supplier ID, name and address.

The voucher table is linked to the accounts table through the account numbers, so that a complete ledger can be generated automatically. The transaction data is entered once, and the system generates the ledger, the trial balance and the final accounts through queries and reports.

7. Grouping of Accounts

In a computerised accounting system, the accounts are grouped according to their nature. For example:

Group Examples
Capital Capital account
Liabilities Loans, creditors
Assets Fixed assets, current assets
Income Sales, interest received
Expense Purchases, salaries, rent

Grouping helps in the automatic preparation of the Profit and Loss Account and the Balance Sheet, because the balances of the income and expense groups are transferred to the Profit and Loss Account, and the balances of the asset, liability and capital groups are shown in the Balance Sheet.

8. Steps in Operating an Accounting System with DBMS

  1. Create the database and define the tables for accounts, vouchers, customers and suppliers.
  2. Enter the opening balances of the accounts.
  3. Record the day-to-day transactions as vouchers.
  4. Run queries to update the ledgers automatically.
  5. Generate the trial balance, Profit and Loss Account and Balance Sheet.
  6. Take regular backups of the database for security.

Quick Revision Tables

Table 1: Database Terms

Term Meaning
Database Organised collection of related data
DBMS Software to manage the database
Table Rows and columns of related data
Record A row in a table
Field A column in a table
Primary key Uniquely identifies each record
Foreign key Links a table to another table

Table 2: Advantages vs Limitations of DBMS

Advantages Limitations
Reduced redundancy High cost
Data consistency Complex system
Data sharing Needs trained staff
Security and integrity System failure risk
Easy reporting Needs regular backups

Table 3: Account Groups

Group Type
Assets Balance Sheet item
Liabilities Balance Sheet item
Capital Balance Sheet item
Income Profit and Loss item
Expense Profit and Loss item

Mind Map

graph TD A["Accounting System using DBMS"] --> B["Database and DBMS"] B --> C["Organised collection of data and its management"] A --> D["Relational Model"] D --> E["Tables, rows and columns"] D --> F["Primary key and foreign key"] D --> G["Queries in SQL"] A --> H["Accounting Tables"] H --> I["Accounts, vouchers, customers, suppliers"] A --> J["Advantages"] J --> K["No redundancy, consistency, sharing, security"] A --> L["Grouping of Accounts"] L --> M["Balance Sheet and Profit and Loss groups"] A --> N["Reports"] N --> O["Ledger, trial balance, final accounts"]

Important Diagrams (SVG)

Diagram 1: Tables in a Relational Accounting Database

Relational Accounting Database Accounts Table Account No (Primary Key) Account Name, Type Vouchers Table Voucher No, Date Account No (Foreign Key), Amount Customers Table Customer ID (Primary Key) Name, Address Suppliers Table Supplier ID (Primary Key) Name, Address Links Primary keys of accounts, customers and suppliers are used as foreign keys in the vouchers table Golden Rule Link tables through primary and foreign keys so that data is stored once but used many times without duplication.

Diagram 2: Working of an Accounting System with a DBMS

Working of an Accounting System with a DBMS 1. Create Database and Tables 2. Enter Opening Balances 3. Record Vouchers 4. Update Ledgers Automatically 5. Generate Reports and Backup Golden Rule Enter each transaction only once; the DBMS updates all related accounts and produces accurate reports automatically.

Common Mistakes

  1. A database and a DBMS are confused; the database is the collection of data while the DBMS is the software that manages it.
  2. The primary key and foreign key are interchanged; a primary key uniquely identifies a record while a foreign key links two tables.
  3. Students store the same data in many tables, creating redundancy, which the relational model is designed to avoid.
  4. The grouping of accounts is ignored, so the automatic preparation of the Profit and Loss Account and Balance Sheet becomes impossible.
  5. It is assumed that the DBMS does not need any security, while data security and integrity are central to a DBMS.
  6. Queries are confused with reports; a query extracts the data while a report presents it in a formal format.
  7. Students forget the limitation of cost and complexity while listing the advantages of the DBMS.

Exam Tips

  1. Define a database and a DBMS with a clear example in each case.
  2. Write the four main accounting tables and give one field of each.
  3. Explain the primary key and foreign key with the help of the accounts and vouchers tables.
  4. Present the advantages and limitations of the DBMS in two point lists.
  5. Show the grouping of accounts and mention which groups go to the Balance Sheet and which to the Profit and Loss Account.
  6. Mention SQL as the query language used to extract data from the database.
  7. Give the steps of operating an accounting system with a DBMS in order.

Conclusion

An accounting system built on a Database Management System organises the accounting data into related tables and processes it efficiently through keys and queries. The relational model removes redundancy, maintains consistency and allows data to be shared securely among users. By grouping the accounts properly, the system generates the ledger, trial balance and final accounts automatically. Although a DBMS involves higher cost and complexity, its advantages of accuracy, security, sharing and easy reporting make it the foundation of modern computerised accounting.