Databases function as structured repositories for persistent data management. Unlike transient memory variables or isolated local files, database systems enable concurrent access, network sharing, and robust integrity controls. Systems are broadly categorized into relational and non-relational architectures. Relational Database Management Systems (RDBMS) enforce structured relationships between data entities, typically persisting information to disk. Prominent implementations include MySQL (open-source, widely adopted for web applications), Oracle (enterprise-grade with advanced clustering), SQL Server (Windows-integrated), MariaDB (MySQL-compatible fork), and SQLite (embedded, lightweight). Conversely, NoSQL databases prioritize high-throughput, schema-less storage using key-value or document models, often residing in memory for speed. Common examples include MongoDB, Redis, and Memcached, though memory-resident systems risk data loss on power failure without explicit persistence configurations.

Core architectural components include:

• Record: A single row containing related data fields.
• Table: A structured collection of records sharing a defined schema.
• Database: A logical container housing multiple tables.
• DBMS: The software layer managing data storage, retrieval, and security.
• Database Server: The host machine executing the DBMS process and listening for network requests.

Deployement Procedures

Windows Environment

1. Retrieve the compressed archive from the official distribution portal and extract it.
2. Launch an elevated command prompt, navigate to the bin directory, and initialize the data directory using mysqld --initialize.
3. Append the bin path to the system PATH environment variable for global CLI access.
4. Register the daemon as a Windows service via mysqld --install. Manage lifecycle using net start mysql and net stop mysql. Service removal requires mysqld --remove prior to uninstallation.
5. Connect via the CLI client: mysql -h <host> -P <port> -u <user> -p. Local connections default to port 3306 and can omit host/port flags.

Linux (CentOS 7) Environment

1. Acquire dependencies: yum -y install wget libaio autoconf.
2. Remove conflicting MariaDB packages: rpm -qa | grep mariadb followed by rpm -e --nodeps <package_name>.
3. Download and extract the binary tarball to /usr/local/, renaming the directory to mysql.
4. Initialize the data directory: /usr/local/mysql/bin/mysqld --initialize --user=mysql.
5. Configure the service daemon: cp support-files/mysql.server /etc/init.d/mysqld, then enable auto-start with chkconfig --add mysqld.
6. Define character encoding in /etc/my.cnf:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
[client]
default-character-set = utf8mb4

7. Export binary path: echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile && source /etc/profile.
8. Configure remote access and firewall:

CREATE USER 'admin_user'@'%' IDENTIFIED BY 'secure_pass';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%';
FLUSH PRIVILEGES;

Disable firewall restrictions: systemctl stop firewalld && systemctl disable firewalld.

Authentication & Character Set Configuration

Credential Management: When the current password is known: mysqladmin -u root -p password 'new_secret'. For lost credentials, bypass the grant tables:

1. Halt the service: net stop mysql (Windows) or service mysqld stop (Linux).
2. Start safely: mysqld --skip-grant-tables.
3. Connect without authentication and reset:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secret';

4. Restart the daemon normaly.

Character Set Configuration: Verify active settings via \s in the CLI. Enforce UTF-8 globally by placing the [mysqld], [mysql], and [client] blocks in the configuration file (my.ini or my.cnf) as demonstrated in the Linux deployment section.

Fundamental SQL Operations

Schema Management:

CREATE DATABASE app_data CHARACTER SET utf8mb4;
SHOW DATABASES;
ALTER DATABASE app_data CHARACTER SET latin1;
DROP DATABASE app_data;
USE app_data;
SELECT DATABASE();

Table Manipulation:

CREATE TABLE employees (emp_id INT, full_name VARCHAR(50));
CREATE TABLE emp_backup SELECT host, user FROM mysql.user; -- Structure + Data
CREATE TABLE emp_template LIKE mysql.user; -- Structure only
SHOW TABLES;
DESCRIBE employees;
ALTER TABLE employees ADD COLUMN department VARCHAR(30);
ALTER TABLE employees MODIFY COLUMN full_name VARCHAR(100);
ALTER TABLE employees CHANGE COLUMN full_name employee_name VARCHAR(100);
ALTER TABLE employees DROP COLUMN department;
RENAME TABLE employees TO staff;
DROP TABLE staff;

Data Manipulation:

INSERT INTO staff (emp_id, employee_name) VALUES (101, 'Alice'), (102, 'Bob');
SELECT emp_id, employee_name FROM staff;
UPDATE staff SET employee_name = 'Charlie' WHERE emp_id = 101;
DELETE FROM staff WHERE emp_id = 102;
TRUNCATE TABLE staff; -- Fast wipe, resets auto-increment

Storage Architecture & Engines

MySQL processes queries through a layered pipeline: connection pooling, SQL parsing, optimization, caching, and finally, the storage engine layer. The engine dictates how data is physically stored, indexed, and locked. Common engines include:

• InnoDB: Default engine supporting ACID transactions, row-level locking, and foreign keys.
• MyISAM: Legacy engine optimized for read-heavy workloads; lacks transaction support but offers faster full-table scans.
• MEMORY: Stores data in RAM for extreme speed; volatile across restarts.
• CSV: Stores rows as comma-separated text files; lacks indexing capabilities.
• MRG_MYISAM: Merges multiple identical MyISAM tables into a single logical view for horizontal partitioning.

Engine assignment occurs during creation: CREATE TABLE logs (id INT) ENGINE=InnoDB;. Globally, set default-storage-engine=InnoDB in the config. Enabling innodb_file_per_table=1 ensures each InnoDB table uses a dedicated .ibd file, preventing the shared ibdata1 system tablespace from bloating and simplifying backup/recovery operations.

Data Typing System

Numeric: Integers default to signed. Ranges scale by byte size: TINYINT (1B), SMALLINT (2B), MEDIUMINT (3B), INT (4B), BIGINT (8B). Append UNSIGNED to double the positive range. Display width (e.g., INT(5)) only affects padding when combined with ZEROFILL. Floating-point types: FLOAT (4B, ~7 digits), DOUBLE (8B, ~15 digits). For exact precision, use DECIMAL(M,D), which stores values as strings internally to avoid rounding errors.

String: CHAR(N) allocates fixed space, padding with spaces; faster for uniform lengths. VARCHAR(N) stores variable lengths with a 1-2 byte length prefix; conserves space but incurs slight overhead. Trailing spaces are stripped in CHAR comparisons but preserved in VARCHAR. Large text uses TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. Binary equivalents include BLOB variants. Use LENGTH() for bytes and CHAR_LENGTH() for characters.

Temporal: YEAR handles 1901-2155. DATE stores YYYY-MM-DD. TIME handles HH:MM:SS or intervals. DATETIME combines both. TIMESTAMP automatically updates to the current time on row modification and stores values in UTC, converting to the session timezone on retrieval.

Enumerations: ENUM('val1', 'val2') restricts input to a single predefined option. SET('opt1', 'opt2') allows multiple comma-separated selections from the defined list.

Constraints & Strict Mode

MySQL 5.7+ enables strict SQL mode by default, rejecting invalid or out-of-range data. For older versions, enforce it via SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';. Constraints enforce data rules at the column level:

• NOT NULL: Prohibits empty values.
• DEFAULT 'val': Assigns a fallback value.
• UNIQUE: Guarantees distinct values across rows; creates an implicit index. Supports composite uniqueness: UNIQUE(col_a, col_b).
• PRIMARY KEY: Combines NOT NULL and UNIQUE. InnoDB requires a primary key to structure its clustered B+ tree index. If omitted, MySQL promotes the first unique not-null column or generates a hidden 6-byte row ID.
• AUTO_INCREMENT: Automatically generates sequential integers for integer primary keys.

Relational Design & Foreign Keys

Foreign keys (FOREIGN KEY) enforce referential integrity between a parent (referenced) and child (referencing) table. The child column must reference a PRIMARY KEY or UNIQUE column in the parent. Rules:

1. Parent tables must exist before child tables.
2. Child inserts require matching parent keys.
3. Parent deletion/update is blocked if child records reference the key, unless cascading is configured: ON DELETE CASCADE or ON UPDATE CASCADE. While foreign keys guarantee consistency at the storage layer, high-concurrency applications often delegate relationship management to the application logic to avoid locking overhead and improve write throughput. Relationship mappings:

• One-to-One: Splits wide tables vertically for performance or security.
• One-to-Many: Standard parent-child hierarchy; foreign key resides in the "many" table.
• Many-to-Many: Requires a junction table containing foreign keys referencing both primary tables, typically guarded by a composite unique constraint to prevent duplicate associations.