Monday, March 24, 2014

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

No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery