6.15- Finding Candidate Key of a Relation By Closure | closure of attributes | Normalization

6.15- Finding Candidate Key of a Relation By Closure | closure of attributes | Normalization 6.14- How To Find The Closure Set Of Functional Dependencies With Example | Normal Forms | dbms 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 Closure of a Set of Functional Dependencies Assume that F is a set of functional dependencies for a relation R. The closure of F, denoted by F+ , is the set of all functional dependencies obtained logically implied by F i.e., F+ is the set of FD’s that can be derived from F. Furthermore, the F+ is the smallest set of FD’s such that F+ is superset of F and no FD can be derived from F by using the axioms that are not contained in F+ . If we have identified all the functional dependencies in a relation then we can easily identify superkeys, candidate keys, and other determinants necessary for normalization. Algorithm: To compute F+ , the closure of FD’s Input: Given a relation with a set of FD’s F. Output: The closure of a set of FD’s F. Step 1. Initialize F+ = F // F is the set of given FD’s Step 2. While (changes to F+ ) do Step 3. For each functional dependency f in F+ Apply Reflexivity and augmentation axioms on f and add the resulting functional dependencies to F+ . Step 4. For each pair of functional dependencies f1 and f 2 in F+ Apply transitivity axiom on f1 and f2 If f 1 and f 2 can be combined add the resulting FD to F+ . Step 5. For each functional dependencies to F+ Apply Union and Decomposition axioms on f and add the resulting functional dependencies to F+ . Step 6. For each pair of functional dependencies f1 and f 2 in F+ Apply Pseudotransitivity axiom on f1 and f 2 If f1 and f2 can be combined add the resulting FD’s to F+ . The additional axioms make the process of computing F+ easier by simplifying the process used for step 3 and 4. If we want to compute F+ only by using Armstrong’s rule than eliminate step 5 and 6. Example. Consider the relation schema R = {H, D, X, Y, Z} and the functional dependencies X→YZ, DX→W, Y→H Find the closure F+ of FD’s. Sol. Applying Decomposition on X→YZ gives X→Y and X→Z Applying Transitivity on X→Y and Y→H gives X→H Thus the closure F+ has the FD’s X→YZ, DX→W, Y→H, X→Y, X→Z, X→H Example. Consider the relation schema R= {A, B, C, D, E} and Functional dependencies A→BC, CD→E, B→D, E→A Sol. Applying Decomposition on A→BC gives A→B and A→C. Functional Dependency and Normalisation 203 Applying Transitivity on A→B and B→D gives A→D. Applying Transitivity on CD→E and E→A gives CD→A Thus the closure F+ has the FD’s A→BC, CD→E, B→D, E→A, A→B, A→C, A→D, CD→A. Notes Link: http://www.tutorialsspace.com/Downloa...