Re: rookie to gurus: transitive?

Arne,

Here's a shot at "street level".

A section of a course is taught by a professor. A professor has an office phone.

Now the question is, does the office phone number belong in the record (row, excu-u-u-u-se me) that describes the course section?

For now, let me make the assumption that professors can be identified by name, without causing problems. This is a bad idea, but that's another story.

Well, if the course section has a key (maybe course ID and section number, and the professor is listed in the record and the phone number is in there as well, so as to make it easy to print the course roster, we have the set up.

Phone number is functionally dependent on the whole key. But it's also dependent on the professor. The professor, in turn, is dependent on the key. That's a transitive dependency. If the professor teaches a lot of different sections, the phone number is copied all over the place, unnecessarily. What if it changes?

A better plan is to have a separate set of records (table, excu-u-u-se me) that describe professors. The professor's name, phone number, and other data that's really about professors, like office number and office hours, is stored in there, just once per professor.

Then the phone number can be dropped out of the class section record, and joined back in when it's necessary for something like the class roster. The professor's name appears in multiple class section records, but the phone number only appears once.

Note: in real life, the professors would be assigned ID's and the ID, not the name, would be in the class section record. What if you had two professors with the same name?

Note that normalization, and functional dependencies, can be discussed perfectly well in terms of sets of records, and not just in terms of rows of tables. In fact, functional dependencies can be discussed with reference to the subject matter (in this case course sections, office phones, and professors) without regard to data processing systems.

How's this for street level?

--
Regards,
    David Cressey
    www.dcressey.com