6.27- Multivalued Dependency In Normalization | 4 Normal Form | Functional Dependency | DBMS

6.27- Multivalued Dependency In Normalization | 4 Normal Form | Functional Dependency | DBMS

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

   • DBMS- Relational Database Design & Fu...  
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


Multivalued Dependencies and Fourth Normal Form
Definition: Let R be a relation having attributes or sets of attributes A, B, and C. There
is a multivalued dependency of attribute B on attribute A if and only if the set of B values
associated with a given A value is independent of the C values.
We write this as A →→ B and read it as A multidetermines B. If R has at least three
attributes, A, B, and C then in R(A, B, C), if A →→ B, then A →→ C as well.
Alternate definition of Multivalued Dependency
More generally, if R is a relation with multivalued dependency
A →→ B
then in any table for R, if two tuples, t1 and t2, have the same A value, then there
must exist two other tuples t3 and t4 obeying these rules
1. t3 and t4 have the same A value as t1 and t2
2. t3 has the same B value as t1
3. t4 has the same B value as t2
4. If R – B represents the attributes of R that are not in B, then the t2 and t3 have
the same values for R – B and
5. t1 and t4 have the same values for R – B
The dependency A →→ B is called a trivial multivalued dependency if B is a subset
of A or A ∪ B is all of R. Now we are ready to consider fourth normal form.
Definition: A relation is in fourth normal form (4NF) if and only if it is in BoyceCodd
normal form and there are no nontrivial multivalued dependencies.
Fourth Normal Form (4NF)
A relation is in 4NF if it is in BCNF and for all Multivalued Functional Dependencies
(MVD) of the form X →→ Y either X → Y is a trival MVD or X is a super key of relation.
Functional Dependency and Normalisation 225
Relations in BCNF also contains anomalies. Consider the relation Project-Work as shown
in Figure 6.18.
Project-Work
Programmer Project Module
P1 1 M1
P2 1 M2
P3 2 M1
P1 3 M1
P4 3 M2
FIGURE 6.18. Project-work relation.
Assumptions:
– A Programmer can work on any number of projects.
– A project can have more than one module.
Relation Project-work is in BCNF but still contains anomalies.
1. Deletion anomaly : If you delete project 2. You will loose information about Programmer
P3.
2. Insertion anomaly : If you want to add a new project 4. You cannot add this project
until it is assigned to any programmer.
3. Updation anomaly : If you want to change name of project 1. Then you have to
search all the programmers having project 1 and update them individually otherwise
it causes inconsistency.
Dependencies in Relation Project-work are
Programmer →→ Project
Project →→ Module
The solution of this problem is to divide relation Project-Work into two relations Prog-Prj
(Programmer, Project) and Prj-Module (Project, Module)

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

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

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

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

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