Hello World!<\/title>\n <link rel=\"preconnect\" href=\"https:\/\/fonts.googleapis.com\">\n <link rel=\"preconnect\" href=\"https:\/\/fonts.gstatic.com\" crossorigin>\n <link href=\"https:\/\/fonts.googleapis.com\/css2?family=Lato:wght@400;600;700&display=swap\" rel=\"stylesheet\">\n <style>\n .iitr_banner_container {\n font-family: lato;\n display: flex;\n flex-direction: row;\n justify-content: space-between;\n border-radius: 16px;\n background: linear-gradient(88deg, #19000F 24.45%, #66003F 83.33%);\n position: relative;\n\n @media (max-width: 768px) {\n min-height: 450px;\n overflow: hidden;\n flex-direction: column;\n }\n }\n .iitr_banner_content {\n display: flex;\n flex-direction: column;\n align-items: flex-start;\n justify-content: center;\n padding: 20px;\n max-width: 50%;\n\n @media (max-width: 768px) {\n max-width: 100%;\n }\n }\n .iitr_banner_title {\n font-size: 24px;\n font-weight: bold;\n color: #FFFFFF;\n\n @media (max-width: 768px) {\n font-size: 20px;\n }\n }\n .iitr_banner_title_highlight {\n color: #FF0071;\n }\n .iitr_banner_subtitle {\n font-size: 14px;\n color: #FFFFFF;\n margin: 10px 0;\n }\n .iitr_banner_btn {\n display: flex;\n justify-content: center;\n align-items: center;\n padding: 8px 48px;\n background-color: #F8F9F9;\n border-radius: 8px;\n border: 1px solid #E3E8E8;\n font-size: 1.4rem;\n font-weight: 600;\n color: #0D3231;\n text-decoration: none;\n margin-top: 16px;\n\n @media (max-width: 768px) {\n padding: 8px 32px;\n }\n }\n .iitr_banner_image {\n position: absolute;\n bottom: 0;\n right: 0;\n\n @media (max-width: 768px) {\n right: auto;\n object-fit: cover;\n min-width: 100%\n }\n }\n .iitr_banner_image_logo {\n margin-bottom: 16px;\n \n @media (max-width: 768px) {\n width: 240px;\n }\n }\n\n \/* Responsive visibility utilities *\/\n .show-in-mobile {\n display: none;\n }\n .hide-in-mobile {\n display: block;\n }\n\n \/* Mobile breakpoint (768px and below) *\/\n @media (max-width: 768px) {\n .show-in-mobile {\n display: block;\n }\n .hide-in-mobile {\n display: none;\n }\n }\n <\/style>\n <\/head>\n <body>\n <div class=\"iitr_banner_container\">\n <div class=\"iitr_banner_content\">\n <img decoding=\"async\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/281\/original\/Frame_1430102419.svg?1769058073\" class=\"iitr_banner_image_logo\" \/>\n <div class=\"iitr_banner_title\">\n AI Engineering Course Advanced Certification by \n \n IIT-Roorkee CEC\n <\/span>\n <\/div>\n <div class=\"iitr_banner_subtitle\">\n A hands on AI engineering program covering Machine Learning, Generative AI, and LLMs – designed for working professionals & delivered by IIT Roorkee in collaboration with Scaler.\n <\/div>\n <a class=\"iitr_banner_btn\" href=\"#\" id=\"iitr_banner_btn\">Enrol Now<\/a>\n <\/div>\n \n <img decoding=\"async\" class=\"iitr_banner_image hide-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/282\/original\/iitr_2.svg?1769058132\" \/>\n \n <img decoding=\"async\" class=\"iitr_banner_image show-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/283\/original\/iitr_2_%281%29.svg?1769059469\" \/>\n <\/div>\n <script>\n document.addEventListener(\"DOMContentLoaded\", () => {\n const pathParts = location.pathname.split(\"\/\").filter(Boolean);\n const currentSlug = pathParts.length > 0 ? pathParts[pathParts.length - 1] : \"homepage\";\n const url = `https:\/\/www.scaler.com\/iit-roorkee-advanced-ai-engineering-course?utm_source=blog&utm_medium=iit_roorkee&utm_content=${currentSlug}`;\n const btns = document.querySelectorAll(\".iitr_banner_btn\");\n btns.forEach(btn => {\n btn.href = url;\n });\n });\n <\/script>\n <\/body>\n<\/html>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 1 , Fundamentals (Weeks 1\u20134)<\/strong><\/span><\/h2>\n\n\n\nThis phase builds your foundation. Most students rush into SQL, but without understanding how databases are designed, SQL becomes mechanical. These 4 weeks focus on how data is structured and why.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What Is DBMS? Types of Databases<\/strong><\/h3>\n\n\n\nA Database Management System (DBMS) is software that allows you to store, organize, retrieve, and manage data efficiently. Instead of working with raw files, DBMS provides structured ways to interact with data using queries.<\/p>\n\n\n\nAt a deeper level, a DBMS handles:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data storage and retrieval<\/li>\n\n\n\n<li>Security and access control<\/li>\n\n\n\n<li>Concurrency (multiple users accessing data)<\/li>\n\n\n\n<li>Data integrity and consistency<\/li>\n<\/ul>\n\n\n\nYou should understand different types of databases and when to use them:<\/p>\n\n\n\nRelational Databases (RDBMS)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Examples: MySQL, PostgreSQL<\/li>\n\n\n\n<li>Data is stored in tables (rows and columns)<\/li>\n\n\n\n<li>Follows strict schema<\/li>\n\n\n\n<li>Uses SQL for querying<\/li>\n\n\n\n<li>Best for structured data and consistency<\/li>\n<\/ul>\n\n\n\nNoSQL Databases<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Examples: MongoDB, Redis<\/li>\n\n\n\n<li>Flexible schema (or schema-less)<\/li>\n\n\n\n<li>Types: document, key-value, column-family<\/li>\n\n\n\n<li>Designed for scalability and high performance<\/li>\n\n\n\n<li>Used in large-scale systems like social media<\/li>\n<\/ul>\n\n\n\nDistributed Databases<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data is spread across multiple machines<\/li>\n\n\n\n<li>Improves availability and fault tolerance<\/li>\n\n\n\n<li>Used in large-scale systems (e.g., cloud platforms)<\/li>\n<\/ul>\n\n\n\nFocus on understanding how databases store, retrieve, and manage data internally, not just definitions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">ER Modeling & Schema Design<\/strong><\/h3>\n\n\n\nBefore writing SQL, you must learn how to design a database properly. This is where beginners often struggle, but it\u2019s one of the most important skills.<\/p>\n\n\n\nER (Entity-Relationship) modeling helps you visualize the structure of your database.<\/p>\n\n\n\nKey components:<\/p>\n\n\n\nEntities<\/strong> <\/strong> These are real-world objects or concepts. Example: Student, Course, Order<\/p>\n\n\n\nAttributes<\/strong> <\/strong> Properties of entities. Example: Student \u2192 name, id, age<\/p>\n\n\n\nRelationships<\/strong> <\/strong> How entities are connected. Example: A student enrolls in a course<\/p>\n\n\n\nCardinality<\/strong> <\/strong> Defines the relationship type:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>One-to-One<\/li>\n\n\n\n<li>One-to-Many<\/li>\n\n\n\n<li>Many-to-Many<\/li>\n<\/ul>\n\n\n\nConstraints<\/strong> <\/strong> Rules applied to data:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Primary Key (unique identifier)<\/li>\n\n\n\n<li>Foreign Key (link between tables)<\/li>\n\n\n\n<li>NOT NULL, UNIQUE<\/li>\n<\/ul>\n\n\n\nThe most important skill here is converting ER diagrams into relational schemas (tables). For example, a many-to-many relationship is converted into a separate table with foreign keys.<\/p>\n\n\n\nThis step ensures your database is logically correct before implementation.<\/p>\n\n\n\n<!DOCTYPE html>\n<html>\n <head>\n <title>Hello World!<\/title>\n <link rel=\"preconnect\" href=\"https:\/\/fonts.googleapis.com\">\n <link rel=\"preconnect\" href=\"https:\/\/fonts.gstatic.com\" crossorigin>\n <link href=\"https:\/\/fonts.googleapis.com\/css2?family=Lato:wght@400;600;700&display=swap\" rel=\"stylesheet\">\n <style>\n .iitr_banner_container {\n font-family: lato;\n display: flex;\n flex-direction: row;\n justify-content: space-between;\n border-radius: 16px;\n background: linear-gradient(88deg, #19000F 24.45%, #66003F 83.33%);\n position: relative;\n\n @media (max-width: 768px) {\n min-height: 450px;\n overflow: hidden;\n flex-direction: column;\n }\n }\n .iitr_banner_content {\n display: flex;\n flex-direction: column;\n align-items: flex-start;\n justify-content: center;\n padding: 20px;\n max-width: 50%;\n\n @media (max-width: 768px) {\n max-width: 100%;\n }\n }\n .iitr_banner_title {\n font-size: 24px;\n font-weight: bold;\n color: #FFFFFF;\n\n @media (max-width: 768px) {\n font-size: 20px;\n }\n }\n .iitr_banner_title_highlight {\n color: #FF0071;\n }\n .iitr_banner_subtitle {\n font-size: 14px;\n color: #FFFFFF;\n margin: 10px 0;\n }\n .iitr_banner_btn {\n display: flex;\n justify-content: center;\n align-items: center;\n padding: 8px 48px;\n background-color: #F8F9F9;\n border-radius: 8px;\n border: 1px solid #E3E8E8;\n font-size: 1.4rem;\n font-weight: 600;\n color: #0D3231;\n text-decoration: none;\n margin-top: 16px;\n\n @media (max-width: 768px) {\n padding: 8px 32px;\n }\n }\n .iitr_banner_image {\n position: absolute;\n bottom: 0;\n right: 0;\n\n @media (max-width: 768px) {\n right: auto;\n object-fit: cover;\n min-width: 100%\n }\n }\n .iitr_banner_image_logo {\n margin-bottom: 16px;\n \n @media (max-width: 768px) {\n width: 240px;\n }\n }\n\n \/* Responsive visibility utilities *\/\n .show-in-mobile {\n display: none;\n }\n .hide-in-mobile {\n display: block;\n }\n\n \/* Mobile breakpoint (768px and below) *\/\n @media (max-width: 768px) {\n .show-in-mobile {\n display: block;\n }\n .hide-in-mobile {\n display: none;\n }\n }\n <\/style>\n <\/head>\n <body>\n <div class=\"iitr_banner_container\">\n <div class=\"iitr_banner_content\">\n <img decoding=\"async\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/281\/original\/Frame_1430102419.svg?1769058073\" class=\"iitr_banner_image_logo\" \/>\n <div class=\"iitr_banner_title\">\n AI Engineering Course Advanced Certification by \n \n IIT-Roorkee CEC\n <\/span>\n <\/div>\n <div class=\"iitr_banner_subtitle\">\n A hands on AI engineering program covering Machine Learning, Generative AI, and LLMs – designed for working professionals & delivered by IIT Roorkee in collaboration with Scaler.\n <\/div>\n <a class=\"iitr_banner_btn\" href=\"#\" id=\"iitr_banner_btn\">Enrol Now<\/a>\n <\/div>\n \n <img decoding=\"async\" class=\"iitr_banner_image hide-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/282\/original\/iitr_2.svg?1769058132\" \/>\n \n <img decoding=\"async\" class=\"iitr_banner_image show-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/283\/original\/iitr_2_%281%29.svg?1769059469\" \/>\n <\/div>\n <script>\n document.addEventListener(\"DOMContentLoaded\", () => {\n const pathParts = location.pathname.split(\"\/\").filter(Boolean);\n const currentSlug = pathParts.length > 0 ? pathParts[pathParts.length - 1] : \"homepage\";\n const url = `https:\/\/www.scaler.com\/iit-roorkee-advanced-ai-engineering-course?utm_source=blog&utm_medium=iit_roorkee&utm_content=${currentSlug}`;\n const btns = document.querySelectorAll(\".iitr_banner_btn\");\n btns.forEach(btn => {\n btn.href = url;\n });\n });\n <\/script>\n <\/body>\n<\/html>\n\n\n\n<h3 class=\"wp-block-heading\">Relational Model & Relational Algebra<\/strong><\/h3>\n\n\n\nOnce your schema is ready, you need to understand how relational databases actually work behind the scenes.<\/p>\n\n\n\nRelational Model<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data is stored in tables (relations)<\/li>\n\n\n\n<li>Each row is a tuple<\/li>\n\n\n\n<li>Each column is an attribute<\/li>\n\n\n\n<li>Each table has a primary key<\/li>\n<\/ul>\n\n\n\nThis model ensures structure and consistency.<\/p>\n\n\n\nRelational Algebra<\/strong> <\/strong> This is the theoretical foundation of SQL. It defines operations used to manipulate data.<\/p>\n\n\n\nKey operations:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SELECT \u2192 choose rows<\/li>\n\n\n\n<li>PROJECT \u2192 choose columns<\/li>\n\n\n\n<li>JOIN \u2192 combine tables<\/li>\n\n\n\n<li>UNION \u2192 merge results<\/li>\n\n\n\n<li>DIFFERENCE \u2192 find missing data<\/li>\n<\/ul>\n\n\n\nUnderstanding relational algebra helps you see how queries are executed internally.<\/p>\n\n\n\nHow Queries Are Processed Conceptually<\/strong><\/p>\n\n\n\nWhen you write a SQL query, the DBMS doesn\u2019t execute it exactly as written. Instead, it:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Converts SQL into relational algebra<\/li>\n\n\n\n<li>Optimizes the query (chooses the fastest way)<\/li>\n\n\n\n<li>Executes it using indexes and execution plans<\/li>\n<\/ul>\n\n\n\nThis is why the same query can have different performance depending on how it\u2019s written.<\/p>\n\n\n\nMastering this phase gives you a strong foundation for advanced topics like normalization, indexing, and query optimization later on.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 2 , SQL Mastery (Weeks 5\u20138)<\/strong><\/span><\/h2>\n\n\n\nThis is where things become practical. You move from \u201cunderstanding databases\u201d to actually working with them like a backend developer<\/strong>. The goal here isn\u2019t just to write queries, it\u2019s to write efficient, readable, and optimized queries<\/strong>.<\/p>\n\n\n\nFor broader backend context: <a href=\"https:\/\/www.scaler.com\/topics\/software-engineering\/backend-developer-roadmap\/\">https:\/\/www.scaler.com\/topics\/software-engineering\/backend-developer-roadmap\/<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">SQL Basics: SELECT, JOIN, GROUP BY, Subqueries<\/strong><\/h3>\n\n\n\nThis is your foundation. But instead of just learning syntax, focus on how queries transform data step by step<\/strong>.<\/p>\n\n\n\nSELECT, WHERE, ORDER BY<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SELECT decides what data you want<\/em><\/li>\n\n\n\n<li>WHERE filters rows before processing<\/li>\n\n\n\n<li>ORDER BY sorts the final result<\/li>\n<\/ul>\n\n\n\nKey insight: SQL execution is not top-to-bottom as written. The actual order is: FROM \u2192 WHERE \u2192 GROUP BY \u2192 HAVING \u2192 SELECT \u2192 ORDER BY<\/strong><\/p>\n\n\n\nUnderstanding this helps you debug and optimize queries.<\/p>\n\n\n\nJOINs (INNER, LEFT, RIGHT)<\/strong> <\/strong>JOINs are one of the most important topics for interviews.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>INNER JOIN<\/strong> \u2192 returns matching records from both tables<\/li>\n\n\n\n<li>LEFT JOIN<\/strong> \u2192 keeps all records from the left table<\/li>\n\n\n\n<li>RIGHT JOIN<\/strong> \u2192 keeps all records from the right table<\/li>\n<\/ul>\n\n\n\nDeep understanding:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>JOINs internally use algorithms like nested loop join, hash join, or merge join<\/strong><\/li>\n\n\n\n<li>Performance depends on indexing and table size<\/li>\n<\/ul>\n\n\n\nGROUP BY & Aggregation<\/strong> <\/strong>Used to summarize data:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>COUNT(), SUM(), AVG(), MAX(), MIN()<\/li>\n<\/ul>\n\n\n\nImportant concept:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>WHERE filters rows before grouping<\/strong><\/li>\n\n\n\n<li>HAVING filters after grouping<\/strong><\/li>\n<\/ul>\n\n\n\nSubqueries<\/strong> <\/strong>Queries inside queries.<\/p>\n\n\n\nTypes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Scalar subqueries (return single value)<\/li>\n\n\n\n<li>Correlated subqueries (run per row \u2192 slower)<\/li>\n<\/ul>\n\n\n\nInterview tip:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Often replace subqueries with JOINs for better performance<\/li>\n<\/ul>\n\n\n\nPractice here should be heavy. Solve real datasets, this is where most learning happens.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Advanced SQL: Window Functions, CTEs, Stored Procedures<\/strong><\/h3>\n\n\n\nThis is where you move from \u201caverage\u201d to interview-ready<\/strong>.<\/p>\n\n\n\nWindow Functions<\/strong> <\/strong>These allow you to perform calculations across rows without collapsing them<\/strong>.<\/p>\n\n\n\nExamples:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ROW_NUMBER() \u2192 unique ranking<\/li>\n\n\n\n<li>RANK() \u2192 handles ties<\/li>\n\n\n\n<li>DENSE_RANK() \u2192 no gaps in ranking<\/li>\n<\/ul>\n\n\n\nUse cases:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Top N per group<\/li>\n\n\n\n<li>Running totals<\/li>\n\n\n\n<li>Ranking users or products<\/li>\n<\/ul>\n\n\n\nKey idea: Window functions use an OVER() clause<\/strong> with PARTITION and ORDER.<\/p>\n\n\n\nCommon Table Expressions (CTEs)<\/strong> <\/strong>Temporary result sets using WITH.<\/p>\n\n\n\nWhy they matter:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Make complex queries readable<\/li>\n\n\n\n<li>Help break down logic step-by-step<\/li>\n\n\n\n<li>Used in recursive queries (hierarchies like org charts)<\/li>\n<\/ul>\n\n\n\nStored Procedures<\/strong> <\/strong>Predefined SQL logic stored in the database.<\/p>\n\n\n\nWhy used:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Reusability<\/li>\n\n\n\n<li>Performance (precompiled)<\/li>\n\n\n\n<li>Security (controlled execution)<\/li>\n<\/ul>\n\n\n\nBut in modern systems, many companies shift logic to application code instead.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Views, Triggers & Indexing<\/strong><\/h3>\n\n\n\nThis section connects SQL to real-world database behavior<\/strong>.<\/p>\n\n\n\nViews<\/strong> <\/strong>Virtual tables created from queries.<\/p>\n\n\n\nWhy use them:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Simplify complex queries<\/li>\n\n\n\n<li>Provide abstraction<\/li>\n\n\n\n<li>Restrict access to sensitive data<\/li>\n<\/ul>\n\n\n\nTypes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Simple views<\/li>\n\n\n\n<li>Materialized views (physically stored \u2192 faster reads)<\/li>\n<\/ul>\n\n\n\nTriggers<\/strong> <\/strong>Automatically execute when events happen (INSERT, UPDATE, DELETE).<\/p>\n\n\n\nUse cases:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Audit logs<\/li>\n\n\n\n<li>Data validation<\/li>\n\n\n\n<li>Maintaining derived data<\/li>\n<\/ul>\n\n\n\nBe careful:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Overuse can make systems hard to debug<\/li>\n<\/ul>\n\n\n\nIndexing (B-tree, Hash)<\/strong><\/p>\n\n\n\nThis is one of the most important performance concepts<\/strong>.<\/p>\n\n\n\nWithout index:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Full table scan \u2192 slow<\/li>\n<\/ul>\n\n\n\nWith index:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Faster lookup (like a book index)<\/li>\n<\/ul>\n\n\n\nTypes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>B-tree index<\/strong> \u2192 balanced, supports range queries<\/li>\n\n\n\n<li>Hash index<\/strong> \u2192 fast equality lookup<\/li>\n<\/ul>\n\n\n\nAdvanced insights:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Indexes improve read speed but slow down writes<\/strong><\/li>\n\n\n\n<li>Too many indexes = bad performance<\/li>\n\n\n\n<li>Composite indexes depend on column order<\/li>\n<\/ul>\n\n\n\nInterview gold question: \u201cWhy is my query slow even with an index?\u201d \u2192 Because of wrong index usage, low selectivity, or poor query structure.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 3 , Core Theory (Weeks 9\u201312)<\/strong><\/span><\/h2>\n\n\n\nThis phase is where most interview questions come from<\/strong>. You now connect theory with real systems.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Normalization: 1NF to BCNF<\/strong><\/h3>\n\n\n\nNormalization reduces redundancy and prevents anomalies.<\/p>\n\n\n\n1NF (First Normal Form)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>No repeating groups<\/li>\n\n\n\n<li>Atomic values<\/li>\n<\/ul>\n\n\n\n2NF<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Remove partial dependency (non-key depends on part of key)<\/li>\n<\/ul>\n\n\n\n3NF<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Remove transitive dependency (non-key depends on another non-key)<\/li>\n<\/ul>\n\n\n\nBCNF<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stronger version of 3NF (every determinant must be a candidate key)<\/li>\n<\/ul>\n\n\n\nFunctional Dependencies (FDs)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Define relationships between attributes Example: StudentID \u2192 Name<\/li>\n<\/ul>\n\n\n\nAnomalies<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Insertion anomaly \u2192 can\u2019t insert data without other data<\/li>\n\n\n\n<li>Deletion anomaly \u2192 losing important info<\/li>\n\n\n\n<li>Update anomaly \u2192 inconsistency<\/li>\n<\/ul>\n\n\n\nReal insight:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Over-normalization can hurt performance \u2192 denormalization is used in real systems<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">ACID Properties & Transactions<\/strong><\/h3>\n\n\n\nTransactions ensure reliable database operations, especially in critical systems like banking.<\/p>\n\n\n\nAtomicity<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>All or nothing<\/li>\n\n\n\n<li>If one step fails, everything rolls back<\/li>\n<\/ul>\n\n\n\nConsistency<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database remains valid before and after transaction<\/li>\n<\/ul>\n\n\n\nIsolation<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Transactions don\u2019t interfere with each other<\/li>\n<\/ul>\n\n\n\nDurability<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Once committed, data is permanent<\/li>\n<\/ul>\n\n\n\nReal-world example: Money transfer \u2192 both debit and credit must succeed or fail together.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Concurrency Control & Locking<\/strong><\/h3>\n\n\n\nWhen multiple users access the database, conflicts occur.<\/p>\n\n\n\nLocks<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Shared lock \u2192 read<\/li>\n\n\n\n<li>Exclusive lock \u2192 write<\/li>\n<\/ul>\n\n\n\nDeadlocks<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Two transactions waiting on each other<\/li>\n<\/ul>\n\n\n\nPrevention techniques:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Lock ordering<\/li>\n\n\n\n<li>Timeout<\/li>\n\n\n\n<li>Deadlock detection algorithms<\/li>\n<\/ul>\n\n\n\nIsolation Levels<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Read Uncommitted<\/li>\n\n\n\n<li>Read Committed<\/li>\n\n\n\n<li>Repeatable Read<\/li>\n\n\n\n<li>Serializable<\/li>\n<\/ul>\n\n\n\nEach level balances consistency vs performance<\/strong>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Query Optimization & Execution Plans<\/strong><\/h3>\n\n\n\nThis is where DBMS becomes powerful.<\/p>\n\n\n\nWhen you write a query:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>DBMS converts it into multiple possible execution plans<\/li>\n\n\n\n<li>Chooses the cheapest one (cost-based optimization)<\/li>\n<\/ul>\n\n\n\nExecution Plans Show:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Table scans vs index scans<\/li>\n\n\n\n<li>Join methods<\/li>\n\n\n\n<li>Estimated cost<\/li>\n<\/ul>\n\n\n\nKey optimization techniques:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Proper indexing<\/li>\n\n\n\n<li>Avoid SELECT *<\/li>\n\n\n\n<li>Use JOIN instead of subqueries (in many cases)<\/li>\n\n\n\n<li>Filter early (WHERE clause)<\/li>\n<\/ul>\n\n\n\nThis is what separates average developers from strong backend engineers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 4 , NoSQL & Beyond (Weeks 13\u201316)<\/strong><\/span><\/h2>\n\n\n\nNow you step into modern, scalable systems.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">NoSQL Databases: MongoDB, Cassandra, Redis<\/strong><\/h3>\n\n\n\nEach type solves a specific problem.<\/p>\n\n\n\nDocument Stores (MongoDB)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>JSON-like structure<\/li>\n\n\n\n<li>Flexible schema<\/li>\n\n\n\n<li>Good for rapidly changing data<\/li>\n<\/ul>\n\n\n\nWide-Column Stores (Cassandra)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Designed for massive scale<\/li>\n\n\n\n<li>High write throughput<\/li>\n\n\n\n<li>Used in distributed systems<\/li>\n<\/ul>\n\n\n\nKey-Value Stores (Redis)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Extremely fast (in-memory)<\/li>\n\n\n\n<li>Used for caching, sessions<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">SQL vs NoSQL: When to Use What<\/strong><\/h3>\n\n\n\nThis is a common system design question.<\/p>\n\n\n\nUse SQL when:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Strong consistency needed<\/li>\n\n\n\n<li>Structured data<\/li>\n\n\n\n<li>Complex queries<\/li>\n<\/ul>\n\n\n\nUse NoSQL when:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>High scalability needed<\/li>\n\n\n\n<li>Flexible schema<\/li>\n\n\n\n<li>Large distributed systems<\/li>\n<\/ul>\n\n\n\nReal-world systems often use both together.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Database Design for Real-World Applications<\/strong><\/h3>\n\n\n\nThis is where everything comes together.<\/p>\n\n\n\nYou should be able to design systems like:<\/p>\n\n\n\nE-commerce app:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Users, Products, Orders<\/li>\n\n\n\n<li>Relationships and indexing<\/li>\n\n\n\n<li>Handling transactions<\/li>\n<\/ul>\n\n\n\nSocial media app:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Posts, Likes, Comments<\/li>\n\n\n\n<li>High read\/write scaling<\/li>\n<\/ul>\n\n\n\nScaling Concepts:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Sharding<\/strong> – split data across servers<\/li>\n\n\n\n<li>Replication<\/strong> – copy data for availability<\/li>\n<\/ul>\n\n\n\n\ud83d\udc49 Explore system design: <a href=\"https:\/\/www.scaler.com\/topics\/system-design\/\">https:\/\/www.scaler.com\/topics\/system-design\/<\/a><\/p>\n\n\n\nThe goal at this stage is not just knowing concepts, but being able to design systems that handle real-world scale, performance, and reliability.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Interview Questions & Preparation (In-Depth Guide)<\/strong><\/span><\/h2>\n\n\n\nThis is the phase where your learning turns into selection-level preparation. At this point, it\u2019s not about knowing topics, it\u2019s about being able to explain, apply, and defend your answers under pressure.<\/p>\n\n\n\nMost candidates struggle not because they don\u2019t know DBMS, but because they give shallow answers, can\u2019t connect theory to real-world scenarios, or freeze when asked to write queries on the spot.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>Most Asked DBMS Topics (What Interviewers Actually Test)<\/strong><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Normalization & Functional Dependencies<\/strong><\/h3>\n\n\n\nThis is one of the most commonly asked areas.<\/p>\n\n\n\nWhat interviewers look for is your ability to design clean schemas and understand redundancy. You should be able to normalize a table step-by-step from 1NF to BCNF, identify functional dependencies, and explain anomalies clearly.<\/p>\n\n\n\nFor example, if given a table with StudentID, Course, Instructor, and InstructorPhone, you should recognize that Instructor determines InstructorPhone. This creates a transitive dependency, which means the table should be split into separate relations.<\/p>\n\n\n\nA strong answer always includes reasoning, not just definitions. Also, remember that normalization is not always ideal in real systems, denormalization is often used to improve performance.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">ACID Properties & Transactions<\/strong><\/h3>\n\n\n\nThis topic checks whether you understand how databases maintain reliability.<\/p>\n\n\n\nYou should explain each property with real-world context. Atomicity means a transaction either fully completes or rolls back. Consistency ensures the database remains valid before and after a transaction. Isolation ensures concurrent transactions do not interfere with each other. Durability guarantees that once a transaction is committed, it is permanently stored.<\/p>\n\n\n\nStrong answers include examples like banking systems and mention mechanisms like write-ahead logging.<\/p>\n\n\n\nA common follow-up is about what happens when isolation is weak. You should be able to discuss issues like dirty reads and phantom reads.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Indexing & Query Optimization<\/strong><\/h3>\n\n\n\nThis is where interviewers distinguish strong candidates.<\/p>\n\n\n\nIndexes improve query performance by avoiding full table scans. However, they come with trade-offs such as slower write operations.<\/p>\n\n\n\nYou should understand index types like B-tree and hash, and more importantly, when indexing fails. For instance, indexes are less useful when there is low selectivity or when queries are written in a way that prevents index usage.<\/p>\n\n\n\nYou should also be comfortable explaining why a query is slow even when an index exists.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Deadlocks & Concurrency Control<\/strong><\/h3>\n\n\n\nThis topic focuses on how databases handle multiple users.<\/p>\n\n\n\nA deadlock occurs when two transactions are waiting on each other indefinitely. You should be able to explain how deadlocks happen, the conditions required for them, and how to prevent or resolve them.<\/p>\n\n\n\nYou should also understand isolation levels such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level represents a trade-off between performance and data consistency.<\/p>\n\n\n\nInterviewers often ask scenario-based questions here, so clarity is important.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">SQL Queries (Hands-On Round)<\/strong><\/h3>\n\n\n\nThis is where many candidates lose marks.<\/p>\n\n\n\nYou will be asked to write queries involving joins, aggregations, subqueries, and window functions. It\u2019s important not just to get the answer, but to write clean and efficient queries.<\/p>\n\n\n\nYou should be comfortable solving problems like finding users who never placed orders, calculating aggregate values, or retrieving ranked results using window functions.<\/p>\n\n\n\nA strong candidate can solve a problem using multiple approaches and explain the trade-offs.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>Preparation Strategy (What Actually Works)<\/strong><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Revise Theory the Right Way<\/strong><\/h3>\n\n\n\nAvoid memorizing definitions. Focus on understanding concepts deeply and explaining them in your own words. Use real-world examples and try teaching the concept to someone else. This improves clarity and retention.<\/p>\n\n\n\nPractice SQL Daily<\/strong><\/p>\n\n\n\nConsistency is key. Spend time every day writing queries and solving problems. Focus on joins, aggregations, subqueries, and window functions. Gradually increase the difficulty level.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Solve Interview Questions<\/strong><\/h3>\n\n\n\nPractice common patterns such as normalization problems, transaction scenarios, and query optimization. Over time, you\u2019ll start recognizing patterns quickly during interviews.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Mock Interviews<\/strong><\/h3>\n\n\n\nMock interviews are one of the most effective ways to prepare. They help you improve communication, handle pressure, and identify weak areas. After each mock, review your performance and refine your answers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>Final Insight<\/strong><\/span><\/h2>\n\n\n\nAt a higher level, interviewers are not just testing your knowledge of DBMS. They are evaluating whether you can think like a backend engineer.<\/p>\n\n\n\nIf you can write efficient SQL queries, explain concepts clearly, and apply them to real-world scenarios, you will stand out.<\/p>\n\n\n\n Strengthen your fundamentals with: <a href=\"https:\/\/www.scaler.com\/topics\/dsa-roadmap\/\">https:\/\/www.scaler.com\/topics\/dsa-roadmap\/<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>FAQs<\/strong><\/span><\/h2>\n\n\n\nQ1: How long does it take to learn DBMS?<\/strong> <\/strong>It typically takes around 3 to 4 months to learn DBMS properly if you follow a structured roadmap like this one. Spending 1\u20132 hours daily is enough to cover both theory and practical SQL, along with revision and interview preparation.<\/p>\n\n\n\nQ2: Is DBMS asked in coding interviews?<\/strong> <\/strong>Yes, DBMS is frequently asked in SDE interviews, especially in product-based companies. While coding rounds focus on DSA, DBMS is heavily tested in technical interviews through conceptual and scenario-based questions.<\/p>\n\n\n\nQ3: What is the difference between DBMS and RDBMS?<\/strong> <\/strong>DBMS refers to any system that manages databases, while RDBMS specifically uses relational tables with structured schemas and supports SQL. RDBMS also enforces constraints and relationships between data.<\/p>\n\n\n\nQ4: Should I learn SQL before DBMS?<\/strong> <\/strong>You can start DBMS and SQL together, but understanding basic DBMS concepts first makes SQL easier to learn. DBMS provides the theory, while SQL helps you apply it practically.<\/p>\n\n\n\nQ5: What are the most important DBMS topics for placements?<\/strong> <\/strong>The most important topics include normalization, ACID properties, transactions, indexing, deadlocks, SQL queries, and query optimization. These are commonly asked in interviews and are essential for backend development roles.<\/p>\n","protected":false},"excerpt":{"rendered":"Introduction DBMS is one of the most important core subjects for computer science students and is consistently asked in product company interviews. Whether you\u2019re preparing for placements or aiming for backend roles, a strong understanding of databases is non-negotiable. This roadmap gives you a clear, step-by-step path: from ER modeling and normalization to SQL mastery, […]<\/p>\n","protected":false},"author":210,"featured_media":12502,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[37,316],"tags":[],"class_list":{"0":"post-12474","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-artificial-intelligence-machine-learning","8":"category-artificial-intelligence"},"acf":[],"_links":{"self":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts\/12474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/users\/210"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/comments?post=12474"}],"version-history":[{"count":2,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts\/12474\/revisions"}],"predecessor-version":[{"id":12501,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts\/12474\/revisions\/12501"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/media\/12502"}],"wp:attachment":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/media?parent=12474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/categories?post=12474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/tags?post=12474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

{"id":12474,"date":"2026-05-05T18:42:44","date_gmt":"2026-05-05T13:12:44","guid":{"rendered":"https:\/\/www.scaler.com\/blog\/?p=12474"},"modified":"2026-05-05T18:43:06","modified_gmt":"2026-05-05T13:13:06","slug":"dbms-roadmap-2026-complete-learning-path-for-beginners-to-advanced","status":"publish","type":"post","link":"https:\/\/www.scaler.com\/blog\/dbms-roadmap-2026-complete-learning-path-for-beginners-to-advanced\/","title":{"rendered":"DBMS Roadmap 2026: Complete Learning Path for Beginners to Advanced"},"content":{"rendered":"\n

Introduction<\/strong><\/h3>\n\n\n\n
DBMS is one of the most important core subjects for computer science students and is consistently asked in product company interviews. Whether you\u2019re preparing for placements or aiming for backend roles, a strong understanding of databases is non-negotiable.<\/p>\n\n\n\n
This roadmap gives you a clear, step-by-step path: from ER modeling and normalization to SQL mastery, transactions, ACID properties, indexing, query optimization, and NoSQL systems. It\u2019s designed as a 16-week structured plan so you know exactly what to learn and when.<\/p>\n\n\n\n
One important distinction: DBMS is broader than SQL. DBMS focuses on concepts like database design, normalization, and transactions, while SQL is the tool used to interact with databases. If SQL is \u201chow you query,\u201d DBMS is \u201chow systems are designed and managed.\u201d<\/p>\n\n\n\n
<\/span>Why Learn DBMS? Career & Interview Relevance<\/strong><\/span><\/h2>\n\n\n\n
DBMS is not just another subject it\u2019s one of the core pillars of SDE interviews, alongside Data Structures and Operating Systems. In most product company interviews, you won\u2019t get away with surface-level knowledge. Interviewers expect you to explain concepts clearly and apply them to real scenarios.<\/p>\n\n\n\n
You\u2019ll regularly face questions like:<\/p>\n\n\n\n
\n
What is normalization and why is it needed?<\/li>\n\n\n\n
Explain ACID properties with real-world examples<\/li>\n\n\n\n
How does indexing improve performance?<\/li>\n\n\n\n
What causes a deadlock and how do you prevent it?<\/li>\n<\/ul>\n\n\n\n
These aren\u2019t theoretical questions, they test whether you understand how systems behave in production.<\/p>\n\n\n\n
For backend developers, DBMS becomes even more important. Every application, whether it\u2019s an e-commerce platform, banking system, or social media app, depends on how well the database is designed. A poorly designed database leads to slow queries, inconsistent data, and scaling issues.<\/p>\n\n\n\n
Learning DBMS helps you:<\/p>\n\n\n\n
\n
Crack technical interviews with strong conceptual clarity<\/li>\n\n\n\n
Design scalable systems that handle millions of users<\/li>\n\n\n\n
Optimize performance using indexing and query tuning<\/li>\n\n\n\n
Understand real-world systems, how data is stored, accessed, and maintained<\/li>\n<\/ul>\n\n\n\n
If DSA helps you solve problems, DBMS helps you build systems that actually work at scale.<\/p>\n\n\n\n
Stop learning AI in fragments\u2014master a structured AI Engineering Course<\/a> with hands-on GenAI systems with IIT Roorkee CEC Certification<\/strong><\/p>\n\n\n\n\n\n \n Hello World!<\/title>\n <link rel=\"preconnect\" href=\"https:\/\/fonts.googleapis.com\">\n <link rel=\"preconnect\" href=\"https:\/\/fonts.gstatic.com\" crossorigin>\n <link href=\"https:\/\/fonts.googleapis.com\/css2?family=Lato:wght@400;600;700&display=swap\" rel=\"stylesheet\">\n <style>\n .iitr_banner_container {\n font-family: lato;\n display: flex;\n flex-direction: row;\n justify-content: space-between;\n border-radius: 16px;\n background: linear-gradient(88deg, #19000F 24.45%, #66003F 83.33%);\n position: relative;\n\n @media (max-width: 768px) {\n min-height: 450px;\n overflow: hidden;\n flex-direction: column;\n }\n }\n .iitr_banner_content {\n display: flex;\n flex-direction: column;\n align-items: flex-start;\n justify-content: center;\n padding: 20px;\n max-width: 50%;\n\n @media (max-width: 768px) {\n max-width: 100%;\n }\n }\n .iitr_banner_title {\n font-size: 24px;\n font-weight: bold;\n color: #FFFFFF;\n\n @media (max-width: 768px) {\n font-size: 20px;\n }\n }\n .iitr_banner_title_highlight {\n color: #FF0071;\n }\n .iitr_banner_subtitle {\n font-size: 14px;\n color: #FFFFFF;\n margin: 10px 0;\n }\n .iitr_banner_btn {\n display: flex;\n justify-content: center;\n align-items: center;\n padding: 8px 48px;\n background-color: #F8F9F9;\n border-radius: 8px;\n border: 1px solid #E3E8E8;\n font-size: 1.4rem;\n font-weight: 600;\n color: #0D3231;\n text-decoration: none;\n margin-top: 16px;\n\n @media (max-width: 768px) {\n padding: 8px 32px;\n }\n }\n .iitr_banner_image {\n position: absolute;\n bottom: 0;\n right: 0;\n\n @media (max-width: 768px) {\n right: auto;\n object-fit: cover;\n min-width: 100%\n }\n }\n .iitr_banner_image_logo {\n margin-bottom: 16px;\n \n @media (max-width: 768px) {\n width: 240px;\n }\n }\n\n \/* Responsive visibility utilities \/\n .show-in-mobile {\n display: none;\n }\n .hide-in-mobile {\n display: block;\n }\n\n \/ Mobile breakpoint (768px and below) \/\n @media (max-width: 768px) {\n .show-in-mobile {\n display: block;\n }\n .hide-in-mobile {\n display: none;\n }\n }\n <\/style>\n <\/head>\n <body>\n <div class=\"iitr_banner_container\">\n <div class=\"iitr_banner_content\">\n <img decoding=\"async\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/281\/original\/Frame_1430102419.svg?1769058073\" class=\"iitr_banner_image_logo\" \/>\n <div class=\"iitr_banner_title\">\n AI Engineering Course Advanced Certification by \n \n IIT-Roorkee CEC\n <\/span>\n <\/div>\n <div class=\"iitr_banner_subtitle\">\n A hands on AI engineering program covering Machine Learning, Generative AI, and LLMs – designed for working professionals & delivered by IIT Roorkee in collaboration with Scaler.\n <\/div>\n <a class=\"iitr_banner_btn\" href=\"#\" id=\"iitr_banner_btn\">Enrol Now<\/a>\n <\/div>\n \n <img decoding=\"async\" class=\"iitr_banner_image hide-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/282\/original\/iitr_2.svg?1769058132\" \/>\n \n <img decoding=\"async\" class=\"iitr_banner_image show-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/283\/original\/iitr_2_%281%29.svg?1769059469\" \/>\n <\/div>\n <script>\n document.addEventListener(\"DOMContentLoaded\", () => {\n const pathParts = location.pathname.split(\"\/\").filter(Boolean);\n const currentSlug = pathParts.length > 0 ? pathParts[pathParts.length - 1] : \"homepage\";\n const url = `https:\/\/www.scaler.com\/iit-roorkee-advanced-ai-engineering-course?utm_source=blog&utm_medium=iit_roorkee&utm_content=${currentSlug}`;\n const btns = document.querySelectorAll(\".iitr_banner_btn\");\n btns.forEach(btn => {\n btn.href = url;\n });\n });\n <\/script>\n <\/body>\n<\/html>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 1 , Fundamentals (Weeks 1\u20134)<\/strong><\/span><\/h2>\n\n\n\nThis phase builds your foundation. Most students rush into SQL, but without understanding how databases are designed, SQL becomes mechanical. These 4 weeks focus on how data is structured and why.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What Is DBMS? Types of Databases<\/strong><\/h3>\n\n\n\nA Database Management System (DBMS) is software that allows you to store, organize, retrieve, and manage data efficiently. Instead of working with raw files, DBMS provides structured ways to interact with data using queries.<\/p>\n\n\n\nAt a deeper level, a DBMS handles:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data storage and retrieval<\/li>\n\n\n\n<li>Security and access control<\/li>\n\n\n\n<li>Concurrency (multiple users accessing data)<\/li>\n\n\n\n<li>Data integrity and consistency<\/li>\n<\/ul>\n\n\n\nYou should understand different types of databases and when to use them:<\/p>\n\n\n\nRelational Databases (RDBMS)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Examples: MySQL, PostgreSQL<\/li>\n\n\n\n<li>Data is stored in tables (rows and columns)<\/li>\n\n\n\n<li>Follows strict schema<\/li>\n\n\n\n<li>Uses SQL for querying<\/li>\n\n\n\n<li>Best for structured data and consistency<\/li>\n<\/ul>\n\n\n\nNoSQL Databases<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Examples: MongoDB, Redis<\/li>\n\n\n\n<li>Flexible schema (or schema-less)<\/li>\n\n\n\n<li>Types: document, key-value, column-family<\/li>\n\n\n\n<li>Designed for scalability and high performance<\/li>\n\n\n\n<li>Used in large-scale systems like social media<\/li>\n<\/ul>\n\n\n\nDistributed Databases<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data is spread across multiple machines<\/li>\n\n\n\n<li>Improves availability and fault tolerance<\/li>\n\n\n\n<li>Used in large-scale systems (e.g., cloud platforms)<\/li>\n<\/ul>\n\n\n\nFocus on understanding how databases store, retrieve, and manage data internally, not just definitions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">ER Modeling & Schema Design<\/strong><\/h3>\n\n\n\nBefore writing SQL, you must learn how to design a database properly. This is where beginners often struggle, but it\u2019s one of the most important skills.<\/p>\n\n\n\nER (Entity-Relationship) modeling helps you visualize the structure of your database.<\/p>\n\n\n\nKey components:<\/p>\n\n\n\nEntities<\/strong> <\/strong> These are real-world objects or concepts. Example: Student, Course, Order<\/p>\n\n\n\nAttributes<\/strong> <\/strong> Properties of entities. Example: Student \u2192 name, id, age<\/p>\n\n\n\nRelationships<\/strong> <\/strong> How entities are connected. Example: A student enrolls in a course<\/p>\n\n\n\nCardinality<\/strong> <\/strong> Defines the relationship type:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>One-to-One<\/li>\n\n\n\n<li>One-to-Many<\/li>\n\n\n\n<li>Many-to-Many<\/li>\n<\/ul>\n\n\n\nConstraints<\/strong> <\/strong> Rules applied to data:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Primary Key (unique identifier)<\/li>\n\n\n\n<li>Foreign Key (link between tables)<\/li>\n\n\n\n<li>NOT NULL, UNIQUE<\/li>\n<\/ul>\n\n\n\nThe most important skill here is converting ER diagrams into relational schemas (tables). For example, a many-to-many relationship is converted into a separate table with foreign keys.<\/p>\n\n\n\nThis step ensures your database is logically correct before implementation.<\/p>\n\n\n\n<!DOCTYPE html>\n<html>\n <head>\n <title>Hello World!<\/title>\n <link rel=\"preconnect\" href=\"https:\/\/fonts.googleapis.com\">\n <link rel=\"preconnect\" href=\"https:\/\/fonts.gstatic.com\" crossorigin>\n <link href=\"https:\/\/fonts.googleapis.com\/css2?family=Lato:wght@400;600;700&display=swap\" rel=\"stylesheet\">\n <style>\n .iitr_banner_container {\n font-family: lato;\n display: flex;\n flex-direction: row;\n justify-content: space-between;\n border-radius: 16px;\n background: linear-gradient(88deg, #19000F 24.45%, #66003F 83.33%);\n position: relative;\n\n @media (max-width: 768px) {\n min-height: 450px;\n overflow: hidden;\n flex-direction: column;\n }\n }\n .iitr_banner_content {\n display: flex;\n flex-direction: column;\n align-items: flex-start;\n justify-content: center;\n padding: 20px;\n max-width: 50%;\n\n @media (max-width: 768px) {\n max-width: 100%;\n }\n }\n .iitr_banner_title {\n font-size: 24px;\n font-weight: bold;\n color: #FFFFFF;\n\n @media (max-width: 768px) {\n font-size: 20px;\n }\n }\n .iitr_banner_title_highlight {\n color: #FF0071;\n }\n .iitr_banner_subtitle {\n font-size: 14px;\n color: #FFFFFF;\n margin: 10px 0;\n }\n .iitr_banner_btn {\n display: flex;\n justify-content: center;\n align-items: center;\n padding: 8px 48px;\n background-color: #F8F9F9;\n border-radius: 8px;\n border: 1px solid #E3E8E8;\n font-size: 1.4rem;\n font-weight: 600;\n color: #0D3231;\n text-decoration: none;\n margin-top: 16px;\n\n @media (max-width: 768px) {\n padding: 8px 32px;\n }\n }\n .iitr_banner_image {\n position: absolute;\n bottom: 0;\n right: 0;\n\n @media (max-width: 768px) {\n right: auto;\n object-fit: cover;\n min-width: 100%\n }\n }\n .iitr_banner_image_logo {\n margin-bottom: 16px;\n \n @media (max-width: 768px) {\n width: 240px;\n }\n }\n\n \/ Responsive visibility utilities \/\n .show-in-mobile {\n display: none;\n }\n .hide-in-mobile {\n display: block;\n }\n\n \/ Mobile breakpoint (768px and below) \/\n @media (max-width: 768px) {\n .show-in-mobile {\n display: block;\n }\n .hide-in-mobile {\n display: none;\n }\n }\n <\/style>\n <\/head>\n <body>\n <div class=\"iitr_banner_container\">\n <div class=\"iitr_banner_content\">\n <img decoding=\"async\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/281\/original\/Frame_1430102419.svg?1769058073\" class=\"iitr_banner_image_logo\" \/>\n <div class=\"iitr_banner_title\">\n AI Engineering Course Advanced Certification by \n \n IIT-Roorkee CEC\n <\/span>\n <\/div>\n <div class=\"iitr_banner_subtitle\">\n A hands on AI engineering program covering Machine Learning, Generative AI, and LLMs – designed for working professionals & delivered by IIT Roorkee in collaboration with Scaler.\n <\/div>\n <a class=\"iitr_banner_btn\" href=\"#\" id=\"iitr_banner_btn\">Enrol Now<\/a>\n <\/div>\n \n <img decoding=\"async\" class=\"iitr_banner_image hide-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/282\/original\/iitr_2.svg?1769058132\" \/>\n \n <img decoding=\"async\" class=\"iitr_banner_image show-in-mobile\" src=\"https:\/\/d2beiqkhq929f0.cloudfront.net\/public_assets\/assets\/000\/176\/283\/original\/iitr_2_%281%29.svg?1769059469\" \/>\n <\/div>\n <script>\n document.addEventListener(\"DOMContentLoaded\", () => {\n const pathParts = location.pathname.split(\"\/\").filter(Boolean);\n const currentSlug = pathParts.length > 0 ? pathParts[pathParts.length - 1] : \"homepage\";\n const url = `https:\/\/www.scaler.com\/iit-roorkee-advanced-ai-engineering-course?utm_source=blog&utm_medium=iit_roorkee&utm_content=${currentSlug}`;\n const btns = document.querySelectorAll(\".iitr_banner_btn\");\n btns.forEach(btn => {\n btn.href = url;\n });\n });\n <\/script>\n <\/body>\n<\/html>\n\n\n\n<h3 class=\"wp-block-heading\">Relational Model & Relational Algebra<\/strong><\/h3>\n\n\n\nOnce your schema is ready, you need to understand how relational databases actually work behind the scenes.<\/p>\n\n\n\nRelational Model<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data is stored in tables (relations)<\/li>\n\n\n\n<li>Each row is a tuple<\/li>\n\n\n\n<li>Each column is an attribute<\/li>\n\n\n\n<li>Each table has a primary key<\/li>\n<\/ul>\n\n\n\nThis model ensures structure and consistency.<\/p>\n\n\n\nRelational Algebra<\/strong> <\/strong> This is the theoretical foundation of SQL. It defines operations used to manipulate data.<\/p>\n\n\n\nKey operations:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SELECT \u2192 choose rows<\/li>\n\n\n\n<li>PROJECT \u2192 choose columns<\/li>\n\n\n\n<li>JOIN \u2192 combine tables<\/li>\n\n\n\n<li>UNION \u2192 merge results<\/li>\n\n\n\n<li>DIFFERENCE \u2192 find missing data<\/li>\n<\/ul>\n\n\n\nUnderstanding relational algebra helps you see how queries are executed internally.<\/p>\n\n\n\nHow Queries Are Processed Conceptually<\/strong><\/p>\n\n\n\nWhen you write a SQL query, the DBMS doesn\u2019t execute it exactly as written. Instead, it:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Converts SQL into relational algebra<\/li>\n\n\n\n<li>Optimizes the query (chooses the fastest way)<\/li>\n\n\n\n<li>Executes it using indexes and execution plans<\/li>\n<\/ul>\n\n\n\nThis is why the same query can have different performance depending on how it\u2019s written.<\/p>\n\n\n\nMastering this phase gives you a strong foundation for advanced topics like normalization, indexing, and query optimization later on.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 2 , SQL Mastery (Weeks 5\u20138)<\/strong><\/span><\/h2>\n\n\n\nThis is where things become practical. You move from \u201cunderstanding databases\u201d to actually working with them like a backend developer<\/strong>. The goal here isn\u2019t just to write queries, it\u2019s to write efficient, readable, and optimized queries<\/strong>.<\/p>\n\n\n\nFor broader backend context: <a href=\"https:\/\/www.scaler.com\/topics\/software-engineering\/backend-developer-roadmap\/\">https:\/\/www.scaler.com\/topics\/software-engineering\/backend-developer-roadmap\/<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">SQL Basics: SELECT, JOIN, GROUP BY, Subqueries<\/strong><\/h3>\n\n\n\nThis is your foundation. But instead of just learning syntax, focus on how queries transform data step by step<\/strong>.<\/p>\n\n\n\nSELECT, WHERE, ORDER BY<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SELECT decides what data you want<\/em><\/li>\n\n\n\n<li>WHERE filters rows before processing<\/li>\n\n\n\n<li>ORDER BY sorts the final result<\/li>\n<\/ul>\n\n\n\nKey insight: SQL execution is not top-to-bottom as written. The actual order is: FROM \u2192 WHERE \u2192 GROUP BY \u2192 HAVING \u2192 SELECT \u2192 ORDER BY<\/strong><\/p>\n\n\n\nUnderstanding this helps you debug and optimize queries.<\/p>\n\n\n\nJOINs (INNER, LEFT, RIGHT)<\/strong> <\/strong>JOINs are one of the most important topics for interviews.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>INNER JOIN<\/strong> \u2192 returns matching records from both tables<\/li>\n\n\n\n<li>LEFT JOIN<\/strong> \u2192 keeps all records from the left table<\/li>\n\n\n\n<li>RIGHT JOIN<\/strong> \u2192 keeps all records from the right table<\/li>\n<\/ul>\n\n\n\nDeep understanding:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>JOINs internally use algorithms like nested loop join, hash join, or merge join<\/strong><\/li>\n\n\n\n<li>Performance depends on indexing and table size<\/li>\n<\/ul>\n\n\n\nGROUP BY & Aggregation<\/strong> <\/strong>Used to summarize data:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>COUNT(), SUM(), AVG(), MAX(), MIN()<\/li>\n<\/ul>\n\n\n\nImportant concept:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>WHERE filters rows before grouping<\/strong><\/li>\n\n\n\n<li>HAVING filters after grouping<\/strong><\/li>\n<\/ul>\n\n\n\nSubqueries<\/strong> <\/strong>Queries inside queries.<\/p>\n\n\n\nTypes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Scalar subqueries (return single value)<\/li>\n\n\n\n<li>Correlated subqueries (run per row \u2192 slower)<\/li>\n<\/ul>\n\n\n\nInterview tip:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Often replace subqueries with JOINs for better performance<\/li>\n<\/ul>\n\n\n\nPractice here should be heavy. Solve real datasets, this is where most learning happens.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Advanced SQL: Window Functions, CTEs, Stored Procedures<\/strong><\/h3>\n\n\n\nThis is where you move from \u201caverage\u201d to interview-ready<\/strong>.<\/p>\n\n\n\nWindow Functions<\/strong> <\/strong>These allow you to perform calculations across rows without collapsing them<\/strong>.<\/p>\n\n\n\nExamples:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ROW_NUMBER() \u2192 unique ranking<\/li>\n\n\n\n<li>RANK() \u2192 handles ties<\/li>\n\n\n\n<li>DENSE_RANK() \u2192 no gaps in ranking<\/li>\n<\/ul>\n\n\n\nUse cases:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Top N per group<\/li>\n\n\n\n<li>Running totals<\/li>\n\n\n\n<li>Ranking users or products<\/li>\n<\/ul>\n\n\n\nKey idea: Window functions use an OVER() clause<\/strong> with PARTITION and ORDER.<\/p>\n\n\n\nCommon Table Expressions (CTEs)<\/strong> <\/strong>Temporary result sets using WITH.<\/p>\n\n\n\nWhy they matter:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Make complex queries readable<\/li>\n\n\n\n<li>Help break down logic step-by-step<\/li>\n\n\n\n<li>Used in recursive queries (hierarchies like org charts)<\/li>\n<\/ul>\n\n\n\nStored Procedures<\/strong> <\/strong>Predefined SQL logic stored in the database.<\/p>\n\n\n\nWhy used:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Reusability<\/li>\n\n\n\n<li>Performance (precompiled)<\/li>\n\n\n\n<li>Security (controlled execution)<\/li>\n<\/ul>\n\n\n\nBut in modern systems, many companies shift logic to application code instead.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Views, Triggers & Indexing<\/strong><\/h3>\n\n\n\nThis section connects SQL to real-world database behavior<\/strong>.<\/p>\n\n\n\nViews<\/strong> <\/strong>Virtual tables created from queries.<\/p>\n\n\n\nWhy use them:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Simplify complex queries<\/li>\n\n\n\n<li>Provide abstraction<\/li>\n\n\n\n<li>Restrict access to sensitive data<\/li>\n<\/ul>\n\n\n\nTypes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Simple views<\/li>\n\n\n\n<li>Materialized views (physically stored \u2192 faster reads)<\/li>\n<\/ul>\n\n\n\nTriggers<\/strong> <\/strong>Automatically execute when events happen (INSERT, UPDATE, DELETE).<\/p>\n\n\n\nUse cases:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Audit logs<\/li>\n\n\n\n<li>Data validation<\/li>\n\n\n\n<li>Maintaining derived data<\/li>\n<\/ul>\n\n\n\nBe careful:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Overuse can make systems hard to debug<\/li>\n<\/ul>\n\n\n\nIndexing (B-tree, Hash)<\/strong><\/p>\n\n\n\nThis is one of the most important performance concepts<\/strong>.<\/p>\n\n\n\nWithout index:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Full table scan \u2192 slow<\/li>\n<\/ul>\n\n\n\nWith index:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Faster lookup (like a book index)<\/li>\n<\/ul>\n\n\n\nTypes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>B-tree index<\/strong> \u2192 balanced, supports range queries<\/li>\n\n\n\n<li>Hash index<\/strong> \u2192 fast equality lookup<\/li>\n<\/ul>\n\n\n\nAdvanced insights:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Indexes improve read speed but slow down writes<\/strong><\/li>\n\n\n\n<li>Too many indexes = bad performance<\/li>\n\n\n\n<li>Composite indexes depend on column order<\/li>\n<\/ul>\n\n\n\nInterview gold question: \u201cWhy is my query slow even with an index?\u201d \u2192 Because of wrong index usage, low selectivity, or poor query structure.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 3 , Core Theory (Weeks 9\u201312)<\/strong><\/span><\/h2>\n\n\n\nThis phase is where most interview questions come from<\/strong>. You now connect theory with real systems.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Normalization: 1NF to BCNF<\/strong><\/h3>\n\n\n\nNormalization reduces redundancy and prevents anomalies.<\/p>\n\n\n\n1NF (First Normal Form)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>No repeating groups<\/li>\n\n\n\n<li>Atomic values<\/li>\n<\/ul>\n\n\n\n2NF<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Remove partial dependency (non-key depends on part of key)<\/li>\n<\/ul>\n\n\n\n3NF<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Remove transitive dependency (non-key depends on another non-key)<\/li>\n<\/ul>\n\n\n\nBCNF<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stronger version of 3NF (every determinant must be a candidate key)<\/li>\n<\/ul>\n\n\n\nFunctional Dependencies (FDs)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Define relationships between attributes Example: StudentID \u2192 Name<\/li>\n<\/ul>\n\n\n\nAnomalies<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Insertion anomaly \u2192 can\u2019t insert data without other data<\/li>\n\n\n\n<li>Deletion anomaly \u2192 losing important info<\/li>\n\n\n\n<li>Update anomaly \u2192 inconsistency<\/li>\n<\/ul>\n\n\n\nReal insight:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Over-normalization can hurt performance \u2192 denormalization is used in real systems<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">ACID Properties & Transactions<\/strong><\/h3>\n\n\n\nTransactions ensure reliable database operations, especially in critical systems like banking.<\/p>\n\n\n\nAtomicity<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>All or nothing<\/li>\n\n\n\n<li>If one step fails, everything rolls back<\/li>\n<\/ul>\n\n\n\nConsistency<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database remains valid before and after transaction<\/li>\n<\/ul>\n\n\n\nIsolation<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Transactions don\u2019t interfere with each other<\/li>\n<\/ul>\n\n\n\nDurability<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Once committed, data is permanent<\/li>\n<\/ul>\n\n\n\nReal-world example: Money transfer \u2192 both debit and credit must succeed or fail together.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Concurrency Control & Locking<\/strong><\/h3>\n\n\n\nWhen multiple users access the database, conflicts occur.<\/p>\n\n\n\nLocks<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Shared lock \u2192 read<\/li>\n\n\n\n<li>Exclusive lock \u2192 write<\/li>\n<\/ul>\n\n\n\nDeadlocks<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Two transactions waiting on each other<\/li>\n<\/ul>\n\n\n\nPrevention techniques:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Lock ordering<\/li>\n\n\n\n<li>Timeout<\/li>\n\n\n\n<li>Deadlock detection algorithms<\/li>\n<\/ul>\n\n\n\nIsolation Levels<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Read Uncommitted<\/li>\n\n\n\n<li>Read Committed<\/li>\n\n\n\n<li>Repeatable Read<\/li>\n\n\n\n<li>Serializable<\/li>\n<\/ul>\n\n\n\nEach level balances consistency vs performance<\/strong>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Query Optimization & Execution Plans<\/strong><\/h3>\n\n\n\nThis is where DBMS becomes powerful.<\/p>\n\n\n\nWhen you write a query:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>DBMS converts it into multiple possible execution plans<\/li>\n\n\n\n<li>Chooses the cheapest one (cost-based optimization)<\/li>\n<\/ul>\n\n\n\nExecution Plans Show:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Table scans vs index scans<\/li>\n\n\n\n<li>Join methods<\/li>\n\n\n\n<li>Estimated cost<\/li>\n<\/ul>\n\n\n\nKey optimization techniques:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Proper indexing<\/li>\n\n\n\n<li>Avoid SELECT <\/li>\n\n\n\n<li>Use JOIN instead of subqueries (in many cases)<\/li>\n\n\n\n<li>Filter early (WHERE clause)<\/li>\n<\/ul>\n\n\n\nThis is what separates average developers from strong backend engineers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Roadmap: Phase 4 , NoSQL & Beyond (Weeks 13\u201316)<\/strong><\/span><\/h2>\n\n\n\nNow you step into modern, scalable systems.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">NoSQL Databases: MongoDB, Cassandra, Redis<\/strong><\/h3>\n\n\n\nEach type solves a specific problem.<\/p>\n\n\n\nDocument Stores (MongoDB)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>JSON-like structure<\/li>\n\n\n\n<li>Flexible schema<\/li>\n\n\n\n<li>Good for rapidly changing data<\/li>\n<\/ul>\n\n\n\nWide-Column Stores (Cassandra)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Designed for massive scale<\/li>\n\n\n\n<li>High write throughput<\/li>\n\n\n\n<li>Used in distributed systems<\/li>\n<\/ul>\n\n\n\nKey-Value Stores (Redis)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Extremely fast (in-memory)<\/li>\n\n\n\n<li>Used for caching, sessions<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">SQL vs NoSQL: When to Use What<\/strong><\/h3>\n\n\n\nThis is a common system design question.<\/p>\n\n\n\nUse SQL when:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Strong consistency needed<\/li>\n\n\n\n<li>Structured data<\/li>\n\n\n\n<li>Complex queries<\/li>\n<\/ul>\n\n\n\nUse NoSQL when:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>High scalability needed<\/li>\n\n\n\n<li>Flexible schema<\/li>\n\n\n\n<li>Large distributed systems<\/li>\n<\/ul>\n\n\n\nReal-world systems often use both together.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Database Design for Real-World Applications<\/strong><\/h3>\n\n\n\nThis is where everything comes together.<\/p>\n\n\n\nYou should be able to design systems like:<\/p>\n\n\n\nE-commerce app:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Users, Products, Orders<\/li>\n\n\n\n<li>Relationships and indexing<\/li>\n\n\n\n<li>Handling transactions<\/li>\n<\/ul>\n\n\n\nSocial media app:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Posts, Likes, Comments<\/li>\n\n\n\n<li>High read\/write scaling<\/li>\n<\/ul>\n\n\n\nScaling Concepts:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Sharding<\/strong> – split data across servers<\/li>\n\n\n\n<li>Replication<\/strong> – copy data for availability<\/li>\n<\/ul>\n\n\n\n\ud83d\udc49 Explore system design: <a href=\"https:\/\/www.scaler.com\/topics\/system-design\/\">https:\/\/www.scaler.com\/topics\/system-design\/<\/a><\/p>\n\n\n\nThe goal at this stage is not just knowing concepts, but being able to design systems that handle real-world scale, performance, and reliability.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>DBMS Interview Questions & Preparation (In-Depth Guide)<\/strong><\/span><\/h2>\n\n\n\nThis is the phase where your learning turns into selection-level preparation. At this point, it\u2019s not about knowing topics, it\u2019s about being able to explain, apply, and defend your answers under pressure.<\/p>\n\n\n\nMost candidates struggle not because they don\u2019t know DBMS, but because they give shallow answers, can\u2019t connect theory to real-world scenarios, or freeze when asked to write queries on the spot.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>Most Asked DBMS Topics (What Interviewers Actually Test)<\/strong><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Normalization & Functional Dependencies<\/strong><\/h3>\n\n\n\nThis is one of the most commonly asked areas.<\/p>\n\n\n\nWhat interviewers look for is your ability to design clean schemas and understand redundancy. You should be able to normalize a table step-by-step from 1NF to BCNF, identify functional dependencies, and explain anomalies clearly.<\/p>\n\n\n\nFor example, if given a table with StudentID, Course, Instructor, and InstructorPhone, you should recognize that Instructor determines InstructorPhone. This creates a transitive dependency, which means the table should be split into separate relations.<\/p>\n\n\n\nA strong answer always includes reasoning, not just definitions. Also, remember that normalization is not always ideal in real systems, denormalization is often used to improve performance.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">ACID Properties & Transactions<\/strong><\/h3>\n\n\n\nThis topic checks whether you understand how databases maintain reliability.<\/p>\n\n\n\nYou should explain each property with real-world context. Atomicity means a transaction either fully completes or rolls back. Consistency ensures the database remains valid before and after a transaction. Isolation ensures concurrent transactions do not interfere with each other. Durability guarantees that once a transaction is committed, it is permanently stored.<\/p>\n\n\n\nStrong answers include examples like banking systems and mention mechanisms like write-ahead logging.<\/p>\n\n\n\nA common follow-up is about what happens when isolation is weak. You should be able to discuss issues like dirty reads and phantom reads.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Indexing & Query Optimization<\/strong><\/h3>\n\n\n\nThis is where interviewers distinguish strong candidates.<\/p>\n\n\n\nIndexes improve query performance by avoiding full table scans. However, they come with trade-offs such as slower write operations.<\/p>\n\n\n\nYou should understand index types like B-tree and hash, and more importantly, when indexing fails. For instance, indexes are less useful when there is low selectivity or when queries are written in a way that prevents index usage.<\/p>\n\n\n\nYou should also be comfortable explaining why a query is slow even when an index exists.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Deadlocks & Concurrency Control<\/strong><\/h3>\n\n\n\nThis topic focuses on how databases handle multiple users.<\/p>\n\n\n\nA deadlock occurs when two transactions are waiting on each other indefinitely. You should be able to explain how deadlocks happen, the conditions required for them, and how to prevent or resolve them.<\/p>\n\n\n\nYou should also understand isolation levels such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each level represents a trade-off between performance and data consistency.<\/p>\n\n\n\nInterviewers often ask scenario-based questions here, so clarity is important.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">SQL Queries (Hands-On Round)<\/strong><\/h3>\n\n\n\nThis is where many candidates lose marks.<\/p>\n\n\n\nYou will be asked to write queries involving joins, aggregations, subqueries, and window functions. It\u2019s important not just to get the answer, but to write clean and efficient queries.<\/p>\n\n\n\nYou should be comfortable solving problems like finding users who never placed orders, calculating aggregate values, or retrieving ranked results using window functions.<\/p>\n\n\n\nA strong candidate can solve a problem using multiple approaches and explain the trade-offs.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>Preparation Strategy (What Actually Works)<\/strong><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Revise Theory the Right Way<\/strong><\/h3>\n\n\n\nAvoid memorizing definitions. Focus on understanding concepts deeply and explaining them in your own words. Use real-world examples and try teaching the concept to someone else. This improves clarity and retention.<\/p>\n\n\n\nPractice SQL Daily<\/strong><\/p>\n\n\n\nConsistency is key. Spend time every day writing queries and solving problems. Focus on joins, aggregations, subqueries, and window functions. Gradually increase the difficulty level.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Solve Interview Questions<\/strong><\/h3>\n\n\n\nPractice common patterns such as normalization problems, transaction scenarios, and query optimization. Over time, you\u2019ll start recognizing patterns quickly during interviews.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Mock Interviews<\/strong><\/h3>\n\n\n\nMock interviews are one of the most effective ways to prepare. They help you improve communication, handle pressure, and identify weak areas. After each mock, review your performance and refine your answers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>Final Insight<\/strong><\/span><\/h2>\n\n\n\nAt a higher level, interviewers are not just testing your knowledge of DBMS. They are evaluating whether you can think like a backend engineer.<\/p>\n\n\n\nIf you can write efficient SQL queries, explain concepts clearly, and apply them to real-world scenarios, you will stand out.<\/p>\n\n\n\n Strengthen your fundamentals with: <a href=\"https:\/\/www.scaler.com\/topics\/dsa-roadmap\/\">https:\/\/www.scaler.com\/topics\/dsa-roadmap\/<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><\/span>FAQs<\/strong><\/span><\/h2>\n\n\n\nQ1: How long does it take to learn DBMS?<\/strong> <\/strong>It typically takes around 3 to 4 months to learn DBMS properly if you follow a structured roadmap like this one. Spending 1\u20132 hours daily is enough to cover both theory and practical SQL, along with revision and interview preparation.<\/p>\n\n\n\nQ2: Is DBMS asked in coding interviews?<\/strong> <\/strong>Yes, DBMS is frequently asked in SDE interviews, especially in product-based companies. While coding rounds focus on DSA, DBMS is heavily tested in technical interviews through conceptual and scenario-based questions.<\/p>\n\n\n\nQ3: What is the difference between DBMS and RDBMS?<\/strong> <\/strong>DBMS refers to any system that manages databases, while RDBMS specifically uses relational tables with structured schemas and supports SQL. RDBMS also enforces constraints and relationships between data.<\/p>\n\n\n\nQ4: Should I learn SQL before DBMS?<\/strong> <\/strong>You can start DBMS and SQL together, but understanding basic DBMS concepts first makes SQL easier to learn. DBMS provides the theory, while SQL helps you apply it practically.<\/p>\n\n\n\nQ5: What are the most important DBMS topics for placements?<\/strong> <\/strong>The most important topics include normalization, ACID properties, transactions, indexing, deadlocks, SQL queries, and query optimization. These are commonly asked in interviews and are essential for backend development roles.<\/p>\n","protected":false},"excerpt":{"rendered":"Introduction DBMS is one of the most important core subjects for computer science students and is consistently asked in product company interviews. Whether you\u2019re preparing for placements or aiming for backend roles, a strong understanding of databases is non-negotiable. This roadmap gives you a clear, step-by-step path: from ER modeling and normalization to SQL mastery, […]<\/p>\n","protected":false},"author":210,"featured_media":12502,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[37,316],"tags":[],"class_list":{"0":"post-12474","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-artificial-intelligence-machine-learning","8":"category-artificial-intelligence"},"acf":[],"_links":{"self":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts\/12474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/users\/210"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/comments?post=12474"}],"version-history":[{"count":2,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts\/12474\/revisions"}],"predecessor-version":[{"id":12501,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/posts\/12474\/revisions\/12501"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/media\/12502"}],"wp:attachment":[{"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/media?parent=12474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/categories?post=12474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scaler.com\/blog\/wp-json\/wp\/v2\/tags?post=12474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}