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.

Previous  

Back to DBAsupport.com