This chapter is from the book
This chapter is from the book
This chapter is from the book
Introduction to Database Modeling
It is said that a picture is worth a thousand words. Nowhere can that saying be truer than in the world of database modeling. As you have seen, databases are much more than groupings of tables. Databases are groupings of related business entities. Ask yourself the question of which is easier to convey: a static list of table names or a diagram that illustrates the tables and the relationships between the tables? Whether the person is technical or nontechnical, diagrams are much easier to work with. Diagrams enable people to more easily comprehend difficult problems and concepts.
Consider the blueprint of a building. Looking at a blueprint, you immediately get a sense of the building's purpose and structure. The blueprint accomplishes much more than words could ever hope to accomplish. In a sense, a database model is a blueprint of a database. The focus of this section is to get you on the path to creating your first database model.
What Does a Database Model Look Like?
In Chapter 2, "The Anatomy of a Real Database," you were introduced to your first database model. To review, Figure 3.1 illustrates the database model for the sample Northwind Traders Database that ships with Access.
){kind=link}
Figure 3.1 The Relationships View enables you to view all the tables in a database as well as how the tables are related.
In reviewing Figure 3.1, you get an immediate sense of the database's purpose. Chapter 2 delved into the purpose of the database by examining the model. Now, you will be introduced to the components of a database model.
A Few Words About Database Design Tools
Database design tools can be as simple as a pen and paper. Database design tools can also be quite feature rich and complex. You can spend as little as a few hundred dollars or as much as several thousand dollars. Sam Snead, one of the greatest golfers in history, discussed how he shot a 76 with a swamp-maple sapling and a wedge in his book, The Game I Love. The lesson, of course, is that it is not the tool you use, but rather, how effectively you use that tool. A drawing on a piece of paper can be just as effective as a diagram produced with a tool costing thousands of dollars. Okay, maybe that is a bit of a stretch. Still, you don't have to take out a second mortgage to afford a good design tool. Two very affordable tools are on the market today:
-
Microsoft Visio—Since the Microsoft acquisition, Visio has shipped as part of the Microsoft Office Suite. For more information, go to the Microsoft Visio Web site at http://www.microsoft.com/office/visio/.
-
Resolution xCase—This is a full-featured database design tool that works with SQL Server, Access, Sybase, Informix, DB2, and many other databases on the market today. For more information, go to the Resolution xCase site at http://www.xcase.com.
Of course, if higher-end tools are what you need, you might be interested in the following tools:
Computer Associates ErWin—Find this at http://ca.com/products/alm/erwin.htm.
Visible Analyst DB Engineer—Find this at http://www.visible.com/dataapp/dappprods/vadbe.htm.
For purposes of illustrating database modeling and related notation, this book uses Microsoft Visio.
Creating Your First Model
Let's take a step back and start with a fleshed-out design to get you familiar with how to create a graphical database model. In this hypothetical situation, you have identified the following entities:
- Customers
- Orders
- Order Line Items
NOTE
All the concepts in this section can be employed with tools as simple as a piece of paper and a pencil. Graphical and automated design tools do make some jobs easier, such as ensuring foreign keys are created in child tables and deciphering whether a one-to-one or one-to-many relationship is involved. Some automated tools can even generate scripts that automatically create database definitions based on the model. Further, some tools are smart enough to keep the model and database in sync whenever changes are made to the model. All these features are nice. However, none of them is crucial when it comes to employing database modeling concepts and learning the notation.
Remember, it is the fiddler, not the fiddle!
When you start Visio, you are greeted with a dialog box prompting you for the proper drawing template to use (see Figure 3.2).
){kind=link}
After you select the Database folder, you see five templates (see Figure 3.3). For this sample model, select the Crow's Foot ERD.VST template. Recalling the beginning of the chapter, the acronym ERD stands for Entity Relationship Diagram.
){kind=link}
After you have opened the template, you can immediately use the Visio Design Surface. The Visio Designer is shown in Figure 3.4.
){kind=link}
Figure 3.2 Visio is capable of creating many types of technical drawings, including database model diagrams.
Figure 3.3 Visio ships with five database design templates. The most common modeling template is the Crow's Foot ERD.
Figure 3.4 The Visio Design Surface starts like a blank piece of paper. Many toolbars with design tools are available to create just about any type of shape and to control just about any attribute.
With an empty design surface, you are ready to create your first chart. The process involves dragging design symbols from the Design Symbol toolbox and dragging the items to the design surface. Looking at the entire process, the following steps are performed in this order:
Place an entity on the design surface.
Add attributes to the entity (name, fields, and keys).
Specify relationships between entities.
To get a sense of how steps one and two will turn out, check out Figure 3.5. Figure 3.5 shows the Customer entity with one field defined. The field, CustomerID, is set as the primary key for the entity. There will be other fields, of course. However, the topic of which fields to include in which entities is discussed in Chapter 4.
){kind=link}
Figure 3.5 The Customer consists of two attributes: the name of the entity and one primary key field.
CLUE
Let's take a moment to review some important concepts. Although the topic of which fields to include in an entity is being deferred until the next chapter, some fields have to be added to the entities early in the process. These fields exist to support the various relationships that exist between the entities. These fields are primary keys and foreign keys. The concepts of primary and foreign keys were introduced in Chapter 1, "An Introduction to Databases," and were illustrated in Chapter 2. If you need a quick review of these concepts, take a few moments now to do so before continuing.
Now that you know what an entity looks like in Visio, let's take a few moments to examine the notation of an entity (refer to Figure 3.5).
The type of diagram being created is called an Entity Relationship Diagram (ERD). An entity in an ERD consists of a rectangle, the name of the entity, and a listing of fields. As you can see, you don't need a special design tool to create diagrams. As you will see very shortly, however, design tools make the job a lot easier.
Creating entities in Visio is very simple. The following steps guide you through the process of creating entities for the Orders and Order Line Items entities:
Select the entity symbol from the Design Symbol toolbox.
While holding the left mouse key down, drag the symbol to the design surface.
After it's positioned over the design surface, release the left mouse key. A new entity on the design surface will be created.
Select the newly created entity in the design surface, and press Ctrl+C, copying the entity to the Windows Clipboard.
-
Press Ctrl+V to paste a new entity onto the design surface. The design surface should look similar to Figure 3.6 after you have arranged the entities.
-
Select the middle entity, right-click, and select Add Attributes from the pop-up menu. Name the entity Orders, and click the OK button. Figure 3.7 shows the first tab of the Define Entity dialog box.
Repeat step 6 for the entity, naming the entity OrderLineItems.
Going back to the Orders entity, repeat step 6, selecting the Attributes tab of the Define Entity dialog box.
-
Click the New button to create a new field. Name the new field OrderID, specify that the field is the primary key, and specify the data type as Integer. Figure 3.8 shows how the dialog box should look.
-
Repeat step 9 for the OrderLineItem entity, adding a field called OrderLineItemID. Your diagram should look similar to Figure 3.9.
){kind=link}
Figure 3.6 The two empty entities in the design surface represent the Orders and Order Line Items entities, respectively.
){kind=link}
Figure 3.7 The General tab of the Define Entity dialog box enables you to specify the name of the entity, as well as various display attributes of the entity in the diagram.
){kind=link}
){kind=link}
Here is where the fun really begins and the power of an automated design tool becomes truly apparent. With the entities and their associated primary keys in place, the relationships between the entities can be created. Creating relationships, like entities, is a simple process if you follow these steps:
Select the relationship symbol from the Design Symbol toolbox.
-
Drag the relationship symbol to the design surface. After it's over the design surface, you will see the relationship line. Before dropping the line, be sure the left end of the line touches one of the blue markers on the Customers entity. If you did the operation correctly, the left end of the relationship line should appear in red when the relationship line is selected (see Figure 3.10).
-
The next step is to attach the right end of the relationship line to the Orders entity. Simply click the right end of the relationship line, and drag it to one of the blue markers of the Orders entity. Your diagram should look similar to that shown in Figure 3.11. To make the illustration clearer, the diagram view is set to 150%.
Repeat step 3 for the relationship between the Orders and OrderLineItems entities.
){kind=link}
Figure 3.8 The Attributes tab of the Define Entity dialog box enables you to specify the fields and their respective attributes for an entity.
Figure 3.9 The sample ERD with three entities, each with a primary key defined.
Figure 3.10 For the automated tasks to work correctly, it is important that each side of the relationship line touches a blue marker attached to the entities.
){kind=link}
Figure 3.11 The relationship line now connects the Customers and Orders entities.
You might be asking where the automation is. After all, you have been doing all the work. The wait is over, and here is where you will see all your hard work pay off!
From the Shape menu, select Update Foreign Keys. Voilà! Based on the relationships, the tool knew the foreign keys that had to be defined. For example, for the Order entity to support a foreign key of CustomerID, a CustomerID field had to be defined. As you will see in the next chapter, whether you elect to have some fields stored in one entity over another is optional, but the fields at this level are not optional. That is why these fields are discussed in this section. Simply put, if you don't have foreign key fields, you can't have relationships. Relationships, after all, are the heart of a relational database. If you performed the steps correctly, your diagram should look similar to Figure 3.12.
){kind=link}
Figure 3.12 The database model is now complete with entities, foreign keys, and relationships.
CLUE
So, why do they call it a "crow's foot" model? Figure 3.13 shows why the model has this seemingly corny name.
){kind=link}
Figure 3.13 A crow's foot illustrates the many-side of the relationship. A vertical line illustrates the one-side of the relationship.
Creating a Physical Database from the Logical Database Model
An automated tool is not much use if it can't take a logical database model—the blueprint of a database—and create a physical database. This is where the worlds of designing a database and implementing a database come together. By working with models, you get a chance to visualize the concepts and ideas before implementation. This process is far more efficient than using the process of trial and error against the physical database to see whether things work correctly.
Visio and other database design tools have facilities to create physical databases from logical database models. In the case of Visio, the capability exists to create an Access database, and the process is easy because a wizard powers it. You select Tools, Macro, Database, Create Database Wizard to start the Create Database Wizard.
CAUTION
Be sure that no ERD errors exist. For example, an unconnected relationship results in an ERD error. If the model has errors, the resulting database will have the same errors. Before creating a database from the model, be sure to update the foreign keys before proceeding with the Create Database Wizard.
After you go through the steps of the Create Database Wizard, you will have a new Access database. The various attributes defined in the model carry through to the physical database. Figure 3.14 illustrates the results of running the Create Database Wizard.
){kind=link}
Figure 3.14 The Create Database Wizard used the model created in this chapter to create this Access database.
Creating a Logical Database Model from a Physical Database
How about the reverse situation in which you have a physical database and no model? Fortunately, Visio has the capability to take a physical database and reverse-engineer the database to create a logical database model.
To import a database, follow these steps:
From the File menu, select New, Database, Map Database Wizard.
Tell the wizard where the database you previously created is located.
Choose to map all entities.
Create a Crow's Foot diagram, and leave the Layout option as is.
Select to show all attributes and show foreign keys. Leave the other attributes as is.
Provide a title and description for the database diagram.
Click the Finish button.
Figure 3.15 shows how the resulting model should appear in Visio.
){kind=link}
Figure 3.15 The Map Database Wizard, reading an existing Access database file, created this database model.