Pertemuan 2 - Pemrograman Basis Data : Normalisasi 2-NF

Introduction

This tutorial provides a step-by-step guide on understanding and applying the principles of 2nd Normal Form (2-NF) in database normalization. 2-NF is crucial for organizing data in a way that reduces redundancy and improves data integrity. By the end of this guide, you will have a clear understanding of how to identify and convert a database schema into 2-NF.

Step 1: Understand the Basics of Normalization

Normalization is a process used in database design to minimize redundancy and dependency. It generally involves several stages, known as normal forms.

• 1st Normal Form (1-NF): Ensures that all columns contain atomic values and each entry is unique.
• 2nd Normal Form (2-NF): Builds upon 1-NF by ensuring that all non-key attributes are fully functionally dependent on the primary key.

Practical Tip

Always start by ensuring your database is in 1-NF before attempting to move to 2-NF.

Step 2: Identify Composite Keys

In 2-NF, it's important to identify if your primary key is composite (consists of more than one attribute).

• Example: If you have a table with a composite key consisting of OrderID and ProductID, these two columns together uniquely identify a record.
• Check if any non-key attributes depend on only part of the composite key.

Common Pitfall

Avoid leaving non-key attributes that depend on only a portion of a composite key, as this violates 2-NF.

Step 3: Eliminate Partial Dependencies

Once you have identified composite keys, the next step is to eliminate partial dependencies.

• If a non-key attribute is dependent on just part of a composite key, it should be moved to a separate table.

Example

Consider the following table:

| OrderID | ProductID | ProductName | Quantity | |---------|-----------|-------------|----------| | 1 | 101 | Widget | 10 | | 1 | 102 | Gadget | 5 | | 2 | 101 | Widget | 20 |

• ProductName is dependent only on ProductID, not on OrderID. Move it to a new table.

New Tables

1. Orders Table

   • Columns: OrderID, ProductID, Quantity

2. Products Table

   • Columns: ProductID, ProductName

Step 4: Verify 2-NF Compliance

After restructuring your tables, check that all non-key attributes are fully functionally dependent on the entire primary key.

• Ensure that no non-key attributes remain that depend on only a part of the primary key.
• Review your tables for further normalization if necessary.

Conclusion

In summary, achieving 2-NF involves understanding normalization basics, identifying composite keys, eliminating partial dependencies, and verifying compliance. By following these steps, you can greatly enhance the structure and efficiency of your database. Consider exploring higher normal forms (like 3-NF) for further improvements in organization and integrity.