Video Transcription

Welcome back everyone. And in this video, we’re going to be taking a look at various ways of pulling out more functional dependencies. Inside of our relations. Particularly, we’re going to be focusing on how we can avoid anomalies and pulling more functional dependencies out using the Armstrong rules. So first off, what is an anomaly? Well, anomaly. An anomaly can be defined as essentially a bad functional dependency, and particularly where where a bad functional dependency holds based off of the current data that we have. So typically, we will know some of the functional dependencies just based off of our knowledge of how the data works in the real world. But we really, truly need to find all of the functional dependencies, so we can identify the bad ones, because the bad ones are going to be the things that causes data consistency issues, and integrity issues down the road as more data is added into our database. So how do we find all of these as a whole? Well, there are a few methods that we can look at to find all functional dependencies. The first one here are called the Armstrong girls. Now, this will cover a little bit more into the the heavy theory side of things with databases. But we are going to cover these just as an informative step. But Armstrong’s first rule is called the splitting and combining rule. So if we have a one attributes a one through a n implies B, one through B M, that means these are all equivalent, right?

So a one through a n implies b one implies b two, and so on. So if we have a multiattribute, functional dependency on the left, that implies the right, then we can split the right hand side of the functional dependency out into individual functional dependencies. The second rule of Armstrong’s rules is called the trivial rule. And this one is relatively straightforward. So if we have a one through a n implies a i, where i is one of a one through a n, right? So in other words, if we have something like this, ABC implies a, ABC implies B, and ABC implies c. So those are all trivial, right? So you know, the student ID is obviously going to imply that we know the student ID, and so on. Our last Armstrong rule that we’ll be covering is the transitive closure rule. So if we have attributes a one through a n implies B, one through B, M, and B, one through bn implies c one through C p, then we can infer that a one through a n implies c one through CP. We did this already before. Using the name, name and color category implies price example in our previous video, but let’s take a look at some functional more functional dependencies centered around that. So here are the same ones that we had before name, color category, category and Play Store and color category implies price. So from these functional dependencies, we can imply all of these other functional dependencies as a result. And let’s break down which Armstrong rules that actually apply here.

Well, the trivial ones are pretty straightforward and easy to go. So name category implies name. That’s obviously the trivial rule because we have the same column on both sides. So that means this is a trivial functional dependency. Name category implies color. We’ve already covered this particular one in a previous video, but this is the transitivity rule, particularly on the dependencies four and one right since we know name and category implies name. Then name category also implies color because name implies color, we have six, which is a trivial rule again, because we again, we have the same cat or the same attribute or column on both sides of the functional dependency. Seven is going to be the split and combine rule based off of five and six. So since we have named category implies color, named category implies category, therefore we have named category implies color category. So we’re coming Binding five and six into one single functional dependency. And the final line here name category implies price is the transitivity rule based off of seven and three. So since category and color implies price, and we have name category implies color category then we have name category also implies price because color category here, this right hand side of seven is this left hand side of this functional dependency. So therefore, we can substitute price for color category because color category implies price. But this is kind of hard, right? I mean, overall not too bad. Most of the rules are trivial or easy to combine or split. But this is kind of painstakingly slow to actually implement and the larger your tables and relations are, the more difficult the Armstrong rules actually get to apply to extract all of the functional dependencies. So in the following video, we’ll take a look at a little bit of an easier way to extract all of them.