Functional Dependencies

Video Transcription

Welcome back everyone. In this video, we’re gonna be talking about functional dependencies in regard to normal forms. But what is a functional dependency? Well, a functional dependency is a form of a database constraint. functional dependencies in general aren’t always defined as part of a physical constraint on your database or inside of a table, like a unique constraint, or a foreign key or a primary key. Finding them is really the core essential part of getting a database designed well. In general, functional dependencies are going to be used for normalizing our database or normalizing our tables or relations. Usually starting with some relational schema, overall overarching idea, finding those functional dependencies between tables between entities, and using them to design a better database overall. So what is exactly a functional dependency. So a functional dependency is a set of columns, that implies another set of columns within a table or relation if we’re talking about other terminology. So at a very basic level, here we have, for example, department and course number implies course name, and course description. We can’t just say course number implies course name Course Description, because we could have, you know, CIS 115, or CC 520, or cin CIS 520. So, database essentials, and CIS 520, which is operating systems. And so we can’t just say course number, but if we say department course number.

So if we know cc 520, then we can pretty much know what the course name and the course description are going to be. So formally, we have this kind of notation. So if we have attributes a one a two through a n, so the dot dot dot means eight through. So this works for any number of columns or attributes as part of a table, the right arrow here is going to mean implies. So attributes a one through a n implies B, one through B M. So if we have department course number, that implies that we know also no course name and course description. So this is the formal logic that you would actually see if you try to look up some functional dependency information online. And formally, it’ll look something like this. So if we know columns, a one a two through a n, then we also know b one, b two through BM. So finding these functional dependencies are crucial to creating a good database design. But when does this functional dependency hold? Right? So how do I know that department and course number means if I know department and course number, then I also know course name and course description. So does that How do we know that actually works across our entire database or within our table. So formally speaking, a functional dependency will hold if for all rows, that functional dependency is true. So in other words, if we have this relationship s, with columns, a one through a n and b, one through BM, so we have all of these columns, this is one table inside of our database, and we have rows denoted by t and t prime here.

So imagine that we have you know, anything any number of rows as part of our table, if rows t and t prime agree or a one through a n, then they also agree for B one through BM, meaning that for every single row in our relation, or in our table inside of our database, this functional dependency holds true. So if I had a table that had all of our departments, and course numbers, along with the name and description for all of those, then we could go through row by row and check to see if our statement that we made earlier here, department course number implies course name course description, we could go through each and every single row to make sure that that statement holds true. So really, what functional dependency functional dependencies here and the notation that I’m using really boils back down into some formal logic, and a lot of it can boil back to what you learned with conditional logic Boolean In logic when you first started learning how to program but in the following videos here we’ll take a look at some examples of various kinds of functional dependencies