Minimum number of tables in ER model to 1NF Conversion

Question

What would be the minimum number of tables for the given ER-Diagram such that the relational database satisfies 1NF?
The question has already been discussed in the link  here but its concept is contradicting the concept explained here . So i am confused which one is right.

Comments

@Shaik Masthan  @srestha

Answer 1

For Multi valued attribute in Bank entity we have to make another table with schema as

1.X(Code,Phone_no)--Code,Phone_no combined Primary key

2.Bank(Code, Name,Address) --- Code is Primary key

3.Bank Branch(Address,Branch_No,Bank Name,Code) ---- Branch_No is Primary key and Code is a FK referring to Bank entity Primary key.

4.Loan(Loan_No,Amount,Type, Branch_No)---Loan_No as Primary key, Branch_No as FK referring to Loan entity Primary key.

Comments

If table is in 1NF, we need not to built a separate table for multivalued attribute.

please refer to Navathe 6th edition, page 521, point no. 2 and 3.

---

I made it different table which is mentioned X

---

YEAH . You are ryt but there is given three ways how we can achieve first normal form .

You have to check option one . IN option One it is clearly said that we can take a seperate table for multivalued  attribute .

Now if we refer to korth it is clearly metioned that "MUTIVALUED IS AN EXCEPTIONAL CASE WE HAVE TO TAKE A SEPERATE TABLE FOR THESE ATTRIBUTES"

---

But the question asks for MINIMUM no. tables, so we have to obviously consider the case where we are taking the multivalued attribute in the relation's table itself  & take the original primary key + multivalued attribute as the new primary of the resulting table.

Again this is my point of view, It was bit ambiguous to me at first thats why i have asked it at first place.

---

@Nirmal Gaur

You can store it in 1 table with primary key(say P) + MVA(multi valued attribute) as the new primary key is MVA is having the NOT NULL constraint.

But then you will be replicating the every attribute in the tuple except for the MVA multiple times which will lead to redundancy of data but that is okay in 1NF.

This redundancy will be removed in 2NF because except for the MVA every other non key element is uniquely dependent on the initial primary key(P) which is now a proper subset of the current primary key(P + MVA). And hence that dependency will be removed.

So its not necessary to take MVA to new table if it is having NOT NULL constraint.