0
306views
Convert the following EER diagram to tables

Convert the following EER Diagram to tables.

enter image description here

1 Answer
2
11views

Conversion of EER Diagram to Tables

Conversion of strong entities -

  • In the given EER diagram CUSTOMER, LOAN, and ACCOUNT are strong entities and form individual tables.

Make all single-valued attributes become a column for the table -

  • CUSTOMER Table - customer-id, customer-street, and customer-city are single-valued attributes.
  • LOAN Table - loan-number and amount are single-valued attributes
  • ACCOUNT Table - account-number and balance are single-valued attributes.

The main key attribute of the entity is represented by the primary key -

  • In the given EER diagram, customer-id, loan-number, and account-number are the primary key attribute for the entities CUSTOMER, LOAN, and ACCOUNT respectively.

The multivalued attribute is represented by a separate table -

  • In the CUSTOMER table, mobileno is a multivalued attribute.
  • Multiple values can not be represented in a single column format of the CUSTOMER table.
  • Therefore, create a separate table for the multivalued attribute named as Cust_Mobileno with column names customer_id and monbileno.

Composite attribute represented by components -

  • In the given CUSTOMER table, customer-name is a composite attribute.
  • It contains fname and lname.
  • In the CUSTOMER table, these attributes can merge as individual columns.

Conversion of weak entities -

  • In the given EER diagram PAYMENT is the weak entity type with attributes payment-number, payment-date, and payment-amount.
  • Create a separate table with the same name for weak entities and include all their attributes.
  • Weak entity PAYMENT holds, payment-number as a weak primary key, and identifying relationship is loan-payment.
  • Weak entity table Include the primary key of a strong entity as a foreign key.
  • Therefore, loan-number act as a foreign key reference to the loan-number of the LOAN table.

Conversion of Relationship Set -

  • In the given EER diagram depositor is the relationship set with attribute access-date.
  • Create a separate table with the same name for the relationship set and include their attribute.
  • The relationship set depositor shows the relationship between the two entities CUSTOMER and ACCOUNT.
  • Therefore, the relationship set table depositor also Includes the primary keys of CUSTOMER and ACCOUNT as foreign keys along with attribute access-date.

Representation of ISA Hierarchy or Specialization or Generalization as a table -

  • In the given EER diagram ACCOUNT table act as a higher entity.
  • Whereas, saving-account and checking-account act as lower entities.
  • This ISA hierarchy work as an "either" clause which means the account either belongs to the saving-account type or either belongs to the checking-account type.
  • This is a specialized approach in the EER diagram.
  • To represent it as in table format create tables for both higher and lower level entities and add their local attributes in their respective tables.
  • But, must include the primary key of a higher-level entity into lower-level attributes.

Using these rules, we can convert the given EER diagram to tables and columns and assign the mapping relations between the tables. Table structure for the given EER diagram is as below:

CUSTOMER Table

Customer-id Primary Key
Fname
Lname
Customer-street
Customer-city

Cust_Mobileno Table for Multivalued attribute of mobileno of CUSTOMER Table

Customer-id Foreign key references to customer-id of CUSTOMER table.
Mobileno

Account Tabel

Account-number Primary Key
Balance

Saving-account table -

Account-number Primary key reference of higher-level entity ACCOUNT table
Interest-rate

Checking-account table -

Account-number Primary key reference of higher-level entity ACCOUNT table
Overdraft-amount

Depositor Table (Relationship Set Table)

Customer-id Foreign key references to customer-id of CUSTOMER table.
Account-number Foreign key references to account-number of ACCOUNT table.
Access-date

LOAN Table

Loan-number Primary Key
Amount

PAYMENT Table (Weak Entity Table)

Loan-number Foreign key references to loan-number of LOAN table.
Payment-number Weak Primary key
Payment-date
Payment-amount

Mapping of Relationships for a Reference

a] Foreign Key Approach

CUSTOMER Table

Loan-number CUSTOMER table makes foreign key references to loan-number of LOAN table.
Account-number CUSTOMER table makes foreign key references to account-number of ACCOUNT table.

LOAN Table

Customer-id LOAN table makes foreign key references to customer-id of CUSTOMER table.
Account-number LOAN table makes foreign key references to account-number of ACCOUNT table.

ACCOUNT Table

Loan-number ACCOUNT table makes foreign key references to loan-number of LOAN table.
Customer-id ACCOUNT table makes foreign key references to customer-id of CUSTOMER table.

B] Identifying the Relationships Approach

Many-To-One Relationship

  • A loan is associated with several (including 0) customers via the borrower, a customer is associated with at most one loan via the borrower.

One-To-One Relationship

  • A customer can deposit money to one account, the account is associated with one customer.
Please log in to add an answer.