Mulitvalued attribute into Table schema

How to convert Multivalued attributes into Table schemas?

Multivalued attribute is a type of attribute which can have zero or more values per record.

Rule:

To convert a multivalued attribute in an ER diagram into relational schema, we need to create a separate table for multivalued attribute along with the primary key of the base table.

Example:

Let us convert the Entity set Employee given in ER Diagram of Figure 1. Entity set Employee has one multivalued attribute (represented inside double ellipse). 

Figure 1 - ER Diagram

According to the rule stated above, we have to create two relation schemas for Employee as follows;

Employee (EID, FName, LName, DoorNo, Street, City) [Refer Composite attribute conversion]
Emp_Phone (EID, Phone)

Why do we need to create separate schema for multivalued attributes?

For answering this question, consider the STUDENT relation given in Table 1 with Phone as multivalued attribute. In Phone attribute, some records have more than one phone numbers and some without phone numbers. The phone numbers are separated by comma for the records with more than one phone numbers. If we store phone numbers like Table 1, the main problem is some queries cannot be directly answered.

RegNo	SName	Gen	PR	Phone
R1	Sundar	M	BTech	9898786756, 9897786776
R3	Karthik	M	MCA
R5	Priya	F	MS	9809780967, 7898886756
R6	Ram	M	MTech	9876887909

Table 1 - STUDENT

Consider the following SQL query which tries to retrieve the student information whose phone number is 9897786776.

SELECT * FROM Student WHERE Phone = 9897786776;  --- Query 1

This query cannot be handled simply like other simple conditional queries. If we execute the query, it will not give any records as result. Instead it will show 'No records found' result. But, we have the phone number given in the query 1 stored in first record's phone number list. Hence, to display that as the result, the above query should be modified as below;

SELECT * FROM Student WHERE Phone LIKE '%9897786776%'; --- Query 2

This query includes string operations (which is considered as the costly operation if we have millions of records). It includes complexity in executing the query, which means we need to parse and extract every value present as part of the record. Some times, we may need external program routines to parse and extract values.

To avoid such a problem, we need to store this Phone column into separate table along with the RegNo attribute as given below in table 2. As a result, we will get two tables, STUDENT and STU_PHONE.

RegNo	Phone
R1	9898786756
R1	9897786776
R5	9809780967
R5	7898886756
R6	9876887909

Table 2 - Stu_Phone

RegNo	SName	Gen	PR
R1	Sundar	M	BTech
R2	Ram	M	MS
R3	Karthik	M	MCA
R4	John	M	BSc

Table 3 - STUDENT

Now, you can execute the first query easily without any string operations on Stu_Phone. (to get student information you need to perform join)

Advantages:

1. The schemas which do not have multivalued attributes (also, no composite attributes) are in 1NF.
2. As we break the table into two or more tables, it avoids redundancy.
3. We are able to run simple conditional queries without string operations. We don't need external programs to extract values.

Disadvantages:

1. Many times the queries can be written using Join operation which is unavoidable.

“No one has ever become poor by giving.” ― Anne Frank