TheHingineer

The Hingineer

Menu

The Hingineer

Menu

  • DBMS

  • DBMS Part-1

  • DBMS Part-2

  • DBMS Part-3

  • DBMS Part-4

  • DBMS Part-5

    •  Algorithms for Select, Project and Join Operations in DBMS

     Introduction

    Relational Algebra me kuch basic operations hote hain:

    • SELECT (σ) – rows filter karta hai

    • PROJECT (π) – specific columns (attributes) choose karta hai

    • JOIN (⨝) – do tables ko combine karta hai

    Lekin real DBMS me in operations ko perform karne ke liye alag-alag algorithms use kiye jaate hain jo efficient aur fast ho.

     1. SELECT (σ) Operation

    SELECT ka use rows ko filter karne ke liye hota hai — jahan condition satisfy ho.

     Example:

    SELECT * FROM Students WHERE age > 20;
     

    Relational Algebra:

    σ_age > 20 (Students)
     

     SELECT Operation ke Algorithms

     A. Linear Search (Brute Force)

    • Kaise kaam karta hai: Poore table ki har row check karta hai.

    • Kab use karein: Jab index nahi ho.

    • Performance: Slow hota hai bade tables me.

     Example:

    Students Table → Row 1 → Row 2 → Row 3 → ... check age > 20
     

     B. Binary Search

    • Kaise kaam karta hai: Sirf tab kaam karta hai jab column sorted ho.

    • Kab use karein: Jab condition sorted data pe ho.

    • Performance: Fast hota hai.

     Example:

    SELECT * FROM Students WHERE roll_no = 105;
     

    Agar roll_no sorted hai, toh binary search ka use hoga.

     C. Index Search

    • Kaise kaam karta hai: DBMS index ka use karta hai (jaise B+ tree ya hash index).

    • Kab use karein: Jab filter column indexed ho.

    • Performance: Bahut fast.

     Example:
    Agar age pe index hai, toh age > 20 ko index se fast dhoondh sakte hain.

     2. PROJECT (π) Operation

    PROJECT ka use hota hai specific columns ko select karne ke liye.

     Example:

    SELECT name, age FROM Students;
     

    Relational Algebra:

    π_name, age (Students)
     

     PROJECT Operation ke Algorithms

     A. Simple Column Extraction

    • Kaise kaam karta hai: Har row me se sirf needed columns copy karta hai.

    • Kab use karein: Jab duplicate rows ka matter nahi ho.

     Example:

    Row: [ID, Name, Age] → Project: [Name, Age]
     

     B. With Duplicate Elimination

    • Kaise kaam karta hai:

      1. Columns extract karo

      2. Duplicate remove karo (sorting ya hashing se)

     Example:

    SELECT department FROM Students;
     

    Agar 50 students “CSE” se hain, toh output me sirf 1 “CSE” dikhna chahiye.

     3. JOIN (⨝) Operation

    JOIN ka use hota hai do tables ko jodne ke liye common column ke base par.

     Example:

    SELECT * FROM Students S JOIN Departments D ON S.dept_id = D.id;
     

    Relational Algebra:

    Students ⨝ S.dept_id = D.id Departments
     

     JOIN Operation ke Algorithms

     A. Nested Loop Join

    • Kaise kaam karta hai:
      Har row Table A se leke, Table B ki har row se compare karta hai.

    • Use kab karein: Choti tables ke liye ya jab index nahi ho.

    • Performance: Slow hota hai (O(n*m)).

     Example:

    For each Student → check every Department → agar match hua → result me lo
     

     B. Index Nested Loop Join

    • Kaise kaam karta hai:
      Har row outer table se leke, inner table pe index ka use karke match dhundhta hai.

    • Use kab karein: Jab inner table ke join column pe index ho.

    • Performance: Fast

     C. Sort-Merge Join

    • Kaise kaam karta hai:

      1. Dono tables ko join column pe sort karo

      2. Fir merge kar do jaha values match ho

     Example:
    Agar dept_id sorted hai dono tables me.

     D. Hash Join

    • Kaise kaam karta hai:

      1. Choti table ko hash table me convert karo using join key

      2. Badi table ke rows ko hash table me match karo

    • Use kab karein: Jab index aur sort nahi ho, but memory available ho.

     Example:

    Hash Students.dept_id → make hash table
    Departments ke dept_id se check karo
    Agar match hua → join karo
     

     Summary Table 

    Operation Algorithm Kab Use Karein Notes
    SELECT Linear Search Jab index na ho Slow
      Binary Search Data sorted ho Fast
      Index Search Index available ho Best
    PROJECT Simple Column Pick Duplicate matter na kare Easy
      Duplicate Remove ke saath Jab unique values chahiye Thoda slow
    JOIN Nested Loop Choti tables me Simple
      Index Loop Jab index ho Fast
      Sort-Merge Tables sorted ho Efficient
      Hash Join Memory ho but index na ho Best for = join

     Final Recap

    • DBMS queries ko optimize karta hai by choosing best algorithm

    • SELECT me: linear, binary, index

    • PROJECT me: column pick, duplicate remove

    • JOIN me: nested loop, index join, sort-merge, hash

    Ye sab algorithms DBMS ko fast aur efficient banate hain.

    < Previous
    Next >
    Scroll to Top