Front Matter

Preface

This book teaches relational database systems from first principles to working applications. It integrates database theory, database design, SQL programming, and hands-on practice with PostgreSQL and MySQL — two of the most widely deployed open-source database systems in industry — into a single, unified text. It is written for undergraduate courses in Computer Science and Engineering (CSE), Electrical and Computer Engineering (ECE), Information Technology (IT), and Data Science, and it also serves the independent reader who wants a complete, self-contained path from "what is a database?" to building, securing, and administering one.

Two principles shape the book. First, theory earns its keep through practice: the relational model, relational algebra, functional dependencies, and normalization are presented precisely, and each idea is immediately connected to the SQL you will write and the designs you will produce. Second, one platform leads, one platform compares: PostgreSQL is the primary platform used for the main worked examples, and MySQL appears throughout as the comparative platform. Standard SQL is always distinguished from PostgreSQL-specific and MySQL-specific syntax, and the two systems are presented side by side wherever their differences are educationally useful — never as rival camps, but as two mature implementations of the same relational foundation.

Every SQL example in this book is realistic and internally consistent. From Chapter 8 onward, examples run against a small but complete university database — departments, students, instructors, courses, sections, and enrollments — defined once, in full, in Appendix H, and reused everywhere. Expected outputs are shown beneath the listings so you can check your own results against them.

Book Organization

The book progresses from fundamental concepts to practical implementation, in eight parts:

• Part I — Database Fundamentals and Relational Theory (Chapters 1–4): what databases are and why they exist; the relational model; relational algebra and calculus; and database system architecture.
• Part II — Database Modeling and Design (Chapters 5–7): requirements analysis and ER modeling; mapping ER diagrams to relational schemas with keys and constraints; and functional dependencies with normalization from 1NF through 5NF.
• Part III — SQL: Structured Query Language (Chapters 8–13): SQL fundamentals and command categories; table definition; data manipulation; functions and aggregation; joins, subqueries, and CTEs; and advanced SQL including window functions and set operations.
• Part IV — PostgreSQL and MySQL in Practice (Chapters 14–17): installing and configuring both systems; database development in each; and a systematic comparative study of the two platforms.
• Part V — Transactions, Concurrency, and Performance (Chapters 18–21): ACID and isolation; indexing and query optimization; security; and backup, recovery, and administration.
• Part VI — Database Programming and Application Development (Chapters 22–24): stored procedures, functions, and triggers; connecting Java, Python, and PHP applications; and database-backed APIs and web integration.
• Part VII — Advanced Topics and Applications (Chapters 25–27): data warehousing and analytical SQL; distributed databases and cloud deployment; and relational databases among emerging technologies.
• Part VIII — Laboratory Exercises and Projects (Chapters 28–30): a structured SQL laboratory, eight end-to-end case studies, and a guided capstone project.

Chapters 1–7 establish the relational model, algebra, ER modeling, keys, constraints, and normalization. Chapters 8–17 develop SQL skills with hands-on PostgreSQL and MySQL implementation, including their similarities and differences. Chapters 18–21 address concurrency, optimization, security, backup, recovery, and database administration. Chapters 22–30 connect database theory with stored programming, application connectivity, analytical systems, laboratory work, and capstone projects.

The appendices provide reference material you will use throughout the course: SQL syntax (A), installation and command references for PostgreSQL (B) and MySQL (C), data types and constraints (D), relational algebra notation (E), ER symbols (F), normalization exercises with solutions (G), the complete university schema and dataset (H), a PostgreSQL-versus-MySQL syntax reference (I), administration commands (J), graded practice problems with solutions (K), a glossary (L), and a curated further-reading list (M).

How to Use This Book

Each chapter is designed for both classroom teaching and independent study, and follows the same pedagogical pattern:

• Learning objectives at the start, phrased as things you will be able to do.
• Key concepts defined in bold when first introduced, then used consistently.
• Explanations with worked examples and diagrams, including expected outputs for SQL listings.
• PostgreSQL and MySQL implementation notes wherever the platforms differ.
• Laboratory exercises near the end, with concrete tasks and expected results.
• Review questions and exercises, mixing conceptual checks with SQL-writing practice, with answers and solutions provided inline.
• A chapter summary and key terms to consolidate what was learned.
• A mini-project where the material naturally supports one.

If you are new to databases, read the chapters in order; each part assumes the vocabulary of the parts before it. If you are using the book for a SQL course that skips deep theory, Chapters 8–13 form a self-contained SQL sequence that can be read with occasional reference back to Chapters 2 and 7. Course instructors can assign the laboratory exercises in Chapter 28 as weekly labs, one case study from Chapter 29 as a group assignment, and Chapter 30's capstone as the semester project.

Laboratory Environment

To work through the examples you need PostgreSQL 16 or later, or MySQL 8.0 or later, on Windows, Linux, or macOS. Chapter 14 covers installation on Windows and Linux in detail, including the psql and mysql command-line clients, pgAdmin, and MySQL Workbench. Once a server is running:

1. Create the sample database (Chapter 9 shows the SQL; Appendix H has the full script).
2. Load the university schema and dataset from Appendix H — every table, row, and expected output in Parts III–VI is consistent with that dataset.
3. Follow along with each chapter's listings in your own client session.

Because expected outputs are printed beneath the listings, you can verify every query you type. Where a listing's exact output depends on data you added yourself in an exercise, the text says so.

Typographic Conventions

• SQL keywords are written in UPPERCASE (SELECT, FROM, WHERE); identifiers use lower_snake_case.
• SQL appears in monospace blocks, one clause per line for longer statements, terminated with a semicolon.
• Expected output of a query appears in a separate monospace block immediately beneath the listing.
• Command-line sessions appear with the psql prompt (university=>) or the mysql> prompt, so you can tell at a glance which platform is being exercised.
• Important listings are captioned in bold, for example: Listing 12.1 — Sections with their instructors.
• Diagrams — ER diagrams, architecture layers, transaction timelines — are drawn in plain text so they can be read anywhere and reproduced in your own notes.
• Platform notes are set inline: In MySQL, ... marks a divergence from the PostgreSQL/default form being shown.

The book is written as of PostgreSQL 16 and MySQL 8.0 and their later versions; features removed or introduced beyond those versions are not assumed.

— ITL Books