Database Normalization

Normalization

(Description with Examples)

Main Table:

Suppliers and Parts By Company Division
Company	Company Founder	Company Logo	Division	Part Type	Supplier	Supplier Country	Supplier Continent
Allied Clock and Watch	Horace Washington	Sundial	Clocks	Spring Pendulum Spring Toothed Wheel	Tensile Globodynamics Tensile Globodynamics Pieza de Acero Pieza de Acero	USA USA Mexico Mexico	N. Amer. N. Amer. N. Amer. N. Amer.
Allied Clock and Watch	Horace Washington	Sundial	Watches	Quartz Crystal Tuning Fork Battery	Microflux Microflux Dakota Electrics	Belgium Belgium USA	Europe Europe N. Amer.
Global Robot	Nils Neumann	Gearbox	Industrial Robots	Flywheel Axle Axle Mechanical Arm	Wheels 4 Less Wheels 4 Less TransEuropa TransEuropa	USA USA Italy Italy	N. Amer. N. Amer. Europe Europe
Global Robot	Nils Neumann	Gearbox	Domestic Robots	Artificial Brain Artificial Brain Metal Housing Backplate	Prometheus Labs Frankenstein Labs Pieza de Acero Pieza de Acero	Luxembourg Germany Mexico Mexico	Europe Europe N. Amer. N. Amer.

First normal form (1NF)

·         A table must be guaranteed not to have any duplicate records; therefore it must have at least one candidate key.

·         There must be no repeating groups, i.e. no attributes which occur a different number of times on different records

Example:

Suppliers and Parts By Company Division

Company	Company Founder	Company Logo	Division	Part Type	Supplier	Supplier Country	Supplier Continent
Allied Clock and Watch	Horace Washington	Sundial	Clocks	Spring	Tensile Globodynamics	USA	N. Amer.
Allied Clock and Watch	Horace Washington	Sundial	Clocks	Pendulum	Tensile Globodynamics	USA	N. Amer.
Allied Clock and Watch	Horace Washington	Sundial	Clocks	Spring	Pieza de Acero	Mexico	N. Amer.
Allied Clock and Watch	Horace Washington	Sundial	Clocks	Toothed Wheel	Pieza de Acero	Mexico	N. Amer.
Allied Clock and Watch	Horace Washington	Sundial	Watches	Quartz Crystal	Microflux	Belgium	Europe
Allied Clock and Watch	Horace Washington	Sundial	Watches	Tuning Fork	Microflux	Belgium	Europe
Allied Clock and Watch	Horace Washington	Sundial	Watches	Battery	Dakota Electrics	USA	N. Amer.
Global Robot	Nils Neumann	Gearbox	Industrial Robots	Flywheel	Wheels 4 Less	USA	N. Amer.
Global Robot	Nils Neumann	Gearbox	Industrial Robots	Axle	Wheels 4 Less	USA	N. Amer.
Global Robot	Nils Neumann	Gearbox	Industrial Robots	Axle	TransEuropa	Italy	Europe
Global Robot	Nils Neumann	Gearbox	Industrial Robots	Mechanical Arm	TransEuropa	Italy	Europe
Global Robot	Nils Neumann	Gearbox	Domestic Robots	Artificial Brain	Prometheus Labs	Luxembourg	Europe
Global Robot	Nils Neumann	Gearbox	Domestic Robots	Artificial Brain	Frankenstein Labs	Germany	Europe
Global Robot	Nils Neumann	Gearbox	Domestic Robots	Metal Housing	Pieza de Acero	Mexico	N. Amer.
Global Robot	Nils Neumann	Gearbox	Domestic Robots	Backplate	Pieza de Acero	Mexico	N. Amer.

Second normal form (2NF)

·         The table must be in 1NF.

·         None of the non-prime attributes of the table are functionally dependent on a part (proper subset) of a candidate key; in other words, all functional dependencies of non-prime attributes on candidate keys are fully functional dependencies. For example, consider a "Department Members" table whose attributes are Department ID, Employee ID, and Employee Date of Birth; and suppose that an employee works in one or more departments. The combination of Department ID and Employee ID uniquely identifies records within the table. Given that Employee Date of Birth depends on only one of those attributes – namely, Employee ID – the table is not in 2NF.

·         Note that if none of a 1NF table's candidate keys are composite – i.e. every candidate key consists of just one attribute – then we can say immediately that the table is in 2NF

Example:

                            Suppliers and Parts By Company Division

Company	Division		Part Type			Supplier
Allied Clock and Watch	Clocks		Spring			Tensile Globodynamics
Allied Clock and Watch	Clocks		Pendulum			Tensile Globodynamics
Allied Clock and Watch	Clocks		Spring			Pieza de Acero
Allied Clock and Watch	Clocks		Toothed Wheel			Pieza de Acero
Allied Clock and Watch	Watches		Quartz Crystal			Microflux
Allied Clock and Watch	Watches		Tuning Fork			Microflux
Allied Clock and Watch	Watches		Battery			Dakota Electrics
Global Robot	Industrial Robots		Flywheel			Wheels 4 Less
Global Robot	Industrial Robots		Axle			Wheels 4 Less
Global Robot	Industrial Robots		Axle			TransEuropa
Global Robot	Industrial Robots		Mechanical Arm			TransEuropa
Global Robot	Domestic Robots		Artificial Brain			Prometheus Labs
Global Robot	Domestic Robots		Artificial Brain			Frankenstein Labs
Global Robot	Domestic Robots		Metal Housing			Pieza de Acero
Global Robot	Domestic Robots		Backplate			Pieza de Acero
Companies
Company	Company Founder				Company Logo
Allied Clock and Watch	Horace Washington				Sundial
Global Robot	Nils Neumann				Gearbox
Suppliers
Supplier		Supplier Country		Supplier Continent
Tensile Globodynamics		USA		N. Amer.
Pieza de Acero		Mexico		N. Amer.
Microflux		Belgium		Europe
Dakota Electrics		USA		N. Amer.
Wheels 4 Less		USA		N. Amer.
TransEuropa		Italy		Europe
Prometheus Labs		Luxembourg		Europe
Frankenstein Labs		Germany		Europe

Third normal form (3NF)

·         The table must be in 2NF.

·         There are no non-trivial functional dependencies between non-prime attributes. A violation of 3NF would mean that at least one non-prime attribute is only indirectly dependent (transitively dependent) on a candidate key, by virtue of being functionally dependent on another non-prime attribute. For example, consider a "Departments" table whose attributes are Department ID, Department Name, Manager ID, and Manager Hire Date; and suppose that each manager can manage one or more departments. {Department ID} is a candidate key. Although Manager Hire Date is functionally dependent on {Department ID}, it is also functionally dependent on the non-prime attribute Manager ID. This means the table is not in 3NF.

Example

Not in 3NF:

Tournament Winners
Tournament	Year	Winner	Winner Date of Birth
Indiana Invitational Cleveland Open Des Moines Masters Indiana Invitational	1998 1999 1999 1999	Al Fredrickson Bob Albertson   Al Fredrickson Chip Masterson	21 July 1975 28 September 1968 21 July 1975 14 March 1977

*The only candidate key is {Tournament, Year}.

In 3NF:

Tournament Winners
Tournament	Year	Winner
Indiana Invitational Cleveland Open Des Moines Masters Indiana Invitational	1998       1999 1999 1999	Al Fredrickson Bob Albertson Al Fredrickson Chip Masterson

Player Dates of Birth
Player	Date of Birth
Chip Masterson Al Fredrickson Bob Albertson	14 March 1977 21 July 1975 28 September 1968

 

 

 

 

                                                                               

                               

Boyce-Codd normal form (BCNF):

The criteria for Boyce-Codd normal form (BCNF) are:

·         The table must be in 3NF.

·         Every non-trivial functional dependency must be a dependency on a superkey

Example:

Suppliers and Parts By Company Division
Company		Division		Part Type			Supplier
Allied Clock and Watch		Clocks		Spring			Tensile Globodynamics
Allied Clock and Watch		Clocks		Pendulum			Tensile Globodynamics
Allied Clock and Watch		Clocks		Spring			Pieza de Acero
Allied Clock and Watch		Clocks		Toothed Wheel			Pieza de Acero
Allied Clock and Watch		Watches		Quartz Crystal			Microflux
Allied Clock and Watch		Watches		Tuning Fork			Microflux
Allied Clock and Watch		Watches		Battery			Dakota Electrics
Global Robot		Industrial Robots		Flywheel			Wheels 4 Less
Global Robot		Industrial Robots		Axle			Wheels 4 Less
Global Robot		Industrial Robots		Axle			TransEuropa
Global Robot		Industrial Robots		Mechanical Arm			TransEuropa
Global Robot		Domestic Robots		Artificial Brain			Prometheus Labs
Global Robot		Domestic Robots		Artificial Brain			Frankenstein Labs
Global Robot		Domestic Robots		Metal Housing			Pieza de Acero
Global Robot		Domestic Robots		Backplate			Pieza de Acero
Suppliers
Supplier			Supplier Country
Tensile Globodynamics			USA
Pieza de Acero			Mexico
Microflux			Belgium
Dakota Electrics			USA
Wheels 4 Less			USA
TransEuropa			Italy
Prometheus Labs			Luxembourg
Frankenstein Labs			Germany
Companies
Company		Company Founder			Company Logo
Allied Clock and Watch		Horace Washington			Sundial
Global Robot		Nils Neumann			Gearbox
Countries
Country	Continent
USA	N. Amer.
Mexico	N. Amer.
Belgium	Europe
Italy	Europe
Luxembourg	Europe

Fourth normal form (4NF):

The criteria for fourth normal form (4NF) are:

·         The table must be in BCNF.

·         There must be no non-trivial multivalued dependencies on something other than a superkey. A BCNF table is said to be in 4NF if and only if all of its multivalued dependencies are functional dependencies.

Example:

Companies
Company	Company Founder	Company Logo
Allied Clock and Watch	Horace Washington	Sundial
Global Robot	Nils Neumann	Gearbox
International Broom	Gareth Patterson	Whirlwind
International Broom	Sandra Patterson	Whirlwind
International Broom	Gareth Patterson	Sweeper
International Broom	Sandra Patterson	Sweeper
Suppliers and Parts By Company Division
Company	Division	Part Type	Supplier
Allied Clock and Watch	Clocks	Spring	Tensile Globodynamics
Allied Clock and Watch	Clocks	Pendulum	Tensile Globodynamics
Allied Clock and Watch	Clocks	Spring	Pieza de Acero
Allied Clock and Watch	Clocks	Toothed Wheel	Pieza de Acero
Allied Clock and Watch	Watches	Quartz Crystal	Microflux
Allied Clock and Watch	Watches	Tuning Fork	Microflux
Allied Clock and Watch	Watches	Battery	Dakota Electrics
Global Robot	Industrial Robots	Flywheel	Wheels 4 Less
Global Robot	Industrial Robots	Axle	Wheels 4 Less
Global Robot	Industrial Robots	Axle	TransEuropa
Global Robot	Industrial Robots	Mechanical Arm	TransEuropa
Global Robot	Domestic Robots	Artificial Brain	Prometheus Labs
Global Robot	Domestic Robots	Artificial Brain	Frankenstein Labs
Global Robot	Domestic Robots	Metal Housing	Pieza de Acero
Global Robot	Domestic Robots	Backplate	Pieza de Acero
Companies
Company
Allied Clock and Watch
Global Robot
International Broom
Company Logos
Company	Company Logo
Allied Clock and Watch	Sundial
Global Robot	Gearbox
International Broom	Whirlwind
International Broom	Sweeper
Company Founders
Company	Company Founder
Allied Clock and Watch	Horace Washington
Global Robot	Nils Neumann
International Broom	Gareth Patterson
International Broom	Sandra Patterson
Suppliers
Supplier	Supplier Country
Tensile Globodynamics	USA
Pieza de Acero	Mexico
Microflux	Belgium
Dakota Electrics	USA
Wheels 4 Less	USA
TransEuropa	Italy
Prometheus Labs	Luxembourg
Frankenstein Labs	Germany
Countries
Country	Continent
USA	N. Amer.
Mexico	N. Amer.
Belgium	Europe
Italy	Europe
Luxembourg	Europe

Fifth normal form (5NF):

The criteria for fifth normal form (5NF and also PJ/NF) are:

·         The table must be in 4NF.

·         There must be no non-trivial join dependencies that do not follow from the key constraints. A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys.

Example:

Part Types By Company Division
Company	Division	Part Type
Allied Clock and Watch	Clocks	Spring
Allied Clock and Watch	Clocks	Pendulum
Allied Clock and Watch	Clocks	Toothed Wheel
Allied Clock and Watch	Watches	Quartz Crystal
Allied Clock and Watch	Watches	Tuning Fork
Allied Clock and Watch	Watches	Battery
Global Robot	Industrial Robots	Flywheel
Global Robot	Industrial Robots	Axle
Global Robot	Industrial Robots	Mechanical Arm
Global Robot	Domestic Robots	Artificial Brain
Global Robot	Domestic Robots	Metal Housing
Global Robot	Domestic Robots	Backplate
Suppliers By Company Division
Company	Division	Supplier
Allied Clock and Watch	Clocks	Tensile Globodynamics
Allied Clock and Watch	Clocks	Pieza de Acero
Allied Clock and Watch	Watches	Microflux
Allied Clock and Watch	Watches	Dakota Electrics
Global Robot	Industrial Robots	Wheels 4 Less
Global Robot	Industrial Robots	TransEuropa
Global Robot	Domestic Robots	Prometheus Labs
Global Robot	Domestic Robots	Frankenstein Labs
Global Robot	Domestic Robots	Pieza de Acero
Parts By Supplier
Part Type	Supplier
Spring	Tensile Globodynamics
Pendulum	Tensile Globodynamics
Spring	Pieza de Acero
Toothed Wheel	Pieza de Acero
Quartz Crystal	Microflux
Tuning Fork	Microflux
Battery	Dakota Electrics
Flywheel	Wheels 4 Less
Axle	Wheels 4 Less
Axle	TransEuropa
Mechanical Arm	TransEuropa
Artificial Brain	Prometheus Labs
Artificial Brain	Frankenstein Labs
Metal Housing	Pieza de Acero
Backplate	Pieza de Acero
Companies
Company	Company Logo
Allied Clock and Watch	Sundial
Global Robot	Gearbox
Company Founders
Company	Company Founder
Allied Clock and Watch	Horace Washington
Global Robot	Nils Neumann
International Broom	Gareth Patterson
International Broom	Sandra Patterson
Suppliers
Supplier	Supplier Country
Tensile Globodynamics	USA
Pieza de Acero	Mexico
Microflux	Belgium
Dakota Electrics	USA
Wheels 4 Less	USA
TransEuropa	Italy
Prometheus Labs	Luxembourg
Frankenstein Labs	Germany
Countries
Country	Continent
USA	N. Amer.
Mexico	N. Amer.
Belgium	Europe
Italy	Europe
Luxembourg	Europe