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

From: Hugo Kornelis <hugo_at_perFact.REMOVETHIS.info.INVALID>
Date: Mon, 27 Oct 2008 23:13:43 +0100
Message-ID: <vlecg4h12tgmk5u5cfo34dgtdg3ibc6r3v_at_4ax.com>


On Sun, 26 Oct 2008 13:51:08 -0700 (PDT), Srubys_at_gmail.com wrote:

(snip)
>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.

Hi Srubys,

Uhm, no. Normalising shouldnot be done by simply removing attributes, but by moving them to seperate tables. So when you removed CLASSROOM from the SUBJECT_TEACHER table, you should at the same time have introduced a new table for the CLASSROOM and the attrribute it depends upon, TEACH_NAME:

TEACHER (TEACHER_NAME, CLASSROOM) Now, if the last subject a teacher teaches is removed, the existence of the teacher and the classroom assigned to her are still known in the database.

(snip)
>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 ) .
>
>* I also assume that with transitive dependency C is also ALWAYS
>dependent on A?!

Yes. If any value of A relates to only one value of B, and any value of B relates to only one value of C, then it follows automatically that any value of A relates to only one value of C. Which is the definition of functional dependency.

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

Best, Hugo Received on Mon Oct 27 2008 - 23:13:43 CET

Original text of this message