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

From: Walter Mitty <>
Date: Mon, 27 Oct 2008 12:00:55 GMT
Message-ID: <X3iNk.662$>

<> wrote in message greetings

Uh, I've been away for the weekend but some new questions about normalization non the less popped up ( it seems every time I give a thought about the subject, a new question arises ). I hope you don't mind if I ask some more, cause there's no way I can figure out this stuff on my own

  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 3NF. But despite table being in 3NF, all of data ( name of the teacher ) about certain teacher could still be lost.

So how can we claim that delete anomality is due to table not being in 3NF, when it is obvious this situation ( in our case data about certain teacher being lost ) can arise even when table complies to third form?

2) Say we have a relation with attributes A, B and C, where A is also a primary key. I know that transitive dependency means that a non-key attribute ( say C ) is dependent on other non key attribute ( B ) .

Two points:

C depends on B and B depends on A. Therefore C depends on A. That's what transitive dependency means.

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). 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. Received on Mon Oct 27 2008 - 13:00:55 CET

Original text of this message