Lets answer this step by step.
Let us assume that we have a student entity set which has which has sid,sname,phone numbers as its attributes where sid is the primary key and phone number is a multivalued attribute.
And also consider we have an entity set representing the books in library with bid,bname,author(s) as its attributes where bid is the candidate key and author(s) is a multi-valued attribute.
Step 1.Convert each entity set into tables.
Students
| Sid |
Sname |
Phone Number |
| 1 |
A |
97,98,99 |
| 2 |
A |
89,88 |
| 3 |
B |
65 |
| 4 |
C |
76 |
Books
| Bid |
Bname |
Author(s) |
| 1 |
X |
M,K,L |
| 2 |
X |
K,L |
| 3 |
Y |
M |
| |
|
|
Sid as a primary key can determine both sname and Phone Number
and same for Bid determining Bname and Author(s)
NOTE:I have represented the above table from ER Model point of view with multivalued attributes in it.In Relational model domain of every attribute is atomic
Step 2.Now we need a separate table for the multivalued attributes along with the key of the respective entity sets.
Students Phone no table
| Sid |
Phone Number |
| 1 |
97 |
| 1 |
98 |
| 1 |
99 |
| 2 |
89 |
| 2 |
88 |
| 3 |
65 |
| 4 |
76 |
Book's Author
| Bid |
Author(s) |
| 1 |
M |
| 1 |
K |
| 1 |
L |
| 2 |
K |
| 2 |
L |
| 3 |
M |
| |
|
Step 3.So we now need to show the relationship among the students and the books and see if any merging of tables is possible or not
Lets assume that student 1 takes book with book id 1
student 2 takes book with book Id 2
student 3 takes book with book Id 3 and student 4 doesnt take any book
One to one mapping with partial on student side and total on book side satisfied as per the question’s reuirement.
Lets bring the bid from the book table to the Students table
Student-Books
| Sid |
Sname |
Bid |
| 1 |
A |
1 |
| 2 |
A |
2 |
| 3 |
B |
3 |
| 4 |
C |
NULL |
Here the Bid has a NULL value which is a foreign key taken from the books table,and is unique due to the one to one mapping.So it has uniqueness constraint on it.
The Book table is left as it is.
Conclusion:
- This is not a good design as we are not able to express the total participation of books through the design
- NULL values prevail in this design as well
Lets bring the sid from the students table to the book table
Books-Students
| Bid |
Bname |
Sid |
| 1 |
X |
1 |
| 2 |
X |
2 |
| 3 |
Y |
3 |
Here the sid is a foreign key taken from the students table which is not null due to the total participation of books and is unique due to the one -one mapping .so it can be promoted to candidate key of this table.
We dont select Sid as the Primary key as it will lead to redundancy.
Selecting sid as primary key may render the table to be not in 3NF which isnt a good design.HOW?
Sid-->Bid,BName
Bid-->Bnme
U can check Bid-->Bname vioaltes the 3NF conditions
So Bid is the primary key
Conclusion:
- No NULL values
- 3NF design
Step 4.Can we merge the tables into 1 single table?
Merged
| Sid |
Sname |
Bid |
Bname |
| 1 |
A |
1 |
X |
| 2 |
A |
2 |
X |
| 3 |
B |
3 |
Y |
| 4 |
C |
NULL |
NULL |
Conclusion:
- NULL values prevail
- Not in 3NF as Bid-->Bname is violating the conditions so not a good design
So finally we have 4 tables 2 for multivalued and 2 for the one to one patial total conversion
Correct answer is 4.
