database - Minimum no of tables that exists after decomposing relation R into 1NF?

Question

Consider the relation R(A, B, C, D, E, F, G) with the following types of attributes:-

Total No of Keys = 1 = {A}

Set of Simple (or) Atomic (or) Single Valued Attributes = {B, C}

Set of Multivalued Attributes = {D, E}

Set of Composite Attributes = { F, G}

What would be the minimum no of tables that exists after decomposing relation R into 1NF?

(A) 3 (B) 2 (C) 4 (D) 5

---

My attempt:

We needed different table for each multivalued attributes with given key(A), total = 2

Similarly, we needed different table for each composite attributes, total = 2.

There are total 4 such attribute. I give 4 tables with given key(A) in each(4) tables. I'm allowed to insert atomic attributes(B,C) to any one of given 4 tables. So, I concluded that 4 tables are sufficient to represents relation in first normal form.

Can you explain in formal way, please?

Answer 1

• If all the candidate keys of a relation contain multivalued attributes:
  Introduce a surrogate attribute for at least one multivalued attribute.

• For each attribute you deem "composite" (having heterogeneous components, like a tuple):

  • For each attribute component that can be missing:
    Add a relation with attributes of some multivalue-free candidate key and an attribute for that component. For every row of the original relation that has that component have a row in the new relation whose candidate key attribute values are the original row's and whose new attribute value is the value of the component. Then drop the component from the composite attribute values. This adds one relation per component that can be missing.

  • For each remaining component:
    Add an attribute to the original relation whose value in each row is the value of the component. Then drop the composite attribute. This adds no relations.

• For each attribute you deem "multivalued" (having homogeneous elements, like a relation):

  • For an attribute of a type that can have zero elements:
    Add a relation with the attributes of some multivalue-free candidate key and that attribute. For every row of the original relation have a set of rows in the new relation whose candidate key attribute values are the original row's and whose new attribute values are the elements of the multivalued attribute. Then drop the multivalued attribute. This adds one relation per multivalued attribute.

  • For each attribute of a type that always has elements:
    Replace every row of the original relation by a set of rows whose multivalued attribute values are the elements of the multivalued attribute and whose other attribute values are the original row's. This adds no relations.

So the final relations here are {A,B,C,F1,...,G1,...}, {A,D} and {A,E}, a total 1 (for original & composites) + 2 (for multivalued).

Of course, in practice you should normalize the new tables, which may generate new tables. But that is to improve the design. Here we want a design with as few added tables as possible.

(There's no such thing as "decomposing" a relation into 1NF. The original sense of "normalize" meant getting rid of relation-valued attributes. Later normalization theory considers every relation to be in 1NF. See this re 1NF & atomicity. We can replace a relation with attributes that we consider to have homogeneous parts (multivalued) or heterogenous parts (composite) by one or more relations that have attributes for parts instead. And we can convert a non-relational design to a relational one. But that non-relational design won't have a candidate/primary key, because that's defined only for relations.)