Once the database schema has been defined in the logical design phase, it can be mapped to a physical design. The physical design is dependent on the actual database management system (DBMS) involved, such as SQL Server or Oracle. For relational databases, the physical design phase involves the creation of Structured Query Language (SQL) table creation statements. These SQL scripts can be run to create the database from scratch any time the database needs to be refreshed.

Benefits of Using ORM

In short, ORM is a well-developed and easy-to-understand method for modeling data at a conceptual level. ORM consists of both the process for creating this model, called Conceptual Schema Design Procedure (CSDP), and the language associated with the model, called Formal Object-Role Modeling Language (FORML).

ORM has been designed to be understandable, capable, reliable, stable, and executable. We can elaborate on how it satisfies these design objectives as follows :

• Understandable Business rules are written in plain language.

• Capable The graphical model supports many different advanced business rules.

• Reliable Business rules are validated using real sample data.

• Stable It is easy to make changes to the model.

• Executable The ORM conceptual data model can be mapped directly to a fully normalized relational database schema.

Comparing ORM and ER

For years , ER modeling has been one of the most popular conceptual modeling languages for database design. ER models are still useful for documenting the conceptual design of a system but are far less useful for formulating that design or making and applying changes to it. In fact, the ORM model still includes a use for ER but only as one “view” of the conceptual data model.

ORM is a system based on natural language because instead of creating its own notation for describing the database of a system, it uses English. ORM is similar to another natural language design methodology called Natural Language Information Analysis Method (NIAM) and is partly based on that method.

There are other benefits to using ORM instead of ER as well. ORM allows database designers to provide more detail about the design in the diagrams and simplify the addition of constraints to the model. In short, the creator of ORM analyzed the modeling languages that came before it and created a language that overcomes many of the deficiencies of those languages while retaining their simplicity.

Creating ORM Diagrams in Visio

As we discussed in Chapter 1, Microsoft Visio for Enterprise Architects is a fullfeatured diagramming tool that has many applications outside of the application design arena. The tool contains hundreds of symbols and icons that can be used to create complex diagrams and graphics. Of course, because it is included with the Enterprise Architect edition of Visual Studio .NET, it contains dozens of icons and templates for designing software applications and databases as well. (Chapter 2 demonstrates how Visio could be used during the envisioning phase of the MSF to create UML state diagrams.)

The two ORM templates in Visio are located under the Database category; they are ORM Diagram and ORM Source Model. The ORM Diagram template contains over 25 ORM icons and is used when you do not want the assistance of wizards and specialized Visio plug-ins. The ORM Source Model contains only three basic icons but contains a fully integrated ORM environment (including wizards and Visio plug-ins) to help create diagrams. Using the ORM Source Model, Visio does most of the work of creating the diagrams for us.

You can see a screenshot of the Visio ORM Source Model template in Figure 5-1. I’ve resized several of the windows to make the Business Rules pane more prominent because that is where we will be doing most of the work.

Figure 5-1: Visio’s ORM Source Model template creates the ORM diagram for you.

Exercise 5-1: Creating a Blank ORM Diagram in Visio for Enterprise Architects

Microsoft Visio contains a number of ORM templates to help you create conceptual database design diagrams. Creating an empty ORM diagram in Visio is quite simple— the process takes only three steps. (Note that the following example requires the Visio for Enterprise Architects software, which comes bundled with Visual Studio .NET Enterprise Architect.)

1. Choose Start All Programs and choose Microsoft Visio to start the application.

2. Choose File New Database and select ORM Diagram to create a blank UML model.

3. Choose File Save As and assign your diagram a name. Click the Save button to save the diagram.

   On The Job

   Microsoft Visio also has the ability to reverse engineer an ORM model based on the physical schema in an existing database. This is done by choosing Database Reverse Engineer from a blank ORM Source Model template. The Reverse Engineer Wizard will walk you through the steps of creating a reverse-engineered ORM diagram.

ORM Resources

Like many things in life, the best way to learn the nuances of database design is through experience. Experience will teach you things a book will have a difficult time explaining. For instance, you will sometimes need to decide between enforcing the rules of normalization and breaking those rules for the sake of performance and increased simplicity. Of course, the process of learning can be accelerated by learning new techniques from a book, reading articles, and discussing design with peers.

To learn more about ORM, I recommend a visit to the official ORM web site as a first necessary step. The official ORM web site is located at http://www.orm.net. That site contains a number of articles that discuss the process of using ORM to construct the conceptual design of databases in more detail. In particular, the article “Object Role Modeling: An Overview” is a very good primer on the topic.

Because ORM is the primary database design methodology recommended by Microsoft, the Microsoft Developer Network (MSDN) web site is also a good resource on the topic. In addition, Microsoft’s “.NET Show” television show aired a two- hour episode on the topic of ORM, and Microsoft has made a copy of it available for download from http://msdn.microsoft.com/theshow/Episode025. Microsoft has also produced a five-part tutorial on creating ORM diagrams using Visio for Enterprise Architects. The first version of the tutorial is available from http://msdn.microsoft.com/ library/en-us/dv_vstechart/html/vstchvseamodelingp1.asp.

For those of us who can’t afford the $2,500 to purchase a single copy of Visual Studio .NET Enterprise Architect in order to get the Visio for Enterprise Architects modeling tool that comes with it, Microsoft provides another ORM modeling tool as a free download. This tool is called VisioModeler. We can’t provide a direct link to VisioModeler, but we can direct you to the Microsoft Download Center at http://download.microsoft.com/. From there, you can obtain the free ORM tool by performing a keyword search and using VisioModeler as the keyword.

From the Classroom—Understanding the Five Rules of Normalization

One of the early concepts of database development, which predates even the existence and widespread use of relational databases, is the concept of normalization . Normalization is the process of organizing a database according to certain rules. These rules exist to ensure that the database is unambiguous and operates as intended— efficiently and without data loss. The ORM process does a good job ensuring that ambiguity and redundancy are removed from the schema before the end of the process, so designers that follow the ORM model do not need to worry too much about normalization.

A database that follows the rules is called normalized , while a database that intentionally breaks one or more of the rules is called denormalized . There are certain circumstances where it is OK to denormalize a database for the sake of efficiency and performance. For instance, if normalizing a table would double or triple the amount of time a critical report takes to complete, you could probably justify leaving the table denormalized for performance reasons.

The five rules of normalization need to be followed in exact order; however, not all normalized databases follow all five rules, and a terminology exists to describe to what level of normalization a database complies. For instance, a database that follows only the first rule of normalization is said to be in first normal form, which is sometimes abbreviated 1NF. A database that follows the second rule is in second normal form, or 2NF. The naming scheme continues up to fifth normal form, or 5NF. Oddly, most normalized databases only meet the requirements for third normal form because the fourth and fifth rules are much harder and less practical to implement.

The first rule of normalization is that there cannot be any repeating groups among the columns. For instance, let’s assume we have a table named Student and we want to store the most recent three grades achieved using three columns named Score1, Score2, and Score3. In this example, the Student table does not follow 1NF, since the Score column is repeated three times. We will need to create a second table, named StudentScores, to list the most recently achieved scores as separate rows. As you can already observe, normalization tends to add more tables to a database.

The second rule of normalization is that a database must (a) be in first normal form, and (b) for tables with composite primary keys, all non-key columns must be wholly dependent on both values of the key. For instance, let’s assume we have a table named StudentCourses, whose composite primary key is StudentNumber and CourseNumber. Let’s also assume that table has two non-key columns, CourseGrade and TeacherID. We can see that the StudentCourses table does not follow 2NF. The reason is that the teacher of a course depends only on the course, not on the student. That is to say, the teacher specified would be teaching that course regardless of which students are actually taking that course. To make this table 2NF, we will have to create a second table named CourseTeachers to contain the teacher IDs associated with each course ID.

The third rule of normalization is that a database must (a) be in second normal form and (b) not have any columns that are not dependent on the primary key. This level of normalization goes a step further than 2NF because this applies to all tables. Let’s assume you have a table named Student with a primary key of StudentNumber. Two of the columns on the Student table are StatusCode (containing either the letter A or I ) and StatusCodeDescription (containing either the word “Active” or “Inactive”). This table does not follow third normal form, because the StatusCodeDescription column is not dependent on the student number but rather on the status code. The solution is to create a table of status codes and their associated descriptions. Once this is done, the Student table can refer only to a status code, and the application will be able to look up the description for that code in another table.

The fourth rule of normalization is that a database must (a) be in third normal form and (b) not have any independent, multivalued facts stored in one table. For instance, let’s assume that the StudentCourses table contains StudentNumber, CourseNumber, and DegreesHeld. This table is in third normal form but not in fourth because CourseNumber and DegreesHeld are both multivalued columns—a student can enroll in more than one course, and a student can hold more than one degree. But the fourth rule says that a table cannot contain more than one multivalued column, so the StudentCourses table will have to be split into two tables.

The fifth rule of normalization is that a database must (a) be in fourth normal form and (b) be free from data redundancy that is not covered by any of the previous normal forms. An example of this rule would be an Enrollment table containing three columns—Department, CourseNumber, and StudentNumber—that cannot be split under the rules of the fourth normal form because StudentNumber and CourseNumber are related to each other (that is, we need to know what courses a student has enrolled in). Splitting the table in two would result in information being lost. But if the Enrollment table were split into three tables (DepartmentCourses, DepartmentStudents, and StudentCourses), no information would be lost. Splitting the table like this would follow 5NF.

As you can see, normalization tends to add tables to a database and create more complex dependencies, but it often makes a database smaller in total size by reducing the duplication of data. For instance, by having the StatusDescription in its own table, the database would only have to keep one copy of that string instead of having potentially thousands of copies—one for each student.

— Scott Duffy, MCSD, MCP+SB, SCJP, IBMCD-XML