Schema-Aware Conversational Natural Language Interface for Relational Databases

Using Classical NLP, Retrieval-Augmented Generation, and Agentic Reasoning

M.Tech NLP Course Project | Classical NLP + RAG + Agentic AI

---

Project Overview

This system converts natural language questions into SQL queries against an e-commerce database. It combines classical NLP techniques (satisfying academic syllabus requirements) with modern RAG and agentic reasoning (industry-grade architecture).

Key Features

• Conversational Interface: Ask questions in plain English, get SQL + results
• Multi-turn Dialogue: Follow-up queries modify previous SQL contextually
• Classical NLP Pipeline: Tokenization, POS tagging, lemmatization, n-grams, TF-IDF, Naive Bayes
• RAG Schema Linking: TF-IDF + embedding-based retrieval over schema metadata
• Agentic Planning: Confidence-based routing, error repair, clarification requests
• CFG SQL Generation: Context-free grammar based SQL construction with syntax trees

Architecture

User Query
    ↓
NLP Preprocessing (tokenize, POS tag, lemmatize, n-grams)
    ↓
Intent Classifier (Naive Bayes + TF-IDF)
    ↓
Schema Retrieval / RAG (TF-IDF cosine + synonym matching)
    ↓
Sequence Slot Tagger (SQL semantic role tagging)
    ↓
CFG SQL Generator (grammar-based SQL assembly)
    ↓
Agent Planner (execute → repair → clarify loop)
    ↓
Results + Explanation

---

Syllabus Coverage

Syllabus Requirement	Module	Implementation
Tokenization	preprocessor.py	Custom tokenizer with contraction handling
POS Tagging	preprocessor.py	Rule-based domain POS tagger
Lemmatization	preprocessor.py	Suffix-based + dictionary lemmatizer
Stemming	preprocessor.py	Porter-like suffix stripper
N-grams (Bi/Tri)	preprocessor.py	Bigram + trigram generation
Frequency Distribution	preprocessor.py	Token frequency counter
TF-IDF	intent_dataset.py	Custom TF-IDF from scratch
CountVectorizer	intent_dataset.py	Custom count vectorizer
Naive Bayes	intent_classifier.py	Multinomial NB from scratch
Confusion Matrix	intent_classifier.py	Full CM with P/R/F1 per class
Sequence Tagging	slot_tagger.py	SQL semantic slot tagging
CFG	sql_generator.py	Grammar-based SQL construction
Syntax Trees	sql_generator.py	Parse tree generation
Entity Recognition	slot_tagger.py	City, brand, status NER
Recommendation Engine	engine.py	Query suggestion system
AI Chatbot	app.py	Conversational Streamlit interface

---

Installation & Setup

# 1. Install dependencies
pip install streamlit pandas scikit-learn

# 2. Create database (if not exists)
python data/create_db.py

# 3. Run the application
streamlit run app.py

# 4. Run evaluation
PYTHONPATH=src python src/evaluation/evaluate.py

---

Project Structure

nl2sql/
├── app.py                          # Streamlit web application
├── data/
│   ├── create_db.py               # Database schema + seed data
│   ├── ecommerce.db               # SQLite database (10 tables, 28K+ rows)
│   ├── schema.json                # Schema metadata for RAG
│   └── intent_dataset.csv         # 200 labeled NL queries
├── models/
│   └── intent_model.pkl           # Trained Naive Bayes model
├── src/
│   ├── engine.py                  # Main orchestrator
│   ├── preprocessing/
│   │   └── preprocessor.py        # Tokenization, POS, lemmatization, n-grams
│   ├── classification/
│   │   ├── intent_dataset.py      # Dataset + TF-IDF vectorizer
│   │   └── intent_classifier.py   # Naive Bayes classifier + evaluation
│   ├── retrieval/
│   │   └── schema_retriever.py    # RAG: TF-IDF + synonym schema retrieval
│   ├── slot_tagging/
│   │   └── slot_tagger.py         # Sequence slot tagger
│   ├── sql_generation/
│   │   └── sql_generator.py       # CFG-based SQL generator
│   ├── agent/
│   │   └── planner.py             # Agentic planning with repair loop
│   ├── conversation/
│   │   └── state_manager.py       # Multi-turn dialogue state
│   └── evaluation/
│       └── evaluate.py            # Full evaluation suite
└── README.md

---

Database Schema

E-Commerce Domain with 10 tables:

Table	Rows	Description
customers	500	Customer profiles + loyalty tier
addresses	649	Shipping/billing addresses
categories	10	Product categories
products	2,000	Product catalog
orders	5,000	Customer orders
order_items	15,218	Line items per order
payments	5,000	Payment records
shipments	4,277	Shipping tracking
returns	648	Return requests
promotions	10	Discount codes

---

Evaluation Results

Metric	Score
Intent Classification Accuracy	~52%
Schema Retrieval Precision@1	91%
Schema Retrieval Precision@3	98%
SQL Execution Accuracy	87%
Macro F1 (Intent)	~54%

---

Example Queries

"Show all customers from Mumbai"
→ SELECT customers.* FROM customers JOIN addresses ON ... WHERE city = 'Mumbai'

"Count total orders in 2025"
→ SELECT COUNT(*) FROM orders WHERE strftime('%Y', order_date) = '2025'

"Top 5 products by rating"
→ SELECT * FROM products ORDER BY rating_avg DESC LIMIT 5

"Average order value by city"
→ SELECT city, AVG(order_total) FROM orders JOIN addresses ON ... GROUP BY city

"Show cancelled orders with refund above 3000"
→ SELECT * FROM orders JOIN returns ON ... WHERE order_status = 'Cancelled' AND refund_amount > 3000

Follow-up Queries

User: "show orders in 2025"
User: "only Mumbai"        → adds WHERE city = 'Mumbai'
User: "group by status"    → adds GROUP BY order_status

---

Technology Stack

• Language: Python 3.10+
• Database: SQLite3
• UI: Streamlit
• ML: scikit-learn (for reference), custom implementations
• No LLM dependency: All NLP is classical (rule-based + statistical)

---

CV-Ready Description

Built a schema-aware conversational NL-to-SQL system integrating classical NLP (TF-IDF, Naive Bayes, sequence tagging, CFG parsing) with retrieval-augmented schema linking and agentic planning for iterative query refinement. Achieved 87% SQL execution accuracy on an e-commerce database with 10 tables and 28K+ rows.

---

Author

M.Tech in Applied AI and Communication NLP Course Project (ECL545)