Class 11 Accountancy Notes Chapter 7 (Accounting System Using Database Management System) – Accountancy-I Book

Detailed Notes with MCQs of a crucial chapter for understanding modern accounting practices, Chapter 7: Accounting System Using Database Management System. This is important not just for your Class 11 curriculum but also forms a base for many competitive government exams where computer literacy and application in domains like accounting are tested.

Chapter 7: Accounting System Using Database Management System - Detailed Notes

1. Introduction

• Modern accounting heavily relies on computerized systems. At the heart of many sophisticated accounting software packages lies a Database Management System (DBMS).
• A DBMS provides a structured way to store, manage, retrieve, and manipulate large volumes of accounting data efficiently and securely.
• It overcomes limitations of manual systems (slow, error-prone, difficult analysis) and basic spreadsheet systems (data redundancy, inconsistency, limited security, difficulty in handling large data volumes).

2. What is a Database?

• A Database is an organized collection of logically related data, stored electronically, designed to be easily accessed, managed, and updated.
• In accounting, this data includes details about customers, suppliers, inventory items, employees, transactions (vouchers), ledger accounts, etc.

3. What is a Database Management System (DBMS)?

• A DBMS is a software system that enables users to create, define, maintain, and control access to the database.
• It acts as an interface between the users/application programs and the physical database.
• Key Functions of DBMS:
  • Data Definition: Defining the structure of the database (tables, fields, data types, constraints).
  • Data Manipulation: Inserting, updating, deleting, and retrieving data from the database.
  • Data Control: Managing security (user access), ensuring data integrity (accuracy and consistency), concurrency control (managing simultaneous access), and data recovery.

4. Key Concepts in DBMS

• Data Hierarchy: Data is organized in a hierarchy:

  • Bit: Smallest unit (0 or 1).
  • Byte: Group of bits (usually 8), represents a character (letter, number, symbol).
  • Field (or Attribute): A single piece of information about an entity (e.g., 'Customer Name', 'Invoice Date', 'Amount'). Corresponds to a column in a table.
  • Record (or Tuple): A collection of related fields representing a single entity instance (e.g., all details of one specific customer). Corresponds to a row in a table.
  • File (or Table/Relation): A collection of related records (e.g., a 'Customer Master' table containing records of all customers).
  • Database: A collection of interrelated files/tables.

• Entity: A real-world object or concept about which data is stored (e.g., Customer, Supplier, Voucher, Product, Account).

• Attribute: A property or characteristic of an entity (e.g., for the 'Customer' entity, attributes could be Customer ID, Name, Address, Phone Number).

5. Relational Database Management System (RDBMS)

• This is the most common type of DBMS used in accounting systems.
• Data is organized into Tables (also called Relations).
• Tables: Consist of Rows and Columns.
  • Rows (Tuples): Represent individual records or instances of an entity.
  • Columns (Attributes/Fields): Represent the properties or characteristics of the entity.
• Keys: Used to uniquely identify records and establish relationships between tables.
  • Primary Key: An attribute (or set of attributes) that uniquely identifies each row in a table (e.g., 'CustomerID' in the Customer table, 'VoucherNo' in the Voucher table). Cannot be NULL (empty).
  • Foreign Key: An attribute in one table that refers to the Primary Key of another table. It establishes a link or relationship between the two tables (e.g., 'CustomerID' in the Sales Invoice table would be a foreign key referencing the 'CustomerID' primary key in the Customer table). Helps maintain Referential Integrity.
  • Referential Integrity: Ensures that relationships between tables remain consistent. For example, you cannot create an invoice for a customer ID that does not exist in the Customer table.

6. Designing an Accounting Database

• Identify Entities: Determine the main objects about which data needs to be stored (e.g., Ledgers, Groups, Vouchers, Cost Centres, Inventory Items, Customers, Suppliers).

• Identify Attributes: Define the properties for each entity (e.g., For Ledger: Account Code, Account Name, Group, Opening Balance).

• Define Relationships: Establish links between entities using Primary and Foreign Keys (e.g., A Voucher record will contain Foreign Keys linking to specific Ledger Accounts).

• Normalization: A process of organizing data in a database to reduce redundancy and improve data integrity. Data is broken down into smaller, related tables. (Basic understanding is sufficient).

• Typical Accounting Database Structure:

  • Master Tables: Store relatively static information (e.g., Chart of Accounts/Ledger Master, Inventory Master, Customer Master, Supplier Master). Contain Primary Keys.
  • Transaction Tables: Store details of day-to-day activities (e.g., Voucher entries - Sales, Purchases, Receipts, Payments, Journal Vouchers). Contain Foreign Keys linking back to Master tables.

7. Structured Query Language (SQL)

• SQL is the standard language used to communicate with relational databases.
• It is used to perform tasks like:
  • Creating tables (CREATE TABLE)
  • Inserting data (INSERT INTO)
  • Querying/Retrieving data (SELECT)
  • Updating data (UPDATE)
  • Deleting data (DELETE)
• Accounting software uses SQL (often behind the scenes) to manage and retrieve financial data for processing and reporting.

8. Advantages of Using DBMS in Accounting

• Data Integration: Data from different parts of the business (sales, purchase, inventory, payroll) can be stored centrally and accessed together.
• Reduced Data Redundancy: Storing data in one place (e.g., customer address) avoids duplication across multiple files, saving space and reducing inconsistency. Normalization helps achieve this.
• Data Consistency: Reducing redundancy ensures that updates to data items (like a customer address) are reflected consistently across all relevant applications.
• Data Integrity: DBMS allows defining rules (constraints) to ensure data accuracy and validity (e.g., ensuring voucher debits equal credits, preventing invalid dates).
• Data Sharing: Authorized users can access and share the same data easily.
• Improved Data Security: DBMS provides mechanisms for controlling user access, preventing unauthorized modifications or viewing of sensitive financial data.
• Efficient Data Access & Reporting: DBMS allows for quick retrieval of specific data using query languages (like SQL), facilitating faster generation of financial reports (Trial Balance, P&L, Balance Sheet, etc.).
• Backup and Recovery: DBMS typically includes utilities for backing up data regularly and recovering it in case of system failure.
• Scalability: DBMS can handle large volumes of data and increasing numbers of users more effectively than simple file systems or spreadsheets.

9. Limitations/Disadvantages

• Cost: High initial cost for DBMS software, hardware, and potentially migration from older systems.
• Complexity: Designing and managing a database requires technical expertise.
• Need for Specialists: Often requires a Database Administrator (DBA) for maintenance and management.
• Performance Overhead: Can be slower for very simple tasks compared to flat files, due to the overhead of the DBMS software itself.
• Single Point of Failure: If the DBMS fails, all applications relying on it may become unavailable (mitigated by proper backup and redundancy).

Conclusion:

Using a DBMS is fundamental to modern accounting information systems. It provides a robust, efficient, and secure way to manage financial data, enabling accurate reporting, better decision-making, and compliance with regulations. Understanding these concepts is vital for anyone working with or auditing computerized accounting systems.

Multiple Choice Questions (MCQs)

1. Which of the following represents a collection of logically related data stored electronically?
   a) DBMS
   b) Field
   c) Record
   d) Database

2. A software system used to create, define, maintain, and control access to a database is called:
   a) Database
   b) SQL
   c) DBMS
   d) Operating System

3. In the data hierarchy, which component represents a single piece of information like 'Customer Name' or 'Invoice Amount'?
   a) Byte
   b) Field (Attribute)
   c) Record (Tuple)
   d) File (Table)

4. In a relational database (RDBMS), data is primarily organized into:
   a) Spreadsheets
   b) Text Files
   c) Tables
   d) Folders

5. An attribute (or set of attributes) that uniquely identifies each row in a table is called a:
   a) Foreign Key
   b) Candidate Key
   c) Primary Key
   d) Composite Key

6. Which key is used to establish and enforce a link between two tables in an RDBMS?
   a) Primary Key
   b) Foreign Key
   c) Super Key
   d) Unique Key

7. Which of the following is a major ADVANTAGE of using a DBMS for accounting over traditional file systems?
   a) Lower initial cost
   b) Reduced data redundancy and improved consistency
   c) Simpler to manage for non-technical users
   d) Requires less powerful hardware

8. In an accounting database, a 'Chart of Accounts' or 'Ledger Master' file would typically be considered a:
   a) Transaction Table
   b) Query Table
   c) Master Table
   d) Report File

9. The standard language used to interact with relational databases (for querying, updating, etc.) is:
   a) C++
   b) Java
   c) HTML
   d) SQL

10. Ensuring that a Sales Invoice record cannot be created for a Customer ID that does not exist in the Customer Master table is an example of:
    a) Data Redundancy
    b) Data Security
    c) Referential Integrity
    d) Data Normalization

Answer Key:

1. d) Database
2. c) DBMS
3. b) Field (Attribute)
4. c) Tables
5. c) Primary Key
6. b) Foreign Key
7. b) Reduced data redundancy and improved consistency
8. c) Master Table
9. d) SQL
10. c) Referential Integrity

Study these notes carefully. Focus on understanding the definitions, the structure (entities, attributes, tables, keys), and the advantages DBMS brings to accounting. Good luck with your preparation!