Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> comp.databases.theory -> Re: More on lists and sets
Brian Selzer wrote:
> "Jan Hidders" <hidders_at_gmail.com> wrote in message
> news:1143707605.673005.123230_at_e56g2000cwe.googlegroups.com...
> >
> > 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. [....]
>
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.
>
Not a related issue, as far as I can tell.