mysql

The impact of database normalization levels (1NF–3NF) on NL2SQL performance remains poorly understood, hindering principled database design for natural-language-driven query systems. Method: We systematically evaluate eight large language models across synthetic and real-world datasets spanning varying normalization levels, measuring SQL generation accuracy under zero-shot and few-shot settings. Contribution/Results: Our empirical study is the first to demonstrate that denormalized schemas improve accuracy for simple retrieval queries, whereas normalized schemas significantly enhance performance on aggregation queries—despite increasing JOIN-related errors. Crucially, few-shot examples effectively mitigate such JOIN errors induced by normalization. Based on these findings, we propose a “query-type–driven adaptive schema selection” strategy, offering actionable guidelines for co-designing database schemas and deploying NL2SQL models. This work bridges database theory and NL2SQL practice, enabling schema-aware model optimization.

This paper addresses the limitations of traditional database systems—including outdated abstractions (SQL, relational algebra, ER modeling, ORM), restricted expressive power, and poor integration with modern programming languages—by proposing the Relational Mapping Type Model (RMTM), a unified data model that subsumes both the relational and ER models. Methodologically, it introduces a mapping-primitive query language grounded in RMTM, formally satisfying seven principled design criteria; this language operates directly over tuples, relations, and hierarchical database structures, enabling paradigm-level unification and elimination of SQL/RA/ERM/ORM distinctions. The approach integrates type-theoretic modeling, declarative semantic definitions, and a compatibility layer to ensure native programming-language integration and multi-level abstraction coherence. Experimental evaluation demonstrates that replacing only the data model—without modifying underlying algorithms—yields up to 3× performance improvement; moreover, the query language exhibits significantly greater expressiveness than SQL and natively aligns with contemporary software stacks.

Contemporary relational database management systems (RDBMSs) suffer from insufficient logical data independence, reducing them to passive storage layers incapable of supporting modern architectural innovation. This paper argues that the Entity-Relationship (ER) model must serve as the native abstraction layer of RDBMSs to overcome this limitation, and it provides the first systematic theoretical justification and empirical validation of the ER model’s necessity and feasibility for ensuring logical independence. Based on this insight, we design and implement ErbiumDB—a prototype system integrating metadata-driven schema management, declarative relational semantic modeling, and runtime relationship evolution. Experimental evaluation demonstrates that ER-based abstraction significantly enhances decoupling between application and storage layers, enabling flexible, semantics-aware data management. ErbiumDB establishes a novel paradigm for intelligent database architectures and delivers a rigorously validated, extensible prototype foundation for future research and development.

This work addresses the challenge of verifying relational database management systems’ (RDBMS) compliance with SQL semantics at the standard specification level. We present the first executable Prolog reference implementation grounded in the complete formal SQL semantics defined by ISO/IEC 9075, integrated with differential fuzz testing for semantic-level black-box validation. Unlike prior approaches relying solely on crash detection or meta-transformation, our method enables end-to-end verifiable modeling of SQL standard semantics. Empirical evaluation across MySQL, TiDB, SQLite, and DuckDB uncovered 19 previously unknown vulnerabilities and 11 semantic inconsistencies—each traceable to explicit violations, omissions, or ambiguities in the SQL standard. Our approach significantly enhances the decidability and interpretability of SQL implementation correctness.

Traditional database query optimization relies solely on explicit schema constraints (e.g., primary and foreign keys), overlooking abundant implicit data dependencies—such as ordering or functional dependencies—that remain undetected and unexploited. Method: This paper introduces a workload-driven, lightweight dependency discovery technique that enables millisecond-scale automatic identification of such dependencies. It tightly integrates dependency-aware optimization into both the query optimizer and execution engine, supporting SQL rewriting, dependency propagation, and subquery optimization. Contribution/Results: The approach transcends conventional schema-only optimization by establishing an end-to-end dependency-aware framework. Evaluated across five mainstream DBMSs using standard benchmarks (e.g., TPC-C), it achieves up to 10% higher throughput versus primary/foreign-key–only optimization, 22% over no-dependency optimization, and overall improvements of 5%–33%. Discovery overhead is fully amortized after a single workload execution.

Current text-to-SQL system evaluations rely on a single static database, which fails to capture model robustness across diverse data instances and may introduce significant bias. This work proposes SynSQL, a novel framework that leverages large language models to directly generate semantically consistent and schema-aligned relational test data from natural language questions. SynSQL formulates database construction as a structured generation task governed by semantic and relational constraints, comprising three stages: schema selection, question-guided data synthesis, and constraint-aware iterative refinement. Experiments on Spider, BIRD, and Spider 2.0 demonstrate that databases generated by SynSQL reduce the performance of ten state-of-the-art models by 3–14%, effectively uncovering errors masked by static evaluation and substantially enhancing assessment reliability and stress-testing capability.

Traditional relational database systems offer a monolithic set of features, yet real-world workloads often require only a subset, leading to resource redundancy and suboptimal efficiency. To address this, this work proposes an LLM-driven approach for automatically generating customized, deployable databases from natural language workload descriptions. The method employs Feature-Oriented Domain Analysis (FODA) to decompose databases into modular components and their implementation variants, constructs a dependency graph—DBGraph—augmented with cooperate edges to capture cross-module design constraints, and leverages a multi-agent architecture comprising Main, Architect, Tester, and Refining Agents to orchestrate end-to-end synthesis. Evaluated on the TPC-C benchmark (10 warehouses), the generated system achieves 130 tpmC—outperforming PostgreSQL and MySQL—while comprising only approximately 3% of their codebase and demonstrating zero failures over 60 minutes of continuous operation.

This work addresses the limitation of traditional database logical design, which overlooks the capacity of large language models (LLMs) to comprehend schema semantics, thereby constraining Text-to-SQL accuracy. For the first time, LLM-friendliness is incorporated into logical schema design through three semantic-preserving and composable transformation strategies: abstraction (+A), workload-aware partitioning (+P), and descriptive renaming (+R). The proposed approach is compatible with both supervised and zero-shot settings, yielding consistent improvements across multiple Text-to-SQL models. Evaluated on the BIRD-Union and Spider-Union benchmarks, the method achieves up to a 4.2% absolute gain in execution accuracy, significantly enhancing the mapping from natural language queries to executable SQL statements.

This work addresses the limitations of traditional SQL and relational databases in efficiently supporting semantic queries for machine learning (ML) and large language models (LLMs), which often necessitate complex engineering and data migration. To overcome this, the paper proposes iPDB, a system that natively integrates ML/LLM inference capabilities within the database by extending SQL syntax to support semantic projection, selection, join, and grouping operations. The key innovations include the introduction of relational prediction operators and a semantic query optimization mechanism, enabling the first unified SQL framework that seamlessly and efficiently combines classical relational algebra with model-based inference. Experimental results demonstrate that iPDB allows concise expression of semantic queries in SQL while achieving superior execution efficiency compared to state-of-the-art approaches.

This work addresses the scarcity of real-world SQL workloads and limitations of existing synthetic query generation methods—such as rigid templates, schema hallucination, and simplistic join structures—that hinder effective training of query optimizers. The authors propose SynQL, a rule-based, deterministic SQL synthesis framework that constructs abstract syntax trees by traversing foreign key graphs in database schemas, eschewing probabilistic generation to guarantee syntactic and schema-level validity. SynQL introduces a configuration vector to explicitly control join topology, analytical complexity, and predicate selectivity, while modeling core query components including multi-table joins, projections, aggregations, and range predicates. Workloads generated on TPC-H and IMDb achieve a topological entropy of 1.53 bits; tree-based models trained on this data attain R² ≥ 0.79 on test sets with sub-millisecond inference latency.