Database Systems Course \u2013 Pak Notes Hub<\/title>\n <link href=\"https:\/\/fonts.googleapis.com\/css2?family=Inter:wght@400;500;600;700&family=Fira+Code:wght@400;500&display=swap\" rel=\"stylesheet\"\/>\n <style>\n *, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }\n :root {\n --green: #1a7a4a;\n --green-dark: #145f38;\n --green-light:#e8f5ee;\n --teal: #17a589;\n --accent: #f0b127;\n --dark: #1c2833;\n --mid: #566573;\n --light: #f4f6f7;\n --code-bg: #f0f4f8;\n --code-border:#2e86c1;\n --note-bg: #fef9e7;\n --note-border:#f0b127;\n --white: #ffffff;\n --radius: 10px;\n --shadow: 0 4px 24px rgba(26,122,74,.10);\n }\n html { scroll-behavior: smooth; }\n body { font-family: 'Inter', sans-serif; background: #f2f6f3; color: var(--dark); line-height: 1.7; font-size: 15px; }\n\n \/* NAV *\/\n nav { position: sticky; top: 0; z-index: 100; background: var(--green-dark); padding: 0 2rem; display: flex; align-items: center; justify-content: space-between; height: 56px; box-shadow: 0 2px 12px rgba(0,0,0,.25); }\n .nav-brand { color: #fff; font-weight: 700; font-size: 1.05rem; }\n .nav-brand span { color: #a9dfbf; }\n .nav-links { display: flex; gap: 1.2rem; }\n .nav-links a { color: #d5f5e3; font-size: .85rem; font-weight: 500; text-decoration: none; transition: color .2s; }\n .nav-links a:hover { color: #fff; }\n\n \/* HERO *\/\n .hero { background: linear-gradient(135deg, var(--green) 0%, #145f38 60%, #0d4527 100%); padding: 5rem 2rem 4rem; text-align: center; position: relative; overflow: hidden; }\n .hero::before { content: 'SQL'; position: absolute; right: 6%; top: 50%; transform: translateY(-50%); font-size: 9rem; font-family: 'Fira Code', monospace; color: rgba(255,255,255,.06); font-weight: 700; pointer-events: none; }\n .hero-tag { display: inline-block; background: rgba(255,255,255,.12); color: #a9dfbf; font-size: .78rem; font-weight: 600; letter-spacing: 1.5px; text-transform: uppercase; padding: .35rem 1rem; border-radius: 100px; margin-bottom: 1.2rem; border: 1px solid rgba(169,223,163,.3); }\n .hero h1 { color: #fff; font-size: clamp(2rem, 5vw, 3.2rem); font-weight: 700; line-height: 1.15; margin-bottom: .8rem; }\n .hero h1 span { color: #a9dfbf; }\n .hero p { color: #d5f5e3; font-size: 1.05rem; max-width: 560px; margin: 0 auto 2rem; }\n .hero-pills { display: flex; flex-wrap: wrap; justify-content: center; gap: .7rem; }\n .pill { background: rgba(255,255,255,.13); color: #e8f5ee; padding: .45rem 1.1rem; border-radius: 100px; font-size: .82rem; font-weight: 500; border: 1px solid rgba(255,255,255,.18); }\n\n \/* LAYOUT *\/\n .page-wrap { max-width: 920px; margin: 0 auto; padding: 2.5rem 1.5rem 4rem; display: grid; grid-template-columns: 235px 1fr; gap: 2.5rem; align-items: start; }\n\n \/* SIDEBAR *\/\n .sidebar { position: sticky; top: 72px; background: var(--white); border-radius: var(--radius); box-shadow: var(--shadow); overflow: hidden; }\n .sidebar-title { background: var(--green); color: #fff; font-size: .75rem; font-weight: 700; letter-spacing: 1.2px; text-transform: uppercase; padding: .85rem 1.1rem; }\n .toc-list { list-style: none; padding: .5rem 0; }\n .toc-list li a { display: flex; align-items: center; gap: .55rem; padding: .42rem 1.1rem; color: var(--mid); font-size: .81rem; font-weight: 500; text-decoration: none; transition: all .18s; border-left: 3px solid transparent; }\n .toc-list li a:hover, .toc-list li a.active { color: var(--green); background: var(--green-light); border-left-color: var(--green); }\n .toc-num { display: inline-flex; align-items: center; justify-content: center; min-width: 20px; height: 20px; border-radius: 4px; background: var(--green-light); color: var(--green); font-size: .7rem; font-weight: 700; flex-shrink: 0; }\n .toc-list li a.active .toc-num { background: var(--green); color: #fff; }\n\n \/* UNIT CARD *\/\n .unit { background: var(--white); border-radius: var(--radius); box-shadow: var(--shadow); margin-bottom: 2.5rem; overflow: hidden; scroll-margin-top: 72px; }\n .unit-header { background: linear-gradient(100deg, var(--green) 0%, #1d9560 100%); padding: 1.4rem 1.8rem; }\n .unit-num { background: rgba(255,255,255,.18); color: #a9dfbf; font-size: .72rem; font-weight: 700; letter-spacing: 1px; text-transform: uppercase; padding: .3rem .75rem; border-radius: 100px; }\n .unit-header h2 { color: #fff; font-size: 1.25rem; font-weight: 700; line-height: 1.25; margin-top: .35rem; }\n .unit-header p { color: #c8f0d8; font-size: .85rem; margin-top: .2rem; }\n .unit-body { padding: 1.8rem; }\n\n \/* SECTION HEADING *\/\n .unit-body h3 { font-size: 1rem; font-weight: 700; color: var(--green); margin: 1.6rem 0 .7rem; padding-left: .85rem; border-left: 3px solid var(--green); line-height: 1.3; }\n .unit-body h3:first-child { margin-top: 0; }\n .unit-body p { color: var(--dark); margin-bottom: .7rem; }\n\n \/* BULLETS *\/\n .unit-body ul { list-style: none; padding-left: 0; margin: .5rem 0 1rem; }\n .unit-body ul li { padding: .3rem 0 .3rem 1.5rem; position: relative; color: var(--dark); font-size: .93rem; }\n .unit-body ul li::before { content: '\u27a4'; position: absolute; left: 0; color: var(--teal); font-size: .75rem; top: .45rem; }\n\n \/* CODE BLOCK *\/\n .code-block { background: var(--code-bg); border-left: 4px solid var(--code-border); border-radius: 6px; padding: 1.1rem 1.3rem; margin: .9rem 0 1rem; overflow-x: auto; }\n .code-block pre { font-family: 'Fira Code', monospace; font-size: .83rem; color: #1a3a54; line-height: 1.65; white-space: pre; }\n .comment { color: #7f8c8d; } .keyword { color: #1a5276; font-weight: 600; } .string { color: #1e8449; }\n\n \/* TABLES *\/\n .data-table { width: 100%; border-collapse: collapse; margin: .9rem 0 1.2rem; font-size: .87rem; border-radius: 8px; overflow: hidden; box-shadow: 0 1px 6px rgba(0,0,0,.06); }\n .data-table thead th { background: var(--green); color: #fff; padding: .7rem 1rem; text-align: left; font-weight: 600; font-size: .82rem; }\n .data-table tbody tr:nth-child(even) { background: var(--green-light); }\n .data-table tbody tr:hover { background: #d4efdf; }\n .data-table td { padding: .6rem 1rem; border-bottom: 1px solid #dce8e0; vertical-align: top; }\n .data-table td:first-child { font-weight: 600; color: var(--green-dark); }\n\n \/* INFO BOX *\/\n .info-box { background: var(--note-bg); border: 1px solid var(--note-border); border-radius: 7px; padding: .85rem 1.1rem; margin: .9rem 0; font-size: .88rem; line-height: 1.6; color: #7d6608; display: flex; align-items: flex-start; gap: .7rem; }\n .info-box .icon { font-size: 1rem; flex-shrink: 0; margin-top: .08rem; }\n\n \/* PRACTICE *\/\n .practice { background: var(--green-light); border: 1.5px dashed var(--green); border-radius: 8px; padding: .85rem 1.1rem; margin-top: 1.4rem; color: var(--green-dark); font-size: .88rem; font-weight: 500; }\n\n \/* TOC CARD *\/\n #toc-section { background: var(--white); border-radius: var(--radius); box-shadow: var(--shadow); margin-bottom: 2.5rem; overflow: hidden; }\n .toc-card-header { background: linear-gradient(100deg, var(--green) 0%, #1d9560 100%); padding: 1.2rem 1.8rem; }\n .toc-card-header h2 { color: #fff; font-size: 1.2rem; font-weight: 700; }\n .toc-card-body { padding: 1.4rem 1.8rem; }\n .toc-grid { display: grid; grid-template-columns: repeat(auto-fill, minmax(250px, 1fr)); gap: .6rem; }\n .toc-item { background: var(--light); border-radius: 7px; padding: .65rem 1rem; display: flex; align-items: center; gap: .75rem; text-decoration: none; color: var(--dark); font-size: .86rem; font-weight: 500; border: 1px solid #d5e5db; transition: all .18s; }\n .toc-item:hover { background: var(--green-light); border-color: var(--green); color: var(--green); transform: translateY(-1px); }\n .toc-badge { background: var(--green); color: #fff; font-size: .7rem; font-weight: 700; min-width: 22px; height: 22px; border-radius: 5px; display: inline-flex; align-items: center; justify-content: center; flex-shrink: 0; }\n\n \/* PROJECT FEATURES *\/\n .project-features { display: grid; grid-template-columns: repeat(auto-fill, minmax(210px, 1fr)); gap: .6rem; margin: .9rem 0 1.2rem; }\n .project-feat { background: var(--green-light); border-radius: 7px; padding: .6rem .9rem; font-size: .85rem; display: flex; align-items: center; gap: .5rem; color: var(--green-dark); border: 1px solid #a2d9b5; font-weight: 500; }\n\n \/* CONGRATS *\/\n .congrats { background: linear-gradient(135deg, var(--green) 0%, #145f38 100%); border-radius: var(--radius); padding: 2.5rem 2rem; text-align: center; color: #fff; margin-bottom: 2.5rem; box-shadow: var(--shadow); }\n .congrats h2 { font-size: 1.8rem; margin-bottom: .5rem; }\n .congrats p { color: #d5f5e3; font-size: 1rem; }\n\n \/* PROGRESS + BACK TOP *\/\n .progress-bar { position: fixed; top: 0; left: 0; height: 3px; background: var(--accent); z-index: 200; transition: width .1s linear; width: 0%; }\n #back-top { position: fixed; bottom: 2rem; right: 2rem; background: var(--green); color: #fff; width: 42px; height: 42px; border-radius: 50%; border: none; cursor: pointer; font-size: 1.1rem; box-shadow: 0 4px 14px rgba(26,122,74,.35); display: flex; align-items: center; justify-content: center; opacity: 0; transition: opacity .25s, transform .25s; pointer-events: none; }\n #back-top.visible { opacity: 1; pointer-events: auto; }\n #back-top:hover { transform: translateY(-2px); }\n\n \/* RESPONSIVE *\/\n @media (max-width: 720px) { .page-wrap { grid-template-columns: 1fr; } .sidebar { position: static; display: none; } .hero::before { display: none; } nav .nav-links { display: none; } }\n <\/style>\n<\/head>\n<body>\n\n<div class=\"progress-bar\" id=\"progress\"><\/div>\n\n\n<nav>\n <div class=\"nav-brand\">Pak <span>Notes Hub<\/span><\/div>\n <div class=\"nav-links\">\n <a href=\"#toc-section\">Course Outline<\/a>\n <a href=\"#unit-1\">Start Learning<\/a>\n <a href=\"#unit-7\">Normalization<\/a>\n <a href=\"#unit-12\">Final Project<\/a>\n <\/div>\n<\/nav>\n\n\n<section class=\"hero\">\n <div class=\"hero-tag\">\ud83d\udcd8 University Level Course<\/div>\n <h1>Database Systems<br\/><span>Complete Course<\/span><\/h1>\n <p>ER Diagrams \u00b7 SQL \u00b7 Normalization \u00b7 Transactions \u00b7 Indexing \u00b7 Security \u2014 All in Easy English<\/p>\n <div class=\"hero-pills\">\n <span class=\"pill\">\ud83d\udcca 12 Units<\/span>\n <span class=\"pill\">\ud83c\udf93 University Level<\/span>\n <span class=\"pill\">\ud83d\udcbb SQL Examples<\/span>\n <span class=\"pill\">\ud83d\udcdd Practice Tasks<\/span>\n <span class=\"pill\">\ud83d\ude80 Final Project<\/span>\n <\/div>\n<\/section>\n\n<div class=\"page-wrap\">\n\n \n <aside class=\"sidebar\">\n <div class=\"sidebar-title\">\ud83d\udccb Course Outline<\/div>\n <ul class=\"toc-list\" id=\"toc-nav\">\n <li><a href=\"#toc-section\"><span class=\"toc-num\">\ud83d\udccb<\/span> Contents<\/a><\/li>\n <li><a href=\"#unit-1\"><span class=\"toc-num\">1<\/span> Introduction<\/a><\/li>\n <li><a href=\"#unit-2\"><span class=\"toc-num\">2<\/span> Data Models<\/a><\/li>\n <li><a href=\"#unit-3\"><span class=\"toc-num\">3<\/span> ER Diagrams<\/a><\/li>\n <li><a href=\"#unit-4\"><span class=\"toc-num\">4<\/span> Relational Model<\/a><\/li>\n <li><a href=\"#unit-5\"><span class=\"toc-num\">5<\/span> SQL Basics<\/a><\/li>\n <li><a href=\"#unit-6\"><span class=\"toc-num\">6<\/span> SQL Advanced<\/a><\/li>\n <li><a href=\"#unit-7\"><span class=\"toc-num\">7<\/span> Normalization<\/a><\/li>\n <li><a href=\"#unit-8\"><span class=\"toc-num\">8<\/span> Transactions<\/a><\/li>\n <li><a href=\"#unit-9\"><span class=\"toc-num\">9<\/span> Indexing<\/a><\/li>\n <li><a href=\"#unit-10\"><span class=\"toc-num\">10<\/span> Query Optimization<\/a><\/li>\n <li><a href=\"#unit-11\"><span class=\"toc-num\">11<\/span> Security<\/a><\/li>\n <li><a href=\"#unit-12\"><span class=\"toc-num\">12<\/span> Trends & Project<\/a><\/li>\n <\/ul>\n <\/aside>\n\n \n <main class=\"content\">\n\n \n <div id=\"toc-section\">\n <div class=\"toc-card-header\"><h2>\ud83d\udccb Table of Contents \u2014 12 Units<\/h2><\/div>\n <div class=\"toc-card-body\">\n <div class=\"toc-grid\">\n <a class=\"toc-item\" href=\"#unit-1\"><span class=\"toc-badge\">1<\/span> Introduction to Databases<\/a>\n <a class=\"toc-item\" href=\"#unit-2\"><span class=\"toc-badge\">2<\/span> Data Models<\/a>\n <a class=\"toc-item\" href=\"#unit-3\"><span class=\"toc-badge\">3<\/span> ER Diagrams<\/a>\n <a class=\"toc-item\" href=\"#unit-4\"><span class=\"toc-badge\">4<\/span> Relational Model<\/a>\n <a class=\"toc-item\" href=\"#unit-5\"><span class=\"toc-badge\">5<\/span> SQL \u2013 Basics<\/a>\n <a class=\"toc-item\" href=\"#unit-6\"><span class=\"toc-badge\">6<\/span> SQL \u2013 Advanced<\/a>\n <a class=\"toc-item\" href=\"#unit-7\"><span class=\"toc-badge\">7<\/span> Normalization<\/a>\n <a class=\"toc-item\" href=\"#unit-8\"><span class=\"toc-badge\">8<\/span> Transaction Management<\/a>\n <a class=\"toc-item\" href=\"#unit-9\"><span class=\"toc-badge\">9<\/span> Indexing & Hashing<\/a>\n <a class=\"toc-item\" href=\"#unit-10\"><span class=\"toc-badge\">10<\/span> Query Optimization<\/a>\n <a class=\"toc-item\" href=\"#unit-11\"><span class=\"toc-badge\">11<\/span> Security & Authorization<\/a>\n <a class=\"toc-item\" href=\"#unit-12\"><span class=\"toc-badge\">12<\/span> Emerging Trends & Project<\/a>\n <\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-1\">\n <div class=\"unit-header\">\n <span class=\"unit-num\">Unit 1<\/span>\n <h2>Introduction to Databases<\/h2>\n <p>Start from the very foundation.<\/p>\n <\/div>\n <div class=\"unit-body\">\n <h3>What is a Database?<\/h3>\n <p>A <strong>database<\/strong> is an organized collection of related data stored so it can be easily accessed, managed, and updated. Think of it as a very powerful, structured digital filing cabinet used by software applications.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Example: When you log into your university portal, your name, roll number, and grades are all stored in a database.<\/div>\n\n <h3>DBMS vs File System<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Feature<\/th><th>File System<\/th><th>DBMS<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Data Redundancy<\/td><td>High \u2014 data repeated in multiple files<\/td><td>Low \u2014 centralized storage<\/td><\/tr>\n <tr><td>Data Inconsistency<\/td><td>Common problem<\/td><td>Controlled by integrity constraints<\/td><\/tr>\n <tr><td>Data Access<\/td><td>Manual search through files<\/td><td>Powerful query language (SQL)<\/td><\/tr>\n <tr><td>Security<\/td><td>Limited file-level permissions<\/td><td>Fine-grained user access control<\/td><\/tr>\n <tr><td>Concurrent Access<\/td><td>No built-in support<\/td><td>Multi-user access with locking<\/td><\/tr>\n <tr><td>Recovery<\/td><td>Manual backup only<\/td><td>Automatic crash recovery<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Types of Databases<\/h3>\n <ul>\n <li><strong>Relational Database<\/strong> \u2014 Data stored in tables (e.g., MySQL, PostgreSQL, Oracle)<\/li>\n <li><strong>NoSQL Database<\/strong> \u2014 Flexible schema for unstructured data (e.g., MongoDB, Cassandra)<\/li>\n <li><strong>Hierarchical Database<\/strong> \u2014 Tree-like structure (e.g., IBM IMS)<\/li>\n <li><strong>Network Database<\/strong> \u2014 Graph-like structure with multiple parent-child links<\/li>\n <li><strong>Cloud Database<\/strong> \u2014 Hosted on cloud platforms (e.g., AWS RDS, Google Firestore)<\/li>\n <\/ul>\n\n <h3>Database Users & Roles<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Role<\/th><th>Responsibility<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Database Administrator (DBA)<\/td><td>Manages the DBMS, users, backups, and performance<\/td><\/tr>\n <tr><td>Database Designer<\/td><td>Designs the schema and ER diagrams<\/td><\/tr>\n <tr><td>Application Developer<\/td><td>Writes code that interacts with the database<\/td><\/tr>\n <tr><td>End User<\/td><td>Uses the application to view or enter data<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> List 3 real-world examples of databases used in daily life and identify which type each one is.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-2\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 2<\/span><h2>Data Models<\/h2><p>Different ways to represent and organize data.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is a Data Model?<\/h3>\n <p>A <strong>data model<\/strong> is a conceptual framework that defines how data is structured, stored, and manipulated. It acts as the blueprint of a database system.<\/p>\n\n <h3>Relational Model<\/h3>\n <p>The most widely used model. Data is organized into <strong>tables (relations)<\/strong> with rows (tuples) and columns (attributes). Tables are linked using <strong>keys<\/strong>.<\/p>\n <div class=\"code-block\"><pre>Table: Student\n+----------+-----------+-----+\n| roll_no | name | age |\n+----------+-----------+-----+\n| 101 | Ahmad Ali | 20 |\n| 102 | Sara Khan | 21 |\n+----------+-----------+-----+<\/pre><\/div>\n\n <h3>Entity-Relationship (ER) Model<\/h3>\n <p>A high-level conceptual model used during <strong>database design<\/strong>. It describes data in terms of <strong>entities<\/strong>, their <strong>attributes<\/strong>, and the <strong>relationships<\/strong> between them.<\/p>\n\n <h3>Comparison of Data Models<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Model<\/th><th>Structure<\/th><th>Best Used For<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Hierarchical<\/td><td>Tree (parent-child)<\/td><td>Structured, predictable data<\/td><td>IBM IMS<\/td><\/tr>\n <tr><td>Network<\/td><td>Graph (multi-parent)<\/td><td>Complex relationships<\/td><td>IDS<\/td><\/tr>\n <tr><td>Relational<\/td><td>Tables<\/td><td>Most business applications<\/td><td>MySQL, Oracle<\/td><\/tr>\n <tr><td>Object-Oriented<\/td><td>Objects & classes<\/td><td>Complex\/multimedia data<\/td><td>db4o<\/td><\/tr>\n <tr><td>NoSQL<\/td><td>Flexible (doc\/key-val)<\/td><td>Big data, web apps<\/td><td>MongoDB, Redis<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Draw the relational model for a simple Library system with Books and Members tables.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-3\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 3<\/span><h2>ER Diagrams<\/h2><p>Visualize your database before building it.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is an ER Diagram?<\/h3>\n <p>An <strong>Entity-Relationship (ER) Diagram<\/strong> is a visual diagram that shows the structure of a database. It is created <strong>before writing any SQL code<\/strong> and helps in planning.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Think of an ER Diagram like an architect’s blueprint \u2014 you design the house on paper before building it.<\/div>\n\n <h3>ER Diagram Symbols<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Symbol<\/th><th>Shape<\/th><th>Meaning<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Entity<\/td><td>Rectangle<\/td><td>A real-world object (e.g., Student, Course)<\/td><\/tr>\n <tr><td>Attribute<\/td><td>Ellipse \/ Oval<\/td><td>Property of an entity (e.g., name, age)<\/td><\/tr>\n <tr><td>Primary Key Attribute<\/td><td>Underlined Ellipse<\/td><td>Uniquely identifies each record<\/td><\/tr>\n <tr><td>Relationship<\/td><td>Diamond<\/td><td>Association between two entities<\/td><\/tr>\n <tr><td>Weak Entity<\/td><td>Double Rectangle<\/td><td>Entity that depends on another entity<\/td><\/tr>\n <tr><td>Multi-valued Attribute<\/td><td>Double Ellipse<\/td><td>Attribute with multiple values (e.g., phone numbers)<\/td><\/tr>\n <tr><td>Derived Attribute<\/td><td>Dashed Ellipse<\/td><td>Value calculated from another attribute (e.g., age from DOB)<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Cardinality (Relationship Types)<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Type<\/th><th>Notation<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>One-to-One (1:1)<\/td><td>1 \u2500\u2500 1<\/td><td>One person has one passport<\/td><\/tr>\n <tr><td>One-to-Many (1:N)<\/td><td>1 \u2500\u2500< N<\/td><td>One teacher teaches many students<\/td><\/tr>\n <tr><td>Many-to-Many (M:N)<\/td><td>M >\u2500\u2500< N<\/td><td>Students enroll in many courses; courses have many students<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Weak Entities<\/h3>\n <p>A <strong>weak entity<\/strong> cannot exist without depending on a <strong>strong entity<\/strong>. It does not have its own primary key \u2014 it uses a <strong>partial key<\/strong> combined with the strong entity’s key.<\/p>\n <div class=\"code-block\"><pre>Strong Entity: Employee (emp_id, name)\nWeak Entity: Dependent (dep_name, relation)\n \u2014 Cannot exist without Employee\n \u2014 Identified by: emp_id + dep_name<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Draw an ER Diagram for a University system with Entities: Student, Course, Professor, Department.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-4\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 4<\/span><h2>Relational Model<\/h2><p>The mathematical foundation of SQL databases.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Key Terminology<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Term<\/th><th>Meaning<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Relation<\/td><td>A table<\/td><td>Student table<\/td><\/tr>\n <tr><td>Tuple<\/td><td>A row in a table<\/td><td>One student’s record<\/td><\/tr>\n <tr><td>Attribute<\/td><td>A column in a table<\/td><td>name, age, roll_no<\/td><\/tr>\n <tr><td>Domain<\/td><td>Allowed values for an attribute<\/td><td>age: integers 1\u2013120<\/td><\/tr>\n <tr><td>Degree<\/td><td>Number of attributes in a relation<\/td><td>Student has 4 attributes \u2192 degree 4<\/td><\/tr>\n <tr><td>Cardinality<\/td><td>Number of tuples in a relation<\/td><td>50 students \u2192 cardinality 50<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Types of Keys<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Key Type<\/th><th>Definition<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Super Key<\/td><td>Any set of attributes that uniquely identifies a tuple<\/td><td>{roll_no}, {roll_no, name}<\/td><\/tr>\n <tr><td>Candidate Key<\/td><td>Minimal super key (no redundant attributes)<\/td><td>{roll_no}, {email}<\/td><\/tr>\n <tr><td>Primary Key<\/td><td>Chosen candidate key for identification<\/td><td>{roll_no}<\/td><\/tr>\n <tr><td>Foreign Key<\/td><td>Attribute referencing primary key of another table<\/td><td>dept_id in Student refs Department<\/td><\/tr>\n <tr><td>Composite Key<\/td><td>Primary key made of two or more attributes<\/td><td>{student_id + course_id}<\/td><\/tr>\n <tr><td>Alternate Key<\/td><td>Candidate keys not chosen as primary key<\/td><td>{email} if roll_no is PK<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Relational Algebra \u2014 Basic Operations<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Operation<\/th><th>Symbol<\/th><th>Purpose<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Select<\/td><td>\u03c3 (sigma)<\/td><td>Filter rows based on a condition<\/td><\/tr>\n <tr><td>Project<\/td><td>\u03c0 (pi)<\/td><td>Select specific columns<\/td><\/tr>\n <tr><td>Union<\/td><td>\u222a<\/td><td>Combine tuples from two relations<\/td><\/tr>\n <tr><td>Intersection<\/td><td>\u2229<\/td><td>Common tuples in two relations<\/td><\/tr>\n <tr><td>Difference<\/td><td>\u2212<\/td><td>Tuples in one relation but not the other<\/td><\/tr>\n <tr><td>Cartesian Product<\/td><td>\u00d7<\/td><td>Combine every tuple of two relations<\/td><\/tr>\n <tr><td>Join<\/td><td>\u22c8<\/td><td>Combine related tuples from two relations<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Given a Student(roll_no, name, dept_id) table, write Relational Algebra to get names of all students in the CS department.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-5\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 5<\/span><h2>SQL \u2013 Basics<\/h2><p>Start writing real database queries.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is SQL?<\/h3>\n <p><strong>SQL (Structured Query Language)<\/strong> is the standard language used to communicate with relational databases. It is used to create tables, insert data, and retrieve information.<\/p>\n\n <h3>DDL \u2014 Data Definition Language<\/h3>\n <p>Used to <strong>define and modify the structure<\/strong> of the database.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create a table<\/span>\n<span class=\"keyword\">CREATE TABLE<\/span> Student (\n roll_no <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n name <span class=\"keyword\">VARCHAR<\/span>(50) <span class=\"keyword\">NOT NULL<\/span>,\n age <span class=\"keyword\">INT<\/span>,\n dept_id <span class=\"keyword\">INT<\/span>,\n email <span class=\"keyword\">VARCHAR<\/span>(100) <span class=\"keyword\">UNIQUE<\/span>\n);\n\n<span class=\"comment\">-- Add a new column<\/span>\n<span class=\"keyword\">ALTER TABLE<\/span> Student <span class=\"keyword\">ADD COLUMN<\/span> cgpa <span class=\"keyword\">FLOAT<\/span>;\n\n<span class=\"comment\">-- Delete the table permanently<\/span>\n<span class=\"keyword\">DROP TABLE<\/span> Student;<\/pre><\/div>\n\n <h3>DML \u2014 Data Manipulation Language<\/h3>\n <p>Used to <strong>insert, update, and delete data<\/strong> inside tables.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Insert a record<\/span>\n<span class=\"keyword\">INSERT INTO<\/span> Student (roll_no, name, age, dept_id)\n<span class=\"keyword\">VALUES<\/span> (101, <span class=\"string\">'Ahmad Ali'<\/span>, 20, 1);\n\n<span class=\"comment\">-- Update a record<\/span>\n<span class=\"keyword\">UPDATE<\/span> Student <span class=\"keyword\">SET<\/span> age = 21 <span class=\"keyword\">WHERE<\/span> roll_no = 101;\n\n<span class=\"comment\">-- Delete a record<\/span>\n<span class=\"keyword\">DELETE FROM<\/span> Student <span class=\"keyword\">WHERE<\/span> roll_no = 101;<\/pre><\/div>\n\n <h3>SELECT \u2014 Retrieving Data<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Get all students<\/span>\n<span class=\"keyword\">SELECT<\/span> * <span class=\"keyword\">FROM<\/span> Student;\n\n<span class=\"comment\">-- Get only name and age<\/span>\n<span class=\"keyword\">SELECT<\/span> name, age <span class=\"keyword\">FROM<\/span> Student;\n\n<span class=\"comment\">-- Filter with WHERE<\/span>\n<span class=\"keyword\">SELECT<\/span> name <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">WHERE<\/span> age > 20;\n\n<span class=\"comment\">-- Sort results<\/span>\n<span class=\"keyword\">SELECT<\/span> name, cgpa <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">ORDER BY<\/span> cgpa <span class=\"keyword\">DESC<\/span>;\n\n<span class=\"comment\">-- Limit results<\/span>\n<span class=\"keyword\">SELECT<\/span> name <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">LIMIT<\/span> 10;<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Create a Department table and a Student table. Insert 5 students. Retrieve students whose age is greater than 19.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-6\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 6<\/span><h2>SQL \u2013 Advanced Queries<\/h2><p>Master JOINs, Subqueries, and Aggregates.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>JOINs \u2014 Combining Tables<\/h3>\n <p>A <strong>JOIN<\/strong> is used to combine rows from two or more tables based on a related column.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- INNER JOIN: only matching rows from both tables<\/span>\n<span class=\"keyword\">SELECT<\/span> Student.name, Department.dept_name\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">INNER JOIN<\/span> Department <span class=\"keyword\">ON<\/span> Student.dept_id = Department.dept_id;\n\n<span class=\"comment\">-- LEFT JOIN: all rows from left + matching from right<\/span>\n<span class=\"keyword\">SELECT<\/span> Student.name, Department.dept_name\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">LEFT JOIN<\/span> Department <span class=\"keyword\">ON<\/span> Student.dept_id = Department.dept_id;<\/pre><\/div>\n\n <h3>Types of JOINs<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>JOIN Type<\/th><th>Returns<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>INNER JOIN<\/td><td>Only rows where condition matches in BOTH tables<\/td><\/tr>\n <tr><td>LEFT JOIN<\/td><td>All rows from left table; NULL for non-matching right rows<\/td><\/tr>\n <tr><td>RIGHT JOIN<\/td><td>All rows from right table; NULL for non-matching left rows<\/td><\/tr>\n <tr><td>FULL OUTER JOIN<\/td><td>All rows from both tables; NULL where no match<\/td><\/tr>\n <tr><td>SELF JOIN<\/td><td>A table joined with itself<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Aggregate Functions<\/h3>\n <div class=\"code-block\"><pre><span class=\"keyword\">SELECT COUNT<\/span>(*) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Total students<\/span>\n<span class=\"keyword\">SELECT AVG<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Average CGPA<\/span>\n<span class=\"keyword\">SELECT MAX<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Highest CGPA<\/span>\n<span class=\"keyword\">SELECT MIN<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Lowest CGPA<\/span>\n<span class=\"keyword\">SELECT SUM<\/span>(fee) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Total fee<\/span><\/pre><\/div>\n\n <h3>GROUP BY & HAVING<\/h3>\n <p><strong>GROUP BY<\/strong> groups rows. <strong>HAVING<\/strong> filters groups (like WHERE but for groups).<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Count students per department<\/span>\n<span class=\"keyword\">SELECT<\/span> dept_id, <span class=\"keyword\">COUNT<\/span>(*) <span class=\"keyword\">AS<\/span> total\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">GROUP BY<\/span> dept_id;\n\n<span class=\"comment\">-- Departments with more than 10 students<\/span>\n<span class=\"keyword\">SELECT<\/span> dept_id, <span class=\"keyword\">COUNT<\/span>(*) <span class=\"keyword\">AS<\/span> total\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">GROUP BY<\/span> dept_id\n<span class=\"keyword\">HAVING COUNT<\/span>(*) > 10;<\/pre><\/div>\n\n <h3>Subqueries<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Students with CGPA higher than average<\/span>\n<span class=\"keyword\">SELECT<\/span> name <span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">WHERE<\/span> cgpa > (<span class=\"keyword\">SELECT AVG<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student);<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Write a query to find the department with the highest number of students using GROUP BY and ORDER BY.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-7\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 7<\/span><h2>Normalization<\/h2><p>Organize your database to remove redundancy.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is Normalization?<\/h3>\n <p>Normalization is the process of <strong>organizing a database<\/strong> to reduce <strong>data redundancy<\/strong> (repeated data) and improve <strong>data integrity<\/strong>. It involves dividing large tables into smaller ones.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Redundancy problem: If a student’s department name is stored in every row, changing the name requires updating hundreds of rows \u2014 and errors can creep in.<\/div>\n\n <h3>Functional Dependency<\/h3>\n <p>If attribute <strong>A<\/strong> uniquely determines attribute <strong>B<\/strong>, we write: <strong>A \u2192 B<\/strong><\/p>\n <div class=\"code-block\"><pre>roll_no \u2192 name (roll_no determines name)\nroll_no \u2192 dept_id (roll_no determines dept_id)\ndept_id \u2192 dept_name (dept_id determines dept_name)<\/pre><\/div>\n\n <h3>Normal Forms<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Normal Form<\/th><th>Rule<\/th><th>Problem it Solves<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>1NF<\/td><td>All attributes must be atomic (single value). No repeating groups.<\/td><td>Removes multi-valued cells<\/td><\/tr>\n <tr><td>2NF<\/td><td>Must be in 1NF. No partial dependency \u2014 non-key attribute must depend on the FULL primary key.<\/td><td>Removes partial dependencies<\/td><\/tr>\n <tr><td>3NF<\/td><td>Must be in 2NF. No transitive dependency \u2014 non-key attribute must not depend on another non-key attribute.<\/td><td>Removes transitive dependencies<\/td><\/tr>\n <tr><td>BCNF<\/td><td>Stronger version of 3NF. For every dependency A\u2192B, A must be a super key.<\/td><td>Handles anomalies 3NF misses<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Example: Normalizing a Table<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- BEFORE (Unnormalized): Redundancy problem<\/span>\n| roll_no | name | dept_id | dept_name |\n| 101 | Ahmad | 1 | Computer |\n| 102 | Sara | 1 | Computer | \u2190 dept_name repeated!\n\n<span class=\"comment\">-- AFTER 3NF: Split into two tables<\/span>\nStudent: (roll_no, name, dept_id)\nDepartment: (dept_id, dept_name)<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Given table Order(order_id, customer_id, customer_name, product_id, product_name, qty). Normalize it to 3NF.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-8\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 8<\/span><h2>Transaction Management<\/h2><p>Ensure data stays safe and consistent.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is a Transaction?<\/h3>\n <p>A <strong>transaction<\/strong> is a sequence of database operations treated as a <strong>single unit<\/strong>. Either ALL operations succeed, or NONE of them are applied.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Bank Transfer Example<\/span>\n<span class=\"keyword\">BEGIN TRANSACTION<\/span>;\n <span class=\"keyword\">UPDATE<\/span> Account <span class=\"keyword\">SET<\/span> balance = balance - 5000 <span class=\"keyword\">WHERE<\/span> acc_id = 1;\n <span class=\"keyword\">UPDATE<\/span> Account <span class=\"keyword\">SET<\/span> balance = balance + 5000 <span class=\"keyword\">WHERE<\/span> acc_id = 2;\n<span class=\"keyword\">COMMIT<\/span>; <span class=\"comment\">-- Save both changes<\/span>\n\n<span class=\"comment\">-- If anything goes wrong:<\/span>\n<span class=\"keyword\">ROLLBACK<\/span>; <span class=\"comment\">-- Undo all changes<\/span><\/pre><\/div>\n\n <h3>ACID Properties<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Letter<\/th><th>Property<\/th><th>Meaning<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>A<\/td><td>Atomicity<\/td><td>All operations complete or none do \u2014 no partial updates<\/td><\/tr>\n <tr><td>C<\/td><td>Consistency<\/td><td>Database moves from one valid state to another valid state<\/td><\/tr>\n <tr><td>I<\/td><td>Isolation<\/td><td>Concurrent transactions don’t interfere with each other<\/td><\/tr>\n <tr><td>D<\/td><td>Durability<\/td><td>Once committed, changes are permanent even after system crash<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Concurrency Problems<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Problem<\/th><th>Description<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Dirty Read<\/td><td>Transaction reads data that another transaction modified but not yet committed<\/td><\/tr>\n <tr><td>Non-Repeatable Read<\/td><td>Same data read twice gives different values because another transaction modified it<\/td><\/tr>\n <tr><td>Phantom Read<\/td><td>A query returns different rows because another transaction inserted\/deleted rows<\/td><\/tr>\n <tr><td>Lost Update<\/td><td>Two transactions update the same data; one update is lost<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Deadlock<\/h3>\n <div class=\"code-block\"><pre>T1 locks Row A, waits for Row B\nT2 locks Row B, waits for Row A\n--> Neither can proceed = DEADLOCK\n\nSolutions:\n- Deadlock Prevention (acquire all locks at start)\n- Deadlock Detection (kill one transaction)\n- Timeout (abort transaction if waiting too long)<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Explain with an example how ACID properties protect a bank transfer transaction from failure.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-9\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 9<\/span><h2>Indexing & Hashing<\/h2><p>Speed up database queries dramatically.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Why Indexing?<\/h3>\n <p>Without an index, the database <strong>scans every row<\/strong> to find data. An <strong>index<\/strong> is a special data structure that allows the database to find rows <strong>much faster<\/strong> \u2014 like an index at the back of a book.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Without index: Search 1,000,000 rows one by one. With index: Jump directly to the result. Speed difference: minutes vs milliseconds!<\/div>\n\n <h3>Types of Indexes<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Index Type<\/th><th>Description<\/th><th>Best For<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Clustered Index<\/td><td>Rows stored physically in index order. Only one per table.<\/td><td>Primary key lookups, range queries<\/td><\/tr>\n <tr><td>Non-Clustered Index<\/td><td>Separate structure; pointer to actual row. Many per table.<\/td><td>Frequently searched non-PK columns<\/td><\/tr>\n <tr><td>Unique Index<\/td><td>Ensures all values in indexed column are unique<\/td><td>Email, username columns<\/td><\/tr>\n <tr><td>Composite Index<\/td><td>Index on two or more columns<\/td><td>Multi-column WHERE conditions<\/td><\/tr>\n <tr><td>Full-Text Index<\/td><td>Optimized for searching inside text<\/td><td>Search engines, article content<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>SQL \u2014 Creating Indexes<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create an index<\/span>\n<span class=\"keyword\">CREATE INDEX<\/span> idx_name <span class=\"keyword\">ON<\/span> Student(name);\n\n<span class=\"comment\">-- Create unique index<\/span>\n<span class=\"keyword\">CREATE UNIQUE INDEX<\/span> idx_email <span class=\"keyword\">ON<\/span> Student(email);\n\n<span class=\"comment\">-- Drop an index<\/span>\n<span class=\"keyword\">DROP INDEX<\/span> idx_name <span class=\"keyword\">ON<\/span> Student;\n\n<span class=\"comment\">-- Check if a query uses an index<\/span>\n<span class=\"keyword\">EXPLAIN SELECT<\/span> * <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">WHERE<\/span> name = <span class=\"string\">'Ahmad'<\/span>;<\/pre><\/div>\n\n <h3>B+ Tree vs Hash Index<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Feature<\/th><th>B+ Tree Index<\/th><th>Hash Index<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Best for<\/td><td>Range queries (>, <, BETWEEN)<\/td><td>Exact match (=) queries only<\/td><\/tr>\n <tr><td>Order<\/td><td>Maintains sorted order<\/td><td>No order maintained<\/td><\/tr>\n <tr><td>Speed<\/td><td>O(log n)<\/td><td>O(1) average<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Create indexes on a Student table for name and email columns. Use EXPLAIN to verify the query uses the index.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-10\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 10<\/span><h2>Query Processing & Optimization<\/h2><p>Understand how the database executes your queries.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Query Processing Steps<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Step<\/th><th>Name<\/th><th>What Happens<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>1<\/td><td>Parsing<\/td><td>SQL query checked for syntax errors and converted to a parse tree<\/td><\/tr>\n <tr><td>2<\/td><td>Translation<\/td><td>Parse tree converted to relational algebra expression<\/td><\/tr>\n <tr><td>3<\/td><td>Optimization<\/td><td>Query optimizer finds the most efficient execution plan<\/td><\/tr>\n <tr><td>4<\/td><td>Evaluation<\/td><td>The chosen plan is executed and results are returned<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Using EXPLAIN<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- See execution plan in MySQL<\/span>\n<span class=\"keyword\">EXPLAIN SELECT<\/span> s.name, d.dept_name\n<span class=\"keyword\">FROM<\/span> Student s\n<span class=\"keyword\">JOIN<\/span> Department d <span class=\"keyword\">ON<\/span> s.dept_id = d.dept_id\n<span class=\"keyword\">WHERE<\/span> s.cgpa > 3.0;\n\n<span class=\"comment\">-- Key output fields:<\/span>\n<span class=\"comment\">-- type: ALL=bad (full scan), ref=good, const=best<\/span>\n<span class=\"comment\">-- key: which index is being used<\/span>\n<span class=\"comment\">-- rows: estimated rows examined<\/span><\/pre><\/div>\n\n <h3>Optimization Tips<\/h3>\n <ul>\n <li><strong>Use indexes<\/strong> on columns used in WHERE, JOIN, and ORDER BY clauses.<\/li>\n <li><strong>Avoid SELECT *<\/strong> \u2014 only retrieve columns you actually need.<\/li>\n <li><strong>Use JOINs instead of subqueries<\/strong> where possible \u2014 often faster.<\/li>\n <li><strong>Filter early<\/strong> \u2014 apply WHERE conditions before JOINs to reduce data size.<\/li>\n <li><strong>Avoid functions on indexed columns<\/strong> \u2014 WHERE YEAR(date) = 2024 disables the index.<\/li>\n <li><strong>Use LIMIT<\/strong> when you only need a few results.<\/li>\n <\/ul>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Write two versions of a query (with and without index). Use EXPLAIN to compare and explain the difference.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-11\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 11<\/span><h2>Database Security & Authorization<\/h2><p>Protect your data from unauthorized access.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Why Database Security?<\/h3>\n <p>Databases contain sensitive information \u2014 student records, financial data, passwords. Without proper security, this data can be <strong>stolen, modified, or destroyed<\/strong> by attackers or unauthorized users.<\/p>\n\n <h3>SQL Injection Attack<\/h3>\n <p>One of the most dangerous attacks. An attacker <strong>injects malicious SQL code<\/strong> through user input fields to manipulate the database.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Vulnerable login query (bad practice):<\/span>\n<span class=\"keyword\">SELECT<\/span> * <span class=\"keyword\">FROM<\/span> users <span class=\"keyword\">WHERE<\/span> username = [user_input] <span class=\"keyword\">AND<\/span> password = [pass];\n\n<span class=\"comment\">-- Attacker types as username: ' OR 1=1 --<\/span>\n<span class=\"comment\">-- The query becomes: WHERE username = '' OR 1=1 --<\/span>\n<span class=\"comment\">-- Result: ALL users returned, login bypassed!<\/span>\n\n<span class=\"comment\">-- Safe fix: Use Prepared Statements<\/span>\n<span class=\"keyword\">SELECT<\/span> * <span class=\"keyword\">FROM<\/span> users <span class=\"keyword\">WHERE<\/span> username = ? <span class=\"keyword\">AND<\/span> password = ?;<\/pre><\/div>\n\n <h3>User Privileges (GRANT & REVOKE)<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create a new user<\/span>\n<span class=\"keyword\">CREATE USER<\/span> <span class=\"string\">'ali'<\/span>@<span class=\"string\">'localhost'<\/span> <span class=\"keyword\">IDENTIFIED BY<\/span> <span class=\"string\">'password123'<\/span>;\n\n<span class=\"comment\">-- Give SELECT permission only<\/span>\n<span class=\"keyword\">GRANT SELECT ON<\/span> university_db.* <span class=\"keyword\">TO<\/span> <span class=\"string\">'ali'<\/span>@<span class=\"string\">'localhost'<\/span>;\n\n<span class=\"comment\">-- Give full access<\/span>\n<span class=\"keyword\">GRANT ALL PRIVILEGES ON<\/span> university_db.* <span class=\"keyword\">TO<\/span> <span class=\"string\">'admin'<\/span>@<span class=\"string\">'localhost'<\/span>;\n\n<span class=\"comment\">-- Remove a privilege<\/span>\n<span class=\"keyword\">REVOKE INSERT ON<\/span> university_db.* <span class=\"keyword\">FROM<\/span> <span class=\"string\">'ali'<\/span>@<span class=\"string\">'localhost'<\/span>;\n\n<span class=\"keyword\">FLUSH PRIVILEGES<\/span>;<\/pre><\/div>\n\n <h3>Views for Security<\/h3>\n <p>A <strong>view<\/strong> is a virtual table that shows only specific data. It hides sensitive columns from certain users.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create a view that hides salary<\/span>\n<span class=\"keyword\">CREATE VIEW<\/span> public_employee <span class=\"keyword\">AS<\/span>\n<span class=\"keyword\">SELECT<\/span> emp_id, name, department <span class=\"keyword\">FROM<\/span> Employee;\n\n<span class=\"comment\">-- User only sees emp_id, name, department<\/span>\n<span class=\"comment\">-- salary column is completely hidden<\/span><\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Create a user with only SELECT privilege on the Student table. Test that they cannot INSERT or DELETE data.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-12\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 12<\/span><h2>Emerging Trends & Final Project<\/h2><p>Modern databases and a complete real-world project.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>NoSQL Databases<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>NoSQL Type<\/th><th>Description<\/th><th>Examples<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Document Store<\/td><td>Data stored as JSON-like documents<\/td><td>MongoDB, CouchDB<\/td><\/tr>\n <tr><td>Key-Value Store<\/td><td>Simple key \u2192 value pairs<\/td><td>Redis, DynamoDB<\/td><\/tr>\n <tr><td>Column-Family<\/td><td>Data grouped by columns, not rows<\/td><td>Cassandra, HBase<\/td><\/tr>\n <tr><td>Graph Database<\/td><td>Nodes and edges for relationship data<\/td><td>Neo4j, ArangoDB<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> SQL is great for structured data with complex queries. NoSQL is better for large-scale, flexible, or real-time data like social media feeds.<\/div>\n\n <h3>Distributed Databases<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Concept<\/th><th>Meaning<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Replication<\/td><td>Same data copied to multiple servers for reliability<\/td><\/tr>\n <tr><td>Sharding \/ Partitioning<\/td><td>Data split across servers based on a key (e.g., by region)<\/td><\/tr>\n <tr><td>CAP Theorem<\/td><td>A distributed system can only guarantee 2 of 3: Consistency, Availability, Partition Tolerance<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>\ud83c\udfaf Final Project \u2014 University Database System<\/h3>\n <p>Design and implement a complete University Database using all concepts learned in this course.<\/p>\n <div class=\"project-features\">\n <div class=\"project-feat\">\u2705 Design ER Diagram (6+ entities)<\/div>\n <div class=\"project-feat\">\u2705 Normalize all tables to 3NF<\/div>\n <div class=\"project-feat\">\u2705 Create tables with keys & constraints<\/div>\n <div class=\"project-feat\">\u2705 Write JOIN & GROUP BY queries<\/div>\n <div class=\"project-feat\">\u2705 GPA calculation query<\/div>\n <div class=\"project-feat\">\u2705 Create indexes on key columns<\/div>\n <div class=\"project-feat\">\u2705 Create views for access control<\/div>\n <div class=\"project-feat\">\u2705 Set up users with limited privileges<\/div>\n <\/div>\n\n <h3>Starter Schema<\/h3>\n <div class=\"code-block\"><pre><span class=\"keyword\">CREATE TABLE<\/span> Department (\n dept_id <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n dept_name <span class=\"keyword\">VARCHAR<\/span>(50)\n);\n\n<span class=\"keyword\">CREATE TABLE<\/span> Student (\n roll_no <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n name <span class=\"keyword\">VARCHAR<\/span>(50),\n cgpa <span class=\"keyword\">FLOAT<\/span>,\n dept_id <span class=\"keyword\">INT<\/span>,\n <span class=\"keyword\">FOREIGN KEY<\/span> (dept_id) <span class=\"keyword\">REFERENCES<\/span> Department(dept_id)\n);\n\n<span class=\"keyword\">CREATE TABLE<\/span> Course (\n course_id <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n title <span class=\"keyword\">VARCHAR<\/span>(100),\n credits <span class=\"keyword\">INT<\/span>,\n dept_id <span class=\"keyword\">INT<\/span>\n);\n\n<span class=\"keyword\">CREATE TABLE<\/span> Enrollment (\n roll_no <span class=\"keyword\">INT<\/span>,\n course_id <span class=\"keyword\">INT<\/span>,\n grade <span class=\"keyword\">CHAR<\/span>(2),\n <span class=\"keyword\">PRIMARY KEY<\/span> (roll_no, course_id),\n <span class=\"keyword\">FOREIGN KEY<\/span> (roll_no) <span class=\"keyword\">REFERENCES<\/span> Student(roll_no),\n <span class=\"keyword\">FOREIGN KEY<\/span> (course_id) <span class=\"keyword\">REFERENCES<\/span> Course(course_id)\n);<\/pre><\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"congrats\">\n <h2>\ud83c\udf89 Congratulations!<\/h2>\n <p>You have completed the <strong>Database Systems Course<\/strong> by Pak Notes Hub!<\/p>\n <p style=\"margin-top:.5rem; font-size:.9rem;\">You now know ER Diagrams, SQL, Normalization, Transactions, Indexing, and Security.<\/p>\n <\/div>\n\n \n <div class=\"unit\">\n <div class=\"unit-header\"><div><h2>\ud83d\udcca Course Summary<\/h2><\/div><\/div>\n <div class=\"unit-body\">\n <table class=\"data-table\">\n <thead><tr><th>#<\/th><th>Unit<\/th><th>Key Skill<\/th><\/tr><\/thead>\n <tbody>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">1<\/td><td>Introduction to Databases<\/td><td>DBMS concepts & roles<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">2<\/td><td>Data Models<\/td><td>ER & Relational models<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">3<\/td><td>ER Diagrams<\/td><td>Database design & diagramming<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">4<\/td><td>Relational Model<\/td><td>Keys & Relational Algebra<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">5<\/td><td>SQL Basics<\/td><td>DDL, DML, SELECT<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">6<\/td><td>SQL Advanced<\/td><td>JOINs, Aggregates, Subqueries<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">7<\/td><td>Normalization<\/td><td>1NF, 2NF, 3NF, BCNF<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">8<\/td><td>Transactions<\/td><td>ACID & Concurrency<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">9<\/td><td>Indexing & Hashing<\/td><td>Query performance<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">10<\/td><td>Query Optimization<\/td><td>EXPLAIN & best practices<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">11<\/td><td>Security<\/td><td>Privileges, Views, SQL Injection<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">12<\/td><td>Emerging Trends<\/td><td>NoSQL, Distributed DB, Project<\/td><\/tr>\n <\/tbody>\n <\/table>\n <\/div>\n <\/div>\n\n <p style=\"text-align:center; color:var(--mid); font-size:.85rem; padding-bottom:1rem;\">\n www.paknoteshub.online \u00b7 More courses coming soon!\n <\/p>\n\n <\/main>\n<\/div>\n\n<button id=\"back-top\" onclick=\"window.scrollTo({top:0,behavior:'smooth'})\">\u2191<\/button>\n\n<script>\n const progress = document.getElementById('progress');\n window.addEventListener('scroll', () => {\n const h = document.documentElement;\n progress.style.width = (h.scrollTop \/ (h.scrollHeight - h.clientHeight) * 100) + '%';\n document.getElementById('back-top').classList.toggle('visible', h.scrollTop > 400);\n });\n const units = document.querySelectorAll('.unit, #toc-section');\n const navLinks = document.querySelectorAll('.toc-list a');\n new IntersectionObserver((entries) => {\n entries.forEach(e => {\n if (e.isIntersecting) {\n navLinks.forEach(a => a.classList.remove('active'));\n const a = document.querySelector(`.toc-list a[href=\"#${e.target.id}\"]`);\n if (a) a.classList.add('active');\n }\n });\n }, { rootMargin: '-20% 0px -70% 0px' }).observe && units.forEach(u => {\n new IntersectionObserver((entries) => {\n entries.forEach(e => {\n if (e.isIntersecting) {\n navLinks.forEach(a => a.classList.remove('active'));\n const a = document.querySelector(`.toc-list a[href=\"#${e.target.id}\"]`);\n if (a) a.classList.add('active');\n }\n });\n }, { rootMargin: '-20% 0px -70% 0px' }).observe(u);\n });\n<\/script>\n<\/body>\n<\/html>\n\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Database Systems Course \u2013 Pak Notes Hub Pak Notes Hub Course Outline Start Learning Normalization Final Project \ud83d\udcd8 University Level Course Database SystemsComplete Course ER Diagrams \u00b7 SQL \u00b7 Normalization \u00b7 Transactions \u00b7 Indexing \u00b7 Security \u2014 All in Easy English \ud83d\udcca 12 Units \ud83c\udf93 University Level \ud83d\udcbb SQL Examples \ud83d\udcdd Practice Tasks \ud83d\ude80 Final […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_angie_page":false,"footnotes":""},"class_list":["post-289","page","type-page","status-publish","hentry"],"_hostinger_reach_plugin_has_subscription_block":false,"_hostinger_reach_plugin_is_elementor":false,"_links":{"self":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages\/289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=289"}],"version-history":[{"count":12,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages\/289\/revisions"}],"predecessor-version":[{"id":355,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages\/289\/revisions\/355"}],"wp:attachment":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

\n\t\t\t\t

\n\t\t\t\t\t

\n\t\t\t\t

\n\t\t\t\t\t\t\t\t\t\n\n\n\n \n \n Database Systems Course \u2013 Pak Notes Hub<\/title>\n <link href=\"https:\/\/fonts.googleapis.com\/css2?family=Inter:wght@400;500;600;700&family=Fira+Code:wght@400;500&display=swap\" rel=\"stylesheet\"\/>\n <style>\n *, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }\n :root {\n --green: #1a7a4a;\n --green-dark: #145f38;\n --green-light:#e8f5ee;\n --teal: #17a589;\n --accent: #f0b127;\n --dark: #1c2833;\n --mid: #566573;\n --light: #f4f6f7;\n --code-bg: #f0f4f8;\n --code-border:#2e86c1;\n --note-bg: #fef9e7;\n --note-border:#f0b127;\n --white: #ffffff;\n --radius: 10px;\n --shadow: 0 4px 24px rgba(26,122,74,.10);\n }\n html { scroll-behavior: smooth; }\n body { font-family: 'Inter', sans-serif; background: #f2f6f3; color: var(--dark); line-height: 1.7; font-size: 15px; }\n\n \/* NAV *\/\n nav { position: sticky; top: 0; z-index: 100; background: var(--green-dark); padding: 0 2rem; display: flex; align-items: center; justify-content: space-between; height: 56px; box-shadow: 0 2px 12px rgba(0,0,0,.25); }\n .nav-brand { color: #fff; font-weight: 700; font-size: 1.05rem; }\n .nav-brand span { color: #a9dfbf; }\n .nav-links { display: flex; gap: 1.2rem; }\n .nav-links a { color: #d5f5e3; font-size: .85rem; font-weight: 500; text-decoration: none; transition: color .2s; }\n .nav-links a:hover { color: #fff; }\n\n \/* HERO *\/\n .hero { background: linear-gradient(135deg, var(--green) 0%, #145f38 60%, #0d4527 100%); padding: 5rem 2rem 4rem; text-align: center; position: relative; overflow: hidden; }\n .hero::before { content: 'SQL'; position: absolute; right: 6%; top: 50%; transform: translateY(-50%); font-size: 9rem; font-family: 'Fira Code', monospace; color: rgba(255,255,255,.06); font-weight: 700; pointer-events: none; }\n .hero-tag { display: inline-block; background: rgba(255,255,255,.12); color: #a9dfbf; font-size: .78rem; font-weight: 600; letter-spacing: 1.5px; text-transform: uppercase; padding: .35rem 1rem; border-radius: 100px; margin-bottom: 1.2rem; border: 1px solid rgba(169,223,163,.3); }\n .hero h1 { color: #fff; font-size: clamp(2rem, 5vw, 3.2rem); font-weight: 700; line-height: 1.15; margin-bottom: .8rem; }\n .hero h1 span { color: #a9dfbf; }\n .hero p { color: #d5f5e3; font-size: 1.05rem; max-width: 560px; margin: 0 auto 2rem; }\n .hero-pills { display: flex; flex-wrap: wrap; justify-content: center; gap: .7rem; }\n .pill { background: rgba(255,255,255,.13); color: #e8f5ee; padding: .45rem 1.1rem; border-radius: 100px; font-size: .82rem; font-weight: 500; border: 1px solid rgba(255,255,255,.18); }\n\n \/* LAYOUT *\/\n .page-wrap { max-width: 920px; margin: 0 auto; padding: 2.5rem 1.5rem 4rem; display: grid; grid-template-columns: 235px 1fr; gap: 2.5rem; align-items: start; }\n\n \/* SIDEBAR *\/\n .sidebar { position: sticky; top: 72px; background: var(--white); border-radius: var(--radius); box-shadow: var(--shadow); overflow: hidden; }\n .sidebar-title { background: var(--green); color: #fff; font-size: .75rem; font-weight: 700; letter-spacing: 1.2px; text-transform: uppercase; padding: .85rem 1.1rem; }\n .toc-list { list-style: none; padding: .5rem 0; }\n .toc-list li a { display: flex; align-items: center; gap: .55rem; padding: .42rem 1.1rem; color: var(--mid); font-size: .81rem; font-weight: 500; text-decoration: none; transition: all .18s; border-left: 3px solid transparent; }\n .toc-list li a:hover, .toc-list li a.active { color: var(--green); background: var(--green-light); border-left-color: var(--green); }\n .toc-num { display: inline-flex; align-items: center; justify-content: center; min-width: 20px; height: 20px; border-radius: 4px; background: var(--green-light); color: var(--green); font-size: .7rem; font-weight: 700; flex-shrink: 0; }\n .toc-list li a.active .toc-num { background: var(--green); color: #fff; }\n\n \/* UNIT CARD *\/\n .unit { background: var(--white); border-radius: var(--radius); box-shadow: var(--shadow); margin-bottom: 2.5rem; overflow: hidden; scroll-margin-top: 72px; }\n .unit-header { background: linear-gradient(100deg, var(--green) 0%, #1d9560 100%); padding: 1.4rem 1.8rem; }\n .unit-num { background: rgba(255,255,255,.18); color: #a9dfbf; font-size: .72rem; font-weight: 700; letter-spacing: 1px; text-transform: uppercase; padding: .3rem .75rem; border-radius: 100px; }\n .unit-header h2 { color: #fff; font-size: 1.25rem; font-weight: 700; line-height: 1.25; margin-top: .35rem; }\n .unit-header p { color: #c8f0d8; font-size: .85rem; margin-top: .2rem; }\n .unit-body { padding: 1.8rem; }\n\n \/* SECTION HEADING *\/\n .unit-body h3 { font-size: 1rem; font-weight: 700; color: var(--green); margin: 1.6rem 0 .7rem; padding-left: .85rem; border-left: 3px solid var(--green); line-height: 1.3; }\n .unit-body h3:first-child { margin-top: 0; }\n .unit-body p { color: var(--dark); margin-bottom: .7rem; }\n\n \/* BULLETS *\/\n .unit-body ul { list-style: none; padding-left: 0; margin: .5rem 0 1rem; }\n .unit-body ul li { padding: .3rem 0 .3rem 1.5rem; position: relative; color: var(--dark); font-size: .93rem; }\n .unit-body ul li::before { content: '\u27a4'; position: absolute; left: 0; color: var(--teal); font-size: .75rem; top: .45rem; }\n\n \/* CODE BLOCK *\/\n .code-block { background: var(--code-bg); border-left: 4px solid var(--code-border); border-radius: 6px; padding: 1.1rem 1.3rem; margin: .9rem 0 1rem; overflow-x: auto; }\n .code-block pre { font-family: 'Fira Code', monospace; font-size: .83rem; color: #1a3a54; line-height: 1.65; white-space: pre; }\n .comment { color: #7f8c8d; } .keyword { color: #1a5276; font-weight: 600; } .string { color: #1e8449; }\n\n \/* TABLES *\/\n .data-table { width: 100%; border-collapse: collapse; margin: .9rem 0 1.2rem; font-size: .87rem; border-radius: 8px; overflow: hidden; box-shadow: 0 1px 6px rgba(0,0,0,.06); }\n .data-table thead th { background: var(--green); color: #fff; padding: .7rem 1rem; text-align: left; font-weight: 600; font-size: .82rem; }\n .data-table tbody tr:nth-child(even) { background: var(--green-light); }\n .data-table tbody tr:hover { background: #d4efdf; }\n .data-table td { padding: .6rem 1rem; border-bottom: 1px solid #dce8e0; vertical-align: top; }\n .data-table td:first-child { font-weight: 600; color: var(--green-dark); }\n\n \/* INFO BOX *\/\n .info-box { background: var(--note-bg); border: 1px solid var(--note-border); border-radius: 7px; padding: .85rem 1.1rem; margin: .9rem 0; font-size: .88rem; line-height: 1.6; color: #7d6608; display: flex; align-items: flex-start; gap: .7rem; }\n .info-box .icon { font-size: 1rem; flex-shrink: 0; margin-top: .08rem; }\n\n \/* PRACTICE *\/\n .practice { background: var(--green-light); border: 1.5px dashed var(--green); border-radius: 8px; padding: .85rem 1.1rem; margin-top: 1.4rem; color: var(--green-dark); font-size: .88rem; font-weight: 500; }\n\n \/* TOC CARD *\/\n #toc-section { background: var(--white); border-radius: var(--radius); box-shadow: var(--shadow); margin-bottom: 2.5rem; overflow: hidden; }\n .toc-card-header { background: linear-gradient(100deg, var(--green) 0%, #1d9560 100%); padding: 1.2rem 1.8rem; }\n .toc-card-header h2 { color: #fff; font-size: 1.2rem; font-weight: 700; }\n .toc-card-body { padding: 1.4rem 1.8rem; }\n .toc-grid { display: grid; grid-template-columns: repeat(auto-fill, minmax(250px, 1fr)); gap: .6rem; }\n .toc-item { background: var(--light); border-radius: 7px; padding: .65rem 1rem; display: flex; align-items: center; gap: .75rem; text-decoration: none; color: var(--dark); font-size: .86rem; font-weight: 500; border: 1px solid #d5e5db; transition: all .18s; }\n .toc-item:hover { background: var(--green-light); border-color: var(--green); color: var(--green); transform: translateY(-1px); }\n .toc-badge { background: var(--green); color: #fff; font-size: .7rem; font-weight: 700; min-width: 22px; height: 22px; border-radius: 5px; display: inline-flex; align-items: center; justify-content: center; flex-shrink: 0; }\n\n \/* PROJECT FEATURES *\/\n .project-features { display: grid; grid-template-columns: repeat(auto-fill, minmax(210px, 1fr)); gap: .6rem; margin: .9rem 0 1.2rem; }\n .project-feat { background: var(--green-light); border-radius: 7px; padding: .6rem .9rem; font-size: .85rem; display: flex; align-items: center; gap: .5rem; color: var(--green-dark); border: 1px solid #a2d9b5; font-weight: 500; }\n\n \/* CONGRATS *\/\n .congrats { background: linear-gradient(135deg, var(--green) 0%, #145f38 100%); border-radius: var(--radius); padding: 2.5rem 2rem; text-align: center; color: #fff; margin-bottom: 2.5rem; box-shadow: var(--shadow); }\n .congrats h2 { font-size: 1.8rem; margin-bottom: .5rem; }\n .congrats p { color: #d5f5e3; font-size: 1rem; }\n\n \/* PROGRESS + BACK TOP *\/\n .progress-bar { position: fixed; top: 0; left: 0; height: 3px; background: var(--accent); z-index: 200; transition: width .1s linear; width: 0%; }\n #back-top { position: fixed; bottom: 2rem; right: 2rem; background: var(--green); color: #fff; width: 42px; height: 42px; border-radius: 50%; border: none; cursor: pointer; font-size: 1.1rem; box-shadow: 0 4px 14px rgba(26,122,74,.35); display: flex; align-items: center; justify-content: center; opacity: 0; transition: opacity .25s, transform .25s; pointer-events: none; }\n #back-top.visible { opacity: 1; pointer-events: auto; }\n #back-top:hover { transform: translateY(-2px); }\n\n \/* RESPONSIVE *\/\n @media (max-width: 720px) { .page-wrap { grid-template-columns: 1fr; } .sidebar { position: static; display: none; } .hero::before { display: none; } nav .nav-links { display: none; } }\n <\/style>\n<\/head>\n<body>\n\n<div class=\"progress-bar\" id=\"progress\"><\/div>\n\n\n<nav>\n <div class=\"nav-brand\">Pak <span>Notes Hub<\/span><\/div>\n <div class=\"nav-links\">\n <a href=\"#toc-section\">Course Outline<\/a>\n <a href=\"#unit-1\">Start Learning<\/a>\n <a href=\"#unit-7\">Normalization<\/a>\n <a href=\"#unit-12\">Final Project<\/a>\n <\/div>\n<\/nav>\n\n\n<section class=\"hero\">\n <div class=\"hero-tag\">\ud83d\udcd8 University Level Course<\/div>\n <h1>Database Systems<br\/><span>Complete Course<\/span><\/h1>\n <p>ER Diagrams \u00b7 SQL \u00b7 Normalization \u00b7 Transactions \u00b7 Indexing \u00b7 Security \u2014 All in Easy English<\/p>\n <div class=\"hero-pills\">\n <span class=\"pill\">\ud83d\udcca 12 Units<\/span>\n <span class=\"pill\">\ud83c\udf93 University Level<\/span>\n <span class=\"pill\">\ud83d\udcbb SQL Examples<\/span>\n <span class=\"pill\">\ud83d\udcdd Practice Tasks<\/span>\n <span class=\"pill\">\ud83d\ude80 Final Project<\/span>\n <\/div>\n<\/section>\n\n<div class=\"page-wrap\">\n\n \n <aside class=\"sidebar\">\n <div class=\"sidebar-title\">\ud83d\udccb Course Outline<\/div>\n <ul class=\"toc-list\" id=\"toc-nav\">\n <li><a href=\"#toc-section\"><span class=\"toc-num\">\ud83d\udccb<\/span> Contents<\/a><\/li>\n <li><a href=\"#unit-1\"><span class=\"toc-num\">1<\/span> Introduction<\/a><\/li>\n <li><a href=\"#unit-2\"><span class=\"toc-num\">2<\/span> Data Models<\/a><\/li>\n <li><a href=\"#unit-3\"><span class=\"toc-num\">3<\/span> ER Diagrams<\/a><\/li>\n <li><a href=\"#unit-4\"><span class=\"toc-num\">4<\/span> Relational Model<\/a><\/li>\n <li><a href=\"#unit-5\"><span class=\"toc-num\">5<\/span> SQL Basics<\/a><\/li>\n <li><a href=\"#unit-6\"><span class=\"toc-num\">6<\/span> SQL Advanced<\/a><\/li>\n <li><a href=\"#unit-7\"><span class=\"toc-num\">7<\/span> Normalization<\/a><\/li>\n <li><a href=\"#unit-8\"><span class=\"toc-num\">8<\/span> Transactions<\/a><\/li>\n <li><a href=\"#unit-9\"><span class=\"toc-num\">9<\/span> Indexing<\/a><\/li>\n <li><a href=\"#unit-10\"><span class=\"toc-num\">10<\/span> Query Optimization<\/a><\/li>\n <li><a href=\"#unit-11\"><span class=\"toc-num\">11<\/span> Security<\/a><\/li>\n <li><a href=\"#unit-12\"><span class=\"toc-num\">12<\/span> Trends & Project<\/a><\/li>\n <\/ul>\n <\/aside>\n\n \n <main class=\"content\">\n\n \n <div id=\"toc-section\">\n <div class=\"toc-card-header\"><h2>\ud83d\udccb Table of Contents \u2014 12 Units<\/h2><\/div>\n <div class=\"toc-card-body\">\n <div class=\"toc-grid\">\n <a class=\"toc-item\" href=\"#unit-1\"><span class=\"toc-badge\">1<\/span> Introduction to Databases<\/a>\n <a class=\"toc-item\" href=\"#unit-2\"><span class=\"toc-badge\">2<\/span> Data Models<\/a>\n <a class=\"toc-item\" href=\"#unit-3\"><span class=\"toc-badge\">3<\/span> ER Diagrams<\/a>\n <a class=\"toc-item\" href=\"#unit-4\"><span class=\"toc-badge\">4<\/span> Relational Model<\/a>\n <a class=\"toc-item\" href=\"#unit-5\"><span class=\"toc-badge\">5<\/span> SQL \u2013 Basics<\/a>\n <a class=\"toc-item\" href=\"#unit-6\"><span class=\"toc-badge\">6<\/span> SQL \u2013 Advanced<\/a>\n <a class=\"toc-item\" href=\"#unit-7\"><span class=\"toc-badge\">7<\/span> Normalization<\/a>\n <a class=\"toc-item\" href=\"#unit-8\"><span class=\"toc-badge\">8<\/span> Transaction Management<\/a>\n <a class=\"toc-item\" href=\"#unit-9\"><span class=\"toc-badge\">9<\/span> Indexing & Hashing<\/a>\n <a class=\"toc-item\" href=\"#unit-10\"><span class=\"toc-badge\">10<\/span> Query Optimization<\/a>\n <a class=\"toc-item\" href=\"#unit-11\"><span class=\"toc-badge\">11<\/span> Security & Authorization<\/a>\n <a class=\"toc-item\" href=\"#unit-12\"><span class=\"toc-badge\">12<\/span> Emerging Trends & Project<\/a>\n <\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-1\">\n <div class=\"unit-header\">\n <span class=\"unit-num\">Unit 1<\/span>\n <h2>Introduction to Databases<\/h2>\n <p>Start from the very foundation.<\/p>\n <\/div>\n <div class=\"unit-body\">\n <h3>What is a Database?<\/h3>\n <p>A <strong>database<\/strong> is an organized collection of related data stored so it can be easily accessed, managed, and updated. Think of it as a very powerful, structured digital filing cabinet used by software applications.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Example: When you log into your university portal, your name, roll number, and grades are all stored in a database.<\/div>\n\n <h3>DBMS vs File System<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Feature<\/th><th>File System<\/th><th>DBMS<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Data Redundancy<\/td><td>High \u2014 data repeated in multiple files<\/td><td>Low \u2014 centralized storage<\/td><\/tr>\n <tr><td>Data Inconsistency<\/td><td>Common problem<\/td><td>Controlled by integrity constraints<\/td><\/tr>\n <tr><td>Data Access<\/td><td>Manual search through files<\/td><td>Powerful query language (SQL)<\/td><\/tr>\n <tr><td>Security<\/td><td>Limited file-level permissions<\/td><td>Fine-grained user access control<\/td><\/tr>\n <tr><td>Concurrent Access<\/td><td>No built-in support<\/td><td>Multi-user access with locking<\/td><\/tr>\n <tr><td>Recovery<\/td><td>Manual backup only<\/td><td>Automatic crash recovery<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Types of Databases<\/h3>\n <ul>\n <li><strong>Relational Database<\/strong> \u2014 Data stored in tables (e.g., MySQL, PostgreSQL, Oracle)<\/li>\n <li><strong>NoSQL Database<\/strong> \u2014 Flexible schema for unstructured data (e.g., MongoDB, Cassandra)<\/li>\n <li><strong>Hierarchical Database<\/strong> \u2014 Tree-like structure (e.g., IBM IMS)<\/li>\n <li><strong>Network Database<\/strong> \u2014 Graph-like structure with multiple parent-child links<\/li>\n <li><strong>Cloud Database<\/strong> \u2014 Hosted on cloud platforms (e.g., AWS RDS, Google Firestore)<\/li>\n <\/ul>\n\n <h3>Database Users & Roles<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Role<\/th><th>Responsibility<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Database Administrator (DBA)<\/td><td>Manages the DBMS, users, backups, and performance<\/td><\/tr>\n <tr><td>Database Designer<\/td><td>Designs the schema and ER diagrams<\/td><\/tr>\n <tr><td>Application Developer<\/td><td>Writes code that interacts with the database<\/td><\/tr>\n <tr><td>End User<\/td><td>Uses the application to view or enter data<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> List 3 real-world examples of databases used in daily life and identify which type each one is.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-2\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 2<\/span><h2>Data Models<\/h2><p>Different ways to represent and organize data.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is a Data Model?<\/h3>\n <p>A <strong>data model<\/strong> is a conceptual framework that defines how data is structured, stored, and manipulated. It acts as the blueprint of a database system.<\/p>\n\n <h3>Relational Model<\/h3>\n <p>The most widely used model. Data is organized into <strong>tables (relations)<\/strong> with rows (tuples) and columns (attributes). Tables are linked using <strong>keys<\/strong>.<\/p>\n <div class=\"code-block\"><pre>Table: Student\n+----------+-----------+-----+\n| roll_no | name | age |\n+----------+-----------+-----+\n| 101 | Ahmad Ali | 20 |\n| 102 | Sara Khan | 21 |\n+----------+-----------+-----+<\/pre><\/div>\n\n <h3>Entity-Relationship (ER) Model<\/h3>\n <p>A high-level conceptual model used during <strong>database design<\/strong>. It describes data in terms of <strong>entities<\/strong>, their <strong>attributes<\/strong>, and the <strong>relationships<\/strong> between them.<\/p>\n\n <h3>Comparison of Data Models<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Model<\/th><th>Structure<\/th><th>Best Used For<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Hierarchical<\/td><td>Tree (parent-child)<\/td><td>Structured, predictable data<\/td><td>IBM IMS<\/td><\/tr>\n <tr><td>Network<\/td><td>Graph (multi-parent)<\/td><td>Complex relationships<\/td><td>IDS<\/td><\/tr>\n <tr><td>Relational<\/td><td>Tables<\/td><td>Most business applications<\/td><td>MySQL, Oracle<\/td><\/tr>\n <tr><td>Object-Oriented<\/td><td>Objects & classes<\/td><td>Complex\/multimedia data<\/td><td>db4o<\/td><\/tr>\n <tr><td>NoSQL<\/td><td>Flexible (doc\/key-val)<\/td><td>Big data, web apps<\/td><td>MongoDB, Redis<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Draw the relational model for a simple Library system with Books and Members tables.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-3\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 3<\/span><h2>ER Diagrams<\/h2><p>Visualize your database before building it.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is an ER Diagram?<\/h3>\n <p>An <strong>Entity-Relationship (ER) Diagram<\/strong> is a visual diagram that shows the structure of a database. It is created <strong>before writing any SQL code<\/strong> and helps in planning.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Think of an ER Diagram like an architect’s blueprint \u2014 you design the house on paper before building it.<\/div>\n\n <h3>ER Diagram Symbols<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Symbol<\/th><th>Shape<\/th><th>Meaning<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Entity<\/td><td>Rectangle<\/td><td>A real-world object (e.g., Student, Course)<\/td><\/tr>\n <tr><td>Attribute<\/td><td>Ellipse \/ Oval<\/td><td>Property of an entity (e.g., name, age)<\/td><\/tr>\n <tr><td>Primary Key Attribute<\/td><td>Underlined Ellipse<\/td><td>Uniquely identifies each record<\/td><\/tr>\n <tr><td>Relationship<\/td><td>Diamond<\/td><td>Association between two entities<\/td><\/tr>\n <tr><td>Weak Entity<\/td><td>Double Rectangle<\/td><td>Entity that depends on another entity<\/td><\/tr>\n <tr><td>Multi-valued Attribute<\/td><td>Double Ellipse<\/td><td>Attribute with multiple values (e.g., phone numbers)<\/td><\/tr>\n <tr><td>Derived Attribute<\/td><td>Dashed Ellipse<\/td><td>Value calculated from another attribute (e.g., age from DOB)<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Cardinality (Relationship Types)<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Type<\/th><th>Notation<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>One-to-One (1:1)<\/td><td>1 \u2500\u2500 1<\/td><td>One person has one passport<\/td><\/tr>\n <tr><td>One-to-Many (1:N)<\/td><td>1 \u2500\u2500< N<\/td><td>One teacher teaches many students<\/td><\/tr>\n <tr><td>Many-to-Many (M:N)<\/td><td>M >\u2500\u2500< N<\/td><td>Students enroll in many courses; courses have many students<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Weak Entities<\/h3>\n <p>A <strong>weak entity<\/strong> cannot exist without depending on a <strong>strong entity<\/strong>. It does not have its own primary key \u2014 it uses a <strong>partial key<\/strong> combined with the strong entity’s key.<\/p>\n <div class=\"code-block\"><pre>Strong Entity: Employee (emp_id, name)\nWeak Entity: Dependent (dep_name, relation)\n \u2014 Cannot exist without Employee\n \u2014 Identified by: emp_id + dep_name<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Draw an ER Diagram for a University system with Entities: Student, Course, Professor, Department.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-4\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 4<\/span><h2>Relational Model<\/h2><p>The mathematical foundation of SQL databases.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Key Terminology<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Term<\/th><th>Meaning<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Relation<\/td><td>A table<\/td><td>Student table<\/td><\/tr>\n <tr><td>Tuple<\/td><td>A row in a table<\/td><td>One student’s record<\/td><\/tr>\n <tr><td>Attribute<\/td><td>A column in a table<\/td><td>name, age, roll_no<\/td><\/tr>\n <tr><td>Domain<\/td><td>Allowed values for an attribute<\/td><td>age: integers 1\u2013120<\/td><\/tr>\n <tr><td>Degree<\/td><td>Number of attributes in a relation<\/td><td>Student has 4 attributes \u2192 degree 4<\/td><\/tr>\n <tr><td>Cardinality<\/td><td>Number of tuples in a relation<\/td><td>50 students \u2192 cardinality 50<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Types of Keys<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Key Type<\/th><th>Definition<\/th><th>Example<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Super Key<\/td><td>Any set of attributes that uniquely identifies a tuple<\/td><td>{roll_no}, {roll_no, name}<\/td><\/tr>\n <tr><td>Candidate Key<\/td><td>Minimal super key (no redundant attributes)<\/td><td>{roll_no}, {email}<\/td><\/tr>\n <tr><td>Primary Key<\/td><td>Chosen candidate key for identification<\/td><td>{roll_no}<\/td><\/tr>\n <tr><td>Foreign Key<\/td><td>Attribute referencing primary key of another table<\/td><td>dept_id in Student refs Department<\/td><\/tr>\n <tr><td>Composite Key<\/td><td>Primary key made of two or more attributes<\/td><td>{student_id + course_id}<\/td><\/tr>\n <tr><td>Alternate Key<\/td><td>Candidate keys not chosen as primary key<\/td><td>{email} if roll_no is PK<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Relational Algebra \u2014 Basic Operations<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Operation<\/th><th>Symbol<\/th><th>Purpose<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Select<\/td><td>\u03c3 (sigma)<\/td><td>Filter rows based on a condition<\/td><\/tr>\n <tr><td>Project<\/td><td>\u03c0 (pi)<\/td><td>Select specific columns<\/td><\/tr>\n <tr><td>Union<\/td><td>\u222a<\/td><td>Combine tuples from two relations<\/td><\/tr>\n <tr><td>Intersection<\/td><td>\u2229<\/td><td>Common tuples in two relations<\/td><\/tr>\n <tr><td>Difference<\/td><td>\u2212<\/td><td>Tuples in one relation but not the other<\/td><\/tr>\n <tr><td>Cartesian Product<\/td><td>\u00d7<\/td><td>Combine every tuple of two relations<\/td><\/tr>\n <tr><td>Join<\/td><td>\u22c8<\/td><td>Combine related tuples from two relations<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Given a Student(roll_no, name, dept_id) table, write Relational Algebra to get names of all students in the CS department.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-5\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 5<\/span><h2>SQL \u2013 Basics<\/h2><p>Start writing real database queries.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is SQL?<\/h3>\n <p><strong>SQL (Structured Query Language)<\/strong> is the standard language used to communicate with relational databases. It is used to create tables, insert data, and retrieve information.<\/p>\n\n <h3>DDL \u2014 Data Definition Language<\/h3>\n <p>Used to <strong>define and modify the structure<\/strong> of the database.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create a table<\/span>\n<span class=\"keyword\">CREATE TABLE<\/span> Student (\n roll_no <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n name <span class=\"keyword\">VARCHAR<\/span>(50) <span class=\"keyword\">NOT NULL<\/span>,\n age <span class=\"keyword\">INT<\/span>,\n dept_id <span class=\"keyword\">INT<\/span>,\n email <span class=\"keyword\">VARCHAR<\/span>(100) <span class=\"keyword\">UNIQUE<\/span>\n);\n\n<span class=\"comment\">-- Add a new column<\/span>\n<span class=\"keyword\">ALTER TABLE<\/span> Student <span class=\"keyword\">ADD COLUMN<\/span> cgpa <span class=\"keyword\">FLOAT<\/span>;\n\n<span class=\"comment\">-- Delete the table permanently<\/span>\n<span class=\"keyword\">DROP TABLE<\/span> Student;<\/pre><\/div>\n\n <h3>DML \u2014 Data Manipulation Language<\/h3>\n <p>Used to <strong>insert, update, and delete data<\/strong> inside tables.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Insert a record<\/span>\n<span class=\"keyword\">INSERT INTO<\/span> Student (roll_no, name, age, dept_id)\n<span class=\"keyword\">VALUES<\/span> (101, <span class=\"string\">'Ahmad Ali'<\/span>, 20, 1);\n\n<span class=\"comment\">-- Update a record<\/span>\n<span class=\"keyword\">UPDATE<\/span> Student <span class=\"keyword\">SET<\/span> age = 21 <span class=\"keyword\">WHERE<\/span> roll_no = 101;\n\n<span class=\"comment\">-- Delete a record<\/span>\n<span class=\"keyword\">DELETE FROM<\/span> Student <span class=\"keyword\">WHERE<\/span> roll_no = 101;<\/pre><\/div>\n\n <h3>SELECT \u2014 Retrieving Data<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Get all students<\/span>\n<span class=\"keyword\">SELECT<\/span> * <span class=\"keyword\">FROM<\/span> Student;\n\n<span class=\"comment\">-- Get only name and age<\/span>\n<span class=\"keyword\">SELECT<\/span> name, age <span class=\"keyword\">FROM<\/span> Student;\n\n<span class=\"comment\">-- Filter with WHERE<\/span>\n<span class=\"keyword\">SELECT<\/span> name <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">WHERE<\/span> age > 20;\n\n<span class=\"comment\">-- Sort results<\/span>\n<span class=\"keyword\">SELECT<\/span> name, cgpa <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">ORDER BY<\/span> cgpa <span class=\"keyword\">DESC<\/span>;\n\n<span class=\"comment\">-- Limit results<\/span>\n<span class=\"keyword\">SELECT<\/span> name <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">LIMIT<\/span> 10;<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Create a Department table and a Student table. Insert 5 students. Retrieve students whose age is greater than 19.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-6\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 6<\/span><h2>SQL \u2013 Advanced Queries<\/h2><p>Master JOINs, Subqueries, and Aggregates.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>JOINs \u2014 Combining Tables<\/h3>\n <p>A <strong>JOIN<\/strong> is used to combine rows from two or more tables based on a related column.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- INNER JOIN: only matching rows from both tables<\/span>\n<span class=\"keyword\">SELECT<\/span> Student.name, Department.dept_name\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">INNER JOIN<\/span> Department <span class=\"keyword\">ON<\/span> Student.dept_id = Department.dept_id;\n\n<span class=\"comment\">-- LEFT JOIN: all rows from left + matching from right<\/span>\n<span class=\"keyword\">SELECT<\/span> Student.name, Department.dept_name\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">LEFT JOIN<\/span> Department <span class=\"keyword\">ON<\/span> Student.dept_id = Department.dept_id;<\/pre><\/div>\n\n <h3>Types of JOINs<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>JOIN Type<\/th><th>Returns<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>INNER JOIN<\/td><td>Only rows where condition matches in BOTH tables<\/td><\/tr>\n <tr><td>LEFT JOIN<\/td><td>All rows from left table; NULL for non-matching right rows<\/td><\/tr>\n <tr><td>RIGHT JOIN<\/td><td>All rows from right table; NULL for non-matching left rows<\/td><\/tr>\n <tr><td>FULL OUTER JOIN<\/td><td>All rows from both tables; NULL where no match<\/td><\/tr>\n <tr><td>SELF JOIN<\/td><td>A table joined with itself<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Aggregate Functions<\/h3>\n <div class=\"code-block\"><pre><span class=\"keyword\">SELECT COUNT<\/span>(*) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Total students<\/span>\n<span class=\"keyword\">SELECT AVG<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Average CGPA<\/span>\n<span class=\"keyword\">SELECT MAX<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Highest CGPA<\/span>\n<span class=\"keyword\">SELECT MIN<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Lowest CGPA<\/span>\n<span class=\"keyword\">SELECT SUM<\/span>(fee) <span class=\"keyword\">FROM<\/span> Student; <span class=\"comment\">-- Total fee<\/span><\/pre><\/div>\n\n <h3>GROUP BY & HAVING<\/h3>\n <p><strong>GROUP BY<\/strong> groups rows. <strong>HAVING<\/strong> filters groups (like WHERE but for groups).<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Count students per department<\/span>\n<span class=\"keyword\">SELECT<\/span> dept_id, <span class=\"keyword\">COUNT<\/span>(*) <span class=\"keyword\">AS<\/span> total\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">GROUP BY<\/span> dept_id;\n\n<span class=\"comment\">-- Departments with more than 10 students<\/span>\n<span class=\"keyword\">SELECT<\/span> dept_id, <span class=\"keyword\">COUNT<\/span>(*) <span class=\"keyword\">AS<\/span> total\n<span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">GROUP BY<\/span> dept_id\n<span class=\"keyword\">HAVING COUNT<\/span>(*) > 10;<\/pre><\/div>\n\n <h3>Subqueries<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Students with CGPA higher than average<\/span>\n<span class=\"keyword\">SELECT<\/span> name <span class=\"keyword\">FROM<\/span> Student\n<span class=\"keyword\">WHERE<\/span> cgpa > (<span class=\"keyword\">SELECT AVG<\/span>(cgpa) <span class=\"keyword\">FROM<\/span> Student);<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Write a query to find the department with the highest number of students using GROUP BY and ORDER BY.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-7\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 7<\/span><h2>Normalization<\/h2><p>Organize your database to remove redundancy.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is Normalization?<\/h3>\n <p>Normalization is the process of <strong>organizing a database<\/strong> to reduce <strong>data redundancy<\/strong> (repeated data) and improve <strong>data integrity<\/strong>. It involves dividing large tables into smaller ones.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Redundancy problem: If a student’s department name is stored in every row, changing the name requires updating hundreds of rows \u2014 and errors can creep in.<\/div>\n\n <h3>Functional Dependency<\/h3>\n <p>If attribute <strong>A<\/strong> uniquely determines attribute <strong>B<\/strong>, we write: <strong>A \u2192 B<\/strong><\/p>\n <div class=\"code-block\"><pre>roll_no \u2192 name (roll_no determines name)\nroll_no \u2192 dept_id (roll_no determines dept_id)\ndept_id \u2192 dept_name (dept_id determines dept_name)<\/pre><\/div>\n\n <h3>Normal Forms<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Normal Form<\/th><th>Rule<\/th><th>Problem it Solves<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>1NF<\/td><td>All attributes must be atomic (single value). No repeating groups.<\/td><td>Removes multi-valued cells<\/td><\/tr>\n <tr><td>2NF<\/td><td>Must be in 1NF. No partial dependency \u2014 non-key attribute must depend on the FULL primary key.<\/td><td>Removes partial dependencies<\/td><\/tr>\n <tr><td>3NF<\/td><td>Must be in 2NF. No transitive dependency \u2014 non-key attribute must not depend on another non-key attribute.<\/td><td>Removes transitive dependencies<\/td><\/tr>\n <tr><td>BCNF<\/td><td>Stronger version of 3NF. For every dependency A\u2192B, A must be a super key.<\/td><td>Handles anomalies 3NF misses<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Example: Normalizing a Table<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- BEFORE (Unnormalized): Redundancy problem<\/span>\n| roll_no | name | dept_id | dept_name |\n| 101 | Ahmad | 1 | Computer |\n| 102 | Sara | 1 | Computer | \u2190 dept_name repeated!\n\n<span class=\"comment\">-- AFTER 3NF: Split into two tables<\/span>\nStudent: (roll_no, name, dept_id)\nDepartment: (dept_id, dept_name)<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Given table Order(order_id, customer_id, customer_name, product_id, product_name, qty). Normalize it to 3NF.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-8\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 8<\/span><h2>Transaction Management<\/h2><p>Ensure data stays safe and consistent.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>What is a Transaction?<\/h3>\n <p>A <strong>transaction<\/strong> is a sequence of database operations treated as a <strong>single unit<\/strong>. Either ALL operations succeed, or NONE of them are applied.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Bank Transfer Example<\/span>\n<span class=\"keyword\">BEGIN TRANSACTION<\/span>;\n <span class=\"keyword\">UPDATE<\/span> Account <span class=\"keyword\">SET<\/span> balance = balance - 5000 <span class=\"keyword\">WHERE<\/span> acc_id = 1;\n <span class=\"keyword\">UPDATE<\/span> Account <span class=\"keyword\">SET<\/span> balance = balance + 5000 <span class=\"keyword\">WHERE<\/span> acc_id = 2;\n<span class=\"keyword\">COMMIT<\/span>; <span class=\"comment\">-- Save both changes<\/span>\n\n<span class=\"comment\">-- If anything goes wrong:<\/span>\n<span class=\"keyword\">ROLLBACK<\/span>; <span class=\"comment\">-- Undo all changes<\/span><\/pre><\/div>\n\n <h3>ACID Properties<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Letter<\/th><th>Property<\/th><th>Meaning<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>A<\/td><td>Atomicity<\/td><td>All operations complete or none do \u2014 no partial updates<\/td><\/tr>\n <tr><td>C<\/td><td>Consistency<\/td><td>Database moves from one valid state to another valid state<\/td><\/tr>\n <tr><td>I<\/td><td>Isolation<\/td><td>Concurrent transactions don’t interfere with each other<\/td><\/tr>\n <tr><td>D<\/td><td>Durability<\/td><td>Once committed, changes are permanent even after system crash<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Concurrency Problems<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Problem<\/th><th>Description<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Dirty Read<\/td><td>Transaction reads data that another transaction modified but not yet committed<\/td><\/tr>\n <tr><td>Non-Repeatable Read<\/td><td>Same data read twice gives different values because another transaction modified it<\/td><\/tr>\n <tr><td>Phantom Read<\/td><td>A query returns different rows because another transaction inserted\/deleted rows<\/td><\/tr>\n <tr><td>Lost Update<\/td><td>Two transactions update the same data; one update is lost<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Deadlock<\/h3>\n <div class=\"code-block\"><pre>T1 locks Row A, waits for Row B\nT2 locks Row B, waits for Row A\n--> Neither can proceed = DEADLOCK\n\nSolutions:\n- Deadlock Prevention (acquire all locks at start)\n- Deadlock Detection (kill one transaction)\n- Timeout (abort transaction if waiting too long)<\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Explain with an example how ACID properties protect a bank transfer transaction from failure.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-9\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 9<\/span><h2>Indexing & Hashing<\/h2><p>Speed up database queries dramatically.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Why Indexing?<\/h3>\n <p>Without an index, the database <strong>scans every row<\/strong> to find data. An <strong>index<\/strong> is a special data structure that allows the database to find rows <strong>much faster<\/strong> \u2014 like an index at the back of a book.<\/p>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> Without index: Search 1,000,000 rows one by one. With index: Jump directly to the result. Speed difference: minutes vs milliseconds!<\/div>\n\n <h3>Types of Indexes<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Index Type<\/th><th>Description<\/th><th>Best For<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Clustered Index<\/td><td>Rows stored physically in index order. Only one per table.<\/td><td>Primary key lookups, range queries<\/td><\/tr>\n <tr><td>Non-Clustered Index<\/td><td>Separate structure; pointer to actual row. Many per table.<\/td><td>Frequently searched non-PK columns<\/td><\/tr>\n <tr><td>Unique Index<\/td><td>Ensures all values in indexed column are unique<\/td><td>Email, username columns<\/td><\/tr>\n <tr><td>Composite Index<\/td><td>Index on two or more columns<\/td><td>Multi-column WHERE conditions<\/td><\/tr>\n <tr><td>Full-Text Index<\/td><td>Optimized for searching inside text<\/td><td>Search engines, article content<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>SQL \u2014 Creating Indexes<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create an index<\/span>\n<span class=\"keyword\">CREATE INDEX<\/span> idx_name <span class=\"keyword\">ON<\/span> Student(name);\n\n<span class=\"comment\">-- Create unique index<\/span>\n<span class=\"keyword\">CREATE UNIQUE INDEX<\/span> idx_email <span class=\"keyword\">ON<\/span> Student(email);\n\n<span class=\"comment\">-- Drop an index<\/span>\n<span class=\"keyword\">DROP INDEX<\/span> idx_name <span class=\"keyword\">ON<\/span> Student;\n\n<span class=\"comment\">-- Check if a query uses an index<\/span>\n<span class=\"keyword\">EXPLAIN SELECT<\/span> * <span class=\"keyword\">FROM<\/span> Student <span class=\"keyword\">WHERE<\/span> name = <span class=\"string\">'Ahmad'<\/span>;<\/pre><\/div>\n\n <h3>B+ Tree vs Hash Index<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Feature<\/th><th>B+ Tree Index<\/th><th>Hash Index<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Best for<\/td><td>Range queries (>, <, BETWEEN)<\/td><td>Exact match (=) queries only<\/td><\/tr>\n <tr><td>Order<\/td><td>Maintains sorted order<\/td><td>No order maintained<\/td><\/tr>\n <tr><td>Speed<\/td><td>O(log n)<\/td><td>O(1) average<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Create indexes on a Student table for name and email columns. Use EXPLAIN to verify the query uses the index.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-10\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 10<\/span><h2>Query Processing & Optimization<\/h2><p>Understand how the database executes your queries.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Query Processing Steps<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Step<\/th><th>Name<\/th><th>What Happens<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>1<\/td><td>Parsing<\/td><td>SQL query checked for syntax errors and converted to a parse tree<\/td><\/tr>\n <tr><td>2<\/td><td>Translation<\/td><td>Parse tree converted to relational algebra expression<\/td><\/tr>\n <tr><td>3<\/td><td>Optimization<\/td><td>Query optimizer finds the most efficient execution plan<\/td><\/tr>\n <tr><td>4<\/td><td>Evaluation<\/td><td>The chosen plan is executed and results are returned<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>Using EXPLAIN<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- See execution plan in MySQL<\/span>\n<span class=\"keyword\">EXPLAIN SELECT<\/span> s.name, d.dept_name\n<span class=\"keyword\">FROM<\/span> Student s\n<span class=\"keyword\">JOIN<\/span> Department d <span class=\"keyword\">ON<\/span> s.dept_id = d.dept_id\n<span class=\"keyword\">WHERE<\/span> s.cgpa > 3.0;\n\n<span class=\"comment\">-- Key output fields:<\/span>\n<span class=\"comment\">-- type: ALL=bad (full scan), ref=good, const=best<\/span>\n<span class=\"comment\">-- key: which index is being used<\/span>\n<span class=\"comment\">-- rows: estimated rows examined<\/span><\/pre><\/div>\n\n <h3>Optimization Tips<\/h3>\n <ul>\n <li><strong>Use indexes<\/strong> on columns used in WHERE, JOIN, and ORDER BY clauses.<\/li>\n <li><strong>Avoid SELECT *<\/strong> \u2014 only retrieve columns you actually need.<\/li>\n <li><strong>Use JOINs instead of subqueries<\/strong> where possible \u2014 often faster.<\/li>\n <li><strong>Filter early<\/strong> \u2014 apply WHERE conditions before JOINs to reduce data size.<\/li>\n <li><strong>Avoid functions on indexed columns<\/strong> \u2014 WHERE YEAR(date) = 2024 disables the index.<\/li>\n <li><strong>Use LIMIT<\/strong> when you only need a few results.<\/li>\n <\/ul>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Write two versions of a query (with and without index). Use EXPLAIN to compare and explain the difference.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-11\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 11<\/span><h2>Database Security & Authorization<\/h2><p>Protect your data from unauthorized access.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>Why Database Security?<\/h3>\n <p>Databases contain sensitive information \u2014 student records, financial data, passwords. Without proper security, this data can be <strong>stolen, modified, or destroyed<\/strong> by attackers or unauthorized users.<\/p>\n\n <h3>SQL Injection Attack<\/h3>\n <p>One of the most dangerous attacks. An attacker <strong>injects malicious SQL code<\/strong> through user input fields to manipulate the database.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Vulnerable login query (bad practice):<\/span>\n<span class=\"keyword\">SELECT<\/span> * <span class=\"keyword\">FROM<\/span> users <span class=\"keyword\">WHERE<\/span> username = [user_input] <span class=\"keyword\">AND<\/span> password = [pass];\n\n<span class=\"comment\">-- Attacker types as username: ' OR 1=1 --<\/span>\n<span class=\"comment\">-- The query becomes: WHERE username = '' OR 1=1 --<\/span>\n<span class=\"comment\">-- Result: ALL users returned, login bypassed!<\/span>\n\n<span class=\"comment\">-- Safe fix: Use Prepared Statements<\/span>\n<span class=\"keyword\">SELECT<\/span> * <span class=\"keyword\">FROM<\/span> users <span class=\"keyword\">WHERE<\/span> username = ? <span class=\"keyword\">AND<\/span> password = ?;<\/pre><\/div>\n\n <h3>User Privileges (GRANT & REVOKE)<\/h3>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create a new user<\/span>\n<span class=\"keyword\">CREATE USER<\/span> <span class=\"string\">'ali'<\/span>@<span class=\"string\">'localhost'<\/span> <span class=\"keyword\">IDENTIFIED BY<\/span> <span class=\"string\">'password123'<\/span>;\n\n<span class=\"comment\">-- Give SELECT permission only<\/span>\n<span class=\"keyword\">GRANT SELECT ON<\/span> university_db.* <span class=\"keyword\">TO<\/span> <span class=\"string\">'ali'<\/span>@<span class=\"string\">'localhost'<\/span>;\n\n<span class=\"comment\">-- Give full access<\/span>\n<span class=\"keyword\">GRANT ALL PRIVILEGES ON<\/span> university_db.* <span class=\"keyword\">TO<\/span> <span class=\"string\">'admin'<\/span>@<span class=\"string\">'localhost'<\/span>;\n\n<span class=\"comment\">-- Remove a privilege<\/span>\n<span class=\"keyword\">REVOKE INSERT ON<\/span> university_db.* <span class=\"keyword\">FROM<\/span> <span class=\"string\">'ali'<\/span>@<span class=\"string\">'localhost'<\/span>;\n\n<span class=\"keyword\">FLUSH PRIVILEGES<\/span>;<\/pre><\/div>\n\n <h3>Views for Security<\/h3>\n <p>A <strong>view<\/strong> is a virtual table that shows only specific data. It hides sensitive columns from certain users.<\/p>\n <div class=\"code-block\"><pre><span class=\"comment\">-- Create a view that hides salary<\/span>\n<span class=\"keyword\">CREATE VIEW<\/span> public_employee <span class=\"keyword\">AS<\/span>\n<span class=\"keyword\">SELECT<\/span> emp_id, name, department <span class=\"keyword\">FROM<\/span> Employee;\n\n<span class=\"comment\">-- User only sees emp_id, name, department<\/span>\n<span class=\"comment\">-- salary column is completely hidden<\/span><\/pre><\/div>\n <div class=\"practice\"><strong>\u270f\ufe0f Practice Task:<\/strong> Create a user with only SELECT privilege on the Student table. Test that they cannot INSERT or DELETE data.<\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"unit\" id=\"unit-12\">\n <div class=\"unit-header\"><span class=\"unit-num\">Unit 12<\/span><h2>Emerging Trends & Final Project<\/h2><p>Modern databases and a complete real-world project.<\/p><\/div>\n <div class=\"unit-body\">\n <h3>NoSQL Databases<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>NoSQL Type<\/th><th>Description<\/th><th>Examples<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Document Store<\/td><td>Data stored as JSON-like documents<\/td><td>MongoDB, CouchDB<\/td><\/tr>\n <tr><td>Key-Value Store<\/td><td>Simple key \u2192 value pairs<\/td><td>Redis, DynamoDB<\/td><\/tr>\n <tr><td>Column-Family<\/td><td>Data grouped by columns, not rows<\/td><td>Cassandra, HBase<\/td><\/tr>\n <tr><td>Graph Database<\/td><td>Nodes and edges for relationship data<\/td><td>Neo4j, ArangoDB<\/td><\/tr>\n <\/tbody>\n <\/table>\n <div class=\"info-box\"><span class=\"icon\">\ud83d\udca1<\/span> SQL is great for structured data with complex queries. NoSQL is better for large-scale, flexible, or real-time data like social media feeds.<\/div>\n\n <h3>Distributed Databases<\/h3>\n <table class=\"data-table\">\n <thead><tr><th>Concept<\/th><th>Meaning<\/th><\/tr><\/thead>\n <tbody>\n <tr><td>Replication<\/td><td>Same data copied to multiple servers for reliability<\/td><\/tr>\n <tr><td>Sharding \/ Partitioning<\/td><td>Data split across servers based on a key (e.g., by region)<\/td><\/tr>\n <tr><td>CAP Theorem<\/td><td>A distributed system can only guarantee 2 of 3: Consistency, Availability, Partition Tolerance<\/td><\/tr>\n <\/tbody>\n <\/table>\n\n <h3>\ud83c\udfaf Final Project \u2014 University Database System<\/h3>\n <p>Design and implement a complete University Database using all concepts learned in this course.<\/p>\n <div class=\"project-features\">\n <div class=\"project-feat\">\u2705 Design ER Diagram (6+ entities)<\/div>\n <div class=\"project-feat\">\u2705 Normalize all tables to 3NF<\/div>\n <div class=\"project-feat\">\u2705 Create tables with keys & constraints<\/div>\n <div class=\"project-feat\">\u2705 Write JOIN & GROUP BY queries<\/div>\n <div class=\"project-feat\">\u2705 GPA calculation query<\/div>\n <div class=\"project-feat\">\u2705 Create indexes on key columns<\/div>\n <div class=\"project-feat\">\u2705 Create views for access control<\/div>\n <div class=\"project-feat\">\u2705 Set up users with limited privileges<\/div>\n <\/div>\n\n <h3>Starter Schema<\/h3>\n <div class=\"code-block\"><pre><span class=\"keyword\">CREATE TABLE<\/span> Department (\n dept_id <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n dept_name <span class=\"keyword\">VARCHAR<\/span>(50)\n);\n\n<span class=\"keyword\">CREATE TABLE<\/span> Student (\n roll_no <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n name <span class=\"keyword\">VARCHAR<\/span>(50),\n cgpa <span class=\"keyword\">FLOAT<\/span>,\n dept_id <span class=\"keyword\">INT<\/span>,\n <span class=\"keyword\">FOREIGN KEY<\/span> (dept_id) <span class=\"keyword\">REFERENCES<\/span> Department(dept_id)\n);\n\n<span class=\"keyword\">CREATE TABLE<\/span> Course (\n course_id <span class=\"keyword\">INT PRIMARY KEY<\/span>,\n title <span class=\"keyword\">VARCHAR<\/span>(100),\n credits <span class=\"keyword\">INT<\/span>,\n dept_id <span class=\"keyword\">INT<\/span>\n);\n\n<span class=\"keyword\">CREATE TABLE<\/span> Enrollment (\n roll_no <span class=\"keyword\">INT<\/span>,\n course_id <span class=\"keyword\">INT<\/span>,\n grade <span class=\"keyword\">CHAR<\/span>(2),\n <span class=\"keyword\">PRIMARY KEY<\/span> (roll_no, course_id),\n <span class=\"keyword\">FOREIGN KEY<\/span> (roll_no) <span class=\"keyword\">REFERENCES<\/span> Student(roll_no),\n <span class=\"keyword\">FOREIGN KEY<\/span> (course_id) <span class=\"keyword\">REFERENCES<\/span> Course(course_id)\n);<\/pre><\/div>\n <\/div>\n <\/div>\n\n \n <div class=\"congrats\">\n <h2>\ud83c\udf89 Congratulations!<\/h2>\n <p>You have completed the <strong>Database Systems Course<\/strong> by Pak Notes Hub!<\/p>\n <p style=\"margin-top:.5rem; font-size:.9rem;\">You now know ER Diagrams, SQL, Normalization, Transactions, Indexing, and Security.<\/p>\n <\/div>\n\n \n <div class=\"unit\">\n <div class=\"unit-header\"><div><h2>\ud83d\udcca Course Summary<\/h2><\/div><\/div>\n <div class=\"unit-body\">\n <table class=\"data-table\">\n <thead><tr><th>#<\/th><th>Unit<\/th><th>Key Skill<\/th><\/tr><\/thead>\n <tbody>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">1<\/td><td>Introduction to Databases<\/td><td>DBMS concepts & roles<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">2<\/td><td>Data Models<\/td><td>ER & Relational models<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">3<\/td><td>ER Diagrams<\/td><td>Database design & diagramming<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">4<\/td><td>Relational Model<\/td><td>Keys & Relational Algebra<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">5<\/td><td>SQL Basics<\/td><td>DDL, DML, SELECT<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">6<\/td><td>SQL Advanced<\/td><td>JOINs, Aggregates, Subqueries<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">7<\/td><td>Normalization<\/td><td>1NF, 2NF, 3NF, BCNF<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">8<\/td><td>Transactions<\/td><td>ACID & Concurrency<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">9<\/td><td>Indexing & Hashing<\/td><td>Query performance<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">10<\/td><td>Query Optimization<\/td><td>EXPLAIN & best practices<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">11<\/td><td>Security<\/td><td>Privileges, Views, SQL Injection<\/td><\/tr>\n <tr><td style=\"color:var(--green);font-weight:700;font-family:inherit\">12<\/td><td>Emerging Trends<\/td><td>NoSQL, Distributed DB, Project<\/td><\/tr>\n <\/tbody>\n <\/table>\n <\/div>\n <\/div>\n\n <p style=\"text-align:center; color:var(--mid); font-size:.85rem; padding-bottom:1rem;\">\n www.paknoteshub.online \u00b7 More courses coming soon!\n <\/p>\n\n <\/main>\n<\/div>\n\n<button id=\"back-top\" onclick=\"window.scrollTo({top:0,behavior:'smooth'})\">\u2191<\/button>\n\n<script>\n const progress = document.getElementById('progress');\n window.addEventListener('scroll', () => {\n const h = document.documentElement;\n progress.style.width = (h.scrollTop \/ (h.scrollHeight - h.clientHeight) * 100) + '%';\n document.getElementById('back-top').classList.toggle('visible', h.scrollTop > 400);\n });\n const units = document.querySelectorAll('.unit, #toc-section');\n const navLinks = document.querySelectorAll('.toc-list a');\n new IntersectionObserver((entries) => {\n entries.forEach(e => {\n if (e.isIntersecting) {\n navLinks.forEach(a => a.classList.remove('active'));\n const a = document.querySelector(`.toc-list a[href=\"#${e.target.id}\"]`);\n if (a) a.classList.add('active');\n }\n });\n }, { rootMargin: '-20% 0px -70% 0px' }).observe && units.forEach(u => {\n new IntersectionObserver((entries) => {\n entries.forEach(e => {\n if (e.isIntersecting) {\n navLinks.forEach(a => a.classList.remove('active'));\n const a = document.querySelector(`.toc-list a[href=\"#${e.target.id}\"]`);\n if (a) a.classList.add('active');\n }\n });\n }, { rootMargin: '-20% 0px -70% 0px' }).observe(u);\n });\n<\/script>\n<\/body>\n<\/html>\n\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Database Systems Course \u2013 Pak Notes Hub Pak Notes Hub Course Outline Start Learning Normalization Final Project \ud83d\udcd8 University Level Course Database SystemsComplete Course ER Diagrams \u00b7 SQL \u00b7 Normalization \u00b7 Transactions \u00b7 Indexing \u00b7 Security \u2014 All in Easy English \ud83d\udcca 12 Units \ud83c\udf93 University Level \ud83d\udcbb SQL Examples \ud83d\udcdd Practice Tasks \ud83d\ude80 Final […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_angie_page":false,"footnotes":""},"class_list":["post-289","page","type-page","status-publish","hentry"],"_hostinger_reach_plugin_has_subscription_block":false,"_hostinger_reach_plugin_is_elementor":false,"_links":{"self":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages\/289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=289"}],"version-history":[{"count":12,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages\/289\/revisions"}],"predecessor-version":[{"id":355,"href":"https:\/\/paknoteshub.online\/index.php?rest_route=\/wp\/v2\/pages\/289\/revisions\/355"}],"wp:attachment":[{"href":"https:\/\/paknoteshub.online\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}