DBMS + SQL Roadmap
A complete branching path through DBMS foundations, data modelling, normalization, relational algebra, SQL, transactions, concurrency control, storage, indexing, recovery and optional advanced database topics.
Learning outline
Expand a stage, choose a topic and start its Gate Smashers lectures.
01DBMS Foundations & Data Modelling6 topics · 23 lectures0 / 23
▶DBMS Fundamentals & Architecture0 / 4 lectures
▶Schema, Abstraction & Data Independence0 / 3 lectures
▶Relational Keys & Integrity Constraints0 / 4 lectures
▶ER Model Fundamentals, Attributes & Cardinalities0 / 5 lectures
▶Foreign Keys & Referential Integrity0 / 4 lectures
▶Weak Entities & ER Model Problem Solving0 / 3 lectures
02Normalization & Relational Theory13 topics · 36 lectures0 / 36
▶Normalization and Anomalies0 / 1 lectures
▶Relational Algebra Fundamentals, Selection & Projection0 / 3 lectures
▶Join Fundamentals, Natural, Equi & Self Joins0 / 4 lectures
▶Functional Dependencies & Attribute Closure0 / 2 lectures
▶Cartesian Product, Rename & Set Operations0 / 4 lectures
▶Outer Joins & Join Problem Solving0 / 3 lectures
▶First and Second Normal Forms0 / 2 lectures
▶Division Operator & Relational Algebra Problem Solving0 / 2 lectures
▶Third Normal Form & BCNF0 / 2 lectures
▶Tuple Relational Calculus0 / 1 lectures
▶Higher Normal Forms & Lossless Decomposition0 / 2 lectures
▶Minimal Covers & Dependency Preservation0 / 5 lectures
▶Normalization Problem Solving0 / 5 lectures
03SQL & Query Processing7 topics · 30 lectures0 / 30
▶SQL Fundamentals, Command Types & Constraints0 / 4 lectures
▶CREATE & ALTER in SQL0 / 5 lectures
▶SQL Queries, Subqueries, GROUP BY & HAVING0 / 5 lectures
▶IN, NOT IN, EXISTS, NOT EXISTS & CTEs0 / 5 lectures
▶SQL Execution Order, Aggregate Functions & NULL0 / 3 lectures
▶Correlated, Non-Correlated Subqueries & Joins0 / 3 lectures
▶SQL Problem Solving, Functions & Data Cleaning0 / 5 lectures
04Transactions & Concurrency Control6 topics · 20 lectures0 / 20
▶Transactions, ACID & Transaction States0 / 3 lectures
▶Schedules & Concurrency Problems0 / 4 lectures
▶Shared/Exclusive Locking & Two-Phase Locking0 / 4 lectures
▶Recoverable & Cascadeless Schedules0 / 2 lectures
▶Conflict & View Serializability0 / 4 lectures
▶Strict 2PL Variants & Timestamp Ordering0 / 3 lectures
05Storage, Indexing & Recovery5 topics · 15 lectures0 / 15
▶Indexing Fundamentals & I/O Cost0 / 4 lectures
▶Primary, Clustering & Secondary Indexes0 / 3 lectures
▶Log-Based Database Recovery0 / 2 lectures
▶RAID Fundamentals0 / 1 lectures
▶B-Tree Fundamentals, Insertion & B+ Tree0 / 5 lectures
06PL/SQL, Advanced Topics & Revision5 topics · 16 lectures0 / 16
▶PL/SQL Fundamentals & Programming0 / 3 lectures
▶Procedures, Cursors, %TYPE & %ROWTYPE0 / 4 lectures
▶Database Views & Objects0 / 3 lectures
▶Big Data, Data Warehousing & Hadoop0 / 3 lectures
▶Complete DBMS Revision & Interview Preparation0 / 3 lectures
Topics covered in this roadmap
Use this stage-by-stage outline to understand the complete learning path before opening the interactive roadmap.
DBMS Foundations & Data Modelling
DBMS Fundamentals & Architecture
A database management system provides structured, controlled access to related data while reducing the limitations of ordinary file systems. This topic introduces the purpose of DBMS software, its major services, the overall course scope, and common two-tier and three-tier application architectures. Students learn why centralized data management improves consistency, security, sharing, concurrency handling and recovery, and how database applications are organized around clients, application logic and database servers.
Schema, Abstraction & Data Independence
This topic explains how a database is described at different levels instead of exposing physical storage details directly to every user. Students learn the meaning of schema and instance, the external, conceptual and internal levels of the three-schema architecture, and the difference between logical and physical data independence. These ideas clarify how database structure can evolve while applications and user views remain insulated from many lower-level changes.
Relational Keys & Integrity Constraints
Relational databases rely on keys and integrity rules to keep rows identifiable and data valid. This topic covers integrity constraints together with super keys, candidate keys and primary keys, showing how uniqueness and minimality differ across key types. Students learn how a suitable primary key is chosen from candidate keys and why entity integrity is essential for reliable relational design. The concepts prepare learners for foreign keys, normalization and SQL constraints.
ER Model Fundamentals, Attributes & Cardinalities
The Entity-Relationship model captures real-world requirements before they are converted into relational tables. This topic introduces entities, attributes and relationships, then develops attribute types and the common one-to-one, one-to-many and many-to-many cardinalities. Students learn how these modelling choices describe the structure of a domain and influence later table design, primary-key placement and the need for separate relationship tables.
Foreign Keys & Referential Integrity
Foreign keys connect related relations and enforce consistency between parent and child rows. Students learn how a foreign key references a key in another table, what referential integrity requires during insert, update and delete operations, and how violations can occur. The topic also covers practical foreign-key questions and ON DELETE CASCADE, helping learners distinguish restrictive behavior from automatic propagation when referenced rows are removed.
Weak Entities & ER Model Problem Solving
Weak entities extend ER modelling to cases where an entity cannot be uniquely identified by its own attributes alone. This topic explains owner entities and identification dependence, then applies ER concepts to table-minimization and exam-style design problems. Students practice deciding when relationships require separate tables, how weak entities participate in mappings, and how to reason from an ER structure to a compact relational design without losing important constraints.
Normalization & Relational Theory
Normalization and Anomalies
Normalization is a systematic approach to organizing relational data so that unnecessary redundancy and modification problems are reduced. This introductory topic focuses on insertion, deletion and update anomalies and shows why poorly designed relations can create inconsistent or incomplete information. Students learn the motivation behind decomposition and normal forms before studying functional dependencies, making this node the conceptual starting point for the entire normalization branch.
Relational Algebra Fundamentals, Selection & Projection
Relational algebra is a procedural formal language for describing operations on relations. This topic introduces its role in relational query processing and develops the two most basic unary operations: selection and projection. Students learn that selection filters tuples according to a condition, while projection chooses attributes and may remove duplicate tuples under relational algebra semantics. These operators form the building blocks for more complex relational expressions.
Join Fundamentals, Natural, Equi & Self Joins
Joins combine related tuples from one or more relations and are central to relational querying. This topic introduces why joins are needed, then distinguishes natural, equi and self joins through examples. Students learn how join conditions connect rows, how natural joins use common compatible attributes automatically, how equi joins use explicit equality predicates, and how aliases make self joins possible when a table must be compared with itself.
Functional Dependencies & Attribute Closure
Functional dependencies express semantic relationships between attributes and provide the main reasoning tool used in normalization. This topic explains what X→Y means, important dependency properties, and how attribute closure is computed from a dependency set. Students learn to use closure to test whether attributes determine others and to support candidate-key and dependency reasoning before applying normal-form and decomposition rules.
Cartesian Product, Rename & Set Operations
This topic expands relational algebra with operators used to combine and restructure relations. Students learn how Cartesian product forms every possible tuple pair, how rename resolves naming ambiguity, and how union and set difference operate on union-compatible relations. Together these operations support more complex query expressions and help explain the formal basis of joins, self-combinations and set-oriented reasoning in relational databases.
Outer Joins & Join Problem Solving
Outer joins preserve rows that would be lost by an ordinary inner join. This topic develops left and right outer joins and uses a comparative practice problem covering inner, left, right and full outer joins. Students learn which unmatched rows are retained, where NULL values appear in the result, and how to predict row preservation accurately when solving SQL or relational-algebra join questions.
First and Second Normal Forms
This topic develops the first two stages of relational normalization. Students learn that First Normal Form requires atomic values and a well-defined tabular structure, while Second Normal Form removes partial dependencies on proper subsets of composite candidate keys. Examples show why 2NF matters mainly when keys contain multiple attributes and how decomposing a relation can remove redundancy caused by partial dependency.
Division Operator & Relational Algebra Problem Solving
The division operator handles relational queries that express an “all” requirement, a pattern that is harder to formulate using only basic operators. This topic explains the purpose of division and then applies relational algebra concepts in an important practice problem. Students learn to identify when a query has universal-quantification meaning and to combine selection, projection, set operations, products and division correctly in exam-style relational expressions.
Third Normal Form & BCNF
Third Normal Form and Boyce-Codd Normal Form address stronger dependency problems than 2NF. Students learn how transitive dependencies can create redundancy, how 3NF evaluates determinants and prime attributes, and why BCNF imposes the stricter requirement that every non-trivial determinant be a super key. The topic also clarifies the relationship between 3NF and BCNF and prepares learners for decomposition and dependency-preservation trade-offs.
Tuple Relational Calculus
Tuple Relational Calculus presents relational querying from a declarative perspective. Instead of specifying an operation sequence, a calculus expression describes the properties that result tuples must satisfy. Students learn the role of tuple variables, predicates, free and bound variables, quantification and safe expressions. The topic provides an important contrast with procedural relational algebra and helps explain the theoretical basis of declarative query languages such as SQL.
Higher Normal Forms & Lossless Decomposition
After 3NF and BCNF, higher normal forms address dependencies that are not captured by ordinary functional dependencies alone. This topic reviews the progression through 1NF, 2NF, 3NF, BCNF, 4NF and 5NF, and connects higher normal forms with decomposition quality. Students also study lossless and lossy decomposition so they can determine whether splitting a relation preserves the ability to reconstruct the original information without spurious tuples.
Minimal Covers & Dependency Preservation
This topic combines two closely related tools used when designing normalized schemas. Students learn how a minimal or canonical cover removes redundant dependencies while preserving equivalence, and how dependency preservation determines whether original constraints can still be enforced on decomposed relations without recomputing joins. BCNF examples highlight the important trade-off between stronger normalization and preserving all dependencies in individual decomposed relations.
Normalization Problem Solving
Normalization questions require several concepts to be applied in the correct order rather than memorized separately. This practice-focused topic develops a repeatable method for finding keys, determining the highest normal form, identifying violations and reasoning about schema decomposition. Students work through exam-style examples involving functional dependencies and relation schemas, building speed and accuracy for GATE-style questions, university exams and technical interviews.
SQL & Query Processing
SQL Fundamentals, Command Types & Constraints
SQL is the practical language used to define, query, modify and control relational databases. This topic introduces SQL features and its major command families, then connects them with schema and integrity constraints. Students learn the roles of DDL, DML, DCL and TCL, the difference between structural and data-level operations, how DELETE, DROP and TRUNCATE differ, and how constraints enforce valid values and relationships.
CREATE & ALTER in SQL
CREATE and ALTER are core DDL commands used to build and evolve relational schemas. Students learn how tables are created with columns and data types, how the CREATE command is executed in Oracle, and how ALTER changes an existing table definition. The topic also distinguishes ALTER from UPDATE so learners do not confuse schema modification with row-value modification, a common source of mistakes in exams and interviews.
SQL Queries, Subqueries, GROUP BY & HAVING
This topic moves from basic SQL querying into nested queries and grouped analysis. Students practice common query patterns, including second-highest-value logic, then learn how GROUP BY forms groups for aggregate evaluation and how HAVING filters those groups. Additional subquery examples reinforce the difference between filtering rows and filtering grouped results, giving learners a practical foundation for more advanced nested and correlated query patterns.
IN, NOT IN, EXISTS, NOT EXISTS & CTEs
Membership predicates and Common Table Expressions make complex SQL conditions easier to express. This topic covers IN and NOT IN for set membership, EXISTS and NOT EXISTS for existence testing, and the WITH clause for defining reusable named query blocks. Students learn how these constructs interact with subqueries, why EXISTS is evaluated by row existence rather than returned values, and how CTEs can improve readability in multi-step queries.
SQL Execution Order, Aggregate Functions & NULL
Understanding SQL requires knowing both what aggregate functions compute and when query clauses are logically processed. This topic covers SUM, AVG, COUNT, MIN and MAX, the behavior of aggregates with NULL values, and the logical order in which SQL query clauses are evaluated. Students learn why written clause order differs from logical processing order and how NULL treatment can change aggregate results and query interpretation.
Correlated, Non-Correlated Subqueries & Joins
This topic compares three important SQL approaches for combining or deriving data: correlated subqueries, non-correlated subqueries and joins. Students learn when a subquery can execute independently, when it depends on values from the current outer row, and how join-based formulations differ conceptually from nested-query formulations. The comparison builds stronger query-design judgment and helps learners recognize equivalent solutions in interview and exam questions.
SQL Problem Solving, Functions & Data Cleaning
This practice-oriented topic applies SQL to interview-style problems and everyday data manipulation. Students solve nth-highest-salary and basic concept questions, distinguish single-row from multi-row functions, use character functions, and apply SQL functions to data-cleaning tasks. The node develops practical fluency in selecting functions, transforming text, handling repeated query patterns and reasoning through common placement questions without relying on one fixed query template.
Transactions & Concurrency Control
Transactions, ACID & Transaction States
A transaction is a logical unit of database work whose operations must be managed reliably even when many users access data concurrently. This topic introduces transaction processing, the ACID properties and the standard states a transaction passes through from active execution to commit or failure. Students learn how atomicity, consistency, isolation and durability define correct transaction behavior and why state transitions matter during recovery and concurrency control.
Schedules & Concurrency Problems
Concurrent transactions improve throughput but can produce incorrect results when their operations interleave unsafely. This topic introduces serial and interleaved schedules and examines dirty reads, incorrect summaries, lost updates, phantom reads and unrepeatable reads. Students learn to recognize read-write and write-read conflicts directly from schedules and understand why concurrency-control protocols are needed to preserve isolation while still allowing useful parallel execution.
Shared/Exclusive Locking & Two-Phase Locking
Lock-based concurrency control coordinates transaction access to shared data items. This topic explains shared and exclusive locks, their compatibility, common drawbacks of basic locking, and the Two-Phase Locking protocol with its growing and shrinking phases. Students learn how 2PL guarantees conflict serializability, why deadlocks can still occur, and how lock acquisition and release rules shape safe concurrent schedules.
Recoverable & Cascadeless Schedules
Recoverability focuses on whether transactions commit in an order that permits safe recovery after failures. This topic distinguishes irrecoverable and recoverable schedules and then explains cascading rollback and cascadeless execution. Students learn why a transaction that reads another transaction’s uncommitted value creates recovery dependencies, how commit ordering affects correctness, and why avoiding reads of uncommitted data prevents chains of rollback across multiple transactions.
Conflict & View Serializability
Serializability determines whether an interleaved schedule is equivalent to some correct serial execution. This topic introduces serializability, conflict equivalence, precedence graphs and view serializability. Students learn how conflicting operations are identified, how graph cycles determine conflict serializability, and why view serializability accepts some schedules that conflict serializability rejects. The comparison is essential for analysing concurrent schedules in DBMS examinations.
Strict 2PL Variants & Timestamp Ordering
This topic compares stricter forms of Two-Phase Locking with timestamp-based concurrency control. Students learn how strict, rigorous and conservative 2PL modify lock holding or acquisition rules, then study basic timestamp ordering and its problem-solving method. The node highlights two different strategies for maintaining serializable execution: controlling access through locks and ordering conflicting operations according to transaction timestamps.
Storage, Indexing & Recovery
Indexing Fundamentals & I/O Cost
Indexes reduce the number of storage blocks that must be examined when searching for records. This topic introduces the purpose of indexing, then develops numerical reasoning about disk I/O cost and surveys major index categories. Students learn why block accesses dominate traditional database cost models, how indexes trade extra storage and update work for faster retrieval, and how to estimate search effort in common indexing questions.
Primary, Clustering & Secondary Indexes
Different index types are defined by the ordering of the data file and the search key used by the index. This topic studies primary, clustering and secondary indexes through examples and introduces multilevel indexing ideas. Students learn how a primary index relates to an ordered key field, how clustering groups records by an ordered non-key field, and how secondary indexes provide alternate access paths independent of the physical record order.
Log-Based Database Recovery
Log-based recovery restores correct database state after transaction or system failures by recording update information in a log. This topic compares immediate and deferred database modification and explains when UNDO and REDO actions are required. Students learn the role of logging in preserving atomicity and durability, why log records must be coordinated with database writes, and how the recovery strategy depends on whether uncommitted changes can reach the database.
RAID Fundamentals
RAID combines multiple physical disks to improve performance, availability or both through striping, mirroring and parity. This topic introduces common RAID levels including RAID 0, 1, 4, 5, 6 and nested RAID 10. Students learn the basic trade-offs among usable capacity, read/write performance and fault tolerance, and why different RAID organizations protect against different numbers and types of disk failures.
B-Tree Fundamentals, Insertion & B+ Tree
B-Trees and B+ Trees are balanced multiway search structures designed for block-oriented storage. This topic introduces B-Tree structure, pointers, order and insertion, then compares B-Trees with B+ Trees and studies B+ Tree order for leaf and non-leaf nodes. Students learn how balanced height supports predictable search cost and why B+ Trees are widely used for database indexes and range-oriented access.
PL/SQL, Advanced Topics & Revision
PL/SQL Fundamentals & Programming
PL/SQL extends SQL with procedural programming features so database logic can include variables, conditions, loops and executable blocks. This topic introduces PL/SQL and its block structure, then develops basic programs using executable examples and iteration constructs such as WHILE and FOR loops. Students learn how procedural statements work alongside SQL and how block-based programming supports reusable server-side database logic.
Procedures, Cursors, %TYPE & %ROWTYPE
This topic develops reusable PL/SQL program units and techniques for working with query results. Students learn the difference between local and stored procedures, how procedures can fetch database data, how cursors manage row-by-row query processing, and how %TYPE and %ROWTYPE derive variable or record types from database definitions. These features reduce duplication and help PL/SQL code stay aligned with schema changes.
Database Views & Objects
Views and other database objects provide logical abstraction and reusable structures above stored tables. This topic introduces database views, compares simple, complex and materialized views, and surveys common objects available in systems such as Oracle and SQL Server. Students learn how ordinary views store query definitions rather than independent row copies, how materialized views persist query results, and why database objects serve different structural and performance purposes.
Big Data, Data Warehousing & Hadoop
This optional advanced topic broadens the roadmap beyond traditional relational DBMS concepts. Students review important questions on big data and data warehousing, learn how big data differs from ordinary small-scale datasets, and receive an introduction to Hadoop and its distributed framework. The node focuses on the high-level purpose of these technologies and the kinds of storage and processing challenges that motivate distributed data platforms.
Complete DBMS Revision & Interview Preparation
The final node consolidates high-value DBMS concepts for rapid revision and interview preparation. It revisits basic database and data-modelling questions, Codd’s rules for relational database systems, and commonly asked SQL interview questions. Students use this section to connect definitions across the roadmap, test whether core distinctions are clear, and review practical SQL and relational concepts after completing the detailed learning path.