PRE-REQUISTES

• If you are not familiar with keys , anomalies in database then read previous Blogs👇

PART-1 (Understand concept of all keys (theory) and what are anomalies in database)

PART-2 (CRUD operations in database , learn syntax & understand concept by doing)

Normal Forms

Normalization works through a series of stages called Normal forms. There are 6 Normal forms.

1NF (First Normal Forms)

• A relation will be 1NF if it contains an atomic value. It simply means that a column should contain a single value not multiple values .

• It states that an attribute of a table cannot hold multiple values.

❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌

❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌❌

✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅

✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅✅

Lets Understand with an example 🗯

In the above 👆 left side table If I want to delete 'Mo' from the Weekdays attribute . I can't delete If I delete whole data will be deleted that why its is mandatory to insert single value in an attribute. Maybe you can use more rows but to qualify for 1NF the data should be in atomic value.

2NF (Second Normal form)

For a table to be in 2NF must satisfy 2 conditions:- 👇

1. It should be in 1st Normal Form.

2. It should not have any Partial Dependencies.

1st Normal Form I already told you guys lets talk about Partial Dependency.

What is Partial Dependency?

Let's understand with an example :- Suppose I have a table with roll_no. & name . It means I have two attributes and now I am using both as a primary key . It means it is a composite key. I have a third attribute subject which is a non-key attribute . If roll_no. would be the primary key than name & subject would be functionally dependent on the roll_no. but now here the scenario is different because rollno.+name is my primary key . Now here subject is functionally dependent on rollno. but not on name so it is partial dependency . In the combination of two columns my subject column is dependent on only one that why it is a partial dependency. To be in 2NF my subject column should also be functionally dependent on name .

Let's understand with another example.👇

In this 👆 Student table their are 4 attributes.

student_id 👆 is the primary key in this table and unique for every row . Hence we can use student_id to fetch any row from the table. Even in a case where students names are same.👇

student_id is a primary key in this table.

A primary key is a column or a group of column which uniquely identifies each rows in the table.

For Example fetch me the Branch name of student with student_id=10 👇

Similarly If I want Name of student with student_id=10 I can get that as well. 👇

As non-key attributes are dependent on a key attributes. This is functional dependency as you all know very well. Now , you guys are in better state to learn what is Partial Dependency.

Now Subject table is created with two attributes one is a key attribute which is subject_id and another one is non-key attribute. As subject_id is a primary key so by this key we can uniquely identify data from another column subject_name.

Now we have Student table , Subject table & now I am creating a Score table to store marks of students of respective subjects. Name of the teacher is also saved with the subject which teacher teaches along with the marks.

Now we have here student_id to know which student's marks are these and subject_id of which subject these marks belongs to then marks and the name of he teacher.

If I want to fetch subject_id = 1 then I can't fetch marks due to insufficient data because I don't know name of the student . Right so that's why student_id+subject_id forms more meanigful primary key.👇

As you all know my primary key in the score_table is student_id+subject_id . Here we have a column name teacher which is only dependent on subject it has nothing to do with any other column this is known as Partial Dependency. 👇

for a table to be in 2NF Partial Dependency should not exist.

One of the ways to remove teacher column is to add that in subject table where it will be appropriate.

We can also create a teacher table with teacher_id & teacher_name like this.👇

Now my table is in 2NF because partial dependency is removed.

3NF (Third Normal form)

What is Transitive Dependency?

Transitive dependency is the easy to understand let's understand with an example. Here in this table as roll_no. is a primary key so all the other columns are functionally dependent on it. But somewhere subject is also dependent on name as they both are non-key attribute still they depend. So this is Transitive dependency and it should not be their as it cannot qualify the 3NF criteria.

Rest of Normal Forms I have shared in the next blog.

<------------END-------------->