Home > Articles > Data

  • Print
  • + Share This
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.

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:

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).

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.

After you have opened the template, you can immediately use the Visio Design Surface. The Visio Designer is shown in Figure 3.4.

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:

  1. Place an entity on the design surface.

  2. Add attributes to the entity (name, fields, and keys).

  3. 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.

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:

  1. Select the entity symbol from the Design Symbol toolbox.

  2. While holding the left mouse key down, drag the symbol to the design surface.

  3. After it's positioned over the design surface, release the left mouse key. A new entity on the design surface will be created.

  4. Select the newly created entity in the design surface, and press Ctrl+C, copying the entity to the Windows Clipboard.

  5. 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.

  6. Figure 3.6 The two empty entities in the design surface represent the Orders and Order Line Items entities, respectively.

  7. 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.

  8. 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.

  9. Repeat step 6 for the entity, naming the entity OrderLineItems.

  10. Going back to the Orders entity, repeat step 6, selecting the Attributes tab of the Define Entity dialog box.

  11. 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.

  12. Repeat step 9 for the OrderLineItem entity, adding a field called OrderLineItemID. Your diagram should look similar to Figure 3.9.

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:

  1. Select the relationship symbol from the Design Symbol toolbox.

  2. 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).

  3. 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.

  4. 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%.

  5. Figure 3.11 The relationship line now connects the Customers and Orders entities.

  6. Repeat step 3 for the relationship between the Orders and OrderLineItems 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.

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.

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.

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:

  1. From the File menu, select New, Database, Map Database Wizard.

  2. Tell the wizard where the database you previously created is located.

  3. Choose to map all entities.

  4. Create a Crow's Foot diagram, and leave the Layout option as is.

  5. Select to show all attributes and show foreign keys. Leave the other attributes as is.

  6. Provide a title and description for the database diagram.

  7. Click the Finish button.

Figure 3.15 shows how the resulting model should appear in Visio.

Figure 3.15 The Map Database Wizard, reading an existing Access database file, created this database model.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020