Second normal form and third normal form Part 53 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 53 of signal
server in this session we'll learn about
second normal form also commonly
referred to as 2nf and third normal form
which is commonly referred to as three
and F before continuing with the session
I strongly recommend to watch part 52 of
this video series where we have covered
the basics of normalization and the
first normal form so when do we say a
table is in second normal form it able
is said to be in the second normal form
if the table meets all the conditions of
the first normal form so the table has
to be already in the first normal form
and then the table should not contain
any redundant data so if the table
contains any redundant data move that
redundant data to a separate table and
then establish the relationship between
the two tables using foreign keys so
let's understand this let's see a table
that's not in second normal form and
let's see how to change the design in
such a way that those tables meet the
rules of the second normal form
so if you look at this table that I have
here which has got employee ID employee
name gender salary and then we also have
department name department head and
Department location columns now let's
assume in my organization I have hundred
thousand employees so obviously in this
employees table I'm going to have one
hundred thousand records but let's say I
only have two departments in my
organization IT and HR okay so if you
look at the department you know if you
look at this table you have employees
data and departments table at data join
together in a single table so obviously
you know since we know that in my
organization I only have two departments
and then 100,000 employees for all the
hundred thousand employees this IT and
HR you know columns should also be
repeated in the sense you know we know
that there are only two departments and
the department head and department
location does not change on a per
employee basis even if I have two
hundred thousand employees in my
organized
you know IT department head is going to
be John and its location is going to be
London and similarly HR departments
located is going to be Mike and the
location is going to be Sydney they are
not going to change on a per employee
basis but then since I am storing these
three columns within the employees table
and there are 200,000 employees in my
organization I have to repeat those
columns for every row so obviously
what's the problem here
this data is redundant and we know that
because of data redundancy there are
several problems what are the problems
of data redundancy obviously disk space
wastage you are unnecessarily repeating
department name department head and
department location information for
every employee that you have within this
organization in a bit we will see how to
break this down into you know two tables
and then reduce this redundancy but then
let's understand the problems of
redundancy first
so obviously disk space wastage and your
data could become inconsistent how let's
say for example you know I want to
update IT departments head name and
let's say there are you know 50,000
employees in the IT department now I
updated the department head name maybe
for 45,000 records in error I forgot to
update you know five records so for
those five records the IT department
head will be you know the old person
whereas for the updated ones it will be
new person for example let's say I
changed John to Josh the IT department
head but I forgot to change it you know
in this row and in the last row and when
I when we look at this data okay here IT
department hand is Josh but here it is
John John so is your data consistent
it's not consistent and why why are we
in such a situation because you have
duplicated data somebody updated few
rows but they forgot to update the other
rows you know which is actually leading
to data inconsistency so redundant data
can also lead to data inconsistencies
and obviously your DML queries which
include insert update and delete
statements could become slow as well why
because there are you know
large number of rules to process so
obviously if there are 100,000 employees
we have 100,000 records to update in as
far as the department head and
department location are concerned
okay but on the other hand if we move
this redundant data into a separate
table let's see how to split that so
what we are doing here we are splitting
this table into two you know one as TBL
employees which is going to store
employees information the other one as
t-bill departments table which is going
to store department information so here
I have department name department head
department location and then to uniquely
identify each row I have a primary key
here now since I have only two
departments within my organization in
the Theban departments table it's enough
if I store these two rows and then if
you look at the employees table the
regular columns employee ID name gender
and salary are present employee ID name
gender and cell they are present and
then we have this additional column
called Department ID which is nothing
but the foreign key so we are using this
column to establish the relationship
between employees and departments table
so if you look at the rules move
redundant data to a separate table so
this department redundant data we are
moving it to a separate table and then
we are creating the relationship between
these two tables using the foreign key
so we are satisfying the third rule of
the you know second normal form so this
table design is now in second normal
form so obviously we don't have a disk
space wastage there before these
normalization we were repeating three
columns now we are just repeating one
column and we have to do this we cannot
completely eliminate data redundancy
okay because if you remove this
department ID column from this
employee's table you will not know which
employee is belonging to which
department so we need to have some
amount or some level of redundancy okay
so we broken this down the stable into
two you know moving the redundant data
into departments table and establishing
the relationship between TBL employee
and TBL department using this foreign
key Department
I D so now this table design is in
second normal form obviously we don't
have disk space wastage we are only
storing two rows and your data will not
be inconsistent anymore because if you
change the department head name you'll
have to do that in this table you either
will do it or you don't do it it's not
now a matter of updating a few rows and
not updating the others and then when
you join this departments table with
this employees table obviously you know
people who belong to the same department
will have the same department head and
same department location and your
Duhamel queries can also be faster
because I just have one row to change if
I have to update a specific department
but whereas in this case if there are
you know 50,000 employees belonging to
IT department then probably I have
50,000 rows to update okay so our table
is in second normal form now let's say
you know about third normal form so a
table is said to be in third normal form
if the table meets all the conditions of
the first and second normal form and
then it does not contain columns or
attributes that are not fully dependent
upon the primary key now when we are
talking in terms of tables we call it
columns but when we are talking about
entity relationship models we call them
attributes and entities but here you
know we'll talk about columns and tables
okay so a table is said to be in the
third normal form if that table does not
contain columns that are not fully
dependent upon the primary key okay what
do we mean by this now if you look at
this table here employees table I've got
employee ID name gender salary and then
there is another column called annual
salary and then Department ID now
department ID except annual salary all
the other columns are fully dependent on
the primary key what do we mean by that
if the primary key changes let's say
instead of one two here so obviously the
employee changes spam and her gender
changes her salary changes you know the
annual
salary also changes but then this is not
fully dependent on the primary key this
annual salary is also dependent on the
salary of the person for example if my
salary is five hundred per month my
annual salary is 12 into five hundred
sixty thousand okay so this one is
actually dependent on salary and well
salaries depend on that sorry it's also
dependent on the employee ID but then
it's not wholly fully dependent on
employee ID it's also dependent on the
salary okay and then if you look at this
is there a need at all to save this
annual salary there's no need it's a
computed column on the fly in your query
you can actually compute the annual
salary of an employee so we don't have
to store those columns you know you can
say computed columns or in a way you can
also tell this column is not fully
dependent on the primary key it's also
dependent on the salary so you know if
they're not fully dependent of the
primary key then you can either remove
them or you can move to a separate table
okay moving to the separate table we'll
talk about that now in the second
example okay so if you look at the
second example so this table is not in a
normal you know third normal form so
what you can do to make this table and
table third normal form is remove that
column completely from this table
because why we can compute annual salary
now if you look at this table this again
you know which has got employee and
Department details okay so every
employee has got department name and
department head now if you look at this
you know employee name gender salary and
department name they are dependent on
the primary key now if you take one as
the employee ID he is Sam his gender is
male salary four thousand five hundred
ins Department is IT and department head
this also depends on employee ID when
the employee changes okay department
head is Mike okay
so it's also dependent on employee ID
but then
it's not fully dependent because
department head is also dependent on the
department name okay wherever you have
IT it's going to be John wherever you
have HR it's going to be Mike the
department head so this department head
it's not fully dependent on employee ID
it's also dependent on department name
okay so what we can do is move this you
know columns that are not fully
dependent on the primary key into its
own table so here we are breaking this
employees table into two so employee is
table here and departments table so
department name and department head and
we have given it a primary key and then
we are using that as a foreign key in
this table so a table is said to be in
the third normal form if it does not
contain any columns that are not fully
dependent upon the primary key so here
annual salary it's not fully dependent
on the primary key it's also dependent
on the salary
similarly department head it's not fully
dependent on the primary key it's also
dependent on the department name in the
case of annual salary you can simply get
rid of that because it's a computed
column it can be calculated on the fly
so to bring this table into third normal
form you simply get rid of that column
whereas if you want to get this column
this table into third normal form you
remove those columns that are not fully
dependent on the primary key into its
own table and establish relationships
between them so basically you know the
rules for the third normal form of that
they should satisfy the conditions of
the first and second normal form and it
should not contain any columns that are
not fully dependent upon the primary key
on this slide you can find resources for
asp.net c-sharp and sequel server
interview questions if you want to
receive email alerts when I post new
videos please subscribe to my youtube
channel that's it for today thank you
for listening
have a great day