26fcs157-normal forms carlos alvarado
DESCRIPTION
NFTRANSCRIPT
11stst, 2, 2ndnd, and 3, and 3rdrd Normal FormsNormal Forms
By By
Carlos AlvaradoCarlos Alvarado
San Jose State UniversitySan Jose State University
OverviewOverview
Today we’ll talk about:Today we’ll talk about: Database NormalizationDatabase Normalization
Data Anomalies Caused by:Data Anomalies Caused by: Update, Insertion, DeletionUpdate, Insertion, Deletion
Brief History/OverviewBrief History/Overview 11st st Normal FormNormal Form 22nd nd Normal FormNormal Form 33rdrd Normal Form Normal Form ConclusionConclusion
Database NormalizationDatabase Normalization
The main goal of Database The main goal of Database Normalization is to restructure the Normalization is to restructure the logical data model of a database to:logical data model of a database to:
Eliminate redundancyEliminate redundancy Organize data efficiently Organize data efficiently Reduce the potential for data Reduce the potential for data
anomalies.anomalies.
Data AnomaliesData Anomalies
Data anomalies are inconsistencies in the Data anomalies are inconsistencies in the data stored in a database as a result of an data stored in a database as a result of an operation such as update, insertion, and/or operation such as update, insertion, and/or deletion.deletion.
Such inconsistencies may arise when have a Such inconsistencies may arise when have a particular record stored in multiple locations particular record stored in multiple locations and not all of the copies are updated.and not all of the copies are updated.
We can prevent such anomalies by We can prevent such anomalies by implementing 7 different level of implementing 7 different level of normalization called Normal Forms (NF)normalization called Normal Forms (NF)
We’ll only look at the first three. We’ll only look at the first three.
Brief History/OverviewBrief History/Overview
Database Normalization was first proposed by Database Normalization was first proposed by Edgar F. Codd.Edgar F. Codd.
Codd defined the first three Normal Forms, which Codd defined the first three Normal Forms, which we’ll look into, of the 7 known Normal Forms.we’ll look into, of the 7 known Normal Forms.
In order to do normalization we must know what In order to do normalization we must know what the requirements are for each of the three Normal the requirements are for each of the three Normal Forms that we’ll go over.Forms that we’ll go over.
One of the key requirements to remember is that One of the key requirements to remember is that Normal Forms are progressive. That is, in order to Normal Forms are progressive. That is, in order to have 3have 3rdrd NF we must have 2 NF we must have 2ndnd NF and in order to NF and in order to have 2have 2ndnd NF we must have 1 NF we must have 1stst NF. NF.
11st st Normal FormNormal FormThe RequirementsThe Requirements
The requirements to satisfy the 1The requirements to satisfy the 1stst NF: NF: Each table has a primary key: minimal set Each table has a primary key: minimal set
of attributes which can uniquely identify a of attributes which can uniquely identify a recordrecord
The values in each column of a table are The values in each column of a table are atomic (No multi-value attributes allowed).atomic (No multi-value attributes allowed).
There are no repeating groups: two There are no repeating groups: two columns do not store similar information in columns do not store similar information in the same table.the same table.
11st st Normal FormNormal FormExampleExample
Un-normalized Students Un-normalized Students table:table:
Normalized Students table:Normalized Students table:
StudentStudent##
AdvIAdvIDD
AdvNamAdvNamee
AdvRooAdvRoomm
ClassClass11
ClassClass22
123123 123A123A JamesJames 555555 102-8102-8 104-9104-9
124124 123B123B SmithSmith 467467 209-0209-0 102-8102-8
StudentStudent##
AdvIAdvIDD
AdvNamAdvNamee
AdvRooAdvRoomm
ClassClass##
123123 123A123A JamesJames 555555 102-8102-8
123123 123A123A JamesJames 555555 104-9104-9
124124 123B123B SmithSmith 467467 209-0209-0
124124 123B123B SmithSmith 467467 102-8102-8
22nd nd Normal FormNormal FormThe RequirementsThe Requirements
The requirements to satisfy the 2The requirements to satisfy the 2ndnd NF:NF: All requirements for 1All requirements for 1stst NF must be met. NF must be met. Redundant data across multiple rows of Redundant data across multiple rows of
a table must be moved to a separate a table must be moved to a separate table.table.
The resulting tables must be related to each The resulting tables must be related to each other by use of foreign key.other by use of foreign key.
22nd nd Normal FormNormal Form Example ExampleStudents tableStudents table
Registration tableRegistration table
Student#Student# AdvIDAdvID AdvNameAdvName AdvRoomAdvRoom
123123 123A123A JamesJames 555555
124124 123B123B SmithSmith 467467
StudentStudent##
ClassClass##
123123 102-8102-8
123123 104-9104-9
124124 209-0209-0
124124 102-8102-8
33rdrd Normal Form Normal FormThe RequirementsThe Requirements
The requirements to satisfy the 3The requirements to satisfy the 3rdrd NF:NF: All requirements for 2All requirements for 2ndnd NF must be met. NF must be met. Eliminate fields that do not depend on Eliminate fields that do not depend on
the primary key;the primary key; That is, any field that is dependent not only That is, any field that is dependent not only
on the primary key but also on another field on the primary key but also on another field must be moved to another table.must be moved to another table.
33rdrd Normal Form Normal Form Example Example
Students table:Students table:
Student table:Student table: Advisor table:Advisor table:
Student#Student# AdvIDAdvID AdvNameAdvName AdvRoomAdvRoom
123123 123A123A JamesJames 555555
124124 123B123B SmithSmith 467467
StudentStudent##
AdvIAdvIDD
123123 123A123A
124124 123B123B
AdvIDAdvID AdvNamAdvNamee
AdvRooAdvRoomm
123A123A JamesJames 555555
123B123B SmithSmith 467467
33rdrd Normal Form Normal Form Example Cont. Example Cont.Students table:Students table:
Registration table:Registration table: Advisor table:Advisor table:Student#Student# Class#Class#
123123 102-8102-8
123123 104-9104-9
124124 209-0209-0
124124 102-8102-8
AdvIAdvIDD
AdvNamAdvNamee
AdvRooAdvRoomm
123A123A JamesJames 555555
123B123B SmithSmith 467467
StudentStudent##
AdvIAdvIDD
123123 123A123A
124124 123B123B
ConclusionConclusion
We have seen how Database We have seen how Database Normalization can decrease Normalization can decrease redundancy, increase efficiency and redundancy, increase efficiency and reduce anomalies by implementing reduce anomalies by implementing three of seven different levels of three of seven different levels of normalization called Normal Forms. The normalization called Normal Forms. The first three NF’s are usually sufficient for first three NF’s are usually sufficient for most small to medium size most small to medium size applications.applications.
ReferencesReferences
Hillyer Mike, MySQL AB. Hillyer Mike, MySQL AB. An Introduction to Database An Introduction to Database NormalizationNormalization, , http://dev.mysql.com/tech-resources/articles/intro-to-normalizationhttp://dev.mysql.com/tech-resources/articles/intro-to-normalization.html.html, accessed October 17, 2006., accessed October 17, 2006.
Microsoft. Microsoft. Description of the database normalization basicsDescription of the database normalization basics, , http://support.microsoft.com/kb/283878http://support.microsoft.com/kb/283878 , accessed October 17, , accessed October 17, 2006.2006.
Wikipedia. Wikipedia. Database Normalization. Database Normalization. http://http://en.wikipedia.org/wiki/Database_normalization.htmlen.wikipedia.org/wiki/Database_normalization.html , accessed , accessed October 17, 2006.October 17, 2006.