Lecture 1: Database Theory & Internal Architecture

Understanding the Foundation: From Data to Database Systems

🎓 Concept Overview

Data vs. Information: Databases bridge the gap between raw data and actionable information.
DBMS Power: Solves strict file system limitations (redundancy, inconsistency, security).
MySQL Architecture: A pluggable system where the SQL Layer (logic) is separate from the Storage Engine (data handling).
InnoDB: The default, ACID-compliant engine you should use for 99% of applications.
ACID: The guarantee that your data remains safe, consistent, and durable.

1.1 Data, Information, and Knowledge

Before diving into databases, we must understand the fundamental building blocks:

Data: Raw, unprocessed facts (e.g., "25", "Red", "101"). It has no meaning on its own.
Information: Data with context (e.g., "Age: 25", "Color: Red", "Room: 101"). It answers "Who?", "What?", "Where?".
Knowledge: Information applied to action (e.g., "People aged 25 prefer red rooms"). It answers "Why?" and "How?".

1.2 What is a Database?

A Database is an organized collection of structured information, or data, typically stored electronically in a computer system.

A DBMS (Database Management System) is the software that interacts with end-users, applications, and the database itself to capture and analyze the data. Examples include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

1.2.1 File System vs DBMS

Before databases, we used file systems (like text files or Excel sheets). Here is why we switched:

Feature	File System (Traditional)	DBMS (Modern)
Redundancy	High. Duplicate data everywhere.	Low. Normalization reduces duplication.
Consistency	Poor. Changing address in one file doesn't update others.	High. Single source of truth.
Querying	Complex. Requires writing custom code/scripts.	Simple. Use SQL (Standard Query Language).
Security	Basic (OS file permissions).	Granular (Table, Row, Column level access).
Concurrency	Poor. One user locks the whole file.	Excellent. Multiple users can edit different rows.

1.3 Types of DBMS

Not all databases are the same. We categorize them by how they structure data.

Type	Description	Examples	Best Use Case
Relational (RDBMS)	Data stored in tables with rows and columns. Strict schema.	MySQL, PostgreSQL	Financial systems, Inventory, General Purpose.
Document (NoSQL)	Data stored in JSON-like documents. Flexible schema.	MongoDB	Content management, Rapid prototyping.
Key-Value (NoSQL)	Simple key-value pairs. Extremely fast.	Redis, Memcached	Caching, Session management.
Graph (NoSQL)	Nodes and Edges. Focus on relationships.	Neo4j	Social networks, Recommendation engines.

📜 A Brief History of DBMS

Before Relational Databases (RDBMS), we had:

Hierarchical Model (1960s): Data stored like a tree (Parent -> Child). Fast but rigid. (Think: Windows Registry).
Network Model: Like a web (Graph). More flexible but very complex to query.

The Relational Model (1970) changed everything by using simple tables.

1.3 Relational DBMS (RDBMS)

MySQL is a Relational Database Management System (RDBMS). It is based on the Relational Model proposed by E.F. Codd in 1970.

1.4.1 Structure

Table (Relation): A collection of data organized in rows and columns.
Row (Tuple): A single record in a table. represents one entity instance.
Column (Attribute): A specific piece of information (field) in a record.

1.4.2 Keys: The Glue of Relations

Keys are special columns used to identify records and create relationships.

🔑 Key Definitions

Primary Key (PK): A column (or set of columns) that uniquely identifies each row. cannot be NULL. (e.g., `user_id`).
Candidate Key: Any column that could be a Primary Key (e.g., `email`, `social_security_number`).
Composite Key: A Primary Key made of multiple columns (e.g., `order_id` + `product_id` in an order_items table).
Foreign Key (FK): A column that links to a Primary Key in another table. usage enforces Referential Integrity.

1.5 MySQL Architecture

MySQL allows you to swap out the storage engine, meaning you can choose different performance characteristics for different tables.

MySQL Layered Architecture

1.6 Query Lifecycle: How it Works

1️⃣

Client Sends Query

SELECT * FROM users;

2️⃣

Parser

Checks syntax. "Is this valid SQL?"

3️⃣

Optimizer

Calculates costs. "Should I use an index?"

4️⃣

Execution Engine

Calls the Storage Engine API.

5️⃣

Storage Engine (InnoDB)

Fetches data from Buffer Pool (RAM) or Disk.

1.7 ACID Properties

ACID is the set of properties that guarantee database transactions are processed reliably.

Property	Meaning	Real World Example
Atomicity	All or Nothing. Either the whole transaction happens, or none of it does.	Bank Transfer: If money leaves Account A but fails to enter Account B, the whole thing is cancelled.
Consistency	The database must go from one valid state to another via defined rules.	You cannot transfer money you don't have (negative balance rule).
Isolation	Transactions are independent. Intermediate states are invisible to others.	If I am reading my balance while you transfer money to me, I see either the before or after amount, not a mix.
Durability	Once committed, changes are permanent, even if power fails.	After "Payment Successful" screen, the data is on the hard drive.

1.8 Instructor Notes

👨‍🏫 Teaching Tips

The Pyramid Analogy: Use a grocery receipt to explain Data vs. Information. The raw numbers are data; the receipt structure is information; knowing you spent too much on snacks is knowledge.
ACID Analogy: Compare specific ACID properties to Amazon delivery.
- Atomicity: You order 3 items. If one is out of stock, do you want the other 2 or cancel the whole order? (Atomicity = Cancel all).
- Durability: Once the "Order Placed" screen appears, even if Amazon's server crashes, your order must still exist.

1.9 Practice Tasks

Task 1: Architect's Choice

Scenario: You are building a social media app where users can "friend" each other. The relationships are complex and interconnected (A knows B, B knows C, C knows A).

Question: Which type of DBMS would be the theoretical best fit for traversal speed, even if you used MySQL in practice?

Show Answer

Graph Database (e.g., Neo4j). While MySQL (Relational) can handle this with Junction Tables, Graph databases are designed specifically for traversing complex relationships efficiently.

Task 2: The ACID Test

Scenario: A player in a game buys a sword for 500 gold. The game crashes exactly when the gold is deducted but before the sword is added to their inventory.

Question: Which ACID property failed if the player lost their gold but got no sword?