Advanced Database Management System - Tutorials and Notes: Reduction of Relationship set into relation schema

Saturday, 12 April 2014

Reduction of Relationship set into relation schema

How to convert a Relationship into relation schema?



Any relationship of an ER diagram can be converted into relations based on the type of the relationships between entity sets. We have the following relationship types;

  • One-to-one
  • One-to-many
  • Many-to-one
  • Many-to-many

Among these relationship types,
1. Only Many-to-many needs separate table to be created.
For many-to-many relationship, create a table with the relationship name with the attributes from the participating entity sets’ primary keys. That is, if we have entity sets A and B connected through a relationship AB which is many-to-many relationship, then AB will have the primary keys of A and B as attributes.
2. For other types, we need not create separate table for relationship.
For other type of relationships, just include the primary of one side as foreign key of the other side. That is, if we have entity sets A and B connected through a relationship AB which is a one-to-one or one-to-many or many-to-one, then include the one side’s primary key as the other side’s foreign key.

Let us consider the following relation schemas for discussion;
Customer (CNo, CName, Address, Phone)
Account (ACNo, Balance, BName)
Also, assume that Borrower is the relationship between Customer and Account. Then, we may have the following cases;
Case 1: Assume that a customer can have one or more accounts and an account can be held by one or more customers (a joint account). This type of relationship is Many-to-Many. For this case, according to the rules, we need separate table for the relationship. The table will consist of the primary keys of the relations Customer and Account. Thus, we have,
Customer (CNo, CName, Address, Phone)

Account (ACNo, Balance, BName)

Borrower (CNo, ACNo)
Case 2: Assume that a customer can have only one account and an account can be held by one or more customers (a joint account). This type of relationship is Many-to-One. For this case, the rule is to include the primary key of one side into the other side. Here, one side is Account and many side is Customer. Then we have to include the primary key of Account into the schema Customer as foreign key. Thus, we have,
Customer (CNo, CName, Address, Phone, ACNo)

Account (ACNo, Balance, BName)
Case 3: Assume that a customer can have many accounts and an account can be held by only one customer. This type of relationship is One-to-Many. Here, we need to insert the primary key of one side, that is, Customer relation into the many side, that is, Account as follows;
Customer (CNo, CName, Address, Phone)

Account (ACNo, Balance, BName, CNo)
Case 4: Assume that a customer can have only one account and an account can be held by utmost one customer. This type of relationship is One-to-One. In this case, we need to insert the primary key of one side into the schema of other side. As we have both side as one sides, then we have the following different possible designations, where both are correct.
Customer (CNo, CName, Address, Phone, ACNo)

Account (ACNo, Balance, BName)
OR
Customer (CNo, CName, Address, Phone)

Account (ACNo, Balance, BName, CNo)
Note: The foreign keys are mentioned in bold letters.

What would be the problem if we create separate tables for all the relationship types other than Many-to-Many?


The result will be Redundant.For example, let us consider case 2, where many-to-one relationship is discussed. After converting into relation schema, we have the following relation schemas.

Customer (CNo, CName, Address, Phone, ACNo)

Account (ACNo, Balance, BName)
Suppose, if we need to create separate table for relationship set, then our relation schemas will look like follows;
Customer (CNo, CName, Address, Phone, ACNo)

Account (ACNo, Balance, BName)

Depositor (CNo, ACNo)
From this, it is clearly visible that both Customer and Depositor have the attributes (CNo, ACNo). Hence it is said to be redundant.

Why do we need to create separate table for many-to-many relationship?


If we include the primary key of one entity set as foreign of other entity set like in the case of one-to-one, one-to-many, and many-to-one, then we would have the following set of relation schemas for case 1.
Customer (CNo, CName, Address, Phone)

Account (ACNo, Balance, BName, CNo)

OR

Customer (CNo, CName, Address, Phone, ACNo)

Account (ACNo, Balance, BName)
Let us explain the problem with the following sample data;

CNo
CName
Address
Phone
C101
Kumar
Chennai
9988554422
C102
Steve
Mumbai
9758632512
C103
Arun
Delhi
8956234512
C104
Karthik
Delhi
7898654523
Figure 1 – Customer table

ACNo
Balance
BName
A101
5000
TNagar
A102
14000
Tambaram
A103
13000
Bandra
A104
30000
Chandni chowk
Figure 2 – Account table

Suppose we have many-to-many relationship between Customer and Account, i.e, one customer can have one or more accounts and one account can be held by more than one customer jointly. Assume that, customer ‘C101’ have accounts ‘A101’ and ‘A102’, customer ‘C102’ have account ‘A103’, and customer ‘C103’ and ‘C104’ jointly have account ‘A104’.
A) Let us include the ACNo as foreign key in Customer relation. Observe the Customer table after conversion.
CNo
CName
Address
Phone
ACNo
C101
Kumar
Chennai
9988554422
A101
C101
Kumar
Chennai
9988554422
A102
C102
Steve
Mumbai
9758632512
A103
C103
Arun
Delhi
8956234512
A104
C104
Karthik
Delhi
7898654523
A104
Figure 3 – Customer table after including the ACNo as foreign key

In figure 3, the customer details for ‘C101’ are repeated for 2 accounts ‘A101’, and ‘A102’.
B) Let us include the CNo as foreign key in Account relation. Observe the Account table after conversion.

ACNo
Balance
BName
CNo
A101
5000
TNagar
C101
A102
14000
Tambaram
C101
A103
13000
Bandra
C102
A104
30000
Chandni chowk
C103
A104
30000
Chandni chowk
C104
Figure 4 – Account table after including the CNo as foreign key

In figure 4, the account details for account ‘A104’ is repeated for customers ‘C103’, and ‘C104’.
In both A and B, it shows redundancy in the data stored.
C) Let us consider separate table for many-to-many relationship as given in the tables below;

CNo
CName
Address
Phone
C101
Kumar
Chennai
9988554422
C102
Steve
Mumbai
9758632512
C103
Arun
Delhi
8956234512
C104
Karthik
Delhi
7898654523
Figure 1 – Customer table

ACNo
Balance
BName
A101
5000
TNagar
A102
14000
Tambaram
A103
13000
Bandra
A104
30000
Chandni chowk
Figure 2 – Account table

CNo
ACNo
C101
A101
C101
A102
C102
A103
C103
A104
C104
A104
Figure 5 – Relationship table Depositor
Figure 5 shows minimal redundancy which cannot be avoided.


No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents