Data Modeling Basics (Part 3)
Related Blogs:
This is the third part in a 3-part series on the basics of data modeling.
In the first part of this blog series, we talked about data modeling and we tried to define what is a data model.
In part 2, we discussed the three types of data models i.e. Conceptual Data Model (CDM), Logical Data Model (LDM) and Physical Data Model including the different notations used in data modeling. We then jumped into creating a Conceptual Data Model (CDM).
In part 3, we complete our journey in Data Modeling Basics by continuing on creating the Logical Data Model and the Physical Data Model.
Logical Data Model (LDM)
In part 2 of this blog series, we created a conceptual data model and we will jump right off of it.
After we have identified the entity types and defined the relationships between these entity types, in logical data modeling we add the attributes of each entity type.
Attributes are various properties of an entity type that characterize the entity. It is something that describes the entity.
For example (refer to the illustration below), [StudentNumber], [FirstName], [MiddleNm], [LastNm] and [BirthDate] are all attributes of the [Student] entity type.
We then choose a candidate key to determine unique records. This will become our primary key when we go to Physical Data Model.
A candidate key is an attribute or combination of attributes that can determine uniqueness of a certain record. There can be multiple candidate keys in an entity type.
Next step is to normalize our logical data model.
Database Normalization, or simply normalization, is the process of organizing the attributes (columns) and entities (tables) to avoid data redundancy and improve data integrity by removing insertion anomaly, update anomaly and deletion anomaly.
Here is a link to site explaining each type of anomaly mentioned above. It also provides a good explanation of the first normal form, the second normal form and the third normal and how to achieve each normal form.
Physical Data Model (PDM)
From a logical data model, we now go to the last data model type i.e. Physical Data Model. This is where the data model transforms to something that can actually store data like in a database management system (DBMS).
As you can recall, while a Conceptual Data Model is a representation of the concepts of business entities and relationships, the Physical Data Model is the representation of the actual tables, columns, data types and foreign key relationships.
In physical data modeling, we define the data types for each column.
A data type is a particular kind of data item, as defined by the values it can take, the programming language used, or the operations that can be performed on it.
Summary of Data Modeling Steps
To summarize, here are the steps in data modeling from Conceptual Data Model to Logical Data Model and finally to Physical Data Model.
1. Identify the entity types.
2. Define the relationships between the entity types.
Output: Conceptual Data Model
3. Add the attributes of each entity type.
4. Choose a candidate key for each entity type.
5. Normalize the logical data model.
Output: Logical Data Model
6. Define the data types for each column.
Output: Physical Data Model
This is depicted in the table below.
Source: https://www.lucidchart.com/pages/ER-diagram-symbols-and-meaning
Disclaimer
Any external linked site(s) referenced in this blog are owned by their respective owners and are not part of this blog. In addition, the author may at times will not be able to verify fully the information in these external sites.