DBMS Basics — IBPS SO IT Officer Mains (Exam-Oriented

Notes)
Quick, crisp concepts + exam tips + frequently-tested SQL patterns.

1. DBMS & Data Models

A Database Management System (DBMS) provides an interface to store, retrieve, and manage data
efficiently while ensuring consistency, integrity, and security.

• Data models: Hierarchical, Network, Relational (most tested), Object-oriented.

• Schema vs. Instance: Schema = structure/blueprint; Instance = current data.
• Three-level architecture: External (views) / Conceptual (logical schema) / Internal (physical).
• Data Independence: Logical vs Physical. Logical independence is harder and more valuable.
• DBMS vs RDBMS: RDBMS uses relational model (tables, constraints). SQL is standard query
language.

ER Model
• Entity (e.g., Student), Attribute (RollNo), Relationship (enrolls).
• Keys: Super key ⊇ Candidate key(s) → Primary key. Alternate keys = remaining candidate keys.
• Participation: Total vs Partial. Cardinality: 1:1, 1:N, M:N.
• Weak entity: Depends on strong entity, uses partial key + owner’s key.
• Attributes: Simple/composite, single/multi-valued, derived, null.

ER → Relational Mapping (quick rules)

• Strong entity → table with attributes; choose a primary key.
• Weak entity → table with partial key + owner key as FK; composite PK.
• 1:1 → merge or use FK with unique constraint.
• 1:N → put FK on N-side.
• M:N → intersection (bridge) table with FKs to both entities.
• Multi-valued attribute → separate table with FK to owner.

2. Relational Algebra & Integrity Constraints

• Operators: σ (select), π (project), ∪ (union), − (set difference), × (Cartesian), ⋈ (join), ÷ (division).
• Joins: Inner (θ, equi, natural), Outer (left/right/full).
• Integrity: Domain, Entity (PK not null & unique), Referential (FK → PK).

3. Normalization & Functional Dependencies

• FD (X → Y): If two tuples agree on X, they must agree on Y.
• Armstrong’s axioms: Reflexivity, Augmentation, Transitivity (+ Union/Decomposition).
• Closure X⁺: Attributes functionally determined by X. Used to check keys.
• Normal forms: 1NF (atomic values) → 2NF (no partial dependency on PK) → 3NF (no transitive
dependency on key) → BCNF (every determinant is a candidate key).

• When to use BCNF vs 3NF: 3NF preserves dependencies; BCNF stronger but may need
decomposition.

• Anomalies: Insertion, Update, Deletion anomalies avoided by normalization.

Minimal Cover (Canonical Cover) — Steps

• 1) Ensure single attribute on RHS of each FD.
• 2) Minimize LHS by removing extraneous attributes using closure.
• 3) Remove redundant dependencies.

4. SQL Essentials (DDL/DML/DCL/TCL)

• DDL: CREATE, ALTER, DROP, TRUNCATE.
• DML: SELECT, INSERT, UPDATE, DELETE, MERGE.
• DCL: GRANT, REVOKE.
• TCL: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION.

Frequently Tested SELECT Patterns

Pattern SQL
Basic SELECT SELECT name, salary FROM Employee WHERE dept = 'IT';
Aliases & ORDER BY SELECT name AS emp, salary FROM Employee ORDER BY salary DESC;
Aggregate & GROUP BY SELECT dept, COUNT(*) AS cnt, AVG(salary) FROM Employee GROUP BY de
JOIN (Inner) SELECT e.name, d.dept_name FROM Employee e JOIN Department d ON e.
LEFT JOIN SELECT c.name, o.order_id FROM Customer c LEFT JOIN Orders o ON c.id =
Subquery (IN/EXISTS) SELECT name FROM Employee WHERE dept_id IN (SELECT id FROM Depar
Correlated Subquery SELECT e.* FROM Employee e WHERE salary > (SELECT AVG(salary) FROM
Window Function (basic) SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM
Set Ops SELECT id FROM A UNION SELECT id FROM B; -- (UNION ALL keeps duplica
View CREATE VIEW HighPaid AS SELECT name, salary FROM Employee WHERE s

Constraints & Keys

• PRIMARY KEY (unique, not null), UNIQUE, NOT NULL, CHECK, FOREIGN KEY.
• ON DELETE: CASCADE | SET NULL | SET DEFAULT | RESTRICT/NO ACTION.

NULL & 3-valued logic

• Comparisons with NULL are UNKNOWN; use IS NULL/IS NOT NULL, COALESCE/NVL.
• COUNT(col) ignores NULL; COUNT(*) counts rows.

5. Transactions, ACID, Concurrency Control

• Transaction: Logical unit of work. ACID: Atomicity, Consistency, Isolation, Durability.
• Isolation anomalies: Dirty read, Non-repeatable read, Phantom read.
• Isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
• Schedules: Conflict serializable (precedence graph acyclic), View serializable.
• Locking: Shared (S) / Exclusive (X) locks, 2PL (growing/shrinking), Strict 2PL, Deadlocks (wait-die,
wound-wait).

• Timestamp-ordering, Validation (optimistic concurrency control).

Recoverability & Logging

• Recoverable schedule: Commit order respects read-from dependencies.
• Cascadeless: No transaction reads uncommitted data.
• ARIES (physiological logging): Write-Ahead Logging (WAL), checkpoints, REDO/UNDO, LSNs, dirty
page table.

6. File Organization & Indexing

• Heap file (unordered), Sorted file (good for range scans).
• Index types: Primary (clustered) vs Secondary (non-clustered). Dense vs Sparse.
• B+ Tree: All records at leaves; internal nodes store keys/pointers; balanced; supports range
queries.

• Hashing: Static vs Extendible/Linear hashing; great for equality, not ranges.

• Cost hints: Clustered index helps ORDER BY/GROUP BY and range scans; Hash index for equality
lookups.

7. Query Processing & Optimization (Basics)

• Phases: Parsing → Logical plan (algebra) → Optimization → Physical plan.
• Heuristics: Push σ (selection) and π (projection) early; replace cross × with joins; use indexes.
• Join algorithms: Nested-loop, Sort-merge, Hash join.

8. Distributed DBMS & NoSQL (Basics)

• Distributed DBMS: Data fragmentation (horizontal/vertical), replication; goals: transparency,
reliability, scalability.

• CAP theorem (NoSQL): Consistency, Availability, Partition tolerance — choose any two under
partition.

• NoSQL models: Key-value, Document, Column-family, Graph.

9. Security & Authorization (Basics)

• Authentication vs Authorization vs Auditing.
• DAC (Discretionary) & RBAC (Role-based) controls.
• SQL injection basics: use parameterized queries/prepared statements.
• Backup/Recovery, encryption at rest/in transit.

Exam Tips & Tricks (IBPS SO IT Mains)

• Expect 3–6 questions on normalization/FDs/keys; practice key/closure quickly.
• Watch for NULL behavior, set operators, and join condition placement.
• For schedules: Build a precedence graph fast to test conflict-serializability.
• For indexes: know B+Tree vs Hash; clustered vs non-clustered.

High-yield formulas & checks

• Key test: Compute closure X⁺; if it covers all attributes → X is a (candidate) key.
• BCNF test: For every non-trivial FD X→Y, X must be a superkey.
• 3NF test: For X→A, X is a superkey OR A is prime (part of some candidate key).
• Serializability: Precedence graph must be acyclic.
• Index selectivity: High distinctness → better for B+ tree seeks.

Mini Practice (5 quick checks)

1 Q1. In a 1:N relationship, where is the foreign key placed? (Ans: On the N-side table).
2 Q2. Which normal form removes transitive dependency on keys? (Ans: 3NF).
3 Q3. What anomaly does strict 2PL prevent? (Ans: Dirty reads/cascading aborts).
4 Q4. Which index fits range queries better: B+ Tree or Hash? (Ans: B+ Tree).
5 Q5. Under CAP, which two are possible during network partition for an AP system? (Ans: Availability
& Partition tolerance).

— End of Notes —