Multivalued Attribute in DBMS

In DBMS (Database Management System), a multivalued attribute is an attribute that can hold multiple values for a single entity. Unlike a single-valued attribute (which stores only one value), a multivalued attribute allows storing a set of values related to an entity.

 For example:

• A student entity may have multiple phone numbers.
• An employee entity may have multiple skills.

This makes multivalued attributes very useful in representing real-world scenarios where a single object can have more than one characteristic of the same type.

Representation in ER Diagram

• Single Valued Attribute: Shown as single oval.
• Multivalued Attribute: Shown as double oval.

Example:
STUDENT → {Name, Roll_No, {Phone_Number}}
Here, Phone_Number is a multivalued attribute represented with a double oval.

Example of Multivalued Attribute in DBMS

Example 1: Student Table

Here, Phone Numbers is a multivalued attribute, because a student may have more than one phone number.

Handle Multivalued Attributes in RDBMS

In DBMS, multivalued attributes (like multiple phone numbers for one student) violate 1NF (First Normal Form), because a column should not hold multiple values.

So, we split the multivalued attribute into a new table. In this example, we create the following “Phone Numbers” Table. The result looks as follows

Example 2: Employee Skills

Implementations of Multivalued Attributes in MYSQL

— Create and use a test database
CREATE DATABASE IF NOT EXISTS dbms_examples;
USE dbms_examples;

—————————————————–
— EXAMPLE 1: Student with Multiple Phone Numbers
—————————————————–

— Student Table
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50)
);

— Phone Numbers (Multivalued Attribute handled in separate table)
CREATE TABLE Student_Phone (
Student_ID INT,
Phone_Number VARCHAR(20),
PRIMARY KEY (Student_ID, Phone_Number),
FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID)
);

— Insert Students
INSERT INTO Student VALUES
(101, ‘Ali’),
(102, ‘Sana’);

— Insert Phone Numbers
INSERT INTO Student_Phone VALUES
(101, ‘0301-1234567’),
(101, ‘0302-9876543’),
(102, ‘0305-2223344’);

— Query to display Students with Phone Numbers
SELECT s.Student_ID, s.Name, p.Phone_Number
FROM Student s
JOIN Student_Phone p ON s.Student_ID = p.Student_ID;

—————————————————–
— EXAMPLE 2: Employee with Multiple Skills
—————————————————–

— Employee Table
CREATE TABLE Employee (
Emp_ID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50)
);

— Employee Skills (Multivalued Attribute handled in separate table)
CREATE TABLE Employee_Skills (
Emp_ID VARCHAR(10),
Skill VARCHAR(50),
PRIMARY KEY (Emp_ID, Skill),
FOREIGN KEY (Emp_ID) REFERENCES Employee(Emp_ID)
);

— Insert Employees
INSERT INTO Employee VALUES
(‘E01’, ‘Ahmed’),
(‘E02’, ‘Sara’);

— Insert Employee Skills
INSERT INTO Employee_Skills VALUES
(‘E01’, ‘Java’),
(‘E01’, ‘SQL’),
(‘E01’, ‘Python’),
(‘E02’, ‘C++’),
(‘E02’, ‘HTML’);

— Query to display Employees with Skills
SELECT e.Emp_ID, e.Name, s.Skill
FROM Employee e
JOIN Employee_Skills s ON e.Emp_ID = s.Emp_ID;

Advantages of Using Multivalued Attributes

• Helps represent real-world data accurately.
• Provides flexibility in handling entities with multiple properties.
• Improves data modeling in ER diagrams.

Disadvantages of Multivalued Attributes

• Not supported directly in relational databases.
• Can cause data redundancy if stored incorrectly.
• Requires extra effort in normalization.

Conclusion

A multivalued attribute in DBMS is an attribute that can store multiple values for a single entity. While it is useful in ER modeling, relational databases require these attributes to be decomposed into separate relations to maintain normalization.

Understanding multivalued attributes is essential for database designers, as it helps create efficient, real-world database models.