Reduction of an ER diagram to tables, ER diagram to table conversion exercise, mapping er model to relational model, convert er diagram to relational table, how to map er model to relational model, entity relationship diagram to relational tables
ER diagram to Relational schema - Solved Exercise
Question: Convert the following ER diagram to set of relational schemas.
Solution:
Strong entity sets
At first, let us identify and reduce
all the strong entity sets (those that have primary keys) into relational
schemas.
In the given diagram, ATM, Bank,
Transaction, Customer, Branch, and Account are strong entity sets. To do this,
we can use the name of the entity sets as schema names and all the attributes
as part of the schema. In this way, we have the following schemas;
ATM (ATM_id, cash_limit,
location)
Bank (B_id, B_name, B_add)
Branch (Br_id, Br_name, Br_add)
Transaction (Tr_id, Tr_type)
Customer (Cust_id, Cust_name, Cust_add,
Ph_no)
Account (Acc_no, Acc_type, Balance)
Primary
keys are underlined
Composite attributes
In entity sets Branch and Customer, the
attributes Br_add and Cust_add respectively are composite
attributes. To reduce the composite attributes we retain the component
attributes in the schema. That is, instead of Br_add, we shall include the
component attributes state, country and pin the Branch table as follows;
Branch (Br_id, Br_name, State,
Country, Pin)
Likewise, Customer becomes;
Customer (Cust_id, Cust_name,
State, Country, Pin, Ph_no)
Multi-valued attributes
Ph_no attribute
of Customer entity set is a multi-valued
attribute. That is, it can have one or more values in it per record (row). To reduce
the multi_valued attribute, we need to create a separate table with a new name with
the multi_valued attribute as one attribute along with the primary key of the
base entity set as follows;
Customer_Phone (Cust_id, Ph_no)
The primary key for this relation is both
(cust_id, ph_no).
Relationship sets
We have the following type of
relationships in the given ER diagram;
Belongs –
a one-to-many relationship from Bank
to ATM. One bank can have many ATMs (and an ATM can belong to at most one Bank).
Has –
a one-to-many relationship from Bank
to Branch. One bank can have many branches (and a branch can belong to only
one bank).
Operates –
a many-to-many relationship from ATM
to Customer. A customer can operate many ATMs and an ATM can be operated by
many customers.
Performs –
a many-to-one relationship from
Transaction to Customer. A transaction can be performed by only one customer (and a
customer can perform any number of transactions).
Holds –
a many-to-many relationship from
Customer to Account. A customer can have any number of accounts and an account
can be maintained by more than one customer as joint accounts.
-
For a many-to-many relationship, we need to
create a separate schema by including the primary keys of participating entity
sets as attributes.
-
For all others, the primary key of one side
has to be included as the foreign key of the other side (no need to create a
separate table).
Hence, we have the following changes;
- Operates (ATM_id, Cust_id) - many-to-many
- Holds (Cust_id, Acc_no) – many-to-many
- For Belongs, include B_id (primary key
of Bank, one side) in ATM entity set (many side).
- ATM (ATM_id, cash_limit,
location, B_id)
- For Has, include B_id (primary key of
Bank, one side) in Branch entity set (many side).
- Branch (Br_id, Br_name, State,
Country, Pin, B_id)
- For Performs, include Cust_id (primary
key of Customer, one side) in Transaction entity set (many side).
- Transaction (Tr_id, Tr_type,
Cust_id).
At the end, after reduction and
modification, we have the following set of relation schemas for the given ER
diagram;
Bank (B_id, B_name, B_add)
Customer (Cust_id, Cust_name,
State, Country, Pin)
Customer_Phone (Cust_id, Ph_no)
Account (Acc_no, Acc_type, Balance)
ATM (ATM_id, cash_limit,
location, B_id)
Branch (Br_id, Br_name, State,
Country, Pin, B_id)
Transaction (Tr_id, Tr_type,
Cust_id)
Operates (ATM_id, Cust_id)
Holds (Cust_id, Acc_no)
