/*
IT405 Unit 4

DBMS Unit 5 Syllabus

  • Functional Dependency
  • Types of Functional Dependency
  • Closure of Functional Dependency
  • Attribute Closure
  • Armstrong Axioms
  • Minimal Cover (Canonical Cover)
  • Normalization
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Boyce-Codd Normal Form (BCNF)
  • Multivalued Dependency (MVD)
  • Fourth Normal Form (4NF)
  • Join Dependency
  • Transaction Processing
  • ACID Properties
  • Distributed Database System (DDBMS)
  • Data Fragmentation
  • Data Replication
  • Object Oriented Database Management System (OODBMS)

DBMS Unit 5 NOTES

Functional Dependency

Functional Dependency DBMS Unit 5 ka sabse important topic hai. Normalization ko samajhne ke liye Functional Dependency ka concept clear hona bahut zaroori hai.

Functional Dependency batati hai ki ek attribute ki value doosre attribute ki value ko uniquely determine karti hai ya nahi.

Definition: Functional Dependency is a relationship between two attributes in which one attribute uniquely determines another attribute.

Simple Meaning

Agar kisi table me ek attribute ki value se doosre attribute ki value uniquely mil jaye, to dono attributes ke beech Functional Dependency hoti hai.

A → B Meaning: A determines B or A ki value se B ki value uniquely identify ho jaati hai.

Example

Roll_No Student_Name Branch
101 Shivam CSE
102 Rahul IT

Is table me Roll_No se Student_Name aur Branch uniquely identify ho rahe hain.

Roll_No → Student_Name Roll_No → Branch

Therefore, Roll_No functionally determines Student_Name and Branch.

Why Functional Dependency is Needed?

  • Database design ko improve karne ke liye.
  • Data redundancy reduce karne ke liye.
  • Normalization perform karne ke liye.
  • Update, insert aur delete anomalies avoid karne ke liye.
  • Relations ko logically decompose karne ke liye.

Functional Dependency Notation

X → Y Where: X = Determinant Y = Dependent Attribute

Yahaan X determinant hai kyunki X ki value Y ko determine karti hai.

Determinant

Functional Dependency ke left side wale attribute ko determinant kehte hain.

Roll_No → Name Roll_No = Determinant Name = Dependent Attribute

Dependent Attribute

Functional Dependency ke right side wale attribute ko dependent attribute kehte hain.

Emp_ID → Emp_Name Emp_Name depends on Emp_ID

Real Life Example

College database me Roll Number har student ke liye unique hota hai. Isliye Roll Number se student ka name, branch aur semester find kiya ja sakta hai.

Roll_No ↓ Name Branch Semester

Types of Functional Dependency

Functional Dependency ke different types hote hain. Ye types normalization ke different forms ko samajhne me help karte hain.

Types of Functional Dependency │ ├── Trivial Functional Dependency ├── Non-Trivial Functional Dependency ├── Full Functional Dependency ├── Partial Functional Dependency └── Transitive Functional Dependency

1. Trivial Functional Dependency

Jab right side attribute left side attribute ka subset hota hai, to use Trivial Functional Dependency kehte hain.

Definition: A functional dependency X → Y is trivial if Y is a subset of X.

Example

{Roll_No, Name} → Name Yahaan Name left side ka part hai. Therefore, it is Trivial FD.

2. Non-Trivial Functional Dependency

Jab right side attribute left side attribute ka subset nahi hota, to use Non-Trivial Functional Dependency kehte hain.

Definition: A functional dependency X → Y is non-trivial if Y is not a subset of X.

Example

Roll_No → Name Name, Roll_No ka subset nahi hai. Therefore, it is Non-Trivial FD.

3. Full Functional Dependency

Jab right side attribute left side ke complete set par depend karta hai aur kisi smaller subset par depend nahi karta, to Full Functional Dependency hoti hai.

Definition: A functional dependency X → Y is full functional dependency if Y depends on the complete set X and not on any subset of X.

Example

{Student_ID, Subject_ID} → Marks Marks dono Student_ID aur Subject_ID par depend karta hai. Sirf Student_ID se Marks nahi milenge. Sirf Subject_ID se Marks nahi milenge.

4. Partial Functional Dependency

Partial Dependency tab hoti hai jab non-key attribute composite key ke kisi part par depend kare.

Definition: A partial dependency occurs when a non-prime attribute depends on part of a composite key.

Example

Student_ID Subject_ID Student_Name Marks
S1 DBMS Shivam 85
Composite Key: {Student_ID, Subject_ID} Dependency: Student_ID → Student_Name Yahaan Student_Name sirf Student_ID par depend hai. Therefore, Partial Dependency exists.

5. Transitive Functional Dependency

Transitive Dependency tab hoti hai jab ek non-key attribute doosre non-key attribute par depend karta hai.

Definition: A transitive dependency occurs when A → B and B → C, then A → C.

Example

Roll_No Dept_ID Dept_Name
101 D1 CSE
Roll_No → Dept_ID Dept_ID → Dept_Name Therefore: Roll_No → Dept_Name This is Transitive Dependency.

Functional Dependency Summary

Type Meaning
Trivial FD Right side is subset of left side
Non-Trivial FD Right side is not subset of left side
Full FD Depends on complete key
Partial FD Depends on part of composite key
Transitive FD Indirect dependency

Memory Trick

Trivial ↓ Already Included ------------------- Non-Trivial ↓ New Attribute ------------------- Full ↓ Complete Key ------------------- Partial ↓ Part of Key ------------------- Transitive ↓ A → B → C

Most Expected Questions

  • Define Functional Dependency.
  • Explain trivial and non-trivial functional dependency.
  • Explain full and partial functional dependency with example.
  • Explain transitive dependency with suitable example.
  • Why Functional Dependency is important in normalization?

Closure of Functional Dependency

Closure of Functional Dependency DBMS Unit 5 ka important theoretical aur numerical topic hai. Iska use diye gaye Functional Dependencies se naye Functional Dependencies derive karne ke liye hota hai.

Simple words me, agar hume kuch FDs diye gaye hain, to unse logically jo bhi extra FDs nikal sakti hain, unka complete set Closure of FD kehlata hai.

Definition: The closure of a set of functional dependencies F, denoted by F+, is the set of all functional dependencies that can be logically derived from F.

Notation

F+ Meaning: Closure of Functional Dependency Set F

Example

Given: A → B B → C Then: A → C Because: A determines B and B determines C Therefore: A determines C

Yahaan A → C derived dependency hai, jo closure ka part banegi.

Why Closure of FD is Needed?

  • New dependencies identify karne ke liye.
  • Candidate keys find karne ke liye.
  • Normalization check karne ke liye.
  • Database decomposition verify karne ke liye.
  • Dependency preservation check karne ke liye.

Closure of FD Example

Given FDs: A → B B → C C → D Possible Derived FDs: A → C A → D B → D Therefore: F+ contains original + derived dependencies.

Attribute Closure

Attribute Closure DBMS exam ka bahut important numerical concept hai. Iska use candidate key find karne ke liye sabse zyada hota hai.

Kisi attribute set se jo bhi attributes functionally determine ho sakte hain, unka complete set Attribute Closure kehlata hai.

Definition: The closure of an attribute set X with respect to FDs F, denoted by X+, is the set of all attributes that can be functionally determined by X.

Notation

X+ Meaning: Closure of Attribute Set X

Steps to Find Attribute Closure

Step 1: Start with X+ = X Step 2: Check all functional dependencies. Step 3: If left side of FD is present in X+, then add right side attributes. Step 4: Repeat until no new attribute can be added.

Solved Example 1

Given Relation:

R(A, B, C, D) FDs: A → B B → C C → D Find A+

Solution

Start: A+ = {A} Using A → B: A+ = {A, B} Using B → C: A+ = {A, B, C} Using C → D: A+ = {A, B, C, D} Therefore: A+ = {A, B, C, D}

Since A+ contains all attributes of relation R, A is a candidate key.

Solved Example 2

Given: R(A, B, C, D, E) FDs: A → B B → C CD → E Find A+

Solution

Start: A+ = {A} Using A → B: A+ = {A, B} Using B → C: A+ = {A, B, C} Now CD → E cannot apply because D is missing. Therefore: A+ = {A, B, C}

A is not a candidate key because A+ does not contain all attributes.

Use of Attribute Closure

  • Candidate key identify karne ke liye.
  • Super key check karne ke liye.
  • Functional Dependency validity check karne ke liye.
  • Normalization problems solve karne ke liye.
  • Lossless decomposition verify karne ke liye.

Shortcut for Candidate Key

If X+ contains all attributes of relation R, Then X is Super Key. If X is minimal, Then X is Candidate Key.

Armstrong Axioms

Armstrong Axioms rules ka set hai jinka use functional dependencies derive karne ke liye kiya jata hai.

Ye rules sound aur complete hote hain. Matlab jo dependencies in rules se derive hoti hain wo logically correct hoti hain.

Definition: Armstrong Axioms are a set of inference rules used to derive all functional dependencies implied by a given set of functional dependencies.

Main Armstrong Axioms

Armstrong Axioms │ ├── Reflexivity Rule ├── Augmentation Rule └── Transitivity Rule

1. Reflexivity Rule

Agar Y, X ka subset hai, to X → Y hamesha true hoga.

If Y ⊆ X Then: X → Y

Example

{Roll_No, Name} → Name Because Name is part of {Roll_No, Name}

2. Augmentation Rule

Agar X → Y true hai, to dono side me same attribute Z add karne par XZ → YZ bhi true hoga.

If X → Y Then: XZ → YZ

Example

A → B Then: AC → BC

3. Transitivity Rule

Agar X → Y aur Y → Z true hai, to X → Z bhi true hoga.

If X → Y and Y → Z Then: X → Z

Example

Roll_No → Dept_ID Dept_ID → Dept_Name Therefore: Roll_No → Dept_Name

Additional Rules Derived from Armstrong Axioms

Rule Meaning
Union Rule If X → Y and X → Z, then X → YZ
Decomposition Rule If X → YZ, then X → Y and X → Z
Pseudo Transitivity If X → Y and WY → Z, then WX → Z

Armstrong Axioms Summary

Axiom Rule Example
Reflexivity If Y ⊆ X, then X → Y AB → A
Augmentation If X → Y, then XZ → YZ A → B then AC → BC
Transitivity If X → Y and Y → Z, then X → Z A → B, B → C then A → C

RGPV Exam Keywords

  • Functional Dependency Closure
  • Attribute Closure
  • F+
  • X+
  • Armstrong Axioms
  • Reflexivity
  • Augmentation
  • Transitivity
  • Candidate Key
  • Inference Rules

Minimal Cover (Canonical Cover)

Minimal Cover DBMS Unit 5 ka important concept hai jo Normalization aur Database Design me use hota hai. Iska objective Functional Dependencies ke unnecessary parts ko remove karke minimum equivalent FD set banana hota hai.

Definition

A Minimal Cover is the smallest set of Functional Dependencies that is equivalent to the original set of Functional Dependencies.

Why Minimal Cover is Needed?

  • Redundant dependencies remove karne ke liye.
  • Database design simplify karne ke liye.
  • Normalization perform karne ke liye.
  • Dependency preservation improve karne ke liye.
  • Efficient schema design ke liye.

Properties of Minimal Cover

Minimal Cover ↓ Single Attribute RHS ↓ No Redundant FD ↓ No Extraneous Attribute

Rules for Finding Minimal Cover

Step 1 Convert RHS into Single Attributes ↓ Step 2 Remove Extraneous Attributes ↓ Step 3 Remove Redundant Dependencies ↓ Final Minimal Cover

Step 1: Single Attribute on RHS

Har Functional Dependency ke right side par sirf ek attribute hona chahiye.

Example

A → BC Convert into A → B A → C

Step 2: Remove Extraneous Attributes

Agar left side ka koi attribute unnecessary ho to use remove kar diya jata hai.

Example

AB → C Suppose A alone → C Then B unnecessary hai Final: A → C

Step 3: Remove Redundant Dependencies

Aisi Functional Dependency jo baaki dependencies se derive ho sakti hai use remove kar diya jata hai.

Example

A → B B → C A → C A → C already derive ho sakta hai Therefore: A → C redundant hai

Solved Example

Given: A → BC B → C A → B

Solution

Step 1 A → B A → C B → C A → B ---------------- Step 2 Duplicate Remove A → B A → C B → C ---------------- Step 3 A → C derived from A → B B → C Therefore Remove A → C ---------------- Minimal Cover A → B B → C

Advantages of Minimal Cover

  • Reduces redundancy.
  • Improves database design.
  • Simplifies normalization.
  • Efficient dependency management.
  • Easy schema decomposition.

Normalization

Normalization DBMS ka sabse important topic hai. RGPV exams me Normalization, 1NF, 2NF, 3NF aur BCNF almost har saal pooche jaate hain.

Definition

Normalization is the process of organizing data in a database to reduce redundancy and eliminate anomalies.

Need of Normalization

  • Reduce data redundancy.
  • Improve consistency.
  • Avoid update anomalies.
  • Avoid insertion anomalies.
  • Avoid deletion anomalies.
  • Improve database efficiency.

Problems Without Normalization

Unnormalized Table ↓ Redundancy ↓ Anomalies ↓ Inconsistency

Types of Anomalies

Database Anomalies │ ├── Update Anomaly ├── Insertion Anomaly └── Deletion Anomaly

1. Update Anomaly

Jab same data multiple rows me present ho aur update karte time inconsistency aa jaye tab Update Anomaly hoti hai.

Example

Student Department
Shivam CSE
Rahul CSE

Agar CSE ko Computer Science karna ho to multiple rows update karni padengi.

2. Insertion Anomaly

Jab unnecessary information ke bina new data insert nahi kiya ja sake tab Insertion Anomaly hoti hai.

Example

Department Add Karna Hai But Student Data Required

3. Deletion Anomaly

Jab kisi row ko delete karne par important information bhi delete ho jaye tab Deletion Anomaly hoti hai.

Example

Last Student Deleted ↓ Department Information Lost

Goals of Normalization

Reduce Redundancy ↓ Remove Anomalies ↓ Maintain Consistency ↓ Efficient Database

Normal Forms

Normalization │ ├── 1NF ├── 2NF ├── 3NF ├── BCNF ├── 4NF └── 5NF

Advantages of Normalization

  • Less redundancy.
  • Better consistency.
  • Easy maintenance.
  • Efficient storage.
  • Improved data integrity.

Disadvantages of Normalization

  • More tables required.
  • Complex joins.
  • Increased query complexity.

Memory Trick

Normalization ↓ Reduce Redundancy ↓ Remove Anomalies ↓ Improve Consistency

RGPV Exam Keywords

  • Minimal Cover
  • Canonical Cover
  • Normalization
  • Redundancy
  • Update Anomaly
  • Insertion Anomaly
  • Deletion Anomaly
  • Database Design
  • Schema Refinement
  • Normal Forms

Most Expected Questions

2 Marks

  • Define Minimal Cover.
  • Define Normalization.
  • What is Update Anomaly?
  • What is Insertion Anomaly?

5 Marks

  • Explain Minimal Cover.
  • Explain Normalization.
  • Explain database anomalies.

7 Marks

  • Explain Minimal Cover with example.
  • Explain Normalization and anomalies.
  • Discuss goals of Normalization.

14 Marks

  • Explain Minimal Cover in DBMS with suitable example. Also explain Normalization, its need, advantages and anomalies.

First Normal Form (1NF)

First Normal Form (1NF) Normalization ka pehla stage hai. Iska objective database table me repeating groups aur multivalued attributes ko remove karna hota hai.

Definition

A relation is said to be in First Normal Form (1NF) if every attribute contains only atomic (single) values and there are no repeating groups.

What is Atomic Value?

Atomic value ka matlab hai ki ek cell me sirf ek hi value honi chahiye.

Correct Phone = 9876543210 ------------------- Wrong Phone = 9876543210, 9988776655

Table Not in 1NF

Student_ID Name Phone
101 Shivam 9876543210,9988776655

Phone attribute me multiple values hain, therefore table 1NF me nahi hai.

Convert into 1NF

Student_ID Name Phone
101 Shivam 9876543210
101 Shivam 9988776655

Ab har cell me single value hai, therefore table 1NF me hai.

Rules of 1NF

  • No multivalued attributes.
  • No repeating groups.
  • Each row must be unique.
  • Each column contains atomic values.
  • Single value per cell.

Advantages of 1NF

  • Improved data organization.
  • Easy searching.
  • Better consistency.
  • Eliminates repeating groups.

Disadvantages of 1NF

  • Redundancy may still exist.
  • Partial dependency may exist.
  • Update anomalies still possible.

Memory Trick

1NF ↓ One Cell ↓ One Value

Second Normal Form (2NF)

Second Normal Form (2NF) 1NF ka improved version hai. Iska objective Partial Dependency ko remove karna hota hai.

Definition

A relation is in Second Normal Form (2NF) if it is already in 1NF and every non-prime attribute is fully functionally dependent on the whole candidate key.

Requirements of 2NF

Relation must be in 1NF + No Partial Dependency = 2NF

What is Partial Dependency?

Jab non-key attribute composite key ke kisi part par depend kare, to Partial Dependency hoti hai.

Example Table (Not in 2NF)

Student_ID Subject_ID Student_Name Marks
S1 DBMS Shivam 85
S2 OS Rahul 90

Composite Key = (Student_ID, Subject_ID)

Student_ID → Student_Name (Student_ID, Subject_ID) → Marks

Student_Name sirf Student_ID par depend karta hai.

Therefore Partial Dependency exists.

Hence table 2NF me nahi hai.

Convert into 2NF

Student Table

Student_ID Student_Name
S1 Shivam
S2 Rahul

Marks Table

Student_ID Subject_ID Marks
S1 DBMS 85
S2 OS 90

Ab Partial Dependency remove ho gayi hai.

Therefore relation 2NF me hai.

Advantages of 2NF

  • Removes partial dependency.
  • Reduces redundancy.
  • Improves consistency.
  • Reduces update anomalies.

Difference Between 1NF and 2NF

1NF 2NF
Atomic values only Must satisfy 1NF
Repeating groups removed Partial dependency removed
Redundancy may exist Less redundancy
Composite key issues possible Composite key issues removed

1NF to 2NF Flow

Unnormalized Form ↓ 1NF (Remove Repeating Groups) ↓ 2NF (Remove Partial Dependency)

Real Life Example

College result database me Student Name ko har subject record ke saath store karna redundancy create karta hai.

2NF me Student information aur Marks information ko separate tables me divide kar diya jata hai.

Exam Shortcut

1NF ↓ One Cell = One Value ------------------- 2NF ↓ No Partial Dependency

RGPV Exam Keywords

  • First Normal Form
  • Second Normal Form
  • Atomic Values
  • Repeating Groups
  • Partial Dependency
  • Composite Key
  • Normalization
  • Database Design
  • Functional Dependency
  • Redundancy Removal

Most Expected Questions

2 Marks

  • Define First Normal Form.
  • Define Second Normal Form.
  • What is Partial Dependency?
  • What are Atomic Values?

5 Marks

  • Explain First Normal Form with example.
  • Explain Second Normal Form with example.
  • Differentiate 1NF and 2NF.

7 Marks

  • Convert a relation into 1NF and 2NF.
  • Explain normalization up to 2NF.
  • Explain Partial Dependency with example.

14 Marks

  • Explain First Normal Form (1NF) and Second Normal Form (2NF) with suitable examples and diagrams.

Third Normal Form (3NF)

Third Normal Form (3NF) Normalization ka next stage hai. 3NF ka main objective Transitive Dependency ko remove karna hota hai.

RGPV exams me 3NF bahut frequently poocha jata hai aur Normalization ke long questions me almost hamesha included rehta hai.

Definition

A relation is in Third Normal Form (3NF) if it is already in 2NF and contains no Transitive Dependency.

Requirements of 3NF

Relation in 2NF + No Transitive Dependency = 3NF

What is Transitive Dependency?

Jab ek non-key attribute kisi doosre non-key attribute par depend karta hai, tab Transitive Dependency hoti hai.

A → B B → C ↓ A → C (Transitive Dependency)

Example (Not in 3NF)

Student_ID Dept_ID Dept_Name
S1 D1 CSE
S2 D2 IT
Student_ID → Dept_ID Dept_ID → Dept_Name Therefore Student_ID → Dept_Name

Dept_Name indirectly Student_ID par depend kar raha hai.

Therefore Transitive Dependency exists.

Hence relation 3NF me nahi hai.

Conversion into 3NF

Student Table

Student_ID Dept_ID
S1 D1
S2 D2

Department Table

Dept_ID Dept_Name
D1 CSE
D2 IT

Ab Dept_Name alag table me hai aur Transitive Dependency remove ho gayi hai.

Therefore relation 3NF me hai.

Advantages of 3NF

  • Removes Transitive Dependency.
  • Reduces redundancy.
  • Improves consistency.
  • Reduces update anomalies.
  • Efficient database structure.

Disadvantages of 3NF

  • More tables may be required.
  • Complex joins in large databases.

Memory Trick

3NF ↓ No Transitive Dependency

Boyce-Codd Normal Form (BCNF)

BCNF (Boyce-Codd Normal Form) 3NF ka stronger version hai.

Kuch situations me relation 3NF me hota hai lekin redundancy phir bhi exist karti hai. Aisi situations ko BCNF solve karta hai.

Definition

A relation is in BCNF if for every non-trivial Functional Dependency X → Y, X must be a Super Key.

BCNF Rule

For Every FD X → Y ↓ X must be Super Key

Example (3NF but Not BCNF)

Consider relation:

R(Student, Course, Teacher) FDs: (Student, Course) → Teacher Teacher → Course

Candidate Keys:

(Student, Course) (Student, Teacher)

Dependency:

Teacher → Course

Teacher Super Key nahi hai.

Therefore relation BCNF violate karta hai.

Convert into BCNF

Teacher Table

Teacher Course
T1 DBMS
T2 OS

Student Teacher Table

Student Teacher
S1 T1
S2 T2

Ab har Functional Dependency BCNF satisfy karti hai.

Conditions for BCNF

  • Relation must be in 3NF.
  • Every determinant should be a candidate key.
  • Every FD left side should be a super key.

3NF vs BCNF

3NF BCNF
No Transitive Dependency Every Determinant is Super Key
Less Strict More Strict
Some Redundancy May Exist Almost No Redundancy
Easier to Achieve Harder to Achieve

Normalization Flow

1NF ↓ Remove Repeating Groups ↓ 2NF ↓ Remove Partial Dependency ↓ 3NF ↓ Remove Transitive Dependency ↓ BCNF ↓ Every Determinant is Super Key

Real Life Example

University database me Teacher aur Course ke beech dependency ho sakti hai.

Agar Teacher uniquely Course determine karta hai to BCNF ensure karega ki unnecessary redundancy database me na aaye.

Exam Shortcut

1NF ↓ Atomic Values ---------------- 2NF ↓ No Partial Dependency ---------------- 3NF ↓ No Transitive Dependency ---------------- BCNF ↓ Every Determinant = Super Key

RGPV Exam Keywords

  • Third Normal Form
  • 3NF
  • BCNF
  • Boyce-Codd Normal Form
  • Transitive Dependency
  • Determinant
  • Candidate Key
  • Super Key
  • Normalization
  • Database Design

Most Expected Questions

2 Marks

  • Define 3NF.
  • Define BCNF.
  • What is Transitive Dependency?
  • What is Determinant?

5 Marks

  • Explain Third Normal Form.
  • Explain BCNF.
  • Differentiate 3NF and BCNF.

7 Marks

  • Convert a relation into 3NF.
  • Explain BCNF with example.
  • Discuss normalization up to BCNF.

14 Marks

  • Explain Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF) with suitable examples and diagrams.

Multivalued Dependency (MVD)

Multivalued Dependency (MVD) DBMS Unit 5 ka advanced concept hai jo Fourth Normal Form (4NF) ko samajhne ke liye bahut important hai.

Jab ek attribute ki multiple independent values kisi doosre attribute se associated hoti hain, tab Multivalued Dependency exist karti hai.

Definition

A Multivalued Dependency exists when one attribute in a relation determines multiple independent values of another attribute.

Notation

A ↠ B Read as A Multidetermines B

Basic Idea

Functional Dependency me ek attribute doosre attribute ki single value determine karta hai.

Multivalued Dependency me ek attribute multiple values determine karta hai.

Example

Student Hobby Language
Shivam Cricket Hindi
Shivam Cricket English
Shivam Music Hindi
Shivam Music English
Student ↠ Hobby Student ↠ Language

Yahaan Hobby aur Language ek doosre se independent hain.

Therefore Multivalued Dependency exists.

Characteristics of MVD

  • Multiple independent values.
  • Redundancy create karti hai.
  • Normalization required hoti hai.
  • 4NF ka base concept hai.

MVD vs Functional Dependency

Functional Dependency Multivalued Dependency
A → B A ↠ B
Single Value Multiple Values
Basic Dependency Advanced Dependency
Used in 2NF/3NF Used in 4NF

Fourth Normal Form (4NF)

Fourth Normal Form (4NF) BCNF ka extension hai. Iska objective Multivalued Dependencies ko remove karna hota hai.

Definition

A relation is in Fourth Normal Form (4NF) if it is in BCNF and contains no non-trivial Multivalued Dependency.

Requirements of 4NF

Relation in BCNF + No Non-Trivial MVD = 4NF

Relation Not in 4NF

Student Hobby Language
Shivam Cricket Hindi
Shivam Cricket English
Shivam Music Hindi
Shivam Music English

Yahaan Student ↠ Hobby aur Student ↠ Language hai.

Relation 4NF violate karta hai.

Convert into 4NF

Student Hobby Table

Student Hobby
Shivam Cricket
Shivam Music

Student Language Table

Student Language
Shivam Hindi
Shivam English

Ab Multivalued Dependency remove ho gayi hai.

Therefore relation 4NF me hai.

Advantages of 4NF

  • Eliminates Multivalued Dependency.
  • Reduces redundancy.
  • Improves consistency.
  • Better database design.
  • Reduces anomalies.

Disadvantages of 4NF

  • More tables required.
  • Complex joins.
  • Increased storage overhead.

Join Dependency (JD)

Join Dependency 5NF ka base concept hai. Ye batati hai ki relation ko multiple relations me divide karne ke baad original relation ko lossless join se reconstruct kiya ja sakta hai ya nahi.

Definition

A Join Dependency exists when a relation can be reconstructed by joining multiple smaller relations without loss of information.

Basic Concept

Original Relation ↓ Decomposition ↓ Smaller Relations ↓ JOIN ↓ Original Relation

Example

Consider relation:

R(Supplier, Part, Project)

Decompose into:

SP(Supplier, Part) SJ(Supplier, Project) PJ(Part, Project)

Agar in teen relations ko join karke original relation mil jaye to Join Dependency exist karti hai.

Join Dependency Properties

  • Used in 5NF.
  • Supports lossless decomposition.
  • Removes redundancy.
  • Improves schema design.

Lossless Join

Lossless Join ka matlab hai decomposition ke baad join karne par koi information loss na ho.

Decompose ↓ Join ↓ Original Relation (No Data Loss)

Normalization Summary

Normal Form Removes
1NF Repeating Groups
2NF Partial Dependency
3NF Transitive Dependency
BCNF Determinant Issues
4NF Multivalued Dependency
5NF Join Dependency

Normalization Flow Chart

1NF ↓ Remove Repeating Groups ↓ 2NF ↓ Remove Partial Dependency ↓ 3NF ↓ Remove Transitive Dependency ↓ BCNF ↓ Every Determinant = Super Key ↓ 4NF ↓ Remove MVD ↓ 5NF ↓ Remove Join Dependency

Memory Trick

4NF ↓ No MVD ------------------- 5NF ↓ No Join Dependency

RGPV Exam Keywords

  • Multivalued Dependency
  • MVD
  • Fourth Normal Form
  • 4NF
  • Join Dependency
  • Lossless Join
  • Schema Decomposition
  • Normalization
  • Database Design
  • Fifth Normal Form

Most Expected Questions

2 Marks

  • Define Multivalued Dependency.
  • Define Fourth Normal Form.
  • What is Join Dependency?
  • What is Lossless Join?

5 Marks

  • Explain Multivalued Dependency with example.
  • Explain Fourth Normal Form.
  • Explain Join Dependency.

7 Marks

  • Convert a relation into 4NF.
  • Explain MVD and 4NF with example.
  • Explain Join Dependency and Lossless Join.

14 Marks

  • Explain Multivalued Dependency (MVD), Fourth Normal Form (4NF) and Join Dependency with suitable examples and diagrams.

Transaction Processing

Transaction Processing DBMS ka important concept hai jo database operations ko reliable aur consistent banata hai. Banking systems, railway reservation systems, e-commerce websites aur ATM systems me transaction processing ka use hota hai.

Definition

A Transaction is a logical unit of work that consists of one or more database operations and changes the database from one consistent state to another consistent state.

Real Life Example

Suppose Shivam ke account me ₹10,000 hain aur Rahul ke account me ₹5,000 hain.

Shivam Rahul ko ₹2,000 transfer karta hai.

Step 1 Shivam Account 10000 → 8000 ↓ Step 2 Rahul Account 5000 → 7000

Ye complete operation ek transaction hai.

Transaction Operations

Transaction │ ├── Read(X) └── Write(X)

Read Operation

Database se data read karna Read operation kehlata hai.

Read(A)

Write Operation

Database me updated value store karna Write operation kehlata hai.

Write(A)

Example Transaction

T1 Read(A) A = A - 2000 Write(A) Read(B) B = B + 2000 Write(B)

ACID Properties

ACID Properties transaction processing ki reliability ensure karti hain.

ACID │ ├── Atomicity ├── Consistency ├── Isolation └── Durability

1. Atomicity

Atomicity ka matlab hai "All or Nothing".

Ya to poori transaction execute hogi ya bilkul execute nahi hogi.

Money Transfer ↓ Debit Success ↓ Credit Failed ↓ Rollback

2. Consistency

Transaction database ko ek valid state se doosri valid state me le jati hai.

Before Transaction Balance = 15000 ↓ After Transaction Balance = 15000

3. Isolation

Concurrent transactions ek doosre ke execution ko affect nahi karni chahiye.

T1 Running ↓ T2 Cannot See Partial Changes

4. Durability

Commit hone ke baad transaction ke changes permanently database me save ho jaate hain.

COMMIT ↓ Power Failure ↓ Data Safe

ACID Summary Table

Property Meaning
Atomicity All or Nothing
Consistency Valid State
Isolation Independent Execution
Durability Permanent Storage

Transaction States

Transaction execution ke dauran transaction different states se pass hoti hai.

Transaction States │ ├── Active ├── Partially Committed ├── Committed ├── Failed └── Aborted

1. Active State

Transaction currently execute ho rahi hoti hai.

Transaction Running ↓ Active State

2. Partially Committed State

Transaction ke last statement execute ho chuke hote hain lekin commit abhi complete nahi hua hota.

3. Committed State

Transaction successfully complete ho chuki hoti hai aur changes permanently save ho jaate hain.

4. Failed State

Kisi error ki wajah se transaction fail ho jaati hai.

5. Aborted State

Failed transaction rollback hone ke baad Aborted state me chali jaati hai.

Transaction State Diagram

Active ↓ Partially Committed ↓ Committed ------------------- Error ↓ Failed ↓ Aborted

Advantages of Transaction Processing

  • Maintains consistency.
  • Supports recovery.
  • Improves reliability.
  • Ensures data integrity.
  • Supports concurrent users.

Memory Trick

ACID ↓ A = Atomicity C = Consistency I = Isolation D = Durability

RGPV Exam Keywords

  • Transaction Processing
  • Read Operation
  • Write Operation
  • ACID Properties
  • Atomicity
  • Consistency
  • Isolation
  • Durability
  • Transaction States
  • Commit
  • Rollback

Most Expected Questions

2 Marks

  • Define Transaction.
  • What is Atomicity?
  • What is Durability?
  • What is Active State?

5 Marks

  • Explain ACID Properties.
  • Explain Transaction States.
  • Explain Transaction Processing.

7 Marks

  • Explain Transaction State Diagram.
  • Discuss ACID Properties with examples.
  • Explain Transaction Processing in DBMS.

14 Marks

  • Explain Transaction Processing and ACID Properties in DBMS. Also explain Transaction States with suitable diagram.

Distributed Database System (DDBMS)

Distributed Database System (DDBMS) DBMS ka advanced concept hai jisme database ek hi location par store nahi hota. Database multiple locations ya multiple computers par distribute hota hai lekin user ko ek single database ki tarah appear hota hai.

Aaj ke modern applications jaise Banking Systems, Google, Amazon, Facebook aur Railway Reservation Systems me Distributed Database ka use kiya jata hai.

Definition

A Distributed Database is a collection of logically related databases distributed over different locations and connected through a communication network.

Basic Concept

User ↓ Distributed DBMS ↓ Node 1 Node 2 Node 3 ↓ Network

Why Distributed Database is Needed?

  • Large organizations ke liye.
  • Multiple branch offices support karne ke liye.
  • Fast data access ke liye.
  • High availability ke liye.
  • Better fault tolerance ke liye.

Example

Suppose ek bank ki branches Delhi, Mumbai aur Bhopal me hain.

Delhi Branch ↓ Local Database ------------------- Mumbai Branch ↓ Local Database ------------------- Bhopal Branch ↓ Local Database

Ye saare databases network ke through connected rehte hain.

User ko ek hi integrated database dikhai deta hai.

Characteristics of Distributed Database

  • Data multiple locations par stored hota hai.
  • Logical integration maintain rehti hai.
  • Network communication required hoti hai.
  • Users ko transparency milti hai.
  • High reliability provide karta hai.

Architecture of Distributed Database

Client ↓ Distributed DBMS ↓ Communication Network ↓ Site 1 Site 2 Site 3

Components of DDBMS

DDBMS Components │ ├── Database Sites ├── Communication Network ├── Distributed DBMS Software └── Users

Types of Distributed Database

Distributed Database │ ├── Homogeneous DDBMS └── Heterogeneous DDBMS

1. Homogeneous Distributed Database

Jab sabhi sites same DBMS software use karti hain tab Homogeneous Distributed Database kehlata hai.

Example

Oracle ↓ Oracle ↓ Oracle

Advantages

  • Easy management.
  • Simple communication.
  • Better compatibility.

2. Heterogeneous Distributed Database

Jab different sites different DBMS software use karti hain tab Heterogeneous Distributed Database kehlata hai.

Example

Oracle ↓ MySQL ↓ SQL Server

Advantages

  • Flexible architecture.
  • Different platforms supported.

Disadvantages

  • Complex implementation.
  • Compatibility issues.

Data Fragmentation

Data Fragmentation me database ko smaller pieces me divide kiya jata hai.

Database ↓ Fragments ↓ Different Sites

Types of Fragmentation

Fragmentation │ ├── Horizontal Fragmentation ├── Vertical Fragmentation └── Mixed Fragmentation

Horizontal Fragmentation

Rows ke basis par database divide kiya jata hai.

Example

Student Table ↓ Delhi Students ↓ Mumbai Students

Vertical Fragmentation

Columns ke basis par database divide kiya jata hai.

Example

Student ↓ Personal Details ↓ Academic Details

Data Replication

Replication me same data multiple sites par store kiya jata hai.

Database ↓ Copy ↓ Site 1 Site 2 Site 3

Advantages of Replication

  • High availability.
  • Fast access.
  • Improved reliability.
  • Fault tolerance.

Advantages of Distributed Database

  • High reliability.
  • Better performance.
  • Scalability.
  • Fast response time.
  • Fault tolerance.
  • Local autonomy.

Disadvantages of Distributed Database

  • Complex design.
  • Higher cost.
  • Security issues.
  • Network dependency.
  • Difficult recovery.

Centralized DBMS vs Distributed DBMS

Centralized DBMS Distributed DBMS
Single Site Multiple Sites
Less Complex More Complex
Single Failure Point Fault Tolerant
Lower Cost Higher Cost
Less Scalable Highly Scalable

Real Life Applications

  • Banking Systems.
  • Airline Reservation Systems.
  • E-Commerce Websites.
  • University Management Systems.
  • Telecommunication Networks.

Memory Trick

Distributed Database ↓ Multiple Locations ↓ Single View ------------------- Homogeneous ↓ Same DBMS ------------------- Heterogeneous ↓ Different DBMS

RGPV Exam Keywords

  • Distributed Database
  • DDBMS
  • Homogeneous Database
  • Heterogeneous Database
  • Data Fragmentation
  • Horizontal Fragmentation
  • Vertical Fragmentation
  • Data Replication
  • Distributed Architecture
  • Communication Network

Most Expected Questions

2 Marks

  • Define Distributed Database.
  • What is Data Fragmentation?
  • What is Replication?
  • Define Homogeneous DDBMS.

5 Marks

  • Explain Distributed Database System.
  • Differentiate Homogeneous and Heterogeneous DDBMS.
  • Explain Data Fragmentation.

7 Marks

  • Explain Distributed Database Architecture.
  • Explain Data Replication with advantages.
  • Discuss advantages and disadvantages of DDBMS.

14 Marks

  • Explain Distributed Database System (DDBMS) with architecture, types, fragmentation, replication, advantages and disadvantages.

Object Oriented Database Management System (OODBMS)

Object Oriented Database Management System (OODBMS) ek advanced database system hai jo Object Oriented Programming (OOP) concepts aur Database concepts ko combine karta hai.

Traditional Relational Database tables aur rows use karti hai, jabki OODBMS data ko objects ke form me store karti hai.

Definition

An Object Oriented Database Management System (OODBMS) is a database system in which data is represented and stored in the form of objects rather than tables.

Need of OODBMS

Relational Databases complex multimedia applications ko efficiently handle nahi kar pati. Isliye OODBMS develop ki gayi.

  • Complex data handle karne ke liye.
  • Multimedia applications ke liye.
  • CAD/CAM systems ke liye.
  • Artificial Intelligence applications ke liye.
  • Object Oriented Programming support ke liye.

Basic Concept

Object ↓ Data + Methods

Object sirf data nahi rakhta, balki us data par perform hone wale functions bhi store karta hai.

Example of Object

Student Object ------------------- Data Roll_No = 101 Name = Shivam Branch = CSE ------------------- Methods Display() Update()

Characteristics of OODBMS

  • Object Oriented.
  • Supports Classes.
  • Supports Inheritance.
  • Supports Encapsulation.
  • Supports Polymorphism.
  • Stores Complex Data Types.

Architecture of OODBMS

Application ↓ Objects ↓ OODBMS ↓ Object Database

Important Concepts of OODBMS

OODBMS Concepts │ ├── Object ├── Class ├── Inheritance ├── Encapsulation ├── Polymorphism └── Object Identity

1. Object

Object real-world entity ka representation hota hai jisme data aur methods dono hote hain.

Example

Car Object ↓ Color Model Speed Start() Stop()

2. Class

Class objects ka blueprint ya template hoti hai.

Example

Class Student ↓ Objects ↓ Shivam Rahul Amit

3. Inheritance

Inheritance ek class ko doosri class ke properties aur methods inherit karne ki facility deta hai.

Example

Person ↓ Student

Student class Person class ki properties inherit karegi.

Advantages of Inheritance

  • Code reuse.
  • Easy maintenance.
  • Less redundancy.

4. Encapsulation

Data aur methods ko ek single unit me combine karna Encapsulation kehlata hai.

Example

Student Object ↓ Data + Methods

Benefits of Encapsulation

  • Data security.
  • Better organization.
  • Easy maintenance.

5. Polymorphism

Polymorphism ka matlab hai ek hi method different forms me behave kar sakti hai.

Example

Shape ↓ Draw() ↓ Circle Draw() Rectangle Draw()

6. Object Identity (OID)

Har object ka unique identifier hota hai jise Object Identity kehte hain.

Example

Object ↓ OID = 1001

Advantages of OODBMS

  • Supports complex data.
  • Better performance for multimedia data.
  • Object Oriented Programming support.
  • Code reuse through inheritance.
  • Improved maintainability.
  • Efficient handling of real-world entities.

Disadvantages of OODBMS

  • Complex implementation.
  • Higher cost.
  • Limited standardization.
  • Less popular than RDBMS.

RDBMS vs OODBMS

RDBMS OODBMS
Stores Data in Tables Stores Data in Objects
Uses Rows and Columns Uses Classes and Objects
No Inheritance Support Supports Inheritance
Limited Complex Data Support Supports Complex Data
SQL Based Object Based

Applications of OODBMS

  • CAD/CAM Systems.
  • Multimedia Systems.
  • Artificial Intelligence.
  • Expert Systems.
  • Medical Databases.
  • Engineering Applications.

Real Life Example

Suppose ek Hospital Management System me Patient object, Doctor object aur Appointment object use kiye jaate hain.

Patient Object ↓ Name Age Disease Treatment()

Ye object-oriented structure OODBMS me efficiently store ki ja sakti hai.

Memory Trick

OODBMS ↓ Objects ↓ Classes ↓ Inheritance ↓ Encapsulation ↓ Polymorphism

RGPV Exam Keywords

  • OODBMS
  • Object Oriented Database
  • Object
  • Class
  • Inheritance
  • Encapsulation
  • Polymorphism
  • Object Identity
  • RDBMS vs OODBMS
  • Multimedia Database

Most Expected Questions

2 Marks

  • Define OODBMS.
  • What is Object Identity?
  • What is Inheritance?
  • What is Encapsulation?

5 Marks

  • Explain OODBMS.
  • Explain OODBMS architecture.
  • Differentiate RDBMS and OODBMS.

7 Marks

  • Explain Object Oriented concepts used in OODBMS.
  • Discuss advantages and disadvantages of OODBMS.
  • Explain OODBMS with suitable examples.

14 Marks

  • Explain Object Oriented Database Management System (OODBMS). Discuss its architecture, characteristics, object-oriented concepts, advantages, disadvantages and applications.

DBMS Unit 5 PYQ Analysis

This PYQ Analysis is prepared from RGPV previous year question papers. The analysis highlights repeated topics, frequency of occurrence and expected questions for upcoming examinations.

Topics Covered

  • Functional Dependency
  • Attribute Closure
  • Armstrong Axioms
  • Normalization
  • 1NF
  • 2NF
  • 3NF
  • BCNF
  • Multivalued Dependency
  • Fourth Normal Form
  • Transaction Processing
  • ACID Properties
  • Distributed Database
  • OODBMS

PYQ Frequency Analysis

Topic 2020 2022 2023 2025 Frequency
Functional Dependency ✅ ✅ ✅ ✅ ★★★★★
Armstrong Axioms ❌ ✅ ✅ ✅ ★★★★★
Normalization ✅ ✅ ✅ ✅ ★★★★★
1NF, 2NF, 3NF ✅ ✅ ✅ ✅ ★★★★★
BCNF ❌ ✅ ✅ ✅ ★★★★☆
Multivalued Dependency ❌ ❌ ✅ ✅ ★★★★☆
Distributed Database ✅ ❌ ✅ ✅ ★★★★☆
OODBMS ❌ ✅ ❌ ✅ ★★★☆☆
Attribute Closure ❌ ✅ ✅ ❌ ★★★☆☆
Transaction Processing ❌ ❌ ✅ ✅ ★★★☆☆

Most Repeated Questions

Q1. Explain Functional Dependency with suitable examples.

Appeared In:

  • 2020
  • 2022
  • 2023
  • 2025

Prediction: ⭐⭐⭐⭐⭐

Q2. Explain Armstrong Axioms with examples.

Appeared In:

  • 2022
  • 2023
  • 2025

Prediction: ⭐⭐⭐⭐⭐

Q3. Explain Normalization with suitable examples.

Appeared In:

  • 2020
  • 2022
  • 2023
  • 2025

Prediction: ⭐⭐⭐⭐⭐

Q4. Explain 1NF, 2NF and 3NF with examples.

Appeared In:

  • 2020
  • 2022
  • 2023
  • 2025

Prediction: ⭐⭐⭐⭐⭐

Q5. Explain BCNF with example.

Appeared In:

  • 2022
  • 2023
  • 2025

Prediction: ⭐⭐⭐⭐⭐

Q6. Explain Distributed Database System.

Appeared In:

  • 2020
  • 2023
  • 2025

Prediction: ⭐⭐⭐⭐☆

Q7. Explain OODBMS and its features.

Appeared In:

  • 2022
  • 2025

Prediction: ⭐⭐⭐⭐☆

Unit 5 Expected Questions 2026

Very High Probability

🔥 Functional Dependency 🔥 Armstrong Axioms 🔥 Attribute Closure 🔥 Normalization 🔥 1NF 🔥 2NF 🔥 3NF 🔥 BCNF

High Probability

⭐ Multivalued Dependency ⭐ Fourth Normal Form ⭐ Distributed Database ⭐ OODBMS ⭐ Minimal Cover ⭐ Transaction Processing

Most Important Long Questions (14 Marks)

1. Functional Dependency and Types 2. Armstrong Axioms 3. Attribute Closure 4. Normalization 5. 1NF, 2NF, 3NF 6. BCNF 7. Distributed Database 8. OODBMS

Unit 5 Weightage Analysis

Topic Importance
Functional Dependency ★★★★★
Armstrong Axioms ★★★★★
Normalization ★★★★★
1NF, 2NF, 3NF ★★★★★
BCNF ★★★★★
Attribute Closure ★★★★☆
Distributed Database ★★★★☆
OODBMS ★★★★☆
Multivalued Dependency ★★★☆☆
Minimal Cover ★★★☆☆

2026 Exam Strategy

If Time is Less ↓ Prepare First ✓ Functional Dependency ✓ Armstrong Axioms ✓ Closure ✓ Normalization ✓ 1NF ✓ 2NF ✓ 3NF ✓ BCNF ------------------------ Then Prepare ✓ Distributed Database ✓ OODBMS ------------------------ Last Priority ✓ Minimal Cover ✓ Join Dependency

Frequently Asked Questions (FAQs)

What is Functional Dependency in DBMS?

Functional Dependency is a relationship between attributes where one attribute uniquely determines another attribute.

What is Attribute Closure?

Attribute Closure is the set of all attributes that can be functionally determined from a given attribute set.

What is Armstrong Axiom?

Armstrong Axioms are inference rules used to derive new Functional Dependencies from existing Functional Dependencies.

Why is Normalization important?

Normalization reduces redundancy, removes anomalies and improves database consistency.

What is BCNF?

BCNF is an advanced Normal Form where every determinant must be a Super Key.

What is Multivalued Dependency?

Multivalued Dependency exists when one attribute determines multiple independent values of another attribute.

What is Distributed Database?

Distributed Database is a collection of logically related databases distributed across multiple locations and connected through a network.

What is OODBMS?

Object Oriented Database Management System stores data in the form of objects rather than tables.

DBMS Unit 5 Quick Revision Sheet

FUNCTIONAL DEPENDENCY ↓ A → B ---------------------------- TYPES OF FD ↓ Trivial FD Non-Trivial FD Full FD Partial FD Transitive FD ---------------------------- ATTRIBUTE CLOSURE ↓ X⁺ Used for Candidate Key ---------------------------- ARMSTRONG AXIOMS ↓ Reflexivity Augmentation Transitivity ---------------------------- MINIMAL COVER ↓ Remove Redundant FD ---------------------------- NORMALIZATION ↓ 1NF ↓ 2NF ↓ 3NF ↓ BCNF ↓ 4NF ↓ 5NF ---------------------------- 1NF ↓ Atomic Values ---------------------------- 2NF ↓ No Partial Dependency ---------------------------- 3NF ↓ No Transitive Dependency ---------------------------- BCNF ↓ Every Determinant = Super Key ---------------------------- 4NF ↓ No Multivalued Dependency ---------------------------- 5NF ↓ No Join Dependency ---------------------------- TRANSACTION ↓ ACID Properties Atomicity Consistency Isolation Durability ---------------------------- DDBMS ↓ Distributed Database ---------------------------- OODBMS ↓ Object Class Inheritance Encapsulation Polymorphism

Last Minute Exam Revision

Topic Priority
Functional Dependency ★★★★★
Attribute Closure ★★★★★
Armstrong Axioms ★★★★★
Normalization ★★★★★
1NF, 2NF, 3NF ★★★★★
BCNF ★★★★★
Distributed Database ★★★★☆
OODBMS ★★★★☆
4NF ★★★☆☆
Minimal Cover ★★★☆☆

Related DBMS Units

DBMS Unit 1 DBMS Unit 2 DBMS Unit 3 DBMS Unit 4

Conclusion

DBMS Unit 5 database design aur advanced database concepts ka sabse important unit hai. Is unit me Functional Dependency, Attribute Closure, Armstrong Axioms, Normalization, BCNF, Distributed Database aur OODBMS jaise topics cover kiye jate hain.

RGPV examinations me Normalization, Functional Dependency, Armstrong Axioms aur BCNF sabse jyada pooche jane wale topics hain. Agar aap ye topics thoroughly prepare kar lete hain to Unit 5 se maximum marks score kar sakte hain.

🏆 UNIT 5 SCORE BOOSTER Must Prepare: ✓ Functional Dependency ✓ Attribute Closure ✓ Armstrong Axioms ✓ Normalization ✓ 1NF ✓ 2NF ✓ 3NF ✓ BCNF ✓ Distributed Database ✓ OODBMS