Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: More on lists and sets

Re: More on lists and sets

From: Jan Hidders <>
Date: 30 Mar 2006 15:42:22 -0800
Message-ID: <>

Brian Selzer wrote:
> "Jan Hidders" <> wrote in message
> >
> > Brian Selzer wrote:
> >>
> >> Let's say that you're creating work orders to make batches of finished
> >> parts
> >> (products). So, you have the following NFNF relation:
> >>
> >> {Order, Product, Process: {Step, Operation, Materials: {Part, Quantity}}}
> >>
> >> [...] For example, if management decides
> >> to affix barcode labels on a particular line of products, then every work
> >> order for that product line must be modified to reflect the additional
> >> operation--even if there are many orders that use the exact same process.
> >
> > Of course, and you would have the same "problem" if you had flattened
> > the table and then normalized it. [....]


> I disagree. If the table were flattened and then normalized, then the
> processes would live in their own table and one update per process would be
> all that is required, instead of one update per order.

Let's take a more compact abstract example:

R : { A, B, C : { D E } }
with FDs A->B, A->C and D->E

An instance would be: (please view with fixed width font)

R : { A, B, C : { D, E } }

      a1 b1       d1 e1
                  d2 e2
      a2 b2       d1 e1
                  d2 e2

Your problem can be summarized as: when we update all the occurrences of the set { (d1, e1), (d2, e2) } by adding a pair (d3, e3) we need to do this in two places. So let's consider the normalized version:

R1 : { A, D, E }

        a1 d1 e1
        a1 d2 e2
        a2 d1 e1
        a2 d2 e2

R2 : { A, B }
       a1 b1
       a2 b2

As you see, we still need to make two insertions if we update the set, namely (a1, d3, e3) and (a2, d3, e3). Now, what would solve this would be the introduction of some identifier that identified the set and replace the sets with that, but that would not be just normalization but really changing the data model.

> > However, you do have a point in that one has to be careful about when
> > to nest an entity in another entity. Suppose that A : { B, C : { D, E }
> > } then this implies that C is a weak entity that depends upon A, not
> > only for its existence but also for its identification. If this is not
> > the case then you get indeed the update anomalies you are talking
> > about.


> Interesting. So in other words nesting is safe when the relationship
> between the respective entities is one of composition rather than
> aggregation. What about generalization?

Not a related issue, as far as I can tell.

Received on Thu Mar 30 2006 - 17:42:22 CST

Original text of this message