Introduction to Relational Databases and MySQL Architecture
Client/Server System Architecture and Hardware Components
Three Main Hardware Components of a Client/Server System:
Clients: End-user hardware devices such as desktop computers, laptops, tablets, and smartphones that run user interface programs and send requests to the server.
Server: A centralized computer with high processing power and storage capacity that houses the database management system and services requests from clients.
Network: The electronic communication pipeline connecting clients to the server, enabling data transmission across physical cabling or wireless signals.

Key Network Topologies and Infrastructures:
Local Area Network (LAN): A network configuration spanning a small geographical area, such as a single office suite, floor, or building, providing high transfer speeds.
Wide Area Network (WAN): A telecommunications network spanning expansive geographic regions, connecting multiple remote locations, branches, or cities.
Cloud Computing Platform: Virtualized hardware and network services hosted off-site by third-party providers, allowing flexible provisioning of database and application servers.
Enterprise System: A comprehensive information system spanning an entire corporation or organization, supporting complex multi-departmental business workflows across disparate geographic networks.
Software Components and the SQL Interface
Division of Software Responsibilities:
Client Software (Front-End Processing):
Application Software: Executes the user interface and business logic, providing the visual layout, forms, input validation, and user interaction mechanics.
Data Access API (Application Programming Interface): Acts as the communication bridge between the application software and the database server, formatting and routing queries while receiving results.
Front-End Processing: Encompasses all processing tasks executed directly on the client machine, including rendering visual elements and capturing user inputs before query transmission.
Server Software (Back-End Processing):
Database Management System (DBMS): Software running on the server responsible for data integrity, transaction processing, indexing, security, disk storage, and query execution.
Back-End Processing: The intensive computations performed directly on the server machine, focusing on data retrieval, row indexing, record locking, and data persistence.

The Structured Query Language (SQL) Communication Flow:
Standard Definition: Structured Query Language (SQL) is the universally recognized standard language used to interact with relational databases.
Query Transmission: Application software generates a data request and routes it through the data access API as an SQL query transmitted across the network.
Server Processing and Data Return: Upon receiving the SQL query, the DBMS parses, compiles, optimizes, and executes it against physical storage, subsequently returning the structured query results back to the client.
Multi-Tier Architecture Implementations
Three-Tier Application Server Architecture:
Client Tier: Houses the user interface layer on end-user hardware, transmitting user requests to the middle tier rather than directly to the database.
Application Server Tier: Contains the application server software executing the core business logic; it translates user requests into formal SQL queries and forwards them to the database engine.
Database Server Tier: Houses the DBMS and data stores; it executes the incoming SQL queries from the application server and returns the raw result sets back to the application server for formatting.

Web-Based Database Architecture:
Web Browser Client: Operates on the client workstation or mobile device, transmitting HTTP requests over the public Internet.
Web Server: Receives incoming HTTP requests across the Internet, executes server-side scripts or applications, converts requests into SQL queries, and issues them to the database server.
Database Server: Executes the SQL queries, returning results to the web server, which generates an HTML/HTTP response document that is routed back to the client web browser.

Relational Database Organization and Structural Elements
Fundamental Relational Structures:
Relational Database: A collection of structured data arranged into two-dimensional tables consisting of rows and columns, linked through logical relationships.
Table: A two-dimensional structure (relation) containing distinct records of a specific entity type.
Column: Represents a single attribute or field of information within the table (e.g.,
vendor_name,vendor_city).Row: A single horizontal record (tuple) representing one complete entity instance within the table.
Cell: The discrete intersection of a single row and a single column containing one atomic data item.

Primary and Unique Key Constraints:
Primary Key: A designated column (or set of columns) that uniquely identifies every individual row in a table. It cannot contain null values.
Composite Primary Key: A primary key formed by combining two or more distinct columns to establish a unique record identifier.
Non-Primary Key (Unique Key): A column or combination of columns that must contain unique values across all rows, preventing duplicate entries while not functioning as the designated primary key.
Index: A specialized physical data structure created by the DBMS to allow rapid locating and retrieval of specific rows without requiring a full table scan.
Table Relationships and Referential Integrity
Foreign Keys and Associative Mechanisms:
Foreign Key: A column in a child table that corresponds directly to the primary key column in a parent table, establishing a referential link between both data sets.
Referential Integrity: Enforces that a foreign key value in a child table must match an existing primary key value in the referenced parent table or be explicitly defined as
NULL.

The Three Types of Relational Cardinality:
One-to-Many Relationship (): The most common relational pattern where a single row in table A links to zero, one, or multiple rows in table B (e.g., one vendor can have multiple invoices, but each invoice belongs to only one vendor).
One-to-One Relationship (): A restrictive association where a single row in table A links to exactly one corresponding row in table B.
Many-to-Many Relationship (): A configuration where multiple rows in table A link to multiple rows in table B; this relationship cannot be modeled directly and requires an intermediate bridge (junction) table containing foreign keys referencing both parent entities.
Enhanced Entity Relationship (EER) Diagrams:
An EER diagram models the overall architectural schema of a database, displaying tables, attributes, primary/foreign keys, and cardinalities.
In the Accounts Payable (AP) schema, the relationships include:
vendorstoinvoices: One-to-many relationship viavendor_id.termstoinvoices: One-to-many relationship viaterms_id.invoicestoinvoice_line_items: One-to-many relationship viainvoice_id.general_ledger_accountstoinvoice_line_items: One-to-many relationship viaaccount_number.termstovendors: One-to-many relationship linking default payment terms viadefault_terms_id.general_ledger_accountstovendors: One-to-many relationship linking default accounts viadefault_account_number.

Column Definitions, Constraints, and Data Types
Column Definitions and Structural Modifiers:
Data Type: Specifies the category of data that can be stored in the column, regulating memory allocation and valid operations.
Null Value: A marker indicating that a specific data value is unknown, unrecorded, or inapplicable (
NULL). It does not equal zero or an empty string.Default Value: A pre-assigned explicit value automatically inserted into a cell if no value is provided during row insertion.
Auto Increment Column: An integer column configured so the DBMS automatically generates a unique, sequential number upon the insertion of every new row.

Common MySQL Data Types:
CHAR: Fixed-length character storage. Space is padded if data is shorter than the defined length.VARCHAR: Variable-length character storage up to a designated maximum length (e.g.,VARCHAR(50)).INT: Integer values containing whole numbers without decimal points.DECIMAL: Exact numeric storage with fixed precision and scale, critical for monetary amounts (e.g.,DECIMAL(9,2)stores up to digits total, with digits positioned after the decimal point).FLOAT: Approximate numeric values representing floating-point numbers.DATE: Stores date representations formatted asYYYY-MM-DD.
Comparison of Major Relational Database Management Systems
Standard SQL versus Product Dialects:
Universal Syntax: The foundational core of SQL syntax is standardized by ANSI/ISO, ensuring basic data definition and querying techniques remain consistent across systems.
Dialect Differences: Each proprietary and open-source database implementation includes specialized extensions, non-standard functions, and unique configuration rules.
Application Portability: Due to dialect variations, migrating database-driven software applications from one RDBMS engine to another almost universally requires code revisions.
Comparison Matrix of Enterprise Relational Databases:
Oracle Database:
Initial Release:
Supported Operating Systems: Unix, z/OS, Windows, Linux, macOS
Primary Application Domain: Large-scale, mission-critical enterprise systems commonly deployed across one or more Unix servers.
IBM DB2:
Initial Release:
Supported Operating Systems: OS/390, z/OS, AIX, Unix, Windows, Linux, macOS
Primary Application Domain: Mission-critical legacy environments operating on IBM enterprise mainframes using the z/OS or OS/390 operating systems.
Microsoft SQL Server:
Initial Release:
Supported Operating Systems: Windows, Linux
Primary Application Domain: Small- to medium-sized corporate systems operating on Windows or Linux server architectures.
MySQL:
Initial Release:
Supported Operating Systems: Unix, Linux, Windows, macOS
Primary Application Domain: Highly popular open-source database running on all major platforms, widely utilized for web application development.
Categorization of SQL Statements: DML vs. DDL
Data Manipulation Language (DML) Statements:
Used for querying, adding, editing, and deleting records stored within existing database tables.
SELECT: Retrieves designated columns and rows of data from one or more tables.INSERT: Adds new rows of data into a table.UPDATE: Alters existing data values within one or more table rows.DELETE: Removes one or more existing rows from a table.
Data Definition Language (DDL) Statements:
Used to create, alter, and delete database structures, schema definitions, and management objects.
CREATE DATABASE: Instantiates a new database container.CREATE TABLE: Defines and establishes a new table schema.CREATE INDEX: Creates an index on specified table columns to improve retrieval performance.ALTER TABLE: Modifies an existing table's schema, such as adding or removing columns and constraints.ALTER INDEX: Modifies properties of an existing database index.DROP DATABASE: Permanently removes an existing database and all contained tables.DROP TABLE: Permanently deletes an existing table and its contents from the database.DROP INDEX: Removes an existing index from an underlying table.
Data Definition Language (DDL) Syntax and Implementation
Database Creation and Context Selection:
Creates a database named
ap:sql CREATE DATABASE ap;
Directs the active session to perform subsequent operations on the
apdatabase:sql USE ap; Table Creation with Constraints and Foreign Keys:
Establishes the
invoicestable with primary keys, auto-incrementing counters, foreign keys, not null constraints, and defaults:sql CREATE TABLE invoices ( invoice_id INT PRIMARY KEY AUTO_INCREMENT, vendor_id INT NOT NULL, invoice_number VARCHAR(50) NOT NULL, invoice_date DATE NOT NULL, invoice_total DECIMAL(9,2) NOT NULL, payment_total DECIMAL(9,2) DEFAULT 0, credit_total DECIMAL(9,2) DEFAULT 0, terms_id INT NOT NULL, invoice_due_date DATE NOT NULL, payment_date DATE, CONSTRAINT invoices_fk_vendors FOREIGN KEY (vendor_id) REFERENCES vendors (vendor_id), CONSTRAINT invoices_fk_terms FOREIGN KEY (terms_id) REFERENCES terms (terms_id) );
Modifying Schema Columns and Managing Indexes:
Appends a new column named
balance_duewith precisionDECIMAL(9,2)to theinvoicestable:sql ALTER TABLE invoices ADD balance_due DECIMAL(9,2);
Removes the
balance_duecolumn from theinvoicestable:sql ALTER TABLE invoices DROP COLUMN balance_due; Creates an index on the
vendor_idcolumn of theinvoicestable to optimize foreign key search operations:sql CREATE INDEX invoices_vendor_id_index ON invoices (vendor_id); Deletes the specified index from the table:
sql DROP INDEX invoices_vendor_id_index ON invoices;
Data Manipulation Language (DML): Querying Data with SELECT
Query Concepts and Result Sets:
Query: An SQL command designed to extract data matching defined conditions from a database.
Base Table: The persistent physical table stored directly on the storage media from which queries draw data.
Result Table (Result Set): The temporary tabular output generated in memory by the DBMS upon evaluating a
SELECTstatement.Calculated Value: A column defined dynamically in the
SELECTlist by applying arithmetic or string operations across other columns.

Retrieving, Filtering, and Sorting Columns:
Computes a dynamic balance due column where , filtering for values greater than and sorting chronologically by date:
sql SELECT invoice_number, invoice_date, invoice_total, payment_total, credit_total, invoice_total - payment_total - credit_total AS balance_due FROM invoices WHERE invoice_total - payment_total - credit_total > 0 ORDER BY invoice_date;

Combining Tables using Joins:
Join Operation: An SQL mechanism that combines data rows from two or more tables based on common relational keys.
Inner Join: Evaluates conditions between two tables and extracts only those records where a match exists on both sides.
Outer Join: Returns all rows from one table along with matching records from the joined table; unmatched rows are populated with
NULL.Joined Query Implementation: Joins the
vendorstable to theinvoicestable onvendor_id, restricts output to invoices where , and orders by vendor name followed by descending invoice total:sql SELECT vendor_name, invoice_number, invoice_date, invoice_total FROM vendors INNER JOIN invoices ON vendors.vendor_id = invoices.vendor_id WHERE invoice_total >= 500 ORDER BY vendor_name, invoice_total DESC;

Data Manipulation Language (DML): Modifying Table Data
Inserting Data Rows:
Inserts a complete record into the
invoicestable with explicit values for designated columns:sql INSERT INTO invoices (vendor_id, invoice_number, invoice_date, invoice_total, terms_id, invoice_due_date) VALUES (12, '3289175', '2022-07-18', 165, 3, '2022-08-17');
Updating Existing Records:
Updates the credit total for a single row specified by an exact invoice number:
sql UPDATE invoices SET credit_total = 35.89 WHERE invoice_number = '367447';
Updates multiple rows simultaneously by extending the
invoice_due_dateby usingDATE_ADD()for all records matching terms identifier :sql UPDATE invoices SET invoice_due_date = DATE_ADD(invoice_due_date, INTERVAL 30 DAY) WHERE terms_id = 4; Deleting Records:
Deletes an isolated row from the table targeting a unique invoice number:
sql DELETE FROM invoices WHERE invoice_number = '4-342-8069';
Evaluates an arithmetic expression to delete all completely settled invoices where the balance due equals :
sql DELETE FROM invoices WHERE invoice_total - payment_total - credit_total = 0;
SQL Coding Standards and Best Practices
Readability and Code Layout Comparison:
Unformatted SQL written as a continuous, uncapitalized block impairs maintainability and code reviews:
sql select invoice_number, invoice_date, invoice_total, payment_total, credit_total, invoice_total - payment_total - credit_total as balance_due from invoices where invoice_total - payment_total - credit_total > 0 order by invoice_date;
Standardized SQL formatted with explicit line breaks, capitalized keywords, and indented expressions:
sql SELECT invoice_number, invoice_date, invoice_total, payment_total, credit_total, invoice_total - payment_total - credit_total AS balance_due FROM invoices WHERE invoice_total - payment_total - credit_total > 0 ORDER BY invoice_date; SQL Comment Syntax:
Block Comments: Delimited by
/*and*/, encompassing multiple documentation lines for author information, revision histories, and script summaries:sql /* Author: Joel Murach Date: 8/22/2023 */ SELECT invoice_number, invoice_date, invoice_total, invoice_total - payment_total - credit_total AS balance_due FROM invoices;
Single-Line Comments: Indicated by two hyphens followed by a space (
--), explaining inline column logic or specific operations:sql -- The fourth column calculates the balance due SELECT invoice_number, invoice_date, invoice_total, invoice_total - payment_total - credit_total AS balance_due FROM invoices; Recommended Formatting Conventions:
Capitalize all SQL reserved keywords (
SELECT,FROM,WHERE,ORDER BY,AND,OR,AS).Use lowercase formatting for non-keyword elements, such as table names, column names, and aliases.
Separate individual words within database object identifiers using underscores (snake_case).
Begin every primary SQL clause on a new line.
Break long expressions or extended column selections into separate lines.
Indent continued lines under parent clauses to maintain visual hierarchy.
Restrict comments strictly to code blocks that exhibit complex or non-intuitive business logic.
DBMS Parser Mechanics:
The MySQL execution engine ignores line breaks, extra whitespace, indentation, and letter case variations when parsing SQL statements; these standards exist entirely for human readability and maintainability.