Dbms 4NF & 5NF

Dbms 4NF & 5NF

• 1.
  SUBJECT – DBMS TOPIC– FOURTH NORMAL FORM(4NF) & FIFTH NORMAL FORM(5NF) 16-Dec-16 1
• 2.
  LEVELS OF NORMALIZATION •Levels of normalization based on the amount of redundancy in the database. • Various levels of normalization are: 1. First Normal Form (1NF) 2. Second Normal Form (2NF) 3. Third Normal Form (3NF) 4. Boyce-Codd Normal Form (BCNF) 5. Fourth Normal Form (4NF) 6. Fifth Normal Form (5NF) 16-Dec-16 2 Redundancy NumberofTables Complexity Most databases should be 3NF or BCNF in order to avoid the database anomalies.
• 3.
  FOURTH NORMAL FORM(4NF) 16-Dec-16 3 •Fourth normal form eliminates independent many- to-one relationships between columns. • To be in Fourth Normal Form, 1. a relation must first be in Boyce-Codd Normal Form. 2. 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.
• 4.
  16-Dec-16 4  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
• 5.
  16-Dec-16 5 Student_ID Subject 100 Music 100Accounting 150 Math 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} Example 1
• 6.
  16-Dec-16 6  Example 2 •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
• 7.
  16-Dec-16 7 Manager Child Jim Beth MaryBob 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} Example 2
• 8.
  FIFTH NORMAL FORM(5NF) 16-Dec-16 8 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.
• 9.
  16-Dec-16 9  Example 1 AgentCompany 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.
• 10.
  16-Dec-16 10 Agent Company Suneet ABC SuneetCDE Raj ABC  P1 Agent Product Suneet Nut Suneet Bolt Raj Bolt Raj Nut  P2 Company Product ABC Nut ABC Bolt CDE Bolt  P3 Example 1
• 11.
  16-Dec-16 11 • From abovetables 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 decomposition is called lossy decomposition. • 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.
• 12.
  THANK YOU 16-Dec-16 12