Reverse engineer or design a database with Microsoft Visio

How to use Microsoft Visio for Enterprise Architects SP-2 to reverse engineer your database to get nice drawings, and how to turn nice drawings into actual database objects.
Part 1: Reverse Engineer your database






















First up you simply open Visio and create a new Database Model Diagram
This gives you a database menu option in the menu bar with several helpful commands. There are other database modeling types like ER diagrams and ORM diagrams, but those don’t allow you to make changes to your diagram and update the database or generate new databases later. Now that you have your new, blank document we can use the Reverse Engineer Wizard to connect to a database and generate our drawing automatically. On the menu bar, select Database…Reverse Engineer to launch the wizard. The first step in the Reverse Engineer Wizard is to use your ODBC data sources to find the database.  If you don’t already have an ODBC connection for your database, you can create a new one inside of the wizard. Use the screenshots at the bottom of this article to guide you through creating a new User Data Source for use with Visio.













Once you have selected your database connection object, pick which types to reverse engineer (tables, keys, indexes, etc) and then pick the objects you want to reverse engineer. You are first presented with a checkbox list of tables and views followed by a checkbox list of stored procedures. Finally you are given the option to allow the system to add the shapes to the page or to add the shapes manually. I recommend allowing the system to do the work. The last screen you are presented is basically a review of your selections. Click Finish to have the system design the drawing. The resulting drawing will have all the primary keys, foreign keys, relationships, etc. When you click on an object such as a a database table you’ll get the ability to look into the table’s design; column names, indexes, triggers, etc. Here is an example of just such a view.
























 Part 2: Generate a database from a drawing

Now let’s take a look at things from the other side. In my particular case I felt that I needed to start from scratch to fully redesign my database, so I began with a blank Database Model Diagram and built up each table individually. To design a new table, drag an Entity object from the Shapes toolbox onto the drawing page and modify its database properties as I have done in this screenshot. To create a relationship between two tables, drag a relationship object from the Shapes toolbox onto the drawing page. Drag the arrow end onto the parent table and the other end onto the other table.
















 Visio will attempt to automatically identify the relationship between your tables based on the conceptual names of your tables’ fields.  This example shows just such a relationship














I worked on my table and relationship details until I had this design. I was able to normalize the data, reuse some existing tables, and basically reform the data architecture underneath this application to something that seemed to be a lot more reasonable. Now, with this kind of a table layout, I’m properly positioned to use custom business objects or an OR Mapper like the one from Paul Wilson. The old architecture made that choice difficult if not impossible.
Now that I have my nice new database object design, I want to create these tables. Actually, what I really want is the DDL script I can edit and use in any database to generate these tables. In my case, some of the tables represented in my drawing pre-exist and don’t need to be changed in any way. To do this, I go back to the handy Database menu and select Generate from the list. The first thing that will happen is that the model will be checked for any errors and warnings – things like similar field names with different data types or mismatched relationships. If I have any errors I must fix them first, much like in Visual Studio. 















Once I pass the error checking, I am presented with the Generate Wizard – . The Generate wizard allows me to chose a file location for a DDL script or I can actually have Visio connect to the database engine for me and create a brand new database. Chose to generate the script and click next to get to the database specification portion. Here you can pick which type of database rules you’re going to follow, SQL Server, Access, Oracle, DB2, etc. Pick SQL Server, give your database a name for use in the script and click Next.

 





You will be presented with a review of everything you are about to export.  Press next to do an automatic physical review of your model against that database’s rules.  Once the validation is completed you can press Finish to generate the DDL file.  Even though you might not have selected to generate the new database, you might be presented with a SQL Server Create Database dialog.  Click Close on this dialog to get to the option to view the generated DDL script in the Visio code editor.  Here is a screenshot of that generated code:

 

Now that you have this code file you can modify it and run it in query analyzer (yes – I’m still running SQL 2000) against any database you like.  The best part is that you can use a tool like Visio to maintain your database.  You can easily reverse engineer a subset of tables in a database, check on relationships, etc.  You can make modifications and persist those changes back to the database.  This is an easy way to learn about your database at a glance.  There is an Update command on the Visio database menu that I didn’t go into.  This command allows you do generate change scripts, detect differences, log errors, etc.  All in all, Visio can be an important tool in your toolbox.

Define Data Warehouse

A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process.

Subject-Oriented: A data warehouse can be used to analyze a particular subject area. For example, "sales" can be a particular subject.

Integrated: A data warehouse integrates data from multiple data sources. For example, source A and source B may have different ways of identifying a product, but in a data warehouse, there will be only a single way of identifying a product.

Time-Variant: Historical data is kept in a data warehouse. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older data from a data warehouse. This contrasts with a transactions system, where often only the most recent data is kept. For example, a transaction system may hold the most recent address of a customer, where a data warehouse can hold all addresses associated with a customer.

Non-volatile: Once data is in the data warehouse, it will not change. So, historical data in a data warehouse should never be altered.

Different Data Models in Data Warehouse

There are three types of data modelling used to construct a data warehouse, namely:

1. Conceptual Data Model
2. Logical Data Model
3. Physical Data Model

Before going in depth, let us consider some importatnt objects created while designing a database:
Entity Names, Entity relationships, Attributes, Primary and Foriegn keys, Table names, Column names and Data types.

The below chart explains about the difference between the data models based on the database objects:



Conceptual Data Model:

Conceptual data model describes about highest level of relationships between entities. It includes only the entities names and their relationships. No table names, primary keys, foreign keys and column names are referred in this model.

Logical Data Model:

Logical data model provides more information on database structure, it include entities and their relationships, Primary and forign keys and provide more possibilities of handling normalization at earlier stage.



Physical Data Model:

A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Denormalization occurs based on user requirements in this stage.

We can see that the complexity increases from conceptual to logical to physical. This is why we always first start with the conceptual data model (so we understand at high level what are the different entities in our data and how they relate to one another), then move on to the logical data model (so we understand the details of our data without worrying about how they will actually implemented), and finally the physical data model (so we know exactly how to implement our data model in the database of choice). In a data warehousing project, sometimes the conceptual data model and the logical data model are considered as a single deliverable.