203 ) Normalization with examples and problems

 Normalization 

=============================

 --------------------------------------------------------------------------------------------------------------- 

1NF  - There should not be multiple values in each cell

( 3 courses of student in same cell ) 

 --------------------------------------------------------------------------------------------------------------- 

2NF  - in 1NF and  All non Non key attributes should be

           dependent on key attributes and all.

 policy table (Policy_ID, Agent_ID, Policy_Type, Agent_Name, Premium_Amount)

Composite Primary Key: (Policy_ID, Agent_ID)

Agent_Name partially depends only on Agent_ID only not on policy_id

 so seperate table shd be there from agent details 

     policy table (Policy_ID, Agent_ID, Policy_Type, Premium_Amount)

    Agent ( Agent_ID,  , Agent_Name, )

 --------------------------------------------------------------------------------------------------------------- 

3NF  -  It should be in 2NF           

           - And  there should not be any transitive dependency
(Policy_ID (PK) ,Customer_ID , Customer_Name, Customer_City)

-   Customer_Name and Customer_City depend on Customer_ID, not directly on the primary key (Policy_ID). 

so there shd be seperate table for customer details 

        Policy table ( Policy_ID (PK) ,Customer_ID  ) 

        customer table ( Customer_ID , Customer_Name, Customer_City)

 --------------------------------------------------------------------------------------------------------------- 

4NF -  Avoids multivalued dependency like 

 -------------------------------------------------------------------

 (Course_ID, Instructor_ID, Textbook_ID)

----------- -------------------------------------------------------------------

Primary Key / Super Key: (Course_ID, Instructor_ID, Textbook_ID) (The entire composite combination acts as the key since a course can have multiple instructors and multiple assigned textbooks).

-------------------------------------------------------------------

Table Names for the 4NF Structure : 

-------------------------------------------------------------------

    Table 1: Course_Instructors (Columns: Course_ID, Instructor_ID)

    Table 2: Course_Textbooks   (Columns: Course_ID, Textbook_ID)

-------------------------------------------------------------------

----------------------------------------------------------------

PROBLEMS WITH NORMALIZATION

----------------------------------------------------------------

• The Problem: Normalization forces heavy, continuous JOINs across multiple tables, slowing down searches and overloading the database.

• The Fix: ( CANONICAL MODEL ) Run a query once a day to pre-calculate and save the results into a summary table.

• Your App: Instead of joining tables live, your app just queries that single summary table instantly.

----------------------------------------------------------------