Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Castle: Causal Cascade Updates in Relational Databases with Large Language Models

Yongye Su, Yucheng Zhang, Zeru Shi, Bruno Ribeiro, Elisa Bertino

TL;DR

Castle addresses cascading updates in denormalized relational databases by enabling schema-only, LLM-assisted generation of UPDATE statements with causal propagation. It introduces a multi-stage framework that identifies direct and cascade targets, plans evidence-grounded subqueries, and generates triggers to maintain derived metrics, all without exposing table data. The approach is evaluated on Soccer Transfer and UCI Online Retail II datasets, achieving up to 85% correctness and outperforming baselines while preserving data confidentiality. The work demonstrates practical implications for privacy-preserving, real-time maintenance of complex relational schemas in modern DBMSs.

Abstract

This work introduces Castle, the first framework for schema-only cascade update generation using large language models (LLMs). Despite recent advances in LLMs for Text2SQL code generation, existing approaches focus primarily on SELECT queries, neglecting the challenges of SQL update operations and their ripple effects. Traditional CASCADE UPDATE constraints are static and unsuitable for modern, denormalized databases, which demand dynamic, context-aware updates. Castle enables natural language instructions to trigger multi-column, causally consistent SQL UPDATE statements, without revealing table content to the model. By framing UPDATE SQL generation as a divide-and-conquer task with LLMs' reasoning capacity, Castle can determine not only which columns must be directly updated, but also how those updates propagate through the schema, causing cascading updates -- all via nested queries and substructures that ensure data confidentiality. We evaluate it on real-world causal update scenarios, demonstrating its ability to produce accurate SQL updates, and thereby highlighting the reasoning ability of LLMs in automated DBMS.

Castle: Causal Cascade Updates in Relational Databases with Large Language Models

TL;DR

Castle addresses cascading updates in denormalized relational databases by enabling schema-only, LLM-assisted generation of UPDATE statements with causal propagation. It introduces a multi-stage framework that identifies direct and cascade targets, plans evidence-grounded subqueries, and generates triggers to maintain derived metrics, all without exposing table data. The approach is evaluated on Soccer Transfer and UCI Online Retail II datasets, achieving up to 85% correctness and outperforming baselines while preserving data confidentiality. The work demonstrates practical implications for privacy-preserving, real-time maintenance of complex relational schemas in modern DBMSs.

Abstract

This work introduces Castle, the first framework for schema-only cascade update generation using large language models (LLMs). Despite recent advances in LLMs for Text2SQL code generation, existing approaches focus primarily on SELECT queries, neglecting the challenges of SQL update operations and their ripple effects. Traditional CASCADE UPDATE constraints are static and unsuitable for modern, denormalized databases, which demand dynamic, context-aware updates. Castle enables natural language instructions to trigger multi-column, causally consistent SQL UPDATE statements, without revealing table content to the model. By framing UPDATE SQL generation as a divide-and-conquer task with LLMs' reasoning capacity, Castle can determine not only which columns must be directly updated, but also how those updates propagate through the schema, causing cascading updates -- all via nested queries and substructures that ensure data confidentiality. We evaluate it on real-world causal update scenarios, demonstrating its ability to produce accurate SQL updates, and thereby highlighting the reasoning ability of LLMs in automated DBMS.

Paper Structure

This paper contains 29 sections, 2 equations, 1 figure, 5 tables, 1 algorithm.

Table of Contents

  1. Introduction
  2. Castle
  3. Research Question
  4. Motivating exampling using our Soccer Transfer task.
  5. Proposed Method.
  6. Skeleton: Identifying Columns for Update
  7. Columns to be directly updated ($C_\text{direct}$).
  8. Causally-dependent columns to be cascade updated ($C_\text{cascade}$).
  9. Subquery Planning: Evidence-grounded Updates
  10. Trigger Verification and Generation
  11. Trigger Verification.
  12. Trigger Generation.
  13. Compatibility with other systems
  14. Experiments
  15. Dataset
  16. ...and 14 more sections

Figures (1)

  • Figure 1: A sample workflow of Castle illustrates how a single transaction with natural language instructions as input is completed as a cascading update SQL transaction, incorporating the reasoning procedures of LLMs alongside the database.