1 / 11

Third Normal Form - 3NF

Third Normal Form - 3NF. It’s all about the key, the whole key, and nothing but the key. Third Normal Form (3NF). Overview. 3NF is a sufficient level of normalization for most databases.

tehya
Download Presentation

Third Normal Form - 3NF

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Third Normal Form - 3NF It’s all about the key, the whole key, and nothing but the key.

  2. Third Normal Form (3NF) Overview 3NF is a sufficient level of normalization for most databases. At this point, the data is sufficiently organized to be useful as information to most organizations or entities. The focus of 3NF is to eliminate dependencies among non-key fields.

  3. Third Normal Form (3NF) The Technical Definition • In order for a table to be considered 3NF, it must be 2NF and, consequentially, 1NF. • In order for a table to be considered 3NF, all fields in the table must have NO • Functional dependence upon non-key fields • Transitive relationships to the primary key • Essentially, all fields must be directly related to or dependent upon the primary key.

  4. Third Normal Form (3NF) The Layman’s Definition • Functional dependence • Field Order_Total is dependent upon fields • Order_Quantity • Order_Item_Price • The functional dependence is defined as • Order_Total = Order_Quantity X Order_Item_Price • Transitive dependence • Field Ship_Company is directly dependent upon • Shipment_ID (primary key) • Field Ship_Company_Address is dependent upon • Ship_Company to link or relate to • Shipment_ID

  5. Third Normal Form (3NF) Why Eliminate … • Functional dependence • Increases data redundancy and reduces independence • An update to one field requires update to others • Transitive dependence • Indicates a field is not describing the object represented by the record or table • Field describes another object and should be in that object’s table

  6. Third Normal Form (3NF) For Example… Two examples of functional dependence.

  7. Third Normal Form (3NF) For Example… Two examples of transitive dependence.

  8. Third Normal Form (3NF) Converting to 3NF • For each field, identify its dependencies and their type. • If it is a functional dependency on a non-key field, simply delete the dependent field.If there is a functional dependence, the value can be computed when the information is retrieved from the table. • If it is transitive dependence, treat it as a partial dependence in 2NF.Handle like in 2NF. Either move to the parent table or create a new table. • Repeat the process until database is 3NF.The database is 3NF when all tables in the database are 3NF.

  9. Third Normal Form (3NF) Corrected Example… Functional dependence: The dependent field has been discarded. FIXED: The tables are now 3NF!

  10. Third Normal Form (3NF) Corrected Example… Transitive dependence: The dependent field moved to own table. FIXED: The tables are now 3NF!

  11. Third Normal Form (3NF) In Summary… • Third normal form (3NF) is defined as • 1NF and 2NF • Having no functional dependencies • Having no transitive dependencies • 3NF is a sufficient normalization for most databases. • 3NF guarantees independence and eliminates data redundancy.

More Related