Skip to content
Log in · soon

Database management (DBMS) for IT Officer

What the subject covers, the key topics, how it has been asked, common mistakes and a two-week plan.

  • 11 min read
  • 7 topic groups
  • 57 terms in the glossary
  • 10 topics

IBPS publishes no official syllabus; this list is based on previous papers.

What the subject covers

A database management system (DBMS) stores data so that many users and programs can read and change it safely. For the IT Officer exam, DBMS means four connected ideas:

  • Design

    How you model the real world (entities, relationships) and turn that model into tables.

  • Querying

    How you ask questions of the data, in SQL and in its mathematical roots (relational algebra and calculus).

  • Correctness under change

    Transactions, the ACID properties, locking and concurrency.

  • Storage and scale

    Indexes, and the analytics side (data warehouses, OLAP) and NoSQL.

Quick revision

Tap a term to open its definition.

Key topics

ER model and keys

Attribute types, cardinality and participation, weak entities, and super, candidate, primary and foreign keys.

Entities, attributes, relationships. Know attribute types: simple vs composite, single- vs multi-valued, stored vs derived, and key attributes.

Derived attribute

Derived from other data instead of being stored.

For example, age derived from date of birth.

Cardinality and participation. 1:1, 1:N, M:N; total vs partial participation.

Weak entity

Weak entities depend on an owner entity and have only a partial key.

Super key

Any set of attributes that identifies a row uniquely.

Candidate key

A minimal super key.

Primary key

The candidate key you pick.

Foreign key

Refers to the primary key of another table.

Relational algebra and calculus

The algebra operators, and tuple relational calculus with its two quantifiers.

Selection (σ)

Picks rows.

Projection (π)

Picks columns.

Also know Cartesian product, joins (theta, equi, natural, outer), rename and division ("for all" queries).

Tuple relational calculus (TRC)

It describes what you want, not how.

TRC exampleTRC
{ t | t ∈ Employee ∧ t.salary > 50000 }

∃

"There exists at least one". A quantifier to know.

∀

"For all". A quantifier to know.

SQL

Command groups, clause order, joins, LIKE, subqueries and views, other objects and features (constraints, triggers, window functions) and the classic queries.
DDL, DML, DCL, TCL
GroupCommands
DDLCREATE, ALTER, DROP, TRUNCATE
DMLSELECT, INSERT, UPDATE, DELETE
DCLGRANT, REVOKE
TCLCOMMIT, ROLLBACK, SAVEPOINT

Some books put SELECT in a separate DQL.

Clause order and meaningSQL

The order is

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY
WHERE vs HAVING
ComparedWHEREHAVING
FiltersRows, before groupingGroups, after aggregation
Joins
JoinWhat it returns
INNERReturns matching rows only.
LEFT, RIGHT and FULL OUTERKeep unmatched rows, padded with NULL.
NATURAL JOINMatches on every column with the same name, which can surprise you.
Self joinJoins a table to itself.
Pattern matching with LIKE
WildcardMatches
%Any run of characters (including none)
_Exactly one

NOT LIKE excludes matches.

Subqueries. Know IN vs EXISTS, and correlated subqueries.

View

A stored query, not stored data.

Materialised view

Stores the result.

Constraints

PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY with ON DELETE / ON UPDATE actions (CASCADE, SET NULL, RESTRICT).

Trigger

Code that runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE.

Window function

Window functions compute across a group of rows but still return every row, unlike GROUP BY, which collapses them.

… OVER (PARTITION BY …)

Classic query: the n-th highest salarySQL
SELECT DISTINCT salary … ORDER BY salary DESC LIMIT 1 OFFSET n−1

DISTINCT, so ties don't give the wrong row. Or a subquery with DISTINCT / COUNT.

Classic query: duplicatesSQL
GROUP BY … HAVING COUNT(*) > 1
DELETE vs TRUNCATE vs DROP
ComparedDELETETRUNCATEDROP
What it removesChosen rowsAll rows, quicklyThe table itself
AlsoCan use WHERE and fires row triggersKeeps the tablenot stated

Whether TRUNCATE can be rolled back depends on the database product.

Normalisation

Functional dependencies and closure, 1NF to BCNF step by step, and good decompositions.

Functional dependency (FD)

X → Y means equal X values force equal Y values.

Attribute closure (X⁺)

Find candidate keys with it.

The normal forms, step by step
FormRule
1NFAtomic values, no repeating groups.
2NF1NF plus no partial dependency.
3NF2NF plus no transitive dependency of a non-prime attribute on a key. Equivalently, for every FD X → A, either X is a super key or A is a prime attribute.
BCNFFor every non-trivial FD X → Y, X is a super key. Stricter than 3NF.

Partial dependency

A non-prime attribute depends on part of a composite key. Only possible when some candidate key is composite.

Transitive dependency

A non-prime attribute depends on another non-prime attribute (transitively on the key).

Lossless decomposition

The join gives back exactly the original.

Decomposition should be lossless and ideally dependency-preserving.

Transactions, ACID and concurrency

ACID, transaction states, schedules and serialisability, anomalies, locks and 2PL, deadlocks and recovery.

Atomicity

All or nothing.

Consistency

The rules hold before and after.

Isolation

Concurrent transactions don't see each other's half-done work.

Durability

Committed data survives a crash.

Transaction states: active, partially committed, committed, failed, aborted.

Serial schedule

One transaction runs fully before the next starts.

Conflict serialisable

A schedule is conflict serialisable if swapping non-conflicting operations turns it into a serial one; the test is the precedence graph, which must have no cycle.

Anomalies: dirty read, non-repeatable read, phantom read, lost update. Know which isolation level (Read Uncommitted → Serializable) prevents which.

Shared vs exclusive lock
ComparedShared (S) lockExclusive (X) lock
OthersLets others read tooAllows no one else
Compatible withSNothing

S is compatible with S, and nothing is compatible with X.

Two-phase locking (2PL)

Has a growing phase, then a shrinking phase. It guarantees conflict serialisability but not freedom from deadlock.

Strict 2PL

Holds X locks until commit.

Deadlock handling in databases: wait-die and wound-wait (timestamp schemes), or detection and rollback.

Recovery: log-based (undo/redo), checkpoints, write-ahead logging.

Indexing and storage

Clustered and secondary indexes, dense and sparse, B-tree vs B+ tree, and hashing.

Primary/clustered vs secondary/non-clustered indexes. Also know dense vs sparse indexes.

Clustered index

Decides the physical order, so a table has at most one.

B-tree vs B+ tree
ComparedB-treeB+ tree
All records (or pointers to them) in the leavesNoYes
Leaves linkedNoYes, which makes range queries fast

That is why databases prefer B+ trees.

Data warehousing, OLAP and NoSQL

OLTP vs OLAP, warehouse schemas, OLAP operations, the NoSQL families, CAP and BASE.
OLTP vs OLAP
ComparedOLTPOLAP
HandlesMany short updatesLarge read-mostly analysis
ExampleCore bankingReports
Schemas: star vs snowflake
SchemaStructure
StarHas one fact table with denormalised dimension tables.
SnowflakeNormalises the dimensions.
Fact constellation (galaxy)Has several fact tables sharing dimensions.

Data mart

A warehouse subset for one department.

Roll-up

Summarises up a hierarchy.

Drill-down

Goes the other way (down a hierarchy).

Slice vs dice
ComparedSliceDice
What it doesFixes one dimension to a single valuePicks a sub-cube on several dimensions

Pivot

Rotates the view.

NoSQL families: key-value, document (for example MongoDB), column-family (for example Cassandra), graph.

CAP theorem

The idea of consistency, availability, partition tolerance.

Also BASE vs ACID.

How it is asked

Official

From the official IBPS documents

These are the facts about the format from the official IBPS documents:

  • Professional Knowledge in the Mains has 50 questions for 100 marks in 40 minutes, so each question is worth 2 marks.
  • A wrong answer costs one-fourth of the question's marks (−0.50).
  • That gives you about 48 seconds a question on average.
  • The one official IT sample question in IBPS's Mains handout is a single-line concept question with five options. The fifth option is "Other than those given as options".

Sources: IBPS official notification CRP SPL-XVI (1 Jul 2026); IBPS Information Handout, Online Main Exam CRP SPL-XVI, “notification §D; handout, Professional Knowledge sample question”

Not official

From memory-based papers (unofficial)

IBPS does not publish past papers. The patterns below are based on memory-based papers shared by candidates (unofficial) for two previous IT Officer Mains exams, held January 2023 and December 2024. They may be incomplete:

  • SQL you have to read or write. Several options show near-identical queries and you pick the correct one: LIKE / NOT LIKE patterns, a BEFORE INSERT trigger, n-th highest salary, GROUP BY, window functions with PARTITION BY, natural join, and what DROP and TRUNCATE do.
  • Theory in one line: which ACID property means all-or-nothing, what a shared lock allows, lock compatibility between several transactions, derived attributes, referential actions such as ON DELETE CASCADE, and what a foreign key requires on insert.
  • Short problems: given a relation and its FDs, name the highest normal form it satisfies; given two transactions run serially, give the final values.
  • Relational calculus notation (TRC syntax and the ∃ quantifier) appeared in the 2024 memory-based paper.
  • Data warehousing and NoSQL came up in both: OLAP slice, fact constellation schema, data mart, identifying a NoSQL database.

We don't give a number of DBMS questions: IBPS publishes none, and the memory-based papers aren't complete.

Common mistakes

Our suggestion

Two-week study plan

A suggestion. Adjust it to your own pace.

  1. Days 1–2

    ER model, keys, ER-to-table mapping

    Practice: Draw ER diagrams for a small bank (customer, account, loan); list the keys

  2. Days 3–4

    Relational algebra, TRC

    Practice: Write each query three ways: algebra, TRC, SQL

  3. Days 5–7

    SQL: clauses, joins, subqueries, constraints, triggers, views, window functions

    Practice: Run queries on a free database (SQLite or MySQL); predict the output before running

  4. Days 8–9

    FDs, closure, candidate keys, 1NF–BCNF, lossless decomposition

    Practice: 10 relations a day: find the keys and the highest normal form

  5. Days 10–11

    Transactions, ACID, schedules, serialisability, locks, isolation levels, recovery

    Practice: Draw precedence graphs; fill in lock-compatibility tables

  6. Day 12

    Indexing, B/B+ trees, hashing

    Practice: Insert keys into a B+ tree by hand

  7. Day 13

    Data warehousing, OLAP, NoSQL

    Practice: Flashcards of definitions

  8. Day 14

    Mixed revision

    Practice: Timed set of 25 questions in 20 minutes (the Mains pace), then review every wrong answer

Show the plan as a table
Two-week study plan
DaysFocusPractice
1–2ER model, keys, ER-to-table mappingDraw ER diagrams for a small bank (customer, account, loan); list the keys
3–4Relational algebra, TRCWrite each query three ways: algebra, TRC, SQL
5–7SQL: clauses, joins, subqueries, constraints, triggers, views, window functionsRun queries on a free database (SQLite or MySQL); predict the output before running
8–9FDs, closure, candidate keys, 1NF–BCNF, lossless decomposition10 relations a day: find the keys and the highest normal form
10–11Transactions, ACID, schedules, serialisability, locks, isolation levels, recoveryDraw precedence graphs; fill in lock-compatibility tables
12Indexing, B/B+ trees, hashingInsert keys into a B+ tree by hand
13Data warehousing, OLAP, NoSQLFlashcards of definitions
14Mixed revisionTimed set of 25 questions in 20 minutes (the Mains pace), then review every wrong answer

Glossary A–Z

57 terms

Symbols

  • 2PLSee Two-phase locking (2PL).
  • ∀"For all". A quantifier to know.
  • ∃"There exists at least one". A quantifier to know.

A

B

  • B+ treeSee B-tree vs B+ tree.
  • BCNFSee The normal forms, step by step.

C

  • Candidate keyA minimal super key.
  • CAP theoremThe idea of consistency, availability, partition tolerance.
  • Clustered indexDecides the physical order, so a table has at most one.
  • Conflict serialisableA schedule is conflict serialisable if swapping non-conflicting operations turns it into a serial one; the test is the precedence graph, which must have no cycle.
  • ConsistencyThe rules hold before and after.
  • ConstraintsPRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY with ON DELETE / ON UPDATE actions (CASCADE, SET NULL, RESTRICT).

D

  • Data martA warehouse subset for one department.
  • DCLSee DDL, DML, DCL, TCL.
  • DDLSee DDL, DML, DCL, TCL.
  • Derived attributeDerived from other data instead of being stored.
  • DiceSee Slice vs dice.
  • DMLSee DDL, DML, DCL, TCL.
  • Drill-downGoes the other way (down a hierarchy).
  • DurabilityCommitted data survives a crash.

E

F

I

  • IsolationConcurrent transactions don't see each other's half-done work.

L

M

N

O

  • OLAPSee OLTP vs OLAP.
  • OLTPSee OLTP vs OLAP.

P

R

S

T

  • TCLSee DDL, DML, DCL, TCL.
  • Transitive dependencyA non-prime attribute depends on another non-prime attribute (transitively on the key).
  • TRCSee Tuple relational calculus (TRC).
  • TriggerCode that runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE.
  • Tuple relational calculus (TRC)It describes what you want, not how.
  • Two-phase locking (2PL)Has a growing phase, then a shrinking phase. It guarantees conflict serialisability but not freedom from deadlock.

V

  • ViewA stored query, not stored data.

W

  • Weak entityWeak entities depend on an owner entity and have only a partial key.
  • Window functionWindow functions compute across a group of rows but still return every row, unlike GROUP BY, which collapses them.
Not official · based on previous papers

DBMS topics

10 topics. Practice questions: 0 so far, 2-mark questions coming soon.

DBMS FAQ

Is DBMS important for IBPS SO IT Officer?

Yes. It appears in the topic list compiled from previous papers, and both memory-based Mains papers we read (shared by candidates, unofficial) had DBMS and SQL questions. IBPS doesn't publish a syllabus or topic weightage, so no one can tell you the exact number of questions.

Do I need to write SQL in the exam?

The exam is multiple choice, so you don't type queries. In the memory-based papers, though, you often have to pick the one correct query from several similar ones, so practise reading SQL carefully.

Which normal forms should I know?

1NF, 2NF, 3NF and BCNF, with functional dependencies and attribute closure to find the keys. Know what makes a decomposition lossless and dependency-preserving.

Are NoSQL and data warehousing part of DBMS for this exam?

They are in the topic list, and OLAP and warehouse-schema questions appear in the memory-based papers. Learn the definitions: star, snowflake and fact constellation schemas, data marts, OLAP operations and the NoSQL families.

How many DBMS questions come in the Mains?

IBPS doesn't say. The Professional Knowledge test has 50 questions in total for 100 marks; how they split across subjects isn't published.

Other subject guides