functional dependency in Software

Generator QR Code JIS X 0510 in Software functional dependency

a constraint about two or more columns of a table X determines Y{X^Y) if there exists at most one value of Y for every value of'X

functional dependency ( F D ) is a constraint about two or more columns o f a table X deter mines Y(X Y) if there exists at most one value o f 7 for every value o f X The word func tion c o m e s from mathematics where a function gives one value For example, Social Secu rity number determines city (StdSSN > StdCity) in the university database table if there is at most o n e city value for every Social Security number A column appearing o n the lefthand side o f an F D is called a determinant or, alternatively, an L H S for left-hand side In this example, StdSSN is a determinant You can also think about functional dependencies as identifying potential candidate keys B y stating thatX > Y, i f X a n d Fare placed together in a table without other columns, X is a candidate key Every determinant (LHS) is a candidate k e y if it is placed in a table with the other columns that it determines For example, if StdSSN, StdCity, and StdClass are placed in a table together and StdSSN - StdCity and StdSSN - StdClass then StdSSN is a candidate key If there are n o other candidate keys, a determinant will b e c o m e the primary key if it does not allow null values

A functional dependency diagram compactly displays the functional dependencies o f a particular table You should arrange F D s to visually group columns sharing the same determinant In Figure 72, it is easy to spot the dependencies where StdSSN is the deter minant B y examining the position and height o f lines, y o u can see that the combination o f StdSSN and OfferNo determines EnrGrade whereas OfferNo alone determines OffTerm,

OffYear, and CourseNo A g o o d visual arrangement can facilitate the normalization process described in the next section If y o u prefer, y o u can list F D s rather than arrange them in a diagram For large collec tions o f F D s , it is difficult to make a diagram You should list the F D s , grouped by L H S , as shown in Table 72

Besides understanding the functional dependency definition and notation, database design ers must be able to identify functional dependencies when collecting database requirements In problem narratives, s o m e functional dependencies can be identified by statements about uniqueness For example, a user m a y state that each course offering has a unique offering number along with the year and term o f the offering From this statement, the designer should assert that OfferNo > OffYear and OffTerm You can also identify functional depen dencies in a table design resulting from the conversion o f an ERD Functional dependencies would be asserted for each unique column (primary key or other candidate key) with the unique column as the L H S and other columns in the table o n the right-hand side (RHS)

FDs for 1-M relationships assert an FD in the child-to-parent direction of a 1-M relationship Do not assert an FD for the parent-to-child direction because each LHS value can be associated with at most one RHS value

Although functional dependencies derived from statements about uniqueness are easy to identify, functional dependencies derived from statements about 1-M relationships can be confusing to identify When y o u see a statement about a 1-M relationship, the functional de pendency is derived from the child-to-parent direction, not the parent-to-child direction For example, the statement, "A faculty teaches many offerings but an offering is taught by one faculty" defines a functional dependency from a unique column o f offering to a unique col umn o f faculty such as OfferNo > FacNo N o v i c e designers sometimes incorrectly assert that FacNo determines a collection o f OfferNo values This statement is not correct because a functional dependency must allow at most one associated value, not a collection o f values Functional dependencies in which the L H S is not a primary or candidate key can also be difficult to identify These F D s are especially important to identify after converting an E R D to a table design You should carefully look for F D s in w h i c h the L H S is not a candidate key or primary key You should also consider F D s in tables with a combined primary or can didate key in which the LHS is part o f a key, but not the entire key The presentation o f nor mal forms in Section 72 explains that these kinds o f F D s can lead to modification anomalies

7 Normalization of Relational Tables 223 Another important consideration in asserting functional dependencies is the minimal i s m o f the L H S It is important to distinguish w h e n one column alone is the determinant versus a combination o f columns A n F D in which the LHS contains more than one column usually represents an M - N relationship For example, the statement, "The order quantity is collected for each product purchased in an order" translates to the F D OrdNo, ProdNo > OrdQty Order quantity depends o n the combination o f order number and product number, not just one o f these columns minimal determinant Part o f the confusion about the minimalism o f the LHS is due to the meaning o f columns in the left-hand versus right-hand side o f a dependency To record that student Social Security number determines city and class, y o u can write either StdSSN > StdCity, StdClass (more compact) or StdSSN > StdCity and StdSSN > StdClass (less compact) If you assume that the e-mail address is also unique for each student, then y o u can write

the determinant (col umn^) appearing on the LHS of a functional de pendency) must not contain extra columns This minimalism re quirement is similar to the minimalism require ment for candidate keys