1 / 14

Second Normal Form - 2NF

Second Normal Form - 2NF. It has to be the whole key. Second Normal Form (2NF). Overview. 2NF is an extension to 1NF that increases the organization in the database. A database is 2NF when all of the contained tables are 2NF.

marijke-nika
Download Presentation

Second Normal Form - 2NF

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. Second Normal Form - 2NF It has to be the whole key.

  2. Second Normal Form (2NF) Overview 2NF is an extension to 1NF that increases the organization in the database. A database is 2NF when all of the contained tables are 2NF. The focus of 2NF is to eliminate partial dependencies on primary keys. A table that is 1NF and uses a singular primary key is automatically 2NF.

  3. Second Normal Form (2NF) The Technical Definition • In order for a table to be considered 2NF • Must be 1NF • All fields should be dependent or related to the whole primary key • If a composite primary key is used, there must not be a field that is only related to a portion of the primary key

  4. Second Normal Form (2NF) The Layman’s Definition • In order for a table to be considered 2NF • No repeating groups (1NF) • All fields must be directly related to the entire primary key. • Tables that utilize a singular primary key are automatically 2NF, provided that they are 1NF.

  5. Second Normal Form (2NF) Non-Singular Primary Keys Are known as composite primary keys and can be problematic. It is a primary key that is composed of two or more fields. It is very common that fields in a composite key are also foreign keys. These fields are both foreign keys and partial primary keys.

  6. Second Normal Form (2NF) Why Use Composite Primary Keys? Sometimes their use is more logical than creating a new primary key. Sometimes values in fields produce a unique identifier when combined that is more useful than a random or sequential number. For Example Name + Date is easier to remember and use as a primary key than an automatically generated 10 digit number. John-Davis-14may07 VS 3245873245

  7. Second Normal Form (2NF) 2NF • Composite primary keys are the source of the problem that requires normalization to 2NF. • Only tables with composite primary keys have to be converted to 2NF. • Avoiding the use of composite keys avoids the problems • Linking a foreign key to two or more fields • Having to guarantee a database is 2NF • It can be summed up as • It’s about the key, the whole key.

  8. Second Normal Form (2NF) For Example… [ DMV ] In the example below, the table is not 2NF because it contains partial dependencies. Problem: Partial dependencies upon the primary key.

  9. Second Normal Form (2NF) For Example… [ DMV ] Let’s verbalize the relationships. • A Registration is the ownership by a person SSN for a vehicle VIN created on date Registered. • SSN, VIN, and Registered are the unique identifier for a registration. • Expires defines the date of expiration for a registration. • The county describes the geographical locale for a registration. • Mfg describes the manufacturer of the vehicle in the registration. • Make describes the model of the vehicle in the registration

  10. Second Normal Form (2NF) For Example… [ DMV ] From verbalizing we can see… SSN, VIN, and Registered are the fields that compose the primary key, thus it is a composite primary key. Expires and County refer to the whole primary key as they apply to the whole concept of a registration. Mfg and Make are partially dependent upon the primary key because they only describe the vehicle represented by VIN.

  11. Second Normal Form (2NF) Converting to 2NF • For each portion of a composite primary key, identify all the partially dependent fields.Find all the fields that are partially dependent upon the same key field. • If the key field is also a foreign key, synchronize the two tables and remove the partially dependent fields.Guarantee that the fields and their information are in the parent table, then remove them from the child table. • If the key field is not a foreign key, make it one and remove the partially dependent fields.Create a table for the object, move the partially dependent fields and data to the new table, and remove them from the child table. • Repeat the process until database is 2NF.The database is 2NF when all tables in the database are 2NF.

  12. Second Normal Form (2NF) For Example… [ DMV ] • Step One: Identify partially dependent fields. • SSN has no partially dependent fields. • VIN has two partially dependent fields. • Mfg • Make • Registered has no partially dependent fields. Problem: Partial dependencies upon the primary key.

  13. Second Normal Form (2NF) For Example… [ DMV ] • Step Two: Move partially dependent fields. • In this case, we guarantee that there is a vehicle table that contains Mfg and Make. • If not, we create the table and make VIN a foreign key linking the two. FIXED: The table is now 2NF!

  14. Second Normal Form (2NF) In Summary… • Second normal form (2NF) dictates that all fields in a table must be wholly related to the primary key. • A table must be 1NF before it can be 2NF. • Tables that are 1NF and do not use composite primary keys are automatically 2NF.

More Related