This article is an introduction to VisioModeler. This is a free CASE tool, that can help you design your database, in the process sharing the information with the client in a visual, easy-to-understand, format. VisioModeler uses an interface called ORM (Object-Role Modeling).
History
When I learned about InfoModeler several years ago, it cost 700 dollars (support for one DBMS), or 3000 dollars (support for multiple DBMSs).
Later, Infomodeler was bought by Visio, and renamed VisioModeler. Visio was eventually bought by Microsoft. Since Microsoft apparently has other plans for this kind of CASE tools, VisioModeler was declared "unsupported".
Download and requirements
VisioModeler is available for free at Microsoft’s site. Just go to www.microsoft.com, and search for “download visiomodeler”. The download is about 25 MB.
Being a somewhat older tool, the hardware requirements should cause no problems. For instance, it requires at least 16 MB RAM.
ORM
The user’s interaction with VisioModeler uses a model called ORM (Object Role Modeling). Instead of presenting information in the form of tables, information is presented in a way which, in my opinion, is much easier to understand for a client than information presented as “tables”, which is quite an abstract concept for anybody but computer programmers.
However, as we shall see, VisioModeler can also show information in table format.
A detailed description of ORM is beyond the scope of this article; the examples will give you an introduction to how information is presented. The help included in the product contains a detailed, more formal, description, for those who are interested.
Overview of the design process
Getting started
OK, that's enough introduction for now; let’s get to work.
For a start, open VisioModeler, click on “File | New Model”, and under model type, select “Object Role Modeling Diagram”. While VisioModeler also lets you design logical diagrams (i.e., in a “table format”), for the purposes of this article we will start our design with an ORM model.
Next, we have to register a few facts that relate different pieces of data (“Objects”). For example, let us create a model for registration of students, as might be used at a university for example.
To see where we are going, here are some facts, in text form. Objects are underlined:
However, when we analyze the relation "Student registers in Course", it turns out that a student may register in the same course more than once, for example, if he flunks it. So, for additional information (to uniquely identify each record in the resulting table), we might want to include the semester. This changes the second relation to a ternary relation (i.e., 3 objects involved):
Image 1 shows the diagram in VisioModeler we want to achieve with this data.
Image 1: Initial model.
To achieve this model, you can proceed in different ways.
First, the hard (or more detailed) way, you can use the buttons on the ORM toolbar, as follows: the second button from the topo (with an oval) creates objects, such as “Student” or “LastName”. The third button from the top creates a relation; whereas the fifth button from the top is used to connect the relation with the objects.
Image 2: The ORM toolbar.
The quick way is to use the menu item “Tools | Create a New Fact” or the shortcut F5. For a start, use the guided version (under “Input style”). Finishing this window will create the objects (if objects by the specified names don’t exist yet), and the relation.
Image 3: The fact editor.
Yet another alternative is the command “Tools | Quick fact” (F6), which will create objects and relations, which you have to edit later, to fill in object names, among others.
Additional information
After creating the basic relationships, the kind of some objects should be changed to “Value”. Take the relation “Student has LastName”. The “student” data has additional properties (or associated data), that is, student is of kind “Entity”. “LastName” does not have additional properties, so it is of kind “value”. Values will eventually translate to fields, rather than separate tables. (Note: The formal definition used by ORM theory is different; read the online help if you are interested in the details.)
Double-click on “LastName”, and on the tab “Definition”, change the kind to “Value”. Note that this changes the outline of the ellipse to a dotted line.
I have done this with the “LastName”, "StudentCode", “SubjectCode” and “SubjectDescription” data items.
Also, for those data items that are defined as "entities" (i.e., the "kind" has not been changed to "value"), you can specify a "Reference"; this can be thought of as the primary key.
With those changes, your diagram should look similar to the first draft, shown above.
Constraints
VisioModeler allows the definition of several constraints to the data. The most important one is the UC constraint, or uniqueness constraint, and it is required for each relation. It tells us which data items, in a relation, can repeat, and which can’t.
One way to do this is to add examples to each relation, and let VisioModeler figure out the corresponding UC constraint, or constraints. This helps improve the documentation. You can also add UC constraints directly, with the constraints toolbar.
Let's try it the first way. Double-click on the relation "Student has LastName", select the "Examples" tab, and fill in the following examples:
Note that the last name "Smith" is repeated; this implies that, indeed, more than one student can have the same LastName. Examples should always show which data items (or combinations of items) can repeat, and which not.
If you like, click on the "Analyze" button to get a quick analysis of the examples. For example, VisioModeler will protest if you provide less than 4 rows of examples, in a binary relation, saying that more examples are required.
Now, click on "Set UC constraints", to place the UC constraints on the relation. In this example, you will get a double arrow over the first part of the relation, the one that corresponds to "Student". This means that this part of the relation can not be repeated. Indeed, that is exactly what our examples imply.
In a one-to-one relation (binary), on the other hand, you will get two double arrows, one for each part of the relation. In a many-to-many relation, you will get a single arrow that spans the entire relation; this means that the combination of the two parts can't repeat.
After placing examples and the corresponding UC constraints for a few additional relations, our model looks as the following image.
Image 4: Model, including UC constraints.
Nesting
As you may be aware, in a relational database a many-to-many relationship, such as "Student takes subject", translates to a separate table. Let's call this table (or entity) "Registration". It would make sense to convert the relation itself into an object, so that we can assign it additional properties; this is achieved through a process known as "Nesting".
Select the relation "Student / Subject / Semester", and, in the ORM toolbar, click on the fourth button from the top, "Nesting". This will create a rounded rectangle around our relation, with the name "StudentSubjectSemester". It makes sense to double-click on this new object and rename it to "Registration", or some other meaningful name.
Next, you can start to include the "Registration" object in other fact. For example, the student will eventually get a final grade, so the fact might read "Registration has FinalGrade".
Image 5: Nesting example.
In this particular case, the nested relation is ternary (three objects), but it can just as well be binary (the most common case), or have any other "arity".
When selecting the nested relation, you must distinguish between the relation (inside) and the nested object (outside); they are two different things.
Creating the logical model
After doing some modeling with ORM, we are ready to create the logical model, i.e., to show the data as tables.
You might first click on the "Check document" button to check for errors (a common one is missing UC constraints). Or, you can directly click on "Build Dictionary"; this will implicitly check the document first.
Field types
Field types can be defined either in the ORM model, or in the logical (tables) model (changes in the logical model can be migrated back to the ORM model). I suggest defining them in the ORM model; in general, I believe everything can be done in the ORM model, although at first it may take some time getting figurint out where to do things.
Field types can be defined in a generic ("portable") way, or for a specific database (e.g., Visual FoxPro).
Creating the database
The database itself is defined with the button "Generate database" of the main toolbar. You have the choice to either create a script that will generate the database (for example, a PRG that will run in Visual FoxPro), or connect via ODBC and create the database directly.
Other capabilities of VisioModeler include modifying an existing database, as well as re-engineering (extracting an existing database design, into either a logical or an ORM model).
Summary
This article was meant to give you a brief overview into this database modeling tool.
VisioModeler also offers some additional capabilities, such as additional constraints, which I will not currently delve into; the overview was meant to get you started.
Source code
