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

greetings

Sorry for not replying sooner

> > But is C always dependent on A simply because B is dependent on A or…
> > I mean, B will be dependent on A regardless of whether we have C
> > attribute, but I assume that is not the case with C?
> I'm afraid I don't understand the question. Could you give an example?

I can’t think of any examples and I’m also not quite sure how to ask differently, but I’ll try. In most transitive dependencies C is dependent on A simply because B is dependent on A. Meaning if we removed B from the relation then C would no longer be dependent on A.

But can there be transitive dependencies where C would still be dependent on A even if we removed B from the relation ( BTW - I realize that if we removed B from the relation that we wouldn’t have transitive dependency anymore)?

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

> In the other case, it's an existence (e.g. There is a teacher named Jane
> smith). Existence assertions based on non key values in tables eventually
> result in a separate table to assert the existence, like
> TEACHERS(TEACHER). But I forget how that relates to normal forms.

So lost of existence anomaly may still exist in 3NF?

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


> > But same argument could be used for the following table:


> > SUBJECT_TEACHER( SUBJECT_ID, SUB_NAME, TEACHER_NAME )

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

I’m not sure, but I think you’ve missed the point of my question.

Table SUBJECT_TEACHER( SUBJECT_ID, SUB_NAME, TEACHER_NAME ) is not the result of me normalizing the following table:

SUBJECT_TEACHER( SUBJECT_ID, SUB_NAME, TEACHER_NAME, CLASSROOM ) Thus the two relations above are two separate, non related tables. I was just pointing out that my book used the argument that if we don’t put a relation into 3NF, then due to delete anomalies ( which are the result of transitive dependencies ) the information about certain teacher could be lost ( if certain row gets deleted). But I argued that book’s argument is weak ( in a sense that book gave the impression that once you have any table in 3NF, then the kind of delete anomaly described above won’t happen, but I argued that with some tables that particular delete anomaly could still happen ) since information about a teacher could be lost even if table is in 3NF ( to get my point across I used a bit different relation, one that never had CLASSROOM attribute ). Thus, even though table is in 3NF, we might still have same kind of delete anomalies. Namely, if all we want to know about a certain teacher is her name, then we might be OK with the following table, which is also in 3NF:
SUBJECT_TEACHER( SUBJECT_ID, SUB_NAME, TEACHER_NAME ) But this time, if teacher teaches just one class, and if remove that particular row, then data about that teacher will also get lost, despite the relation being in 3NF.

thank you Received on Wed Oct 29 2008 - 01:32:49 CET