Please visit, subscribe and share 10 Minutes Lectures in Computer Science

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)

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.

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

 “No one has ever become poor by giving.”