Database Management System (DBMS) | ISC Class 12 Accounts Notes

This note covers the concept and features of a Database Management System (DBMS), the basic SQL commands (SELECT, WHERE, AND, OR, UPDATE, DELETE) and functions (AVG, COUNT, MAX, MIN, SUM) used to retrieve and update data, the elements of database design (tables, fields, relationships, forms, reports and indexing), and the use of DBMS in business applications such as accounting information, debtors and creditors records, the Bank Reconciliation Statement and asset accounting.

What is a Database Management System (DBMS)?

A database is an organised collection of related data stored so that it can be entered, stored, retrieved and updated efficiently.

A Database Management System (DBMS) is software that creates, maintains and controls access to a database, allowing a user to add, change, search and delete data without having to manage the physical storage of that data directly.

Definition: DBMS. A Database Management System is a software package that enables users to define, create, maintain and control access to a database, so that data can be stored once and used for many purposes without unnecessary duplication.

In an accounting context, a firm's ledgers, vouchers, stock records and fixed asset registers can all be held as linked data in a single database rather than as separate paper books or unconnected spreadsheets.

The accountant or data entry operator interacts with the DBMS through commands and screens, while the DBMS itself looks after storage, retrieval and the accuracy of the stored data.

What are the types and features of a DBMS?

DBMS packages are commonly described by the way they organise data. A relational DBMS (RDBMS) stores data in tables that are linked to one another through common fields, and most accounting software used for maintaining books of account is built on this relational model.

Other types mentioned in general use include hierarchical and network database systems, which organise records as linked structures rather than as tables.

The basic features of a DBMS include the ability to define the structure of data, to store large volumes of data compactly, to retrieve selected data quickly through queries, to update or delete specific records without disturbing the rest of the database, to control who may view or change data, and to keep data consistent when the same information is used in more than one place.

Definition: Data redundancy. Data redundancy is the unnecessary repetition of the same piece of data in more than one place in a set of records, which a properly designed database tries to minimise.

Because a DBMS keeps a single, shared copy of data, a change made in one place, such as a correction to a customer's address, is reflected wherever that data is used, which reduces data redundancy and helps maintain data integrity, meaning that the data remains accurate and consistent.

How is data organised within a database?

A relational database is built from tables, also called relations, each of which stores data about one type of thing, such as customers, suppliers or fixed assets.

Each table is made up of fields (columns), which describe a characteristic of the data, such as Customer Name or Balance, and records (rows), each of which holds the complete set of field values for one entry, such as one customer.

A primary key is a field, such as a Customer ID or Voucher Number, whose value is different for every record and so uniquely identifies that record.

A relationship is a link between two tables, usually created by placing the primary key of one table as a foreign key, a matching field, in another table, so that data in different tables can be connected and used together.

For example, a Debtors table holding customer balances can be related to a Sales table through the common Customer ID field, so that a single query can bring together a customer's name, address and outstanding balance even though this data is stored in two separate tables.

What are forms, reports and indexing used for?

A form is an on-screen input layout used to enter or view one record at a time in a simple, labelled format, so that a data entry operator does not need to type data directly into rows and columns of a table.

A report is a formatted output, usually printed or exported, that presents selected data from one or more tables in an organised way, such as a monthly statement of debtors or a list of fixed assets due for depreciation.

Definition: Indexing. Indexing is the creation of a separate, ordered reference built on one or more fields of a table so that the DBMS can locate and retrieve matching records far more quickly than by searching every record in turn.

Indexing works in the same way as an index at the back of a book: instead of reading every page to find a topic, the index points directly to the relevant page.

If a Debtors table is indexed on the Customer ID field, a query asking for one customer's balance can be answered almost immediately, even if the table holds thousands of records, because the DBMS does not have to check every row.

Database design therefore involves deciding which tables are needed, which fields belong in each table, how the tables are related to one another, which fields should be indexed for speed of retrieval, and how forms and reports will be used to enter and present the data.

How do SQL commands retrieve and update data?

SQL, or Structured Query Language, is the language used to create, retrieve, update and delete data held in a relational DBMS. A statement written in SQL to ask the database to do something is called a query.

The SELECT command is used to retrieve data, naming the fields to be shown and the table they come from.

The WHERE clause is added to a SELECT, UPDATE or DELETE command to restrict the action to records that meet a particular condition, such as a balance above a certain amount or an invoice dated before a given day.

The AND operator and the OR operator are used inside a WHERE clause to combine more than one condition.

AND requires every stated condition to be true before a record is selected, while OR selects a record if at least one of the stated conditions is true.

The UPDATE command changes existing field values, for example revising a customer's balance after a payment, and the DELETE command removes records that are no longer required, for example a closed account.

Worked example 1. A firm's Debtors table has the fields CustomerID, CustomerName and Balance. It wants to list the names and balances of all customers whose outstanding balance exceeds ₹50,000.

Answer: The query SELECT CustomerName, Balance FROM Debtors WHERE Balance > 50000; uses SELECT to choose the two required fields and WHERE to restrict the output to balances over ₹50,000. The firm should run this query each month to prepare its list of customers for follow-up on overdue amounts.

Worked example 2. A customer, whose CustomerID is C007, pays ₹15,000 against an outstanding balance of ₹45,000 recorded in the Debtors table.

Answer: The query UPDATE Debtors SET Balance = Balance - 15000 WHERE CustomerID = 'C007'; uses UPDATE with a WHERE clause so that only this one customer's balance is reduced from ₹45,000 to ₹30,000.

The accountant should choose the UPDATE command rather than re-entering the whole record, since it changes only the Balance field and leaves the rest of the record unaffected.

How do SQL functions summarise data?

Besides retrieving individual records, a DBMS can calculate summary figures across many records at once using built-in functions. These are especially useful in accounting, where totals, averages and extreme values are needed regularly.

The SUM function adds up a numeric field, such as finding the total amount owed by all creditors. The AVG function calculates the average of a field, for example the average sale value for the month.

The COUNT function counts how many records match a condition, for example how many debtors are overdue. The MAX function and MIN function find the highest and lowest values respectively, such as the highest and lowest balances owed by customers.

Worked example 3. A Creditors table lists the amount owed to each supplier in a field called Balance. The firm wants the total amount it currently owes to all suppliers.

Answer: The query SELECT SUM(Balance) FROM Creditors; applies the SUM function to the Balance field across every record in the Creditors table and returns a single total figure.

The accountant should use this single query rather than adding figures manually from a printed ledger, since it is faster and less open to arithmetic error.

How is DBMS used in accounting information systems?

An accounting information system built on a DBMS stores transaction data, such as sales, purchases, receipts and payments, in related tables and uses queries to produce ledgers, trial balances and financial statements on demand, instead of these being written out by hand.

Because the same underlying data is used to produce every report, figures remain consistent across the cash book, the ledger and the final accounts.

Storing accounting data in a DBMS also makes it straightforward to search for a particular transaction, such as a payment to a named supplier on a given date, by writing a SELECT query with a WHERE condition, rather than paging through a physical cash book or journal.

How does DBMS help manage debtors and creditors?

A Debtors table holds data on customers who owe money to the firm, typically including the customer's identity, the amount outstanding and the due date, while a Creditors table holds the equivalent data for suppliers to whom the firm owes money.

Keeping these as database tables allows the firm to query for overdue debtors, to total the amount owed to any one creditor, and to update balances automatically each time a receipt or payment is recorded.

Worked example 4. A firm wants to know how many debtors in its Debtors table have a Balance greater than zero and a DueDate earlier than today's date, so that it can send reminder letters.

Answer: The query SELECT COUNT(CustomerName) FROM Debtors WHERE Balance > 0 AND DueDate < CURRENT_DATE; combines two conditions with the AND operator, so only overdue customers with an outstanding balance are counted, and uses COUNT to give the number of such customers.

The firm should recommend this query to its accounts clerk as a routine check before issuing reminder letters.

Because debtor and creditor data is linked to the Sales and Purchases tables through a common customer or supplier identity field, a single update, such as recording a new sale, can automatically carry through to update the correct customer's outstanding balance without the accountant having to amend more than one record by hand.

How does DBMS support the Bank Reconciliation Statement?

A Bank Reconciliation Statement (BRS) compares the balance shown in a firm's own cash book with the balance shown in the bank's passbook or statement for the same period, and explains the differences, such as cheques issued but not yet presented, or cheques deposited but not yet cleared.

When bank and cash book data are both held in a database, a query can compare the two sets of transactions directly, for example by matching transaction references, and list only the entries that appear in one table but not the other.

This use of SELECT and WHERE conditions to compare two related tables makes it possible to identify unmatched items for the reconciliation statement far more quickly than checking entries one by one on paper, and reduces the chance that a difference is overlooked.

How does DBMS support asset accounting?

An asset register held in a database stores data on each fixed asset, such as its description, date of purchase, cost, rate of depreciation and accumulated depreciation, with one record for every asset.

Queries can then be used to find the written down value of any asset, to list all assets of a particular type, or to total the depreciation charge for the year using the SUM function.

Because the asset register is a database table, updating the accumulated depreciation figure for every asset at the end of the year can be done through a single UPDATE command applied to all matching records, rather than recalculating and rewriting each asset's figures separately.

Advantages of DBMS in business applications

A DBMS offers several advantages over keeping accounting data on paper or in unconnected files. It reduces data redundancy, since the same data is stored once and shared by every report that needs it.

It improves data integrity and consistency, because an update made in one place is reflected everywhere that data is used.

It allows fast retrieval of specific information through queries, even from very large volumes of data, especially where indexing is used.

It also allows controlled access, so that different users can be permitted to view or change only the data relevant to their work.

Limitations of DBMS in business applications

A DBMS also has limitations. Setting up a database correctly requires careful design of tables, fields and relationships, and errors made at the design stage can be difficult to correct later.

The software and the hardware needed to run a DBMS, and the staff trained to operate it, add to a firm's cost compared with simple manual records.

If the database is not properly backed up, a system failure can lead to loss of a large amount of accounting data at once, which would not happen if records were kept on paper in several separate books.

Going beyond the syllabus

Many small and medium firms today keep their accounting data in spreadsheet or dedicated accounting software rather than writing their own SQL queries by hand, but the same underlying database ideas of tables, fields, records and queries apply, since most such software stores data in a relational database behind its screens and menus.

Glossary

• Database — an organised collection of related data stored so that it can be entered, retrieved and updated efficiently.
• DBMS — software that creates, maintains and controls access to a database.
• Table — a collection of records about one subject, made up of fields and rows.
• Field — a column in a table describing one characteristic stored for every record.
• Record — one complete row of data in a table, holding values for every field.
• Primary key — a field whose value is unique for every record and identifies that record.
• Foreign key — a field in one table that matches the primary key of another table to create a relationship.
• Relationship — a link between two tables created through a common field.
• Form — an on-screen layout used to enter or view one record at a time.
• Report — a formatted output presenting selected data from one or more tables.
• Indexing — building an ordered reference on a field so matching records can be found quickly.
• SQL — Structured Query Language, used to create, retrieve, update and delete data in a DBMS.
• Query — a statement written in SQL asking the database to retrieve or change data.
• Data redundancy — the unnecessary repetition of the same data in more than one place.
• Data integrity — the accuracy and consistency of data stored across a database.

Common errors and misconceptions

• Misconception: SELECT changes the data in a table. Correct: SELECT only retrieves and displays data; UPDATE and DELETE are the commands that change stored data.
• Misconception: AND and OR can be used interchangeably in a WHERE clause. Correct: AND requires every condition to be true, while OR requires only one condition to be true, so they give different results.
• Misconception: A field and a record are the same thing. Correct: A field is a column describing one characteristic; a record is a complete row holding values for every field.
• Misconception: A primary key can repeat for more than one record. Correct: A primary key must be unique for every single record in a table.
• Misconception: A form and a report serve the same purpose. Correct: A form is used mainly to enter or view one record, while a report presents selected data from many records in an organised output.
• Misconception: Indexing changes the content of the data stored. Correct: Indexing only creates a faster way to locate matching records; it does not alter the values stored.
• Misconception: AVG and SUM give the same kind of answer. Correct: SUM adds up values to give a total, while AVG divides that total by the number of records to give a mean figure.

Exam-style questions with model answers

Q1. State what the abbreviation DBMS stands for and define it in one sentence. [1 mark]

1. DBMS stands for Database Management System, which is software that creates, maintains and controls access to an organised collection of related data.

Q2. State two features of a Database Management System. [2 marks]

1. A DBMS reduces data redundancy by storing data once and sharing it across every report that needs it.
2. A DBMS allows fast retrieval of specific information through queries, particularly where fields are indexed.

Q3. A firm's accounts clerk sometimes needs to view existing debtor balances, and sometimes needs to change a debtor's balance after receiving a payment. Distinguish between the SQL commands suited to each task, giving the name of the command in each case. [3 marks]

1. To view existing data, the clerk uses the SELECT command, which retrieves and displays specified fields from a table without changing them.
2. To change a debtor's balance, the clerk uses the UPDATE command, which alters the value of the Balance field in the relevant record.
3. SELECT is therefore used only to read data, while UPDATE is used to write new values into existing records, and the two should not be confused.

Q4. A firm's Debtors table has fields CustomerName and Balance. Explain any four basic SQL commands that could be used while working with this table. [4 marks]

1. SELECT is used to choose which fields, such as CustomerName and Balance, should be displayed from the table.
2. WHERE is used to limit the records shown to those meeting a condition, such as a balance above a given amount.
3. UPDATE is used to change the Balance field for a particular customer, for example after a payment is received.
4. DELETE is used to remove a customer's record entirely from the table, for example when an account is closed.

Q5. An Asset register table has a numeric field called Cost for every fixed asset. Explain the SQL functions AVG, COUNT, MAX, MIN and SUM, giving one business use of each in relation to this table. [5 marks]

1. SUM adds up the Cost field across all assets, giving the total cost of all fixed assets in the register.
2. AVG calculates the average Cost across all assets, giving the mean cost of an asset in the register.
3. COUNT counts how many assets satisfy a condition, for example how many assets cost more than a stated amount.
4. MAX finds the single highest value in the Cost field, showing the most expensive asset in the register.
5. MIN finds the single lowest value in the Cost field, showing the least expensive asset in the register.

Q6. Explain, with a suitable example in each case, how a DBMS can be applied to accounting information, to debtors and creditors records, to the Bank Reconciliation Statement, and to asset accounting. [6 marks]

1. In accounting information, transaction data such as sales and payments is stored in related tables, and queries produce ledgers and trial balances on demand, for example listing all payments made in a month.
2. In debtors records, a Debtors table storing each customer's balance allows a query with a WHERE condition to list only customers whose balance is overdue.
3. In creditors records, a Creditors table allows the SUM function to calculate the total amount currently owed to all suppliers in a single query.
4. In the Bank Reconciliation Statement, comparing a bank table with a cash book table through a query can identify cheques issued but not yet presented by the bank.
5. In asset accounting, an asset register table storing cost and depreciation for each asset allows a single UPDATE command to revise the accumulated depreciation of every asset at the year end.
6. Across all four uses, the common benefit is that data stored once can be queried in several different ways without being re-entered, which saves time and reduces error.

Q7. A Creditors table has fields SupplierName and Balance. Write the SQL query needed to list the names and balances of suppliers to whom the firm owes more than ₹1,00,000, and explain each part of the query. [3 marks]

1. The query is SELECT SupplierName, Balance FROM Creditors WHERE Balance > 100000.
2. SELECT SupplierName, Balance chooses which two fields will be shown in the result.
3. WHERE Balance > 100000 limits the output to only those suppliers whose balance exceeds ₹1,00,000, so smaller balances are excluded from the list.

Q8. State two limitations of maintaining accounting data through a DBMS. [2 marks]

1. Designing the tables, fields and relationships correctly requires careful planning, and mistakes made at this stage can be difficult to correct later.
2. If the database is not backed up properly, a system failure can cause the loss of a large amount of accounting data at once.

Key takeaways

• A DBMS is software that creates, maintains and controls access to an organised collection of related data.
• Data is organised into tables made up of fields (columns) and records (rows), linked through primary and foreign keys.
• Forms are used to enter or view single records, reports present selected data, and indexing speeds up retrieval.
• SELECT retrieves data, WHERE restricts it by condition, AND and OR combine conditions, and UPDATE and DELETE change or remove data.
• The functions SUM, AVG, COUNT, MAX and MIN calculate totals, averages, counts and extreme values across many records at once.
• Accounting information, debtors and creditors, the Bank Reconciliation Statement, and asset accounting can all be organised as linked database tables.
• A DBMS reduces data redundancy and supports data integrity, since data stored once is shared by every report that uses it.
• Setting up and maintaining a database requires careful design, suitable hardware and software, and reliable backup arrangements.

Test yourself

What is the difference between a field and a record in a database table?

A field is a column describing one characteristic, such as Balance, while a record is a complete row holding values for every field for one entry.

Which SQL command would you use to find all debtors with a balance above a given amount?

SELECT combined with a WHERE clause, for example SELECT CustomerName FROM Debtors WHERE Balance > 50000.

Which function would total the amount owed to all creditors?

The SUM function, applied to the Balance field of the Creditors table.

What does indexing a field in a database achieve?

It creates an ordered reference on that field so the DBMS can find matching records much faster than searching every row.

Name two business applications of DBMS mentioned for accounts.

Any two of accounting information, debtors and creditors records, the Bank Reconciliation Statement and asset accounting.

What is the difference between AND and OR in a WHERE clause?

AND selects a record only if every stated condition is true, while OR selects a record if at least one condition is true.

Give one limitation of using a DBMS for accounting data.

Any one, such as higher setup cost, the need for careful table design, or the risk of data loss without proper backup.