DB2 - Logical Database Design

---

Logical database design involves three phases:

1. Requirements analysis phase

2. Data modeling phase

3. Normalization phase

Requirements Analysis Phase:

The requirements analysis phase involves examining the business being modeled, interviewing users and management to assess the current system and to analyze future needs, and determining information requirements for the business as a whole. This process is relatively straightforward.

Data Modeling Phase:

The data modeling phase involves modeling the database structure itself. This involves using a data modeling method which provides a means of visually representing various aspects of the database structure, such as the tables, table relationships, and relationship characteristics.

Some of the common data modeling methods are:

• Entity Relationship modeling (ER modeling)
• Semantic object modeling
• Object role modeling

The method which you use here is a basic version of ER modeling.

ER Modeling:

A simple ER Diagram looks as follows.

This figure represents several aspects of the database. First, it conveys the fact that there are two tables in this database, one called Agents and the other called Clients; each of the tables is represented by a rectangle. The diamond represents the fact that there is a relationship between these two tables, and the "1:N" within the diamond indicates that the relationship is a one-to-many relationship. Finally, the diagram conveys the fact that a client must be associated with an agent (indicated by the vertical line next to the AGENTS table), but an agent does not necessarily have to be associated with a client (as indicated by the circle next to the CLIENTS table).

An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database of a Logical data modeling.

Symbols used in ER Diagram:

• Box represents Entity
• Diamond represents Relationship
• Oval represents Attribute

Key Activities in ER Modeling:

Following are the key activities involved in designing Logical database using Entity-Relationship Mode:

• Define Entities
• Define Primary Key
• Define Relationships among Entities
• Define Additional Attributes for the Entities

Define Entities:

• You begin the ER Model, by defining the entities, the significant objects of interest
• Entities are the things about which you want to store information.

Define Primary Key:

• A primary key is a unique identifier for an Entity.
• If a primary key is made up of more than one attribute, then it is called as “Composite Key”.

Define Relationships among Entities:

A connection established between a pair of tables is known as a relationship. A relationship exists when a pair of tables is connected by a Primary key and a foreign key or is linked together by a third table, known as a linking table.

Relationships are very important because they help to reduce redundant data and duplicate data. They also provide the means to define views.

Every relationship can be characterized by the type of relationship that exists between the tables, the type of participation each table has within the relationship, and the degree of participation each table has within the relationship.

---

Types of Relationships (Cardinality):

When two tables are related, there is always a specific type of relationship (traditionally known as cardinality) that exists between them. There are three possible types of relationships:

• one-to-one

• one-to-many and many-to-one relationships

• many-to-many

One-to-One Relationships:

A one-to-one relationship exists between a pair of tables if a single record in the first table is related to only one record in the second table and a single record in the second table is related to only one record in the first table.

Following figure shows an example of a one-to-one relationship involving an EMPLOYEES table and a COMPENSATION table. In this example, a single record in the EMPLOYEES table is related to only one record in the COMPENSATION table; likewise, a single record in the COMPENSATION table is related to only one record in the EMPLOYEES table.

One-to-Many Relationships:

A one-to-many relationship exists between a pair of tables if a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table.

A one-to-many relationship involving a STUDENTS table and an INSTRUMENTS table is shown in the following figure. In this case, a single record in the STUDENTS table can be related to one or more records in the INSTRUMENTS table, but a single record in the INSTRUMENTS table is related to only one record in the STUDENTS table.

Many-to-One Relationships:

A many-to-one relationship exists between a pair of tables if a single record in the first table can be related to only one record in the second table, but a single record in the second table can be related to one or many records in the first table.

A many-to-one relationship involving an EMPLOYEE table and a DEPARTMENT table is shown in the following figure. In this example, a single record in the EMPLOYEE table can be related to only one record in the DEPARTMENT table and a single record in the DEPARTMENT table can be related to one or more records in the EMPLOYEE table.

Following figure represents many-to-one relationship. Here a relationship is established between two tables with the help of a linking table.

Many-to-Many Relationships:

A many-to-many relationship exists between a pair of tables if a single record in the first table can be related to one or more records in the second table, and a single record in the second table can be related to one or more records in the first table.

Following figure shows a classic many-to-many relationship. In this example, a single record in the STUDENTS table can be related to one or more records in the CLASSES table; likewise, a single record in the CLASSES table can be related to one or more records in the STUDENTS table.

Following figure represents many-to-many relationship. Here a relationship is established between two tables with the help of a linking table.

---

Types of Participation (Optionality):

There are two types of participation that a table can have within a relationship:

• Mandatory
• Optional

Say there is a relationship between two tables called TABLE A and TABLE B.

If records in TABLE A must exist before any new records can be entered into TABLE B, TABLE A's participation within the relationship is mandatory.

However, if it is not necessary for records in TABLE A to exist in order to enter any new records into TABLE B, TABLE A's participation within the relationship is optional.

Each table within a relationship can participate in either manner. The type of participation each table has within a relationship is typically determined by the way the data in each table is related and how the data is being used.

Consider the relationship between the AGENTS and CLIENTS tables in the following figure. The AGENTS table has a mandatory participation within the relationship if agents must exist before a new client can be entered into the CLIENTS table. But the AGENTS table's participation is optional if it isn't necessary to have agents in the AGENTS table before a new client can be entered into the CLIENTS table.

The type of participation established for the AGENTS table is determined by the way its data is being used in relation to the data in the CLIENTS table. For example, if it is necessary to ensure that each client is assigned an available agent, then the participation of the AGENTS table within the relationship should be mandatory.

Representation of Cardinality and Optionality:

In general the following conventions are being used for representing cardinality and optionality,

Cardinality:

• The notion of cardinality is expressed as either "one" or "many"

• A cardinality of “one” is expressed as a “straight line” and a cardinality of “many” is expressed using “crow's feet”.

Optionality:

• The notion of optionality is expressed as either "mandatory" or "optional"

• An optionality of “Optional” is expressed as a “circle” and an optionality of “Mandatory” is expressed as a “vertical bar”.

Sample ER Diagram:

Consider the example of a database that contains information on the residents of the cities. The ER Diagram contains two Entities – Person and City.

Optionality:

A person should live in a city. (That is why a bar appears adjacent to City from Person). A City can exist without a person. (That is why a circle appears adjacent to Person in the “City – Person Relationship”).

Define Additional Attributes for the Entities:

Defining the attributes for an entity includes the following activities.

• Defining attribute name.

• Defining Data Type for the attributes.

• Defining appropriate values for the attributes - what values are acceptable for the various attributes of a table.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!