[go: up one dir, main page]
Scribd
Upload
260 views19 pages

Normal Forms

The document discusses different normal forms for database normalization including: 1) First normal form (1NF) which requires attributes to be atomic and have unique names. 2) Second normal form (2NF) which requires tables to be in 1NF and have no partial dependencies. 3) Third normal form (3NF) which requires tables to be in 2NF and have no transitive dependencies. 4) Boyce-Codd normal form (BCNF) which requires tables to be in 3NF and have no reverse dependencies. 5) Fourth normal form (4NF) which requires tables to be in BCNF and have no multi-valued dependencies. Examples are provided to

Uploaded by

Jebastin Raj
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
260 views19 pages

Normal Forms

The document discusses different normal forms for database normalization including: 1) First normal form (1NF) which requires attributes to be atomic and have unique names. 2) Second normal form (2NF) which requires tables to be in 1NF and have no partial dependencies. 3) Third normal form (3NF) which requires tables to be in 2NF and have no transitive dependencies. 4) Boyce-Codd normal form (BCNF) which requires tables to be in 3NF and have no reverse dependencies. 5) Fourth normal form (4NF) which requires tables to be in BCNF and have no multi-valued dependencies. Examples are provided to

Uploaded by

Jebastin Raj
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
You are on page 1/ 19

Normal Forms

And
Types of Normal Forms
Define Normalization:

Database Normalization is a database Schema design technique, by


which an existing schema in modified to minimize redundancy and
dependency of data.

Normalization split a large table into smaller tables and define


relationships between them to increase the clarity in organizing data.

Why Normalization need?

Normalization is a process of organizing the data in database to avoid data


redundancy, insert anomaly, update anomaly, delete anomaly.

Let’s discuss about normal forms .


Types of Normal Forms:

 First Normal Form(1Nf)


 Second Normal Form(2NF)
 Third Normal Form(3NF)
 Boyce-Codd Normal Form(BCNF)
 Fourth Normal Form(4NF)
 Fifth Normal Form(5NF)
Rules For First Normal Form(1Nf):

 It should only have single(atomic) value attribute.

 Values stored in a column should be of the same


domain.

 All the column in a table should have Unique names.

 The order in Which data it Stored does not matter.


First Normal Form:

Table_Student
Roll_no Name Subject
101 AAA OS,CN
103 BBB JAVA
102 CCC C,C++

(This table is not in first normal form because the “Subject” column
contains multiple values)
After decomposing it into first normal form:

Table_Student
Roll_no Name Subject
101 AAA OS
101 AAA CN
103 BBB JAVA
102 CCC C
102 CCC C++
Rules For Second Normal Form(2Nf):

 Table should be in 1NF.

 The Table should not contain any Partial dependency.

 All non-prime attributes should depend on the table entire primary key not
on part of a primary key(i.e Candidate Key).

Prime attribute – Which is the part of the primary-key ,is known as prime
attribute.

Non-Prime attribute – Which is not a part of the primary-key ,is known as


Non-prime attribute.
Second Normal Form:

Stud_no Course_no Course_fee


101 C1 1500
101 C3 1500
102 C1 1500
103 C1 1500
104 C2 2000
104 C3 1500
105 C4 3000

(This table is not in second normal form)


After decomposing it into Second normal form:

Stud_no Course_no
101 C1 Course_no Course_fee
101 C3 C1 1500
102 C1 C2 2000
103 C1 C3 1500
104 C2 C4 3000
104 C3
105 C4
Rules For Third Normal Form(3Nf):

 Table should be in 2NF.

 It should not contain Transitive dependency.(i.e A non-key column


should not depend on another non-key column)

A table is in 3NF if it is in 2NF each functional dependency x→y at least one of the
following conditions:

i. X is a super key.

ii. Y is a prime attribute of table


Third Normal Form:
Book_ID Category_ID Category_Type Price
101 1 Competitive 600
102 2 Sports 350
103 2 Sports 275
104 1 Competitive 750
105 3 Novel 500
106 4 Poetry 350

(This table is not in Third normal form)


After decomposing it into Third normal form:

Book_ID Category_ Price


ID Category_ID Category_Type
101 1 600
1 Competitive
102 2 350
2 Sports
103 2 275
3 Novel
104 1 750
4 Poetry
105 3 500
106 4 350
Rules For Boyce Codd Normal Form(BCNF):

 Table Should be in 3Nf.

 There Should not be reverse dependency.

 X→Y ,Non-prime attributes values depend on prime attributes value


it’s called as a reverse dependency.

 X-Non-prime , Y-prime.
Boyce Codd Normal Form(BCNF):

Student_table
Student_id Subject Professor
101 Java Mr.Pravin
101 C++ Mrs.Nancy
102 Scripting lang Mr.Jeyasingh
103 C++ Mrs.Leena
104 Java Mr.Pravin

(This table is not in BCNF normal form)


After decomposing it into Boyce Codd Normal Form(BCNF):

Professor_table
Student_table
Professor_id Professor Subject
Student_id Professor_id
201 Mr.Pravin Java
101 201
202 Mrs.Nancy C++
102 202
203 Mr.Jeyasingh Scripting
103 203
lang
104 204
204 Mrs.Leena C++
Fourth Normal Form(4NF):

 Table should be a BCNF.

 It has no multi valued dependency.

How to Identify the Multi Valued Dependency:

 A table should have at least 3 Columns.

 For a dependency A→ B, for a single value of A , Multiple value of B exits.

 In a relation R(A,B,C) for a multi valued dependency A→B , then B and C


should be independent of each other.
Fourth Normal Form(4NF):
Roll_no Course Hobby
501 PHP Cricket
501 MYSQL Dancing (This table is not in 4NF normal
502 C Drawing form)
503 HTML Gaming
503 CSS Drawing

DATA DUPLICATION FOR ROLL-NO-501:


Roll_no Course Hobby
501 PHP Cricket
501 PHP Dancing
501 MYSQL Cricket
501 MYSQL Dancing
After decomposing it into Fourth Normal Form(4NF):

Roll_no Course Roll_no Hobby


501 PHP 501 Cricket
501 MYSQL 501 Dancing
502 C 502 Drawing
503 HTML 503 Gaming
503 CSS 503 Drawing
THANK YOU!!!

You might also like