Collection Contents Index Database design concepts Designing the database table properties pdf/chap13.pdf

User's Guide
   PART 2. Relational Database Concepts
     CHAPTER 13. Designing Your Database       

The design process

There are five major steps in the design process.

For Info     For information about implementing the database design, see Working with Database Objects.

Top of page  Step 1: Identify entities and relationships

  To identify the entities in your design and their relationship to each other:
  1. Define high-level activities     Identify the general activities for which you will use this database. For example, you may want to keep track of information about employees.

  2. Identify entities     For the list of activities, identify the subject areas you need to maintain information about. These subjects will become entities. For example, hire employees, assign to a department, and determine a skill level.

  3. Identify relationships     Look at the activities and determine what the relationships will be between the entities. For example, there is a relationship between parts and warehouses. Define two roles to describe each relationship.

  4. Break down the activities     You started out with high-level activities. Now, examine these activities more carefully to see if some of them can be broken down into lower-level activities. For example, a high-level activity such as maintain employee information can be broken down into:

  5. Identify business rules     Look at your business description and see what rules you follow. For example, one business rule might be that a department has one and only one department head. These rules will be built into the structure of the database.

Top of page  Entity and relationship example


ACME Corporation is a small company with offices in five locations. Currently, 75 employees work for ACME. The company is preparing for rapid growth and has identified nine departments, each with its own department head.

To help in its search for new employees, the personnel department has identified 68 skills that it believes the company will need in its future employee base. When an employee is hired, the employee's level of expertise for each skill is identified.

Define high-level activities 

Some of the high-level activities for ACME Corporation are:

Identify the entities and relationships 

Identify the entities (subjects) and the relationships (roles) that connect them. Create a diagram based on the description and high-level activities.

Use boxes to show entities and lines to show relationships. Use the two roles to label each relationship. You should also identify those relationships that are one-to-many, one-to-one, and many-to-many using the appropriate annotation.

Below, is a rough entity-relationship diagram. It will be refined throughout the chapter.

Break down the high-level activities 

The following lower-level activities below are based on the high-level activities listed above:

These lower-level activities can be used to identify if any new tables or relationships are needed.

Identify business rules 

Business rules often identify one-to-many, one-to-one, and many-to-many relationships.

The kind of business rules that may be relevant include the following:

Top of page  Step 2: Identify the required data

  To identify the required data:
  1. Identify supporting data.

  2. List all the data you need to track.

  3. Set up data for each entity.

  4. List the available data for each entity. The data that describes an entity (subject) answers the questions who, what, where, when, and why.

  5. List any data required for each relationship (verb).

  6. List the data, if any, that applies to each relationship.

Identify supporting data 

The supporting data you identify will become the names of the attributes of the entity. For example, the data below might apply to the Employee entity, the Skill entity, and the Expert In relationship.



Expert In

Employee ID

Skill ID

Skill level

Employee first name

Skill name

Date skill was acquired

Employee last name

Description of skill

Employee department

Employee office

Employee address

If you make a diagram of this data, it will look something like this picture:

Observe that not all of the attributes you listed appear in this diagram. The missing items fall into two categories:

  1. Some are contained implicitly in other relationships; for example, Employee department and Employee office are denoted by the relations to the Department and Office entities, respectively.

  2. Others are not present because they are associated not with either of these entities, but rather the relationship between them. The above diagram is inadequate.

The first category of items will fall naturally into place when you draw the entire entity-relationship diagram.

You can add the second category by converting this many-to-many relationship into an entity.

The new entity depends on both the Employee and the Skill entities. It borrows its identifiers from these entities because it depends on both of them.

Things to remember 

Top of page  Step 3: Normalize the data

Normalization is a series of tests that eliminate redundancy in the data and make sure the data is associated with the correct entity or relationship. There are five tests. This section presents the first three of them. These three tests are the most important and so the most frequently used.

Why normalize?    
The goals of normalization are to remove redundancy and to improve consistency. For example, if you store a customer's address in multiple locations, it is difficult to update all copies correctly should he move.

For Info     For more information about the normalization tests, see a book on database design.

Normal forms 

There are several tests for data normalization. When your data passes the first test, it is considered to be in first normal form. When it passes the second test, it is in second normal form, and when it passes the third test, it is in third normal form.

  To normalize data in a database:
  1. List the data.

  2. Put data in first normal form.

  3. Put data in second normal form.

  4. Put data in third normal form.

Data and identifiers 

Before you begin to normalize (test your design), simply list the data and identify a unique identifier each table. The identifier can be made up of one piece of data (attribute) or several (a compound identifier).

The identifier is the set of attributes that uniquely identifies each row in an entity. The identifier for the Employee entity is the Employee ID attribute. The identifier for the Works In relationship consists of the Office Code and Employee ID attributes. You can make an identifier for each relationship in your database by taking the identifiers from each of the entities that it connects. In the following table, the attributes identified with an asterisk are the identifiers for the entity or relationship.

Entity or Relationship



*Office code
Office address
Phone number

Works in

*Office code
*Employee ID


*Department ID
Department name


*Department ID
*Employee ID

Member of

*Department ID
*Employee ID


*Skill ID
Skill name
Skill description

Expert in

*Skill ID
*Employee ID
Skill level
Date acquired


*Employee ID
last name
first name
Social security number
phone number
date of birth

Putting data in first normal form 

In the entity below, Phone number can repeat—an office can have more than one telephone number.

Remove the repeating attribute and make a new entity called Telephone. Set up a relationship between Office and Telephone.

Putting data in second normal form 

In this example, the identifier of the Employee and Department entity is composed of two attributes. Some of the data does not depend on both identifier attributes; for example, the department name depends on only one of those attributes, Department ID, and Employee first name depends only on Employee ID.

Move the identifier Department ID, which the other employee data does not depend on, to a entity of its own called Department. Also move any attributes that depend on it. Create a relationship between Employee and Department.

Putting data in third normal form 

In this example, the Employee and Office entity contains some attributes that depend on its identifier, Employee ID. However, attributes such as Office location and Office phone depend on another attribute, Office code. They do not depend directly on the identifier, Employee ID.

Remove Office code and those attributes that depend on it. Make another entity called Office. Then, create a relationship that connects Employee with Office.

Top of page  Step 4: Resolve the relationships

When you finish the normalization process, your design is almost complete. All you need to do is to generate the physical data model that corresponds to your conceptual data model. This process is also known as resolving the relationships, because a large portion of the task involves converting the relationships in the conceptual model into the corresponding tables and foreign-key relationships.

Whereas the conceptual model is largely independent of implementation details, the physical data model is tightly bound to the table structure and options available in a particular database application. In this case, that application is Adaptive Server Anywhere.

Resolving relationships that do not carry data 

In order to implement relationships that do not carry data, you define foreign keys. A foreign key is a column or set of columns that contains primary key values from another table. The foreign key allows you to access data from more than one table at one time.

A database design tool such as the DataArchitect component of Powersoft PowerDesigner can generate the physical data model for you. However, if you're doing it yourself there are some basic rules that help you decide where to put the keys.

Resolving relationships that carry data 

Some of your relationships may carry data. This situation often occurs in many-to-many relationships.

If this is the case, each entity resolves to a table. Each role becomes a foreign key that points to another table.

The Inventory entity borrows its identifiers from the Parts and Warehouse tables, because it depends on both of them. Once resolved, these borrowed identifiers form the primary key of the Inventory table.

A conceptual data model simplifies the design process because it hides a lot of details. For example, a many-to-many relationship always generates an extra table and two foreign key references. In a conceptual data model, you can usually denote all of this structure with a single connection.

Top of page  Step 5: Verify the design

Before you implement your design, you need to make sure that it supports your needs. Examine the activities you identified at the start of the design process and make sure you can access all of the data that the activities require.

If you can answer yes to all the questions above, you are ready to implement your design.

Final design 

Applying steps 1 through 3 to the database for the little company produces the following entity-relationship diagram. This database is now in third normal form.

The corresponding physical data model appears below.

Top of page  

Collection Contents Index Database design concepts Designing the database table properties pdf/chap13.pdf