Mapping Attributes to Relational Tables

In the previous lesson, we learned how entities are converted into relational tables and how primary keys and foreign keys help identify and connect records.

However, creating tables is only part of the database design process.

Every entity contains attributes, and not all attributes are treated the same way during mapping. Some attributes are stored directly as columns, while others must be broken into smaller parts or even moved into separate tables.

Understanding how different attributes are mapped is essential because incorrect mapping can lead to data redundancy, poor performance, and difficulty in retrieving information.

In this lesson, we will learn how to convert different types of attributes from an ER diagram into relational tables. We will also study how different types of relationships are implemented using foreign keys and additional tables.

By the end of this lesson, you will be able to convert almost any ER diagram into a properly designed relational schema.

---

Learning Objectives

After completing this lesson, you will be able to:

• Map composite attributes correctly.
• Map multivalued attributes into separate tables.
• Understand why derived attributes are usually not stored.
• Convert One-to-One relationships.
• Convert One-to-Many relationships.
• Convert Many-to-Many relationships.
• Choose the correct mapping strategy for different scenarios.
• Avoid common mistakes in relational database design.

---

Why Attribute Mapping is Important

Imagine a Student entity with the following attributes:

Student

Student_ID
Name
Address
Phone Numbers
Age

At first glance, it may seem that each attribute should become a column.

Student

Student_ID
Name
Address
Phone_Numbers
Age

Unfortunately, this design is not always correct.

Different attributes require different mapping rules.

For example:

• Address is a composite attribute and should usually be divided into smaller columns.
• Phone Numbers is a multivalued attribute and should be stored in a separate table.
• Age is a derived attribute and is usually calculated from the Date of Birth instead of being stored.

If we ignore these rules, the database becomes difficult to search, update, and maintain.

In the following sections, we will study each type of attribute individually and learn the correct mapping technique used in professional database design.

Mapping Composite Attributes

A Composite Attribute is an attribute that can be divided into two or more smaller, meaningful attributes.

Instead of storing one large piece of information, we divide it into smaller parts that are easier to store, search, update, and maintain.

A composite attribute represents a group of related attributes that together describe one property of an entity.

---

What is a Composite Attribute?

Consider the following Student entity.

Student

Student_ID
Name
Address

At first glance, the Address attribute looks perfectly fine.

However, think about what an address actually contains.

Address

House Number
Street
Area
City
State
Country
PIN Code

This means Address is not a simple attribute.

It is actually made up of several smaller attributes.

Therefore, Address is called a Composite Attribute.

---

Another Example

Suppose we have an Employee entity.

Employee

Employee_ID
Name
Salary

The Name attribute may look simple.

But in many applications, it is divided into

Name

First Name

Middle Name

Last Name

Therefore,

Name can also be a Composite Attribute.

---

Why Do We Split Composite Attributes?

Imagine an online shopping website.

Customer Address

12 Green Street,
Kochi,
Kerala,
India,
682001

Now suppose you want to answer these questions.

• Show all customers from Kerala.
• Show all customers from Kochi.
• Deliver products only to PIN Code 682001.

If the entire address is stored in one column, these searches become difficult.

Instead,

House_No

Street

City

State

Country

PIN_Code

each piece of information can be searched independently.

This improves

• Search performance
• Data organization
• Query simplicity
• Reporting

---

Incorrect Mapping

Many beginners make this mistake.

ER Diagram

Student

Address

Mapped Table

Student

Student_ID

Name

Address

Although this works,

it is not considered good database design because Address contains multiple pieces of information.

---

Correct Mapping

Instead,

Address is divided into individual columns.

Student

Student_ID

Name

House_No

Street

Area

City

State

Country

PIN_Code

This is the preferred design.

---

Visual Representation

Composite Attribute

              Address
                 ○
          /   /   |   \
 House   Street City State

During mapping,

Address

does NOT become a column.

Instead,

its child attributes become columns.

Student

House_No

Street

City

State

---

Example 1 — Student Database

ER Diagram

Student

Student_ID

Name

Address

Address contains

House No

Street

City

State

Country

Mapped Table

Student

Student_ID

Name

House_No

Street

City

State

Country

Notice that there is no Address column.

---

Example 2 — Customer Database

Customer Entity

Customer

Customer_ID

Name

Full Name

Full Name contains

First Name

Middle Name

Last Name

Mapped Table

Customer

Customer_ID

First_Name

Middle_Name

Last_Name

---

Example 3 — Employee Database

Employee

Employee

Employee_ID

Office Address

Office Address

Building

Floor

Room Number

Mapped Table

Employee

Employee_ID

Building

Floor

Room_No

---

Real-Life Banking Example

Suppose a bank stores customer addresses.

Incorrect

Address

"45 MG Road Kochi Kerala India"

Suppose the bank wants to find

Customers living in Kerala.

The database must search inside every address.

This is inefficient.

Correct

House_No

Street

City

State

Country

PIN_Code

Now the query becomes very simple.

SELECT *
FROM Customer
WHERE State='Kerala';

Proper mapping makes searching much easier.

---

Advantages of Mapping Composite Attributes

Better Searching

Each component can be searched independently.

Example

Find all students from Kozhikode.

---

Easier Sorting

Sort customers according to

• City
• State
• Country

without extra processing.

---

Better Data Validation

Each component can have different validation rules.

Example

PIN Code

must contain six digits.

State

must contain valid state names.

Country

may be selected from a dropdown.

---

Easier Updates

Suppose only the city changes.

Instead of editing the complete address,

only the City column needs updating.

---

Better Reporting

Generate reports like

Students from Kerala

Employees from Bangalore

Customers in India

without complicated string processing.

---

When Should You NOT Split an Attribute?

Not every long value is a Composite Attribute.

Example

Email

john@gmail.com

Although it contains

• Username
• Domain

we normally store it as a single attribute.

Similarly,

Phone Number

Passport Number

Roll Number

Aadhaar Number

are generally stored as single attributes.

Only split attributes when the smaller parts are useful individually.

---

Common Mistakes

Mistake 1

Keeping Address as one column.

Incorrect

Student

Address

Correct

House_No

Street

City

State

Country

---

Mistake 2

Splitting attributes unnecessarily.

Incorrect

Email

Username

Domain

Usually,

Email is stored as one attribute.

---

Mistake 3

Ignoring business requirements.

Some organizations need only

Full Name

Others require

First Name

Middle Name

Last Name

Database design should match application requirements.

---

Rule to Remember

Whenever an attribute contains multiple meaningful parts,

Split it into individual columns.

Remember

Composite Attribute

↓

Individual Columns

Never create one large column for composite information unless there is a specific business reason.

---

Quiz 1

Question 1

Which of the following is a Composite Attribute?

A. Age

B. Salary

C. Address

D. Gender

Answer

C. Address

---

Question 2

How should a Composite Attribute be mapped?

A. Ignore it

B. Store it as a relationship

C. Split it into multiple columns

D. Store it as a foreign key

Answer

C. Split it into multiple columns

---

Question 3

Which of the following is usually NOT a Composite Attribute?

A. Name

B. Address

C. Office Address

D. Email

Answer

D. Email

---

Question 4

Why is splitting a Composite Attribute useful?

A. It makes searching easier.

B. It improves reporting.

C. It simplifies updates.

D. All of the above.

Answer

D. All of the above.

---

Interview-Focused Questions