Database Management System (DBMS): ISC Class 12 Accounts Notes and SQL

What Database Management System (DBMS) is about

Weightage: Database Management System (DBMS) carries 10 of the 80 theory marks in Section C (Computerised Accounting). Candidates answer either Section B or Section C (20 marks each), after the compulsory 60-mark Section A.Source: CISCE ISC Accountancy (Accounts) syllabus, examination year 2027, distribution of marks

Key concepts

Data, information and the data hierarchy

Data are raw facts and figures (an employee's name, days worked, basic pay). Information is data processed into a meaningful form for a decision (the bonus payable to that employee, or a ledger balance).

Data hierarchy (smallest to largest):

1. Data item / character: a single value.
2. Field (attribute, column): one characteristic of an entity, such as Balance.
3. Record (tuple, row): all fields about one entity, such as one debtor.
4. File / table: a collection of related records, such as the DEBTORS table.
5. Database: a collection of related tables.

Arranged from the top for creating a DBMS: File → Record → Field → Data.

An entity is an object about which data is stored (a customer); its attributes are its characteristics (name, city, balance).

Worked example

Suppose a firm stores each supplier's code, name and amount due. Identify a field, a record and a file in this case. [2 marks]

Try it first, then show the solutionSolution

1. Field: "Amount due" (one characteristic).
2. Record: all the details of one supplier.
3. File/table: the collection of all supplier records (CREDITORS).

Answer: Field: amount due; record: one supplier's row; file: the CREDITORS table.

Types and features of DBMS

Types by data model: hierarchical (tree, parent-child), network (records linked in many-to-many fashion), relational (data in tables linked by common keys: the most common), object-oriented.

Types by location: desktop database (single user, one computer, for example MS Access) and server database (multi-user, on a server, for example SQL Server, Oracle, MySQL). Before choosing, consider: what data is to be stored, who will capture or modify it, how often it changes, how many users and what tasks they perform, whether access is over LAN or the Internet, and the hardware and operating system available.

Features (advantages): reduced data redundancy; data consistency and integrity; data sharing among many users; security through access control; backup and recovery; independence of data from programs; quick retrieval through queries.

A data model is a collection of conceptual tools to describe data, data relationships, data semantics and constraints. The storage manager has components: authorisation and integrity manager, transaction manager, file manager and buffer manager.

Worked example

Suppose a chain of 40 stores wants all branches to update stock in one database at the same time. Should it choose a desktop or a server database? Give two reasons. [2 marks]

Try it first, then show the solutionSolution

1. Many users at many locations need simultaneous access: a server database supports multi-user, networked access.
2. A server database offers stronger security, central backup and better handling of large volumes.

Answer: A server database, for multi-user network access and central security and backup.

SQL: languages and basic commands

SQL (Structured Query Language) is the standard language for relational databases. Its parts:

• DDL (Data Definition Language): defines structure: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE.
• DML (Data Manipulation Language): works with the data: SELECT, INSERT INTO, UPDATE, DELETE. DML can be embedded in a host programme.
• DCL (Data Control Language): GRANT, REVOKE (access rights).

Syllabus commands:

• SELECT retrieves columns: SELECT Name, Balance FROM DEBTORS;
• WHERE filters rows: … WHERE City = 'Delhi';
• AND / OR combine conditions: WHERE City = 'Delhi' AND Balance > 30000;
• UPDATE changes existing rows: UPDATE DEBTORS SET Balance = 40000 WHERE Code = 'D01';
• DELETE removes rows: DELETE FROM DEBTORS WHERE Balance = 0;

Always end a statement with a semicolon and put text values in single quotes.

Worked example

Suppose a DEBTORS table has columns Code, Name, City and Balance. Write SQL statements to (i) display names of Delhi debtors with balance over ₹30,000; (ii) reduce D03's balance to ₹20,000. [2 marks]

Try it first, then show the solutionSolution

1. (i) SELECT Name FROM DEBTORS WHERE City = 'Delhi' AND Balance > 30000;
2. (ii) UPDATE DEBTORS SET Balance = 20000 WHERE Code = 'D03';

Answer: As above.

SQL aggregate functions

Aggregate functions work on a column and return one value:

• SUM(column): total.
• AVG(column): average.
• COUNT(column) or COUNT(*): number of values or rows.
• MAX(column) / MIN(column): highest / lowest value.

They can be combined with WHERE: SELECT SUM(Balance) FROM DEBTORS WHERE City = 'Delhi';

In accounting these give totals of debtors and creditors, the largest outstanding balance, the number of overdue accounts and average invoice value.

Worked example

Using the DEBTORS table below, give the output of SELECT SUM(Balance), AVG(Balance), MAX(Balance) FROM DEBTORS; [2 marks]

Table DEBTORS

Code	Name	City	Balance (₹)	LastPaid
D01	Arora Traders	Delhi	45,000	2026-01-10
D02	Basu & Co	Kolkata	82,000	2025-12-02
D03	Chopra Stores	Delhi	27,000	2026-02-18
D04	Desai Agencies	Mumbai	61,000	2025-11-25
D05	Eapen Mart	Kochi	15,000	2026-03-05

Try it first, then show the solutionSolution

1. SUM = ₹2,30,000; AVG = 2,30,000 ÷ 5 = ₹46,000; MAX = ₹82,000.

Answer: 2,30,000; 46,000; 82,000.

DELETE, TRUNCATE and DROP

DELETE	TRUNCATE
Removes only the rows specified by WHERE (all rows if no WHERE)	Removes all rows from the table
Can be rolled back	Cannot be rolled back
Keeps a log of each deleted row, so it is slower	Does not log individual rows, so it is faster
Uses row locks	Uses a table lock
A DML command	A DDL command

DROP TABLE removes the table itself (structure and data). TRUNCATE empties it but keeps the structure.

Worked example

Suppose at the start of a new year a firm wants to clear all records from a TEMP_ENTRIES table quickly but keep the table for re-use. Which command should it use and why? [1 mark]

Try it first, then show the solutionSolution

1. All rows go and the structure stays; speed matters.

Answer: TRUNCATE TABLE TEMP_ENTRIES; because it removes all rows quickly while keeping the table structure.

DBMS in business: design, tables, relationships, forms, reports, indexing

• Database design: describing the structure of the parts of the database: which tables, fields and data types, keys and relationships.
• Table: rows (records) and columns (fields). A primary key uniquely identifies each record (Code); a foreign key links to another table.
• Relationships: one-to-one, one-to-many (one customer, many invoices), many-to-many (through a linking table).
• Forms: screens for entering and editing data with validation.
• Reports: formatted outputs for printing or sharing (debtors' ageing report, asset register).
• Indexing: an index on a field speeds up searching and sorting, like a book's index.

Business applications named in the syllabus:

• Accounting information: vouchers, ledgers, trial balance generated from transaction tables.
• Debtors and creditors: party master tables linked to invoices and payments; ageing reports.
• Bank reconciliation statement: matching the cash book table with the bank statement table to list unmatched items.
• Asset accounting: asset register with cost, date, depreciation method; depreciation schedules.

Worked example

Suppose a firm wants to prepare its bank reconciliation statement using a DBMS. Explain how. [2 marks]

Try it first, then show the solutionSolution

1. Keep two tables: CASH_BOOK (bank column entries) and BANK_STATEMENT, each with date, cheque number and amount.
2. A query matching on cheque number and amount lists items in one table but not the other (cheques issued but not presented, deposits not credited); a report presents these as the reconciliation.

Answer: By matching two tables with a query and reporting the unmatched items.

Database Management System (DBMS) in real life

Each concept, met in an everyday situation. Read the situation, answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Topics in this chapter

• DBMS: Concepts, Features, Types and Business Applications
• SQL: SELECT, WHERE, AND and OR
• SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM

Database Management System (DBMS): key facts

Retrieve

SELECT column1, column2 FROM table WHERE condition;

Combine conditions

WHERE condition1 AND condition2 / WHERE condition1 OR condition2

Change data

UPDATE table SET column = value WHERE condition;

Remove rows

DELETE FROM table WHERE condition;

Aggregates

SELECT SUM(col), AVG(col), COUNT(*), MAX(col), MIN(col) FROM table;

How the board asks Database Management System (DBMS)

The question patterns to expect, and the method that works for each.

Tricks to remember Database Management System (DBMS)

Short memory aids. Say each one aloud once, then use it on a question the same day.

• Hierarchy: "Data Fills Rows in Files"

  Data → Field → Record → File → Database (small to large).

  Use it when: Hierarchy questions.

• DDL builds, DML fills

  DDL (CREATE, ALTER, DROP, TRUNCATE) builds the structure; DML (SELECT, INSERT, UPDATE, DELETE) fills and reads it.

  Use it when: Classifying commands.

• Five aggregates: "SAC-MM"

  SUM, AVG, COUNT, MAX, MIN.

  Use it when: Function questions.

Database Management System (DBMS): board-level solved questions

Ordered from board level to HOTS. Cover the solution, write your own, then compare step by step.

Case-study practice (Section A style)

Read the situation, then answer every part. In the board paper this is a 6-mark question split into 2 + 2 + 2 marks.

Where the marks are

How a typical answer is split into step marks. Show every step: a right final answer alone rarely earns full marks.

Where students lose marks in Database Management System (DBMS)

The slips examiners see most often, and what to write instead.

• ✗Mistake: Not attempting DBMS questions

  CISCE examiners' comments, 2024 and 2025, on Questions 16 and 17: "Very few candidates attempted this question."

  ✓Do this: Learn the short definitions and SQL statements; they are quick marks in Section C.

  Usually costs: Up to 9 marks across Questions 16 and 17.

• ✗Mistake: Hierarchy written in the wrong order

  The 2024 answer key arranges the hierarchy as FILE, RECORD, FIELD, DATA; mixing up field and record loses the mark.

  ✓Do this: Record = one row (one entity); field = one column (one attribute).

  Usually costs: 1-2 marks.

• ✗Mistake: SQL without WHERE or with wrong syntax

  UPDATE or DELETE without WHERE changes every row; text values need quotes and statements end with a semicolon.

  ✓Do this: Write the full statement: command, table, SET/columns, WHERE condition, semicolon.

  Usually costs: ½-1 mark per statement.

• ✗Mistake: Confusing DELETE with TRUNCATE or DROP

  The 2025 answer key expects differences such as WHERE-based deletion, rollback, logging and DML versus DDL.

  ✓Do this: DELETE: chosen rows, DML; TRUNCATE: all rows, DDL, structure kept; DROP: table removed.

  Usually costs: 1-2 marks.

Practice questions on Database Management System (DBMS)

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · Very short answerBoard level

   Arrange in hierarchy (largest first) to create a DBMS: FIELD, DATA, FILE, RECORD.

   Hint

   Top-down.

   Answer

   FILE, RECORD, FIELD, DATA.

2. Q2 · 1 mark · Very short answerBoard level

   What is the use of the UPDATE command in SQL?

   Hint

   Existing rows.

   Answer

   It modifies existing rows of a table, e.g. UPDATE DEBTORS SET Balance = 0 WHERE Code = 'D05';

3. Q3 · 1 mark · Very short answerBoard level

   What is a data model?

   Hint

   Conceptual tools.

   Answer

   A set of concepts used to describe a database: the data, how the data items relate to one another, what they mean, and the rules (constraints) they must obey; it shows how entities and their attributes are related.

4. Q4 · 2 marks · Short answerHarder board level

   List the components of the storage manager.

   Hint

   Four managers.

   Answer

   Authorisation and integrity manager, transaction manager, file manager, buffer manager.

5. Q5 · 2 marks · Short answerHarder board level

   Write SQL to show the highest and lowest balances in the CREDITORS table.

   Hint

   MAX and MIN.

   Answer

   SELECT MAX(Balance), MIN(Balance) FROM CREDITORS;

6. Q6 · 1 mark · Very short answerHarder board level

   Give the meaning of database design.

   Hint

   Structure.

   Answer

   The description of the structure of the different parts of the overall database: tables, fields, keys and relationships.

7. Q7 · 2 marks · Short answerHOTS / competency

   Explain one-to-many relationship with an accounting example.

   Hint

   Customer and invoices.

   Answer

   One record in a table relates to many in another: one customer (CUSTOMERS) can have many invoices (INVOICES), linked by CustomerCode.

8. Q8 · 2 marks · Short answerHOTS / competency

   A clerk ran DELETE FROM DEBTORS; intending to remove only zero-balance debtors. What went wrong and what should he have written?

   Hint

   Missing clause.

   Answer

   Without WHERE, every row was deleted. Correct: DELETE FROM DEBTORS WHERE Balance = 0;

9. Q9 · 1 mark · Very short answerBoard level

   Name two SQL basic commands and two aggregate functions in the ISC syllabus.

   Hint

   Any two each.

   Answer

   Commands: SELECT, UPDATE (also WHERE, AND, OR, DELETE). Functions: SUM, AVG (also COUNT, MAX, MIN).

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.What is a record?Show answer

   All fields about one entity: a row of a table.

2. 2.Which SQL clause filters rows?Show answer

   WHERE.

3. 3.Is TRUNCATE DDL or DML?Show answer

   DDL.

4. 4.Name the five aggregate functions in the syllabus.Show answer

   AVG, COUNT, MAX, MIN, SUM.

5. 5.What uniquely identifies a record?Show answer

   The primary key.

Quick revision checklist

The night before the exam: can you do each of these without looking?

• ☐Hierarchy: File → Record → Field → Data.
• ☐DDL: CREATE, ALTER, DROP, TRUNCATE; DML: SELECT, INSERT, UPDATE, DELETE.
• ☐SELECT … FROM … WHERE … AND/OR …;
• ☐SUM, AVG, COUNT, MAX, MIN.
• ☐Primary key unique; foreign key links tables.
• ☐Business uses: accounting information, debtors and creditors, BRS, asset accounting.

Database Management System (DBMS): frequently asked questions