Databases and Data Management

Week of 2026-10-13 · Download .docx

Objectives

Explain relational database concepts — tables, primary keys, foreign keys, ER diagrams — and describe ACID transaction properties.
Write basic SQL queries using SELECT, WHERE, INSERT, and JOIN to retrieve and manipulate data.
Compare NoSQL database types (document, key-value, column, graph) and explain when each is appropriate.
Apply normalization principles to reduce redundancy and describe how indexes improve query performance.

Key terms

Relational Database: Database organizing data into tables of rows and columns, linked through key relationships — queried with SQL.
Primary Key: Column(s) uniquely identifying each row in a table. The database enforces uniqueness and NOT NULL constraints.
Foreign Key: Column storing values from another table's primary key, creating a parent-child relationship and enforcing referential integrity.
ACID: Transaction properties: Atomicity (all-or-nothing), Consistency (valid state transitions), Isolation (concurrent transactions don't interfere), Durability (committed data survives failures).
SQL: Structured Query Language — the standard language for creating, reading, updating, and deleting data in relational databases.
SELECT: SQL command to retrieve rows from one or more tables — the Read operation in CRUD.
WHERE: SQL clause filtering rows based on a boolean condition — only rows where the condition is true are included.
JOIN: SQL operation combining rows from two or more tables based on a matching condition (typically FK = PK relationship).
Normalization: Process of organizing a database schema to eliminate data redundancy and prevent update anomalies.
Index: Auxiliary data structure (B-tree, hash) built on one or more columns to speed up SELECT queries at the cost of write overhead.
MongoDB: Document-oriented NoSQL database storing BSON (Binary JSON) documents with flexible, hierarchical schemas.
Redis: In-memory key-value store providing sub-millisecond response times — used for caching, session storage, and real-time data.
ER Diagram: Entity-Relationship diagram — visual tool showing database entities (tables), their attributes (columns), and relationships.
CRUD: Create, Read, Update, Delete — the four fundamental database operations mapping to INSERT, SELECT, UPDATE, and DELETE in SQL.

The concept

Data is the raw material of the information economy. Databases are the organized containers where data is stored, retrieved, and manipulated. Every application — from a student information system to a social network — is fundamentally a layer on top of a database.

**Relational Databases**

A relational database stores data in two-dimensional tables: rows are records, columns are attributes. Tables are linked through key relationships. The primary key uniquely identifies each row — the database enforces that no two rows share a primary key value and no primary key is NULL. A foreign key in one table references the primary key of another table, creating a parent-child relationship. The database enforces referential integrity: you cannot insert a child row that references a non-existent parent.

ER (Entity-Relationship) diagrams visualize the database design before any tables are created, showing entities (rectangles), their attributes, and relationships (one-to-many, many-to-many) using standardized notation.

**ACID Properties**

Reliable databases implement ACID transactions. Atomicity guarantees that a transaction is all-or-nothing — a bank transfer deducts from one account and credits another in a single indivisible operation. If the system crashes midway, the partial transaction is rolled back. Consistency ensures the database always moves between valid states. Isolation prevents concurrent transactions from interfering with each other's uncommitted changes. Durability ensures committed data survives system failures by writing to persistent storage.

**SQL**

SELECT retrieves rows: SELECT name, email FROM students WHERE enrollment = 2026. JOIN combines related tables: SELECT s.name, e.course FROM students s JOIN enrollments e ON s.id = e.student_id. INSERT adds records: INSERT INTO students (name, year) VALUES ('Williams', 2026). The WHERE clause filters rows — without it, an UPDATE or DELETE affects every row in the table.

**NoSQL**

Not all data fits the relational model. NoSQL databases sacrifice ACID consistency for horizontal scalability and flexible schemas. Document databases (MongoDB) store JSON-like documents with variable schemas — ideal for content management and catalogs. Key-value stores (Redis) provide O(1) lookups by a unique key — ideal for caching and session storage. Graph databases (Neo4j) model relationships as first-class data structures for social networks and recommendation engines.

**Normalization and Indexing**

Normalization eliminates redundancy by ensuring each piece of data is stored in only one place. When the same data exists in multiple locations, an update must be applied everywhere simultaneously — missing one location creates an inconsistency. Indexes build auxiliary data structures (typically B-trees) on frequently queried columns, reducing SELECT query time from O(n) table scans to O(log n) — at the cost of additional write overhead on INSERT, UPDATE, and DELETE.

Worked examples

Example 1: Writing a SQL JOIN query: A school database has two tables: students(id, name, year) and grades(student_id, course, grade). To list each student's name alongside their course grades, you need to JOIN the tables on the student_id foreign key: SELECT s.name, g.course, g.grade FROM students s INNER JOIN grades g ON s.id = g.student_id WHERE g.grade >= 90. This query returns only students with grades at or above 90, combining columns from both tables by matching the student_id relationship.

Example 2: Normalization example: A poorly designed single table stores: student_id, student_name, student_email, course_id, course_name, grade. If a course name changes, it must be updated in every row for that course — an update anomaly. Normalized design: students(id, name, email), courses(id, name), grades(student_id, course_id, grade). Now course_name exists in one place. Updating it requires one row change instead of hundreds. The tables are joined when combined data is needed.

Common mistakes

Confusing primary key and foreign key. The primary key uniquely identifies a row IN ITS OWN TABLE — it cannot be NULL and must be unique. The foreign key stores values FROM ANOTHER TABLE'S primary key — it can repeat (many orders for one customer) and can be NULL for optional relationships.
Writing SELECT * in production code. SELECT * returns every column, including future columns added by schema changes. This breaks applications that expect a specific column order. Always name the columns you need explicitly.
Forgetting WHERE in UPDATE and DELETE. UPDATE students SET year = 2027 with no WHERE clause updates every student. DELETE FROM grades with no WHERE clause deletes all grades. Always verify your WHERE clause before executing a destructive SQL command.
Thinking NoSQL is always better than SQL. NoSQL excels at flexible schemas, horizontal scaling, and massive volume. SQL excels at complex queries, ACID transactions, and well-structured relational data. The choice depends on the data model and consistency requirements, not on which sounds more modern.
Over-indexing a database. Every index speeds up SELECT but slows INSERT, UPDATE, and DELETE because indexes must be maintained. A write-heavy table with many indexes can perform worse than one with no indexes. Index only the columns used in frequent WHERE, JOIN, and ORDER BY clauses.

Self-check

Try each question before reading the answer. Answers at the bottom of this page.

1. Which SQL clause filters which rows are returned by a SELECT statement?

GROUP BY
ORDER BY
WHERE
JOIN

2. What does the 'A' in ACID stand for?

Accessible
Atomicity
Automated
Authentication

3. A foreign key in a table:

Uniquely identifies each row in its own table
References the primary key of another table
Speeds up query performance
Encrypts sensitive data

4. Which NoSQL type provides sub-millisecond lookups and is commonly used for caching?

Document (MongoDB)
Graph (Neo4j)
Key-value (Redis)
Columnar (Cassandra)

5. Database normalization primarily aims to:

Increase query speed
Eliminate redundant data storage to prevent update anomalies
Reduce the number of tables
Add indexes to all columns

Self-check answers

1. C — WHERE evaluates a boolean condition for each row and includes only matching rows in the result set.
2. B — Atomicity means a transaction is all-or-nothing — it either fully succeeds or fully rolls back with no partial changes.
3. B — A foreign key references another table's primary key, creating a parent-child relationship and enforcing referential integrity.
4. C — Redis is an in-memory key-value store providing sub-millisecond response times — the standard choice for caching and session storage.
5. B — Normalization stores each fact in one place, eliminating redundancy and the update anomalies that occur when the same data exists in multiple locations.

Canvas is the official record. This companion enhances the PGCC curriculum; it does not replace it. Last name and class year only. Students with a 504 plan or IEP: your accommodations apply.

↑ Back to top