Certification Objective 7.02: Building a Data Model for Your Application

The quality of the database design has a direct impact on the quality of the overall application. Databases that have been designed well provide the following benefits to application designers:

• They tend to provide the best performance.

• They do not need to be overhauled for the next iteration of application changes.

• They are less susceptible to lost or corrupted data.

• The database objects have a high correlation to the business objects, which makes them easier to program for.

In this section, we will examine how a database conceptual design is turned into a logical data model.

Understanding Keys and Constraints

A primary key is a column (or set of columns) that uniquely identifies the contents of a table. By definition, primary keys must be unique within a table—two records cannot exist with the same primary key. Another important rule regarding primary keys is that defining one is optional, although each table can only have a maximum of one primary key. As a general rule, every table within the database should have a primary key.

There are several reasons for this policy. The first is practical: imagine you have two records in the database that are identical to each other, as in this example:

Employee Name	Position	Department
John	Programmer	IT
John	Programmer	IT

There are two guys named John working in the IT department—that’s entirely possible, of course, so there is nothing wrong with the way this table is designed so far. But let’s say one of the John’s in IT gets promoted to the position of “Senior Programmer.” How would an application be able to choose which “John” is the one who was updated? How could that application tell the database server to only update one record?

But if you had a primary key on that table, for instance, there would be no problem:

Employee ID	Name	Position	Department
1	John	Programmer	IT
2	John	Senior Programmer	IT

The other reason for having a primary key is performance. Database servers usually create some internal optimizations (called indexes) based on sorting by primary key. Users can define their own indexes to help with sorting and searching, but the primary key is by far the fastest type of index. For example, it is much faster to look up an employee by ID than by first name. For tables that contain a large number of records (hundreds of thousands, for instance), a primary key can make a profound difference in performance.

On The Job

In general, primary keys should be based on unique numeric values (such as 1, 2, and 3), instead of strings (such as “Mary,” “John,” and “Larry”). SQL Server has two handy data types, IDENTITY and UNIQUEIDENTIFIER, that make ideal primary keys due to their uniqueness. Although it is possible to have an employee’s full name be the primary key, you run the risk of being unable to find records due to case sensitivity, extra spaces, and even spelling mistakes.

Foreign keys establish relationships between tables. A foreign key is a column that refers to the primary key of another table for the purposes of referential integrity. Referential integrity is a set of rules that ensure proper order in the database. One of the rules of referential integrity is that a record whose primary key is being used as a foreign key in another table cannot be deleted. Another rule is that a record with a foreign key cannot be inserted unless that key refers to a valid value.

Scenario & Solution

To create a unique column in a table that other tables can use to form a relationship,…	Create a primary key constraint.
To create a column in a table that references the primary keys of another table to form a relationship,…	Create a foreign key constraint.
To make searches faster when sorting tables in an order other than by their primary key,…	Create an index.
To create a unique key on a table that is composed of more than one column,…	Create a composite key.

For instance, let’s assume you have a database that contains two tables: Message and Submitter. Message has a foreign key that points to the primary key of Submitter, like so:

This means that:

• A Submitter record cannot be deleted if there are any Message records that refer to it in the database. The Message records will have to be deleted first before the related Submitter can be deleted.

• A Message record cannot be added to the database if it does not point to a valid Submitter. The Submitter referred to in the Message record must exist and cannot be empty or null.

There are other types of constraints that can be added to database table definitions during logical design. For instance, the mandatory constraint, sometimes called a non-null constraint, indicates columns that cannot be left empty. A default constraint can define a default value for columns that have not been assigned a value upon data entry.

Exercise 7-2: Converting Conceptual Database Design to Logical Design

We will turn again to the same example we used in Exercise 7-1. A law firm has asked you to redesign their client billing application. From your conversations with the client, you have learned the following about the relationship between an invoice and a billable task: “Invoices contain a list of one or more billable tasks.” The following illustration depicts this elementary fact using ORM notation:

1. Using relational database terminology, describe the relationship between the Billable Task and Invoice data objects.

2. Define a basic schema for both tables, using primary and foreign keys to implement referential integrity into the data model.

Exam Watch

Several database modeling concepts such as primary keys, foreign keys, and field-level constraints are likely to be tested on the exam.

Converting an ORM Conceptual Design to a Logical Model

Building the logical data model is remarkably simple when you’re working from a well-defined ORM conceptual design model. The ORM conceptual model almost qualifies as a logical design.

Before we get into the steps required to convert such a model, we need to define a few new terms related to this context. A simple key can be derived from any elementary fact whose uniqueness constraint spans only one role. A composite key is an elementary fact whose uniqueness constraint spans more than one role. And a compidot (compositively identified object type) is defined in the ORM specification as “either a nested object type (an objectified predicate)…, or a co-referenced object type (its primary reference scheme is based on an external uniqueness constraint)…” The most common type of compidot would be an object whose primary relationships with others are external uniqueness constraints.

There are basically four steps to convert a conceptual data model into a logical schema:

1. Initially treat the compidot as a “black box” by ignoring the external uniqueness constraint.

2. Map each composite key into its own table, creating the primary key based on the composite key.

3. Group facts with simple keys into the same table as their associated object, creating the primary key based on the object ID of this object type.

4. Unpack the compidot into its component attributes.

In order to get a better understanding of how the conceptual model can be converted, it would be helpful to look at an example.

Case Study: Creating the Logical Database Design

Most of Chapter 5 was spent working through an example of designing the ORM conceptual data model for an employment agency application. Figure 7-1 shows the final conceptual design that was created for that application.

Figure 7-1: A sample conceptual database design model

You can now work through the process of converting that data model into a logical database design, using the four steps outlined in the previous section.

The first step instructs you to temporarily ignore compidots within the application design. That is easy to do, since the conceptual model in Figure 7-1 does not have any.

The second step instructs you to move all composite keys to their own tables. Composite keys are identified on the diagram by horizontal arrows that span more than one role. There are two composite keys in Figure 7-1—the relationship between Candidate, Skill, and SkillLevel, and the relationship between Job, Skill, and SkillLevel.

So, in response to the second step, two new tables are added to the logical data model: CandidateSkill and JobSkill. The CandidateSkill and JobSkill tables each contain three fields, and they will be graphically represented in our logical database design as follows:

The third step is to add all single keys as attributes to their parent objects. This will add the Candidate, Job, Skill, and SkillLevel tables to our database. These four tables will inherit the attributes assigned to them in the conceptual design phase and are shown here:

You may notice that we actually added a couple of attributes (assigning a Name property to the Skill and SkillLevel objects) that were not present in the original conceptual design. It is not too late to make these types of changes at this stage if you discover one or two columns that should probably be there.

On The Job

Once the conceptual data model has been mapped to a logical design (or schema), all database changes should first be made to the conceptual model and then to the logical one. The conceptual model does not get discarded once we have created the database schema.

The fourth step is to unpack the compidot into its component attributes, if any. Again, we don’t have to worry about this step. But if we did, the basic procedure would be to get rid of the object that only has external uniqueness facts and roll it into another object.

We need to map down many of the other ORM conceptual constraints, such as the value constraints and mandatory constraints, to ensure they do not get forgotten. We also need to map any existing table relationships, such as the relationship between Candidate, Skill, and SkillLevel. The following illustration represents the final database diagram for this model:

Exercise 7-3: Creating Logical Design Diagrams Using Microsoft Visio for Enterprise Architects

Note that this exercise assumes you have access to the Microsoft Visio for Enterprise Architects (VEA) software that ships with Visual Studio .NET Enterprise Edition.

In this exercise, we will be creating a logical database design diagram using Microsoft Visio.

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

2. Click File | New | Database and select Database Model Diagram to create a blank database model.

3. Drag the Entity icon from the left panel onto the blank page.

4. Using the Database Properties tab at the bottom of the screen, enter a name for this table.

5. Select the Columns category from the Database Properties tab. Enter the names and data types of the columns of the database.

6. Indicate the mandatory columns as well as the primary keys on this screen as well.

7. Click File | Save As and assign your diagram a name. Click the Save button to save the diagram.