Understanding Normalization & Database Design: 1NF, 2NF, 3NF, BCNF, & Decomposition, Slides of Database Management Systems (DBMS)

Download Understanding Normalization & Database Design: 1NF, 2NF, 3NF, BCNF, & Decomposition and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Normal Forms

Overview

• Database Normalization
• 1 st^ Normal Form
• 2 nd^ Normal Form
• 3 rd^ Normal Form
• Boyce- Codd Normal Form (BCNF)
• Lossless-Join

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

• No longer in 1NF because Telephone Number has a multivariable.
• Now we need to redesign our table

First Normal Form Example

• Not in First Normal forum
  • Tel. No. 3 is a null attribute
  • Tel. No. 1-2 repeat similar information (Repeating group)

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

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

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 Form

Example

• 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

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