Scribd
Upload
98 views

Module 6 - Normalization PDF

The document discusses database normalization. It begins by defining normalization as a process of splitting a table into multiple related tables to avoid anomalies like insertion, update, and deletion anomalies. It then covers the different normal forms including 1st, 2nd, 3rd, Boyce-Codd, and 4th normal forms. Examples are provided to illustrate the normalization process and different normal forms. References and additional resources on database design and normalization are also listed.

Uploaded by

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

Module 6 - Normalization PDF

The document discusses database normalization. It begins by defining normalization as a process of splitting a table into multiple related tables to avoid anomalies like insertion, update, and deletion anomalies. It then covers the different normal forms including 1st, 2nd, 3rd, Boyce-Codd, and 4th normal forms. Examples are provided to illustrate the normalization process and different normal forms. References and additional resources on database design and normalization are also listed.

Uploaded by

Jacob Satorious Excalibur
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 10

Fundamentals of Database Systems

1
Database and Database Users

Module 6 Normalization

Course Learning Outcomes:


1. Understand what Normalization is
2. Learn what are the normal forms
3. Learn how to normalize relations

Introduction

Normalization is a process of producing a set of tables (relations) with a proper


attribute. In normalization you usually divide a table into 2 or more tables using
Normal Forms as a formal guide.
The goal of normalization is to remove redundancy and data modification problems
like:
• Insertion Anomaly
• Update Anomaly
• Deletion Anomaly

Two basic way to design a table

1. Normalization – Splitting a big table into multiple related table to avoid


anomalies.
2. Three (3) Level modeling approach:
a. Conceptual Design
b. Logical Design
c. Physical Design

Course Module
Example of an Anomaly

As you can see if you change one (1) domain, you also need to change the value of the other
domain. If you delete one domain, the rest of the row will also be deleted.

Example of a Normalized Tables


Fundamentals of Database Systems
3
Database and Database Users

Normal Forms
Steps use for normalization process

1st Normal Form (1NF)


The table is in a 1NF if
• It satisfies the definition of a relation
• NO “repeating groups” (columns)

Example of a Repeating Groups

Course Module
Avoid Repeating Groups by transforming the data into additional rows, rather than additional columns

There’s still a problem in 1st normal form.


The problems are:
• Redundancy
• Anomalies

2nd Normal Form (2NF)


The table is in a 2NF if
• It is in 1st NF, and
• NO Partial Dependency
Partial Dependency – a non-key attribute is dependent on part of a
composite primary key.

Example of a 1st NF
Fundamentals of Database Systems
5
Database and Database Users

How to transform 1st Normal Form into 2nd Normal Form


Steps
1. Identify the Primary Key (PK)
2. If Primary Key consists of only one field, then it is in 2NF
3. If PK is a composite key, then look for partial dependency
4. If there is partial dependency, move the partial dependency involved attributes to
another relation.

There’s still a problem in 2nd normal form.


The problems are:
• Redundancy
• Inconsistency

Course Module
3rd Normal Form (3NF)
The table is in a 3NF if
• It is in 2nd NF, and
• All attributes must, and only, be functionally dependent on the primary key
• NO Transitive Dependency
Transitive Dependency – indirect relationship causes functional
dependency.

Example of a 2nd NF

How to transform 2nd Normal Form into 3rd Normal Form


• Just move the attributes involved in a transitive dependency to another relation or table.
Fundamentals of Database Systems
7
Database and Database Users

Boyce Codd Normal Form (BC/NF)


It is a stricter form of 2nd and 3rd normal form

Example of a BC/NF

Course Module
4th Normal Form (4NF)
Multi-value dependency

Example of a 4NF
Fundamentals of Database Systems
9
Database and Database Users

References and Supplementary Materials


Books and Journals
1. Ramez Elmasri and Shamkant B. Navathe; 2016; Fundamentals of Database Systems;
USA; Pearson
2. Dr. Kashif Qureshi; 2018; Advanced concepts of information technology; educreation
publishing; India.
Online Supplementary Reading Materials
1. RelationalDBDesing; https://www.relationaldbdesign.com/basic-sql/module3/intro-
relational-databases.php; March 31, 2020
2. Advantages of Database Management System;
https://www.tutorialspoint.com/Advantages-of-Database-Management-System;
March 31, 2020
3. DesigningandManagingData;
https://www.academia.edu/36712448/Entity_Relationship_Diagram_ERD_Basics_CIS
_3730_Designing_and_Managing_Data; April 01, 2020
4. DesigningandManagingData; http://jackzheng.net/teaching/archive/cis3730-2010-
fall/; April 03,2020
Online Instructional Videos
1. Introduction to Database; https://www.youtube.com/watch?v=8e-
wgQnsFxE&list=PLJ5C_6qdAvBHKccG0ZyOxcf_2YO6r4Q4l; March 21, 2020
2. Three levels of Architecture/DBMS;
https://www.youtube.com/watch?v=j6xh8wKfjkY; April 01,2020
3. Relational Data Model; https://www.youtube.com/watch?v=TsSf1Z3g0Kk; Arpil 06,
2020.
4. Basic Concept of Database Normalization;
https://www.youtube.com/watch?v=xoTyrdT9SZI; April 08, 2020

Course Module

You might also like