Hey everyone, welcome to part four of topic five in our database class. And in this video, I'm going to provide several different examples of denormalization. In this case, we'll do it just based on an entity relationship diagram. So this is a scenario that is familiar to you, even though perhaps you have never thought of it from a database perspective. And it is an example of a way in which a university might keep track of its students and classes and enrollments. So which students are taking which classes? So let's briefly just take a tour of this database design. Here in the upper left, we have a student table where we're storing information about students. And I've done my best just to keep this very simple, right? So it's student ID, primary key, first name, last name, no big deal, no additional attributes. Over here in the upper, we have a course table. So we give each course a unique ID. And then maybe we have a name and a description of the course. Easy enough. But then we get into things that are a little more complex because it isn't at anything but the smallest universities. It is a common thing to have multiple sections of a class. So what this means then is we need a way of keeping track of the sections of each class. So one of the things that we could do then, like with this design here, is we could keep track of these different course sections by recording the course ID. And then we have some section ID. So section number one, I store that information here as a composite primary key course ID. Serves as a foreign key link back to its parent table, which makes this a identifying relationship, right? And an ID dependent week entity, things that we learned during our last topic. So we can then have multiple course sections and we can store additional non key information in this table that would be of interest as well. Like the location of the class, maybe it's online on zoom, maybe it's in a classroom, whatever we can store that here. And then maybe we have a maximum enrollment. It is the maximum number of students that are allowed to enroll in the class. So when you go through the enrollment process, when you're enrolling in classes for each semester, you each class has an a maximum enrollment. We can't let 718 people take this database class, which just wouldn't be a good experience for you or me to do that. So we set it up for limit on the enrollment. And obviously when you're going through the enrollment process, maybe classes are full. That's because a total number of students in the class has met the enrollment limit. So then we have this additional table over here in the lower left that actually keeps track of which students are taking which sections of which classes. So each of you has a class schedule. And with this kind of design that class schedule would be tracked in the university system through this enrollment table. So we would keep track of things like, okay, I say, student number one is enrolled in sections 4, 5 and 6. And we could then look over here and figure out what courses are associated with those sections. And in that way, we could know which classes a student is taking. And we also have another non key attribute in here. As you can see, we've got this great attribute. This one you'll note is not bold faced. So null values are allowed. And at the end of a semester once a final grade has been assigned, we can store that grade in here. So maybe we store it as a letter grade. Maybe we store it as a certain number of GPA points, whatever it may be, we store that in here. So at the same time, if you're currently taking a class, but you don't yet have a final grade, that value can be null. And we can use that null value as a flag or a signal to indicate that these are the classes that a student is currently taking that have not yet been completed. So here's a lovely four table design. And hopefully all of this from a design perspective makes sense to you. It's fully normalized allows us to keep track of all of our students, all of our courses, all of our course sections. And which students are enrolled in which course sections. And that I would have support the needs of a university or a large secondary school, something like that. And that's all fine. However, we're talking about denormalization in this lecture. So let's introduce some problems here that will require us to do some denormalization. Let's say to begin that one of the things that we would like to know. Is how many students are currently enrolled in a course section. And so this is a good thing to know. How do I know if the class is full? Well, I need to know how many students are currently in that section of the class and compare that to our maximum enrollment. So with this current design, we can answer that question. We can say, hey, database, how many students are enrolled in the course that has the section number seven. And the database would be able to answer that by looking over here in the enrollment table. And counting the number of rows in that table where the section ID is equal to seven in this example. So if I want to know what is the total enrollment for section number seven, the database needs to scan through this enrollment table. And count the number of rows and then return the result. And that's fine, but there's a better way. Well, there's a way that would be allow the database answer the query more quickly. And that is if we use one of our denormalization techniques. So what we could do then is that we could store that answer to that question directly in the database in the form of a new attribute. And that way, whenever someone is, I don't know, say it's the time of the of the year when people are enrolling in classes, we would know immediately how many people are already enrolled in the class without having to count the number of related rows in the enrollment table. So maybe we would add, I don't know, an additional attribute out here in our course section table. And we call it like, I don't know, current enrollment or something like that. Sorry for those of you who learn British English here in North America, we use two L's in enrollment. I know that in British English, they use just use one. So if that looks a little weird to you, I apologize. Now what we've done here is we have added a new attribute to our course section table, right? Because the total current enrollment applies to the course section. So that's why we're putting it in the course section table. And what we would do then is just store in this table the number of students that are currently enrolled in whatever class section this is. And if I want to have some sort of information system that relies on this, like the university's registration system, the class registration system, I can now easily determine if a class is full by comparing these two values. Right, is current enrollment less than max enrollment? If the answer is yes, then I know that there is at least one seat available in that class section. And I don't have to scan through this entire table and count the number of students that are already enrolled in that section in order to answer that question. So it's much less work for the database, but by doing this, we need to implement some kind of new business rule. In order to ensure that the value stored in this attribute is kept up to date, that is that it's accurate. So how would we keep that value up to date? Well, we could create some sort of new business rule and we would do something like, okay, whenever a student enrolls in a class, or from a database perspective, what would happen is that we would add a new row to this table. Right, so if you are student number one and you're enrolling in the class section number seven, we're going to add a new row to this table with values of one for student ID and seven for section ID. And that is the process of enrolling the student in the class. Right, now what we could do is we could set up something called a trigger, which is an action that the database will take in response to an event. The event in this case is a new row has been added to this table. And what we would do is we would set up a trigger that says, okay, whenever that event occurs, whenever a new row is added to this table. We update the value of current enrollment over here. Okay, so if let's say a class currently has 24 people in it, the value stored in this current enrollment attribute would be 24. And then let's say that you enroll in that class, so we add a new record to this enrollment table over here. And in response to that event, we add one. Right, so this current enrollment value over here to keep this up to date and accurate. So here's an example of adding an attribute to a table that allows us to avoid having to do a join. Right, we don't have to scan through this enrollment table in order to answer the question of is a class full or how many students are currently in a class. Right, we can get those answers directly just by looking in one place. I can just look in here and get those answers directly with this kind of design. All right, let's see another example. We'll do one that's more student focused. So let's say that one of the things that people are interested in as students is their grade point average. What is your GPA? Okay. Now let's say that we're curious to know what the GPA is for student number 10. Okay, and let's assume that we are storing grade points in this grade attribute here instead of letter grades that we could do it either way, but with letter grades, it would require a little more work. So your grade, if you get an A would be like 4.0. Right, if you get a B would be 3.0. If you get a A minus it would be 3.7. Right, so we just store those values in here. And with that concept in mind, if we wanted to know what a student's overall GPA was, we would need to scan through this enrollment table again. Well, first we would say we look up the student by name, we get their name, we get this associated student ID, we follow this relationship line down to the enrollment table, and then we have to scan through that table and find all records associated with our students of interest. And then we could, I don't know, do the average of the GPA or the grade points. It's a weighted thing where you have like a certain number of units per class or whatever. But anyway, we could figure it out. We just have to do the math at runtime. Right, so the database would have to scan through this table, find all the associated records, and then do the average in order to calculate the GPA, and then return the result to whatever person or application had submitted that to questions in the database. And that's fine, so fully normalized design, the database is able to answer the question with the current design. However, there's a way that we could answer that question more quickly, and that is by using our denormalization techniques. So in this case, again, what we could do is add an additional attribute, and we would probably add that to the student table. If we're interested in the student's current GPA, and I could add like a current GPA attribute out here, and then the idea would be that we just keep that value up to date. Right, so I'm storing the students current GPA here, even though I could calculate it at any time. It's just that calculating it is a computationally intensive task. So instead of generating this, or calculating that value every time that a student wants to know, or like a staff member wants to know a student's GPA, we can just store the value directly. But again, by doing this, we now have the risk of the values stored in this new attribute of not being accurate. So we need to introduce some new business rule that will keep each student's current GPA up to date. And then you just have to imagine, okay, how would we do that? Well, in this case, again, we could use like a database trigger to do that. And we would say something like whenever the value of this grade attribute changes in the enrollment table, then recalculate the students current GPA and store the result up here. In so doing, we could rest reasonably well assured that the values stored in the current GPA table would always be up to date. Cool. So just some additional examples of normalization strategies and things that we have available to us. Add additional attributes, use business rules to keep the values of those new attributes up to date. And in so doing, we can save the database a lot of work and no longer has to scan through this enrollment table to calculate the GPA for a student. All right, and no longer has to scan through this enrollment table to figure out how many students are enrolled in a current class. We just store those values directly in our new attributes that we added out here. So that was a couple of examples of denormalization. So we saw one here of merging tables together. We saw a couple here of adding new attributes to avoid mathematical calculations or avoid the necessity of having to do a join. And hopefully you can appreciate and understand how that would allow the database to answer those types of questions more quickly. But do remember that whenever we're doing denormalization, it is motivated by a specific and defined need. So if a fully normalized design like this would work well and we're not experiencing any performance issues, this is the preferred design. It's much, much better to have a fully normalized design and to have a design that has been denormalized because we don't have risks here of any modification anomalies. So we don't have risks of any bad data creeping in based on the design of the database. And we don't have any risks of values being out of date. Right, with this kind of design, if I want to know what is the current number of students enrolled in a class. And I do that calculation at runtime. The answer that I get will always be accurate at that moment in time. Same thing for calculating a student's CPA. If I use this design, I know that the, yes, I have to do the work to calculate it when the query is submitted to the database, but I know that the result is accurate as of that moment in time. And if I use an approach like what we saw previously, the only way that I can feel comfortable. That these values are up to date is if I create new business rules and have some mechanism of enforcing them to keep the values in these new attributes up to date. Now, one last thing before we move on, if you remember, I said that whenever we do denormalization, we are always introducing redundant information into the database. Okay, so that's going to require additional storage space. And hopefully the storage space thing is obvious, right? If I have new attributes here. We have to store those values somewhere on the system, like on the database server. So it's going to take up more space than if we just used this design. However, sometimes the notion of redundancy is a little tricky to understand. So to understand why if I store somebody's GPA in the student table here, why is that redundant information? It's because that information could already have been obtained from the database without adding this additional attribute. With this design here, I can still get the students current GPA. Right, the information is already stored there. I just have to calculate it at runtime. So by adding these additional attributes, I am introducing redundant information. It's just that that information exists at a higher level of aggregation. So just if I keep track of all of the sales, individual sales transactions at a store. I want to know what were my total sales for the day. That information exists in the database. I can just calculate it by adding all of the individual sales together. But I can aggregate it up to a higher level, like by doing a sum, right? And store that in the database as well. But I just need to acknowledge and understand that by storing that sum, I am storing redundant information. And that information exists in another form already in the database.