Week2 Discussion2 DBMS
Given the business rule “an employee may have many degrees,” discuss its effect on attributes, entities, and relationships. (Hint: Remember what a multivalued attribute is and how it might be implemented.)
Multivalued attributes are attributes that can have multiple values. In the given business rule it is clearly that degree (attribute) may have multiple values in it. In this scenario we may have three possibilities
1. Degrees can be stored in as single attribute named Emp_Degree in Employee table with attributes Emp_no, Emp_Name, Emp_Degree.
Employee Table
Emp_No
Emp_Name
Emp_Degree
1
Bangar Reddy Aluri
B.TECH,M.S,MPS
2
John
MBBS, MD
3
Anthony
B.A,LLB
We can identify the problem in this approach. It is now complicated to search Employee table for any particular Emp_Degree values as each employees have many number of degrees. It is not possible to write a query that will individually list degrees that are present in Emp_Degree. This approach has many conflicts with characteristics of relation table, i.e. “Each row/column intersection represents a single data value” This approach would result to use lot of complex queries which could lead to performance degrade as it consumes lot of time when used for reporting purposes, having seen the above problems it is considered as a bad design while designing a relational database management system.
2. Create several new attributes, one for each value of the multivalued attribute as shown in table below.
Employee Table
Emp_no
Emp_Name
Degree1
Degree2
Degree3
1
Bangar Reddy Aluri
B.TECH
MS
MPS
2
John
MBBS
MD
3
Anthony
BA
LLB
By looking at the above design there is a possibility of two problems, firstly the existence of blank spaces in the table that are the NULL values, while designing the database it is recommended to eliminate unnecessary null values which are existing in the above table. Second problem is the above table has only three fields to accommodate the degrees as per our business rule, here we cannot
Multivalued attributes are attributes that can have multiple values. In the given business rule it is clearly that degree (attribute) may have multiple values in it. In this scenario we may have three possibilities
1. Degrees can be stored in as single attribute named Emp_Degree in Employee table with attributes Emp_no, Emp_Name, Emp_Degree.
Employee Table
Emp_No
Emp_Name
Emp_Degree
1
Bangar Reddy Aluri
B.TECH,M.S,MPS
2
John
MBBS, MD
3
Anthony
B.A,LLB
We can identify the problem in this approach. It is now complicated to search Employee table for any particular Emp_Degree values as each employees have many number of degrees. It is not possible to write a query that will individually list degrees that are present in Emp_Degree. This approach has many conflicts with characteristics of relation table, i.e. “Each row/column intersection represents a single data value” This approach would result to use lot of complex queries which could lead to performance degrade as it consumes lot of time when used for reporting purposes, having seen the above problems it is considered as a bad design while designing a relational database management system.
2. Create several new attributes, one for each value of the multivalued attribute as shown in table below.
Employee Table
Emp_no
Emp_Name
Degree1
Degree2
Degree3
1
Bangar Reddy Aluri
B.TECH
MS
MPS
2
John
MBBS
MD
3
Anthony
BA
LLB
By looking at the above design there is a possibility of two problems, firstly the existence of blank spaces in the table that are the NULL values, while designing the database it is recommended to eliminate unnecessary null values which are existing in the above table. Second problem is the above table has only three fields to accommodate the degrees as per our business rule, here we cannot
References: 1) Database system Design implementation and management 9th edition Coronel, C., Morris, S.,& Rob, P(2011). 2) http://www.tomjewett.com/dbdesign/dbdesign.php?page=phone.php