Re: We claim that delete anomality is due to table not being in 3NF, but...

Greetings

On 29 okt., 09:16, Jon Heggland <jon.heggl..._at_ntnu.no> wrote:
> Sru..._at_gmail.com wrote:
> > 1) Third Normal Form prohibits transitive dependencies ( non_key
> > attribute CLASSROOM is dependent on other non key attribute
> > TEACHER_NAME). One of the arguments why we should put a table in 3NF
> > form is because of delete anomalies, which for SUBJECT_TEACHER table
> > means, that if we remove certain subject from the table, then all of
> > the data about certain teacher could also be lost ( in cases where
> > teacher teaches just one subject ).
>
> > SUBJECT_TEACHER( SUBJECT_ID, SUB_NAME, TEACHER_NAME, CLASSROOM )
>
> I think what is causing difficulty is the phrase "the data about certain
> X"---it is not immediately clear what that means.
>
> You don't explicitly state the FDs for the above table, but I will
> assume { SUBJECT_ID } -> { SUB_NAME, TEACHER_NAME, CLASSROOM } and {
> TEACHER_NAME } -> { CLASSROOM }. I agree that this design exhibits
> delete anomalies, because we could lose data about teachers (loosely
> speaking, as you'll see). But what is "data about teachers", and why? I
> posit that "data about teachers" in this context is just CLASSROOM,
> because of the FD { TEACHER_NAME } -> { CLASSROOM }.
>
> > But same argument could be used for the following table:
>
> > SUBJECT_TEACHER( SUBJECT_ID, SUB_NAME, TEACHER_NAME )
>
> Assuming { SUBJECT_ID } -> { SUB_NAME, TEACHER_NAME }: I would say not,
> because here there are no non-trivial FDs with { TEACHER_NAME } on the
> left side. In other words, there is no "data about teachers" here.
>
> Of course, there is "data about teachers" in the sense that the table
> above indicates what subjects a teacher teaches. But that is not really
> relevant for the 3NF/anomalies discussion, which further indicates that
> "data about" here means just "attributes determined by" (again loosely
> speaking). This is confusing and unfortunate; it's what you often get
> when you try to explain formal concepts informally.
>
> > But this time we don’t have any non-key attribute in a table dependent
> > on some other non-key attribute, and as such SUBJECT_TEACHER is in
> > 3NF. But despite table being in 3NF, all of data ( name of the
> > teacher ) about certain teacher could still be lost.
>
> Another cause for confusion is the distinction between an attribute and
> what the attribute represents. We talk about "teachers", but the tables
> don't mention teachers---only teacher /names/. It is perhaps natural to
> regard the TEACHER_NAME attribute in the second table as "data about
> teachers"---it is in the real world---but it isn't in this design. It's
> just a name. So when you delete a row, it would be more correct
> (disregarding grammar) to say that "all of data (name of the teacher)
> about certain teacher name could still be lost"---which is trivial and
> unproblematic. The table exhibits no more delete anomalies than the
> table SUBJECT_TEACHER(SUBJECT_ID, SUB_NAME, TEACHER_ID) does.
>
> Jon

So in essence, we don't consider just the loss of existence an anomaly?

On 29 okt., 22:07, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
> >> Delete anomalies can surface in more than one normal form. I haven't
> >> thought about the sepcific case you raise. It seems like a different kind
> >> of delete anomaly. In one case, the data that's lost is a relationship
> >> (e.g. Jane Smith always teaches in classroom 13).
>
> >But existence is also lost here.
>
> Existence was never properly represented. If existence of a teacher is
> worth storing in your DB, then you need a table of teachers. Otherwise,
> you may be implying existence of a teacher as a side effect of storing
> attributes of other entities, but yoy fail to actively and explicitly
> store it.
>
>
> Best, Hugo

I understand ( to a point ) what you are saying here, but still, even though the existence is just implied in a relation

SUBJECT_TEACHER( SUBJECT_ID, SUB_NAME, TEACHER_NAME, CLASSROOM ) would it be so wrong to say that due to transitive dependency we have a delete anomaly, which may cause a lost of relatioship ( thus lost of data ) and also a loss of existence ( I realize lost of existence is not considered an anomaly )?

Thank you Received on Thu Oct 30 2008 - 02:32:13 CET