Normal forms fourth and fifth.pptx

Fourth and Fifth normal forms

1. SUBJECT – DBMS TOPIC – FOURTH NORMAL FORM(4NF) & FIFTH NORMAL FORM(5NF)

2. LEVELS O F NORMALIZATION L e v e l s o f n o r m a liz a tion b a s e d o n t h e a m o u n t of redundancy in the database. Various levels of normalization are: First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce-Codd Normal Form (BCNF) Fourth Normal Form (4NF) Fifth Normal Form (5NF) Number of Tables Most databases should be 3NF or BCNF in order to avoid the database anomalies.

3. FOURTH NORMAL FORM(4NF) Fourth normal form (4NF) is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key. It builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF). It states that, in addition to a database meeting the requirements of BCNF, it must not contain more than one multivalued dependency.  Properties –  A relation R is in 4NF if and only if the following conditions are satisfied:    It should be in the Boyce-Codd Normal Form (BCNF). the table should not have any Multi-valued Dependency.

4. A table with a multivalued dependency violates the normalization standard of Fourth Normal Form (4NK) because it creates unnecessary redundancies and can contribute to inconsistent data. To bring this up to 4NF, it is necessary to break this information into two tables.  To relation R be in Fourth Normal Form, a relation must first be in Boyce-Codd Normal Form. a given relation may not contain more than one multi- valued attribute. The multi-valued dependency X→Y holds in a relation R, if whenever we have two tuples of R that same in all the attributes of X, then we can swap their Y components and get two new tuples that are also in R.

5. Example 1 Primary key → {Student_ID , Subject , Activity } Many Student_ID have same Subject. Many Student_ID have same Activity. Thus violates 4NF. Student_ID Subject Activity 100 Music Swimming 100 Accounting Swimming 100 Music Tennis 100 Accounting Tennis 150 Math Jogging

6. Student_ID Subject 100 Music 100 Accounting 150 Math 16-Dec-16 6 Student_ID Activity 100 Swimming 100 Tennis 150 jogging Example 1(convert to 4NF) Old Scheme → {Student_ID , Subject , Activity} New Scheme → {Student_ID , Subject} New Scheme → {Student_ID , Activity}

7. Example 2 16-Dec-16 7 Primary key → {Manager , Child , Employee } Each manager can have more than one child. Each manager can supervise more than one employee. Thus violates 4NF. Manager Child Employee Jim Beth Alice Mary Bob Jane Mary NULL Adam

8. Manager Child Jim Beth Mary Bob 16-Dec-16 8 Manager Employee Jim Alice Mary Jane Mary Adam Example 2(convert to 4NF) Old Scheme → {Manager , Child , Employee } New Scheme → {Manager , Child} New Scheme → {Manager , Employee}

9. FIFTH NORMAL FORM(5NF) 16-Dec-16 9 A table is in the 5NF if it’s in 4NF and if for all join dependency of ( 𝑅 1 , 𝑅 2 , 𝑅 3 ,…….., 𝑅 𝑚 ) in R ,every Ri is a super key for R. A table is in the 5NF if it”s in 4NF and if it can’t have a loseless decomposition in to any number of smaller tables. It’s also known as Project-join normal form(PJ/NF). Fifth normal form is satisfied when all tables are broken into as many tables as possible in order to avoid redundancy. Once it is in fifth normal form it cannot be broken into smaller relations without changing the facts or the meaning.

10. Example 1 16-Dec-16 10 Agent Company Product Suneet ABC Nut Raj ABC Bolt Raj ABC Nut Suneet CDE Bolt Suneet ABC bolt The table is in 4NF because it contains no multi-valued dependency. Suppose that table is decomposed into it’s three relations P1,P2 & P3.

11. Agent Company Suneet ABC Suneet CDE Raj ABC 16-Dec-16 P1 Agent Product Suneet Nut Suneet Bolt Raj Bolt Raj Nut P2 Company Product ABC Nut ABC Bolt CDE Bolt P3

12. From above tables or relations if we perform natural join between any of two above relations i.e P1 ⋈ P2 , P2 ⋈ P3 or P1 ⋈ P3 then extra rows are added so this dec o mp o sitio n i s c a l l ed loss y deco mp o sition . But if we perform natural join between the above three relation then no extra rows are added so this decomposition is called loseless decomoposition . So, above three tables P1,P2 and P3 are in 5NF. 16-Dec-16