Limitations of BCNF Decomposition

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at some limitations behind decomposition using Boyce Codd Normal Form. So previously, we talked about how we, when we’re decomposing our tables, we need to be careful because if we’re not actually adhering to Boyce Codd Normal Form, and we’re decomposing our tables, ad hoc Li, we could actually have lossy decomposition, meaning we actually lose the original information, or at least, we lose, we don’t lose rows of data. But we can’t we can’t actually reconnect those rows of data together. So our joins become useless there. But there are some limitations behind Boyce Codd Normal Form. So Boyce Codd, normal form by itself and we’re decomposing according to it. Our decompositions are always lost less, which is a good thing, which is a good thing. But what about functional dependencies? So if we have this relation here, Professor projects departments, where we have our functional dependencies, Professor implies department and projects department implies professor. This is a Boyce Codd Normal Form violation because we do have a functional dependency. That is not a super key. Okay, so we want to decompose this. And so we have Professor departments, Professor department, and then we’ll have everything else, right, Professor department and everything else. So that’s Professor department, and we have Professor project. But what about this functional dependency here, Project department implies professor.

So this has no functional dependencies. But we lose that dependency, right? We lose this functional dependency project department no longer implies. Professor, right? Project department no longer implies professor. So this is a issue right? Or this is a problem with Boyce Codd Normal Form more of a limitation, right? We still don’t lose information, we still don’t lose information, we can actually join this back together and get our original table back. So what’s the general problem here? Right, what’s the general problem? Well, what about this right, what about this set of data? Right? We have Professor department Professor project, and we have Johnson COEs Robinson CIS Johnson recruitment, Robinson recruitment. So no problem here, right? Because our functional dependencies, local to the individual tables are satisfied, right? That’s fine. But if we pull all the data back into a single table again, ah, Project department implies Professor no longer actually holds, right? Because we have CDs recruitment, CDs, recruitment, and now no longer implies professor. Right, that no longer applies. Professor. So this is either a bad functional dependency, or we can try to attempt juice do more with our decompositions. How do we keep hold of those functional B? How do we keep a hold of those functional dependencies when we decompose our tables? So let’s take a look at how we might do this. So we lose dependencies, when in relation with a dependency x implies y is decomposed, And x ends up in one of the new relations and y ends up only and another. So if a functional dependency is split apart, we lose that functional dependency.

So that decomposition is we refer to it as not dependency preserving, it is lost less, but it is not dependency preserving. So the common form of this issue is a b implies c and c implies b Right? So remember our example. We had Professor implies department, and project department implies professor. So A, B implies c, and c implies B. So this is our limitation, right? This is our limitation with Boyce Codd Normal Form. Boyce Codd. Normal Form decomposition does not always preserve dependencies. Let’s take a look at this example. This is an example that I previously broke down using decompose using Closure sets. But if we attach I’m not going to go over the actual decompose Part, feel free to track, go down the tree here and follow this if you’d like. But this was decomposers enclosure sets. And so let’s take a look at our functional dependencies now, as a result, so we have, we have d implies a, so that holds that holds, we have c implies d, that still holds, we have f implies B, and that holds. But this functional dependency here, a b implies C, no longer holds. So again, another example of how Boyce Codd Normal form of decomposition may not keep all of your functional dependencies intact, it may not keep all of your functional dependencies intact. So, general goals here, why are we decomposing them? Right? Why are we decomposing them? If we lose functional dependencies as part of it? Well, big goal that we want to target here is eliminating anomalies, right anomalies. reduce redundancy, right? When we update and delete data, right? We don’t want to have to update and delete in multiple parts. Because that leads to inconsistency with data.

That is the big problem that we’re trying to solve here. Eliminate anomalies. We want to also be able to recover information when we decompose, right when we decompose a relation. We don’t want to lose data as results. So can we get the original one back? That’s good. But preservation of dependencies, can we enforce functional dependencies without performing joints? Right? Can we achieve this without performing joints? That is our general goals when we try to decompose our tables and there are ways that we can decompose typically if we choose to decompose into a specific or two particular groups of attributes to hold on to those functional dependencies. So generally speaking here, right, Boyce Codd, normal form decomposition, no anomalies. Awesome. Good green thumbs up there. recoverability of information. Also a good big thumbs up there as well. But unfortunately, sometimes we may lose dependencies as a result. So we can get most we can hit most of our goals, right? We can hit most of our goals with Boyce Codd Normal Form decomposition. And sometimes if we have multiple ways of being able to decompose that relation, sometimes we can hold on to those dependencies, but not always right. Not always