Data Modeling

What is Data Modeling??

Data modeling is a process to define and analyze data requirements needed to support the business processes within the scope of corresponding information systems in organizations.

Data models provide a structure for data used within information systems by providing specific definition and format. If a data model is used consistently across systems then compatibility of data can be achieved. If the same data structures are used to store and access data then different applications can share data seamlessly.

• Visual representation of objects and their relationships
• Nature of Data, Business rules and organization in database.

What to and who will visualize - 

• House – Database
• Building Architect – Data Modeler
• Building Requirement – Business Requirement
• Building Plan – Data model
• Civil Engineers - DBA

Advantages of Data Modeling -

• Define the significance of business on various objects. 
• Business rules can be easily represented. 
• Easy communication with Line of business.
• To assist business analysts, programmers, testers, manual writers, IT package selectors, engineers, managers, related organizations and clients to understand and use an agreed semi-formal model the concepts of the organization and how they relate to one another. 
• To manage data as a resource 
• For the integration of information systems 
• For designing databases/data warehouses (aka data repositories)
• All data objects are covered from requirement and represented and connected pictorially

Life without Data Modeling - 

• Write everything in a piece of paper or notepad 
• Write all rules in text how various objects are related 
• Miss out pieces while writing 
• Others won’t understand the complete picture of requirement 
• Inefficiency in the starting point of software cycle 
• Navigates down the line and the outcome is a bad product Maintenance or enhancement?

Data Modeling Tools in Market

• Erwin – Computer Associates 
• Embarcadero - Embarcadero Technologies 
• Rational Rose - IBM 
• Power Designer - Sybase Corporation 
• Oracle Designer - Oracle Corporation

Data Modeling Development Cycles

• I PHASE - Business requirement gathering 
• II PHASE - Conceptual Data Modeling 
• III PHASE - Logical Modeling 
• IV PHASE - Physical Modeling 
• V PHASE - Physical Creation of Database Objects

Business Requirement Gathering:

In this Phase we understand existing the Business process , procedures and Rules. We do data analysis of existing system to understand the data and do perform gap analysis by doing the meetings and knowledge sharing sessions by Business SME’s.

• Understanding the Business by meeting the Process/Business Owners.
• Understanding the Existing Business Processes, Procedures and Rules followed by organization.
• Understanding the goals and expectations of business users and there problem areas.
• Doing Data Analysis.

Conceptual Data Modeling:

In conceptual data model we identifies the highest-level relationships between the different entities.

• Outline of entities.
• Includes the important entities and the relationships among them.Drawing a picture - No detail information 
• Thin layer in Development cycle that we don’t realize
• No attribute is specified. 
• No primary key is specified.

Logical Data Modeling:

In Logical data Model, we describes the data in as much detail as possible, without regard to how they will be physical implemented in the database.

1. Includes all entities and relationships among them. 
2. All details are captured – Entities, attributes, relationships & key groups 
3. No info related to database.
4. All attributes for each entity are specified. 
• The primary key for each entity is specified.
• Foreign keys (keys identifying the relationship between different entities) are specified.
• Normalization occurs at this level. 
5. The steps for designing the logical data model are as follows:
• Specify primary keys for all entities.
• Find the relationships between different entities.
• Find all attributes for each entity.
• Resolve many-to-many relationships.

Physical Data Modeling:

In Physical data model we represent how the model will be built in the database.A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables.

1. All tables, columns, relationships and database properties are used here 
2. Abbreviated names for objects if needed 
3. NULL, NOT NULL, RI and relationships are ensured.
4. Features of a physical data model include:
• Specification all tables and columns.
• Foreign keys are used to identify relationships between tables.
• De-normalization may occur based on user requirements.
• Physical considerations may cause the physical data model to be quite different from the logical data model.
• Physical data model will be different for different RDBMS. For example, data type for a column may be different between MySQL and SQL Server.
5. The steps for physical data model design are as follows:
• Convert entities into tables.
• Convert relationships into foreign keys.
• Convert attributes into columns. Modify the physical data model based on physical constraints / requirements.

Physical Creation of Database Objects: 

In this phase we implements the Physical data model in real time Database to create different Database objects. This implementation happens environment wise, these environment are based on the project cycle like - Development, Testing, Production.