Database Normalisation Second Normal Form

This video is about database normalisation, specifically normalising a database to second normal form. It follows on from the video about first normal form by working through the same example. The technical definition of second normal form states that the data must be in first normal form and a relation must not contain any partial dependencies. What this means is that the data must be in first normal form and each non-key field must be about the same thing as the primary key. Also, each table must contain data about only one type of thing. This table is in first normal form. Well, debatably. Let's remove any argument by separating the first name and last name into separate columns. So now the first condition for second normal form is satisfied. That is, the table is already in first normal form. Although it's not necessary for second normal form, let's also clean up the data a little. Let's give David a last name and let's suppose there is only one Mervyn Drake after all. You can see that there's still an issue with redundant data here. For every course a student does, the student's details are duplicated. This is a big clue that the table is not yet in second normal form. Is that a problem? Well, it could become one. Imagine there were thousands of records. There'd be even more duplication. And imagine one of the students got married and you wanted to update their marital status. You'd need to make exactly the same change in several places. This is how inconsistency errors can start to creep in. Let's add some more columns to exaggerate the problem. There's more information about each course now, which means more duplication. And there's also the grade that each student achieved when they completed a course. By definition, to be in second normal form, a relation, in other words a table, must not have any attributes that are partially dependent on the primary key. A much simpler way of saying that is a table should only contain data about one type of thing. Or to be slightly more technical, a table should only contain data about one type of entity. If it's about more than one type of entity, the data need to be split into separate tables. So what's this table about? What things does it describe? For a start, it contains various data which are just about students. An ID, first name, last name and marital status. When you examine the data, you can see that the ID is unique for each student and that the first name, last name and marital status are each about the same thing that the ID is about. This table uses a simple sequential number for each ID, but it could have used something more personal like a student union number or a national insurance number, something that will always belong to a particular student. On that basis, if you change the ID, then the record is all about a different student. So you need to change the first name, last name and marital status as well. When you have to change one thing because you've changed another, this is known as a functional dependency. The first name, last name and marital status are functionally dependent on the ID and it's the only thing that they are functionally dependent on. The table also contains various data just about courses. The course title, fee and qualification. Examine the data and you can see that the course title is unique for each course. The fee and qualification are both about the same thing as the course title. Change the course title for a course and it becomes a different course. So you need to change the fee and the qualification as well. They are functionally dependent on the course title and it's the only thing that they're functionally dependent on. And finally, and this might not be immediately obvious, this table also contains data about each student and course combination. Look at the grade column. Each student has a grade for each course they attended. Kevin Drum, for example, did three courses, so he has three separate grades. Each of Kevin's grades is not just about Kevin. It's also about the course he did to achieve that grade. Each grade is partially dependent on the student and partially dependent on the course. Now consider this table's primary key for a moment. At first sight, it might not appear to have a primary key at all. The ID column is unique for each student, but it's not unique for each record. So by definition, the ID column is not the primary key of this table. The course title column is unique for each course, but it's not unique for each record. So it can't be the primary key of this table. However, the combination of ID and course title is unique for each record. Together, these two fields serve as a composite primary key. The primary key for each record in this table contains a piece of information about a student and a piece of information about a course. In fact, it tells you which students are doing which courses, and you need to retain this information. So here are the three tables you're going to need. The students table only contains data about students. Each student occurs only once in the students table, and each student record has a unique ID. The unique ID is the primary key, and in this table, each ID is only about a student. Every other attribute in the students table is only about a student. To put that in technical jargon, every non-key attribute is functionally dependent on the primary key, and only the primary key. There are no partial dependencies. The courses table only contains data about courses. Each course only occurs once in the courses table, and each course has a unique course title. The course title is the primary key in this table, and it's only about a course. Every non-key attribute in the courses table is also only about a course. In other words, every non-key attribute of the courses table is functionally dependent on the primary key, and only the primary key. There are no partial dependencies. The student course table is about which students enrolled on which courses, and how well each student performed. It only contains information about each student course combination. The combination of ID and course title is unique in the student course table, so this table has a composite primary key. Every non-key attribute in this table, namely the grade, is about the student course combination. The grade is functionally dependent on the primary key, and only the primary key. There are no partial dependencies here. All of these tables are now in second normal form. The student course table is sometimes called a bridging table or a link entity, because it serves to join the three tables together. Overall, there are fewer individual data items being stored than when there was a single table in first normal form. The database is now more space efficient, and this efficiency will become even more significant as the number of students and courses grows. Not only that, but if it becomes necessary to change something like a student's marital status or the fee for a course or a grade, this only needs to be changed in one place, so there's less chance of inconsistent data. To summarise, in technical terms, in order for a table to be in second normal form, the data must already be in first normal form, and a relation must not contain any partial dependencies. In simpler terms, the data must be in first normal form. Each non-key field must be about the same thing as the primary key. Each table must contain data about only one type of thing, only one type of entity. You can see how important it is to know what the data means in order to make these decisions.