Basics of Data Modeling in Relational Data Base Management System
A process of creating a simplified diagram of a software system and data elements it contains, using text and symbols to represent the data flows is data modeling.
What is Data Modelling: A process is used to define and to analyze the data requirement that is needed to support the business process. Helps the visual representation of data and enforces business rules. Describes the conceptual representation of the business and the key association among various objects which is otherwise known as entity relationship. It is necessary foundational work that not only allows data to more easily be stored in a database but also positively impacts data analytics to drive the business as well.
What is a Data Model: An abstract layer that organizes the data descriptions. Emphasizes what is needed and how it should be organized to run a business. Provides a meaningful insights of a business operation. There are two types of modeling techniques Entity relationship & Unified modelling language.
Let’s discuss entity-relationship known as er model in details, the symbols are being used to model the elements and it’s importance in industrial use case. There are several ways to model entity-relationship diagrams. The most high-level type is Conceptual Data Model. next level is Logical Data Model. And the lowest level therefore the most detailed type is Physical Data Model.
Conceptual Data Model: Defines What the system contains. Establishes a broad or a high level view of what processes or the activities to be included. This model helps to organize, the scope and to define business concepts.
— Include important entities and the relationship between them.
— Do not specify any attributes and any keys such as primary keys.
1. Customer and address are two entities. Such as customer name, date-of- birth, gender etc. are the attributes of the customer entity.
2. Similarly street, city, state, country etc. are the attributes of Address entity.
3. “Lives at” is the relationship between the customer and the address.
Logical Data Model: Defines How the system should be implemented which includes technical maps and business data structures. Contains more detailed compared to the conceptual model.
— Include all the entities and relationships between them.
— Specify the attributes for each entity.
— Specify the primary keys, foreign keys for each entity.
— Involve normalization, process of removing of redundancy.
1. Listing all the attributes those are specific to entities we defined and those are required for Business.
2. Segregate the entities subject area wise.
3. Constructs the relationships which describes the cardinality.
Physical Data Model: Describes How the system has been implemented in a technical fashion such as enforcing data quality, cardinality, and relationships among business data structures in a specific RDBMS physically.
— Specify actual table name and the data elements such as columns.
— Includes the foreign keys to identify the relationships.
1. Actual relationship has been established between entities. Data base objects has been created along with other objects such as indexes, constraints etc.
2. Columns should have exact datatypes, lengths and default values.
3. Joins can be applied to fetch combined data for reporting.
There are few other aspects have to be considered while designing the model.
Types of Entities — Entities are objects or concepts that represent important data. They are typically nouns, such as Customer, Supervisor, Location etc.
— Strong entities exist independently from other entity types. They always possess one or more attributes that uniquely distinguish each entities.
— Weak entities depend on other entity and don’t possess unique attributes and have no meaning in the diagram without depending on another entity.
— Associative entities those associate the instances of one or more entities. Contain the attributes that are unique to the relationships among the entities.
Type of Relationships: Relationships are the meaningful associations among or between the entities. They are usually verbs, e.g. assign, associate, or track. Weak relationships or identifying relationships are the connections that exist between a weak entity type.
Types of Attributes: Attributes are the characteristics of an entity, a many to many relationship or one to one relationship.
— Multivalued attributes are capable of taking on more than one value.
— Derived attributes are calculated from related attribute values.
Below symbols are used at the most granular level of entity relation diagram. Relationships are illustrated an association between two tables.
Presenting below an entity relationship model of a customer address entity.
Conclusion: Thus data modeling makes easier for top level management and other stakeholders to view and analyze the business flow in a sequential way. In addition to data modelling helps:
- Increases the consistency in system design across the enterprise level.
- Improves the application and the database operation wise.
- To communication between business and analytics teams.
- Faster way of data retrieval's and data manipulations
Thank you for reading and being with me, I hope this blog will help you to get some idea how to model raw data in to business format per operational usage. Chat with me in case of more questions you have, on my Twitter handle or my LinkedIn or leave a comment below. Good luck!
Few other links for your reference to go through in details. Link 1, 2
