Academic Session 2025-26
Even Semester Jan-Jun’26
Apex Institute of Technology
Department of Artificial Intelligence and Machine Learning
System Design (23CST-390)
Faculty: (Er.) Kamaljeet Kaur (E19147)
DISCOVER . LEARN . EMPOWER
Lecture
Databases in System Design
Learning Objectives
• Understand the fundamental concepts of system design and its importance in building
scalable applications.
• Learn the principles of scalability, availability, and reliability in large systems.
• Differentiate between Monolithic and Microservices architectures.
• Explain key performance metrics such as latency and throughput.
• Understand how caching improves system speed and reduces load on backend services.
Course Outcomes
On completion of this course, the students shall be able to:-
Understand the fundamental system design principles, including scalability, high availability, and fault tolerance,
CO1
while analyzing how these core principles influence architectural decisions in real-world distributed environments.
Apply various distributed architectural patterns, key database models (SQL and NoSQL), and critical communication
CO2
protocols to evaluate their specific applications and trade-offs in modern system designs.
Apply advanced scaling techniques, such as caching, load balancing, database sharding, and replication, to design
CO3
system components that effectively handle massive user traffic and data volumes.
Analyze the trade-offs between different system architectures, data storage solutions, and consistency models (like the
CO4
CAP theorem) to select and justify optimized configurations for performance, cost, and reliability goals.
Create and document comprehensive, end-to-end system designs that are inherently scalable, distributed, and fault-
CO5
tolerant, leveraging modern paradigms such as cloud computing and microservices architecture.
Unit-1 Syllabus
Unit-1 System Design
Introduction to Introduction to System Design, Functional & Non-Functional
System Design Requirements, Scaling Challenges, Load Balancing, Caching Strategies,
Replication, Sharding.
Databases in System Databases in System Design – SQL vs NoSQL, CAP Theorem,
Design Distributed Databases, Consistency Models, Eventual Consistency,
Distributed Caching (Redis, Memcached), High Availability, Fault
Tolerance, Microservices vs Monolithic Architecture.
Topic to be Covered
• SQL vs NoSQL Databases
• Relational Model
• NoSQL Databases: Flexibility and Scale
• Key-Value Stores
• Document Databases
• Column-Family Stores
• Graph Databases
• SQL vs NoSQL: A Detailed Comparison
9
SQL vs NoSQL Databases
• SQL (Structured Query Language) databases are
relational database management systems that store
data in structured tables with predefined schemas.
They use SQL for defining and manipulating data,
ensuring ACID properties for reliable transactions.
• SQL databases excel in scenarios requiring
complex queries, transactions, and strict data
consistency. They're the traditional choice for
financial systems, e-commerce platforms, and
enterprise resource planning applications.
Relational Model
• Data is organised into tables (relations) with rows and columns. Tables are linked through
foreign keys, enabling complex queries across multiple tables. This normalised structure
eliminates data redundancy and maintains consistency.
Examples
• MySQL: Open-source, widely used for web applications
• PostgreSQL: Advanced features, strong ACID compliance
• Oracle: Enterprise-grade, highly scalable
• Microsoft SQL Server: Integrated with Microsoft ecosystem
NoSQL Databases: Flexibility and Scale(Con….)
NoSQL databases emerged to address the limitations of traditional SQL databases when
dealing with massive scale, unstructured data, and distributed systems. They sacrifice some
consistency for availability and partition tolerance.
NoSQL Databases: Flexibility and Scale
• Key-Value Stores : Simplest NoSQL model storing data as key-value pairs. Extremely fast
for lookups and caching. Examples: Redis, Amazon DynamoDB, Riak.
• Document Databases : Store data in flexible, JSON-like documents. Schema-less design
allows easy iteration. Examples: MongoDB, CouchDB, Firebase.
• Column-Family Stores : Optimised for queries over large datasets. Store data in columns
rather than rows. Examples: Cassandra, HBase, ScyllaDB.
• Graph Databases : Designed for data with complex relationships. Nodes and edges
represent entities and connections. Examples: Neo4j, Amazon Neptune, ArangoDB.
SQL vs NoSQL: A Detailed Comparison
Aspect SQL Databases NoSQL Databases
Fixed, predefined schema. Changes Dynamic, flexible schema.
Schema
require migrations Easy to modify structure
Query Database-specific query
Standardized SQL across platforms
Language languages
Transactions Multi-row ACID transactions Limited transaction support
Data Structure Structured, relational data Unstructured, semi-structured
Financial systems, ERP, complex Real-time analytics, content
Use Cases
queries requiring joins. management, social networks.
Summary of the Lecture
• SQL Databases (Relational Databases)
SQL databases store data in structured tables with fixed schemas, using rows and columns.
They follow ACID properties (Atomicity, Consistency, Isolation, Durability), ensuring
strong data consistency and reliability. SQL databases are best suited for applications
requiring complex queries, transactions, and strict data integrity, such as banking systems,
ERP, and traditional business applications.
• NoSQL Databases (Non-Relational Databases)
NoSQL databases are designed for flexibility and scalability, handling large volumes of
unstructured or semi-structured data. They support dynamic schemas and follow BASE
principles (Basically Available, Soft state, Eventual consistency). NoSQL databases are ideal
for big data, real-time web applications, and distributed systems where high availability and
horizontal scaling are critical.
Next Lecture
• CAP Theorem and BASE Principles
• Distributed Databases and Eventual Consistency
• Distributed Caching: Deep Dive into Redis and Memcached
Quiz
1. What is the primary characteristic of SQL databases?
a) Schema-less data storage
b) Fixed schema with structured tables
c) Graph-based data storage
d) Key-value data storage
2. Which property is followed by SQL databases?
a) BASE
b) CAP
c) ACID
d) MAP
3. NoSQL databases are mainly designed to handle:
a) Small datasets only
b) Highly structured data
c) Unstructured and semi-structured data
d) Only transactional data
FAQ’s
1. What is the main difference between SQL and NoSQL databases?
SQL databases use structured tables with fixed schemas and strong consistency, while NoSQL
databases offer flexible schemas and are designed for scalability and high availability.
2. When should SQL databases be used?
SQL databases are best used when applications require complex queries, transactions, and
strong data integrity, such as banking, finance, and enterprise systems.
3. When are NoSQL databases preferred?
NoSQL databases are preferred for handling large volumes of unstructured or semi-structured
data, real-time applications, big data, and distributed systems.
4. Do NoSQL databases support transactions?
Some NoSQL databases support transactions, but generally they focus on performance and
scalability rather than strict ACID compliance.
References/ Articles/ Videos
Book Reference:
• Silberschatz, A., Korth, H. F., & Sudarshan, S.
Database System Concepts, 7th Edition, McGraw-Hill.
• Ramakrishnan, R., & Gehrke, J.
Database Management Systems, 3rd Edition, McGraw-Hill.
Tutorial Link:
[Link]
Video Link:
[Link]
Class-Wise Feedback
Thank You
For queries Email: kamaljeet.e19147@[Link]