Part I — Database Fundamentals and Relational Theory

Chapter 1. Introduction to Database Systems

Software almost never works in isolation. A university records which students are enrolled in which courses, a bank tracks thousands of transactions per second, an online store remembers your cart across visits, and a hospital must retrieve a patient's medication history instantly. All of these systems depend on storing data reliably and retrieving it quickly and correctly. Databases are the disciplined answer to that need, and the discipline is what this book teaches.

You already know how to program, so you have felt the problem personally: data kept in variables disappears when a program ends, and data scattered across hundreds of files becomes inconsistent the moment two parts of a program disagree about its format. Databases exist to solve exactly this. They centralize data, describe its structure precisely, enforce rules on it, and let many programs and users share it safely. This chapter introduces the vocabulary and concepts on which the rest of the book is built, using a small university database that we will carry through every chapter as our running example.

After studying this chapter you will be able to:

• Distinguish data, information, and knowledge, and explain why the distinction matters to system builders.
• Use core database terminology — table, row, column, schema, instance, query — precisely.
• Contrast file-processing systems with database systems and name the problems databases were invented to fix.
• Explain what a DBMS does, including its typical subsystems and services.
• Describe what makes a DBMS relational and why the relational model came to dominate.
• Identify the roles of database users, administrators, and developers.
• Give concrete examples of database applications across industries.
• Compare PostgreSQL, MySQL, SQLite, Oracle, and SQL Server at a high level.

1.1 Data, information, and knowledge

Data are recorded facts: raw, uninterpreted symbols such as 21100003, Arif Mahmud, or 3.90. On their own, they carry no meaning — the string 21100003 could be a phone number, a serial number, or a student identifier. Data become information when they are organized, processed, and given context so that a human or program can act on them. Knowing that student 21100003, Arif Mahmud, has a cumulative GPA of 3.90 is information: it answers a question someone cares about. Knowledge goes one step further — it is information internalized, generalized, and combined with experience: "students who earn an A in Programming Language II almost always pass Database Systems" is a claim derived from many observations, useful for advising, though it must be used with statistical care.

The distinction is practical, not philosophical. A university stores data (names, grades, credits) so that it can produce information (a dean's enrollment report, a student's transcript) and, over time, extract knowledge (which courses predict success). Database systems are engineered bottom-up for this pipeline: their job is to store data with integrity (correctness and consistency), transform it into information through queries, and do both safely for many users at once. Keeping the three levels separate also clarifies what software can be responsible for: a DBMS guarantees that stored data is correct and retrievable; turning it into information is what queries do; deciding what the information means remains a human responsibility.

1.2 Database concepts and terminology

A database is an organized, shared collection of logically related data — the data itself, together with a description of that data. A Database Management System (DBMS) is the software that stores, manages, and provides access to the database. The distinction is like the one between a library (the collection) and the librarian plus the cataloguing system (the management). In practice, when people say "the database is down," they usually mean the DBMS server process is down; this book keeps the terms separate but follows common usage when the meaning is clear.

In the relational world, which is our focus, the core terms are:

The book's running example is a university database with six tables — department, student, instructor, course, course_section, and enrollment — whose full definition and dataset appear in Appendix H. A small excerpt of the student table gives the flavor (the "now" of this book is Fall 2026):

Two rows deserve immediate attention. Arif Mahmud holds the highest GPA in the university, and Zara Hossain, admitted in 2026, has NULL in the gpa column — a marker for "value not yet available," not a number. NULL is so important to correct database work that Chapter 2 treats it as a topic in its own right.

1.3 File-processing systems versus database systems

Before DBMSs, applications stored data in ordinary files. Each program defined its own file formats and its own procedures for reading and writing them. In a 1970s university, the registrar's office might keep students.dat, the CSE department might keep cse_students.txt, and the accounts office might keep yet another copy of student records. This classic file-processing approach fails in ways that are worth memorizing, because they define what a DBMS must fix:

• Redundancy and inconsistency. The same student's address is stored in three offices; the student moves; two offices update their files and one does not. The copies now disagree. Redundancy is not merely wasted disk space — it is an invitation to contradiction.
• Difficulty accessing data. If a dean asks, "which departments had the highest average grade in Spring 2025?", no existing report may answer it. Writing a brand-new program against raw files, again, costs days.
• Data isolation. Data scattered across files in different formats is hard to query uniformly.
• Integrity problems. Rules such as "a student cannot enroll in a section whose course they already passed" must be encoded inside every program that touches the data; one missed program silently corrupts the records.
• Atomicity problems. If a program crashes halfway through transferring a payment, the system is left half-done. There is no mechanism to undo a partially completed operation.
• Concurrent-access anomalies. Two users editing the same record at once can interleave their writes and produce nonsense.
• Security problems. File-level permissions are all-or-nothing; a program that should read one view of the data gets everything.

A database system attacks all seven problems with a single architectural decision: the data lives in one place, described once, and every program goes through the DBMS to reach it. Redundancy can be controlled and declared; new queries need no new program; constraints are stated once in the schema and enforced centrally; transactions wrap multi-step updates atomically; concurrency is managed by the DBMS scheduler; and access rights are granted per table, per view, even per column. The cost is a layer of software with its own concepts to learn — which is this book.

1.4 Database Management Systems (DBMS)

A DBMS is a layer of system software between users' programs and the stored data. Its job is to make shared, persistent, correct data look simple to its clients. Concretely, a modern DBMS provides:

• Data definition. A language (SQL's DDL, Data Definition Language) for declaring tables, types, and constraints: CREATE TABLE, ALTER TABLE, DROP TABLE.
• Data manipulation. A language (SQL's DML, Data Manipulation Language) for querying and changing data: SELECT, INSERT, UPDATE, DELETE.
• Transaction management. Grouping operations into atomic, consistent, isolated, durable units — the ACID properties introduced properly in Chapter 18.
• Concurrency control. Letting hundreds of users work simultaneously as if each were alone (Chapter 18).
• Recovery. Restoring a consistent state after a crash, using logs (Chapter 21).
• Authorization. Controlling who may read or write what (Chapter 20).
• Storage management. Indexes, file layouts, and buffer management tuned by the DBMS rather than by each application (Chapter 19).
• Query processing. Parsing a declarative statement, finding an efficient execution plan, and running it (Chapter 19).

The crucial design idea is data abstraction: users state what they want, not how to find it. When you ask for the Fall 2026 sections, the DBMS decides whether to scan the table, use an index, or rewrite the query — and its choice can change as data grows without any change to your query. A second idea, developed fully in Chapter 4, is that the schema is itself stored data (metadata, held in the catalog or data dictionary), so a DBMS can answer questions about its own structure.

1.5 Relational Database Management Systems (RDBMS)

A relational DBMS is a DBMS that presents data as a collection of relations — in everyday language, tables — and manipulates them with a small, mathematically defined set of operations. The idea was published by Edgar F. Codd in 1970 in his paper "A Relational Model of Data for Large Shared Data Banks," one of the most influential papers in computing history. Codd's insight was to give data manipulation a formal foundation: tables are mathematical relations (sets of tuples), queries are expressions over those relations, and a language can be declarative — you describe the result, and the system computes any equivalent plan.

Three properties made the relational model dominant:

1. Simplicity. Tables are a structure everyone understands — accountants used ledgers for centuries. Compare this with the tree and network models of the 1970s, where a program had to follow stored pointers and a new question literally meant a new navigation path through the data.
2. Set-at-a-time operations. Queries process whole tables at once, in one statement, instead of looping record by record in application code.
3. Data independence. Because programs see tables and columns, not storage layouts and pointers, the physical organization can change without breaking applications (Chapter 4 develops this).

An RDBMS is therefore characterized by two things together: the relational data model (Chapter 2) and a relational query language, in practice SQL (Structured Query Language; Chapters 8–13). Be careful with terminology: SQL is based on the relational model but is not a pure implementation of it — SQL tables can contain duplicate rows and allow column ordering, both departures from Codd's strict mathematics. We flag such distinctions throughout the book, because interview questions and real-world bugs turn on them.

1.6 Database users, administrators, and developers

A database system serves many kinds of people, and textbooks group them by how they touch the data. The roles overlap in small organizations, but the categories are analytically useful:

• End users obtain or update information for their jobs. Naive users invoke canned applications — a student checking a schedule on the portal — and never write SQL. Casual/parametric users use menus or fill forms; sophisticated users write their own queries directly, as analysts do with SQL or with analytical notebooks.
• Database application developers write the programs end users see: web and mobile front ends, APIs, reports. Their skill set is a programming language plus a database API (JDBC, ODBC, language-specific drivers), plus enough SQL to write correct, efficient queries.
• Database administrators (DBAs) are responsible for the system as a whole: installing and upgrading the DBMS, designing and evolving schemas with developers, granting and revoking privileges, monitoring performance, tuning indexes, planning backups, and leading recovery after failures. In a university, the DBA decides whether the enrollment table gets a new index and who may read salaries from instructor.
• Data engineers and analysts occupy the data-facing end of the spectrum: engineers build pipelines that move data between systems, and analysts extract information and knowledge, often from a warehouse or replica rather than the operational database.
• DBMS implementers build the DBMS itself — the kernel, optimizer, and storage engine. Most readers will use these systems, but Chapters 4, 14, and 19 peek inside so that informed users can reason about performance.

The DBA's watchword is the least privilege principle: every role gets exactly the access it needs and nothing more. A student-facing application should be able to read course sections and insert enrollments, but never read instructor salaries.

1.7 Database applications and industry use cases

Databases are among the oldest and most successful categories of software, and virtually every industry runs on them:

• Banking and finance. Every withdrawal is a transaction against a customer's row; atomicity and concurrency control are non-negotiable. Databases also back risk reports and fraud detection, which join transaction streams with account history.
• Universities. Registration, grading, degree audits, and transcripts — the very example of this book. Enrollment in a limited-seat section is a classic concurrency problem: many students press "enroll" simultaneously; the DBMS must ensure no seat is double-sold.
• E-commerce and retail. Product catalogs, shopping carts, orders, and inventory. A catalog is read-heavy and tolerant of eventual consistency; an order is a strict ACID transaction — the same system often splits workloads by these requirements.
• Healthcare. Patient records, medication histories, lab results. Integrity constraints are literally a matter of life and death, and privacy law imposes strict access control.
• Telecommunications. Billing records arrive continuously at enormous rates; call-detail records are stored in some of the largest relational databases in existence.
• Airlines and travel. Reservations and seat maps are the textbook concurrency case: two travel agents selling the same seat must serialize.
• Web applications generally. Nearly every dynamic site is a three-tier system (Chapter 4) whose middle tier talks SQL to a relational database — usually PostgreSQL or MySQL, both of which you will install and drive in this book.

Beyond individual companies, online analytical processing (OLAP) and data warehousing extract information and knowledge from operational data, and NoSQL systems (document stores, key-value stores, graph databases) trade some relational guarantees for flexibility or horizontal scale. These are complements to, not replacements for, relational systems; we compare them honestly in Chapter 27. The through-line of all these cases is the same: persistent, shared, correct data with fast, flexible retrieval.

1.8 Overview of PostgreSQL, MySQL, SQLite, Oracle, and SQL Server

This book teaches with PostgreSQL as its primary platform and uses MySQL as a comparative platform, because together they are free, open-source, widely deployed, and different enough to teach the idea of dialects. The others below are summarized so you can hold an intelligent conversation about them.

PostgreSQL (1989→1996 as Postgres, descended from Codd-influenced research at Berkeley; "Post-gres-cue-ell" or "Post-gress") is an open-source ORDBMS — object-relational — with a reputation for standards conformance and correctness. It offers rich data types (arrays, JSON as json/jsonb, ranges, custom types), user-defined functions in many languages, transactional DDL (schema changes can roll back!), and a sophisticated optimizer. PostGIS extends it into the dominant open-source geospatial engine. Its storage architecture is unusual and worth knowing early: one server process owns the data directory, and each client connection gets its own backend process — Chapter 4 shows the picture.

MySQL (1995) is the other open-source giant, famous for speed and ubiquity in web hosting. Since 8.0 it has window functions, common table expressions, and enforced CHECK constraints, and its default storage engine InnoDB provides full ACID transactions with row-level locking. MySQL is famous for pluggable storage engines — the same SQL surface over different physical stores (Chapter 16). Its historic developer, MySQL AB, was acquired by Sun (2008) and then Oracle Corporation (2010); MariaDB is a popular community fork of the same lineage.

SQLite (2000) is not a client-server DBMS at all: it is a compact C library that stores an entire database in one portable file, embedded inside the application. It is the most widely deployed database engine in the world — inside every mobile phone, browser, and countless devices — because it is public domain, has no server to administer, and is ACID-compliant for single-writer workloads. It is ideal for local application data, testing, and teaching, but not for many concurrent writers over a network.

Oracle Database (1979) is the long-standing flagship of commercial databases: extreme scalability features (Real Application Clusters, partitioning), deep DBA tooling, and a substantial licensing cost. It set many patterns other systems later adopted.

SQL Server (1989, Microsoft; "Sequel Server") is the commercial DBMS most tied to the Windows and .NET ecosystem, with strong tooling (SSMS, Azure integration), its own T-SQL dialect, and consistently high benchmark results across its editions, which now also run on Linux.

For learning SQL proper, the choice of platform matters less than people fear: the standard core — SELECT, joins, grouping, constraints, transactions — is portable across all five, and where dialects differ, this book shows the difference explicitly so that you develop dialect awareness rather than dialect dependence.

Chapter Summary

• Data are raw recorded facts; information is data organized into an answer; knowledge is information generalized into reusable understanding.
• A database is an organized, shared collection of related data; a DBMS is the software that manages it and mediates all access.
• Core relational terms map cleanly onto everyday ones: tables, rows, columns, schema (structure), and instance (current data).
• File-processing systems suffer redundancy, inconsistency, limited access, isolation, integrity, atomicity, concurrency, and security problems.
• Centralizing data description and access in a DBMS fixes these by controlling redundancy, centralizing constraints, and providing transactions, concurrency control, and authorization.
• A DBMS provides data definition, manipulation, transactions, concurrency, recovery, authorization, storage, and query processing — users declare what, the system decides how.
• The relational model (Codd, 1970) presents data as relations manipulated by set-at-a-time operations, giving queries a mathematical foundation.
• SQL implements the relational model with pragmatic departures (duplicate rows, column ordering) that careful professionals must know.
• Database people include end users, application developers, DBAs, data engineers/analysts, and DBMS implementers; least privilege governs access.
• Databases power banking, universities, commerce, healthcare, telecom, travel, and nearly every dynamic web application.
• PostgreSQL and MySQL are the book's paired open-source platforms; SQLite, Oracle, and SQL Server round out the professional landscape.

Key Terms

Laboratory Exercises

1. Install PostgreSQL 16 or newer on your machine and connect with psql to the default database. Run SELECT version(); and record the exact server version string you see. Expected result: a one-row output naming PostgreSQL 16 or later.
2. Create the six tables of the university schema (Appendix H) in a new database named university, using the PostgreSQL DDL from the schema. Then run \dt in psql and confirm that all six relations — department, student, instructor, course, course_section, enrollment — are listed.
3. Load the canonical dataset (Appendix H) and verify the quick-fact counts with four separate queries: SELECT COUNT(*) FROM student; and the analogous counts for instructor, course, course_section, and enrollment. Expected results: 12 students, 6 instructors, 10 courses, 13 sections, 28 enrollments.
4. Using the catalog (not the chapter text), list all columns of the student table and their data types. In psql, the command \d student shows this metadata. Expected result: six columns — student_id, full_name, major_dept_id, admission_year, total_credits, gpa — with their types.
5. Install MySQL 8.x alongside PostgreSQL, create the same schema there (replacing NUMERIC with DECIMAL where noted in Appendix H), and run SHOW TABLES;. Write three sentences on what differed between the two installations and what did not.
6. Ask two different DBMSs the same question and compare: SELECT COUNT(*) FROM enrollment WHERE grade IS NULL; on both your PostgreSQL and MySQL university databases. Expected result: 8 in-progress (ungraded) enrollments on both platforms — the Fall 2026 rows.

Review Questions and Exercises

1. In your own words, distinguish data, information, and knowledge, giving one university example of each. Data: the row (21300003, 'Arif Mahmud', ..., 3.90); information: "Arif Mahmud has the highest GPA in the university (3.90)"; knowledge: "students who complete CSE251 early in their program tend to maintain high GPAs" — a generalization built from many such rows.
2. What exactly is the difference between a database and a DBMS? The database is the organized collection of data plus its description; the DBMS is the software that manages that collection and mediates all access to it.
3. List four distinct problems of file-processing systems and explain how a database system addresses each. Redundancy/inconsistency — single declared source of data; difficult ad hoc access — one query language over all data; integrity enforcement — constraints declared once and checked centrally; concurrent access — DBMS concurrency control serializes conflicting operations. (Atomicity and security answers also acceptable.)
4. Why is a schema stored inside the database rather than in application programs, and what is this stored schema called? So that the structure is defined once, enforced for every user, and inspectable by the DBMS itself; it is called metadata and lives in the catalog (data dictionary).
5. What does it mean for a language to be declarative, and why does that matter for query optimization? A declarative language states the desired result rather than the algorithm; because the procedure is left open, the DBMS is free to choose among equivalent execution plans, and can re-optimize as data changes without changing the query.
6. Name Codd's 1970 contribution and two properties of the relational model that helped it displace earlier models. The relational model paper, "A Relational Model of Data for Large Shared Data Banks"; its simple table structure and set-at-a-time, declarative operations (data independence is also acceptable).
7. Why is SQL not a pure relational language? SQL permits duplicate rows (relations are sets) and exposes column order (relations have unordered attributes), among other pragmatic departures.
8. A student-portal application must never be allowed to read instructor salaries. Which role from Section 1.6 enforces this, and with which DBMS capability? The DBA, using the authorization (privilege) subsystem — granting the portal's account SELECT on course/section/enrollment data but nothing on instructor.salary, per least privilege.
9. SQLite is described as "not a client-server DBMS." Explain what that means and give one situation where it is the better choice. SQLite is an embedded library inside the application process with the whole database in one file, no server or network protocol; it is ideal for local application data (mobile apps, browsers, prototypes) with a single writer.
10. Write the SQL statement that creates the department table from the university schema, using standard-SQL keywords in uppercase.

    CREATE TABLE department (
        dept_id     INTEGER       PRIMARY KEY,
        dept_name   VARCHAR(50)   NOT NULL UNIQUE,
        building    VARCHAR(30),
        budget      NUMERIC(12,2) CHECK (budget >= 0)
    );

11. Write a single query that returns the number of students whose GPA has not been computed yet.

    SELECT COUNT(*) AS students_without_gpa
    FROM student
    WHERE gpa IS NULL;

    Expected output: 1 — Zara Hossain, admitted in 2026.
12. Classify each of the following as a DBMS capability or a file-processing leftover: (a) a CHECK constraint, (b) each program decoding its own record layout, (c) rollback after a crash, (d) chmod 400 on students.dat. (a) DBMS capability — constraint enforcement; (b) file-processing leftover — data isolation; (c) DBMS capability — recovery/atomicity; (d) file-processing leftover — all-or-nothing security with no per-column, per-user rules.

Mini-Project

Set up your semester laboratory. Install PostgreSQL 16+ and MySQL 8.x, create a database university in each, load the full canonical schema and dataset from Appendix H, and write a one-page README that records: the exact versions installed, the commands you used to create and load the schema, the five count queries and their answers (12 students, 6 instructors, 10 courses, 13 sections, 28 enrollments), and one difference you noticed between the two systems. Keep this environment — every chapter's exercises build on it, and by Chapter 24 you will have turned it into a small but complete registration application.