Category: Physical Data Modeling

What is Reverse Engineering a Data Model?

Reverse Engineering is a process or function to create a logical and physical data model by extracting information from an existing data source. You may either create a physical data model or logical data model or combination of both. Data Source may be oracle data base or MS SQL Server or IBM DB2 or .sql files etc.

Example: Oracle database may contain objects likes tables, views, indexes, sequences, triggers, rules, data type, owner, constraints like primary keys, foreign keys, comments etc. You can specify the objects that you are interested to create a model. If you are not interested in triggers, then there is no need for you to check that option.

All you have to know is how to connect to the data source and whether necessary privileges are allocated to you.

If you do have any problems, while connecting to the data source, please ask system administrator or database administrator to resolve the issues.

Reasons for Reverse Engineering:

Data base may be present but there may be no data models and hence no logical data model and physical data model print outs for which project team may not be sure about the objects(tables, index, constraints etc) and relationships between them in data base.

Data Models may be present but it may not be in sync with data base.

When you integrate different sources(some sources may not have data models) into one database to create a data warehouse, then you have to represent these sources as data models (original AS IS). During discussions within the project team, it will give more clarity about the original structure (AS IS) and future (data warehouse) data models.

You can give logical data models to the business community (SME, Business Analysts etc.) and physical data models to development community (developers and dbas)

Note:

You can modify/update the reverse engineered model as per your needs. Alignment of tables and relationship on the work place may not be proper and printing out the models will not be in a presentable format. So please align it.

The Reverse Engineering wizard for each data base modeling software may be different. Learn how to connect to the databases, how to check different database objects that you want to reverse engineer and see whether you can save these settings.

How to create a data model from a Database?

For example, if you would like to reverse engineer database object from Oracle Production Server. To connect to oracle, you will need user name, password, and connection string. Then you have to check/click all different database objects(tables, columns, indexes, views, synonyms etc) that your are interested to reverse. Try to find out whether you can save these settings with a “NAME” (PRODUCTION_ORACLE_SERVER). The reason is when you connect to the same database next time, then there is no need for you to check all those options once again and you can use the saved information with the name “PRODUCTION_ORACLE_SERVER”.

How to create a data model by reverse engineering from scripts?

In the section, we are using the DDL scripts which have been created earlier in the page DDL Scripts from Data Model. Generated.SQL is the file name that contains DDL scripts which is stored in your hard drive. Now we will explain how to reverse engineer and create data model from the above script.

In the bottom of the window, you can see buttons like Generate, Show Code, Show Log.

Click the button ‘Generate’. It will save the DDL scripts in a file under a folder. You can see the location of the file in “what to generate tab”. Example: C:\Users\Data Modeling\Documents\Toad Data Modeler\GeneratedScripts\Generated.SQL

If you have access to the databases, then you can directly execute those scripts and create database objects in database or database schema.

Click Show. It will retrieve the information from that file “Generated.sql” and code is shown below.