将关系 R 分解为 1NF 后存在的最少表数? [英] Minimum no of tables that exists after decomposing relation R into 1NF?

问题描述

考虑具有以下属性类型的关系 R(A, B, C, D, E, F, G):-

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

密钥总数 = 1 = {A}

Total No of Keys = 1 = {A}

简单(或)原子(或)单值属性集 = {B, C}

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

多值属性集 = {D, E}

Set of Multivalued Attributes = {D, E}

复合属性集 = { F, G}

Set of Composite Attributes = { F, G}

将关系 R 分解为 1NF 后存在的最小表数是多少?

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

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

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

我的尝试:

对于给定键(A)的每个多值属性，我们需要不同的表，总数 = 2

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

同样，我们需要为每个复合属性使用不同的表，总计 = 2.

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

共有 4 个这样的属性.我在每个(4)表中给出了 4 个带有给定键(A)的表.我可以将原子属性(B，C)插入给定的 4 个表中的任何一个.因此，我得出结论，4 个表足以表示第一范式中的关系.

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?

推荐答案

• 如果关系的所有候选键都包含多值属性:
  为至少一个多值属性引入代理属性.

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

    对于您认为复合"的每​​个属性(具有异构组件，如元组):

    • 对于每个可能缺失的属性组件:
      添加与一些无多值候选键的属性和该组件的属性的关系.对于具有该组件的原始关系的每一行，在新关系中都有一行，其候选键属性值是原始行的，其新属性值是组件的值.然后从复合属性值中删除组件.这会为每个可能缺失的组件添加一个关系.

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

    所以这里的最终关系是 {A,B,C,F1,...,G1,...}, {A,D} 和 {A,E}，共 1(对于原始和复合) + 2(多值).

    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.

    (没有将关系分解"为 1NF 这样的东西.规范化"的原始含义意味着摆脱关系值属性.后来的规范化理论认为每个关系都在 1NF 中.参见 这 是 1NF 和原子性.我们可以用我们认为具有同质部分(多值)或异质部分(复合)的属性替换一个关系，用一个或多个具有部分属性的关系来代替.我们可以将非关系设计转换为关系设计.但是那个非关系设计不会有候选/主键，因为它只为关系定义.)

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

    这篇关于将关系 R 分解为 1NF 后存在的最少表数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！