Scribd
Upload
5 views24 pages

Steps in Database Normalization Guide

The document outlines the steps in database normalization, detailing the requirements for First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Fifth Normal Form (5NF). It provides examples of student course enrollment and orders and customers, illustrating how to convert tables into normalized forms by removing multi-valued attributes, partial dependencies, and transitive dependencies. The document emphasizes the importance of achieving minimal redundancy and maximum data integrity in database design.

Uploaded by

mrcloquinario
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
5 views24 pages

Steps in Database Normalization Guide

The document outlines the steps in database normalization, detailing the requirements for First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Fifth Normal Form (5NF). It provides examples of student course enrollment and orders and customers, illustrating how to convert tables into normalized forms by removing multi-valued attributes, partial dependencies, and transitive dependencies. The document emphasizes the importance of achieving minimal redundancy and maximum data integrity in database design.

Uploaded by

mrcloquinario
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

Normalization

CIS1204 - Information Management I


Steps in Normalization
Steps in Normalization

[Link] Normal Form (1NF):


• Ensure each column contains atomic (indivisible) values.
• Identify and separate repeating groups into individual tables.
• Identify a primary key.
[Link] Normal Form (2NF):
• Meet the requirements of 1NF.
• Remove partial dependencies (ensure all non-key attributes
depend on the whole primary key).
• Split the table into two or more tables to eliminate redundancy.
Steps in Normalization

[Link] Normal Form (3NF):


Meet the requirements of 2NF.
Eliminate transitive dependencies (ensure all non-key
attributes depend only on the primary key).
Further split tables if necessary to achieve this.
[Link]-Codd Normal Form (BCNF):
Meet the requirements of 3NF.
Ensure every determinant is a candidate key.
Steps in Normalization

[Link] Normal Form (4NF):


Meet the requirements of BCNF.
Eliminate multi-valued dependencies.
[Link] Normal Form (5NF) and Domain-Key Normal
Form (DK/NF):
Address cases where certain normal forms may not fully
eliminate all anomalies.
Ensure minimal redundancy and maximum data integrity.
Example#1: Student Course Enrollment

StudentID StudentName Courses Instructor

101 Alice Smith Math, Physics John, Lisa

102 Bob Jones Chemistry Sarah

103 Carol Lee Math, Chemistry, Biology John, Sarah, Emma


Example#1: Student Course Enrollment
Step 1: First Normal Form (1NF)
Remove multi-valued attributes - each cell must hold a
single value
Example#1: Student Course Enrollment
Step 2: Second Normal Form (2NF)
Remove partial dependencies (i.e., fields that depend only
on part of a composite primary key).
Assume: (StudentID, Course) is the composite key.

We separate into two tables:


● Student Table
● Enrollment Table
Example#1: Student Course Enrollment

a. Student Table [Link] Table


Example#1: Student Course Enrollment
Step 2: Third Normal Form (3NF)
Remove transitive dependencies – non-key attributes
should depend only on the primary key.

We separate into three tables:


● Student Table
● Enrollment Table
● Course Table
Example#1: Student Course Enrollment

[Link] Table [Link] Table


Example#1: Student Course Enrollment

[Link] Table
Example#1: Student Course Enrollment

Converting to Relations
1. Converting to 2NF
Student

StudentID StudentName

Enrollment

StudentID Course Instructor


Example#1: Student Course Enrollment

Converting to Relations Course

[Link] to 3NF Course Instructor


Student

StudentID StudentName

Enrollment

StudentID Course Instructor


Example#2: Orders and Customers
Example#2: Orders and Customers
Step 1: First Normal Form (1NF)
Example#2: Orders and Customers
Step 2: Second Normal Form (2NF)
[Link] Table

[Link] Table
Example#2: Orders and Customers
Step 2: Second Normal Form (2NF)
[Link]
Example#2: Orders and Customers
Step 3: Third Normal Form (3NF)
[Link] Table
Example#2: Orders and Customers
Step 3: Third Normal Form (3NF)
[Link] Table

[Link] Table
Example#2: Orders and Customers
Step 3: Third Normal Form (3NF)
[Link] Table (Updated)
Example#2: Orders and Customers
Converting to relations
1. Converting to 2NF
a. Customer Table
CustomerID CustomerName CustomerAddress

[Link] TableOrderID CustomerID

OrderID Product ProductPrice


C. OrderDetails Table
Example#2: Orders and Customers
Converting to relations
2. Converting to 3NF
a. Customer Table

CustomerID CustomerName CustomerAddress


[Link] Table
OrderID CustomerID

c. Product Table
ProductID Product ProductPrice

d. OrderDetails Table

OrderID ProductID
Practice
• Change this in 1NF, 2NF and 3NF form based on the table
below.
• Convert it to relations
• Answer directly in 1 whole sheet of yellow paper

You might also like