Data Model Principle Architect or Solution Designer or Database designer
Data Model tells how the logical structure is modeled.
Data Models are fundamental entities in DBMS
Data Models define how data is connected to each other and how it will be processed and stored inside the system.
1.Conceptual Data Model
This model defines the high level relationsh
it will display important entities and the re
No Attributes defined or specified
ored inside the system.
el defines the high level relationship between the entities
play important entities and the relationship among them
utes defined or specified
2.Logical Model
Logical Model defines the data as much as possible.
This model physically implemented in the Database.
Display all the entities and the attribute
PK for Each entity is specified
FK for each entity is defined if present.
l the entities and the attributes and its relationship
h entity is specified
h entity is defined if present.
3.Physical Model
Physical model defines how the physical model exist into the system
Display all the table columns
Display FK
change the relationship into
Entity becomes table
Attribute becomes column da
fact_
DIM_
product sales
Display all the table columns pid 1 1
type 546 3456
change the relationship into FK des bricks 444
Entity becomes table 21/10/21
Attribute becomes column data types. price 970
quantity 230
Always in DWH the data in Data models will be stored in two format
1.Dimension 2.Facts
1.SCD(Slowly Changing Dimension) 1.Additive
2.Confirmed Dimension 2.Semi-Additive
3.Degenerated 3.Non-Additive
4.Junk
5.Roll Playing
6.Static
7.Shrunken
1.SCD(Slowly Changing Dimension)
The Dimensions which are changing slowly over a peroid of time .
Example SCD SCD
EMP_ID NAME Year Loc
1 Praveen 2015 HYD
SCD1 Current records history will be lost
It will not maintain the history SCD1 SCD1
EMP_ID NAME Year Loc
1 Praveen 2019 Mumbai
SCD2
It will store the current record as well history SCD2 SCD2
EMP_ID NAME Year Loc
1 Praveen 2015 HYD
By using two methods
1.Start date and End date
2.ETL record Flag
1.Start date and End date
EMP_ID NAME Year Loc
1 Praveen 2015 HYD
1 Praveen 2017 PUNE
1 Praveen 2019 Mumbai
2.ETL record Flag
EMP_ID NAME Year Loc
1 Praveen 2015 HYD
1 Praveen 2017 PUNE
1 Praveen 2019 Mumbai
SCD 3
New Column will be added to maintainhistory
It will maintain only one (previous one) history
It will not add new records or rows
EMP_ID NAME Current_Year
1 Praveen 2021
SCD 4
EMP_ID NAME Current_Year
1 Praveen 2021
Current Table
EMP_ID NAME Current_Year
1 Praveen 2021
History Table
EMP_ID NAME Year
0 Praveen 2021
Praveen 2020
Praveen 2019
Praveen 2018
Praveen 2017
no scd 5
SCD 6
scd1 +scd 2+scd 3
EMP_id Name Current_designation Current year
101 Vithal Test Engineer 2021
EMP_id Name Current_designation Current year
101 Vithal Sr Test engineer 2022
101 Vithal Sr Test engineer 2022
EMP_id Name Current_designation Current year
101 Vithal BA 2022
101 Vithal BA 2022
101 Vithal BA 2023
SCD 0
IT Will not get updated
It stures first table only
START_DATE END_DATE
12/2/2015 20/05/2017
20/05/2017 28/09/2019
28/09/2019 NULL select * from emp where end_date is null Current records
ETL_RECORDFLAG
0N History
0N
1Y Current
Current_Loc OLd_Year Current_Loc
Pune 2019 HYD
Transational Syatem
Current_Loc
Pune
Current_Loc
Pune
Loc
Pune
HYD
BLG
DELHI
GOA
Old_designation OLd year ETL_Flag
Null null Y
Old_designation old year ETL_Flag
Null null N
Test Engineer 2021 Y
Old_designation old year ETL_Flag
Null null N
Test Engineer 2021 N
Sr Test engineer 2022 Y
Current records
