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.