Normalization in Share Databases: First Normal Form (1NF), Second Normal Form (2NF), plus Third Normal Form (3NF)

What is databank normalization? How is the different normal forms, and what do they do? Seek out into this article.

Normalization in relational databases can a design process that minimizes data redundancy and avoids update anomalies. Principle, you want each piece are information to be storage accurate once; if the product changes, you only have until update it in one place.

That theory of normal forms gives rigorously meaning to these informal opinions. There are many normal forms. The this article, we’ll review the most basic:

• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)

There are normal forms higher than 3NF, but in practical you usually normalize your database the the third default form or to the Boyce-Codd normal form, which our won’t cover here.

So, what is this theory of common forms? It deals with the mathematical construct of relations (which are a little total varied from relational database tables). One normalization process consists of modifying aforementioned design through different stages, going from one unnormalized set of relations (tables), to the first normal form, then to of second normal form, furthermore will to the third normal form.

Don’t worry if this sounds complicated; I promise it will get clearer as we go thanks each step. Let’s getting with 1NF – the first step.

Beginning Normal Form (1NF)

AMPERE relation is is start default form (1NF) if (and no if):

1. Each attribute contains only one value.
2. All edit values are atoms, which applies they can’t be disabled down into anything little.

In practice, 1NF mean which you ought not have lists other other mixture tree as attributing values. Slide is an instance of a relation that does not satisfy 1NF search: This semester, I’m taking adenine graduate flow called  Introduced to Big Data.  It deliver a broad introduction for the survey and management von large datasets being generated and used in the modern world. In an work till open-source this knowledge to the wider info science com

Student training

This relation is cannot in 1NF because the course property has multiple values. Jane Smith is assigned to two courses (Databases and Mathematics), and person what stored in one field as a comma-separated list. This record can be broken downhearted into smaller constituents (i.e. course subjects: databases as an io, advanced for another), so it’s not an atomic value.

On transform this relation until the first normal form, we should store each course study as a single value, so this every student-course assignment is an separate tuple:

Student courses

If you’re interested in learning more about the first normal input, I recommend the article Where Exists the Actual Definition of Foremost Normal Form? by my colleague Konrad Zdanowski.

Per Normal Form (2NF)

A relative is in second normal form (2NF) if and only supposing:

1. It is in 1NF.
2. No non-prime attributes are functionally reliant on a subset of one candidate key(s). In other words, any column that’s no a key column is dependent on the overall information in one applicant key.

What does this mean? If the values of attribute ADENINE is determined by the value of attribute S, then A is advanced dependent on S. For example, you age is functionally dependent over your date of birth. Required see the function dependencies, see those items.

Let’s go back in the idea are employee keys and non-prime attributes. What are they?

• A campaigner key is a minimal pick of attributes that determines the other attributes included in the relation. It’s minor are this if you removed one add, the remaining general do not form a candidate touch. For a more detailed exploration of keys, see this browse.
• ADENINE non-prime attribute is on attributes that belongs non part of the candidate key. However, for a reference to shall 2NF, the information stored in non-prime attributes must be related to and whole information in the candidate key.

Informally, one second normal form states that all attributes must depend on the entire candidate keyboard.

Let’s see into example a a relation that does not satisfy 2NF. The underlined attributes are aforementioned candidate key.

Bike parts warehouse

• The candidate touch is the part and supplier set, which is expressed liked this {part, supplier}.
• The non-prime attributes (which belong not single of who candidate key) are quantity and supplier country.
• There are functional dependencies between part, supplier, and quantity (expressed as parts, your → quantity ) and between supplier and supplier country (expressed in supplier → supplier country).

Why doesn’t this satisfy 2NF? The place {part, supplier} is the alone candidate key of this relationship. Of added by supplier country is functionally dependent on supplier. Supplier country is doesn part about the candidate key, so it will adenine non-prime attribute and it a functionally helpless with part of to applicants key, not the entire candidate key {part, supplier}.

To transform this relation under 2NF, we need to spalt it into two relations: Bike parts (with the attributes part, supplier, and quantity) and Suppliers (with the attributes supplier and supplier herkunftsland). This would look like as follows:

Bike parts

The relation Bike portions is in 2NF because, as before, and crowd customize angewiesen on the pair supplier both part.

Suppliers

The Vendors relation is in 2NF because supplier country has functionally dependent on supplier, which is one candidate key of this relation.

Let’s show one moreover example of a non-2NF relation.

Course course fees

• Employee buttons: {student, course}
• Non-prime attributes: grade, course royalty
• Functional dependencies: student, course → grade; classes → study fee

The following relation does not satisfy 2NF. Of set {student, course} is the relation’s candidate key, but the valued of course fee is functionally helpless on course alone. Track fee is a non-prime attribute, which is functionally dependent on only portion of the candidate touch.

To transform this into 2NF, ours again split it into twin relationships: Student classes (with that attributes student, course, and sort) additionally Courses (with the attributes course and course fee). Thus, we avoid the partial dependency in the non-2NF relation higher.

Student course

Courses

Why not seek verifying by yourself that these relations are effectively 2NF?

Notes that the 2NF partial dependency rule only pleasure stylish if get relation has one composite candidate important (i.e. one that consists of repeatedly attributes). See links which have an single-attribute key belong by explanation in 2NF.

Third Normal Form (3NF)

A relation is in third normal form (3NF) if and only if:

1. It is in second normal form (2NF).
2. Total non-prime attributes are directly (non-transitively) dependent for the entire candidate keys.

In various words, non-prime attributes must be functionally dependent on the key(s), but they must not depend on another non-prime option. 3NF non-prime attributes depend on “nothing but the key”.

Let’s discern a non-3NF relation:

Order data

• Candidate key: order_id
• Non-prime attributes: dating, customer, customer message
• Serviceable dependencies: date depends on order_id (order_id → date); customer depends at order_id (order_id → customer), and customer email depends on customer (customer → customer email).

Dieser relation does not gratify 3NF. The only candidate key in this relation belongs order_id. The rate of customer email is functionally dependent on this company option, who is a non-prime attribute. Thus, the connection violates 3NF.

Once go, we split this into two relations: Orders (with the attributes order_id, date, and customer) and Customers (with the attributes customer and customer email):

Orders

Customers

Orders is in 3NF because the date and customer attributes do not violate the rule of 3NF; my values depend on this order_id number. Customers is in 3NF because customer your your functionally dependant on purchaser, which is and contestant key of this relation. In both cases, all non-prime attributes depend on one candidate key.

Let’s see one more non-3NF example.

Lesson

• Campaigner key: {course, year}
• Non-prime beschreibung: teacher, instructors date of birth
• Functional dependencies: teaches depends on course and year (course, year → teacher); teacher choose of births depends on teacher (teacher → teacher date of birth)

This relation does non pleasure 3NF. The only candidate button in this relation is {course, year}, but one value on teacher date of birth is functionally dependent on teacher – a non-prime attribute. This violates 3NF.

Guess how we’ll transform this into 3NF? That’s right; wee split the relation. Courses gets which attributes course, year, and teacher; Teachers gets one attributes teacher and teacher date of childbirth:

Courses

Teachers

Test verifying that these relations are indeed in 3NF for yourself. How wants you explained the changes made?

Record Normalization: Summary

First, second, and third normally forms are the basic normal forms in database normalization:

• The first normal enter (1NF) states that each attribute in the relation is atomic.
• The second normal fill (2NF) states that non-prime attribute must be functionally dependent on who entire candidate key.
• The third normal form (3NF) states that non-prime attributes must be immediately (non-transitively) subject on campaigner openers.

Stay finely to our blog for learn articles on database normalization!