Database Normalisation: Second Normal Form

partial dependency database This is a topic that many people are looking for. bluevelvetrestaurant.com is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, bluevelvetrestaurant.com would like to introduce to you Database Normalisation: Second Normal Form. Following along are instructions in the video below:

“Video is about database. Normalization specifically normalizing a database to a second normal form. It it follows on from the video about first normal form by working through the same. 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 lastname 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 moving drake after all you can see that there s still an issue with redundant data here for every course. A student does the students 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 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 function be 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 tables 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 attributes in the courses table is also only about a course in other words.

Every non key attributes 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 attributes 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 linked 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 summarize 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 ” ..

Thank you for watching all the articles on the topic Database Normalisation: Second Normal Form. All shares of bluevelvetrestaurant.com are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
description:

tags:

Leave a Comment