Third Normal Form vs BCNF

Video Transcription

Welcome back everyone. In this video, we’re going to be talking more about the third normal form. And so just read your reiterates our statement that we’ve seen, so far every non key attribute must provide a fact about the key the whole key and nothing but the key. This was famously famously done by William Kinte, who was the database researcher, but this really, really fills out our need for third normal form right we have any any non prime attributes must not be a functional dependency right if we have a non prime attribute that is a functional dependency or implies another column that is not the key, then we have an issue we need to split that out into its own table. So, as a refresher right third normal form, if a relation R is in third normal form, if for every non trivial functional dependency in our where a one through a n implies B, then A one through a n must be a super key if it is not or it is not part of the key then it is not in third normal form. So, if the functional dependency the left hand side of a functional dependency is not a super key, or it is not part of a key, then it is not in third normal form. So let’s talk about some differences here with third normal form versus Boyce Codd Normal form because we’ve covered Boyce Codd Normal Form quite a bit.

And remember, a relationship is a relation R is in Boyce Codd Normal Form, if for every non trivial functional dependency a one through a n implies B, then A one through a n is a super key. Boyce Codd Normal Form is slightly stronger than third normal third normal form, right. So if we bring up this picture here, we have Boyce Codd Normal Form is deeper into that image then third normal form. We have fourth and fifth up here that would be stronger than Boyce Codd Normal Form. fourth normal form has no multivalued dependencies. And fifth normal form says that non trivial join dependencies are implied by candidate keys. But like I mentioned before, we’re not going to cover fourth and fifth normal forms. But big big picture item here that we want to remember is that Boyce Codd Normal Form is slightly stronger than third normal form. An example of this is with this example here. So we have a relationship ABC with a B implies c c implies b remember, this is the example that we had that when we decompose this in Boyce Codd Normal Form, we lose functional dependencies or we can lose functional dependencies, but this here is in third normal form, this is in third normal form, because we have no non trivial functional dependencies or that are not part of a key right that are not part of the key b Right. So c implies b b as part of the key and so therefore, therefore, this is in third normal form, but B is not a super key, right this is c implies B is not a super key. Therefore it is not in Boyce Codd Normal Form. So we have a relation that is third normal form, but not in Boyce Codd Normal Form.

So what does this mean for our decompositions? Right? So we we now know that Boyce Codd Normal Form is the stronger normalization. But with third normal form, we can still recover all of our information that we that we have in the original after we’ve decomposed. And Aha, we do preserve dependencies now. Right this is a big benefit of third normal form versus Boyce Codd Normal Form is that we are able to preserve dependencies however, however, third normal form can still have anomalies, right? We can still have anomalies. So that is a big thumbs down for third normal form. So some benefits right over the over Boyce Codd Normal Form, particularly with this right with Boyce Codd Normal Form, we’re not guaranteed to preserve all of our functional dependencies. But with Boyce Codd Normal Form, we do not have any anomalies, right. So we have no anomalies with Boyce Codd Normal Form, but with third normal form. There might still be anomalies as part of it. But some practical advice here. We’re going to add For Boyce Codd Normal Form, but settle for third normal form. And all practicality for third normal form for most databases for most use cases is going to be good enough. But if you really want to focus down on getting a really well formed database, along with, you know, along with lots of extra, like if you have a lot of data if this is going to be a really large database, you know, obviously Boyce Codd Normal Form is going to help you out there by reducing a lot of those data anomalies. Third Normal Form is is perfectly fine for most use cases, especially if the database is on a much smaller scale. But that’s going to conclude most of our discussion on design, particularly around normalization. So we will have some more discussions on designing tables, not necessarily particularly around normalizing them, but how we might organize and design tables to adhere to certain data, data relationships.