Compare Data Models and create DDL scripts

During the development cycle of data model, several changes/updates may take place over time depending upon the needs and requirement of that project. As and when required, different data model versions will be created with little or more changes between them. At one point of time, one may require to know the exact differences between different versions(older version and newer version) of data models. Knowing these will help in assuring the updates in the newer version are correct and staying intact with the needs of the requirement. Also you can apply these changes to the underlying database by creating “ALTER” scripts. In that case, data modeling tools can help us to sort out the differences between data models.

With the help of a data modeling tool you can compare:

  • between two data models
  • data model with the script
  • data model with the underlying database

Significance of comparing data models:

  • To know the differences between them Or to know what changes have been taken place in the latest version.
  • Whatever differences are there, we can create scripts and the same can be implemented on the database.

In this article, we are using Toad software to compare between two different data model versions as well as to create ALTER scripts for database update.

Note: In Toad Data Modeler Software, data models are saved with file extension of .txp.

The following table represents a data model with a file name “Domain_Rule_Default_v1.txp” in the first stage of a development of a data model.

Column NameData Type
FULL_NAMENAME_DOMAIN NN
STREET_NAMENAME_DOMAIN NN
CITY_NAMENAME_DOMAIN NN
GENDERChar(1) NN
SSNChar(9) NN
RECORD_DATEDate NN
USER_NAMENAME_DOMAIN NN

In the second stage of a development of a data model, you are requested to add another column “COUNTRY_NAME VARCHAR2 (30) NOT NULL)”.

Follow the guidelines below to insert the requested column in the data model using Toad Software.

  • Save the existing data model “Domain_Rule_Default_v1.txp” as “Domain_Rule_Default_v2.txp”.
  • Add a column “COUNTRY_NAME VARCHAR2 (30)” and assign NOT NULL to it.
  • Save the data model.

The following table represents the data model “Domain_Rule_Default_v2.txp” created in the second stage of a development of a data model.

Column NameData Type
FULL_NAMENAME_DOMAIN NN
STREET_NAMENAME_DOMAIN NN
CITY_NAMENAME_DOMAIN NN
GENDERChar(1) NN
SSNChar(9) NN
RECORD_DATEDate NN
USER_NAMENAME_DOMAIN NN
COUNTRY_NAMEVarchar2(30) NN

For comparing the data models:

  • Open TOAD Data Modeler.
  • Click menu File/Sync & Convert/Sync & Convert Wizard.
  • Action: On the new window that is appearing, click the button “Compare Models and Generate Alter Report”. Click next.
  • Select Left Side: Click the button Model File “*.txp, *.txl “. Select “Domain_Rule_Default_v2.txp”. Click next.
  • Select Right Side: Similarly click the button Model File “*.txp, *.txl “. Select “Domain_Rule_Default_v1.txp”. Click next.
  • Settings: You can select the comparison rules (Ignore text case, Ignore Model Name, Resolve Application Variables, Ignore empty Primary Keys, etc). Click next.
  • Select Object Types: You have compare all, compare all without graphics, compare settings buttons. Click compare all and. Click next.
  • Select Items: A new window appears and you can see that COUNTRY_NAME doesn’t exist in “Domain_Rule_Default_v1.txp”.

How to generate Alter Script after comparing the data models?

  • Open TOAD Data Modeler.
  • Click menu File/Sync & Convert/Sync & Convert Wizard.
  • Action: On the new window that is appearing, click the button “Generate Alter Script”. Click next.

Finally, the script generated by TOAD as follows:


ALTER TABLE “DOMAIN_RULE_DEFAULT” ADD (“COUNTRY_NAME” Varchar2(30) NOT NULL)


 

Comparison of Relational and Dimensional Data Modeling

Relational | Dimensional Data Modeling:

Relational Data Modeling is used in OLTP systems which are transaction oriented and Dimensional Data Modeling is used in OLAP systems which are analytically based. In a data warehouse environment, staging area is designed on OLTP concepts, since data has to be normalized, cleansed and profiled before loaded into a data warehouse or data mart. In OLTP environment, lookups are stored as independent tables in detail whereas these independent tables are merged as a single dimension in an OLAP environment like data warehouse.

Relational vs Dimensional Data Modeling

Relational Data ModelingDimensional Data Modeling
Data is stored in RDBMSData is stored in RDBMS or Multidimensional databases
Tables are units of storageCubes are units of storage
Data is normalized and used for OLTP. Optimized for OLTP processingData is de-normalized and used in data warehouse and data mart. Optimized for OLAP
Several tables and chains of relationships among themFew tables and fact tables are connected to dimensional tables
Volatile(several updates) and time variantNon volatile and time invariant
SQL is used to manipulate dataMDX is used to manipulate data
Detailed level of transactional dataSummary of bulky transactional data(Aggregates and Measures) used in business decisions
Normal ReportsUser friendly, interactive, drag and drop multidimensional OLAP Reports

 

Comparison of Logical and Physical Data Modeling

Logical | Physical Data Modeling:

When a data modeler works with the client, his title may be a logical data modeler or a physical data modeler or combination of both. A logical data modeler  designs the data model to suit business requirements, creates and maintains the lookup data, compares the versions of data model, maintains change log, generate reports from data  model and 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 vs Physical Data Modeling

Logical Data ModelPhysical Data Model
Represents business information and defines business rulesRepresents the physical implementation of the model in a database.
EntityTable
AttributeColumn
Primary KeyPrimary Key Constraint
Alternate KeyUnique Constraint or Unique Index
Inversion Key EntryNon Unique Index
RuleCheck Constraint, Default Value
RelationshipForeign Key
DefinitionComment