Database Design in System Design

Week 4: Database Design

1. Relational vs. NoSQL Databases:

Lecture 1: Introduction to Database Types
Explore the fundamental differences between relational and NoSQL databases.
Compare it to choosing between a structured library and an open playground for data storage.
Discuss the strengths and weaknesses of each type.
Lecture 2: When to Use Relational Databases
Discuss scenarios where relational databases, with their structured tables and relationships, are the best choice.
Example: Using a relational database for financial transactions.
Lecture 3: When to Use NoSQL Databases
Highlight situations where NoSQL databases, with their flexibility and scalability, are preferable.
Example: Storing unstructured data like social media posts in a NoSQL database.
Lab 1: Choosing the Right Database
Given a project description, decide whether a relational or NoSQL database is the most suitable choice.
Justify your decision.

2. Data Modeling and Normalization:

Lecture 4: Data Modeling Basics
Understand the importance of data modeling in database design.
Explain how data entities, attributes, and relationships are represented.
Lecture 5: Normalization
Explore the concept of normalization to eliminate data redundancy and improve database efficiency.
Discuss different normal forms (1NF, 2NF, 3NF).
Lab 2: Designing a Relational Database Schema
Given a set of data requirements, design a normalized relational database schema.
Ensure data integrity and minimize redundancy.

3. Indexing and Query Optimization:

Lecture 6: Introduction to Indexing
Explain how indexing works and why it is essential for efficient data retrieval.
Example: An index in a book helping you find specific information faster.
Lecture 7: Query Optimization Strategies
Discuss techniques to optimize database queries, such as using indexes and denormalization.
Explain the importance of analyzing query execution plans.
Lab 3: Indexing and Query Optimization
Create indexes on tables within a database.
Write and analyze SQL queries to measure the impact of indexing.

4. CAP Theorem and Database Consistency:

Lecture 8: Understanding the CAP Theorem
Introduce the CAP theorem, which states that a distributed database can have at most two of the following: Consistency, Availability, Partition Tolerance.
Use the analogy of a trade-off triangle.
Lecture 9: Consistency Models
Explore different consistency models, including strong consistency and eventual consistency.
Discuss scenarios where each model is suitable.
Lab 4: CAP Theorem Analysis
Study a distributed database system and evaluate its consistency, availability, and partition tolerance in different failure scenarios.

Homework Assignment:

Research and document a real-world use case where the choice of database type (relational or NoSQL) significantly impacted the success of a project.

Read More System Design