schema-exploration

name: schema-exploration description: Lists tables, describes columns and data types, identifies foreign key relationships, and maps entity relationships in a database. Use when the user asks about database schema, table structure, column types, what tables exist, ERD, foreign keys, or how entities relate.

Schema Exploration Skill

Workflow

1. List All Tables

Use sql_db_list_tables tool to see all available tables in the database.

This returns the complete list of tables you can query.

2. Get Schema for Specific Tables

Use sql_db_schema tool with table names to examine:

• Column names - What fields are available
• Data types - INTEGER, TEXT, DATETIME, etc.
• Sample data - 3 example rows to understand content
• Primary keys - Unique identifiers for rows
• Foreign keys - Relationships to other tables

3. Map Relationships

Identify how tables connect:

• Look for columns ending in "Id" (e.g., CustomerId, ArtistId)
• Foreign keys link to primary keys in other tables
• Document parent-child relationships

4. Answer the Question

Provide clear information about:

• Available tables and their purpose
• Column names and what they contain
• How tables relate to each other
• Sample data to illustrate content

Example: "What tables are available?"

Step 1: Use sql_db_list_tables

Response:

The Chinook database has 11 tables:
1. Artist - Music artists
2. Album - Music albums
3. Track - Individual songs
4. Genre - Music genres
5. MediaType - File formats (MP3, AAC, etc.)
6. Playlist - User-created playlists
7. PlaylistTrack - Tracks in playlists
8. Customer - Store customers
9. Employee - Store employees
10. Invoice - Customer purchases
11. InvoiceLine - Individual items in invoices

Example: "What columns does the Customer table have?"

Step 1: Use sql_db_schema with table name "Customer"

Response:

The Customer table has these columns:

- CustomerId (INTEGER) - Primary key, unique customer ID
- FirstName (NVARCHAR) - Customer's first name
- LastName (NVARCHAR) - Customer's last name
- Company (NVARCHAR) - Company name (optional)
- Address, City, State, Country, PostalCode - Location fields
- Phone (NVARCHAR) - Phone number
- Fax (NVARCHAR) - Fax number
- Email (NVARCHAR) - Email address
- SupportRepId (INTEGER) - Foreign key to Employee table

Sample data shows customers like:
- Luís Gonçalves from Brazil
- Leonie Köhler from Germany
- François Tremblay from Canada

Example: "How do I find revenue by artist?"

Step 1: Identify tables needed

• Artist (has artist names)
• Album (links artists to tracks)
• Track (links albums to sales)
• InvoiceLine (has sales data)
• Invoice (has revenue totals)

Step 2: Map relationships

Artist (ArtistId)
  ↓ 1:many
Album (ArtistId, AlbumId)
  ↓ 1:many
Track (AlbumId, TrackId)
  ↓ 1:many
InvoiceLine (TrackId, UnitPrice, Quantity)

Response:

To find revenue by artist, you'll need to:
1. Start with the Artist table
2. JOIN to Album on ArtistId
3. JOIN to Track on AlbumId
4. JOIN to InvoiceLine on TrackId
5. SUM the revenue (UnitPrice * Quantity)
6. GROUP BY artist name

This requires the query-writing skill to execute.

Quality Guidelines

For "list tables" questions:

• Show all table names
• Add brief descriptions of what each contains
• Group related tables (e.g., music catalog, transactions, people)

For "describe table" questions:

• List all columns with data types
• Explain what each column contains
• Show sample data for context
• Note primary and foreign keys
• Explain relationships to other tables

For "how do I query X" questions:

• Identify required tables
• Map the JOIN path
• Explain the relationship chain
• Suggest next steps (use query-writing skill)