YT YouTube2Text

YouTube2Text → Transcripts → What is Normalization in SQL? | Database Normalization Forms - 1NF, 2NF, 3NF, BCNF | Edureka

What is Normalization in SQL? | Database Normalization Forms - 1NF, 2NF, 3NF, BCNF | Edureka — Transcript

by edureka! · 2,068 words · 310 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:11dito in the database is stored in terms
0:13of enormous quantity retrieving certain
0:16data will be a tedious task if the data
0:18is not organized correctly with the help
0:21of normalization we can organize this
0:23data and also reduce the redundant data
0:26hey guys this is predict from Ed Eureka
0:29and I welcome you all to this
0:30interesting session on normalization in
0:32SQL in this session I'll explain
0:35everything that is related to
0:36normalization with simple examples that
0:39are easy to remember firstly let's look
0:41at the agenda for today's session so
0:44we're going to start off with
0:45understanding normalization and moving
0:47further we shall look at various types
0:49of normalization and those are first
0:51normal form second normal form third
0:54normal form and boyce-codd normal form I
0:56hope you guys are clear with the agenda
0:59but before moving further if you haven't
1:01subscribed to a channel then do
1:03subscribe to never miss out an update
1:04with that being said let's get started
1:07the first topic in today's session is
1:09what is normalization database
1:12normalization is a technique of
1:14organizing the data in the database it
1:17is a systematic approach of decomposing
1:19tables to eliminate data redundancy it
1:21is a multi-step process that puts data
1:24into tabular form removing the
1:26duplicated data from its relational
1:28tables on the screen we just saw that
1:31the table is getting decomposed into two
1:33smaller table is it really necessary to
1:36normalize the table that is present on
1:38the database well every table in the
1:40database has to be in the normal form so
1:43normalization is used mainly for two
1:45purpose so the first one is it is used
1:48to eliminate repeated data having
1:50repeated data in the system not only
1:52makes the process flow but will cause
1:54trouble during the later part of
1:55transactions and second one is to ensure
1:58the data dependencies make some logical
2:00sense yes usually the data is stored in
2:03database with certain logic huge data
2:06sets without any purpose are completely
2:08waste it's like having an abundant
2:11resource without any application
2:13the data that we have should make some
2:15logical sense normalization came into
2:17existence because of the problems that
2:19occurred on data now let's look at those
2:22problems and these are known as data
2:24anomalies if a table is not properly
2:27normalized and has data redundancy then
2:30it will not only eat up the extra memory
2:32space but will also make it difficult to
2:34handle and update the data base let's
2:37look at the first anomaly that is
2:39insertion anomaly suppose for a new
2:42position in a company mr. Ratchett is
2:44selected but the department has not been
2:46allotted for him in that case if we want
2:49to update his information to the
2:50database we need to set the department
2:52information as null similarly if we have
2:55to insert data of thousand employees who
2:58are in similar situation then the
3:00department information will be repeated
3:02for all those thousand employees this
3:05scenario is a classical example of
3:07insertion anomalies the next one is
3:09update anomaly what if mr. Ratchett
3:12leaves the company or is in no longer
3:15the head of the marketing department in
3:16that case all the employee records will
3:19have to be updated and if by mistake we
3:22miss any record it will lead to data
3:24inconsistency this is nothing but
3:26updation anomaly and the final one is
3:30deletion anomaly in our employee table
3:33two different pieces of information are
3:35kept together that is employee
3:37information and Department information
3:39hence at the end of financial year
3:42if employee records are deleted we will
3:44also lose the department information
3:46this is nothing but deletion anomaly so
3:49these were some of the problems that
3:51occurred while managing the data to
3:53eliminate all these anomalies
3:55normalization came into existence
3:57there are many normal forms which are
3:59still under development but let's focus
4:01on the very basic and the essential ones
4:03only so we will be talking about first
4:06normal form second normal form third
4:09normal form and finally end this session
4:11with boyce-codd normal form so without
4:14wasting for the time let's proceed your
4:16first normal form
4:17in first normal form we tackle the
4:20problem of atomicity here at alma city
4:23means values in the table should not be
4:25further divided in simple terms a single
4:28cell cannot hold multiple values if a
4:31table contains a composite or
4:32multivalued attributes it violates the
4:35first normal form so the following
4:37functions will be performed in first
4:39normal form the first one is it removes
4:41repeating groups from the table and next
4:44it creates a separate table for each set
4:46of related data and finally it
4:49identifies each set of related data with
4:51the primary key to understand this in a
4:54better way let's look at the given table
4:56in the employee table we have employee
4:59ID employee name phone number and salary
5:03as columns we can clearly see that the
5:05phone number column has two values thus
5:08it violates the first normal form now if
5:11we apply the first normal form to the
5:13above table we get the following result
5:15in this table each and every row is
5:18listing that is no cell has multiple
5:21values the table has achieved a dhama
5:24city first normal form is simple and can
5:27be easily identified in the table we can
5:30clearly see there is no multiple values
5:32in each and every column thus the first
5:35normal form is achieved now let's move
5:38to the second normal form second normal
5:40form was originally defined by EF chord
5:43in 1971 a table is said to be in second
5:47normal form only when it fulfills the
5:49following condition the first condition
5:52is it has to be in first normal form and
5:54the second one is the table also should
5:57not contain partial dependency here
6:00partial dependency means the proper
6:02subset of a candidate key determines a
6:05non-prime attribute so what is a
6:07non-prime attribute let's understand
6:10this in a simple way attributes that
6:12form a candidate key in a table or
6:14called Prime attributes and the rest of
6:16the attributes of the relation are non
6:18prime for a table prime attributes can
6:21be like employee ID and Department ID
6:23and the non prime attributes can be like
6:26office location to understand second
6:29normal form let's consider this table
6:31this table has a composite primary key
6:34that is employee ID and department ID
6:37makes a primary key the non key
6:39attribute is office location in this
6:42case office location only depends on
6:44department ID which is only the power of
6:47primary key
6:48therefore this table does not satisfy
6:50the second normal form so what to do in
6:53such scenario the answer is simple
6:56split the table accordingly to bring
6:59this table to second normal form we need
7:01to break the table into two parts which
7:03will give the following tables the first
7:05table has employee ID and department ID
7:08as columns the second one has department
7:11ID and office location as columns as you
7:14can see we have removed the partial
7:16functional dependency that we initially
7:18had now in the table the column office
7:21location is fully dependent on the
7:23primary key of that table which is
7:25nothing but department ID I hope you
7:28understood second normal form now that
7:30we have learned first normal form and
7:32second normal form let's head to the
7:34next part of this normalization next
7:37topic is third normal form third normal
7:39form is a normal form that is used in
7:42normalizing the table to reduce the
7:44duplication of data and ensure
7:45referential integrity the following
7:48condition has to be met by the table to
7:50be in third normal form and the first
7:52condition is the table has to be in
7:54second normal form and the second
7:56condition is no non-prime attribute is
7:59transitively dependent on any non-prime
8:02attribute which depends on other non
8:04prime attributes
8:05I know it's bit confusing so let me make
8:08it simple for you it's like if C is
8:10dependent on B and in turn B is
8:13dependent on a then transitively C is
8:16dependent on a this should not happen in
8:18third normal form all the non prime
8:21attributes must depend only on the prime
8:23attributes
8:25these are the two necessary condition
8:26that needs to be attained so why was the
8:29normal form design firstly to eliminate
8:32undesirable data anomalies next one is
8:35to reduce the need for restructuring
8:37over time and finally to make the data
8:40model more informaiton since we have
8:43understood the third normal form let's
8:45look at the example table in the above
8:47table student ID determines subject ID
8:50and subject ID determine subject
8:52therefore student ID determines subject
8:55yr subject ID this implies that we have
8:58transitive functional dependency and
9:00this table does not satisfy the third
9:03normal form now in order to achieve the
9:06normal form we need to divide the table
9:08as shown below
9:09firstly let's divide the table and store
9:12student ID student name subject ID and
9:15address in it all the columns are
9:17referring to the primary key which is
9:19student ID let the second table have
9:22subject ID and subject column so subject
9:25is dependent only on subject ID and not
9:28on student ID as you can see from the
9:31above table all the non-key attributes
9:33are now fully functionally dependent
9:35only on the primary key in the first
9:37table column such as student name
9:40subject ID and address are only
9:42dependent on student ID in the second
9:45table subject is only dependent on
9:47subject ID with this being understood
9:50now we can proceed further to next
9:52normal form that is Boyce Codd normal
9:53form this is also known as 3.5 normal
9:57form it is the higher version of third
10:00normal form and was developed by Raymond
10:02F boys and Edgar F Codd to address
10:05certain types of anomalies which were
10:07not dealt with third normal form before
10:09proceeding to Boyce Codd normal form the
10:11table has to satisfy third normal form
10:13in Boyce Codd normal form if every
10:17functional dependency that is a implies
10:19B then a has to be the super key of that
10:22particular table so what is a super key
10:25a super key is a group of single or
10:27multiple keys which identifies rows in a
10:30table let's look at the table to clearly
10:33understand Boyce Codd normal form
10:35in the given table one student can
10:38enroll for multiple subjects there can
10:40be multiple professor teaching one
10:42subject and for each subject a professor
10:45is assigned to the student these are the
10:48necessary condition of the stable in
10:50this table all the normal forms are
10:52satisfied except boyce-codd normal form
10:55Y as you can see that student ID and
10:58subject form the primary key which means
11:01that the subject column is prime
11:03attribute but there is one more
11:05dependency that is professor is
11:07depending on subject and well subject is
11:10a prime attribute professor is a
11:12non-prime attribute which is not allowed
11:14by boyce-codd normal form now in order
11:17to satisfy the boyce-codd normal form we
11:20will be dividing the table into two
11:21parts the table at the top will hold
11:24student ID which already exists and we
11:27will create a new column that is
11:28professor ID and in the second table
11:31which is below we'll have the columns
11:33professor ID professor and subject
11:36columns why do we need to have a new
11:38column that is professor ID by doing
11:41this we are removing the non prime
11:43attributes functional dependency in the
11:46second table professor ID will be the
11:48super key of that table
11:49and remaining column will be
11:51functionally dependent on it by doing
11:53this we are satisfying boyce-codd normal
11:55form so this brings us to the end of
11:58this session I hope you have clearly
12:00understood the normalization and its
12:02different types if you have any queries
12:05or doubts regarding this session please
12:06let me know in the comment section and
12:08I'll get back to you with an answer
12:10thank you guys for watching this video
12:12and have a great day
12:14I hope you have enjoyed listening to
12:16this video please be kind enough to like
12:18it and you can comment any of your
12:21doubts and queries and we will reply
12:23them at the earliest do look out for
12:26more videos in our playlist and
12:27subscribe to any rekha channel to learn
12:30more happy learning

About this transcript

This page contains the full transcript of What is Normalization in SQL? | Database Normalization Forms - 1NF, 2NF, 3NF, BCNF | Edureka by edureka!, generated from the public captions YouTube serves with the video. The transcript has 2,068 words across 310 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.