Reverse Engineering a Data Model

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.

Sources from which data model can be reverse engineered: Oracle, MS SQL, PostGreSql, DB2 UDB, MYSQL, MS ACCESS, Sybase, xml files, sql files etc.

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.

Reverse Engineering – Creating a data model from DDL scripts using Toad Data Modeler:

• Click File→Reverse Engineering.Click Next button on the bottom of the page.
• Select Data Source: Under “Select Data Source”, you can see the options “Database RE” and “DDL Script RE”.
• Since we have created this .sql script from “oracle 11g release 2” database, highlight “oracle 11g release 2” and click next.
• Select Data Provider: By default, “DDL script file data provider” is chosen. Click next.
• Connecting:Browse file from your folder and select the file “C:\Users\Data Modeling\Documents\Toad Data Modeler\GeneratedScripts\Generated.SQL”. Click next.
• Select Data Migrator: By default, it chooses “Reverse Engineering from oracle 11g release 2 DDL script of database”. Click next.
• What to reverse: Check the boxes against different objects like tables, sequences, triggers etc. Click next
• Options: Check different options like check constraints, indexes, unique constraints etc. Click next
• Alias: If you want to use the same kind of settings in future, then save your settings, click save and enter some alias name. Click next.
• Tables: You can check the tables that are required. Click Execute.

Now you can see the created data model with all database objects, relationships, and their properties in the work-space.