ERD Mapping

As all of you have felt, drawing the ERD is the most difficult case, but we can’t forget ERD Mapping. Because it is the logical representation of the database as we learned as Conceptual Level.

So from this tutorial, I will explain how we can convert an ERD into a implementable schema called a set of Relations.

We have to do mapping in few steps.

1. Mapping Strong Entities
2. Mapping Weak Entities
3. Mapping Composite Attributes (if any..)
4. Mapping Multi-Valued Attributes
5. Mapping Relationships

So let’s begin.

 

Lets take the example from my first post on ERD.

Figure 1 – ERD

I will take this as a model to explain.

 

1.Mapping Strong Entities.

When mapping strong entities, it’s not a big deal. We just write the attributes except derived attributes and multi-valued attributes.
When considering the above example, the mapping of the entities would be like this.

Customer (customerID, customerName, address, tel)
Category (categoryCode, name)

Though Entities are defined as independent datasets in a system. But here you can see, Customer and Category are the only stand-alone entities. Item has a connection to Category. That meanswe have to have a way to find out the category of any item. Which eventually leads us to include categoryID in items table. In this case, this is okay. But sometimes it won’t be. In the other entities also, we might be able to guess what it should be. So we will talk about this in Mapping Relationships.

So for now, we will just put only the attributes of its own for those entities.
Like this…

Item (itemCode, name, description, category, unit, unitPrice, qtyOnHand, reOrderLevel)

Order (orderID, customerID, date)
  Amount is ignored as it is a derived attribute and no need to store it.

Primary keys should be underlined same as in ERDs.

2.Mapping Weak Entities

Weak entities are defined as the entities which depend on another entity. Which means the existence of a weak entity totally depends on the existence of it’s owner. So to find out who is the owner of each dependent, it is needed to keep track of its owner and we need to insert a new attribute,a foreign key containing owners primary key, on the dependent entity.
Here is the mapping of Invoice, which is a weak entity depends on Order entity.

Note : A Foreign Key is a primary key of an Entity which wants to be included in another Entity.

Invoice (invoiceNo, orderNo, deliveryDate)
( orderNo is a foreign key from Orders. )

 

3.Mapping Composite attributes

 

When there is a composite attribute, we split it to its parts and include it in the attribute list.

Example :

Figure 2 – Composite Attribute

To map this, we use ONLY the sub attributes of the composite attribute.

so the mapping would include no, street, city as attributes.

 

4.Mapping Multi-Valued Attributes

As we know, an attribute is a single valued property of an entity. So it only can hold atomic values for a single instance. See the following example.

Suppose customers can have more than one telephone number. So someone may insert data like this.

Customer	Name	…….	Tel
C-0001	Mr.De Silva	…….	0778459621,0772156784
C-0002	Mr.Perera	…….	0755768471, 0112244665

Though this seems possible, there will be a disaster when searching and filtering data. So to avoid that we might have an idea about insert data as following.

customerID	Name	…….	Tel
C-0001	Mr.De Silva	…….	0778459621
C-0001	Mr.De Silva	…….	0772156784
C-0002	Mr.Perera	…….	0755768471
C-0002	Mr.Perera	…….	0112244665

This would be a mess and it wastes so much space to store this database because of the Data Redundancy.

So the best solution is to create another table to store only the telephone numbers for each customer as below.

customerID	Tel
C-0001	0778459621
C-0001	0772156784
C-0002	0755768471
C-0002	0112244665

This won’t remove or completely avoid data repetition, but it will surely be reduced.

So, if we have a multi-valued attribute to be mapped, we just can create a new relation and it would only contain a foreign key of the entity (in here, CustomerID), and the attribute value.

So the mapping will include a relation like this.

Tel(customerID, tel)

This relation may include duplicate data. So both customerID and tel should act as a composite primary key.

 

5.Mapping Relationships

The hardest part in mapping and should be carefully done. The mapping differs according to the degree of the relationship and will be discussed separately.

Unary Relationships

This means an Entity has a relationship with itself. The mapping is done by just putting a new attribute on the attribute list.

For example Employee is supervised by another Employee, a person we call a Supervisor. This can be mapped by inserting an attribute, let’s say supervisor to the attribute list of the Employee as following.

Employee (empID, name, address, tel,… supervisor)

(supervisor holds an empID itself.)

 

Binary Relationship

1. 1:1 Relationships

   This is easy to be mapped. When two entities are having a 1-1 relationship, a foreign key of an entity to one of the attribute lists of entities. But we have to decide, to which entity’s attribute list we are going put the foreign key of the other’s.
   For example, let’s consider following piece of ERD.

   

   This is said as “A Department is managed by only one employee and if an employee is a Manager, he only can manage one Department”. When mapping this, we have to consider also about the participation of each Entity to the relationship. Let’s suppose we are going to insert depNo to Employee. But it will leave a null to employees who are not managing a Department.

   empID	name	….	depNo
   E-0001	Mr.De Costta	….	D-0001
   E-0002	Mr.S.Perera	….	null

   This is not an appropriate way to map this relationship as employee has a total participation. So we have to insert empID into the attribute list of Department as it has a partial participation. it won’t leave any null values as every Department MUST have a manager.

   depNo	name	….	empID
   D-0001	Sales	….	E-0001
   D-0002	HR	….	E-0002

   This is mapped as follows.

   Department (depNo, name, empID)

2. 1:M Relationships

   Mapping a 1:M is rather easy than 1:1. Because we only have to consider the degree, no need to think about the participation constraints. We just map the primary key of the 1 side entity, into  many side entity.
   Consider the 1:M Relationship between Item and Category in the ERD shown in Figure 1.

   

   Someone may map Category like this.

   categoryCode	name	….	itemCode
   CT-0001	Soap	….	I-0001
   CT-0001	Soap	….	I-0002
   CT-0002	Dhal	….	I-0003

   We cannot map ItemCode into Category, because that occur lots of Data Redundancy as a Category may contain thousands of Items in it. So this is not the way to map this relationship.
   So we have to map categoryCode into Item as below.

   itemCode	name	….	categoryCode
   I-0001	Soap	….	CT-0001
   I-0002	Soap	….	CT-0001
   I-0002	Dhal	….	CT-0002

   So this can be mapped like this.

   Item (itemCode, name, description, categoryCode, unit, unitPrice, qtyOnHand, reOrderLevel)

3. M:N Relationships

This is so much easy to be mapped comparing with others. We have seen in above mapping examples, if we mapped in a wrong way, we map in the wrong direction, we will end up with a mess. But in here both Entities can have the same instance for different instances of another Entity and vice versa. So we just can’t put any of the primary keys to another Entity.
Consider the M;N relationship between Items and Orders in the ERD shown in Figure 1.

According to this, an Order can have any amount of Items in it and an Item can be included in any amount of Orders.

We cannot map this relationship in this way by mapping Orders into Item..

itemCode	name	description	….	orderID
I-0001	Sunlight	Washing Soap	….	O-0001
I-0002	Vim	Washing Soap	….	O-0001
I-0001	Sunlight	Washing Soap	….	O-0002
I-0003	Potatoes	Yam	….	O-0002

Or by mapping Item to Orders

orderID	date	….	itemCode
O-0001	2011-08-24	….	I-0001
O-0001	2011-08-24	….	I-0002
O-0002	2011-08-27	….	I-0001
O-0002	2011-08-27	….	I-0003

There is only one way to map this, and it’s by creating a new Relation only including primary keys of both Entities. An it would be like this.

Note : ”If there are any Relationship Attributes, they also are mapped into this new Relation. Because mostly Relationship Attributes are visible in many to many relationships.”

orderID	date	….	itemCode
O-0001	2011-08-24	….	I-0001
O-0001	2011-08-24	….	I-0002
O-0002	2011-08-27	….	I-0001
O-0002	2011-08-27	….	I-0003

And the mapping would be like this.
orderItems (orderID, itemCode, qty)

So the mapping of the ERD on Figure 1 is as following.

• Customer (customerID, customerName, address, tel)
• Category (categoryCode, name)
• Item (itemCode, name, description, categoryCode, unit, unitPrice, qtyOnHand, reOrderLevel)
• Order (orderID, customerID, date)
• Invoice (invoiceNo, orderNo, deliveryDate)
• OrderItems (orderID, itemCode, qty)

 

Mapping EERD will be soon…