MODELS
1.What is Enterprise Data Modeling?
- The development of a common consistent view and understanding of data elements and their relationships across the enterprise is referred to as Enterprise Data Modeling.
- This type of data modeling provides access to information scattered throughout an enterprise under the control of different divisions or departments with different databases and data models.
- Enterprise Data Modeling is sometimes called as a global business model and the entire information about the enterprise would be captured in the form of entities.
- Enterprise data model comprises of all entities required by an enterprise.
- The development of a common consistent view and understanding of data elements and their relationships across the enterprise is referred to as Enterprise Data Modeling.
- For better understanding purpose, these data models are split up into subject areas.
- This type of data modeling provides access to information scattered throughout an enterprise under the control of different divisions or departments with different databases and data models.
- Enterprise Data Modeling is sometimes called as global business model and the entire information about the enterprise would be captured in the form of entities.
Data Model Highlights
- When a enterprise logical data model is transformed to a physical data model, supertypes and subtypes may not be as is. i.e. the logical and physical structure of supertypes and subtypes may be entirely different.
- A data modeler has to change that according to the physical and reporting requirement.
- When a enterprise logical data model is transformed to a physical data model, length of table names, column names etc may exceed the maximum number of the characters allowed by the database.
- So a data modeler has to manually edit that and change the physical names according to database or organization’s standards.
- One of the important things to note is the standardization of the data model.
- Since a same attribute may be present in several entities, the attribute names and data types should be standardized and a conformed dimension should be used to connect to the same attribute present in several tables.
- Standard Abbreviation document is a must so that all data structure names would be consistent across the data model.
2.Steps to create a Data Model?
- 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.
- Sometimes, data modeler may be asked to develop a data model based on the existing database.
- n that situation, the data modeler has to reverse engineer the database and create a data model.
- Get Business requirements.
- Create High Level Conceptual Data Model.
- Create Logical Data Model.
- Select target DBMS where data modeling tool creates the physical schema.
- Create standard abbreviation document according to business standard.
- Create domain.
- Create Entity and add definitions.
- Create attribute and add definitions.
- Based on the analysis, try to create surrogate keys, super types and sub types.
- Assign datatype to attribute. If a domain is already present then the attribute should be attached to the domain.
- Create primary or unique keys to attribute.
- Create check constraint or default to attribute.
- Create unique index or bitmap index to attribute.
- Create foreign key relationship between entities.
- Create Physical Data Model.
- Add database properties to physical data model.
- Create SQL Scripts from Physical Data Model and forward that to DBA.
- Maintain Logical & Physical Data Model.
- 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.
- Create a change log document for differences between the current version and previous version of the data model
3.What is 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(CDM) – Second Phase:
- This data model includes all major entities, relationships and it will not contain much detail about attributes and is often used in the INITIAL PLANNING PHASE.
- Logical Data Modeling(LDM) – Third Phase:
- This is the actual implementation of a conceptual model in a logical data model. A logical data model is the version of the model that represents all of the business requirements of an organization.
- Physical Data Modeling(PDM) – Fourth Phase:
- This is a complete model that includes all required tables, columns, relationship, database properties for the physical implementation of the database.
- 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.
4.Standardization Needs | Modeling data:
- 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.
- Nowadays, business to business transactions(B2B) are quite common, and standardization helps in understanding the business in a better way.
- Inconsistency across column names and definition would create a chaos across the business.
- For example, when a data warehouse is designed, it may get data from several source systems and each source may have its own names, data types etc.
- These anomalies can be eliminated if a proper standardization is maintained across the organization.
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 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
Fact – FCT – Used for transaction tables:
e.g. Credit Card Fact – CREDIT_CARD_FCT
Cross Reference – XREF – Tables that resolves many to many relationships.
e.g. Credit Card Member XREF – CREDIT_CARD_MEMBER_XREF
History – HIST – Tables the stores history.
e.g. Credit Card Retired History – CREDIT_CARD_RETIRED_HIST
Statistics – STAT – Tables that store statistical information.
e.g. Credit Card Web Statistics – CREDIT_CARD_WEB_STAT
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
Identifier – ID – Character column that is used as an identifier.
e.g. Credit Card Identifier – CRDT_CARD_ID
Code – CD – Numeric or alphanumeric column that is used as an identifying attribute.
e.g. State Code – ST_CD
Description – DESC – Description for a code, identifier or a key.
e.g. State Description – ST_DESC
Indicator – IND – to denote indicator columns.
e.g. Gender Indicator – GNDR_IND
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 IDX 01 – CRDT_CARD_FCT_IDX01
Primary Key – PK – for Primary key constraint names.
e.g. CREDIT Card Fact PK01- CRDT-CARD_FCT_PK01
Alternate Keys – AK – for Alternate key names.
e.g. Credit Card Fact AK01 – CRDT_CARD_FCT_AK01
Foreign Keys – FK – for Foreign key constraint names.
e.g. Credit Card Fact FK01 – CRDT_CARD_FCT_FK01
5.What is 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(data warehouse data modelling).
- Document logical, physical and dimensional data models (data warehouse data modelling).
- 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
6.What is data model repository?
Data Model and its relevant data like entity definition, attribute definition, columns, data types etc. are stored in a repository, which can be accessed by data modelers and the entire team.
7.What particulars you would need to come up with a conceptual model in a health care domain project?
For a health care project, below details would suffice the requirement to design a basic conceptual model
- Different categories of health care plan and products.
- Type of subscription (group or individual).
- Set of health care providers.
- Claim and billing process overview.
8.Categories of data model:
- High level provides concepts that are close to the way many users perceive data.
- It uses concepts such as Entities, Attributes and Relationships.
- Physical data model describes how data is stored in the computer by representing information such as record formats, record orderings and access path.
- These data models are used most frequently. They include Hierarchical data model, Network data model and Relational data model.
The level of complexity and detail increases from conceptual to logical to a physical data model.
The conceptual model shows a very basic high level of design while the physical data model shows a very detailed view of design.
The conceptual model will be just portraying entity names and entity relationships.
Figure 1 shown in the later part of this article depicts a conceptual model.
The logical model will be showing up entity names, entity relationships, attributes, primary keys and foreign keys in each entity.
Figure 2 shown inside question#4 in this article depicts a logical model.
The physical data model will be showing primary keys, foreign keys, table names, column names and column data types. This view actually elaborates how the model will be actually implemented in the database.
Different entities that were linked together. These entities were subscriber, member, healthcare provider, claim, bill, enrollment, group, eligibility, plan/product, commission, capitation, etc.
Logical Data Model:
- The logical data model is nothing but the representation of your database in logical way.
- The logical data model is actually a representation of business requirements in logical way.
- The logical data model is the actual implementation and extension of a conceptual data model.
- There are following different things used for creating logical data model:
- Entities, Attributes, Super Types, Sub Types, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition, business rule, etc
Physical Data Model :
- The Physical data model is nothing but the representation of physical database.
- .Physical data model includes all required tables, columns, relationship, database properties for the physical implementation of databases.
- Database performance, indexing strategy, and physical storage are important parameters of a physical model.
- The important or main object in a database is a table which consists or rows and columns.
- The approach by which physical data models are created is called as physical data modeling.
9.What does data model contain?
Logical Data Model: Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition, business rule, etc
Physical Data Model: Table, Column, Primary key Constraint, Unique Constraint or Unique Index, Non Unique Index, Check Constraint, Default Value, Foreign Key, comment etc.
conceptual data model showing how the project looked like on a high-level
Each of the data entities has their own data attributes. For example, a data attribute of the provider will be provider identification number, few data attributes of the membership will be subscriber ID, member ID, one of the data attribute of claim will claim ID, each healthcare product or plan will be having a unique product ID and so on
9.What is Conceptual Data Modeling?
- 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.
- Conceptual data model is created by gathering business requirements from various sources like business documents, discussion with functional teams, business analysts, smart management experts and end users who do the reporting on the database.
- Data modelers create conceptual data model and forward that model to functional team for their review.
Conceptual Data Model – Highlights
- CDM is the first step in constructing a data model in top-down approach and is a clear and accurate visual representation of the business of an organization.
- CDM visualizes the overall structure of the database and provides high-level information about the subject areas or data structures of an organization.
- CDM discussion starts with main subject area of an organization and then all the major entities of each subject area are discussed in detail.
- CDM comprises of entity types and relationships. The relationships between the subject areas and the relationship between each entity in a subject area are drawn by symbolic notation(IDEF1X or IE).
- In a data model, cardinality represents the relationship between two entities. i.e. One to one relationship, or one to many relationship or many to many relationship between the entities.
- CDM contains data structures that have not been implemented in the database.
In CDM discussion, technical as well as non-technical team projects their ideas for building a sound logical data mode
The approach by which conceptual data models are created is called as conceptual data modeling.
10.What is a physical data model and physical data modeling?
- Physical data model includes all required tables, columns, relationship, database properties for the physical implementation of databases.
- Database performance, indexing strategy, and physical storage are important parameters of a physical model.
- The important or main object in a database is a table which consists or rows and columns.
- The approach by which physical data models are created is called as physical data modeling.
- Data Modelling is the diagrammatic representation showing how the entities are related to each other.
- It is the initial step towards database design.
- We first create the conceptual model, then logical model and finally move to the physical model.
- Generally, the data models are created in data analysis & design phase of software development life cycle.
12.Logical V/s Physical Data Model?
- A logical data model is the version of a data model that represents the business requirements (entire or part of an organization).
- This is the actual implementation and extension of a conceptual data model.
- Logical Data Models contain Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition etc.
- The approach by which logical data models are created is called as logical data modeling.
- When a data modeler works with the client, his title may be a logical data modeler or a physical data modeler or a combination of both.
- A logical data modeler designs the data model to suit business requirements, creates and maintains the lookup data, compare the versions of data model, maintains change log, generate reports from data model
- whereas a physical data modeler has to know about the source and target databases properties.
- A physical data modeler should know the technical-know-how to create data models from existing databases and to tune the data models with referential integrity, alternate keys, indexes and how to match indexes to SQL code.
- It would be good if the physical data modeler knows about replication, clustering and so on.
The differences between a logical data model and physical data model is shown below.
| Logical Data Modeling | Physical Data Modeling |
| Represents business information and defines business rules | Represents the physical implementation of the model in a database |
| Entity | Table |
| Attribute | Attribute |
| Primary Key | Primary Key Constraint |
| Alternate Key | Unique Constraint or Unique Index |
| Inversion Key Entry | Non Unique Index |
| Rule | Check Constraint, Default Value |
| Relationship | Foreign Key |
| Definition | Comment |
13. What is forward engineering in a data model?
- Forward Engineering is a process by which DDL scripts are generated from the data model.
- Data modeling tools have options to create DDL scripts by connecting with various databases. With these scripts, databases can be created.
14. What is reverse engineering in a data model?
- Reverse Engineering is a process useful for creating the data models from database or scripts.
- Data modeling tools have options to connect to the database by which we can reverse engineer a database into a data model.
15.What is Forward Engineering and Reverse Engineering in a data model?
- Forward Engineering is a process by which Data Definition Language(DDL) scripts are generated from the data model.
- Data modeling tools have some options to create DDL scripts by coupling or connecting with several databases.
- By using these scripts, databases can be created. Reverse Engineering is a process used for creating the data models from database or scripts.
- Data modeling tools have some options to connect with the database through which we can reverse engineer a database into a data model.