Pertemuan 2 - Pemrograman Basis Data : Normalisasi 3-NF & 3.5-NF

Introduction

This tutorial explores the concepts of database normalization, specifically focusing on Third Normal Form (3-NF) and Boyce-Codd Normal Form (3.5-NF). Understanding these principles is essential for designing efficient and effective relational databases. This guide will walk you through the key steps and considerations for normalizing your database to these standards.

Step 1: Understand Database Normalization

• Definition: Database normalization is the process of organizing data to reduce redundancy and improve data integrity.
• Purpose: The main goal is to ensure that the data is stored logically and efficiently, which helps minimize update anomalies and ensures data consistency.
• Forms of Normalization: Familiarize yourself with various normal forms, starting from First Normal Form (1-NF) to Boyce-Codd Normal Form (3.5-NF).

Step 2: Achieve Third Normal Form (3-NF)

To achieve 3-NF, follow these steps:

1. Ensure 1-NF:

   • Each column must have atomic values.
   • Each record should be unique, typically achieved by having a primary key.

2. Achieve Second Normal Form (2-NF):

   • Eliminate partial dependencies; all non-key attributes must be fully functional dependent on the primary key.

3. Eliminate Transitive Dependencies:

   • Ensure that non-key attributes are not dependent on other non-key attributes.
   • If a non-key attribute depends on another non-key attribute, it should be moved to a separate table.

4. Example of 3-NF:

   • Consider a table storing student information where a student's major is dependent on their advisor. This would need to be separated into different tables to remove the transitive dependency.

Step 3: Understand Boyce-Codd Normal Form (3.5-NF)

To achieve 3.5-NF, follow these additional considerations:

1. Identify Determinants:

   • In 3.5-NF, every determinant must be a candidate key.
   • A determinant is any attribute on which some other attribute is fully functionally dependent.

2. Eliminate Any Remaining Anomalies:

   • Check if there are any functional dependencies that violate the 3.5-NF rules where a non-key attribute can determine another non-key attribute.

3. Practical Example:

   • If you have a table where "Course" can determine "Instructor" but "Instructor" is not a primary key, you will need to separate "Instructor" into another table linked by a key that uniquely identifies it.

Conclusion

In this tutorial, we covered the essential steps to normalize a database to Third Normal Form (3-NF) and Boyce-Codd Normal Form (3.5-NF). By following these normalization steps, you can design databases that are more efficient and less prone to anomalies.

As a next step, consider applying these normalization techniques to your own database projects. Analyze existing tables and identify areas where normalization can improve data integrity and efficiency.