Data
Modeling
The Foundation of Data
02
Me analyzing data without doing data modeling
Data
Data
Analysis Modeling
*Me
Looking at the meme above, you might understand the importance of data
modeling. Let's dive into the topic
03
DATA MODELING:
Data
Sales Product
Customer
Data modeling is an important step in designing and building a database.
Just like a blueprint visualizes the plan and details for a house, data modeling
creates
a visual representation of data entities and the relationships between data
elements.
04
COMPONENTS OF DATA MODELING:
Name
1
Address
Phone Number
Entities Attributes Relationships
A data model consists of 3 components
Entities:
These are the main things we want to store information about. For instance, in
business, an entity could be "Customer" or "Product."
Attributes:
These are the specific pieces of information about an entity. For a "Customer,"
attributes could include name, address, and phone number.
Relationships:
These define how entities are connected or related to each other. For example, a
"Customer" can have a relationship with an "Order."
05
TYPES OF DATA MODELS:
There are 3 types of data models:
Conceptual Data Model, Logical Data Model, Physical Data Model
TYPES OF DATA MODELS:
1 2 3
Conceptual Logical Physical
Data Model Data Model Data Model
1 CONCEPTUAL DATA MODEL
A high-level view of what needs to be stored and how different entities
relate to each other. It's like a bird's eye view.
Product
Sales Date
Customer
06
2 LOGICAL DATA MODEL
More detailed than the conceptual model, specifying attributes and
relationships. It's like a floor plan of a house.
Product
Product_id
Name
Category
Unit Price
Color
Sales Date
Rating
Description
Customer_id (FK) Date
Date (FK) Year
Product_id (FK) Fiscal_Year
Customer Sold_QTY Month
Customer_id Sales_Amount Quarter
Name
Region
Address
Phone_NO
Email_id
3 PHYSICAL DATA MODEL
It specifies how the data will be stored, considering database
technologies and constraints. It's like the actual construction of the
house.
Product
Product_id Integer
Name Varchar(30
Category )
Unit Price Varchar(30
Color ) Integer
Sales Date
Rating Varchar(30
Description ) Integer
Varchar(30 Customer_id (FK) Intege Date Date
Date (FK) r Date Year Integer
)
Fiscal_Year
Customer
Product_id (FK) Intege Integer
Sold_QTY r Month Varchar(30
Customer_id Integer Sales_Amount Intege Quarter ) Integer
Name Varchar(30
r Float
Region )
Address Varchar(30
Phone_NO )
Email_id Varchar(30
)
Varchar(10)
Varchar(30
)
07
TYPES OF TABLES:
Data modeling establishes a connection and flow of data between tables, typically
consisting of fact tables surrounded by dimension tables, along with the
relationships between these tables.
1 FACT TABLE:
A fact table contains measurements, metrics, or facts about a business process.
It generally compresses transactional data. It has two types of columns:
one representing facts of the business and another containing foreign keys
to dimension tables.
Example: A Sales fact table contains data on store sales, detailing the
quantity of each product sold and the revenue generated from each sale.
Foreign
Facts Key
Fact Table
Facts: amount, date, quantity. These columns represent the business facts.
Foreign keys: customer_id, product_id. These columns contain foreign
keysthat link to dimension tables.
08
2 DIMENSION TABLE:
Fact tables are connected to dimension tables using foreign keys.
Dimension tables consist of attributes that describe the objects of a fact
table. Each dimension table includes a primary key that uniquely identifies
each record
and using this key dimension table associates with fact tables.
Example: A dim_Customer table stores information about the customers
who made purchases.
Primary Key
Dimension Table
Primary Key: Customer_code.
This column contains unique, non-null values associated with records in fact
tables.
09
TYPES OF RELATIONSHIPS:
Only Data Analysts Can Relate
The Relationship The Relationship
I Want I Get
1 *
Dimension Table Fact Table
Here dim_customers is a dimension table, and Sales is a fact table The tables
should consist of a common column attribute to make a relationship
between tables.
There are 4 types of relationships:
[Link]-to-One relationship
[Link]-to-Many relationship
[Link]-to-One relationship
[Link]-to-Many relationship
10
1 One-to-One Relationship:
Telangana
Hyderabad
Each row in the first table is mapped to only one row in the second
table. For example, each state has only one capital.
2 One-to-Many Relationship:
In a one-to-many relationship, each row in the first table can be
associated with multiple rows in the second table.
For example, a customer can place several orders over time, but each order
is tied
to a specific customer.
11
3 Many-to-One Relationship:
Multiple rows in the first table are mapped to a single related row in the
second table.
For instance, different students can receive the same grade.
4 Many-to-Many Relationship:
Multiple records from one table relate to multiple records from another
table.
12
Many-to-Many Relationship:
For example, more than one teacher can teach multiple courses. Dhaval
teaches ML and Power BI; Power BI is taught by both Dhaval and Hem.
Follow for more content
like this
Ganesh R
Senior Azure Data Engineer
