Understand the data.
Make the query work.

Objectives

This chapter introduces databases, the functions of a DBMS and the people who use database systems. By the end, you should be able to:

• distinguish a database, a DBMS and a database system;
• explain the main functions of a DBMS;
• describe when a DBMS is useful;
• distinguish the responsibilities of administrators, designers, developers and end users.

Chapters 2 and 3 develop data models, architecture and database design.

Overview of a Database System

A Database Management System (DBMS) is software used to create, organize, store, and manage a database (a structured collection of related data). A DBMS provides a systematic way to store data, retrieve it quickly, and modify it safely when required.

In practical terms, a DBMS acts as an interface between the stored data and the users/applications, providing mechanisms for consistency, security and availability when multiple users access it. The guarantees depend on the system, its configuration and correct application use.

Key Points

• A DBMS manages a collection of interrelated data stored in a computer system.
• It provides tools to organize and access data efficiently.
• It supports storage, retrieval, insertion, deletion, and update operations.
• It is designed to handle large volumes of data, multiple users, and complex queries reliably.

Definition of DBMS and Database System

• Database: An organized collection of related data (e.g., student records, sales records, hospital data).
• DBMS: Software that manages the database (e.g., MySQL, PostgreSQL, Oracle).
• Database System: Database + DBMS + applications + users + procedures (the complete environment).

Core Functions of a DBMS

A DBMS typically provides:

• Data Storage: Save data in structured form (tables/records).
• Data Retrieval: Fetch data using queries (e.g., SQL).
• Data Manipulation: Insert, update, and delete records safely.
• Data Management: Ensure integrity, security, concurrency, and recovery.

When a DBMS is useful

A DBMS is particularly useful when data is:

• Large in volume (thousands to millions of records)
• Shared among many users (multi-user access)
• Frequently updated (consistency must be maintained)
• Sensitive (security and authorization are required)
• Critical (backup and recovery are needed)

A DBMS provides coordinated tools for managing shared data reliably and efficiently.

Database and Database Users

A database is an organized collection of related data stored in a computer system. The purpose of maintaining a database is to store information in a structured form so that it can be easily accessed, updated, and managed.

A database system is not used by a single person only. In real organizations, different users interact with the database in different ways, depending on their job role and permissions. For example:

• Administrators perform privileged management tasks within the organisation’s access policy.
• Programmers/developers write applications and SQL code to interact with the database.
• End users access data using ready-made applications (such as ERP portals, mobile apps, dashboards, etc.).

So, not everyone gets the same level of access—this is important for security and controlled data management.

Types of Users and Their Responsibilities

Database Administrator (DBA)

A DBA is responsible for the overall management of the database system. Typical tasks include:

• Installing and configuring the DBMS software
• Creating user accounts and managing permissions
• Ensuring database security and backup/recovery
• Monitoring performance and tuning the system
• Ensuring high availability and reliability of the database

In short: the DBA is the caretaker and security guard of the database.

Database Designer

A database designer builds the database structure based on system requirements. Responsibilities include:

• Identifying entities (e.g., Customer, Product, Order) and relationships
• Designing tables, attributes, keys, and constraints
• Creating ER/EER models and converting them into schemas
• Ensuring the database design is efficient and consistent

In short: the designer decides what the database should look like before it is implemented.

Database Developer

A database developer works mainly on implementation and database-side programming. Responsibilities include:

• Writing SQL queries, stored procedures, functions, and triggers
• Implementing constraints and optimization techniques
• Creating and maintaining database objects (tables, views, indexes)
• Supporting performance improvements and query tuning

In short: the developer makes the database work smoothly and respond fast.

Application Programmer

An application programmer develops software applications that interact with the database. Responsibilities include:

• Writing application code (Java/PHP/Python/.NET etc.) that reads/writes database data
• Creating forms, reports, dashboards, and APIs
• Handling transactions and validating inputs in the application layer
• Ensuring secure and correct database access from the software

In short: the application programmer builds the user-facing system that uses the database behind the scenes.

End Users

End users use the database system to perform their work. Many access it through ready-made applications; some, such as analysts, may also write queries. Examples include:

• Students using an admission portal
• Bank customers using mobile banking
• Data analysts viewing reports
• Office staff entering records in an ERP system

End users typically:

• Retrieve information
• Enter or update data through forms
• Generate reports and summaries
• Perform routine operations based on permissions

In short: end users are the consumers of the database services.

Summary

Each role exists because a database system is a shared resource:

• DBA manages and secures the database system.
• Designer designs the database structure.
• Database developer builds database logic and performance features.
• Application programmer builds software that accesses the database.
• End user uses the application to view and update data.

This separation of responsibilities keeps the database system secure, organized, scalable, and maintainable.

Check your understanding

1. A spreadsheet already stores student marks. What additional responsibilities would a DBMS take on when 30 teachers update marks concurrently?
2. Who should approve access privileges: an end user, an application programmer or a DBA? Explain the separation of responsibilities.
3. Give one example each of a database, DBMS and database application.

Concepts and Architecture

A database system is not just a collection of tables. It is a complete environment that includes data, users, and the DBMS software that manages the data. To understand how a DBMS works, we must understand a few key concepts that form the foundation of database architecture:

• Data Model: A method to describe how data is structured, related, and stored in a database.
• Views of Data: Different ways of presenting the same data to different users according to their needs.
• Schema and Instance: The schema defines the database structure (blueprint), while an instance represents the actual data stored at a particular time.
• DBMS Architecture and Data Independence: A DBMS contains multiple internal components (modules) that manage storage, queries, and transactions. Data independence allows some storage or schema changes without requiring changes to applications. The extent of this independence depends on the change and the system’s design.

In short, these concepts help us design databases that are organized, scalable, secure, and maintainable.

Data Models

A data model is a conceptual tool used to describe:

• the structure of data (how it is organized),
• the relationships among data items,
• and the constraints that ensure correctness.

A good data model makes data storage and retrieval logical, efficient, and consistent.

Major Types of Data Models

1) Hierarchical Model

In the hierarchical model, data is organized like a tree:

• Each child record has only one parent
• One-to-many relationships are naturally supported

Best suited for: strict hierarchical structures
Example: an organizational chart

• One manager supervises many employees, but each employee reports to only one manager.

2) Network Model

In the network model, data is organized like a graph:

• A record can have multiple parents and multiple children
• Many-to-many relationships are supported directly

Best suited for: complex linked data
Example: a legacy parts-and-suppliers database, in which records can participate in multiple linked record sets. The historical network model is not the same as a modern graph database.

3) Relational Model

In the relational model, data is stored in tables (relations):

• Each table contains rows (tuples) and columns (attributes)
• Relationships between tables are created using keys (primary key and foreign key)

Best suited for: most real-world applications (business systems, banking, education)
Example: a customer–order database

• Customer details are stored in a Customer table
• Orders are stored in an Order table
• Both are linked using a common key like customer_id

Why it dominates today:
The relational model is widely used because it is:

• easy to understand (tables are intuitive),
• flexible for design,
• scalable for large datasets,
• supported by SQL, a standard language.

Choosing the Right Model

Each data model has strengths and limitations:

• Hierarchical is simple but rigid (not very flexible).
• Network is powerful but complex to manage.
• The relational model combines a familiar table structure with flexible querying and integrity constraints. The best model for an application depends on its requirements.

DBMS components and two views of architecture

A DBMS is not a single program. It is a collection of components that work together to store data, process queries, and maintain correctness even when many users access the database at the same time. Major internal components typically include:

• Storage Manager: Manages how data is stored on disk, retrieved into memory, and written back safely.
• Query Processor: Interprets SQL queries, optimizes them, and executes them efficiently.
• Transaction Manager: Ensures transactions follow ACID properties and handles concurrency and recovery.

To manage complexity and make database systems scalable, DBMS design follows the idea of separating responsibilities into layers and separating data descriptions into levels. This is commonly explained using the three-schema (three-level) architecture, which provides data abstraction.

Three-tier application architecture

A three-tier architecture is widely used in real database applications because it improves scalability, performance, and maintainability. It divides the system into three logical layers:

1) Presentation Layer (User Interface Layer)

This is the top layer that interacts with users.

• Provides a GUI/Web interface (forms, dashboards, login pages).
• Accepts user inputs, does basic validation, and shows results.
• Sends requests to the application layer.

Example: A student portal screen where a user enters a roll number and clicks “View Result”.

2) Application Layer (Middle / Business Logic Layer)

This layer processes user requests and applies rules.

• Implements business logic (eligibility rules, fee calculations, workflow).
• Handles security, validation, and request processing.
• Communicates with the DBMS to fetch/update data.

Example: Checking if a student has paid fees before allowing registration.

3) Data Layer (Database Server Layer)

This is the bottom layer where data is actually stored and managed.

• Performs database operations like SELECT, INSERT, UPDATE, DELETE.
• Enforces constraints such as PRIMARY KEY, FOREIGN KEY, etc.
• Maintains consistency and integrity of stored data.

Example: Storing student records, course data, exam results, etc.

Three-schema data architecture

The DBMS also organizes data description using three levels (schemas). This is mainly to achieve data abstraction and enable data independence.

1) External Schema (External View / User View)

• Represents how different users see data.
• Each user/group may have a different external view.
• Focuses only on required data, hiding unnecessary details.

Example:

• A teacher sees: StudentName, Marks
• Accounts section sees: StudentName, FeeStatus

2) Conceptual Schema (Logical View)

• Represents the complete logical structure of the entire database.
• Includes entities, attributes, relationships, and constraints.
• It is a global unified view for the organization.

Example: Student, Course, Enrollment tables and their relationships.

3) Internal Schema (Physical View)

• Describes how data is physically stored on disk.
• Includes file structures, indexes, storage locations, access paths.
• Mostly handled by DBMS internally.

Example: B+ tree indexes, heap files, data blocks, partitioning.

Keep these two architectures separate

Three-tier describes where application responsibilities belong: presentation, business logic and data access. Three-schema describes levels of data abstraction: external, conceptual and internal. There is no required one-to-one mapping between their levels. An application layer may use several external views; the database server supports conceptual and internal schemas together.

Physical data independence: changing an index or storage arrangement without changing the logical table design or application queries.

Logical data independence: changing the conceptual schema while preserving relevant user views and application interfaces. This usually requires careful view or mapping design; it is not an automatic promise that every schema change is harmless.

A practical design decision

A university adds an index to speed up searching by roll number. This is a physical change. If it splits a Student table into Student and StudentContact, that is a logical change. A compatible view can sometimes preserve an older reporting interface.

Data Modelling Using the Entity–Relationship (ER) Approach

Data modelling is the process of describing data requirements clearly before creating database tables. The Entity–Relationship (ER) approach is one of the most widely used techniques for conceptual database design. It helps us identify the important objects in a system, the properties of those objects, and how they are connected.

ER Model: Definition and Purpose

The Entity–Relationship (ER) model is a high-level model used to describe the structure of a database using three main concepts:

• Entities: Real-world objects or concepts (e.g., Student, Customer, Product).
• Attributes: Properties that describe an entity (e.g., name, email, price).
• Relationships: Associations between entities (e.g., Student enrolls in Course, Customer buys Product).

Why we use the ER Model

• Converts real-world requirements into a clear database design.
• Makes the database structure easy to understand and communicate.
• Serves as a blueprint before converting the design into relational tables.

Entities and entity types

Entities

An entity is a specific object we want to store information about.
Examples in a university system include:

• Student (Harish, Riya, Aman…)
• Course (DBMS, OS, AI…)
• Professor (Dr. Mehta, Dr. Sharma…)
• Department (CSE, ECE…)

Entity Types

An entity type represents a category/class of similar entities.
For example:

• The entity type Student represents all students.
• The entity type Course represents all courses offered by the university.

So:

• Student is an entity type
• Harish is an entity (an instance of Student)

Attributes (Name, Email, Credits, etc.)

Attributes describe the details of an entity.

Examples:

• Student entity may have attributes:
  StudentID, Name, Email, Address, PhoneNumber

• Course entity may have attributes:
  CourseCode, Title, Description, Credits

• Professor entity may have attributes:
  ProfessorID, Name, Email

• Department entity may have attributes:
  DeptCode, DeptName

In ER modelling, attributes can be simple (Name), composite (Address → street, city), multivalued (PhoneNumbers), or derived (Age from DOB).

Relationships and Relationship Types (Enrollment, Teaching, Advising)

A relationship shows how entities are connected.

Examples in a university:

• Enrollment: Student enrolls in Course
• Teaching: Professor teaches Course
• Advising: Professor advises Student

Relationship Type

A relationship type is the category/class of a relationship.

• Enrollment relationship type represents all enrollments
• Any specific enrollment (e.g., Student 101 enrolled in DBMS) is a relationship instance

Relationships often have constraints like:

• Cardinality: one-to-one, one-to-many, many-to-many
• Participation: total participation means every entity of that type must take part in the relationship; partial participation allows some not to.

ER Diagram Notation (Basic Symbols)

In Chen notation, ER diagrams use the following symbols:

• Rectangle: Entity / Entity Type
  (Student, Course, Professor)
• Oval: Attribute
  (Name, Email, Credits)
• Diamond: Relationship Type
  (Enrolls, Teaches, Advises)
• Lines: Connect entities to relationships and attributes

Different notations exist (Chen, Crow’s Foot), but the basic idea remains the same: entities + attributes + relationships.

Example ER Model: University Database

Entity Types

• Student, Course, Professor, Department

Attributes

• Student: (StudentID, Name, Email, Address, PhoneNumber)
• Course: (CourseCode, Title, Description, Credits)
• Professor: (ProfessorID, Name, Email)
• Department: (DeptCode, DeptName)

Relationship Types

• Enrollment (Student–Course)
• Teaching (Professor–Course)
• Advising (Professor–Student)
• Department-Association (Department–Course / Department–Professor)

Text-based ER Representation (for understanding)

• Student —(Enrolls)— Course
  • Many students can enroll in many courses (M:N)
• Professor —(Teaches)— Course
  • A professor can teach multiple courses (1:N) (or M:N if team-teaching exists)
• Professor —(Advises)— Student
  • A professor advises many students (1:N)

Weak entities and identifying relationships

A weak entity is an entity that cannot be uniquely identified by its own attributes alone.
It depends on a strong (owner) entity for its identification and existence.

Key points

• Has no complete primary key of its own.
• Identified using:
  • Owner entity’s primary key +
  • Partial key (discriminator) of the weak entity
• If the owner entity is deleted, the weak entity usually cannot exist.

Example

• Employee (EmpID, Name, …) → strong entity
• Dependent (DepName, Age, Relation) → weak entity
  A dependent is identified as: (EmpID + DepName)

Identifying relationships

A weak relationship (also called an identifying relationship) is the relationship that links a weak entity to its owner entity.

Key points

• It is used to identify the weak entity.
• Requires total participation of weak entity (every weak entity must be linked to an owner).
• Typically one-to-many: one owner can have many weak entities.

Example

• Relationship: Employee “has” Dependent
• Dependent cannot be identified without knowing which Employee it belongs to.

Weak-entity notation

• Weak Entity: double rectangle
• Weak Relationship: double diamond
• Partial Key: dashed underline
• Total participation: double line from weak entity to relationship

Foreign keys help represent relationships in relational tables. However, a foreign key alone does not make an entity weak.

A foreign key alone does not make an entity weak

A weak entity means:

1. It cannot be uniquely identified by its own attributes, and
2. Its primary key includes the owner’s key (so its identity depends on the owner).

So yes, in tables it usually looks like a foreign key is involved, but the key point is the FK becomes part of the PRIMARY KEY (or part of a composite key), not just a normal FK column.

Compare identification models

Independent identification with a foreign key

Example: Orders(order_id PK, customer_id FK)

• order_id alone identifies the row.
• customer_id is just a reference. This is not a weak entity under this identification model.

Owner-dependent identification

Example: Employee → Dependent

Employee(emp_id PK, …)
Dependent(emp_id FK, dep_name, age, …)
Primary Key of Dependent = (emp_id, dep_name)

• dep_name alone is not unique globally.
• Dependent is identified by (emp_id, dep_name), assuming dependent names are unique within each employee. This is a weak entity under the stated identification model.

Key distinction

Weak entity ≠ just foreign key.
A weak entity is identified through its owner and a partial key. In the usual relational mapping, the owner key is included in the weak entity’s composite primary key.

Enhanced ER (EER) Concepts

A) Specialization and Generalization

These concepts model IS-A relationships.

Specialization

Defining more specific entity types, called subtypes, from a general entity type, called a supertype.

Example:
Vehicle → Car, Truck, Motorcycle
Each subtype inherits the supertype’s attributes and may have additional attributes or relationships.

Generalization

Identifying common features of several entity types and representing them in a shared supertype.

Example:
Car, Truck, Motorcycle → Vehicle
Common attributes stay in the generalized entity.

Associative entities and aggregation are different ideas

An OrderItem table resolves an Order–Product many-to-many relationship and records details such as quantity. It is an associative entity. Do not use this alone as the definition of EER aggregation. In conceptual EER aggregation, a relationship together with its participating entities is treated as a higher-level object so that it can participate in another relationship.

Mapping ER Model to Relational Model (Introduction)

To implement an ER design in a relational database:

• Entities → Tables
  Each entity type maps to a table; entity instances map to rows, and attributes map to columns.

• Relationships → Foreign Keys / Bridge Tables

  • 1:N relationship → foreign key placed on “N side”
  • M:N relationship → new table (bridge/associative table)

Example mapping for online store:

• Product(ProductID, ProductName, ProductPrice)
• Customer(CustomerID, CustomerName, CustomerEmail)
• Order(OrderID, OrderDate, CustomerID)
• OrderItem(OrderID, ProductID, Quantity)

Here:

• Order.CustomerID is a foreign key → Customer
• OrderItem.OrderID is FK → Order
• OrderItem.ProductID is FK → Product

Design exercise: university enrolment

A student can take several courses, and each course has several students. Create an Enrolment table with student_id and course_id as foreign keys. For a simplified single-offering model, their pair is the primary key. If courses repeat across semesters, add an offering identifier; otherwise a valid repeat enrolment would be rejected.

Do not assume: a foreign key automatically makes an entity weak. A table can have a foreign key and still possess an independent identifier. Weakness concerns identification through the owner plus a partial key.

A relation consists of tuples (rows) described by named attributes (columns). Each attribute has a domain: the set of values it may contain. SQL tables provide a familiar representation, although SQL and the mathematical relational model differ in important ways. Mathematical relations do not contain duplicate tuples; SQL query results can contain duplicates unless removed with DISTINCT or a relevant set operation. SQL also supports NULL, so it should not be treated as identical to the mathematical model.

• Superkey: attributes sufficient to identify a tuple uniquely.
• Candidate key: a minimal superkey; removing any attribute destroys that guarantee.
• Primary key: the candidate key selected as the main identifier.
• Foreign key: one or more columns whose non-NULL values must match a referenced key, usually a primary key, in another table or the same table. Composite foreign keys have additional NULL-handling rules.

A student's email may be a candidate key only if the institution guarantees that it is unique and present. A name alone is not a reliable identifier.

The shared laboratory dataset

The examples in Chapters 4–7 use two tables. Each order belongs to one customer. Some customers have no orders.

Order totals are whole currency units in this teaching example. Chapter 6 provides the complete setup, including these sample rows.

Constraints in the laboratory

The Customers table uses a primary key; Orders references it. NOT NULL prevents missing required values. CHECK prevents negative order totals. In SQL, a CHECK condition that evaluates to unknown due to NULL may not reject the row; combine CHECK with NOT NULL when missing values are also invalid.

Technical cross-check: PostgreSQL constraints documentation.

Queries as operations on relations

Relational algebra combines operators that accept relations and return relations. Selection, written σ, filters rows. Projection, written π, chooses columns and removes duplicate result rows. Renaming gives relations or attributes new names so that references are unambiguous. Union, difference and intersection require compatible schemas. A Cartesian product pairs every row in one relation with every row in another. A theta join keeps only the pairs that satisfy a specified condition. Classical algebra uses sets, unlike SQL's common duplicate-preserving results. Reference: RPI relational algebra notes.

Use the Customers and Orders dataset introduced in Chapter 4 for the following examples.

Projection removes duplicate tuples in classical algebra: customer 101 appears in two orders but once in the projected set. Its SQL counterpart for this purpose is SELECT DISTINCT customer_id FROM Orders.

From a product to a useful join

Start with 25 customer–order pairs. Retain only pairs whose customer identifiers agree. Exactly five remain, corresponding to the five orders. Rename ambiguous attributes first—for example C.customer_id and O.customer_id in explanatory notation. Explicit SQL JOIN conditions are clearer than relying on matching column names accidentally. Reference: RPI algebra and normalization lecture.

Two different operations, two different purposes

To find names of Boston customers, first select Boston tuples, then project customer_name. Reversing these steps loses customer_city before the filter can use it. This is a useful way to reason about whether an expression is well formed.

Learn with one consistent schema

SQL defines structures, retrieves data and changes stored records. SQL commands are often grouped by purpose:

• Data Definition Language (DDL) defines structures.
• Data Manipulation Language (DML) changes stored data.
• Data Query Language (DQL) is a teaching label for queries.
• Data Control Language (DCL) manages privileges.
• Transaction Control Language (TCL) manages transactions.

These categories vary across textbooks and products; queries are also commonly included within DML.

The examples use customer_id, customer_name and customer_city consistently. Copy and run the complete setup below once in a new, empty SQLite database. It creates demonstration tables and inserts the sample rows introduced in Chapter 4. Do not run it against production data.

The first statement enables SQLite foreign-key enforcement for the current connection. The Accounts table supports the transaction example in Chapter 7. Keep this connection open while working through the examples; if you start again, use another empty database.

-- DBMS study edition. Run ONLY in a new, empty SQLite database.
-- Run this setup once; no existing tables are removed.
PRAGMA foreign_keys = ON;
CREATE TABLE Customers (
 customer_id INTEGER PRIMARY KEY,
 customer_name TEXT NOT NULL,
 customer_city TEXT NOT NULL
);
CREATE TABLE Orders (
 order_id INTEGER PRIMARY KEY,
 customer_id INTEGER NOT NULL,
 order_total INTEGER NOT NULL CHECK(order_total >= 0),
 FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)
);
INSERT INTO Customers VALUES
 (101,'John','New York'),(102,'Lisa','Los Angeles'),(103,'David','Chicago'),
 (104,'Sarah','Boston'),(105,'Mike','Houston');
INSERT INTO Orders VALUES (1,101,200),(2,102,300),(3,101,150),(4,103,500),(5,102,250);
CREATE TABLE Accounts(account_id INTEGER PRIMARY KEY,balance INTEGER NOT NULL CHECK(balance>=0));
INSERT INTO Accounts VALUES (1,2000),(2,1000);

Order totals are whole currency units in this teaching dataset. Production systems need an explicit currency and precision policy rather than binary floating-point money.

Create, change and remove data

INSERT INTO Customers VALUES (106, 'Asha', 'Pune');
UPDATE Customers SET customer_city = 'Delhi' WHERE customer_id = 106;
DELETE FROM Customers WHERE customer_id = 106;

Read the WHERE condition before running UPDATE or DELETE. Without it, the command affects every row. DDL such as DROP TABLE removes structure, not merely selected records. TRUNCATE and ALTER syntax, privilege commands and transaction behaviour differ among products; they are not part of the portable lab.

Ask a precise question

SELECT customer_name, customer_city
FROM Customers
WHERE customer_city IN ('Boston', 'Houston')
ORDER BY customer_name;

This returns Mike/Houston and Sarah/Boston. ORDER BY makes the requested order explicit. Without ORDER BY, a result has no guaranteed presentation order.

NULL is not a value to compare with equals

NULL marks missing or unknown information; it is not the same as zero or an empty string. Test for it with IS NULL or IS NOT NULL, not = NULL. An outer join can produce NULL columns when no matching row exists. COUNT(*) counts result rows; COUNT(column) counts non-NULL values in that column.

Grouping and filtering

WHERE filters rows before grouping; HAVING filters groups after aggregation. SELECT should not mix an ungrouped ordinary column with grouped results unless the DBMS explicitly permits it and its meaning is valid. Prefer portable, explicit grouping.

Views, indexes and privileges

A normal view stores a query definition rather than a separate copy of every result row. An index is an additional access structure; it can improve suitable reads but costs space and maintenance during writes. It is not a guarantee that every query becomes faster. GRANT and REVOKE control privileges in server DBMSs; SQLite does not implement that user-privilege model.

Practice: add two customers in a transaction, check them with SELECT, then roll back. Verify that neither persists. Use a disposable database.

Database tools and interfaces

SQL can be entered through a command-line client, a graphical tool or an application interface. MySQL Workbench and Oracle SQL Developer are examples of product-specific graphical tools. These examples use SQLite. You can run them in a SQLite command-line client or a graphical tool that supports SQLite.

Use the shared dataset

The Customers and Orders tables introduced in Chapter 4 contain five customers and five orders. Run the complete setup in Chapter 6 before trying these queries.

Join related tables

SELECT c.customer_name, o.order_id, o.order_total
FROM Customers AS c
JOIN Orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

INNER JOIN returns the five matched orders. LEFT JOIN preserves all customers, including Sarah and Mike, whose order columns become NULL. A RIGHT JOIN preserves the right-hand table; a logically equivalent LEFT JOIN can be obtained by swapping the tables. An outer join followed by a WHERE condition on the nullable side may remove the unmatched rows—place conditions deliberately. Technical cross-check: PostgreSQL joins.

Total spend above 300

SELECT customer_id, SUM(order_total) AS total_order_amount
FROM Orders
GROUP BY customer_id
HAVING SUM(order_total) > 300
ORDER BY customer_id;

Check the result: John has 200 + 150 = 350, so customer 101 must be included.

Customers with more than two orders

SELECT customer_id, customer_name
FROM Customers
WHERE customer_id IN (
  SELECT customer_id FROM Orders
  GROUP BY customer_id HAVING COUNT(*) > 2
)
ORDER BY customer_id;

This dataset returns no rows. John and Lisa each have two orders, not more than two. This is a valid empty result, not a failed query.

Highest order, including ties

SELECT DISTINCT c.customer_id, c.customer_name
FROM Customers AS c
JOIN Orders AS o ON o.customer_id = c.customer_id
WHERE o.order_total = (SELECT MAX(order_total) FROM Orders)
ORDER BY c.customer_id;

Result: 103, David. Unlike an arbitrary LIMIT 1 subquery, this version retains every customer tied for the maximum order value. Maximum single order and maximum total customer spend are different questions.

EXISTS and NOT EXISTS

SELECT c.customer_name FROM Customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders AS o
  WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Result: Sarah, Mike. EXISTS checks whether a subquery returns at least one row. Here the subquery is correlated: it refers to the customer in the outer query. NOT EXISTS avoids the surprising effect that NULLs can have in some NOT IN queries.

Set operations

UNION combines compatible query results and removes duplicate rows; UNION ALL retains duplicates. INTERSECT gives shared rows; EXCEPT gives rows in the first result but not the second. Column counts and corresponding types must be compatible. Product support and the alternative MINUS spelling are dialect-specific.

SELECT customer_id FROM Customers
EXCEPT
SELECT customer_id FROM Orders
ORDER BY customer_id;

Result: 104 and 105. Use identifiers, not potentially repeated customer names, when identifying unmatched people.

A reusable view and an index

CREATE VIEW view_order_details AS
SELECT c.customer_name, o.order_id, o.order_total
FROM Customers AS c
JOIN Orders AS o ON c.customer_id = o.customer_id;

CREATE INDEX idx_orders_customer ON Orders(customer_id);

Run the view and index definitions once after the Chapter 6 setup. Read the view using SELECT. Measure an index with representative data and query plans; a five-row example cannot establish a production performance gain.

Transactions: both steps or neither

A transaction groups changes into a unit. COMMIT keeps the changes; ROLLBACK cancels the active transaction. A savepoint marks an intermediate position inside it; rolling back to it does not cancel earlier statements. Technical cross-check: PostgreSQL transactions.

Place the savepoint before both updates if cancelling the transfer must undo both. A savepoint placed after the debit would leave that debit in place when rolled back to. The example below demonstrates cancellation; application error handling remains necessary.

BEGIN;
SAVEPOINT before_transfer;
UPDATE Accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 500 WHERE account_id = 2;
-- Demonstrate cancelling the entire transfer:
ROLLBACK TO before_transfer;
RELEASE before_transfer;
COMMIT;

The Chapter 6 setup starts accounts at 2000 and 1000, so both balances remain unchanged. A real transfer also needs validated amounts, affected-row checks, sufficient-funds rules, concurrency handling and application error management. This is not production banking code.

Database programming: deliberately separate dialects

PL/SQL is Oracle's procedural language. MySQL stored-program syntax and PostgreSQL PL/pgSQL are different; do not paste one into another DBMS. Procedures encapsulate operations; functions return results subject to product-specific rules; triggers run on specified database events. Hidden trigger side effects can make debugging difficult.

Treat procedural SQL as a separate, DBMS-specific topic. The worked examples use SQLite; they do not execute Oracle PL/SQL, MySQL stored programs or PostgreSQL PL/pgSQL.

SELECT c.customer_id, c.customer_name
FROM Customers AS c
LEFT JOIN Orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Begin with business rules, not just a sample

A functional dependency X → Y means that any two rows with the same X values must also have the same Y values. The rule must hold for every valid database state, not just the current sample. For example, if each student identifier belongs to one student, student_id determines student_name. We use these rules to identify keys and separate data into well-designed tables. Reference: RPI normalization notes.

Our original example assumes one enrolment per student–course pair, one department per course, and these dependencies:

student_id → student_name
course_id → course_title, dept_id
dept_id → dept_name
student_id, course_id → grade

The initial relation is EnrolmentSheet(student_id, student_name, course_id, course_title, dept_id, dept_name, grade). The pair (student_id, course_id) determines every attribute and neither member alone determines grade, so the pair is a candidate key under the stated rules.

Why split it?

Repeating course titles across enrolment rows invites inconsistent updates. A course with no student cannot be recorded naturally in a table whose key requires a student. Deleting the last enrolment could lose the course's descriptive information. These are update, insertion and deletion anomalies. Reference: Cornell normalization overview.

First normal form

Record one student–course enrolment per row, not a comma-separated course list. Treat each field as one value from its intended domain. For example, S1 studying DBMS and OS becomes two enrolment rows, not a single 'DBMS, OS' field. Reference: Cornell 1NF.

Second normal form

A relation in 1NF is in 2NF when every non-prime attribute depends on each candidate key as a whole, not on just part of it. A non-prime attribute is one that does not belong to any candidate key. Here student_name depends on student_id alone; course details depend on course_id alone. Split these partial dependencies:

Student(student_id PK, student_name)
Course(course_id PK, course_title, dept_id, dept_name)
Enrolment(student_id PK/FK, course_id PK/FK, grade)

The Enrolment key is still the pair. Grade belongs with the entire pair. Introductory comparison: Cornell 2NF.

Third normal form

In a nontrivial dependency X → A, the attribute A is not already part of X. X is the determinant. A prime attribute belongs to at least one candidate key. A relation is in 3NF when, for every such dependency, X is a superkey or A is prime. BCNF uses the stricter condition that X must be a superkey. Formal definitions: RPI normalization notes.

Course still stores dept_name through course_id → dept_id → dept_name. Move department descriptions into Department(dept_id PK, dept_name), leaving Course(course_id PK, course_title, dept_id FK). Under the stated dependencies, each resulting table also satisfies BCNF. This is a conclusion about these rules, not about every database with similarly named tables. Introductory comparison: Cornell 3NF.

Check the decomposition

Each split shares a determinant that is a key in the separated descriptive relation: student_id, course_id or dept_id. This supplies a lossless-join argument for the successive binary decompositions. Each listed dependency can be checked within one resulting relation, so these dependencies are preserved. As an additional exercise, create a few rows in the original relation, split them into the four resulting tables and join them back together. Compare the reconstructed rows with the originals. A sample check supports your understanding, but does not replace the dependency argument. Further reading: Brown University normalization slides.

WITH CourseResources(course_id, book_id, instructor_id) AS (
  VALUES ('C1', 'B1', 'I1'), ('C1', 'B1', 'I2'),
         ('C1', 'B2', 'I1'), ('C1', 'B2', 'I2')
), CourseBook AS (
  SELECT DISTINCT course_id, book_id FROM CourseResources
), CourseInstructor AS (
  SELECT DISTINCT course_id, instructor_id FROM CourseResources
)
SELECT b.course_id, b.book_id, i.instructor_id
FROM CourseBook AS b
JOIN CourseInstructor AS i ON i.course_id = b.course_id
ORDER BY b.course_id, b.book_id, i.instructor_id;

The four ACID properties

Atomicity means that a transaction’s changes are applied as a whole or not at all. Consistency means that a transaction preserves the database’s rules when its logic is correct. Isolation controls how concurrent transactions affect what each can observe. Durability means that committed changes persist under the system’s stated guarantees. A transaction boundary does not make incorrect business logic correct.

Lost update: a schedule to inspect

Imagine two applications reading a counter initially equal to 10. A reads 10; B reads 10; A writes 11; B writes 11. Two increments were intended, but the result is 11. One remedy is an appropriate atomic database update; more complex read–modify–write logic needs a suitable locking or isolation design. This schedule is a teaching illustration. The single-connection SQLite examples do not reproduce concurrent behaviour.

Serializability is about the result, not necessarily sequential execution

A serializable execution must have the effect of some serial order. PostgreSQL's Serializable isolation can reject transactions to prevent anomalies; applications must handle serialization failures by retrying the whole transaction. It does not mean that every statement runs alone or that errors disappear. Reference: PostgreSQL isolation.

Locking and deadlocks

Basic two-phase locking first acquires locks, then releases them without acquiring new ones afterwards. Two transactions can still wait in a cycle: T1 holds A and waits for B, while T2 holds B and waits for A. Consistent acquisition order can help avoid this pattern; DBMS deadlock handling may abort a participant. Two-phase locking and two-phase commit solve different problems—concurrency control versus distributed commitment.

Recovery: logs, checkpoints and backups

Write-ahead logging records changes durably before the corresponding changed data pages are written. A DBMS can redo logged changes after a crash without having flushed every changed table page at each commit. PostgreSQL also uses archived WAL with a suitable base backup for point-in-time recovery. Reference: PostgreSQL WAL introduction.

A checkpoint helps bound recovery work; it is not an independent backup. A backup is a recoverable copy, and a restore test checks whether it can actually be used. The transaction example demonstrates logical rollback only; it does not simulate power failure, implement WAL or certify a backup procedure.

Distribution, fragmentation and replication

A distributed database manages logically related data at multiple networked sites. Horizontal fragmentation divides rows; vertical fragmentation divides columns, retaining identifiers needed to reconstruct records. Replication keeps copies. These choices can improve locality or availability but add communication and coordination costs; simply adding nodes does not guarantee better performance.

A university can place regional enrolment rows near regional offices. It might replicate a read-mostly course catalogue. Keeping identity data separately from finance columns is an example of vertical decomposition, but keys and access controls must preserve correct reconstruction and privacy.

Components and query execution

The global catalogue records schemas and data locations. A distributed query processor locates relevant fragments, chooses a plan and combines results. Local DBMSs execute local work; communication and transaction components coordinate cross-site operations. Network transfer may dominate some workloads, but not every workload.

Replication trade-offs

In synchronous replication, the system waits for confirmation from a configured set of replicas before reporting that a write has succeeded. It does not necessarily wait for every replica. Asynchronous replication can leave temporary lag. Consistency, durability, failover and availability guarantees depend on the actual protocol and configuration. A distributed DBMS is not automatically a globally ACID system for every operation.

A question worth asking

If two sites lose their network connection, can both accept writes to the same record? What happens when the connection returns? Answer this from the system's conflict and consistency policy—not from the word 'distributed'.

Indexes: choose an access path, not a decoration

An index can locate a small subset of rows without examining the entire table. The query planner may still prefer a scan, especially for a small table or a query returning much of it. Indexes require storage and write-time maintenance. Reference: PostgreSQL indexing introduction.

The idx_orders_customer index created in Chapter 7 supports lookups by customer_id. After creating that index, run the following statement to inspect SQLite’s chosen access path. The output may vary with the SQLite version and dataset; an observed plan does not establish a timing benefit on five rows.

EXPLAIN QUERY PLAN
SELECT * FROM Orders WHERE customer_id = 101;

Do not add an index to every column by default. Start with a frequently executed query, measure the workload and consider insert/update costs.