Introduction to Databases and SQL

This chapter establishes the core terminology and concepts required to understand how MySQL works as a Relational Database Management System (RDBMS).

1. What is a Relational Database Management System (RDBMS)?

An RDBMS is a software application (like MySQL, PostgreSQL, or Oracle) that is used to create, maintain, and retrieve data from a relational database.

• Relational: Data is organized into structured tables that can be linked, or related, to one another based on common fields. This structure helps ensure data consistency and minimizes redundancy.
• Database: A collection of information organized to be easily accessed, managed, and updated.
• Management System: The software that provides the tools to manage access, security, concurrency, and integrity.

2. The Core Structure: Tables, Rows, and Columns

The fundamental organization of data in an RDBMS is the Table.

• Table (Relation): A collection of related data entries, consisting of columns and rows (e.g., a Customers table).
• Column (Field/Attribute): The vertical components that define the structure and hold a specific type of information (e.g., first_name, email, date_of_birth).
• Row (Record/Tuple): The horizontal components that represent a single, complete set of data for one entity (e.g., all the information for a single customer).

3. The Role of Keys

Keys are crucial for defining relationships and ensuring data integrity within the database.

• Primary Key (PK): A column (or set of columns) that uniquely identifies every row in a table.
  • Rule 1: Must contain unique values for every row.
  • Rule 2: Cannot contain NULL values.
  • Example: customer_id in a Customers table.
• Foreign Key (FK): A column in one table that references the Primary Key in another table.
  • Purpose: To create a link or relationship between two tables.
  • Example: customer_id in an Orders table links that order back to the specific customer in the Customers table.

4. What is SQL?

SQL (Structured Query Language) is the standardized language used to communicate with an RDBMS. It is the language you use to ask the database questions, update data, and define structure.

SQL commands are divided into major categories:

A. Data Definition Language (DDL)

Commands used to define, modify, or delete the structure of database objects (tables, databases, indexes).

• CREATE (e.g., CREATE TABLE)
• ALTER (e.g., ALTER TABLE)
• DROP (e.g., DROP TABLE)

B. Data Manipulation Language (DML)

Commands used to manage and manipulate the data contained within the tables. This is the most frequently used set of commands.

• SELECT (Retrieves data)
• INSERT (Adds new data)
• UPDATE (Modifies existing data)
• DELETE (Removes data)

---

5. Local Execution Environment

To follow the examples in this tutorial, you need access to a running MySQL Server instance. This is typically achieved using:

• XAMPP / MAMP / WAMP: Local server stacks that include MySQL and a GUI tool called phpMyAdmin.
• MySQL Workbench / DBeaver: Dedicated database client applications.
• Command Line: Directly accessing the MySQL client via the terminal.

For simplicity, all examples will use standard SQL commands that can be executed in any of these environments.