hi guys this is Mina and welcome back to my channel today we are going to learn how to
design a relational data model using the three normal forms relational data model is also known
as the corporate information factory architecture although a dimensional model is also a form of
relational database the term relational database has often been used to refer to the corporate
information factory architecture so in this video we are going to stick to the universal term
relational database in my previous video dubbed introduction to data models i introduced you
to the types of data models for designing a data warehouse which are the relational and dimensional
data models and i also explained based on a case study the stages of data modeling which are the
conceptual logical and physical models i also have a video on dimensional data modeling techniques
explained based on a case study feel free to click on the links in the description section
to watch these videos especially the introduction to data modeling video as it will help you to
understand what we are going to learn in this particular video okay at the end of this tutorial
you would know what a relational data model is you will be able to design a relational data model
and understand the three normal forms i'll also explain the stages of data modeling and we are
going to base all our explanations on a case study so moving on to today's topic what is a
relational data model it is the process of organizing data into multiple related tables using
the normalization approach this technique was invented by E.F.Codd and propagated by Bill Inmon
so you'd ask what is data normalization well it's the use of set of rules to break a chunk of data
into multiple related tables and the purpose of that is to minimize data redundancy although
there are several data normalization rules, in this tutorial we are going to discuss the first three
normal forms or the first three normalization rules and if you are able to effectively apply
these first three sets of rules in your relational model your data will be well normalized and trust
me you won't be needing any other subsequent type of normal forms but for your knowledge you
can read further about the other normal forms i mentioned that we normalize data to minimize
data redundancy so what is data redundancy it is a term used to refer to duplication of data
within a database or even ophan records present in a database and you will see this as we move along
in this tutorial as i've mentioned previously our explanation of data normalization today
is going to be based on this case study and i've provided a copy of the case study below this video
in the description section so feel free to refer to it as you follow along and in this case study
you have a company that has several stores in multiple cities a company manager wants us to
track product sales in cold stores and design a data model that would meet the company's needs he
also provided us with source data and this is the data that he gave to us as data modelers to use
to help him solve his problem in relational data modeling this table is unacceptable at
first glance we can easily see that even this table violates the first normalization rule or
the first normal form because we have more than one values in some of the columns separated by
comma and also we have different data types within a particular column for instance we have this
three value which is an integer and then we also have four which is a text data type we cannot
mix data types within a particular column we have to stick to one data type so we need to resolve
this table and the first step is to normalize this table to the first normal form the first
normalization rule states that each attribute must contain a single value and each value within
a particular column must be of the same data type what do i mean by that taking this table that the
manager gave to us in order for us to certify the first normal form each column for instance taking
the city ID column each column has to contain a single value so we cannot have one and two all in
the same row we have to take one of those values to the next row so we have to take two to the next
row and do the same for three and four so in this table we supposed to get
four rows so at the end of the day for this original table to satisfy the first normal form
this is how it's supposed to look like the first column which is the city id has one, two, three, four
values so we break them down to four rows and then we move on to the city name
Winnipeg, Brandon as you can see it looks as if the one that represents Winnipeg and two represents
Brandon so we have broken them down for the silly name ideally you need to talk to the business to
find out if that is really how they intended to capture this data but looking at it it's
supposed to be broken down this way one thing that i also want to emphasize on is this product name
you can see that we have only one product name however we have two city names
city ids and store name which tells me that this particular product name which is crackers
was purchased two times but because it's the same product they decided to just write it once so
i have represented it here two times so in Calgary crackers were bought and also in Edmonton crackers
was sold okay again you need to talk to the business just to confirm that this is what they
actually intended to do under quantity sold we can see that there are two quantities represented here
one of the values is an integer and the other is a text we need to maintain the same data type
for our table to be in the first normal form so as you can see i've sticked to just one data type
in this particular quantity sold columns so you have two, three and then the next row we have four
and four i've also done the same thing for the amount column okay
so what we have done now is we have separated the data in this table into multiple
rows and this table satisfies the first normal form moving on to the second normal form the rule
states that the table must first be in the first normal form and there must not be any partial
dependency of any column on the primary key what does that mean let's take our first normalized
form table and see how we can conform it to the second normalization rule
here we have city name which fully depends on the city id a city id is a unique identifier of
the table or the primary key so city name fully depends on city id but we cannot say the same for
the rest of these columns so in the second normal form there shouldn't be any partial dependency
of any column on the primary keys these columns are not even dependent on the
primary key which is the city id so for this table to satisfy the second normal form
we have to move these columns that do not depend on the primary key into separate tables so for
instance we are going to have to create table for city name store and also for product just as this
and each table will have its own unique identifier or primary key so for instance in this city table
we have city name as the column and it depends on the primary key which is the city id we also
have a store table which is uniquely identified by this store id and also we have product sales table
and the primary key is the product id product name fully depends on the product id these two
columns quantity sold and amount are depending on the product name and we are going to resolve
this particular issue under the third normal form so let's move on to the third normal form
for the third normal form the table has to satisfy the second normal form
and also every non-prime attribute of the table must be dependent on the primary key
to explain this i'm going to use the product table that we created under our second normal form
this is a product table that we generated under the second normal form
in this table product name quantity sold and amount are the non-prime
attributes and the rule states that all non-prime attributes should depend on the primary key
it is true that product name which is a non-prime attribute depends on the
product id the primary key however we cannot say the same for quantity sold
and amount as they are rather depending on the product name which is a non-prime attribute
and this violates the third normal form so for this table to satisfy the third normal form again
we have to break the table down into new tables and provide primary keys for each of the tables
as you can see here so now we have the product table and it is uniquely identified by the primary
key which is the product id we also have sales table which is being uniquely identified by sales
id and the quantity sold an amount now fully depends on the sales id primary key
so at this point we can say that our product sales table has been normalized to the third normal form
so far we have been able to normalize our big chunk of table into four tables but remember
this is a relational database so we have to show relationships between these tables and to do
that that's when my storytelling skills comes in think of it this way that you have a city
and in each city you have store so i'm going to link the city table to the store table as this
also within each store sales are being made so i will link the store table to the source table
we also have product table and you know that products are the items being sold so it will
make sense to link the product table to the source table so this is the relationship that you can
generate for your normalized tables the way to indicate that a table in a relational database
is linked to another table is if you link them together using their unique columns oftentimes the
primary key in table A will have to be listed in table B as a foreign key so for instance in this
city table i have city id and city name city id is the primary key so in order to link this city
table to the start table i have to include the unique identifier of the city table which is the
city id in the store table as a foreign key so city id which is a primary key in the city table
will be included in the store table as a foreign key so this establishes the relationship between
the two tables foreign key also ensures referential integrity between two tables
such that if data does not exist in the city table the database management system will not
permit you to enter that data in the store table for instance it is impossible to have this role
in our database management system why because we do not have any city id eight in the city table so
this is going to throw an error if you try to enter such data in the store table eight as
the city id has to be listed first in the city table before it can be added to the store table
the only time you can be able to do that is if there is no relationship between these two tables
but to ensure that accurate data is being stored in our database it is always advisable to have
a relationship between these two tables so this should be our correct relational data
model for the case study if you have been following our introduction to data modeling
videos you would know that up to this point we have covered the conceptual and logical models
at this point i have a question for you
if the manager now wants us to keep records of all categories of products sold by each source person
how will our design look like remember he now wants us to keep record of all product category
so that can be one table and also sales person so sales person is also going to be another table
okay i guess you are done now if you answered it correctly then you should get a model that
looks like this okay unlike the sales pricing table which links directly to the sales table
the product category links to the product table and then the product table to the sales table
you cannot link the product category table directly to the sales table because you already
have a product table which is a subdivision of product category so it's better to link the
product contemporary to the product table and then the product table goes to the sales table more
like a hierarchical form this is the highest level comes the lowest level and then it's being sold
since we have covered the conceptual and logical models the third and final stage of
our relational data model would be to create these tables in a database management system
such as circle server management studio and this stage is called the physical model in designing a
physical model in a database management system you have to specify all data types as well as
primary and following key relationships and other constraints that you want to give to your tables
so this brings us to the end of our introduction to relational data models feel free to watch my
videos on how to create tables or physical model in super server management studio in fact try this
out and let me know in the comment section how it goes and if you need further assistance from
me i would gladly do that thank you for watching this video and have a great day bye bye