6.18

Consider two entity sets \(A\) and \(B\) that both have the attribute \(X\) (among others whose names are not relevant to this question).

  1. If the two \(X\) s are completely unrelated, how should the design be improved?

  2. If the two \(X\) s represent the same property and it is one that applies both to \(A\) and to \(B\), how should the design be improved? Consider three subcases:

  • \(X\) is the primary key for \(A\) but not \(B\)
  • \(X\) is the primary key for both \(A\) and \(B\)
  • \(X\) is not the primary key for \(A\) nor for \(B\)

  1. Rename the attribute \(X\) to something that describes more info in both entity sets, so that it can have a different name in both the entity sets.

Case 1: \(X\) is the primary key for \(A\) but not \(B\)

In this case we would create a foreign-key constraint on the attribute \(X\) of the entity set \(B\) referencing the entity set \(A\).

Case 2: \(X\) is the primary key for both \(A\) and \(B\)

In this case we merge the two entity sets \(A\) and \(B\) into one entity set \(C\). The new entity set \(C\) will have the attribute \(X\) as a primary key and moreover it will have all the other attributes of \(A\) and \(B\) (making proper attribute name modifications, if needed).

Case 3: \(X\) is not the primary key for \(A\) nor for \(B\)

In this case we can create a new entity set called \(C\) having a single attribute \(X\). And the attribute \(X\) in \(A\) and \(B\) will be a foreign-key referencing the attribute \(X\) in the entity set \(C\). This also gives room for storing more information about the attribute \(X\) in side of the entity set \(C\).