Complete Syllabus
8 Modules. 50+ Topics. Theory + Hands-On SQL + Interview Prep.
Click any module to expand.
Click any module to expand.
What databases are, why they exist, and how they're structured internally
Problems with file-based storage: data redundancy, inconsistency, no concurrent access, no security. How DBMS solves these problems. Data vs information. Database vs DBMS vs database system. Real-world database applications students use daily.
External (view) level, conceptual (logical) level, internal (physical) level. Data independence: logical and physical. Why three levels matter — changing storage doesn't break applications. Client-server vs three-tier architecture.
Relational model (tables — the dominant model). Hierarchical (tree — legacy). Network (graph — legacy). Object-oriented. Document (MongoDB). Key-value (Redis). Column-family (Cassandra). Graph (Neo4j). When to use which model — the modern landscape.
Super key, candidate key, primary key, alternate key, foreign key, composite key. How keys enforce uniqueness and relationships. Surrogate keys vs natural keys — the design decision every database faces. Auto-increment IDs vs UUIDs.
Domain constraints, entity integrity (primary key can't be NULL), referential integrity (foreign key must reference existing row). NOT NULL, UNIQUE, CHECK, DEFAULT constraints. What happens when constraints are violated — CASCADE, SET NULL, RESTRICT.
Database Administrator (DBA): schema definition, access control, backup/recovery, performance tuning. Application developers: query writing, schema design. End users: data consumption. How roles map to real jobs at companies.
Designing databases before writing SQL — the conceptual and logical design process
Entities (strong, weak), attributes (simple, composite, multivalued, derived), keys (primary, partial). ER diagram notation. Designing an ER diagram from a problem statement — the skill tested in design questions.
Relationship types: one-to-one, one-to-many, many-to-many. Cardinality ratios: 1:1, 1:N, M:N. Participation constraints: total vs partial. Recursive relationships. Ternary relationships and when they're needed.
IS-A relationships: specialisation (top-down) and generalisation (bottom-up). Disjoint vs overlapping. Total vs partial specialisation. Aggregation. Mapping EER to relational schema. How inheritance works in database design.
Step-by-step algorithm: strong entities → weak entities → 1:1 relationships → 1:N relationships → M:N relationships → multivalued attributes → specialisation. The mechanical process for converting any ER diagram to tables. Interview favourite: "Given this ER, write the schema."
Selection (σ), projection (π), union (∪), intersection (∩), set difference (−), Cartesian product (×), joins (natural, theta, equi, outer). Division operation. Why relational algebra matters: it's the theoretical foundation of SQL. Query equivalence and optimisation.
The most tested DBMS skill — writing SQL queries that produce correct results
Creating tables with data types (INT, VARCHAR, DATE, DECIMAL, BOOLEAN). Adding/dropping columns with ALTER. DROP vs TRUNCATE vs DELETE — the classic interview question. AUTO_INCREMENT / SERIAL for primary keys. CONSTRAINTS in CREATE TABLE.
INSERT INTO ... VALUES, INSERT INTO ... SELECT. UPDATE with WHERE (forgetting WHERE updates ALL rows — the most dangerous SQL mistake). DELETE with WHERE. Multi-row INSERT. Conditional UPDATE with CASE.
SELECT, WHERE, ORDER BY, LIMIT/OFFSET. Comparison operators, BETWEEN, IN, LIKE (%, _), IS NULL. Column aliasing (AS). DISTINCT for unique values. Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
INNER JOIN (matching rows from both tables). LEFT JOIN (all from left + matching from right). RIGHT JOIN. FULL OUTER JOIN. CROSS JOIN (Cartesian product). SELF JOIN (employee-manager hierarchy). The Venn diagram mental model. Multi-table JOINs. JOIN is THE most tested SQL topic.
COUNT, SUM, AVG, MIN, MAX. GROUP BY for grouping rows. HAVING for filtering groups (WHERE filters rows, HAVING filters groups). COUNT(*) vs COUNT(column) — difference with NULLs. GROUP BY with multiple columns.
Subquery in WHERE (scalar subquery). IN with subquery. EXISTS vs IN — performance difference. Correlated subquery: inner query depends on outer query (re-executes per row). Subquery in FROM (derived table). Subquery in SELECT. "Find the second highest salary" — the classic correlated subquery question.
UNION (removes duplicates) vs UNION ALL (keeps duplicates). INTERSECT for common rows. EXCEPT/MINUS for difference. Rules: same number of columns, compatible data types. When to use SET operations vs JOINs.
Window functions, CTEs, views, stored procedures — the SQL that product companies test
ROW_NUMBER(), RANK(), DENSE_RANK() — the three ranking functions and their differences. LEAD(), LAG() for accessing adjacent rows. SUM/AVG/COUNT as window functions. OVER(PARTITION BY ... ORDER BY ...). ROWS BETWEEN for running totals. The most powerful SQL feature for analytics — and increasingly tested in interviews.
WITH clause for readable, reusable query blocks. CTEs vs subqueries — readability and performance. Recursive CTEs: generating sequences, hierarchical data traversal (employee org chart), tree/graph traversal in SQL. When CTEs make a query 10x more readable.
CREATE VIEW — virtual tables that simplify complex queries. Security benefit: expose only specific columns. Updatable views vs read-only views. Materialised views: pre-computed results for performance. REFRESH strategies. When to use views vs CTEs vs subqueries.
Stored procedures: reusable SQL blocks with parameters (IN, OUT, INOUT). Functions: return a value, usable in SELECT. Triggers: auto-execute on INSERT/UPDATE/DELETE events (BEFORE, AFTER). Advantages: reduced network traffic, business logic in DB layer. When stored procedures are appropriate vs when they're not.
GRANT, REVOKE — controlling who can access what. Privilege types: SELECT, INSERT, UPDATE, DELETE, ALL. COMMIT, ROLLBACK, SAVEPOINT — transaction control. Why explicit COMMIT/ROLLBACK matters in production systems.
EXPLAIN / EXPLAIN ANALYZE — reading query execution plans. Sequential scan vs index scan. Why queries are slow: missing indexes, full table scans, unnecessary JOINs, SELECT * instead of specific columns. The 5 most common SQL performance mistakes and how to fix them.
Designing databases that don't have redundancy, anomalies, or data integrity problems
What X → Y means. Trivial vs non-trivial FDs. Finding all FDs from a given set. Closure of attributes (X⁺). Closure of FD set. Candidate key identification using closure. Armstrong's axioms: reflexivity, augmentation, transitivity.
Insertion anomaly, update anomaly, deletion anomaly — concrete examples showing data corruption from poor design. Redundancy = wasted space + inconsistency risk. Normalisation eliminates anomalies by decomposing tables.
1NF: atomic values, no repeating groups. 2NF: no partial dependencies (on part of composite key). 3NF: no transitive dependencies. BCNF: every determinant is a candidate key. Step-by-step normalisation with worked examples. The most asked DBMS theory topic after SQL.
4NF: no multi-valued dependencies. 5NF: no join dependencies. Why 4NF and 5NF are rarely tested but worth knowing. Denormalisation: deliberately adding redundancy for performance (read-heavy systems). When to normalise vs when to denormalise — the practical design trade-off.
Lossless join: decomposed tables can be rejoined without losing data. How to verify lossless decomposition. Dependency preservation: all FDs are checkable without joins. Why both properties matter for correct normalisation. Trade-offs when you can't have both.
How databases handle multiple users and survive crashes without losing data
Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). The bank transfer example that explains ACID perfectly. Why each property matters — and what happens when one is violated.
Active, partially committed, committed, failed, aborted states. Serial vs non-serial schedules. Serialisability: conflict serialisability (precedence graph) and view serialisability. Why serial schedules are correct but slow — and how serialisability gives us the best of both.
Shared locks (read) vs exclusive locks (write). Two-Phase Locking (2PL): growing phase and shrinking phase. Strict 2PL. Deadlock: detection (wait-for graph) and prevention (wait-die, wound-wait). Starvation. Lock granularity: row-level vs table-level.
Timestamp ordering protocol. Thomas' write rule. Multi-Version Concurrency Control (MVCC) — how PostgreSQL and MySQL InnoDB actually handle concurrency. Read snapshots without blocking writes. Why MVCC is dominant in modern databases.
Read Uncommitted, Read Committed, Repeatable Read, Serializable. Problems each level prevents/allows: dirty reads, non-repeatable reads, phantom reads. Default isolation levels in PostgreSQL (Read Committed) vs MySQL (Repeatable Read). Choosing the right level for your application.
WAL (Write-Ahead Logging): log before write. Undo/redo recovery. Checkpoints: reducing recovery time. ARIES recovery algorithm (conceptual). How databases survive power failures, crashes, and disk corruption. Why durability works.
How databases store data physically and find it fast — the internals that interviewers love
Heap files (unordered), sequential files (sorted), hash files. Records, pages, blocks — how data lives on disk. Fixed-length vs variable-length records. Why disk I/O is the bottleneck — and why everything in DBMS internals is about reducing disk reads.
Primary index (dense/sparse), secondary index, clustered vs non-clustered index. Index on primary key vs on non-key. Multi-level indexing. Why indexes speed up reads but slow down writes. "When should you NOT create an index?" — a common interview question.
B-tree: balanced, self-adjusting, all nodes store data. B+ tree: only leaf nodes store data, leaf nodes form a linked list. Why B+ trees are preferred for databases (sequential access, range queries). Order, height calculation, search/insert/delete operations. How MySQL InnoDB and PostgreSQL use B+ trees.
Static hashing: hash function maps key to bucket. Overflow handling: chaining, open addressing. Dynamic hashing: extendible hashing, linear hashing. When hash indexes beat B+ trees (exact-match lookups) and when they don't (range queries). Hash indexes in PostgreSQL.
Composite index on (col1, col2, col3): leftmost prefix rule — index works for queries on (col1), (col1, col2), (col1, col2, col3) but NOT (col2, col3). Covering index: all queried columns are in the index — no table lookup needed. Index design for query patterns.
Query processing stages: parsing → translation → optimisation → evaluation. Cost-based optimisation: estimating I/O cost. Join algorithms: nested loop, sort-merge, hash join. EXPLAIN plan reading: what sequential scan, index scan, bitmap scan mean. Why index selection is the #1 performance decision.
Beyond relational — document stores, key-value, CAP theorem, and database security
Document stores (MongoDB): JSON-like documents, schema flexibility. Key-value stores (Redis): fastest lookups, caching. Column-family (Cassandra): wide columns, write-optimised. Graph databases (Neo4j): relationships as first-class citizens. When to use each type — the decision framework.
CAP: Consistency, Availability, Partition tolerance — pick two. CP systems (consistent under partition — MongoDB). AP systems (available under partition — Cassandra). ACID (relational) vs BASE (Basically Available, Soft state, Eventually consistent — NoSQL). The fundamental trade-off in distributed databases.
Horizontal partitioning (sharding): splitting data across servers by key range or hash. Vertical partitioning: splitting columns. Replication: master-slave, master-master. Read replicas for scaling reads. Consistency challenges with replication. How Instagram, Twitter, and Uber scale their databases.
PostgreSQL: MVCC, advanced data types (JSON, arrays, hstore), full-text search, window functions, CTEs. MySQL/InnoDB: most popular web database, MVCC, replication. Key differences between PostgreSQL and MySQL. Which companies use which — and why it matters for your resume.
SQL injection: how it works ('; DROP TABLE users;--), parameterised queries as prevention. Access control: GRANT/REVOKE, role-based access. Encryption: at-rest (TDE) and in-transit (TLS). Data masking for sensitive information. Audit logging. Why every developer must understand SQL injection — it's the #1 web vulnerability.
Object-Relational Mapping: what Hibernate (Java), SQLAlchemy (Python), Sequelize (Node.js) do. N+1 query problem and how to avoid it. Connection pooling. Database migrations. How databases fit into web application architecture (MVC pattern). When to use raw SQL vs ORM.
From basic SELECT to complex window functions — graded by difficulty, auto-evaluated on a live database.
Given a real-world scenario (e-commerce, hospital, library), design the complete ER diagram and convert to relational schema.
Start with a messy table full of redundancy. Identify FDs, find candidate keys, decompose to 3NF/BCNF step by step.
Given slow queries, use EXPLAIN to diagnose, add indexes, rewrite queries, and measure the improvement.
Build a small application database: schema design, table creation, sample data, complex queries, views, and stored procedures.
Demonstrate SQL injection on a vulnerable form, then fix it with parameterised queries. Understand the attack to prevent it.
Trainers explain concepts with real database examples — not slides with bullet points. Every theory session includes a live SQL demo on a real database.
Students write SQL queries on our platform against live databases. Auto-evaluated: your query either returns the correct result or it doesn't. No partial credit — just like placement rounds.
MCQs on DBMS theory (normalisation, transactions, indexing) + timed SQL query challenges. Auto-evaluated with topic-wise analytics.
Student-wise dashboards showing: SQL query accuracy, theory concept mastery, weak topics. TPOs see batch-level DBMS readiness.
TCS NQT, Infosys SP, Wipro NLTH, Cognizant GenC, Accenture — every service company's technical MCQ section includes 5–10 DBMS questions. Product companies test SQL query writing in separate rounds. There is no placement test that skips DBMS
Many companies (Amazon, Goldman Sachs, Flipkart, analytics firms) have dedicated SQL rounds where you write queries on a live database. Students who can write JOINs, window functions, and subqueries fluently pass. Students who can only write SELECT * FROM fail.
Full Stack, Backend Developer, Data Engineer, Data Analyst, DevOps — every technical role interacts with databases daily. DBMS isn't just an interview topic — it's a daily work skill. Students who understand indexing, query optimisation, and transactions write better applications.
Product company system design rounds ask: "Which database would you use? SQL or NoSQL? How would you shard this? What indexes would you create?" DBMS knowledge is the foundation of system design answers — without it, system design rounds are impossible.
E-305, Industrial Area, Sahibzada Ajit Singh Nagar, Punjab 160055, IN
