normal forms l.
Skip this Video
Loading SlideShow in 5 Seconds..
Normal Forms PowerPoint Presentation
Download Presentation
Normal Forms

Loading in 2 Seconds...

play fullscreen
1 / 23

Normal Forms - PowerPoint PPT Presentation

  • Uploaded on

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.
Download Presentation

Normal Forms

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.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

Normal Forms

By Christopher Archibald

October 16th 2007

  • Database Normalization
  • 1st Normal Form
  • 2nd Normal Form
  • 3rd Normal Form
  • Boyce- Codd Normal Form (BCNF)
  • Lossless-Join
  • Normalization is a technique for designing relational table to:
    • Minimize duplication of information
    • Reduce the potential for data anomalies
normal form
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
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
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 form example7
First Normal FormExample
  • No longer in 1NF because Telephone Number has a multivariable.
  • Now we need to redesign our table
first normal form example8
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
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
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 form example
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 example12
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
  • 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
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 form example
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 form example16
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
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 form example
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
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
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