Figuring Out Functional Dependencies in Relations

I have been trying to teach databases to juniors for the past some time. In one of my previous lectures on normalization, I had a guest auditor in my class to review my lecture. After the lecture was over, I had an opportunity to talk to him. The purpose was to get reflections on my teaching style.

I was stunned by his question that how long I was taking to prepare my lectures. My reply was that I had nearly spent five weeks to prepare my lecture on normalization (although I have spent more than that). The reason I spent so much time in preparing for normalization is that I had eyed it in the beginning of the course as the most important topic in databases. And, indeed, it is.

Anyhow, the reason he surmised that I was not preparing my lectures well was grounded in his observation about the way I taught about functional dependencies. A student asked that should X always be a key attribute in a functional dependency X->Y? My answer was no, it need not be. I am still adamant about my answer. So in a functional dependency such as X->Y, either one of X or Y can be a key or non-key attribute. The idea behind normal forms is to address issues related to key and non-key attributes.

A succinct definition of a functional dependency, such as X->Y, is that whenever X assumes a certain value (say x), and for that value of X (x), Y assumes a certain value y, then in every subsequent retrievals of X, whenever X assumes the value x, Y should assume the value y. Thats it! Functional dependency is no more than that. It is a very simple mathematical idea. It is the job of the subsequent normal forms to reduce the dependency of non-key attributes on non-key attributes.

I had to haggle with my friend a little bit to convince him about my point. And that discourse kept me thinking about functional dependency for quite some time. One of the questions I pondered about was how to figure out a functional dependency between attributes, which are nothing more than variables in a mathematical sense.

A very easy way to figure out functional dependencies between attributes is to review the cardinality of their relationships. Just keep in mind the definition of functional dependency I gave in the paragraph above (actually two paragraphs above). Now if two attributes (X and Y) have a zero-to-zero relationship, what is the functional dependency going to be? They shall have no functional dependency! They shall be independent of each other as nothing in X maps on Y.

Similarly, in case of a one-to-one relationship between X and Y, there will be a bidirectional dependency meaning X->Y and Y->X shall hold.

In case of a one-to-many relationship between X and Y, where X is on the one side and Y on the many side, the functional dependency Y->X shall hold. The reason is that many values of Y shall map on a single value of X, so Y-X. But one value of X shall map on many values of Y, so X->Y shall not hold. Similarly, for the converse, i.e. many-to-one relationship between X and Y, the functional dependency X->Y shall hold.

For the many-to-many relationship between X and Y, there will again be no functional dependency between the two. The reason is that a value of X shall map on multiple values of Y and vice versa, thus violating the definition of functional dependency.

What do you think would be the functional dependency in the case of a zero-or-one-to-many relationship? I leave it for you guys to figure out.

Photo by mattacevedo