relational

What I do

• Apply relational algebra and set theory
• Design normalized schemas (1NF, 2NF, 3NF, BCNF)
• Write complex SQL queries and joins
• Optimize query performance
• Implement referential integrity

When to use me

When working with relational databases, designing data models, or writing complex queries.

Key Concepts

Normalization Forms

• 1NF: Atomic values, no repeating groups
• 2NF: No partial dependencies
• 3NF: No transitive dependencies
• BCNF: Boyce-Codd normal form
• Denormalization: When and how to denormalize

Query Optimization

• Index selection and composite indexes
• Query execution plans
• Join order optimization
• Subquery vs join
• Partition pruning

Transactions

• ACID properties
• Isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)
• Locking strategies
• Deadlock prevention
• Savepoints and rollback

Advanced SQL

• Window functions
• CTEs (Common Table Expressions)
• Recursive queries
• JSON/JSONB operations
• Full-text search
• Spatial queries

Design Patterns

• Entity-Attribute-Value (EAV)
• Single Table Inheritance
• Class Table Inheritance
• Materialized paths
• Audit tables

Performance

• Query profiling
• Index maintenance
• Statistics management
• Connection pooling
• Read replicas
• Partitioning strategies