Scribd
Upload
23 views27 pages

Relational Database Normalization Guide

The document discusses relational databases and normalization. It defines key concepts like relations, schemas, anomalies and normal forms. There are three types of anomalies - modification, insertion and deletion anomalies. Normalization is the process of assigning attributes to tables to reduce data redundancy and anomalies. It involves removing repeating groups and transitive dependencies to normalize relations from the first normal form (1NF) through the third normal form (3NF). Functional dependencies describe relationships between attributes that help determine the normal forms.

Uploaded by

mohd firhan jasni
Copyright
© Attribution Non-Commercial (BY-NC)
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
23 views27 pages

Relational Database Normalization Guide

The document discusses relational databases and normalization. It defines key concepts like relations, schemas, anomalies and normal forms. There are three types of anomalies - modification, insertion and deletion anomalies. Normalization is the process of assigning attributes to tables to reduce data redundancy and anomalies. It involves removing repeating groups and transitive dependencies to normalize relations from the first normal form (1NF) through the third normal form (3NF). Functional dependencies describe relationships between attributes that help determine the normal forms.

Uploaded by

mohd firhan jasni
Copyright
© Attribution Non-Commercial (BY-NC)
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

F3803 – DATABASE SYSTEM

UNIT 4
RELATIONAL DATABASE
NORMALIZATION
Relational Data Model

A two-dimensional table with columns


and rows is called a RELATION
What is Relation?
Skema Hubungan

Skema hubungan adalah senarai atribut yang terkandung


dalam hubungan tersebut
Format :
Nama_relation (Kunci Primer, Atribut Bukan Kunci)
• Nama relation : PUBLISHER
• Kunci Primer : Publisher_Code
• Atribut Bukan Kunci : Name, City

PUBLISHER (Publisher_Code, Name, City)


prepared by: nurizah@[Link]
• AUTHOR (Author_No, Author_Name)
• BOOK_AUTHOR (Book_Code, Author_No)
• BOOK (Book_Code, Book_Title, , Description,
Type, Price, Book_Sample, Book_Cover,
Publisher_Code)y)
C# and P# is a primary key for CUSTOMER and PRODUCT.
While the others is non-primary key.
Problem in Relational Data Model

Anomalies

Information
Redundancy
• Anomali Pengemaskinian (Update Anomalies)
• Anomali - Ralat yang mungkin berlaku apabila pengguna ingin mengemas
kini jadual (relation / table) yang mengandungi data yang berulang.

(a) Jenis-jenis Anomali:


i) Anomali Pengubahsuaian
(ii) Anomali penambahan
(iii) Anomali penghapusan

(b) Pertindanan Maklumat (Information Redundancy)


• Maklumat yang berulang yang disebabkan oleh penyimpanan data yang
sama beberapa kali.
Anomalies

• Errors or inconsistence that may result


when a user attempts to update a
table that contains redundant data
Anomalies • 3 types of anomalies ( modification,
insert & delete )

Information • Duplication of data due to storing the


Redundancy same data multiple times
Update/Modification Anomalies

e • The modification anomaly occurs when


at the record is updated in the relation. In
this anomaly, the modification in the
pd value of specific attribute requires
modification in all records in which that
U value occurs.
prepared by: nurizah@[Link]
Insert Anomalies

Insert

• The insertion anomaly occurs when


a new record is inserted in the
relation. In this anomaly, the user
cannot insert a fact about an entity
until he has an additional fact
about another entity.
prepared by: nurizah@[Link]
Delete Anomalies

•The deletion anomaly occurs


when a record is deleted from
Deletethe relation. In this anomaly,
the deletion of facts about an
entity automatically deleted
the fact of another entity
prepared by: nurizah@[Link]
Normalization

A process for assigning attributes


into a table and reduces data
redundancy and helps eliminate
the data anomalies that associated
with poor database design
(un-normalized)
• Penormalan (Normalisation)
• Satu proses untuk menempatkan atribut ke dalam
jadual bagi mengelakkan masalah anomali dan
pertindanan data yang akan menyebabkan pangkalan
data yang tidak stabil (Unnormalised).
•
• Jadual Unnormalised (Unnormalised Form (UNF))
• Jadual yang mengandungi satu atau lebih kumpulan
data yang berulang
The purposes of normalization

To ensure that the anomalies doesn’t occur

To reduce data or storage redundancy

To ensure easy for record searching/finding


Steps in Normalization

Remove repeating groups Remove transitive dependencies

UNF 1NF 2NF 3NF

Identify functional dependencies


Functional dependencies describe
Functional
Dependencies
relationships between attributes
in a relation
A relationship between attributes
in which one attribute or group of
attribute determines the value of
another in the same table
A Functional dependency between 2 or more
Dependencies
Transitive
non-key attributes
A condition in which an attribute is
dependent on another attributes that is not
part of the primary key
A relation is in 3NF if it is in 2NF and has no
transitive dependencies
Non-primary key attribute is functionally
dependent on another non-primary key
attribute

You might also like