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.
- Mapping Strong Entities
- Mapping Weak Entities
- Mapping Composite Attributes (if any..)
- Mapping Multi-Valued Attributes
- 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.
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.
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.
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.
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.
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.
This relation may include duplicate data. So both customerID and tel should act as a composite primary key.
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.
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.)
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)
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)
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..
Or by mapping Item to Orders
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.”
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…