Database management (DBMS) for IT Officer

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:

1. Design: how you model the real world (entities, relationships) and turn that model into tables.
2. Querying: how you ask questions of the data, in SQL and in its mathematical roots (relational algebra and calculus).
3. Correctness under change: transactions, the ACID properties, locking and concurrency.
4. Storage and scale: indexes, and the analytics side (data warehouses, OLAP) and NoSQL.

Banks run on databases (core banking, payments, reporting), so this subject is close to the job itself.

Key topics

ER model and keys

• Entities, attributes, relationships. Know attribute types: simple vs composite, single- vs multi-valued, stored vs derived (for example, age derived from date of birth), and key attributes.
• Cardinality and participation. 1:1, 1:N, M:N; total vs partial participation; weak entities, which depend on an owner entity and have only a partial key.
• Keys.
  • A super key is any set of attributes that identifies a row uniquely.
  • A candidate key is a minimal super key; the primary key is the candidate key you pick.
  • A foreign key refers to the primary key of another table.
  • Every candidate key is a super key, but not the other way round.

Relational algebra and calculus

• Algebra operators.
  • Selection σ picks rows; projection π picks columns.
  • Union, intersection and difference need union-compatible relations.
  • 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: { t | t ∈ Employee ∧ t.salary > 50000 }. Know the quantifiers: ∃ ("there exists at least one") and ∀ ("for all").

SQL

• DDL, DML, DCL, TCL.
  • DDL: CREATE, ALTER, DROP, TRUNCATE. DML: SELECT, INSERT, UPDATE, DELETE (some books put SELECT in a separate DQL).
  • DCL: GRANT, REVOKE. TCL: COMMIT, ROLLBACK, SAVEPOINT.
• Clause order and meaning. The order is SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation. That is the written order; SQL evaluates them as FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• Joins.
  • INNER returns matching rows only.
  • LEFT, RIGHT and FULL OUTER keep unmatched rows, padded with NULL.
  • NATURAL JOIN matches on every column with the same name, which can surprise you.
  • A self join joins a table to itself.
• Pattern matching with LIKE. % matches any run of characters (including none) and _ matches exactly one. NOT LIKE excludes matches.
• Subqueries and views.
  • Know IN vs EXISTS, and correlated subqueries.
  • A view is a stored query, not stored data; a materialised view stores the result.
• Other objects and features.
  • Constraints: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY with ON DELETE / ON UPDATE actions (CASCADE, SET NULL, RESTRICT).
  • Triggers: code that runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE.
  • Window functions: … OVER (PARTITION BY …). They compute across a group of rows but still return every row, unlike GROUP BY, which collapses them.
  • Classic queries: the n-th highest salary (SELECT DISTINCT salary … ORDER BY salary DESC LIMIT 1 OFFSET n−1, so ties don't give the wrong row, or a subquery with DISTINCT / COUNT), duplicates (GROUP BY … HAVING COUNT(*) > 1).
• DELETE vs TRUNCATE vs DROP.
  • DELETE removes chosen rows, can use WHERE and fires row triggers.
  • TRUNCATE removes all rows quickly but keeps the table.
  • DROP removes the table itself. Whether TRUNCATE can be rolled back depends on the database product.

Normalisation

• Functional dependency (FD). X → Y means equal X values force equal Y values. Find candidate keys with attribute closure (X⁺).
• The normal forms, step by step.
  • 1NF: atomic values, no repeating groups.
  • 2NF: 1NF plus no partial dependency, where a non-prime attribute depends on part of a composite key. Only possible when some candidate key is composite.
  • 3NF: 2NF 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.
  • BCNF: for every non-trivial FD X → Y, X is a super key. Stricter than 3NF.
• Decomposition should be lossless (the join gives back exactly the original) and ideally dependency-preserving. BCNF always allows a lossless decomposition but may lose dependencies; 3NF can always keep both.

Transactions, ACID and concurrency

• ACID.
  • 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.
• Schedules. In a serial schedule, one transaction runs fully before the next starts. 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.
• Locks.
  • A shared (S) lock lets others read too; an exclusive (X) lock allows no one else.
  • 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

• Primary/clustered vs secondary/non-clustered indexes. A clustered index decides the physical order, so a table has at most one. Also know dense vs sparse indexes.
• B-tree vs B+ tree. In a B+ tree all records (or pointers to them) are in the leaves, and the leaves are linked, which makes range queries fast. That is why databases prefer B+ trees.
• Hashing: good for equality search, poor for ranges.

Data warehousing, OLAP and NoSQL

• OLTP vs OLAP. OLTP handles many short updates (core banking); OLAP handles large read-mostly analysis (reports).
• Schemas.
  • A star schema has one fact table with denormalised dimension tables.
  • A snowflake schema normalises the dimensions.
  • A fact constellation (galaxy) has several fact tables sharing dimensions.
  • A data mart is a warehouse subset for one department.
• OLAP operations.
  • Roll-up summarises up a hierarchy; drill-down goes the other way.
  • Slice fixes one dimension to a single value; dice picks a sub-cube on several dimensions.
  • Pivot rotates the view.
• NoSQL families: key-value, document (for example MongoDB), column-family (for example Cassandra), graph. Also the CAP theorem idea (consistency, availability, partition tolerance) and BASE vs ACID.

How it is asked

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".

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

• Confusing 2NF and 3NF. 2NF is about part of a key; 3NF is about a non-prime attribute depending on another non-prime attribute (transitively on the key). A table whose candidate keys are all single attributes is automatically in 2NF.
• Assuming BCNF and 3NF are the same. They differ when a non-super-key determines a prime attribute.
• WHERE vs HAVING. You can't use an aggregate such as COUNT(*) > 1 in WHERE.
• NULL comparisons. col = NULL is never true; use IS NULL. COUNT(col) skips NULLs, COUNT(*) doesn't.
• NATURAL JOIN. It joins on all same-named columns, not just the one you meant.
• "2PL prevents deadlock." It doesn't; it guarantees serialisability.
• B-tree vs B+ tree: only the B+ tree keeps every record in linked leaves.
• Slice vs dice: one dimension fixed vs several ranges selected.
• Rushing close SQL options. Similar queries often differ in one keyword: AND vs OR, = vs LIKE, IN vs LIKE. Read every option to the end. The negative marking (−0.50) makes a pure guess between five options worth nothing on average (0.2 × 2 − 0.8 × 0.50 = 0). Once you can rule out even one option, a guess is worth something on average.

Two-week study plan

A suggestion. Adjust it to your own pace.