Hi it's Jesper here i make data architecture in digital transformation videos on youtube
today i'm going to unpack data normalization the language of data data normalization is
both mathematics and philosophy and i think you will get a sense for this as the video progresses
doesn't explain everything but it gives us a deeper understanding of one particular kind of
data which is called the structure data and how to connect structured data and do more with it
such as automation analytics prediction artificial intelligence all those fun and good things and
coincidentally or perhaps not it also gives us an insight into the other side of data unstructured
data the things that sit in spreadsheets the things that being generated on the internet
put simply it's a perfect starting point for a greater and deeper understanding of data and how
data works and how data connects and what you potentially can do with it it uses a language
data modeling to show how data is connected and the nature of these connections or relationships
to tell a story this language is radically different to the language we normally use
it even has its own alphabet called cardinality but this will be covered in a separate video
we're used to process based thinking such as planning we use processes and process flows
workflows arrows etc to depict things in business how we do things the steps and the
sequences of achieving something in life we often describe ourselves as a process
if we ask that the partly to describe ourselves we often describe what we do not who we are
now it's getting philosophical a process describes what we do data describes who we are data
can exist without the process whereas the process must have data to exist you could say that data
is persistent whereas the process is not that raises the question so we thought the process
who are we very philosophical and certainly worthy of a serious dna conversation
but edgar court entered the scene and he wanted more than great in the conversations
he reduced data in data relationships into mathematics and in 1970 he released the relational
model which is a systematic approach of connecting and maintaining data based on mathematical rules
technology companies like oracle ibm microsoft amazon google used his relational model to
create their own relational databases popular open source databases like mysql are also based on it
but that's all technology let's forget about the technology for now doctor cod provides five
rules to normalize data where each rule builds on the other starting this first normal form
and ending with fifth normal form normalization is a gateway into deeper data understanding
because it addresses the thing that give data most meaning which is its relationships with other data
the magic of data lies in its relationships and types of relationships called cardinality
put simply normalization is about connecting data in the right way the first three rules of
normalization are about core basics whereas the latter two deal with exceptions hence
for practical reasons normalization typically refers to third normal form
and remember to be in third normal form it must also be in first and second normal form
so the focus today is normalization up to third normal form
first normal form is about atomic values and unique identifiers
let's say we want to model employees and their skills
and we have been handed this spreadsheet of data with the task of normalizing it
i've used spreadsheets as an example to make it easier to understand but the correct tool is
either to use table or entity first normal form specifies that the following actions need to be
taken on the data number one each cell may never contain more than one value for example a cell
cannot contain both skill id and skill name as a result we need to split into separate columns
number two each row must be unique that is one column or a combination of columns
must be able to uniquely identify the row this is called the primary key in this example name and
address would be a potential primary key yet often the primary key is system generated in our case we
will add a computer generated primary key the primary key is of great importance and features
prominently in all other normalization rules three it also means that each column name must be unique
and in this case we need to rename our skill columns to make them unique and four there
must be no repeating groups repeating groups are removed and put into a new spreadsheet or table
now we have two spreadsheets or tables with nice rows of data it is uniquely identified
each has no more than one value in each cell and there are no repeating group yay welcome to first
normal form but the fun doesn't stop here second normal form enforces new rules and states that
all data must depend on the primary key so let's first examine spreadsheet one name and address and
job names are all related to employee id so it's already in second normal form yay that's great
but what about the second spreadsheet skill name relates to skill id but not to employee id
second normal form stipulates that any column that don't depend on the whole primary key must
be split into its own spreadsheet or table so we need to create one more called employee skill
a primary key that links to other spreadsheets or tables are also called a foreign key
so in this case employee id is the foreign key of employee and skill id is a foreign key of skill
now i have three spreadsheets or tables with nice rows of data
but each column depends on whole primary key yay again welcome to second normal form
but dr codd still wasn't happy it introduced a set of type 2 rules called third normal form
third normal form also focuses on the primary key and states that the primary key must fully define
all columns and columns might not depend on any other key so let's examine our spreadsheets again
in skills skill id defines skill name and skill name does not relate to any other key
so it's satisfied third normal form in employee skills employee id and skill id has no other
columns and hence satisfied third normal form in employee employee id defines name and address
and name and address do not relate to any other key and hence satisfied third normal form
but employee id does not define job name hence violating third normal form
this means that job name needs to be split into its own spreadsheet and table and for
consistency we have created a computer-generated job id because the job id links employee and job
we need to create a new column job id in employee as we discussed in second normal form
any primary key that links spreadsheets or tables also become a foreign key
now we have four spreadsheets or tables with nice rows of data but a primary key defines
each non-key column welcome to third normal form in summary third normal form has transformed
one unnormalized spreadsheet or table into four normalized spreadsheets
i hope this has explained normalization and how to normalize data to third normal form
and if you enjoyed this video please hit like and subscribe hope to see you in my next video