TheHingineer

  • DBMS


  • DBMS Part-1

  • DBMS Part-2

  • DBMS Part-3

  • DBMS Part-4

  • DBMS Part-5

  •  Decomposition in DBMS 

     Decomposition kya hota hai?

    Decomposition ka matlab hota hai ek badi table ko tod ke chhoti-chhoti tables mein divide karna, bina data ya information ko loss kiye.

    Ye hum redundancy (duplicate data) ko kam karne ke liye karte hain, aur database ko clean aur structured banate hain.


     Decomposition ki zarurat kyun padti hai?

    Sochiye aapke paas ek aisi table hai:

     Table: StudentCourse

    StudentID StudentName CourseID CourseName Instructor
    101 Anjali C01 DBMS Mr. Verma
    101 Anjali C02 OS Mrs. Rao
    102 Raj C01 DBMS Mr. Verma

     Is table mein problems kya hain?

    • Redundancy: Anjali aur Mr. Verma ka data repeat ho raha hai.

    • Update Problem: Agar Mr. Verma ka naam badalna ho, toh har jagah change karna padega.

    • Insert Problem: Jab tak student kisi course mein enroll nahi hota, aap course add nahi kar sakte.

    • Delete Problem: Agar course ke saare students delete ho gaye, toh course info bhi chali jaayegi.


     Decomposition ka Solution

    Is table ko 3 alag tables mein divide kar dete hain:

     1. Student Table

    StudentID StudentName
    101 Anjali
    102 Raj

     2. Course Table

    CourseID CourseName Instructor
    C01 DBMS Mr. Verma
    C02 OS Mrs. Rao

     3. Enrollment Table

    StudentID CourseID
    101 C01
    101 C02
    102 C01

     Ab kya fayda mila?

    • Data repeat nahi ho raha.

    • Updates easy ho gaye.

    • Har entity (Student, Course) alag table mein hai.

    • Tables ke beech relation foreign key se maintain ho raha hai.


     Achi Decomposition ke 2 Important Rules

    1. Lossless-Join Decomposition

      • Jab aap chhoti tables ko dobara join karo, toh original table milni chahiye, bina data loss ke.

    2. Dependency Preservation

      • Jo bhi functional dependencies pehle table mein thi, woh nayi tables mein bhi follow honi chahiye.


     Example: Project Table ko Decompose karna

     Original Table:

    ProjectID ProjectName ManagerID ManagerName
    P01 Alpha M01 Ramesh
    P02 Beta M01 Ramesh

     Decomposition:

    1. Project Table

    ProjectID ProjectName ManagerID
    P01 Alpha M01
    P02 Beta M01

    2. Manager Table

    ManagerID ManagerName
    M01 Ramesh

    Yeh dono tables ManagerID ke through linked hain. Redundancy khatam ho gayi aur data properly structured hai.


     Summary Table

    Feature Description
    Kya hai? Ek table ko logically todna
    Kyun? Redundancy hatana, clean data
    Types of properties Lossless-Join & Dependency-Preserving
    Kaam aata hai Normalization mein
    Example StudentCourse → Student, Course, Enrollment
    Scroll to Top