Database Fundamentals and MySQL Architecture

#Introduction

We'll learn MySQL from first principles. By the end of this chapter you should understand why MySQL is designed the way it is, not just how to write SQL.

Topics: what a database is, DBMS vs RDBMS, why MySQL, MySQL architecture, storage engines (InnoDB vs MyISAM), database objects, data types, constraints, keys, AUTO_INCREMENT, and a real production scenario.

#What is a Database?

Imagine you're building an event ticket booking system with users (Raj, Rahul, Ankit), tickets (101, 102, 103) and payments (500, 600, 700). Where do you store them — Excel? A text file? JSON? A Java ArrayList?

None of these are suitable, because:

• Data gets lost on restart.
• Multiple users can't safely update it.
• Searching is slow.
• Relationships are difficult to manage.

A database stores data in a structured, persistent and efficient way.

Real Example

Think about an ATM. When you withdraw 500, your balance goes from 10,000 to 9,500 — and millions of such updates happen every day. A database guarantees no data loss, correct balances, safe simultaneous access by many users, and fast retrieval.

#What is a DBMS?

DBMS = Database Management System — software that helps us store, retrieve, update, delete and secure data. Examples: MySQL, PostgreSQL, Oracle Database, SQL Server.

The Problem with a Simple DBMS

Suppose we store users and orders like this:

Tomorrow Raj changes his name to Rajesh. The users table now says Rajesh, but the orders table still says Raj — the data is inconsistent.

#What is an RDBMS?

RDBMS = Relational Database Management System. It stores data in tables and connects them using relationships.

Instead of storing the name in orders, we store the user_id:

Advantages: no duplication, consistency, better performance and easy joins.

#Why MySQL?

• Open source
• Fast and reliable
• Huge community
• Easy to learn
• Excellent Spring Boot support
• Used by startups and enterprises

Companies using MySQL include Meta (for many internal workloads), Netflix (in parts of its infrastructure), Uber (alongside other databases), Airbnb and Shopify.

#MySQL Architecture

One of the most commonly misunderstood topics.

1. Spring Boot sends SELECT * FROM users;
2. The parser checks — is the syntax valid? Does the table exist? Any mistakes?
3. The optimizer decides — scan the whole table, or use an index? It chooses the least-cost execution plan.
4. The executor runs the query.
5. The storage engine (usually InnoDB) fetches the rows.
6. The result returns to Java.

#Storage Engines

A storage engine decides how data is stored and retrieved — think of it as the implementation behind your tables.

#Database Objects

For now, focus on databases and tables.

#Data Types

Choosing the right data type matters for performance and correctness.

#Constraints

Constraints enforce data quality — e.g. a user's ID cannot be NULL and must be unique. Common constraints:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• NOT NULL
• CHECK (enforced since MySQL 8.0.16; earlier versions parsed but ignored it)
• DEFAULT

Each is covered in detail in later chapters.

#Keys

#AUTO_INCREMENT

Instead of manually generating IDs (1, 2, 3, 4…), MySQL automatically creates the next value:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(100)
);

#Practice Assignment

Create an event ticket booking schema with these tables: users, events, venues and tickets. For each table:

• Add a primary key.
• Use appropriate data types.
• Include created_at and updated_at columns.
• Add NOT NULL where appropriate.
• Use AUTO_INCREMENT for IDs.

Don't worry about foreign keys yet — they're covered later.

Homework

1. Install MySQL (if not already installed) and connect using MySQL Workbench or another SQL client.
2. Create the event_booking database.
3. Create the four tables listed above.
4. Insert at least 10 rows into each table.
5. Draw the relationships you think should exist between the tables.