Normal Forms

1 / 23

# Normal Forms - PowerPoint PPT Presentation

Normal Forms. By Christopher Archibald October 16 th 2007. Overview. Database Normalization 1 st Normal Form 2 nd Normal Form 3 rd Normal Form Boyce- Codd Normal Form (BCNF) Lossless-Join. Normalization. Normalization is a technique for designing relational table to:

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Normal Forms' - raine

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Normal Forms

By Christopher Archibald

October 16th 2007

Overview
• Database Normalization
• 1st Normal Form
• 2nd Normal Form
• 3rd Normal Form
• Boyce- Codd Normal Form (BCNF)
• Lossless-Join
Normalization
• Normalization is a technique for designing relational table to:
• Minimize duplication of information
• Reduce the potential for data anomalies
Normal Form
• Normal forms provide a stepwise progression toward the goal of fully normalized relation schema that are free for data redundancies.
First Normal Form (1NF)
• 1NF definition: A schema R is in 1NF only when the attributes comprising the schema are atomic and single-valued
• No Multi-valued attributes
• No composite attributes
• No repeating groups (2 columns can not store similar information)
• Can’t have a Null Attribute
• Must have a Primary Key
First Normal Form Example
• This is in 1NF (Has primary Key, no repeating group, No Null attributes and No multivariable
• What happens if James gets a Second Phone Number?
First Normal FormExample
• No longer in 1NF because Telephone Number has a multivariable.
• Now we need to redesign our table
First Normal FormExample
• Not in First Normal forum
• Tel. No. 3 is a null attribute
• Tel. No. 1-2 repeat similar information (Repeating group)
First Normal Form
• This is in First Normal Form
• Telephone Number is no long a repeating group
• No Multivariable
• No Null Attributes
• Has a Primary Key
Second Normal Form (2NF)
• 2NF Definition: A relation schema R is in 2NF if every non-prime attribute in R is fully functionally dependent on the primary key of R.
• Must be 1NF
• An Attribute that is not part of the candidate key must be dependent on the candidate key and not a part of the candidate key
Second Normal FormExample
• Only Candidate key is (Employee, Skill)
• Not in 2NF
• Current Work Location is dependent on Employee
• Can Cause an Anomaly

Updating Jones Work location for Typing and Shorthand but not Whittling. Then asking “What is Jones current work location”, can cause a contradictory answer, because there are 2 different locations.

Second Normal Form Example
• Both tables are in 2NF
• Meets 1NF requirements
• No non-primary key attribute is dependent on part of a key.
1NF and 2NF
• 1NF and 2NF remove most anomalies
• Following table is in 2NF
• There is redundancy under Winner/Winner DoB
• Al Fredrickson and Chip Masterson
• Can cause an anomaly
Third Normal Form (3NF)
• 3NF Definition: A relation schema R is in 3NF if no non-prime attribute is functionally dependent on another non-prime attribute in R
• Table must be in 2NF
• Eliminate field that do not depended on the primary key by placing them in different tables
Third Normal FormExample
• Table is in 2NF but fails to meet 3NF
• Winner Date of Birth is Dependent on Winner
• If Al Fredrickson Date of birth is update in the first row but not the second ask, “What Al Fredrickson Date of birth” will result in 2 different dates.
Third Normal FormExample
• Table is in 3NF
• Meets 1NF and 2NF
• No non-primary Key attribute is Dependent on another non-primary Key attribute
• Update Anomalies cannot occur in these tables
Boyce-Codd Normal Form (BCNF)
• BCNF Definition: A relation Schema R is in BCNF if for every non-trivial functional dependency in R, the determinant is a superkey of R
• Does not allow Functional Dependency that is not part of a Candidate key
• Most 3NF meet the requirement of a BCNF
Boyce-Codd Normal FormExample
• Candidate key (Tutor ID, Student ID) And (SSN, Student ID)
• Table is in 3NF, but not BCNF
• SNN is dependent on Tutor ID but (Tutor id, SNN) is not a Candidate key
Other Normal Forms
• There is also
• Fourth normal form
• Fifth normal Form
• Domain/key Normal form
• Sixth normal form
• Which will be covered in chapter 9
Lossless-Join Decomposition
• The principle behind Lossless-Join decomposition is that the decomposition of a relation schema, R, should be strictly reversible, i.e.
• When we break tuples in to different tables for normalization we should be able to combined them and get what we started