What are the benefits of using normalization? Give an example of relation (with taken few records) which does not in first normal form, convert the relation stepwise till the third normal form using Functional dependency.
Answers
Answer:
What Is Normalization?
Normalization is the branch of relational theory that provides design insights. It is the process of determining how much redundancy exists in a table. The goals of normalization are to:
Be able to characterize the level of redundancy in a relational schema
Provide mechanisms for transforming schemas in order to remove redundancy
Normalization theory draws heavily on the theory of functional dependencies. Normalization theory defines six normal forms (NF). Each normal form involves a set of dependency properties that a schema must satisfy and each normal form gives guarantees about the presence and/or absence of update anomalies. This means that higher normal forms have less redundancy, and as a result, fewer update problems.
Normal Forms
All the tables in any database can be in one of the normal forms we will discuss next. Ideally we only want minimal redundancy for PK to FK. Everything else should be derived from other tables. There are six normal forms, but we will only look at the first four, which are:
First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)
Boyce-Codd normal form (BCNF)
BCNF is rarely used.
First Normal Form (1NF)
In the first normal form, only single values are permitted at the intersection of each row and column; hence, there are no repeating groups.
To normalize a relation that contains a repeating group, remove the repeating group and form two new relations.
The PK of the new relation is a combination of the PK of the original relation plus an attribute from the newly created relation for unique identification.
Process for 1NF
We will use the Student_Grade_Report table below, from a School database, as our example to explain the process for 1NF.
Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
In the Student Grade Report table, the repeating group is the course information. A student can take many courses.
Remove the repeating group. In this case, it’s the course information for each student.
Identify the PK for your new table.
The PK must uniquely identify the attribute value (StudentNo and CourseNo).
After removing all the attributes related to the course and student, you are left with the student course table (StudentCourse).
The Student table (Student) is now in first normal form with the repeating group removed.
The two new tables are shown below.
Student (StudentNo, StudentName, Major)
StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
How to update 1NF anomalies
StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
To add a new course, we need a student.
When course information needs to be updated, we may have inconsistencies.
To delete a student, we might also delete critical information about a course.