1

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?

Display Name is missing
  • 196
  • 2
  • 11
  • 25
  • @philipxy, I supposed that [first normal form](https://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjv4OjAlPrMAhVLQI8KHb3YDDYQFggbMAA&url=http%3A%2F%2Fwww.cis.drexel.edu%2Ffaculty%2Fthu%2FTeaching%2FINFO365%2FNORMALIZATION-p2.ppt&usg=AFQjCNHy3DRKVDP5KPTbvBwDSohGetGQJg&sig2=IbmLLNJsCNxaofjV7HMwWw&bvm=bv.122676328,d.c2I) can not allowed multivaued and composite attribute, so I did separate table for each composite/multivalued(continue..). – Display Name is missing May 27 '16 at 11:46
  • ...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. Am I correct? – Display Name is missing May 27 '16 at 11:46
  • Your linked ppt slides unfortunately talk about "flattening" a multivalued attribute so as to not introduce a new table by the unintelligible "repeats the repeating group value in a separate tuple". They reference Elrasmi et al's textbook, which you can find online. (This option means you could use just one relation, but I expect it only works for "multivalued" types with no zero-element values.) PS Re your comment about 1NF: My link explains how it does not make sense to talk about a relation not being in 1NF or to talk about an attribute of a row of a relation holding anything but one value. – philipxy May 27 '16 at 12:43
  • PS You must keep the non-multivalued attributes in the original table, because if an A has an empty multi-valued value it will not have any rows in that attribute's added relation. – philipxy May 27 '16 at 12:45
  • ["1NF" has many meanings.](https://stackoverflow.com/a/40640962/3404097) They all involve replacing some table with parameterized structure by some table(s) with a column per parameter. Similarly, Multivalued & Composite have no fixed meanings. (Although here they seem to mean having (respectively) homogeneous & heterogeneous.) So you need to tell us your definitions & preferably also textbook name & edition. – philipxy Jan 10 '21 at 10:57

1 Answers1

3

  • 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.)

philipxy
  • 14,867
  • 6
  • 39
  • 83