Q

# An overview of normalization forms

## Our expert defines Fourth Normal Form and Fifth Normal Form.

Please define Fourth Normal Form and Fifth Normal Form? What are the advantages of using Fifth Normal Form?

The following answer is summarized from my book, Database administration: The complete guide to practices and procedures. The book goes into more details on normalization and walks through examples.

Normalization is a series of steps followed to obtain a database design that allows for efficient access and storage of data in a relational database. These steps reduce data redundancy and the chances of data becoming inconsistent. A table is said to be normalized if it satisfies certain constraints. Codd's original work defined three such forms but there are now five generally accepted steps of normalization. The output of the first step is called First Normal Form (1NF), the output of the second step is Second Normal Form (2NF), etc.

A row is in first normal form if and only if all underlying domains contain atomic values only. 1NF eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship. A row is in second normal form if and only if it is in first normal form and every non-key attribute is fully dependent on the key. 2NF eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key. A row is in third normal form if and only if it is in second normal form and every non-key attribute is non-transitively dependent on the primary key. 3NF eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key.

But normalization does not stop with 3NF. Additional normal forms have been identified and documented. However, normalization past 3NF does not occur often in normal practice because most tables in 3NF are usually also in 5NF. The additional normal forms are:

* Boyce Codd Normal Form (BCNF) is a further refinement of 3NF. Indeed, in his later writings Codd refers to BCNF as 3NF. A row is in Boyce Codd normal form if and only if every determinant is a candidate key. Most entities in 3NF are already in BCNF.

* An entity is in Fourth Normal Form (4NF) if and only if it is in 3NF and has no multiple sets of multi-valued dependencies. In other words, 4NF states that no entity can have more than a single one-to-many relationship within an entity if the one-to-many attributes are independent of each other.

* Fifth Normal Form (5NF) specifies that every join dependency for the entity must be a consequence of its candidate keys.

A comparison of BCNF and 3NF is given here:
http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node12.html

A complete definition of 4NF is given here:
http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node16.html

Finally, walk thru the presentation at this link for a nice introduction to normalization thru 5NF: http://syllabus.syr.edu/ECS/ilcoman/cse581/chap6p4mod/sld001.htm

This was first published in December 2002

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Oldest

• ### SQL engines boost Hadoop query processing for big data users

Organizations with big data environments are turning to SQL-on-Hadoop software to speed up analytical queries and data ...

• ### Reality check needed to assess AI applications

When assessing the reality behind today's AI technology, businesses need to think about how it can perform in specific tasks ...

• ### Experts advise on data science for business teambuilding

When it comes to building a data science team, businesses should expect to find workers from a variety of backgrounds rather than...

## SearchAWS

• ### Amazon Inspector gives dev automated security assessment

Cloud vulnerabilities can quickly evolve into security threats; vigilance is a key in identifying weaknesses. Amazon Inspector ...

• ### AWS, partners' balancing act weighs on users, too

AWS partners are a critical part of the growing ecosystem, but the choice between third-party services and the waiting game for ...

• ### Words to go: AWS data storage

If you're confused about which data storage option is ideal for your enterprise, refer to our reference sheet on AWS tools and ...

## SearchContentManagement

• ### Document control practices in the age of HIPAA

The time has come to bring information governance stakeholders together to develop a practical plan for document management and ...

• ### E-signature application saves insurer time and money

Insurance company Unum has experienced a dramatic reduction in document-processing time throughout the company since adopting ...

• ### Slack kicks up dust in collaboration software tool market

Slack is taking aim at traditional communication tools like instant messenger and email. But its integration with other services ...

## SearchOracle

• ### OAUG head Dues talks tech plans, Oracle cloud applications

OAUG president Patricia Dues talks about the technology that has the OAUG's attention and why it's important to learn about the ...

• ### ECCU shares ups, downs of Oracle Fusion Financials migration

Moving to Oracle Fusion Financials has been a mixed blessing for the Evangelical Christian Credit Union. It saved money, but had ...

• ### Oracle Enterprise Manager 13c gives DBAs new cloud tools

The latest version of Oracle Enterprise Manager is designed to make life easier for DBAs working in the cloud. Oracle Enterprise ...

## SearchSAP

• ### Integrate cloud to on-premises with HANA Cloud Integration

SAP offers a raft of prebuilt integrations that handle many of the key business processes between major cloud and on-premises ...

• ### Courtroom lessons from a failed SAP ERP implementation

A consulting firm's expert witness explains what SAP and a global manufacturer did -- and didn't do -- that led to a major SAP ...

• ### Building data visualization with SAP Fiori tools

Some BI developers will get by fine with features such as the Fiori Launchpad and Overview pages. Here's what's built into Fiori ...

## SearchSOA

• ### Finding harmony between middleware tools and emerging apps

Tom Nolle explains how developers and architects can navigate the bewildering world of middleware tools and create harmony ...

• ### Why model-driven software development deserves another look

Model-driven development has been around a long time, but in today's complicated app world, it may be time to give this ...

• ### Mobile enterprise solutions: A mobile strategy that'll work

Mobile technology changes rapidly. It's a challenge for any company to keep up to date. But some techniques can be applied to ...

## SearchSQLServer

• ### SQL Server 2016 T-SQL features add to DBCC CHECKDB and more

Expert Ashish Kumar Mehta shares some of the enhancements to T-SQL ahead in SQL Server 2016. This includes a DROP IF EXISTS ...

• ### Database trends to watch after SQL Server 2005 end of life

Two database industry experts discuss infrastructure and analytics trends to consider for database upgrades following the end of ...

• ### Why SQL Server 2005 end of life is good news for DBAs

Two database experts explain how to take advantage of the opportunity created by SQL Server 2005 end of life and how to build a ...

Close