Normal Forms in Database Management: Understanding 1NF, 2NF, and 3NF

Data and Computers

Database Management

Normal Forms

The First Normal Form

A relationship is in its first normal form if it does not contain repetitive groups, that is, when it is structured as a collection of tuples and attributes A relationship is in the first normal form (1FN) when it meets the fundamental requirements of the relational model which are:

• All rows in the table contain the same number of columns;
• attributes represent elementary information (atomicity of attributes);
• the values that appear in a column are of the same type, i.e. they belong to the same domain;
• each row is different from all the others, i.e. there cannot be two rows with the same values in the columns;
• The order in which the rows appear in the table is irrelevant.

Data and Computers

Database Management

Normal Forms

The Second Normal Form

A relationship is in the second normal form (2FN) if it is in the first normal form, and all of its attributes that are not part of any key in the relationship are fully dependent on each key.

• Any partial functional dependencies that exist within the tables that make up the database schema are deleted.
• Partial functional dependencies are eliminated by extracting them from the original table and inserting them into new, specially created tables.
• In these new tables, you must delete any duplicate records. The role played in the original table by the newly extracted functional dependencies is taken over by the primary keys of the new tables.

Data and Computers

Database Management

Normal Forms

The Third Normal Form

A relationship is in the third normal form (3FN) if it is in the second normal form and if each of its attributes that are not part of any key depends directly and nontransitively on each key in the relationship.

• The third normal form requires that from a scheme in the second normal form all transitive functional dependencies are eliminated, i.e. that all columns are independent of each other.
• The third normal form makes it possible to distinguish homogeneous groups of information.

Data and Computers

Database Management

Normal Forms

Employee Category Salary Example

EMPLOYEE CATEGORY SALARY NERI 3 30.000 VERDI 3 30.000 ROSSI 4 50.000 MORI 4 50.000 BIANCHI 5 72.000 EMPLOYEE > CATEGORY CATEGORY ->SALARY VIOLATE THE 3NF BECAUSE CATEGORY IS NOT KEY AND SALARY IS NOT FIRST ATTRIBUTE IT'S IN 2NF BECAUSE SALARY DEPENDS ON THE KEY ALBEIT THROUGH CATEGORY! TRANSITIVE DEPENDENCE!

Data and Computers

Database Management

Normal Forms

Employee Salary Project Example

Employee Salary Project Budget Role Rossi 20 Marte 2 Technician Verdi 35 Giove 15 Designer Verdi 35 Venere 15 Designer Neri 55 Venere 15 Director Neri 55 Giove 15 Consultant Neri 55 Marte 2 Consultant Mori 48 Marte 2 Director Mori 48 Venere 15 Designer Bianchi 48 Venere 15 Designer Bianchi 48 Giove 15 Director Employee > Salary Project > Budget PARTIAL DEPENDENCE! VIOLATE 2NF!

Data and Computers

Database Management

Normal Forms

Name Course Professor Example

NAME COURSE CODE PROFESSOR INFO COMPUTER SCIENCE 2121 MARIO ROSSI, PROFESSOR, CODE 12345 MATH ANALYSIS 3213 LUCA BIANCHI, RESEARCHER, CODE 78910 VIOLATE 1NF!

Data and Computers

Database Management

Normal Forms Summary

The meaning of normal forms is summarized below:

1. First Normal Form: A relationship is said to be normal in its first form when it meets the fundamental requirements of the relational model, in particular each attribute is elementary, there are no equal rows and there are no repetitive attributes.
2. Second Normal Form: when it is in the first normal form and there are no non-key attributes that are partially dependent on the key.
3. Third Normal Form: when it is in the second normal form and there are no non-key attributes that are transitively dependent on the key The relational model requires only the first normal form as mandatory. However, the transition to the higher normal forms allows objects to be distinguished and separated precisely, without loss of information, even if a redundancy of data is generated, but it is constantly under control.

Data and Computers

Database Management

Normal Forms

The Boyce-Codd Normal Form (BCNF)

A relation is in normal Boyce-Codd form if for every existing functional dependency X->A, X is a superkey to the relation.

• A BCNF relationship is also in the third normal form, but the reverse is generally not true.
• It is used to avoid data anomalies, due to prime attributes that depend on non-prime attributes.

Database Management

Data and Computers

Normal Forms

Normalizing Considerations

Normalizing is not mandatory, but:

• eliminates anomalies, but can weigh down the execution of certain operations;
• The frequency with which the data is changed affects which is the most appropriate choice;
• Redundancy in non-normalized relationships must be quantified.

Database Management

Data and Computers

Normal Forms

Normal Form Properties

Summarizing:

A normal form is a property of a relational schema that guarantees its "quality", i.e. the absence of certain defects:

An unnormalized relationship has redundancies and results in undesirable behavior during updates The definition of normal forms (3NF and BCNF) is based on the functional dependency constraint (FD) Normalizing a schema means decomposing it into subschemas Any decomposition must be lossless, i.e. it must allow the original non- decomposed relationship to be reconstructed exactly It is also desirable that decomposition preserves FDs.

Data and Computers

Database Management

Normal Forms

Boyce-Codd Normal Form Characteristics

A relation is in the normal Boyce-Codd form (BCNF) when it respects the fundamental characteristics of the relational model (1NF) and in it every determinant is a candidate key, i.e. any attribute on which other attributes depend can act as a key.

The BCNF can also be expressed as follows: if functional dependency A -> B holds in a relationship, then the set of attributes A must contain a key (and thus can act as a key).

Database Management

Data and Computers

Normal Forms

BCNF and Other Normal Forms

It follows that a relationship that satisfies the NCB is also in its second and third normal form, since the BCNF excludes that a determinant may consist of only a part of the key, as is the case for violations of the 2FN or that it can be external to the key, as is the case with 3FN violations.

A relationship in BCNF is also in the third normal form, but the reverse is not true.