An Overview of Data Modeling in Business


By:

CM Maran
Lecturer - Information Systems
TKM Institute of Management
Musaliar Hills, Karuvelil (PO), Kollam-691505 (Kerala State)
E-mail:
cmaran78@yahoo.com
 


INTRODUCTION

A Data model is a conceptual representation of data structures (tables) required for a database and is very powerful in expressing and communicating the business requirements.

A data model visually represents the nature of data, business rules governing the data, and how it will be organized in the database. A data model is comprised of two parts logical design and physical design.

Data model helps functional and technical team in designing the database. Functional team normally refers to one or more Business Analysts, Business Managers, Smart Management Experts, End Users etc., and Technical teams refers to one or more programmers, DBAs etc. Data modelers are responsible for designing the data model and they communicate with functional team to get the business requirements and technical teams to implement the database.

    Importance of Data Model

  • The goal of a data model is to make sure that all data objects provided by the functional team are completely and accurately represented.
  • Data model is detailed enough to be used by the technical team for building the physical database.
  • The information contained in the data model will be used to define the significance of business, relational tables, primary and foreign keys, stored procedures, and triggers.
  • Data Model can be used to communicate the business within and across businesses.
  • Need for developing a Data Model:

  • A new application for OLTP(Online Transaction Processing), ODS(Operational Data Store), data warehouse and data marts.
  • Rewriting data models from existing systems that may need to change reports.
  • Incorrect data modeling in the existing systems.

Data Modeling Tools
There are a number of data modeling tools to transform business requirements into logical data model, and logical data model to physical data model. From physical data model, these tools can be instructed to generate sql code for creating database.

Popular Data Modeling Tools

Tool Name

Company Name

Erwin

Computer Associates

Embarcadero

Embarcadero Technologies

Rational Rose

IBM Corporation

Power Designer

Sybase Corporation


Data Modeling Tools: What to Learn?
Data modeling tools are the only way through which we can create powerful data models. Following are the various options that we have to know and learn in data modeling tools before start building data models.

Software:
How to install the data modeling tool on server/client?

Logical Data Model:
How to create entity and add definition, business rule?
How to create domains?
How to create an attribute and add definition, business rule, validation rules like default    values and check constraint?
How to create supertypes, subtypes?
How to create primary keys, unique constraint, foreign key relationships, and recursive    relationships?
How to create identifying and non-identifying relationship?
How to assign relationship cardinality?
How to phrase relationship connecting two tables?
How to assign role names?
How to create key groups?
How to create sequence no's?

Physical Data Model:
How to rename a table?
How to rename a column,validation rules like default and check constraints?
How to assign NULL and NOT NULL to columns?
How to name foreign key constraints?
How to connect to databases like MS Access, Oracle, Sybase
How to generate sql code from data model to run against databases like MS Access, Oracle, Sybase, and Terradata etc.?

Data Modeling Development Cycle

Gathering Business Requirements - First Phase: Data Modelers have to interact with business analysts to get the functional requirements and with end users to find out the reporting needs.

Conceptual Data Modeling Second phase

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.


Logical Data Modeling Third Phase

This is the actual implementation and extension of a conceptual data model. A Logical data model is the version of a data model that represents the business requirements (entire or part) of an organization and is developed before the physical data model.


Physical Data Modeling Fourth phase

Physical data model includes all required tables, columns, relationships, and database properties for the physical implementation of databases. Database performance, indexing strategy, physical storage and denormalization are important parameters of a physical model.


Database - Fifth Phase:
DBAs instruct the data-modeling tool to create SQL code from physical data model. Then the SQL code is executed in server to create databases.

Data Modeling Standardization:

Data modeling standardization has been in practice for many years and the following section highlight the needs and implementation of the data modeling standards.

Several data modelers may work on the different subject areas of a data model and all data modelers should use the same naming convention, writing definitions and business rules.

Table Names Standardization:
Giving a full name to the tables, will give an idea about data what it is about. Generally, do not abbreviate the table names; however this may differ according to organization's standards. If the table name's length exceeds the database standards, then try to abbreviate the table names. Some general guidelines are listed below that may be used as a prefix or suffix for the table.

    Examples:

    Lookup LKP - Used for Code, Type tables by which a fact table can be directly accessed.
    e.g. Credit Card Type Lookup CREDIT_CARD_TYPE_LKP

Column Names Standardization:
Some general guidelines are listed below that may be used as a prefix or suffix for the column.

    Examples:

  • Key Key System generated surrogate key.
    e.g. Credit Card Key CRDT_CARD_KEY

Database Parameters Standardization:
Some general guidelines are listed below that may be used for other physical parameters.

    Examples:

  • Index Index IDX for index names.
    e.g. Credit Card Fact IDX01 CRDT_CARD_FCT_IDX01

Steps to create a Data Model

These are the general guidelines to create a standard data model and in real time, a data model may not be created in the same sequential manner as shown below. Based on the enterprise's requirements, some of the steps may be excluded or included in addition to these.

1 Get Business requirements.
2 Create High Level Conceptual Data Model.
3 Create Logical Data Model.
4 Select target DBMS where data-modeling tool creates the physical schema.
5 Create standard abbreviation document according to business standard.
6 Create domain.
7 Create Entity and add definitions.
8 Create attribute and add definitions.
9 Based on the analysis, try to create surrogate keys, super types and sub types.
10 Assign data type to attribute. If a domain is already present then the attribute should be attached to the domain.
11 Create primary or unique keys to attribute.
12 Create check constraint or default to attribute.
13 Create unique index or bitmap index to attribute.
14 Create foreign key relationship between entities.
15 Create Physical Data Model.
16 Create SQL Scripts from Physical Data Model and forward that to DBA.
17 Maintain Logical & Physical Data Model.
18 For each release (version of the data model), try to compare the present version with the previous version of the data model. Similarly, try to compare the data model with the database to find out the differences.
19 Create a change log document for differences between the current version and previous version of the data model.

Data Modeler Role

Business Requirement Analysis:

Interact with Business Analysts to get the functional requirements.
Interact with end users and find out the reporting needs.
Conduct interviews, brain storming discussions with project team to get additional    requirements.
Gather accurate data by data analysis and functional analysis.

Development of data model:
Create standard abbreviation document for logical, physical and dimensional data models.
Create logical, physical and dimensional data models.
Document logical, physical and dimensional data models.

Reports:
Generate reports from data model.

Review:
Review the data model with functional and technical team.

Creation of database:
Create sql code from data model and co-ordinate with DBAs to create database.
Check to see data models and databases are in synch.

Support & Maintenance:
Assist developers, ETL, BI team and end users to understand the data model.
Maintain change log for each data model.

Data Modeling Report

From Data Modeling tools, reports can be easily generated for technical and business needs. The reports that have been generated from logical data model and physical data model are called as business reports and technical reports respectively. Most of the data modeling tools provide default reports like subject area reports, entity reports, attribute reports, table reports, column reports, indexing reports, relationship reports etc. The advantage of these reports is, whether they are technical or non-technical, everybody would understand what is going on within the organization.

Logical Data Model Report:
Logical Data Model Report describes information about business such as the entity names, attribute names, definitions, business rules, mapping information etc.

Logical Data Model Report Example:


Physical Data Model Report:
Physical Data Model Report describes information such as the ownership of the database, physical characteristics of a database (in oracle, table space, extents, segments, blocks, partitions etc), performance tuning (processors, indexing), table name, column name, data type, relationship between the tables, constraints, abbreviations, derivation rules, glossary, data dictionary, etc., and is used by the technical team.

Physical Data Model Report Example:


Conclusion:

Other than default reports provided by data modeling tools, a data modeler can also create customized reports as per the needs of an organization. For example, if an expert asks of both logical and physical reports of a particular subject area in one file (e.g in .xls), logical and physical reports can be easily merged and reports can be easily generated accordingly. Data modeling tools provide the facility of sorting, filtering options and the reports can be exported into file formats like .xls, .doc, .xml etc.

Bibliography:

1. The Data Models Resource Book - Len Silverston.
2. Data Modeling Essentials - Graeme Simsion                                                                                                                                                                                                                                                                                                                                    
3. Data Modeling - G Lawrence sander
4. Data Modelers workbench Steve Hoberman
5. www.Learndatamodelling.com
 


CM Maran
Lecturer - Information Systems
TKM Institute of Management
Musaliar Hills, Karuvelil (PO), Kollam-691505 (Kerala State)
E-mail:
cmaran78@yahoo.com
 

Source: E-mail July 22, 2005

  

Back to Articles 1-99 / 100 onwards / Faculty Column Main Page

 

Important Note :
Site Best Viewed in Internet
Explorer in 1024x768 pixels
Browser text size: Medium