Functional Dependency Examples

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at some examples of functional dependencies. So first we’ve talked about when a functional dependency may hold officially as part of a relation. So a functional dependency holds or doesn’t hold on an instance of a relation or table. If for all cases are all rows, and that functional dependency holds true, or we find a row, that’s where a row where that functional dependency does not hold true. So we have this example bit of information. Here we have column student id, name, phone and department. And let’s say for the sake of this example, we say that our student ID in informs us that we know we can infer name, phone and department from just the student ID. So if we have a computer science students, we can say that that computer, we, if we have their ID, we can infer their name, and the phone number and department that they belong to. Let’s say we also can say that if we know the department, then we also know the phone number for that department. So let’s see if if these things hold true here. Well, if we look down the student ID column, this holds true right student ID implies name, phone and department because if I look at student ID, all of the bits of information, name, phone and department are unique for every single student ID. So we have no duplicated information, right? So if, for example, though, if both of these students here student ID, 3542 and 1111. If both of them had the same name, then we would have some issues, right, our functional dependency would not hold, because there would be no way for us to tell the two apart. So let’s continue on looking at some of these. So department phone, well, math holds true. So math implies 1234. And g implies 1234. Cis implies 9876 97. Six, that’s consistent.

But we can also then, let’s try this other way. Right? Phone implies department. So if we have phone implies department, let’s look. Well, that works. Okay. But what about this, right, this is where we have an instance, where we have something that does not hold. So if we had, the student ID example is kind of hard, a little bit harder to see. But it’s a little bit more clear. If we talked about phone number. So if we have, if we had the same student ID but different names or different departments, then we’d have an issue with the student ID. But here we have two departments that share the same phone number. And so department implies phone still holds that functional dependency, but phone implies department does not. Because if 1234 implies math, and then here 1234 implies English. And so this functional dependency, we can say does not hold for that situation. So this means what the functional dependency and functional dependencies hold only if the data being stored, holds, if it matches through with all the rows that we’re actually storing inside of our database. If we introduce a record to our table that causes that functional dependency to not hold, then we have some some form of issue either our application is not catching all scenarios and cleaning the data that we need cleaned up before it’s being stored, or we have an inherent flaw in our database design. Another you know, a confusing example I guess with with functional dependencies here are the idea of city state and zip.

Even if we consider like Kansas City, Kansas, Kansas City, Missouri, or even was just Kansas City, Kansas side right? There’s Letha, Shawnee, Olathe North of the South, we have all all these different zip codes. And the zip code does not necessarily imply city name because a zip code can span multiple cities. A city can span multiple zip codes. And a city can also span multiple states. So city state and zip is usually something that someone we all default to well, if we Know, the zip code, then we know, the the city or the state, and so on and so forth. So there’s some really confusing things that can with data that can make it difficult to actually write good databases, or good tables as a result of that. But let’s take a look at this example. Here, we have a few functional dependencies here. And remember, our functional dependencies are constraints inside of our database. On some instances they hold. And on others, they don’t, as we saw with our previous table with the phone number and the department, but do all of these functional dependencies hold for this particular example? So take a pause real quick in the video and take a look at the data and check to see if all of these functional dependencies hold. Well, let’s take a quick look. So we have name implies color. So iPad silver, iPhone, silver. So this is okay, so far. We don’t have so good so far. Right? Category store, categories, store, Gadget, campus store, gadget campus store, good so far. And then we have color and category. So gadget silver implies price 529. So gadget silver 529. Gadget silver for 29. Ah, well, this functional dependency here does not hold according to the data that we have in our database right now.

But what if we added an additional additional row here, right? What if we add an additional row? How about here Well, name, category, name, color, name implies color category implies store. That looks to be all good. But let’s take a look at color category again. So gadget silver implies price 529, gadget black, so we’ve changed the color here, Gadget, Black 429, tablet, Silver 569. So, so far, so good. With this particular situation, right? They all hold here. But just because they hold all all, just because they hold here in this instance, of our table doesn’t necessarily mean it’s a good functional dependency. And we can enforce the idea of a functional dependency on in some cases, we can add things like check constraints, which we’ll talk about later in the course, to enforce functional dependencies inside of our physical design of our database. And then we can also enforce restrictions on the application side to filter out data. So we don’t actually violate any of our functional dependencies that we have defined. But they all they all hold here in this particular example. Generally speaking, we’re going to, or at least our goal with doing a good database design is we want to be able to extract and identify all of our functional dependencies that we have. So how do we actually achieve that task? Well, if we have, just for a sake of example, our functional dependencies that we had just just a second ago, if they all hold, then we can also imply name category implies price. But well, why? Well, if we have a set of known functional dependencies, typically we can actually extract more functional dependencies out of that as a result. And in this case, we can extract the name category implies price through a transitive property. So if name implies color, and color category implies price, then we can also say color character. Then we can also say, name. Category also implies price because name implies color, color, and category implies price. Therefore name and category implies price as well. So just one method for us to extract more functional dependencies. But in the following video, we’ll actually take a look at more ways that we can extract all of our functional dependencies. And a lot of times, we are defining functional dependencies initially, just based off of our inherent knowledge of our data, and then we can use these other techniques to pull out more