Convert the following EER Diagram to tables.

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

Cust_Mobileno Table for Multivalued attribute of mobileno of CUSTOMER Table

Account Tabel

Saving-account table -

Checking-account table -

Depositor Table (Relationship Set Table)

LOAN Table

PAYMENT Table (Weak Entity Table)

Mapping of Relationships for a Reference

a] Foreign Key Approach

CUSTOMER Table

LOAN Table

ACCOUNT 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.