Harvard CS50's Introduction to Databases with SQL

Database Fundamentals and SQL

• 💡 The course introduces databases using SQL, covering creation, reading, updating, and deleting data in relational databases.
• 🎯 Key concepts include modeling real-world entities, normalizing data to reduce redundancy, and joining tables using primary and foreign keys.
• 🔑 SQL is presented as a structured query language for interacting with databases, enabling automation with views and optimization with indexes.

Relational Databases and Design

• 🚀 The course transitions from single-table databases to relational databases with multiple tables (e.g., authors, books, publishers) and explores relationships like one-to-one, one-to-many, and many-to-many.
• 🧩 Entity-Relationship (ER) diagrams and Crow's Foot notation are introduced to visualize these relationships.
• 🛠️ Concepts of primary keys (unique identifiers) and foreign keys (referencing primary keys in other tables) are explained for linking tables.

Querying and Data Manipulation

• 🔍 SQL querying techniques include SELECT, WHERE for filtering, LIMIT for restricting results, and pattern matching with LIKE.
• 📊 Aggregate functions like AVG, COUNT, MAX, MIN, and SUM are used to summarize data, often with the GROUP BY clause.
• 📈 ORDER BY is used for sorting results, and JOIN operations combine data from multiple tables.

Advanced Concepts and Scaling

• 💡 Stored procedures, triggers, views, and transactions (ACID properties) are introduced for automating tasks, ensuring data integrity, simplifying complex queries, and handling concurrency.
• 🚀 Database scaling strategies like vertical scaling (more powerful single server) and horizontal scaling (multiple servers) are discussed, along with replication and sharding.
• 🔒 Security aspects like access control and preventing SQL injection attacks are highlighted, emphasizing the use of prepared statements.

Database Management Systems (MySQL & PostgreSQL)

• 💻 The course compares MySQL and PostgreSQL with SQLite, focusing on data types (e.g., INT, VARCHAR, ENUM, DATE, DECIMAL) and their specific features.
• ⚙️ Concepts like ALTER TABLE for modifying existing tables and CREATE TABLE for defining new ones are demonstrated.
• 🚀 Scaling strategies like replication (single-leader, multi-leader) and sharding are explained as ways to handle large datasets and high traffic.