Articles

Entity Relationship Diagram (ERD) — Part-II

In Database Systems on June 16, 2012 by Muhammad Haadi Tagged: , , , , , , , , ,

Cardinality of a Relationship

Cardinality is the mapping of entities i-e zero, one or many. It basically explains how a table is linked to another table. It can be particularized more as the number of distinct values of a table connected to how many values of the other table – both minimum and maximum.

NOTE: Some authors explain optionality as a separate concept which is zero or one in this case.

Following are the two possible classifications of relations among entities.

  • One to One
  • One to Many

There is also a third case of (Many to Many) relationship but I consider it separate from the two basic classifications as it is basically resolved with the help of the same technique used in resolving (one to many) relationship. We will see that in detail later.

We will first discuss the One-to-Many and Many-to-Many  scenario as the example I am going to use is the One-to-One relation would need an understanding of one-to-many relation.

One to Many Relationship

One to many relationship is the most commonly used relation among entities and is the easiest to resolve. It has one simple rule that the key is sent from the one instance table to the many instances table. Follow this and you’ll never be mistaken.

 Image

Example of one-to-many relation is like houses in a street. One street has many houses but one house is in one street only. Another example is the faculty member of an institute, who belongs to one department only but a department has many faculty members.

 Image

Many to Many Relationship

After grasping the concept of one-to-many relationship, many-to-many is just a piece of cake. It always gets resolved by breaking the relation into two one-to-many relations and a junction table and then the rule for one-to-many is applied.

Many-to-many can be understood as the relation between people and the address they live at. A person can have more than one addresses and one address can be of more than one person. Students living at a hostel are the best example as they all share the common address of hostel where as their house addresses are different.

Image

Image

The relation between these two entities is broken into the following three tables to resolve the scenario.

Image

A room may be given to more than one guests and one guest can be accommodated in several rooms. Here a junction table is added to facilitate both the tables and the foreign keys. The junction table has many-to-one relation with the guest and room tables.

Note that junction table is created because of the many-to-many relation between the two entities.

One to One Relationship

One to one relationship maps one instance of an entity to just a single instance of another entity. The rule for one-to-one relation is apparently simple, i-e the key of any entity can be sent to the other (linked) entity.

It seems quite simple but the problem is to identify which key has to be sent to the other entity.

It can be understood as there is only one Identity Card number which identifies an individual; i-e a person has only one identity card number and one identity card number identifies just one person.

Image

Image

Here we have a scenario. There are two tables of Employee and Employee_Login. Every employee has exactly one login to log on to the system and each login relates to one and one employee only. Now the question arises that either the Employee table key is sent to the Employee_Login or vice versa? By rule, we can implement both scenarios but in order to keep the whole ERD connected, we need to see which table is connected with the rest of the ERD; e.g. in this case, it is quite obvious that the other tables of this ERD would be directly connected with the Employee table not with Employee_Login so if we make a primary key of Employee and refer it to Employee_Login as foreign key (called Unique Key in 1-1 relation), then it would not be possible for the Employee table to access the Employee_Login table. This is a bit tricky but you’ll have a clearer picture of the scenario when you have to implement one.

I’ll portray another scenario with no table names at all and that would serve as a test for you to check your understanding of the concepts. If you are unable to grasp the idea, just give it 2-3 more tries.

Image

Now, if you see the above illustration carefully, starting from the bottom left table, it is easy to navigate from table 1 to table 2 and similarly from table 2 to table 3. But as table 3 has a one to one relation with table 4, so if you need to make a relationship between the two, you have to be very vigilant not to block the access of the remaining tables by sending the wrong reference; although you can send that by rule.

The question is whether to send the key from table 3 to table 4 or from table 4 to table 3?

I know it is a bit confusing but you should give it a try and then I’ll tell you the answer.

The foreign key reference would be sent from table 4 to table 3. Now, keeping all questions and opinions aside, let us just try and figure out why is it so. The reason for this is simple; if you send the key from table 3 to table 4, table 3 would have no reference in itself about any key (i-e it has no reference that it has a relationship with any other table). However, if the reference is sent from table 4 to table 3, it would be easily accessible.

P.S. the above example may not be clearly understandable to everyone, especially the concept of navigating the tables. It is more oriented to the implementation side. If the idea is confusing for you, then just remember the rule for foreign key in one-to-one relationship.

Advertisements

2 Responses to “Entity Relationship Diagram (ERD) — Part-II”

  1. I think there is an error with your many to many relationship. Room_No is to be the foreign key in Booking and not End_Date

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: