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