Database Normalization Lecture Notes

Introduction to Normalization

• Topic: Database normalization.
• Purpose: Avoid anomalies in relational databases.
• Previous Content: Anomaly issues discussed in a prior video.
• Goal: Understand and apply normalization rules to prevent anomalies and redundancies.

Normal Forms

Zeroth Normal Form (0NF)

• Description: All data is unnormalized in a single table.
• Example: Database of a computer company, hyperEDV.
• Issues: Data stored in one table with multiple fields (e.g., order number, name, DOB).

First Normal Form (1NF)

• Rule: All attribute values must be atomic.
• Process:
  • Break down attributes into atomic units.
  • Example: Split 'name' into 'first name' and 'last name'.
  • Address needs to be split into 'street', 'place', and 'postal code'.
• Consideration: Weigh necessity of splitting all fields (e.g., house number might not need splitting).

Second Normal Form (2NF)

• Rule:
  • Table must be in 1NF.
  • Non-key attributes must fully depend on primary key.
• Key Concepts:
  • Primary Key: Unique identifier for table entries.
  • Composite Primary Key: Multiple columns combined to form a unique key (e.g., order number and article number).
• Example Process:
  • Break original table into multiple tables (Order, Customer, Article, OrdersArticle).

Functional Dependencies

• Fully Functional Dependency: Non-key attributes must fully depend on the entire primary key.
  • Example: Order number and article number jointly determine 'number'.

Relationships in Relational Databases

1 to n Relationship

• Example: Between Customer and Order tables.
• Explanation:
  • Each order has one or no customers.
  • Each customer can have multiple orders.
• Implementation: Primary key on the customer side becomes a foreign key in the Order table.

n to n Relationship

• Example: Between Order and Article.
• Explanation:
  • An item can have multiple orders.
  • An order can have multiple articles.
• Solution: Requires an additional table to resolve the relationship (OrdersArticle).

Conclusion

• Normalization Steps: Follow the rules strictly to achieve higher normal forms.
• Importance: Ensures efficiency and reduces redundancy in database design.