On the effects of logical database design on database size, query complexity, query performance, and energy consumption

2025-01-13

AI Summary

The impact of relational database normalization (1NF→4NF) on storage overhead, query complexity, transaction performance, and energy consumption remains empirically underexplored. Method: Using the IMDb dataset and PostgreSQL, we constructed multi-normal-form database instances and conducted a joint theoretical and system-level empirical analysis measuring storage, throughput, latency, and energy per transaction. Contribution/Results: Our study quantifies—for the first time—that advancing from 1NF to 2NF reduces disk footprint by 10%, increases transaction throughput fourfold, and cuts per-transaction energy consumption by 74%. In contrast, further normalization (2NF→4NF) yields no statistically significant performance gains; instead, it increases storage overhead by ~7% and monotonically raises query complexity due to increased join operations. We identify 2NF as a critical inflection point for co-optimizing energy efficiency and performance: beyond 2NF, diminishing—and ultimately negative—returns emerge. These findings provide empirically grounded, quantitative guidance for trade-offs in normalization degree during database design.

Technology Category

• Data Mining & Knowledge Management: Intelligent Query Processing
• Knowledge Representation and Reasoning: Computational Complexity of Reasoning
• Search and Optimization: Evaluation and Analysis

Application Category

• Web Mining and Content Analysis: Normalization, clustering, classification, and summarization of Web text
• Systems and Infrastructure for Web, Mobile and WoT: Web performance, measurement, and characterization
• Security and Privacy: Data transparency and provenance

Abstract

Database normalization theory is the basis for logical design of relational databases. Normalization reduces data redundancy and consequently eliminates potential data anomalies, while increasing the computational cost of read operations. Despite decades worth of applications of normalization theory, it still remains largely unclear to what extent normalization affects database size and efficiency. In this study, we study the effects of database normalization using the Internet Movie Database (IMDb) public dataset and PostgreSQL. The results indicate, rather intuitively, that (i) database size on disk is reduced through normalization from 1NF to 2NF by 10%, but not from 2NF to 4NF, (ii) the number of tables and table rows in total increase monotonically from 1NF to 2NF to 4NF, and that (iii) query complexity increases with further normalization. Surprisingly, however, the results also indicate that (iv) normalization from 1NF to 2NF increases throughput by a factor of 4, and consequently, (v) energy consumption per transaction reduces by 74% with normalization from 1NF to 2NF. The results imply that the gains of normalization from 2NF to 4NF in terms of throughput and energy consumption are minimal, yet increase the storage space requirements by approximately 7%. While these results represent merely one specific case, they provide needed empirical evaluation on the practical effects and magnitude of database normalization.

Problem

• Database Normalization
• Query Efficiency
• Energy Consumption

Innovation

• Database Normalization
• Energy Efficiency
• Data Processing Speed