Designing a Database Conceptually

Database design is a critical part of the software development process. To ensure that the design fulfills business and operational requirements, database designers typically create three different database models, as shown in Figure 5-1.

• Conceptual model   A high-level representation of user and operational requirements. It helps the formalization process of the requirements and represents how data is perceived by business users.

• Logical model   Detailed representation of the software model that will capture the structured data. It helps to eliminate or at least drastically reduce data redundancy and increases data integrity. Logical models represent how the developer sees the data.

• Physical model   Detailed specification of all tables and columns of the database. It maps specific vendor technologies in the model. Physical models represent how the server stores the data.

Figure 5-1: Three-Step Database Model

Note 

To the untrained eye, this three-step process may seem time consuming, but that is not the case. Modeling the database in three steps offers a better quality control process and is frequently faster than a single-step design process.

Validating Business Requirements through Conceptual Models

To comprehend and validate the business problem, database designers create conceptual models that serve as the foundation of logical and physical models. Four modeling techniques can be used to create conceptual models.

• Object-Role Modeling (ORM)

• Unified Modeling Language (UML)

• Object-Oriented Systems Model (OSM)

• Entity-Relationship modeling (ER)

Note 

Microsoft Office Visio for Enterprise Architects supports ORM, UML, and ER diagrams.

ER conceptual modeling is used in this book, but you should examine other models to discover a system with which you are comfortable.

Creating the First Model

To create a conceptual model of your application, you will need to examine all of the captured requirements and identify the following components :

• Entities   People, places, items, or concepts

• Attributes   Properties of an entity

• Relationships   Connections between entities

The best way to understand these concepts is by example. As part of the requirement-gathering process, assume that you obtain the following copy of an Adventure Works invoice.

On this invoice, you identify the following entities: Invoice, Invoice Line (Invoice Item), Customer, Order, and Payment. You also identify the following attributes of the Customer entity: Name , Address, City, State, Zip Code, and Phone. Lastly, there is a relationship between Invoices and Customer entities. Continue identifying model components (entities, attributes, relationships) until all of the requirements are covered and all relevant business information is modeled .

Diagramming the Model

Several tools allow you to create ER diagrams, and Microsoft Visio for Enterprise Architects will be used in this book. This version of Visio is included in Visual Studio 2005 Team System (Developer, Tester, Architect) or as part of Visual Studio Professional with MSDN Premium.

Creating a Conceptual ER Diagram Using Microsoft Visio

1. From the Start menu, select Programs Microsoft Office Microsoft Office Visio For Enterprise Architects.

2. In the Choose Drawing Type window, select the Database category.

3. In the Database category, choose the Database Model Diagram or ER Source Model.

   

Creating Entities

To document an entity in an ER Visio diagram, complete the following steps.

Modeling Entities in Visio 2003

1. Drag and drop an entity shape from the Shapes toolbar to the diagram surface.

2. Select the recently added entity.

3. In the Database Properties window, choose the Definition category.

4. Name the entity using the Conceptual Name field.

   

   Try to maintain a simple naming standard when naming objects. The object names you choose should be easily identifiable by users.

Adding Attributes

To include attributes in the appropriate entity, complete the following steps.

Modeling Attributes in Visio 2003

1. Choose the Columns option in the Categories listbox, which is located to the left of the Database Properties window.

2. Add the attribute name in the Physical Name column.

   

3. You may also choose the attribute datatype at this time, but datatypes will be discussed in greater detail in the sections dealing with logical modeling.

Adding Relationships

In an ER diagram, relationships are represented with arrows. The arrow representing a relationship always points to the referenced entity. This means that the model would verify that the referenced element exists, and the integrity of the data is preserved for every referencing element. For example, when creating a relationship between the Cities and States/Provinces entities, the model would not allow the inclusion of a city that references a State or Province that does not exist.

Many types of relationships can be modeled in ER diagrams. The most common and simple type of relationship is the parent-child relationship . In this relationship, for each entity A, there are many entities B. Entity A is called the parent entity, and Entity B is the child entity. Parent-child relationships are also referred to as one-to-many relationships because one entity in the parent entity may be referenced by many entities in the child entity.

Modeling a Parent-Child Relationship

1. Drag and drop a relationship shape from the Shapes toolbar to the diagram surface.

2. Select the head of the arrow, and drag and drop it over the parent entity.

   In a relationship between entities, the parent entity is the one that is referenced. For example, in the Customer-Invoice relationship, Customer is the parent entity and Invoice is the child entity.

3. Select the bottom of the arrow, and drag and drop it over the child entity.

   

Other Types of Relationships

When creating a conceptual model, you may encounter some distinctive relationships that do not have a standard parent-child relationship between two entities. These relationships include intersection, multi-intersection, and self-referencing relationships.

Modeling intersection relationships   Intersection relationships possess a many-to-many association between entities, such as students and courses. Students may register for many courses, and courses may have numerous enrolled students. There is no parent-child relationship between students and courses. To model a many-to-many relationship, you will need an additional table that may or may not have additional attributes.

Modeling an Intersection Relationship

1. Drag and drop three entity shapes from the Shapes toolbar to the diagram surface.

2. Name the entities Students, Courses, and EnrolledStudents.

3. Add the required attributes to the Students and Courses entities.

4. Add two relationship shapes to the diagram surface.

5. Drag and drop the heads of both arrows over the EnrolledStudents entity.

6. Drag and drop the bottom of one relationship arrow over the Students entity and the bottom of the other relationship arrow over the Courses entity.

7. Add attributes to the EnrolledStudents entity.

   

To model a many-to-many relationship, add an additional table and establish two one-to-many relationships between the intersection entity and the other entities.

Modeling multi-intersection relationships   The multi-intersection relationship uses the same guidelines as the intersection relationship, with the difference being that more than two tables participate in the relation. For example, assume that you want to enhance the previous model and capture information about scheduled courses in a university. You want to capture the relationship between courses, classrooms, professors, and schedules. The relationship can be modeled as shown in Figure 5-2.

Figure 5-2: Example of a Multi-Intersection Relationship

Modeling self-referencing relationships   Self-referencing relationships involve entities that reference themselves . They do not have two different participating entities, but instead have only one entity participating in both ends of the relationship. An employee-boss relationship is one example of a self-referencing relationship. Every employee may have a boss or manager, who is also an employee. To model an employee-boss relationship, you will need to create a self-referencing relationship.

Modeling a Self-Referencing Relationship

1. Drag and drop one entity shape from the Shapes toolbar to the diagram surface.

2. Name the entity Employee.

3. Add the required attributes to the Employee entity.

4. Add one relationship shape to the diagram surface.

5. Drag and drop the head of the relationship arrow over the Employee entity.

6. Drag and drop the bottom of the relationship arrow over the Employee entity.

   

Approving the Model

After you have modeled all of the requirements and are satisfied with the model, it should be validated with users and customers. Take care to present the model to the domain experts. Domain experts are power users and business experts who intimately know how the business works, and they can be found at any level of the organization. Continue the process of model refinement until it satisfies all stakeholders.

Tip 

Validate the database model early in the development process, for this will prevent costly mistakes in the construction phase.