Lecture 51 : Database Normalization(Part-2)

IIT Kanpur July 201
20 followers
2 Video Views·Oct 5, 2024
So let us switch to the first normalized form and let us see how we can transfer
our un-normalized form to the first. So it is also known as 1NF. So the
requirements of this normal form is that each entry in your table each entry must
be unique in the sense that there should be no repeated row. This is the first requirement
and each column entry means the row that is a tuple and each column that is an attribute.
Each column contains atomic values that is individual values.
So to create our first normal form from the previous table, we can again create a table.
Our fields are student id, name, course name, phone number. So the first row should be 141.303.
Name is Abhishek, course name is Mortal Design, phone number is 123.
Next row also belongs to Abhishek. Next student was Manish. So his student id was 305.
So now you may ask what has changed from the previous un-normalized form. So this is the
first normal form 1NF table for the table which we are studying. So in the un-normalized
form we had 4 rows only and there are composite values. But in the first normal form we have
1, 2, 3, 4, 5 rows. So we have increased one row, removed the composite variable. So these
were the requirements for first normal form. Each entry must be unique. So in the un-normalized
form if you see there are no repetitions. Repetitions could be that the same let's say
the third row could be again inserted into the table. So all the values of this row could
be repeated again. So your un-normalized form have these kinds of repetitions. You should
directly remove those rows because you don't need redundancy. So the first requirement
of the 1NF is you have no repeated rows. So we don't have any repeated rows. Now the next
is each column contains atomic values. So if you see the un-normalized form you have
student id are unique and there is nothing you can do about it. Same with the name. But
in the phone numbers there are some rows where there are multiple phone numbers. So these
are not atomic. So to create atomic columns you need to divide these into multiple columns.
So that is what we have done here. We used this row and divided it into another row as
well. So all the values remain the same but the phone number is different from this. So
what has changed? We introduced one additional row to get the
atomic value. So there are issues with this 1NF form as well. First is redundancy. Like
you can see there are multiple rows where id's and names are in repetition. And you
need to do multiple updates. How? So if any student changes his or her phone number then
you need to update in each row where the phone number is stored. So this is the problem
that you need to do multiple updates. And the last is delete issues. So let's say that
student gets un-enrolled from these courses that they have enrolled in. But all the information
about students are stored in this table and if let's say Abhishek drops his course on
model design and machining then his phone number is also lost. But maybe he is not doing
these courses or maybe he is not doing any courses in this current semester. But his
phone number that is his details will also get lost from the school or college management
system. So this is the problem of delete issues. So now let us create our first normal form
table in the MariaDB server. So we can create our table again.
So you can check how many tables you have now. There are two tables student-unnormalized
form and student1nf. Now you can check select start from student1nf and there is nothing
in this table right now. So you can start inserting the table. Insert into student1nf.
Now we will fill the values in a single command this time. So you can write the first is 141
So this is the one complete row. The first row. Second will be 1410. Name is again Abhishek.
Third row is about Manish. 1410305 is the row number. He is enrolled in fluid mechanics.
This is string. We should use single quotation marks and his phone number is 98765. Again
it is about Manish. So this is the last row. This is student1nf. This is student1nf. This
is the last row which is the new row for the first normal form. So we have provided
5 rows and there are 5 records in this. There are no duplicates and zero warnings. Now you
can again check. We can check the contents of our first normal form by providing select
star from student1nf. So now there is an atomicity in this table and the only difference is we
have a new row. So now let us move to the second normal form. It is also known as 2nf.
And the very first requirement of this is that your database table must be in the first
normal form which means that whatever is the requirement of the first normal form like
this no repeated rows and no atomic volume. These should be present in your database table.
After that only you can use the second normal form and make your tables more efficient.
So now what do we do here? We try to remove the non-key attributes and try to connect
it with the primary key only. So each non-key attribute should depend on primary key. So
we already know what is the primary key. It is the identifying information for a particular
row in the database table. So to do this step we need to do the following. We first need
to identify the composite key. So in our table that is in our first normal form the
composite key could be student id and the name of the student. So with these two identifying
information every other thing is related to this. And then we should remove columns that
don't depend on both parts of the primary key.
So now we will at this stage we will divide a single table. This single table of five
rows into two different tables to get it into second normal form. So the first table would
be student id, name and phone number. And phone number was 1, 2, 3. Student was 1, 4,
1, 2, 3, 0, 5. His name was Manish. And using these two composite keys the next non-key
attribute is his phone number which was 987. Since Manish has two phone numbers so we have
one more row in this table. 1, 4, 1, 0, 3, 0, 5. And his other phone number was 987.
So this is the first table of the second normal form. The second table could be the student
id and the course name. So let's say for the first student 1, 4, 1, 0, 3, 0, 1 which was
Abhishek. He is enrolled in machine design, model design. Second he is also enrolled in
machining course. And the student id 305 is associated with fluid mechanics. And he
is also associated with machine design. Now we have two tables. So this is the 2NF form.
And this student id is associated with the course name. And student id and name is associated
with the phone number in this first table. So what are the modifications to achieve this
second normal form? Phone and name depend on id. And we have created a new enrollment
table. So let's say this is the student table. And this is the enrollment table. Enrollment
of courses. So enrollment table will store only id and associated course. So this is
the second normal form. And this is an activity to all the students that they can write their
queries in the MariaDB server to create these two different tables and try to connect these
two with each other. Now let us switch to the issues because we have more normal forms
to study. So what is the problem with the second normal form? So there are some issues.
The first issue is the redundancy. And this is the major issue with this form. And let's
say this user Manish changes his phone number. Then we still need to update it into different
rows. So this is a very small table. But let's say you are managing a complete school or
college management system. So if we are storing the tables in this second normal form then
the problem is redundancy and updation of multiple rows. So now let us switch to the
third normal form. Again the requirement remains the same that you cannot create a 3NF table
without reaching until 2NF. So before doing the third normalization the database table
must be 2NF. And what do we do in this stage? That there should be no non-key attribute
that depends on another non-key attribute. So what does this mean?
If you see that currently name is a non-key attribute and you can uniquely identify a
row by using the student ID because usually the student ID is a unique identifier of a
student. So this is a non-key attribute. Phone is a non-key attribute. The name is a non-key
attribute. So there are multiple students with the name Abhishek. So let's say there
are two Abhisheks and they have different phone numbers and they are different people.
So if you assign name and phone numbers in a single table and you are saying that these
two non-key attributes can be stored in a single table then there should be many more
redundancies and these are not actual redundancies. So there are two different kinds of Abhishek.
Two different students with the same name Abhishek and they have different phone numbers.
So that is why we must associate the non-key attributes like name or phone numbers in different
tables with the student ID or a key attribute. So this is what we are saying here in the
first requirement. So how to achieve this? First try to identify non-key attribute dependent
on another non-key column. Then create a new table. So this new table will delete all the
transitive dependencies. So in 3NF we will further divide these two tables into one more
table. So the first table will have a student ID and name associated with it. So we have
1410301 and the name is Abhishek for this student. The next student was 1410305 and
the name was Manish. Now this is a small table and it is only telling that we have two students
in our school management system and this is a student table. And this is the primary key
and this is the data associated with the primary key. The second table again has a student
ID associated with it because it is the identifying information and the phone number. So the phone
number for this student was 122. So now at this stage we have also divided the phone
numbers and student ID into another table. So let's say this table is phone table and
this is again the student ID is the primary key attribute. And the third table will store
the information about courses where these roll numbers are enrolled. So this is the
enrollment table. It will also have student ID and course name. This is the enrollment
table. Now these three tables will be efficiently stored on your MariaDB server and this is
not the ultimate form but this is the most useful form and mostly database developers
will at least try 3NF. After that there are advanced forms as well like BCNF but 3NF is
a very useful form and you can have multiple tables. So storing multiple tables is not
an issue in your MariaDB or any DBMS server but what they do is the links between these
tables will try to retrieve or manipulate the data very efficiently and quickly. So
let us try to create these tables in your MariaDB server now. So we will first create
table student for 3NF and we have student ID and the constraint on this. So now we are
first using our constraints which we studied earlier. So student ID is a primary constraint
so we can write primary and the second attribute for this field is student name. It is a web
text with 100 characters limit and it has no constraint. So this is the simple table
definition and now we have a student table for third normalized form. Now we can insert
data into this table using insert into student 3NF, student ID, student name. What are the
values which we want to store? Value and let us provide multiple values together. So the
first row was the 1410301 as a student ID and the name was Abhishek. The second row
for the table was 1410305 and the name was Manish. These are the only two rows in this
table. So select star from student and the name is Abhishek.
So this is the first table for the student data and let us now create the second table.
So create table phone 3NF and it should have student ID which should be int the phone number
should be string and now let us define the primary key for this table. Primary key is
student ID and phone number and since this table is connected we should also write foreign
key. Foreign key is student ID and its reference is the table student 3NF and from there the
student ID field. So you can check what is the scheme of this table by using DESC that
is DESCRIBE statement. So there are two primary keys and these are connected. Now you can
insert the data. So insert into phone 3NF student ID and phone number. So this is the
second table. So now we have error. Why? Because while filling the student ID field
I have filled a wrong value that is 14101301 and while filling the current table I was
filling with 1410301. So why the database system has found that these two values are
not correct? Because we have created a foreign key with the student ID. So if you are not
providing the same student ID that is available in the previous table that means there is
something wrong and your DBMS is smart enough to find out and then it is highlighting that
there is an error either you should correct your previous value or you should correct
that value in the current table. So let us update your table as I have told you that
we had a wrong value filled in the table which was this 14101301 with the student name associated
student name was Abhishek. So to correct that we can write this update command that is update
which table that is student 3NF table and set the student ID to 1410301 where student
ID is this wrong value. So we are updating this information in the table. Now the current
table is correct. So now we can input the same value. So this was the actual thing that
the your RDBMS will tell you whenever you will try to connect it with the foreign key.
So foreign key assignment and the foreign key checks these constraints will be helpful
while filling your database. So find the same statement inserted into student 3NF. So we
were filling this phone table so insert into phone 3NF. So now we have our student information
regarding their phone numbers in the phone 3NF and this table is not a simple table it
is connected with foreign keys from the student ID that is the actual student 3NF table. Now
let us create the last table that is the table for the enrollment ID and the course enrollment
information. So create table first we need to create the schema of the table. So enrollment
1.4.3.NF and it has student ID that is int course name text string. Let us now define
the primary key for this table the same student ID.
Again define the foreign key to connect it with the actual tables defined above. So foreign
key is student ID and its references to the student 3NF table. From that table it will
use the student ID as the field attribute. So we missed a comma here
Now both of these fields are assigned as a primary key and we have created the schema.
So the last step is to insert the value. So enrollment 1.4.3.NF and it has student ID
and course name. What are the values? So the first student ID is 1410301. The course name
was model design. Second entry is also 1410301. The course name was machine learning. And
the last entry was 1410305. The course was machine design.
So we can cross check our data in the enrollment table as well and this is how we have the
third normal form of this table. So with this we have learned about three normal forms of
the table from un-normalized form to first normal form then second normal form and the
last is the third normal form. Now in the upcoming week we will try to develop our own
Karban accounting database and we will try to understand how to create different tables
and utilize that into a user interface of the database by creating that user interface
using Python programming language. Thank you.
