Functional Dependency - Quiz 2

RegNo	SName	Gen	PR	Phone	PManager
R1	Sundar	M	BTech	9898786756	Kumar
R2	Ram	M	MS	9897786776	Kumar
R3	Karthik	M	MCA	8798987867	Steve
R4	John	M	BSc	7898886756	Badrinath
R5	Priya	F	MS	9809780967	Kumar
R6	Ram	M	MTech	9876887909	Jagdeesh

Relational Database Design - Functional Dependency Quiz 2

1. Which of the following functional dependencies are held in the given table?

RegNo --> SName Gen PR
RegNo --> Phone
PManager SName --> RegNo
All of the above

2. {(RegNo --> SName Gen PR Phone PManager), (Phone --> RegNo SName Gen PR PManager), (PR --> PManager)}. If these are the functional dependencies of the given relation, which of the following is the Primary key?

RegNo
Phone
(RegNo Phone)
(RegNo PR)

3. Assume that (RegNo, SName), (PR, Gen), (Phone, RegNo), and (RegNo) are the super keys for a relation. Which of the following is the candidate key for this relation?

SName
RegNo
PR
Phone

4. For a table r(A, B, C, D, E), if (A, B), (A, B, E), and (C, D, E) are the candidate keys, which of the following would be the Primary key?

ABE
CDE
AB
None of the above

5. For a table ST(B, O, I, S, Q, D), if S --> D, I --> B, IS --> Q, and B --> O, then what are the keys for ST?

IS
IB
BO
SD

6. In a functional dependency with multiple attributes, which of the following is true?

More than one attribute on LHS may be essential
More than one attribute on RHS may be essential
Both are essential
None of the above

7. If A --> B, B --> C, and C --> D, then which of the following is true?

A --> C
B --> D
A --> D
All of the above

8. If A --> BC, then A --> B and _________ is Decomposition rule.

B --> C
B --> A
A --> C
All of the above

9. Union rule, Decomposition rule, and Pseudotransitivity rule can be proved using Armstrong's axioms.

TRUE
FALSE
TRUE
FALSE

10. In a relational schema R(A, B, C, D, E) with functional dependencies AC --> E, C --> D, and D --> A, which of the functional dependencies has an extraneous attribute?

AC --> E
C --> D
D --> A
None of the above

Score =

Correct answers:

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