Chapter Purpose and Overview

Learning Objectives

1. 1Explain what a database is and why organized data storage matters.
2. 2Identify tables, records (rows), fields (columns), and data types in a relational database.
3. 3Explain what primary keys and foreign keys are and why they are needed.
4. 4Describe one-to-many and many-to-many relationships between tables.
5. 5Explain what a query is and what kinds of operations queries perform.
6. 6Read basic SQL SELECT statements and describe what they retrieve.
7. 7Use WHERE, ORDER BY, and aggregate functions in SQL at an introductory level.
8. 8Explain what a database management system (DBMS) does.
9. 9Define a transaction and explain the ACID properties.
10. 10Explain why backups and replication are important for reliability.
11. 11Describe what an information system is and give examples of types.
12. 12Connect database design to data governance and responsible information management.

Sections at a Glance

• 18.1Why Databases MatterProblems with scattered data; data redundancy; what a database solves; where databases appear in daily life
• 18.2Tables, Records, and FieldsRelational databases; rows as records; columns as fields; data types; constraints; database vs. spreadsheet
• 18.3Keys and IdentifiersPrimary keys; natural vs. surrogate keys; candidate keys; foreign keys; referential integrity
• 18.4Relationships Between TablesOne-to-many; many-to-many; junction tables; one-to-one; ER Diagrams
• 18.5QueriesSelection, projection, joining, aggregation, sorting, insertion, update, deletion; result sets; queries in everyday computing
• 18.6SQL at an Introductory LevelSELECT, WHERE, ORDER BY, aggregate functions, GROUP BY, JOIN, INSERT, UPDATE, DELETE
• 18.7Database Management SystemsWhat a DBMS does; storage, query processing, access control, concurrency, recovery; major DBMS types; web application architecture
• 18.8Transactions and ConsistencyThe bank transfer problem; commit and rollback; ACID properties; race conditions and concurrency
• 18.9Backups and ReliabilityFull, incremental, differential, and log backups; RPO and RTO; replication; high-availability systems
• 18.10Information Systems in OrganizationsTPS, MIS, DSS, CRM, ERP; school database systems; database administrators; data governance and policy

Why Databases Matter

What a Database Is

Where Databases Appear

1. In your own words, explain the difference between a database and a database management system. Use an analogy if it helps.
2. What is data redundancy? Give an example from a school context where redundancy could cause a problem.
3. Choose one type of organization from the table above. List three additional pieces of information its database would likely need to store beyond what is shown.

Tables, Records, and Fields

Records and Fields

Data Types and Constraints

1. Using the Students table above: how many records does it contain? How many fields? What is the data type of the GPA field?
2. A library database has a Books table. Suggest five fields it should contain, and for each field, specify an appropriate data type.
3. Why would a NOT NULL constraint be important on the LastName field of a Students table? What would happen without it?
4. Could a spreadsheet replace a database for storing 500,000 student records? What problems would arise?

Keys and Identifiers

Primary Keys

Natural Keys vs. Surrogate Keys

Foreign Keys and Referential Integrity

Gold = Primary Key (PK)  ·  Teal = Foreign Key (FK) referencing Teachers.TeacherID

1. In the Students table from Section 18.2, which field is the primary key? Could FirstName + LastName serve as a primary key? Explain why or why not.
2. Explain the difference between a natural key and a surrogate key. Give one example of each from a different context than the textbook.
3. A foreign key in an Orders table contains CustomerID: 8847, but there is no customer with that ID in the Customers table. What does referential integrity do in this situation? Why is that the correct behavior?
4. Why should primary keys never change after being assigned? Give a real-world scenario where changing a primary key would cause problems.

Relationships Between Tables

One-to-Many

Many-to-Many

One-to-One

Entity-Relationship Diagrams

  [Students]         [Enrollments]         [Courses]
  +-----------+      +-----------+-------+  +----------+
  |StudentID  |◄─────|StudentID  |CourseID|─►|CourseID  |
  |FirstName  |      +-----------+-------+  |CourseName|
  |LastName   |      (junction table)        |TeacherID |──►[Teachers]
  |GradeLevel |                              +----------+   |TeacherID|
  |GPA        |                                             |Name     |
  +-----------+                                             +---------+

  ◄── = foreign key reference
  one-to-many: one Teacher teaches many Courses
  many-to-many: Students ↔ Courses through Enrollments

1. Classify each relationship below as one-to-many or many-to-many, and explain your reasoning:
   
   a. One author can write many books; each book has one primary author.
   b. Students can check out many library books over time; the same book can be checked out by many students across time.
   c. One department can have many employees; each employee belongs to one department.
2. Design a junction table for a many-to-many relationship between Actors and Films. What fields would this table contain? What would a primary key for this table look like?
3. Why can a many-to-many relationship not be stored using just two tables and a single foreign key?

Queries

What Queries Do

1. For each scenario, identify the type of query operation (selection, projection, joining, aggregation, sorting, or a combination):
   
   a. "Show me the names of all students who borrowed a book in the last 30 days."
   b. "What is the total value of all orders placed this month?"
   c. "Show me each employee's name, their department name, and their manager's name."
   d. "List all products sorted from most expensive to least expensive."
2. A result set is not stored back in the database. Explain in your own words what this means and why it is important.

SQL at an Introductory Level

SELECT — Retrieving Data

SELECT FirstName, LastName
FROM Students;

WHERE — Filtering Rows

SELECT FirstName, LastName, GPA
FROM Students
WHERE GradeLevel = 11 AND GPA > 3.0;

ORDER BY — Sorting Results

SELECT FirstName, LastName, GPA
FROM Students
ORDER BY GPA DESC;

Aggregate Functions

SELECT GradeLevel, AVG(GPA) AS AverageGPA, COUNT(*) AS StudentCount
FROM Students
GROUP BY GradeLevel
ORDER BY GradeLevel;

JOIN — Combining Tables

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
ORDER BY Students.LastName;

INSERT, UPDATE, DELETE

-- Add a new student record
INSERT INTO Students (StudentID, FirstName, LastName, GradeLevel, GPA)
VALUES (1006, 'Leon', 'Mbeki', 12, 3.65);

-- Update an existing record
UPDATE Students
SET GPA = 3.30
WHERE StudentID = 1001;

-- Remove a record
DELETE FROM Students
WHERE StudentID = 1004;

1. Read this query and answer the questions below:
   

   SELECT FirstName, LastName, GPA
   FROM Students
   WHERE GradeLevel = 10
   ORDER BY GPA DESC;

   a. Which table is being queried?
   b. Which fields will appear in the result?
   c. Which students from the sample table (Section 18.2) will appear in the result?
   d. In what order will they appear?
2. Write a plain-English description of what this query does:
   

   SELECT COUNT(*) FROM Students WHERE GradeLevel = 12;

3. A developer writes this UPDATE statement:
   

   UPDATE Students
   SET GradeLevel = 12;

   What is the result? What should the developer have written instead?
4. Manually compute the result of this query using the sample data from Section 18.2:
   

   SELECT GradeLevel, COUNT(*), AVG(GPA)
   FROM Students
   GROUP BY GradeLevel;

Database Management Systems

What a DBMS Does

Common Database Systems

How Applications Use a DBMS

  User (Browser)
       │
       │  HTTP request: "Add item to cart"
       ▼
  Web Server
  (Application Code: Python / Node / Java / etc.)
       │
       │  SQL: INSERT INTO Cart (UserID, ItemID) VALUES (42, 789);
       ▼
  Database Management System (DBMS)
  ┌─────────────────────────────────────────┐
  │  Parses SQL → Creates execution plan    │
  │  Enforces constraints → Runs query      │
  │  Manages transaction → Returns result   │
  └─────────────────────────────────────────┘
       │
       │  Reads / writes data files
       ▼
  Storage (SSD / Hard Drive)
  [Data files · Indexes · Transaction logs]

1. List three responsibilities of a DBMS and explain why each matters. For each one, describe what could go wrong if the DBMS did not handle it.
2. When you log in to a streaming service and see your "continue watching" list, describe the sequence of events involving the DBMS. Include the query that might be sent.
3. What is the difference between a relational DBMS and a NoSQL DBMS? Give a use case where each type would be appropriate.

Transactions and Consistency

The Bank Transfer Problem

Transactions: Commit and Rollback

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 'CHK-001';

UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 'SAV-001';

COMMIT;  -- makes all changes permanent

  BEGIN TRANSACTION
         │
         ▼
    [Operation 1] ──FAIL──► ROLL BACK
         │                  (database returns to
         ▼                   pre-transaction state)
    [Operation 2] ──FAIL──► ROLL BACK
         │
         ▼
    [Operation N]
         │
         ▼
       COMMIT
  (all changes permanent)

The ACID Properties

1. In your own words, explain the difference between "commit" and "rollback" in a database transaction.
2. Which ACID property protects against the bank transfer problem described in this section? Explain why.
3. A hospital records a patient's medication dosage in two separate tables — the Prescriptions table and the Administration Log. Explain why this update should be wrapped in a transaction, and what could go wrong if it were not.
4. ACID compliance requires careful engineering in a DBMS. Why do you think financial, medical, and legal databases prioritize ACID compliance above almost everything else?

Backups and Reliability

Types of Backups

Key Reliability Measures

1. A small school takes a full backup every Sunday night at 11 PM and uses no other backup method. The database is corrupted at Friday 4 PM. How much data could be lost? What would you recommend to reduce this risk?
2. Explain the difference between a backup and replication. Which protects against accidental data deletion? Which protects against server hardware failure?
3. Why is it dangerous to store database backups on the same physical server as the database itself?

Information Systems in Organizations

Types of Information Systems

Database Administrators

Data Governance and Policy

1. Classify each of these as a TPS, MIS, DSS, CRM, or ERP system and explain your reasoning:
   
   a. A system that shows a customer service agent every purchase, complaint, and return for the customer currently on the phone.
   b. A system that records each sale the instant a customer checks out at a grocery store.
   c. A system used by a hospital to manage payroll, supply ordering, billing, and patient scheduling all in one place.
   d. A system that lets a retail chain's executives see projected revenue under different pricing scenarios.
2. A school stores student medical records, grades, and disciplinary records all in one shared database. Three different staff members — a nurse, a teacher, and an administrator — need access to different parts of this data. Describe a data governance policy that would control access appropriately.

Chapter Summary

• Sec 18.1Databases solve the problems of data redundancy and scattered files by providing a single organized system for storing and accessing data. A DBMS is the software that manages the database. Databases power nearly every digital system you use.
• Sec 18.2A relational database organizes data into tables. Each row is a record representing one instance; each column is a field representing one attribute. Fields have data types. Constraints enforce data quality automatically.
• Sec 18.3A primary key uniquely identifies each record and never changes. A foreign key in one table references the primary key of another, creating a connection. Referential integrity ensures every foreign key points to a real record.
• Sec 18.4Tables are connected through relationships: one-to-many (one teacher, many courses), many-to-many (students and courses, through a junction table), and one-to-one. ER Diagrams plan database structure visually.
• Sec 18.5A query is a request for data. Queries filter rows (selection), choose columns (projection), combine tables (joining), compute statistics (aggregation), and sort results. The output is a result set — a temporary view of the data.
• Sec 18.6SQL is the standard language for relational databases. SELECT retrieves data; WHERE filters rows; ORDER BY sorts results; aggregate functions compute statistics; GROUP BY aggregates by category; JOIN combines tables; INSERT, UPDATE, and DELETE modify data.
• Sec 18.7A DBMS handles storage, query processing, access control, concurrency, transaction management, integrity enforcement, and recovery. Major relational systems include MySQL, PostgreSQL, SQLite, SQL Server, and Oracle.
• Sec 18.8A transaction groups operations into a unit that either commits (all succeed, changes permanent) or rolls back (all cancelled, database returns to previous state). ACID properties — Atomicity, Consistency, Isolation, Durability — ensure transaction reliability.
• Sec 18.9Backups create copies that allow recovery after failure. Types include full, incremental, differential, and transaction log. RPO and RTO measure acceptable data loss and recovery time. Replication maintains real-time synchronized copies on separate servers.
• Sec 18.10Information systems combine databases with software, hardware, people, processes, and policies to support organizations. Types include TPS, MIS, DSS, CRM, and ERP. DBAs design and protect database systems. Data governance determines how organizations manage access, retention, and responsible use.

Key Terms Review

Diagram Review

  Table: Students
  ┌══════════════╤═══════════╤══════════╤════════════╤════════
  │ StudentID    │ FirstName │ LastName │ GradeLevel │ GPA   │
  │ (PK INTEGER) │ (TEXT)    │ (TEXT)   │ (INTEGER)  │(REAL) │
  ╞══════════════╪═══════════╪══════════╪════════════╪═══════╡
  │ 1001         │ Maria     │ Garcia   │ 11         │ 3.20  │
  │ 1002         │ James     │ Obi      │ 10         │ 3.75  │
  │ 1003         │ Priya     │ Sharma   │ 12         │ 3.90  │
  └──────────────┴───────────┴──────────┴────────────┴───────┘
       ↑                                                 ↑
   Primary Key                                      Data Type
   (unique, not null,                               REAL = decimal
    never changes)                                  numbers allowed

  Each ROW = one record (one student)
  Each COLUMN = one field (one attribute)
  PK = Primary Key · TEXT/INTEGER/REAL = data types

  Students                Enrollments (junction)       Courses
  ┌────────────┐          ┌────────────┬──────────┐    ┌──────────┬───────────────────┐
  │ StudentID  │◄─────────│ StudentID  │ CourseID │────►│ CourseID │ CourseName        │
  │ FirstName  │    FK    │ (FK)       │ (FK)     │ FK │ TeacherID│                   │
  │ LastName   │          └────────────┴──────────┘    └──────────┴───────────────────┘
  │ GradeLevel │                                                │
  │ GPA        │                                                │ FK
  └────────────┘                                                ▼
                                                          Teachers
                                                          ┌──────────┬──────────┐
                                                          │ TeacherID│ Name     │
                                                          └──────────┴──────────┘

  ◄──── = foreign key reference pointing to a primary key
  one-to-many: one Teacher → many Courses
  many-to-many: Students ↔ Courses (through Enrollments junction table)

Code Review

Code Review 18-1: Reading a SELECT Query

SELECT FirstName, LastName, GPA
FROM Students
WHERE GradeLevel = 10
ORDER BY GPA DESC;

1. Which table is this query reading from?
2. Which three fields will appear in the result?
3. What condition must a record meet to be included?
4. How will the results be ordered? What does DESC mean?
5. Trace the query against the Students table from Section 18.2. Which students appear in the result, and in what order?

Code Review 18-2: Reading a JOIN Query

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
WHERE Courses.CourseName = 'Introduction to CS';

1. How many tables does this query use?
2. What is the purpose of each JOIN condition?
3. Using the sample data from this chapter, what result would this query produce?
4. Explain in plain English what this query is asking for.

Code Review 18-3: Aggregate and GROUP BY

SELECT GradeLevel, COUNT(*) AS StudentCount, AVG(GPA) AS AverageGPA
FROM Students
GROUP BY GradeLevel
ORDER BY GradeLevel;

1. What does COUNT(*) compute? What does AVG(GPA) compute?
2. What does AS StudentCount do in the result?
3. What does GROUP BY GradeLevel do? Why is it necessary here?
4. Compute the result manually using the sample data from Section 18.2.

Practice Questions

Complete each sentence with the correct term from the chapter.

1. A ____________ is the field that uniquely identifies each record in a table and never changes.
2. A ____________ is a field in one table that refers to the primary key of another table.
3. The rule that every foreign key value must match a valid primary key is called ____________.
4. A ____________ groups multiple database operations into a single unit that either fully succeeds or fully cancels.
5. ____________ is the ACID property that ensures committed changes survive a system crash.
6. A ____________ is used to implement a many-to-many relationship between two tables.
7. The SQL clause that filters which rows appear in a result is ____________.
8. The SQL clause that combines rows from two tables based on a related field is ____________.

1. A Students table has fields: StudentID, FirstName, LastName, GradeLevel, Email, GPA. Which field would make the best primary key? Could Email serve as a primary key? Explain.
2. A school has a Teachers table and a Courses table. Each course has exactly one teacher; one teacher can teach multiple courses. What type of relationship is this? Where would the foreign key be placed?
3. Describe in plain English what this query does:
   SELECT CourseName FROM Courses WHERE TeacherID = 'T07';
4. What is the difference between a full backup and an incremental backup? What is the advantage of each?
5. Explain in your own words the difference between a database and an information system.

1. Consider an online store. List at least four tables that its database would likely need, and for each table suggest two or three fields it would contain. Identify primary keys and at least one foreign key relationship between tables.
2. Explain why storing teacher names directly in the Courses table as a text field (TeacherName: "Rivera") is worse than storing a foreign key (TeacherID: "T07"). What specific problem arises when the teacher's name changes?
3. An airline reservation system must ensure that only one customer can book the last available seat on a flight. Explain how transactions and the ACID property of isolation prevent two customers from both successfully booking the same seat simultaneously.
4. A nonprofit collects this information about volunteers: name, email, phone, date joined, hours volunteered per month, and projects they work on (each volunteer may work on multiple projects; each project has multiple volunteers). Design a database with appropriate tables. Describe tables, fields, primary keys, and any foreign keys or junction tables needed.

Error Analysis

Design and Application Questions

1. Design 18-1: Design a database for a small public library. Identify at least three tables. For each table, list fields, identify the primary key, and describe at least one relationship between tables. Sketch a simple ER Diagram using the notation from this chapter.
2. Design 18-2: A school wants to track student attendance electronically. Design a database table or tables to store this information. Consider what fields are needed, how absences and tardies would be distinguished, and how attendance would be linked to students and specific class periods.
3. Design 18-3: Write SQL queries (or describe them in plain English) for the following requests against the school database from this chapter:
   
   a. Find the names of all students with a GPA above 3.5.
   b. Count how many students are in each grade level.
   c. Find the names of all courses that Maria Garcia (StudentID 1001) is enrolled in.
   d. Find the average GPA of all students in the database.
4. Design 18-4: A hospital database stores patient records, doctor information, and appointments. Each appointment is between one patient and one doctor; each patient can have many appointments with different doctors; each doctor can have many appointments with different patients. What type of relationship exists between patients and doctors? Design the tables and explain your choices.

Real-World Applications

1. When you log into a website — a school portal, streaming service, or email — and enter your username and password, what database operations are likely happening behind the scenes? Describe the process step by step, including what query is sent and what the DBMS returns.
2. An airline wants to show available seats when you search its website. What tables might its database contain? What query would the website need to run to show available flights between two cities on a given date?
3. During a major online sale event, millions of customers are purchasing products simultaneously. Explain two ways that database design — transactions and concurrency control — protects against errors during this high-traffic event.
4. A hospital uses a database to store patient medical records. Describe three different user roles who might access this database and the level of access each should have. What database feature enforces these access levels?
5. A city stores property tax records in a database. A homeowner moves and needs to update their mailing address. Explain why it is better to store the homeowner's address in one place — their record in a Homeowners table — rather than in every tax record associated with them.

Chapter 18 Checkpoint

Mixed Review

These questions draw on earlier chapters of the textbook.

1. (Chapter 14) A CSV file and a database table can both store information in rows and columns. What are two advantages a database table has over a CSV file for storing data that is frequently updated and queried?
2. (Chapter 13) A database is an example of abstraction. What detail does the database hide from programs and users that query it? What interface does it provide instead?
3. (Chapter 12) A database uses an index on a field to speed up searches. How is this conceptually similar to binary search? What tradeoff does adding an index create?
4. (Chapter 10) A developer accidentally deletes all records from a critical table by omitting a WHERE clause. What kind of error is this — syntax, runtime, or logic? Why is a logic error particularly dangerous in a database context?
5. (Chapter 9) SQL functions like COUNT(), AVG(), and SUM() are similar to functions in a programming language. What do they have in common? How do they differ in how they are called and what they return?