Hi Guys, this is Mina and I'm excited to introduce you to data models. At the end of this video,
you would know what a data model is and the importance of having one. I will introduce
you to the stages involved in designing a data model and the different types of data
models that exist. We will base our explanations on a case study, so you can get a clearer
understanding of the whole data modeling concept. So, what is a data model? It is a visual representation
of database objects such as tables and it helps us to organize data to be logically
stored within a database. with data models, we can easily establish relationships between
our tables, and also to store accurate and quality data in a database. Lastly, it helps
us to enforce business rules, security, and regulatory compliances on our data. So what
are the stages of data modeling? The 1st stage is the conceptual level where we identify
our database objects and the highest level of relationships between the different entities.
We do not assign attributes or keys at this stage. Here is a case study to help us understand
the conceptual model and I've provided a copy of this case study below this video in the
description section so feel free to refer to it. In this case study, a company has several
stores in multiple cities in a province and the manager wants us to design a data model
to help him track product sales in all the stores. I have 2 solutions for this case study.
In the 1st solution, one would say that oh there is a company, I'd have company as an
entity and also there is the manager so I'd list manager as an entity and then go on to
select city, product store and sales transaction. However, in your conceptual level, you have
to understand that you will be moving on to another stage where you would assign attributes.
In this question, we only have a company and one manager so if we move on to the next stage
with this particular, first model, we will not be able to get enough attributes for the
company or manager as there are only one company and manager. The 2nd model would be the right
one because, in the question, it says multiple cities so under the city we can have several
cities and those are going to be the attributes for the city table as well as product and
store and sales transaction. This makes the 2nd model the correct solution for this case
study under the conceptual level. So this is how our conceptual model should look like.
As you can see, we have properly identified all our entities and the highest level of
relationship between them which is this arrow, and also we have not specified any attributes
yet under this level. This conceptual level diagram is also called the entity-relationship
model. The second stage of data modeling is the logical level, where we take our conceptual
model and specify attributes for each entity. So the city entity has attributes city name
and CityID also we specify primary and foreign keys. So CityID is a primary key for the city
table and that cityID is listed under the store table as a foreign key so that there
would be a relationship between the city table or city entity and the store entity. The store
table also has attributes of its own, which are the store ID and that's a primary key
and also store name is an attribute for the store table. The same applies to the product
entity and sales transactions entity. we have attributes for product entity and also sales
transaction entity. The third and final stage of data modeling is the physical level where
we represent all our entities in a database management system and here its a build-up
from the logical model and within the database management system, entities will become tables
so city entity will be created as a table within the database management system and
then every attribute for that particular entity becomes column names. So city ID that was
an attribute under our logical level, will now become a column name. We also specify
datatypes. So cityID will have a data type of an integer and primary and foreign keys
will also be indicated for the appropriate columns. Also in the store table, we have
store ID as a column name integer is the data type and PK as a primary key city table is
being linked to store table so we have city ID integer and its a foreign key here. We
did that for the rest of the tables. Moving on to the types of data models, we have 2
types of data models. The first one I'm going to talk about is the relational data model
that was propagated by Bill Ilmon. This uses the Normalization Technique. So at normalization
is basically organizing data into multiple related tables and the purpose of that is
to reduce data redundancy. So this is the model we generated from the beginning of this
video, and it's actually a relational data model. We can achieve a relational data model
by applying the normal forms. We have the 1st Normal form, 2nd Normal form, 3rd Normal
form so on and so forth. Think of normal forms as rules that you need to follow in order
to obtain a relational data model and I intend to teach more about the normal forms in a
future video. Once normalization has been applied to our model, we tend to have separate
and independent entities that are being linked to each other. For instance, city entity,
or city table is an independent entity and it's being linked to the store entity using
the primary and foreign key concept. What I mean by separate and independent entities
is that each entity or table should contain attributes that pertain to that particular
entity. So for instance, in this city entity, it has only attributes that pertain to the
city entity such as city ID and City Name. We do not have any other attributes that do
not belong to the city entity. If we do that, that's going to create data redundancy in
our relational data model. Now foreign keys also ensure that there is referential integrity
between tables. What I mean by that is, for instance, if data does not exist in the city
table, it cannot be entered into the store table. The database management system will
show an error if we try to do that. If our scenario is changed, and the manager now wants
us to include a product category in our model. For us to obtain a normalized relational data
model, we need to create a separate table for product category and link it to the product
table, remember in the relational data model, we are breaking entities down into multiple
related tables and the purpose of that, is to minimize data redundancy. Also if the company
now has stores in other provinces, then we need to create a province table and link it
to its corresponding related table, which is the city table. In this case, we have achieved
normalized model by instinctively applying the normal forms, however, I intend to show
you in a future video, what these normal forms are. The 2nd type of data model is the dimensional
data model which was invented by Ralph Kimball. In this model, we denormalize the data by
grouping related data together in order to obtain a small number of tables and by so
doing, we increase performance. Under the dimensional data model, we have to identify
what we call dimensions, are descriptive entities. So for instance store is a descriptive entity,
and also product table. All the attributes for the product are descriptive and city so
these are called dimensions. And then we have to identify what we call Fact. Facts are the
metrics or numeric measurements such as sales amount and quantity sold. Once we identify
the dimensions and facts, we have to create the fact table. So the fact table will contain
the facts, which are the metrics and also foreign keys of all the associated dimensions.
This diagram is often referred to as a star schema. Because we have a fact table in the
center surrounded by multiple dimensions.If the manager now wants us to include product
category in our modeling, unlike the relational data model where, where we will have to create
a separate table for product category, we can merge product category name into the product
table and have the same table for both product and product category. So as you can see here,
under the product table I have category name and product name. Also, if the company extends
to other provinces, instead of creating a separate table for the province, we can easily
change our city table to a location table and include our province name under the same
table. So under the dimensional model, we are grouping like data into the same table
in order to obtain minimal tables and we are making sure that we are separating our dimensions
from our facts and linking them together to obtain a star schema. So this brings us to
the end of our introduction to data models. Thank you so much for watching this video
and feel free to refer to my other YouTube videos on dimensional modeling. Have a great
day.