MySQL Database Systems Study Reviewer

MySQL Architecture & Core Relational Concepts

  • MySQL: One of the world's most widely utilized open-source Relational Database Management Systems (RDBMS). Known for its performance, velocity, robustness, and simple compatibility, it acts as a central storage backend for web services, enterprise systems, and data-driven systems.

  • Relational Database Management System (RDBMS): A database software system that structures data logically inside flat, multi-column tables and maintains explicit relationships across columns using PRIMARY KEY and FOREIGN KEY links.

  • Client-Server Architecture: The underlying network software engine design of MySQL, where the central MySQL database server runs perpetually on a machine, managing data storage files and operations, and multiple client systems connect to it via network channels to issue queries.

  • InnoDB: The standard default transactional storage engine of MySQL. InnoDB supports transaction-safe processing, full referential checking via FOREIGN KEY constraints, and row-level locking to enable concurrent access from multiple clients.

  • MyISAM: An older, non-transactional storage engine in MySQL. While it offers high-speed read-only execution, it lacks transaction safety, ignores FOREIGN KEY integrity rules, and locks entire tables during writes, making it unsuitable for highly concurrent write-heavy systems.

  • ACID Transactions: An architectural guarantee ensuring database transaction reliability. Transactions are bundles of SQL operations treated as a single cohesive unit, complying with four core attributes:

    • Atomicity: The complete block succeeds or is rolled back completely.

    • Consistency: The database state remains valid throughout execution.

    • Isolation: Independent execution processes operate without cross-interference.

    • Durability: Committed modifications survive system crashes and power failures.

  • Replication: A system capability that automatically duplicates and syncs data transactions from a main master database server to one or more auxiliary slave servers, improving system backup resilience, query load distribution, and database availability.

  • Common Relational Database Architecture Categories:

    • Database: A physical logical container holding a collection of schemas, tables, views, procedures, and security indexes. Inside MySQL, each database behaves as an independent container.

    • Table: A physical structure representing a collection of related records, composed of rows (records) and columns (attributes).

    • Row: Represents a single complete structured record within a table.

    • Column: Represents a specific field with its associated storage rules and data types (e.g., VARCHAR, INT, DATE).

-- Creating and Opening a Database in MySQL
CREATE DATABASE customer_records;
USE customer_records;

Data Types & Storage Selection Optimization

Choosing optimized, correct data types is critical for database structural health. It limits disk allocation, speeds up indexing, and ensures strict data alignment.

  • Numeric Data Types:

    • TINYINT: A very small integer type. Signed range is −128-128 to 127127; unsigned range is 00 to 255255. Ideal for status flags, states, or boolean configurations (00 or 11).

    • SMALLINT: A small integer type. Signed range is −32,768-32,768 to 32,76732,767; unsigned range is 00 to 65,53565,535.

    • INT / INTEGER: A standard integer type used for general quantities, identifying codes, or primary key increments. Range spans approximately −2.14×109-2.14 \times 10^9 to 2.14×1092.14 \times 10^9 (specifically −2,147,483,648-2,147,483,648 to 2,147,483,6472,147,483,647).

    • BIGINT: A large integer type for very high numeric scales (e.g., international financial transactions, system logs, or user session counters).

    • FLOAT / DOUBLE: Floating-point types for real numbers with approximate precision. FLOAT uses single-precision (4 bytes4\,\text{bytes}), while DOUBLE uses double-precision (8 bytes8\,\text{bytes}).

    • DECIMAL / NUMERIC: An exact fixed-point numeric type where the exact scale and precision are defined by the designer. Ideal for currency, monetary fields, and absolute precision calculations. Example: DECIMAL(10, 2) guarantees 1010 total digits, with 22 dedicated to the fractional part.

  • Date and Time Data Types:

    • DATE: Stores calendar dates in the YYYY-MM-DD format, without any time zone attachment. Ranges from 1000-01-01 to 9999-12-31.

    • DATETIME: Stores composite date and time in the YYYY-MM-DD HH:MM:SS format. Spans 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Independent of local server time zones.

    • TIMESTAMP: Stores timezone-aware date and time values. It records the total seconds elapsed since 1970-01-01 00:00:00 UTC. Values automatically adjust dynamically to correspond with the client's current connection timezone.

    • TIME / YEAR: TIME stores time offsets or durations in the HH:MM:SS format, while YEAR stores four-digit calendar years.

  • String and Text/Binary Data Types:

    • CHAR: A fixed-length character string. If an insert contains fewer characters than the predefined length, MySQL pads the empty spaces. Highly optimized for uniform-length strings like country abbreviations, state codes, or fixed hashes.

    • VARCHAR: A variable-length character string. It allocates only the actual size of the input string plus 1–2 bytes1\text{--}2\,\text{bytes} for length indexing. Essential for variable text fields like names, emails, and address fields.

    • TEXT: For storing large text-based payloads. Available in sizes: TINYTEXT, TEXT (up to 65 KB65\,\text{KB}), MEDIUMTEXT, and LONGTEXT (up to 4 GB4\,\text{GB}). TEXT columns cannot have default values and are stored outside the primary table row space.

    • BLOB (Binary Large Object): Stores large unformatted binary data blocks (e.g., media files, document PDFs, or encrypted binary code). Available as TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

Keys, Relationship Cardinality & Referential Actions

  • Core Relational Database Keys:

    • Primary Key: A column (or column group) that uniquely identifies each record in a table. It cannot contain duplicate values and strictly blocks any NULL entries, enforcing Entity Integrity.

    • Foreign Key: A column mapping to a parent table's Primary Key. It establishes a relational constraint between columns in a child table and parent table, enforcing Referential Integrity.

    • Unique Key: Enforces distinct values across all column records in a table to prevent duplication. Unlike primary keys, a Unique Key allows a single NULL value.

    • Composite Key: A primary or unique key made up of two or more columns that together uniquely identify a record, used when a single attribute is insufficient.

  • Database Relationship Types:

    • One-to-One (1:1): An association where each record in Table A matches exactly one record in Table B. This is typically implemented by placing the primary key of Table A as a UNIQUE Foreign Key inside Table B.

    • One-to-Many (1:N): The most common database relationship. Each row in Table A can match multiple rows in Table B, but a row in Table B can only relate to a single row in Table A. Implemented by placing Table A's Primary Key as a Foreign Key inside Table B.

    • Many-to-Many (M:N): An association where multiple rows in Table A can map to multiple rows in Table B. It is structurally impossible to link them directly; instead, a Junction Table (or Bridge Table) is created with Foreign Keys referencing both parent tables.

  • Advanced Cascade Rules and Referential Actions:

    • ON DELETE CASCADE: A referential rule. If a record in the parent table is deleted, MySQL automatically and immediately deletes all referencing rows inside the child table, preventing orphan keys.

    • ON UPDATE CASCADE: If a Primary Key value inside a parent table is modified, MySQL automatically updates the corresponding Foreign Key values inside the referencing child table rows to maintain reference alignment.

    • SET NULL: If a parent row is deleted or modified, MySQL automatically resets the referencing child row's Foreign Key columns to NULL, provided those columns are not configured with a NOT NULL constraint.

-- Configuring Foreign Keys with Cascade Actions
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

MySQL Integrity Constraints & Data Validation

Constraints are defensive validation rules applied to columns. They block invalid or malformed write attempts, protecting database accuracy at the transactional boundary.

  • NOT NULL: Enforces a mandatory value policy for a column, rejecting any insert or update attempt that omits this column or supplies a NULL value.

  • UNIQUE: Ensures all data within the column remains distinct. Perfect for securing unique credentials like usernames or emails.

  • PRIMARY KEY: Enforces both NOT NULL and UNIQUE on the specified column to uniquely identify each row.

  • FOREIGN KEY: Validates that any entered value exists in the referenced parent table, enforcing referential links.

  • CHECK: Validates inputs against a logical Boolean expression. If an input evaluates to FALSE, the system rejects the statement. Example: CHECK (unit_price >= 0.00).

  • DEFAULT: Supplies a fallback default value for a column if no explicit value is provided during an INSERT command.

-- Composite Constraints & Validation Rules in Table Creation
CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  sku_code VARCHAR(50) UNIQUE NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  unit_price DECIMAL(10, 2) DEFAULT 0.00 CHECK (unit_price >= 0.00),
  stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);

Structural Query Language (SQL) Basics & CRUD

SQL commands are categorized based on their functional purpose within the database:

  • DDL (Data Definition Language): Commands that define, modify, or delete the physical structural schemas of database objects (e.g., CREATE, ALTER, DROP, TRUNCATE).

  • DML (Data Manipulation Language): Commands that manipulate and write data within the structured tables without modifying the schema structure (e.g., INSERT INTO, UPDATE, DELETE).

  • DQL (Data Query Language): Commands that retrieve and filter data records from tables (e.g., SELECT).

  • DCL (Data Control Language): Commands that manage user security access privileges and database permissions (e.g., GRANT, REVOKE).

  • Core CRUD Syntax Guides & Best Practices:

    • INSERT INTO: DML command used to add new rows of data into a table. Values can be added to specific columns or all columns in order of schema definition. sql -- Inserting records INSERT INTO products (sku_code, product_name, unit_price) VALUES ('SKU-100', 'Optical Mouse', 25.50);     

    • SELECT: DQL command to retrieve and filter data rows. Can be combined with WHERE clauses to filter records, or ORDER BY to sort them. sql -- Filtering and retrieving values SELECT product_name, unit_price FROM products WHERE unit_price > 20.00;     

    • UPDATE: DML command used to modify existing values in a table. It must be combined with a WHERE clause; otherwise, all records will be updated globally. sql -- Updating specific row values UPDATE products SET unit_price = 29.99 WHERE sku_code = 'SKU-100';     

    • DELETE: DML command to remove specific rows from a table using a WHERE filter. If the WHERE clause is omitted, all records are deleted, but the table structure, columns, and constraints remain intact. sql -- Deleting specific rows DELETE FROM products WHERE sku_code = 'SKU-100';     

Database Table Manipulation & ALTER Commands

ALTER TABLE is the primary DDL command used to modify the structural definition of an existing database table. It acts as a parent command for specific sub-commands to modify, add, delete, or rename columns and integrity constraints.

  • Adding Columns (ADD COLUMN): Appends a new column to an existing table structure. By default, newly added columns are initialized with NULL values for all pre-existing rows.

  -- Appending a column post-creation
  ALTER TABLE products ADD COLUMN discount_rate DECIMAL(3, 2) DEFAULT 0.00;
  ```

- **Modifying Column Specifications (`MODIFY COLUMN`)**: Changes the properties of an existing column, such as altering its data type, changing character length, or adding/removing constraints.

sql -- Modifying data type size and constraints ALTER TABLE products MODIFY COLUMN product_name VARCHAR(150) NOT NULL;   ```

  • Renaming Columns and Tables:

    • RENAME COLUMN: Changes the physical name of an existing column in a table, adjusting system references without altering any underlying data.

    -- Renaming columns
    ALTER TABLE products RENAME COLUMN unit_price TO base_price;
        ```
    - **RENAME TABLE**: DDL command to rename an entire table to adjust system references.
    

    sql -- Renaming a table RENAME TABLE products TO store_items;     ```

  • Deleting Columns and Post-Creation Constraints:

    • DROP COLUMN: Permanently deletes an existing column from a table, immediately and irreversibly erasing all data stored within that column.

    -- Dropping a column
    ALTER TABLE store_items DROP COLUMN discount_rate;
        ```
    - **ADD CONSTRAINT**: Appends a new named constraint (e.g., primary or foreign key) to an existing table structure.
    

    sql -- Appending post-creation primary and unique keys ALTER TABLE store_items ADD CONSTRAINT pk_items PRIMARY KEY (product_id);     ```

    • DROP CONSTRAINT: Removes a defined constraint from a table using its constraint name. sql -- Dropping constraints ALTER TABLE store_items DROP CONSTRAINT pk_items;     

  • Duplicating and Copying Tables:

    • Copying Structure Only: Utilizes the LIKE clause to create an exact structural clone of a table (including data types, indexes, and constraints) without copying any data records.

    -- Creating structural empty backup
    CREATE TABLE backup_items LIKE store_items;
        ```
    - **Copying Structure and Data**: Uses an `INSERT INTO ... SELECT` statement to clone both the physical structure and copy all existing rows from the original table to the destination table.
    

    sql -- Copying structure with data records CREATE TABLE archive_items LIKE store_items; INSERT INTO archive_items SELECT * FROM store_items;     ```

  • Metadata Inspections & Schema Diagnostics:

    • DESCRIBE (DESC): Utility command used to display the detailed metadata and physical structure of a table, displaying column names, data types, sizes, nullability, constraints, and default values. sql -- Inspecting table structure DESCRIBE store_items;     

Advanced Destruction Commands & Comparison

  • DELETE: A transactional DML command that removes rows based on a WHERE filter. This statement deletes rows individually, logs each deletion, and preserves the table structure, columns, and constraints.

  • TRUNCATE TABLE: A highly efficient DDL command that removes all rows from a table by deallocating the database storage pages. It maintains the table schema, resets auto-incrementing identity columns, operates faster than DELETE, and cannot be rolled back.

  • DROP TABLE: A highly destructive DDL command that completely deletes a table's schema, structure, indexes, metadata, and all of its data from the database. Once dropped, the table ceases to exist.

  • Comparison Matrix of Deletion Strategies:

Feature / Aspect

DELETE Command

TRUNCATE TABLE

DROP TABLE

Command Type

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Definition Language (DDL)

Logical Action

Removes specific or all rows based on a WHERE clause.

Deallocates database storage pages, emptying the table.

Permanently destroys the table schema, constraints, and data.

Performance

Slower; row-by-row logging.

Extremely Fast; minimally logged.

Instantaneous; drops structural definitions.

Rollback Ability

Yes; fully rollable back in transactions.

No; cannot be rolled back under standard execution.

No; completely irreversible.

Resets Auto-ID

No; values resume from where they stopped.

Yes; resets the increment sequence seed back to 11.

N/A; the table and sequence are completely destroyed.

WHERE Clause

Yes; allows targeted row deletion.

No; always purges the entire table structure.

No; target filters are not supported.