To further refine the database model it is imperative that you examine the relationships that exist among the data of an entity. The initial design of the entities will require further refinements as we progress with the database design. These refinements should be implemented, because they will help in limiting the problems in the final design.

In the above example, there are a lot of problems with this entity. For example, there will be data redundancy. Each time we place an item in the database the supplier's name and address will have to be specified. Suppose that on an average a supplier supplies 50 items. This means that the supplier name and address are repeated 50 times. These redundancy problems will lead to problems in managing and maintaining the database and in maintaining the data integrity.

Consider the following table. We will make use of the table to illustrate the concepts of functional dependencies. Let R be a relation and let X and Y be arbitrary subsets of the set of attributes of R. Then we say that Y is functionally dependent on X. In symbolic notation we will write X —► Y (read as 'X functionally dependent on Y' or simply 'X arrow Y'). We can say this, if and only if each X-value on R has associated with it one Y-value in R. Or in other words, we can say that X is functionally dependent on Y only if each X-value has associated with it one Y-value in R. So whenever two tuples of R have the same X-value and if they are functionally dependent, then they should agree on their Y-values also. Now in the ORDER table, we can say that DISTJD is functionally dependent on NAME or '{DISTJD} ~~► {NAME}', because every tuple in the ORDER table with a given DISTJD also has the same NAME value.



The left-hand side and the right-hand sides of a functional dependency are sometimes called the determinant and the dependent respectively. As the definition states, the determinant and the dependent are both sets of attributes. When the set contain more than one attribute we will use the braces to enclose them as shown above.

If X is a candidate key of relation R, especially if it is the primary key, then all attributes Y of relation R must necessarily be functionally dependent or if you consider the BOOK table as an example, then we must have:

ISBN r* {TITLE, Author, Publisher, Year, Price}

So a functional dependency is a many-to-one relationship between two sets of attributes of a given relation. Given a relation R, the functional dependency X—*Y (where X. and Y are subsets of the set of attributes of R) is said to hold in R if and only if, whenever two tuples (rows) of R have the same value of X, they also have the same value for Y.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM Mainframe Topics