6.24- Dependency Preservation Property | Functional Dependency And Normalization | Normalization

6.24- Dependency Preservation Property | Functional Dependency And Normalization | Normalization

PLAYLIST:
DBMS - Data Base Management System Tutorials In Hindi | GATE- NET- DBMS Lectures in Hindi
   • DBMS Complete Syllabus- All University exa...  

   • DBMS- Relational Database Design & Functio...  
Relational Database Design- Functional Dependencies And Normalization


dbms tutorials,
database management system tutorial,
database tutorials,
relational database design,
dbms lectures,
normalization in dbms,

what is normalization in dbms,
dbms gate lectures in hindi,

dbms gate lectures,
dbms gate lectures in hindi,
dbms ugc net lectures,

#gatedbmslecturesinhindi
#ugcnetdbmslecturesinhindi
#gatedbmslectures
#ugcnetdbmslectures
#dbmsLectures
#dbmsTutorials
#NormalizationInDbms

Third Normal Form (3NF)
A relation is in Third Normal Form if it is in 2NF and non-primary key attributes must
be non-transitively dependent upon primary key attributes.
In other words a relation is in 3NF if it is in 2NF and having no transitive dependency.
Consider the relation Student as shown in Figure 6.12(a).
Student
RollNo. Name Semester Hostel
1 Lalit 1 H1
2 Gaurav 2 H2
3 Vishal 1 H1
4 Neha 4 H4
5 John 3 H3
(a)
Functional Dependency and Normalisation 219
RollNo
Name
Semester
Hostel
(b)
FIGURE 6.12. Student relation.
The Primary Key is (RollNo.). The condition is different Hostel is allotted for different
semester. Student relation is in 2NF but still contains anomalies.
1. Deletion anomaly : If you want to delete student Gaurav. You loose information
about Hostel H2 because he is the only student staying in hostel H2.
2. Insertion anomaly : If you want to add a new Hostel H8 and this is not allotted
to any student. You cannot add this information.
3. Updation anomaly : If you want to change hostel of all students of first semester.
You have to search all the students of first semester and update them individually
otherwise it causes inconsistency.
The solution of this problem is to divide relation Student into two relations Student(RollNo.
Name, Semester) and Hostels(Semester, Hostel)

Lossless Join and Dependency Preserving Decomposition
Decomposition of a relation is done when a relation in relational model is not in appropriate normal form. Relation R is decomposed into two or more relations if decomposition is lossless join as well as dependency preserving.

Lossless Join Decomposition

If we decompose a relation R into relations R1 and R2,

Decomposition is lossy if R1 ⋈ R2 ⊃ R
Decomposition is lossless if R1 ⋈ R2 = R
To check for lossless join decomposition using FD set, following conditions must hold:

Union of Attributes of R1 and R2 must be equal to attribute of R. Each attribute of R must be either in R1 or in R2.
Att(R1) U Att(R2) = Att(R)
Intersection of Attributes of R1 and R2 must not be NULL.
Att(R1) ∩ Att(R2) ≠ Φ
Common attribute must be a key for at least one relation (R1 or R2)

R1(ABC) and R2(AD) which is a lossless join decomposition as

6.17- Convert a relation into 2nf | Second Normal Form | Functional Dependency And Normalization    • 6.17- Convert a relation into 2nf | Second...  

6.18- Third normal form In DBMS With Easy Example | 3nf in dbms | Normalization in dbms    • 6.18- Third normal form In DBMS With Easy ...  

6.19- Numerical Convert A Relation Into 3nf | Convert relation in third normal form | normalization    • 6.19- Numerical Convert A Relation Into 3n...  

Decomposition in DBMS removes redundancy, anomalies and inconsistencies from a database by dividing the table into multiple tables.

The following are the types −

Lossless Decomposition
Decomposition is lossless if it is feasible to reconstruct relation R from decomposed tables using Joins. This is the preferred choice. The information will not lose from the relation when decomposed. The join would result in the same original relation.

6.17- Convert a relation into 2nf | Second Normal Form | Functional Dependency And Normalization    • 6.17- Convert a relation into 2nf | Second...  

6.18- Third normal form In DBMS With Easy Example | 3nf in dbms | Normalization in dbms    • 6.18- Third normal form In DBMS With Easy ...  

6.19- Numerical Convert A Relation Into 3nf | Convert relation in third normal form | normalization    • 6.19- Numerical Convert A Relation Into 3n...  

6.20- Properties Of Normalization- Lossless join & Dependency Preservation Property of Normalization    • 6.20- Properties Of Normalization- Lossles...  

6.21- Lossless Join Property Of Normalization | Lossless Decomposition | Normalization | dbms    • 6.21- Lossless Join Property Of Normalizat...   Notes Link:
http://www.tutorialsspace.com/Downloa...