Reduce ER diagram to relation schemas exercise 8

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)

Go to Solved ER exercises page