If you look at any software application in the world, you will see that at the very fundamental level, it will deal with organization, manipulation and presentation of data to fulfill the business requirements.
A** data model** is a conceptual representation to express and communicate business requirements. It visually represents the nature of data, business rules governing the data, and how the data will be organized in the database.
The process of data modeling can be compared to the process of construction of a house. Assume that a company ABC needs to build a guest house (data base). It calls a building architect (data modeler) and explains its building requirements (business requirements). Building Architect (data modeler) develops the plan (data model) and gives it to company ABC. Finally company ABC calls civil engineers (DBAs and database developers) to construct the guest house (database)
Key Terms in Data Modeling:
**_Entity and Attributes: _**The entities are the “things” in the business environment about which we want to store data e.g Products, Customers, Orders etc. Attributes provide a means of organizing and structuring data. E.g we need to store certain information about the products we sell, such as selling price or quantity available. These pieces of data are the attributes of the Product entity. Entities generally represent tables of database while attributes are columns of those tables.
**_Relationship: _**The relationship between entities describes how one entity is linked to another. In a data model, entities can be related as any of: one-to-one, many-to-one or many-to-many. This is said to be the cardinality of a given entity in relation to another.
Intersection Entity(Reference Table): In case of many-to-many relationship among entities, an intersection entity can be used to resolve it to many to one and one to many relationships. A simple example is: There are 2 entities, TV Show and Person. Each TV show may be watched by one or more persons while a person can watch one or more TV shows:
This can be resolved by introducing a new intersecting entity ‘Viewing record” as follows:
ER Diagram: A diagram that shows the entities and the relationships between them is called ER diagram. An ER diagram can take the form of Conceptual Data Model, Logical Data Model or Physical Data Model.
Conceptual Data Model: Conceptual data model includes all major entities and relationships and does not contain much detailed level of information about attributes and is often used in the initial planning phase. An example:
Logical Data Model: It is an extension of the conceptual data model. It includes all entities, attributes, key groups and relationships that represent business information and define business rules. An example:
Physical Data Model: It includes all required tables, columns, relationships, database properties, for the physical implementation of databases. Database performance, indexing strategy, physical storage and denormalization are important parameters of a physical model. An example:
Data Modeling Development Cycle:
Relational vs Dimensional Modeling:
#data-modeling #star-schema #database #dimensional-model #data-warehousing #data analysis
