Database: Does each multivalued attribute requires a separate table in 1NF?

Question

Following question and answer from GateForum test series:

Now see the following text i coped from Korth, we will create an new for each value of multivalued attribute:

Hence answer should be 1 for this question.

Comments

@manu

primary key will be present after decomposing it into one table only ??

---

@Nitish yes, as utkarsh has shown below, customer id and phone number will combinely work as PK.

---

@manu

one more thing,

what if some customers doesn't have a 'phone number' at all, than there will be NULL in phn number field, and according to entity integrity constraint, no prime attribute(here Phn number is prime attribute) should be NULL..

---

I was having the same doubt for a long time, i also thought that we can do this by creating one table only. But i think @joshi_nitish makes sense, it is not necessary that multivalued attribute must have a value it can be null also and when we create composite key of multivalued attribute and primary key of the table, then it will not allow null value in the multivalued attribute. So we should make another table for multivalued attribute.

Answer 1

Contact hobbies

contactid	firstname	lastname	hobbies
1639	George	Barnes	reading
5629	Susan	Noble	hiking, movies
3388	Erwin	Star	hockey, skiing
5772	Alice	Buck
1911	Frank	Borders	photography, travel, art
4848	Hanna	Diedrich	gourmet cooking

In the above table, hobbies are the multivalued attributes.The problem with doing it is that it is now difficult (but possible) to search the table for any particular hobby that a person might have. Multivalued attributes need a separate table so in my example we have only one Multivalued attribute(hobbies), But in your example there will be 5 tables which will be T1(A,B,C), T2(A,D), T3(A,E), T4(A,F), T5(A,G).

Hobbies

contactid	hobby
1639	reading
5629	hiking
5629	movies
3388	hockey
3388	skiing
1911	photography
1911	travel
1911	art
4848	gourmet cooking

Comments

it doesn't make any difference! being a relation into 1NF, there shouldn't be any multivalued attribute! and we can create a separate row for each of the value. Same has been shown in the book also.

hence we need only 1 table, and our relation will remain in 1NF.

---

There will be a lot of redundancy. If we apply your logic in my example you can see that there is contactid, firstname and lastname. But your point is correct. I checked on wiki also there can be 2 designs one is yours in a single table and other with creating a separate table. This question contains 2 answers.

contactid	firstname	lastname	hobbies
1639	George	Barnes	reading
5629	Susan	Noble	hiking
5629	Susan	Noble	movies

---

Wiki

Designs that Comply with 1NF[edit]

To bring the model into the first normal form, we split the strings we used to hold our telephone number information into "atomic" (i.e. indivisible) entities: single phone numbers. And we ensure no row contains more than one phone number.

Customer

Customer ID	First Name	Surname	Telephone Number
123	Pooja	Singh	555-861-2025
123	Pooja	Singh	192-122-1111
456	San	Zhang	182-929-2929
456	San	Zhang	(555) 403-1659 Ext. 53
789	John	Doe	555-808-9633

Note that the "ID" is no longer unique in this solution with duplicated customers. To uniquely identify a row, we need to use a combination of (ID, Telephone Number). The value of the combination is unique although each column separately contains repeated values. Being able to uniquely identify a row (tuple) is a requirement of 1NF.

An alternative design uses two tables:

Customer Name Customer ID First Name Surname 123 Pooja Singh 456 San Zhang 789 John Doe

Customer Telephone Number Customer ID Telephone Number 123 555-861-2025 123 192-122-1111 456 (555) 403-1659 Ext. 53 456 182-929-2929 789 555-808-9633

Columns do not contain more than one telephone number in this design. Instead, each Customer-to-Telephone Number link appears on its own row. Using Customer ID as key, a one-to-many relationship exists between the name and the number tables. A row in the "parent" table, Customer Name, can be associated with many telephone number rows in the "child" table, Customer Telephone Number, but each telephone number belongs to one, and only one customer.^[5] It is worth noting that this design meets the additional requirements for second and third normal form.

---

yes, i agree! but if i have been asked to meet 1NF requirement, i will go with 1 table. Second method applies if we have been asked to meet the requirement of 3NF.

Anyway, thanks!!