Normalization and Database Design

The most confusing concept of Database. Most people find it confusing. Okay, what if i say actually it is easy. Let’s talk about it.

• Anomalies(update, insert, delete)
• First Normal Form (1NF). Second Normal Form (2NF). Third Normal Form (3NF).
• Boyce & Codd normal form (BCNF). Fourth Normal Form(4NF). Fifth Normal Form(5NF)

Okay, as you know there are a lot of data over world and some or maybe most of them are wrong. Let’s look at the database design below:

data integrity fail | bad database design | not normalized

Before starting normalization we should know some concepts briefly:

database — an organized collection of related data which is stored electronically and can be managed for easy access, update and deletion.

data redundancy — it is duplicate of data. for example the same rows in a table.

Why is redundancy a problem? (Anomalies)

Anomalies are problems that happen when a database is poorly designed and contains unnecessary duplicated data. There are some anomaly problems: insertion, deletion, update anomaly

Second normal form

update anomaly

Imagine the department name is written in 20 different documents and its name is changed. If you update only 19 documents, some still contain the old names. Now the database contains conflicting information. This is an Update Anomaly.

insertion anomaly

A new course AI is created. But no student has enrolled yet.

You can’t insert because student data is mixed with course data. This is an Insertion Anomaly.

A university wants to create a new course. But the system doesn’t allow creating courses unless at least one student already exists. That’s a design problem.

deletion anomaly

Deleting the last student removes the course information. This is a Deletion Anomaly.

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

So far we used normalization term so much. Do you wonder What actually normalization is?

Think normalization as a set of rules to ensure your database design is good and well structured where data integrity doesn’t fail, no redundancy, so on. Maybe real life example could be better. Think you are going to an exam(as a student the first thing i thought)))) and you check whether you are ready for exam.

firstly, you check your university card because before entering the exam, you must have your university card. If you don’t have it, nothing else matters. (min safety guarantee)

subsequently, you check your pen because having a university card alone isn’t enough anymore. if you write on paper without pen you can’t write anything (better safety guarantee)

thirdly, now you have entered the university and have a pen. Next, you need to know the material. You satisfy all previous requirements plus another new requirement. (safety increases)

….. and so on it continues like these.

and let’s give another example which is likely more similar

Organising a Library

1NF Every book has exactly one place on the shelf. (No book stored in multiple piles or mixed lists.)

2NF Books are separated by category instead of repeating category information on every record.

3NF Publisher information is stored in a Publisher catalog instead of repeating it for every book.

Each step reduces duplication and improves organization.

it destroys 1NF because on line 3 cell has multiple values

In normalization there are some rules:

• redundancy is not allowed (the data cant be duplicated)

1NF:

1. mixing data types within the same column is not permitted
2. having a table without a primary key is not permitted
3. repeating groups are not permitted
4. using row order to convey info is not permitted

First Normal Form (1NF)

Rule: every cell holds one value only, and every cell is unique

Fix: one row per item

Second Normal Form (2NF)

Rule: must be in 1NF, and every non-key column must depend on the whole primary key — not just part of it.

Third Normal Form (3NF)

Rule:must be in 2NF and no non-key colmun should depend on another non-key column.

Every column should tell you something about the primary key — not about another column. This is called eliminating transitive dependencies.

Boyce–Codd Normal Form (BCNF)

Rule: a stricter version of 3NF. Every determinant (column that determines another) must be a candidate key.

3NF has a small loophole involving multiple overlapping candidate keys. BCNF closes it. In practice, if you’re in 3NF you’re usually in BCNF too — the difference only shows up in rare edge cases.

Fourth Normal Form (4NF)

Rule: must be in BCNF, and no table should store two or more independent one-to-many relationships in the same table.

If a teacher can teach many subjects AND speak many languages, and these facts are independent of each other — don’t combine them in one table. You’d create fake combinations.

Fifth Normal Form (5NF)

Rule: must be in 4NF, and the table cannot be split into smaller tables and then re-joined to give back the original without losing information — unless that split is based on candidate keys.

This is about join dependencies. Rarely encountered in practice. The rule says: if a 3-way relationship between A, B, C is not derivable from its pairwise combinations, keep them together.