A previous article covered some of the features of Visio, “The Microsoft Office Business and Technical Diagramming Program.” One of Visio’s key features is its ability to reverse engineer a database, and in this article, we will take a look at doing exactly that using the sample schemas Oracle provides. Comparing the results of Visio’s reverse engineering and the schema diagrams illustrated in the Oracle Database Sample Schemas documentation, and seeing how well they match should give you some confidence in Visio’s features. The version of Visio used in these examples is the Professional 2003 edition.
Although the Professional edition is obviously more advanced than the standard version, you cannot forward engineer a schema, that is, once you have created the entity relationship diagram or model, there is not a feature that generates the data definition language for you. However, this forward engineering feature is available in the Visual Studio .NET Enterprise Architect (VSEA) version. The cost of VSEA is around $2500, but may be available for less through a reseller.
Getting started
If you can connect to a schema in SQL*Plus, you will be able to connect to it using Visio. Microsoft provides a driver for Oracle and you can select a specific driver for the version of Oracle you are using.
To create the schema diagram for the HR user in Oracle9i/10g, I selected the tables from HR and opted to show the primary and foreign keys. Given that the number of tables is small (only seven), Visio has no problem with placing the objects in the diagram. With a larger number of tables, Visio provides a warning about how the reverse engineering process may affect the model (the database) and that placing numerous objects directly in the drawing may take a significant amount of time.
The end result of reverse engineering the HR schema is shown below.
In comparison, let’s examine the diagram shown in the sample schemas documentation.
In both diagrams, you can easily see several of the relationships (e.g., the flow from departments to locations to countries to regions). The employees table refers to itself, and there is a two-way relationship between employees and departments.
With respect to referential integrity relationships, are the diagrams the same? The answer is no. One difference is between the jobs and the job_history tables. Visio shows a relationship between the job_id whereas the diagram from Oracle does not. Someone or something is wrong, and in this case, it is the diagram from Oracle.
Examination of the script (hr_cre.sql, used to create the HR schema) shows the addition or creation of foreign key constraints in the job_history table (it references the jobs, employees and departments tables).
ALTER TABLE job_history
ADD ( CONSTRAINT jhist_emp_id_st_date_pk
PRIMARY KEY (employee_id, start_date)
, CONSTRAINT jhist_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs
, CONSTRAINT jhist_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees
, CONSTRAINT jhist_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
) ;
In a second diagram showing the relationship between the HR and Order Entry (OE) schemas, the HR schema diagram reflects what Visio generated.
Exploring database properties
Now that we have the basic ERD (you can select the notation used – Crow’s foot, etc.), let’s take a look at the database properties tool. Double-click the employees table to bring up the properties toolbar.
What we are looking to confirm here is that the data definition/data types shown in Visio match those of the DDL statements in Oracle. A quick inspection shows four NOT NULL constraints (the primary key is defined via an alter table statement as opposed to being an inline statement): LAST_NAME, EMAIL, HIRE_DATE and JOB_ID. This matches with the create table statement in hr_cre.sql.
CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;
You can also see that the length of the data types matches as well. One minor variation is that Visio refers to the NUMBER data type as NUMBERPS (number, precision and scale). Visio includes a NUMBER data type (select it via the Edit button), but the reverse engineering made manager_id and department_id as NUMBERPS instead of NUMBER.
One other feature in the Database Properties toolbar is Visio’s ability to show the relationships (PK to FK) between tables. In a more complicated schema, tracing the relationship lines between tables (and trying to match different column names) can be a chore, so when in doubt, select the relationship line to view the relationship between a parent and child table. In the example shown below, we can easily see the relationship between regions and countries.
If region_id in the COUNTRIES table had been named locale_id, someone unfamiliar with the schema may have had difficulty relating the tables. The arrow association immediately clears up any doubt in this regard.
In Closing
Visio is very easy to use and offers database modelers a wide range of tools. Its only shortcoming (in the Professional edition) is its inability to forward engineer a schema. Microsoft provides an abundance of help with this product, both inside the application and at its MSDN Web site (similar to Oracle’s Oracle Technology Network site). Overall, Microsoft deserves high marks for supporting this product. Like several other current Microsoft products, Visio was originally owned and developed by another company (Shapeware), and under Microsoft’s ownership, it has grown into an industry leader.
