Normal Forms Relationships Among Normal Forms Relations in

Normal Forms

Relationships Among Normal Forms Relations in 1 NF Relations in 3 NF Relations in BCNF Relations in 4 NF

Properties of Normal Forms Property 3 NF BCNF 4 NF Eliminates Redundancy due to FD's No Yes Eliminates Redundancy due to MVD's No No Yes Decomposition can always preserve FD's Yes No No Decomposition can always preserve MVD's No No No

If your database has lots of redundancy due to multivalued dependencies, which decomposition is best? 1. 3 NF 2. BCNF 3. 4 NF 4. Depends

If your database has lots of redundancy due to functional dependencies, which decomposition is best? 1. 3 NF 2. BCNF 3. 4 NF 4. Depends

If your database needs to preserve functional dependencies, which decomposition is best? 1. 3 NF 2. BCNF 3. 4 NF 4. Depends

If your database needs to preserve multivalued dependencies, which decomposition is best? 1. 3 NF 2. BCNF 3. 4 NF 4. Depends

What about Second Normal Form? Second normal form (2 NF) is basically never used. Nor for that matter is first normal form (1 NF). Neither provides properties that want from databases on their own. For completeness: A table is in 2 NF if it is in 1 NF and no non-prime attribute is dependent on any proper subset of any key of the name. ◦ A non-prime attribute of a table is an attribute that is not a part of any key of the table. In other words, a table is in 2 NF if it is in 1 NF and every non-prime attribute of the table is dependent on the whole of every candidate key. Don't worry about it, nobody (including me) cares about this form. Sorry, 2 NF nobody likes you.

Higher Normal Forms There are fifth and sixth and more normal forms that eliminate other forms of redundancy that are beyond the scope of this class. Those other types of redundancy are rarely present in databases, so the higher normal forms are rarely invoked.

What is normalization? Normalization is the process of converting your database schema to conform to one of the normal forms, and hence remove some form of redundancy.

Is normalization a good thing? 1. Always, otherwise your database is broken 2. Yes, if the properties of the normal form are desirable 3. No, they slow down development and cost programmer time. 4. Never, normal is boring, be strange!