Secondly: I have still problem with understanding the Second normal form. A different student ID means a different student name. So in your design I am not allowed to have 2 students with same names? What will happen when this, not so uncommon situation occurs?
Glad you like it Wojtek! Yes, in this design you can have two students with the same name. They would have different IDs and different rows in the table, as two people with the same name is common like you mentioned. This is the best explanation on why and how to normalize Tables… excellent work, maybe the best explanation out there….
Hi, Thanks for the post. That is exactly what I was looking for. But I have a question, how would I insert into student address and teacher address. Best regards. This is amazing, very well explained. Simple example made it easy to understand. Thank you so much! Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email.
This site uses Akismet to reduce spam. Learn how your comment data is processed. Database normalisation is a concept that can be hard to understand. Table of Contents Click on the following headings to be taken to the relevant place on this page.
What is Database Normalization? What Are the Normal Forms? What Is Database Normalization? Why Normalize a Database? So why would anyone want to normalize their database?
Insert Anomaly An insert anomaly happens when we try to insert a record into this table without knowing all the data we need to know. Update Anomaly An update anomaly happens when we want to update data, and we update some of the data but not other data. Ideally, we would only update the value once, in one location. Delete Anomaly A delete anomaly occurs when we want to delete data from the table, but we end up deleting more than what we intended. What Are The Normal Forms? What Is First Normal Form?
Our Example Database We have a set of data we want to capture in our database, and this is how it currently looks. How can we normalise this? We start with getting the data to First Normal Form. To apply first normal form to a database, we look at each table, one by one, and ask ourselves the following questions of it: Does the combination of all columns make a unique row every single time? What field can be used to uniquely identify the row? The second question says: What field can be used to uniquely identify the row?
Any other field? Related: The Complete Guide to Database Keys When we create a new primary key, we can call it whatever we like, but it should be obvious and consistently named between tables. This is our new table: Student student ID , student name, fees paid, date of birth, address, subject 1, subject 2, subject 3, subject 4, teacher name, teacher address, course name This can also be represented in an Entity Relationship Diagram ERD : The way I have written this is a common way of representing tables in text format.
This data is now in first normal form. What Is Second Normal Form? The rule of second normal form on a database can be described as: Fulfil the requirements of first normal form Each non-key attribute must be functionally dependent on the primary key What does this even mean?
Student student ID , student name, fees paid, date of birth, address, subject 1, subject 2, subject 3, subject 4, teacher name, teacher address, course name Are all of these columns dependent on and specific to the primary key?
Each fees paid value is for a single student. More than one student can be enrolled in one subject. How can we resolve those we marked as No? Subject First, the subject 1 column. Subject subject ID , subject name This means we have a student table and a subject table. We can do this for all four of our subject columns in the student table, removing them from the student table so it looks like this: Student student ID , student name, fees paid, date of birth, address, teacher name, teacher address, course name But they are in separate tables.
How do we link them together? Teacher The next column we marked as No was the Teacher Name column. Teacher teacher name, address Just like with the subject table, the teacher name and address is not unique. Course course ID , course name We now have our tables created from columns that were in the student table. Foreign Keys in Tables We have four separate tables, capturing different pieces of information. How can we keep track of this? We use a concept called a foreign key. Here are our two tables so far: Student student ID , student name, fees paid, date of birth, address Subject subject ID , subject name Teacher teacher ID , teacher name, teacher address Course course ID , course name To link the two tables using a foreign key, we need to put the primary key the underlined column from one table into the other table.
We need to either: Add the course ID from the course table into the student table Add the student ID from the student table into the course table But which one is it? In this situation, I ask myself a question to work out which way it goes: Does a table1 have many table2s, or does a table2 have many table1s?
So, if we substitute table1 and table2 for course and student: Does a course have many students, or does a student have many courses? This means that the course ID goes into the student table. Depending on the scenario, they could be related in one of a few ways: A student can have one teacher that teaches them all subjects A subject could have a teacher than teaches it A course could have a teacher that teaches all subjects in a course In our scenario, a teacher is related to a course.
Does a teacher have many courses, or does a course have many teachers? What about the subject table? Does a subject have many students, or does a student have many subjects? The answer is both. How is that possible? This makes it hard to maintain and is very prone to errors. Many to Many Relationships A many to many relationship is common in databases.
We use a joining table. It works like this. Each record in the row would look like this: student ID subject ID 1 1 1 2 2 2 2 3 2 4 3 5 Each row represents a relationship between a student and a subject. Student 1 is linked to subject 1. Student 1 is linked to subject 2. Student 2 is linked to subject 2. And so on. This has several advantages: It allows us to store many subjects for each student, and many students for each subject.
It separates the data that describes the records subject name, student name, address, etc. It allows us to add and remove relationships easily. It allows us to add more information about the relationship. We could add an enrolment date, for example, to this table, to capture when a student enrolled in a subject. An ERD of these tables looks like this: This database structure is in second normal form. What Is Third Normal Form? The rule for this is: Fulfils the requirements of second normal form Has no transitive functional dependency What does this even mean?
What is a transitive functional dependency? We need to check if this is the case for any of our tables. Student student ID , course ID , student name, fees paid, date of birth, address Do any of the non-primary-key fields depend on something other than the primary key? Student determines the address ZIP code which determines the suburb. So, how can we improve this? Teacher teacher ID , teacher name, teacher address The teacher table also has the same issue as the student table when we look at the address.
The course name is dependent on the course ID. So, what does our database look like now? Fourth Normal Form and Beyond Fourth normal form is the next step after third normal form. What does it mean? It needs to satisfy two conditions: Meet the criteria of third normal form. There are no non-trivial multivalued dependencies other than a candidate key. So, what does this mean? Both the student and teacher table have these What if a student moves addresses? Do we update the address in this field?
If we do, then we lose the old address. If an address is updated, is it because they moved? Or is it because there was an error in the old address? What if two students have the same street address. Are they actually at the same address? What if we update one and not the other? What if a teacher and a student are at the same address? What if we want to capture a student or a teacher having multiple addresses for example, postal and residential? This is a multivalued dependency. We can solve this by moving the address to a separate table.
The address can then be linked to the teacher and student tables. Address address ID , street address, address code ID In this table, we have a primary key of address ID, and we have stored the street address here. There are a few enhancements you can make to this design, but it depends on your business rules: Combine the student and teacher tables into a person table, as they are both effectively people, but teachers teach a class and students take a class.
Page 1. Story Dende's Sacrifice Don't worry Dende, everything's gonna be just fine! Trunks x Goku thang. Sent in by Pitdragon. Download Zip file of this story. Just say no! By Cowboy Story What!? Freeza's Gay!? Aw c'mon, don't act all surprised! By Mereculus. Story Dr. Sent in by Kirk Downey Download Zip file of this story. Story Piccolo's Auction Goku's fun day at Piccolo's auction. How much is that Tien there? By Kirk Downey Download Zip file of this story.
Story Trunks By Burndit. Page 1 - Page 2 - Page 3. Story Skittles Taste the Rainbow Whee, Vegetto has ben turned into a Skittle! By ChibbiTrunks Download Zip file of this story. Story Freeza's Suicide Don't do it, man! Repaint for the payware Carenado A The repaint is dedicated to Johnny Rosario, who has a awesome website that is dedicated to repaints. Check it out, stay awhile and enjoy. Repaint by Kevin Michael. Two textures for the same aircraft. The difference concerns the rear port side door.
Repaint only for the payware Carenado Cessna By Kevin Michael.
0コメント