It goes without saying that, in general, application designers and
For the last 25 years, entity relationship (ER) modeling has been the leading standard methodology for conceptual database design. ER was, and still is, a popular notation for graphically defining the structure of a database.
Object Role Modeling (ORM) is a relatively new technique for modeling database design. It is
Although the 70-300 exam may contain only a few ORM-
Object Role Modeling (ORM) is a process for developing the conceptual design for a database. ORM uses natural language to describe the conceptual elements of a system, such as objects and roles. In fact, ORM provides a step-by-step process for creating a conceptual database design known as the Conceptual Schema Design Procedure (CSDP).
Within the context of overall application design, database design might be one of the most important things to do properly. A well thought-out database design will influence application design in a positive way, while a poorly thought-out database design will have the
The process of database design involves three phases: conceptual, logical, and physical. Unlike application design, database design is not a prequel to another phase. At the end of database design, you will have a database. The database may be just one small component of a larger application that still needs to be
The purpose of the conceptual design phase of database design is to describe the rules of a system in natural language. For instance, if we were designing the database for an online banking application, the conceptual design would establish the system’s entities (such as bank, account holder, and account) and their properties. Here are some examples of account properties:
An account has an account number.
An account has a balance.
An account has one or more account holders.
Each account holder has a
During the conceptual design phase, the focus is on formalizing and validating the requirements of the system. It involves making an exhaustive list of these properties and checking to see if any of them need to be split or combined. Database designers often find it useful to use diagrams to help them visualize these entities. Viewing these entities and their relationships in a diagram often reveals problems with the design that could be overlooked in a textual description.
Once the most suitable conceptual design has been established, the database can be mapped to a
which results in a schema. The logical design takes into account the type of database involved (relational, hierarchical, object-oriented, and so on) in defining tables, primary keys, foreign keys, and other constraints. Developers will often refer back to the logical design model when programming the objects and
|On The Job||
I have worked in some places where the logical design diagram was printed on large 6' × 8' pieces of paper and taped to a wall for easy reference by developers. For databases with a hundred or more tables in them, this helps make the system easier for everyone to understand.
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.
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
Understandable Business rules are written in plain language.
Capable The graphical model supports many different advanced business rules.
Business rules are
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.
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.
The ability to create a conceptual design using a method such as ORM is one of the skills measured by exam 70-300.
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
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
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
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.)
Choose Start All Programs and choose Microsoft Visio to start the application.
Choose File New Database and select ORM Diagram to create a blank UML model.
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.
Like many things in life, the best way to learn the
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-
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.
Visual Studio .NET for Enterprise Architects is one of the products available for download to MSDN Universal subscribers. For about the same price as a single license of Visual Studio .NET, you can receive development versions of almost all of Microsoft’s enterprise software. This includes Visual Studio .NET, all current versions of Windows, productivity applications (such as Office XP), and other server software (such as SQL Server and Exchange Server). The MSDN Subscription web site is at http://msdn.microsoft.com/subscriptions.
One of the early concepts of database development, which predates even the existence and widespread use of relational databases, is the concept of
. 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—
A database that follows the rules is called
, while a database that intentionally breaks one or more of the rules is called
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
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
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
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
— Scott Duffy, MCSD, MCP+SB, SCJP, IBMCD-XML