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

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

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

It is not obvious. There is no anomaly.

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

Since A is a key, by definition all attributes are dependent on A.

> * But is C always dependent on A simply because B is dependent on A or…

The reason for the dependency is found in the real world. If this is just an abstract example, there are no reasons as such. But maybe I'm not understanding you correctly.

Consider SUBJECT(SUB_ID, TEACHER_ID, TEACHER_NAME), with { SUBJECT_ID } -> { TEACHER_ID } and { TEACHER_ID } -> { TEACHER_NAME }, attempting to model a situation where subjects have only one teacher, and teacher IDs are unique (speaking loosely). By FD transitivity rule, we can establish that { SUB_ID } -> { TEACHER_NAME }. This also makes sense in the real world: If we know the (sole) teacher id of a given subject, and we know the (sole) teacher name of a given teacher id, then we obviously know the teacher name given a subject. Does this answer your question?

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

If A is a key, all attributes are dependent on it, regardless of other attributes and dependencies. You question doesn't make sense.

-- 
Jon