Skip to content

normalization_summary.md

Latest commit

 

History

History
149 lines (113 loc) · 5.49 KB

normalization_summary.md

File metadata and controls

149 lines (113 loc) · 5.49 KB

Mobile Specs Database Normalization Summary

Database Connection Status

✅ Successfully connected to MySQL server

  • Host: localhost:3306
  • User: root
  • Database: mobile_specs
  • pass: bbbb
  • All tables created and populated with sample data

Normalized Schema Overview

The original CSV with 67+ columns has been normalized into 15 related tables following 3NF and BCNF principles.

1. Core Entity Tables

brands (1NF → 3NF)

  • Purpose: Eliminates brand name redundancy
  • Key: brand_id (Primary Key)
  • Normalization: Removes partial dependency on brand name

phones (Main Entity)

  • Purpose: Core device information
  • Key: phone_id (Primary Key)
  • Foreign Keys: brand_id → brands
  • Unique Constraint: (brand_id, model) prevents duplicate models per brand

2. Lookup/Reference Tables (3NF compliant)

chipsets

  • Eliminates chipset redundancy
  • Groups architecture and fabrication with chipset name

operating_systems

  • Normalizes OS name, version, and UI interface
  • Unique constraint on (os_name, os_version)

display_types, storage_types, ram_types

  • Simple lookup tables for categorical data
  • Eliminates string repetition

3. Specification Tables (3NF/BCNF compliant)

phone_specifications

  • Dependencies: All non-key attributes depend only on phone_id
  • Foreign Keys: References chipsets, OS, display_types, storage_types, ram_types
  • BCNF Compliance: All determinants (phone_id) are candidate keys

display_specifications

  • Purpose: Groups all display-related attributes
  • Dependencies: All attributes functionally dependent on phone_id only
  • 3NF: No transitive dependencies

physical_specifications

  • Purpose: Physical dimensions and durability specs
  • Dependencies: height, width, thickness, weight all depend only on phone_id

camera_specifications

  • Purpose: Camera and video recording capabilities
  • Dependencies: All camera attributes depend only on phone_id

audio_features

  • Purpose: Audio jack and speaker information
  • Dependencies: Audio features depend only on phone_id

additional_features

  • Purpose: Miscellaneous features (GPS, sensors, SIM, etc.)
  • Dependencies: All features depend only on phone_id

4. Many-to-Many Relationship Tables

phone_colors

  • Relationship: One phone can have multiple colors
  • Junction Table: Resolves M:N relationship
  • Unique Constraint: (phone_id, color_name) prevents duplicates

phone_pricing

  • Relationship: One phone can have multiple price variants
  • Purpose: Handles different storage/RAM configurations with different prices
  • Dependencies: Price information depends on phone_id + variant

Normalization Compliance Analysis

First Normal Form (1NF) ✅

  • Atomic Values: All columns contain single, indivisible values
  • No Repeating Groups: Colors and price variants moved to separate tables
  • Unique Rows: Primary keys ensure uniqueness

Second Normal Form (2NF) ✅

  • 1NF Compliance: ✅
  • No Partial Dependencies: All non-key attributes depend on entire primary key
  • Example: In phone_specifications, RAM and storage depend on complete phone_id, not partial key

Third Normal Form (3NF) ✅

  • 2NF Compliance: ✅
  • No Transitive Dependencies: Non-key attributes don't depend on other non-key attributes
  • Example: Brand name doesn't depend on model; moved to separate brands table

Boyce-Codd Normal Form (BCNF) ✅

  • 3NF Compliance: ✅
  • All Determinants are Candidate Keys: Every determinant is a superkey
  • Example: In all tables, only primary keys determine other attributes

Sample Data Verification

Successfully Inserted Sample Records:

  1. Oppo K13 Turbo Pro (512GB)

    • 12GB RAM, 512GB storage, 7000mAh battery
    • Colors: Black Warrior, Purple No. 1, Knight Silver
    • Prices: ₹56,000 (512GB), ₹46,000 (256GB)

  2. Samsung Galaxy M36

    • 6GB RAM, 128GB storage, 5000mAh battery
    • Colors: Orange Haze, Velvet Black, Serene Green
    • Price: ₹29,500

Key Benefits of Normalization

  1. Data Integrity: Foreign key constraints prevent invalid data
  2. Storage Efficiency: Eliminates redundant brand names, chipset details
  3. Consistency: Single source of truth for each data element
  4. Scalability: Easy to add new phones without data duplication
  5. Query Flexibility: Can easily join tables for complex queries
  6. Maintenance: Updates to brand info only need single table change

Performance Optimizations

  • Indexes Created: On foreign keys and frequently queried columns
  • Appropriate Data Types: DECIMAL for prices, BOOLEAN for flags
  • Constraints: UNIQUE constraints prevent data inconsistencies

Sample Queries Demonstrated

-- Get phone specs with brand info
SELECT p.model, b.brand_name, ps.ram_gb, ps.internal_storage_gb, ps.battery_capacity 
FROM phones p 
JOIN brands b ON p.brand_id = b.brand_id 
JOIN phone_specifications ps ON p.phone_id = ps.phone_id;

-- Get phone colors and pricing
SELECT p.model, GROUP_CONCAT(pc.color_name) as colors, pp.price_unofficial 
FROM phones p 
LEFT JOIN phone_colors pc ON p.phone_id = pc.phone_id 
LEFT JOIN phone_pricing pp ON p.phone_id = pp.phone_id 
GROUP BY p.phone_id, pp.pricing_id;

The normalized database successfully transforms the flat CSV structure into a relational model that maintains data integrity, eliminates redundancy, and supports efficient querying while complying with 3NF and BCNF requirements.