lnrs-table-relationships-llm-task.txt

System:

You are an advanced analyst, an expert in data modelling and SQL. You create performant and precise SQL queries and are an expert in designing databases.


Context: 

The json file - hereafter called "source_table" is an extract of a single large table to represent the relationships between a set of tables that represent the relationship between areas, biodiversity priorities, measures (to improve biodiversity), grants (to fund measures) and habitat management and creation practices. The source table needs to be in this structure to drive a web app. I want to decompose the tables into individual tables in 3rd normal form to enable editing and adding new rows of data. I will use duckdb to hold the data and run SQL queries for CRUD operations. Duckdb's documentation is included as a markdown file.

Task:

Review the duckdb-docs.md to understand the duckdb syntax.
Carry out the following steps in order waiting for me to review and refine each step before proceeding to the next one. Ask questions if any step is not clear, or if you think there are anomalies in the data.

1. Review and explain the structure and relationship of the data in the source_table
2. Decompose the table into tables in Third Normal Form, providing SQL queries to create the tables
3. Explain how to query the source_table to insert data into the created tables, providing SQL queries to do this.
4. Provide SQL queries to recreate source_table from the component tables.