Archives for RDBMS Concepts category

Normalization

*  In relational database design, the process of organizing data to minimize duplication.
*  Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
*  The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just
one table and then propagated through the rest of the database via the defined relationships.”
“Normalization” refers to the process of creating an efficient, reliable, flexible, and appropriate “relational” structure for storing information. Normalized data must be in a “relational” data structure.
1st Normal Form
1st Normal Form ==> There should be no repeating groups.
For instance consider the following structure
Student Table

StudentID (PK)

Name

Dob

Advisor

Advisor Telephone

Advisor Address

Course

Course Instructor

Problem to Fix : A student can take multiple courses and hence repeating groups.
Soultion : Make a separate table for course & link it to student table using StudentID. This will results in the below design
Student Table

StudentID (PK)

Name

Dob

Advisor

Advisor Telephone

Advisor Address

Courses table

ID (Course ID) (Primary Key -> Composite Key ID + StudentID)

Course

Course Instructor

StudentID

2nd Normal Form ==> No Non-Key field should depend on the part of the primary key.
Problem to Fix : Here in Courses table the primary key is a composite key ID + StudentID. (student ID is not unique in itself, as one student may take multiple courses; similarly, course ID is not unique in itself as many students may take the same course; however, each student will only be taking a particular course once at any one time, so the combination of student ID + course ID gives us a unique primary key). And a non-key field Course Instructor is depending on the Course ID alone, which is only a part of the primary key. Solution : To fix this we create a third table, to get the below structure for our database.
Student Table

StudentID (PK)

Name

Dob

Advisor

Advisor Telephone

Advisor Address

Courses table

ID (Course ID) (PK)

Course

Course Instructor

Student Courses table

Student ID (Primary Key ==> Composite Key)

Course ID

3rd Normal Form ==> No fields may depend on other non-key fields. Ie each field in a record should contain information about the entity that is defined by the primary key.
Problem to Fix : Here in student table, the primary key is Student ID & the non-key fields like Advisor Telephone & Advisor Address cannot be defined with the Primary key. Solution : To fix this we need to create a 4th table Advisor table, to get the below structure.
Student Table

StudentID (PK)

Name

Dob

Advisor ID

Courses table

ID (Course ID) (PK)

Course

Course Instructor

Student Courses table

Student ID (Primary Key ==> Composite Key)

Course ID

Advisor table

ID (Advisor ID) (Primary Key)

Advisor

Advisor Telephone

Advisor Address

The database is in 3rd normal form now