Core Structure & Relationships

• Tables (relations) store data in rows and columns
• Primary keys uniquely identify each row in a table
• Foreign keys create relationships by referencing primary keys in other tables
• Three main relationship types:
  • One-to-one (e.g., user and profile)
  • One-to-many (e.g., customer and orders)
  • Many-to-many (e.g., students and courses, using junction tables)

Data Integrity & ACID Properties

• Atomicity: Transactions complete fully or not at all
• Consistency: Database rules and constraints are maintained
• Isolation: Concurrent transactions don't interfere with each other
• Durability: Committed data persists even after system failures
• Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY

Normalization & Design

• 1NF: Eliminates repeating groups, atomic values only
• 2NF: Removes partial dependencies on composite keys
• 3NF: Eliminates transitive dependencies
• Denormalization: Sometimes used strategically for performance

SQL Operations

• CRUD operations: CREATE, READ, UPDATE, DELETE
• JOIN types:
  • INNER JOIN: Returns matching rows from both tables
  • LEFT/RIGHT JOIN: All rows from one table with matches from the other
  • FULL OUTER JOIN: All rows from both tables
• Aggregate functions: COUNT, SUM, AVG, MAX, MIN
• GROUP BY and HAVING clauses for grouped data analysis
• Subqueries and CTEs (Common Table Expressions)

Performance & Optimization

• Indexes speed up reads but slow down writes
• Types: Single-column, composite, unique, full-text indexes
• Query optimization: Analyze execution plans, avoid N+1 problems
• Use appropriate JOINs instead of multiple queries
• Index cardinality and covering indexes

Transactions & Concurrency

• Isolation levels:
  • Read Uncommitted: Lowest isolation, allows dirty reads
  • Read Committed: Prevents dirty reads
  • Repeatable Read: Prevents non-repeatable reads
  • Serializable: Highest isolation, prevents phantom reads
• Concurrency issues: Dirty reads, non-repeatable reads, phantom reads
• Deadlock detection and prevention strategies

Practical Considerations

• Connection pooling for efficient resource management
• Prepared statements for SQL injection prevention and performance
• Database backups and recovery strategies
• Replication: Master-slave, master-master configurations
• Scaling strategies: Read replicas, sharding, partitioning
• Stored procedures vs. application logic trade-offs