Welcome to Lesson Two of Module 11 on Normalization Concepts and Practice. I'm going to start with an important practical question, about falsifying functional dependencies. What is the practical usage of falsifying functional dependencies in sample tables? Lesson Two extends your background about normalization concepts and practice from the motivation covered in Lesson One. The most important part of the normalization process is to identify or assert functional dependencies. This lesson covers important concepts about functional dependencies including specification and falsification sample rows. The objectives in this lesson involve obtaining a solid grounding about asserting functional dependencies for a table. You should be able to define functional dependency. Explain the analogy to a unique constraint. And falsify functional dependencies in sample rows. A functional dependency is a value neutral constraint similar to primary key and foreign key constraints. A value neutral constraint involves a comparison of columns. A primary key can take any value as long as it does not match the primary key in an existing row. A foreign key constraint requires that the value of a column in one table matches the value of a primary key in another table. Constraints must be asserted so that understanding business requirements and rules is necessary. Constraints are normative, specifying conditions that should normally be true. As you will see, specification of functional dependencies drives the normalization process. Specification of functional dependencies can not be automated because an understanding of business requirements and rules is necessary. A functional dependency or FD for short is a constraint about two or more columns of the table. X determines Y, X arrow Y. If there is just at most one value of Y, for every value of X. The word function comes from mathematics where a function gives one value. For example, student number determines student city. In the university database if there's at most one city value for every student number. In a functional dependency, X and Y are one or more columns. The X columns are known as the determinant or left hand side. LHS for short. The Y columns are known as the right hand sides, RHS for sort. You could also think about functional dependencies as unique constraints. By stating X determines Y, if X and Y were placed together to table without other columns, X should have unique values. For example if OfferNo and OffYear are placed in a table without other columns, an OfferNo that determines OffYear that OfferNo has unique values in a table. Likewise, if OfferNo, StdNo, EnrGrade, are placed in a table without other columns, the combination of OfferNo and StdNo is unique. If the FD OfferNo, StdNo determines EnrGrade. Columns that are unique in a context with other columns should be placed together in a table. Essentially, functional dependencies indicate columns that should be placed in the same table. You will see that this simple idea is the essence of normal forms presented in Lesson Three. A simple organization of functional dependencies is to list them, group by left hand side or determinate. This list contains four FD groups with determinate StdNo, OfferNo, CourseNo and a combination of StdNo and OfferNo. As you will see an FD list, organized by left hand side, facilitates the normalization process. As an alternative organization, a functional dependency diagram compactly displays functional dependencies. In a FD diagram, line height and position visually show columns sharing the same determinate. By examining the position in height of lines, you can see that the combination of StdNo and OfferNo determines the role grade. It's easier to see that OfferNo alone determines off term off year and CourseNo. With a large number of functional dependencies, functional dependency diagrams can be difficult to draw and understand. Thus, functional dependency lists are preferred to functional dependency diagrams even though functional dependency lists can be long for large collections of functional dependencies. A functional dependency cannot be proven to exist by examining the rows of a table. However, you can falsify a functional dependency by examining sample rows of a table. Falsifying a functional dependency means proving that the functional dependency is not true. Practice with falsifying functional dependencies can help you understand the meaning of the functional dependency concept and communicate with users about questionable functional dependencies. A falsification pattern, or example, involves two rows with the same X value but a different Y value. To demonstrate the falsification of potential functional dependencies, let's use the sample table with StdNo as the left hand side. I will falsify a few functional dependencies. And leave it as an exercise for you to falsify the remaining functional dependencies with StdNo as the determinate. StdNo determines OfferNo is falsified by two pairs of rows. One and two, three and four. Rows one and two have the same StdNo value, S1. But a different OfferNo value, O1 and O2. StdNo determines EnrGrade is falsified by one pair of row, three and four. Rows three and four have the same StdNo value, S2, but different EnrGrade values, 3.1 and 3.4. StdNo determines student class is not falsified by any pair of rows. Note that rows two and three do not falsify the functional dependency because a StdNo values, S1 and S2 are different in the rows. Now let's wrap up Lesson Two about functional dependencies. Functional dependencies are important constraints to remove unwanted redundancy in a table. A functional dependency is essentially a constraint about the weakness of a column if used in a table alone with other columns. You learned about the definition of functional dependency. Specification of functional dependencies and falsification of functional dependencies in sample tables. In answer to the opening question, falsification of functional dependencies has an important practical usage. Falsifying FDs using sample rows can help communicate with users about business rules. A user could be shown sample rows that falsify an FD. If a user confirms that the example is valid, the FD can be eliminated. Falsification is typically used for functional dependencies in which a database designer is unsure and needs user feedback. After this background on functional dependencies, you're ready to use functional dependencies in rules known as normal forms, to remove unwanted redundancy. Lesson Three provides details about an important numeral form based on a simple pattern of functional dependencies to remove redundancy.
