• A database is an organized place to store data
• Built for large amounts of data
• Built to keep data consistent over time
• Data is stored so it can be found again later

• A database is an organized place to store data
• Data is structured
• Storage is intentional, not ad hoc

• Databases handle large volumes of data
• Much more than what fits in memory
• Designed to grow over time

• Databases keep data consistent
• Rules prevent invalid entries
• Same data seen by all users

• Data is stored so it can be found again later
• Queries let us ask questions
• Results are predictable and repeatable

• Before 1440 – Manual Writing: Handwritten records
• 1440 – Printing Press: Faster copying
• 1800s – Shipping Letters: ~10–14 days by ship

• Born: August 19, 1923
• Known for: Relational Database Model
• Key Contribution: Relational Algebra, Normal Forms
• Impact: Foundation of SQL and modern databases
• Award: ACM Turing Award (1981)

• 1970 – Relational Database
• 1980s – SQL: Standard query language
• 1990s – Client Server: Multi user databases
• 2000s – Digitalization: Paper to databases

• Cloud: Scalable remote databases
• Big Data: Massive data processing
• AI: Smarter data analysis
• Edge: Low latency storage

• Many devices produce data continuously
• Many users need shared access
• Many apps depend on the same source of truth

• Apps read and write data
• APIs connect services
• DBMS keeps data consistent
• Transactions ensure correct updates
• Concurrency control supports many users at once
• Constraints prevent invalid data
• Indexes improve query performance
• Security rules control who can access what

• LLMs can generate SQL, results are not always correct
• Generated queries may:
  • use invalid syntax
  • reference the wrong tables or columns
  • misinterpret the intent of the question
• Incorrect SQL can:
  • return misleading results
  • update the wrong rows
  • delete data permanently

• Software evolves quickly
• Frameworks change every few years
• Languages rise and fall
• Tools are frequently replaced

• Tables match how we organize records
• SQL gives a standard interface
• Constraints protect correctness
• Transactions protect consistency

• Entity → a table
• Row → a record
• Key → identity + links

flowers(id, name, color, price)
orders(id, flowerId, qty)
        

Question

SELECT name, price
FROM flowers
WHERE price > 2.00;
      

Can this query be used in programs written in Java, C++, and Python?

1. No, SQL queries are specific to Java
2. Each program requires a different query syntax
3. Yes, but only if the database is SQLite
4. Yes, SQL is programming language

Question

Which component is executing the SQL query?

1. The programming language runtime
2. The operating system
3. The database managment system
4. The text editor used to write the query

Question

What is the role of a programming language when working with a database?

1. To replace SQL entirely
2. To store data directly on disk
3. To send SQL commands to the DBMS
4. To define how tables are physically stored

Imperative

# step-by-step instructions
# how to do it

for flower in flowers:
  if flower.price > 2.00:
    print(flower.name, flower.price)
      

• Tell the computer how
• Explicit steps
• Order matters

Query A:
SELECT * FROM flowers;

Query B:
INSERT INTO flowers(name, price)
VALUES ('Rose', 2.50);
      

• Does the order of these queries matter?
• Would the result be the same if we swap them?

• Database: one file (.db)
• Table: structure inside the file
• SQLite has no server process

• Database: container (file in SQLite)
• Table: structured collection of rows
• SQLite database = single .db file

Step 1: Create the Table

• Create a table named inventory
• Columns:
• id INTEGER PRIMARY KEY
• name TEXT NOT NULL
• category TEXT NOT NULL
• price REAL NOT NULL
• quantity INTEGER NOT NULL

Verify 1

• Verify the table exists and columns are correct
• Use a SQLite CLI command (not SQL)

Step 2: Insert Data

• Insert three rows:
• Oak Table, Table, 299.99, 4
• Leather Sofa, Sofa, 899.00, 2
• Desk Lamp, Lamp, 39.50, 12
• Write three INSERT statements

Verify 2

• Read the inventory table
• Show: id, name, category, price, quantity

Business Question

• List items with low stock
• Definition: quantity <= 3
• Return: name, quantity

Step 3: ALTER TABLE

• Add a new column supplier to inventory
• Type: TEXT

Verify 3

• Verify the new column exists
• Use a SQLite CLI command

Step 4: Update Existing Rows

• Set supplier values:
• Oak Table → Pacific Furnishings
• Leather Sofa → West Coast Leather
• Desk Lamp → BrightHome
• Write three UPDATE statements

Verify 4

• Read and confirm supplier values
• Return: id, name, supplier, quantity

Final Check

• List all items supplied by BrightHome
• Return: name, category, quantity

Definition (No Meaning)

• A primary key is a column (or columns) to identify rows
• It is an identifier only (no semantic meaning required)
• It exists so rows can be referenced precisely

What a Primary Key Enforces

• Uniqueness (no duplicates)
• Stability (used for references)
• One row ↔ one key value

Why We Need It

• Target exactly one row
• Avoid accidental multi-row updates
• Enable relationships later (foreign keys)

Primary Key in Our Case Study

• Table: inventory
• Primary key: id
• Chosen as a simple numeric identifier

SQLite: Auto-Generated IDs

• INTEGER PRIMARY KEY can be auto-generated
• You can omit id during INSERT
• SQLite assigns a new integer value

AUTOINCREMENT (When to Use)

• For stricter "never reuse old ids" behavior
• Prevents reusing a deleted id later
• Usually not needed, but good to know

Verify the ID Was Assigned

• Always run a SELECT after inserts
• Confirm rows and IDs match expectations

Safe Updates Use the Key

• Primary key targets one row
• Reduces risk of mass updates
• More reliable than matching on names

Safe Deletes Use the Key

• Primary key targets one row
• Prevents deleting multiple matches
• Use with care

Common Constraints

• NOT NULL : value must exist
• UNIQUE : no duplicates allowed
• DEFAULT : value assigned automatically

• Column must always have a value
• INSERT fails if value is missing
• Useful for required attributes

• Ensures values do not repeat
• Often used for identifiers
• Different from primary key

• Reduces required input
• Prevents NULL values
• Encodes common assumptions

Supplier Table (Parent)

• Links rows across tables
• Preserves consistency
• Prevents invalid references

Task 1: Design the Supplier Table

• Table name: supplier
• At least 3 attributes
• Include a primary key
• Use NOT NULL and UNIQUE where appropriate

Task 2: Create the Supplier Table

• Use INTEGER PRIMARY KEY AUTOINCREMENT
• Ensure name is NOT NULL
• Ensure email is UNIQUE

Task 3: Add Supplier Reference

• Add supplier_id to inventory
• Use a foreign key constraint
• Reference supplier(id)

Agenda

• Requirements → entities
• Entities and relationships
• Cardinality: 1:1, 1:N, N:N
• Attributes, data types, constraints
• ERD → SQL schema

Translate Requirements → Model

• Underline nouns → candidate entities
• Underline verbs → candidate relationships
• Details become attributes

Entities

• Real world objects or concepts
• Usually nouns
• Later implemented as tables

Relationships

• Connections between entities
• Usually verbs
• Shown as lines in ERD

Quick Check

• Question: "Customer rents a Bike"
• Is it an entity or a relationship?

Quick Check

• Question: "Bike"
• Is it an entity or a relationship?

Name Rules

• Conceptually, ERD names can overlap
• Technically, table names must be unique
• Use consistent casing

Case Sensitivity

• Conceptually, case does not matter in ERD
• Technically, it depends on DB and OS

Text → Visual

• Text is good for brainstorming
• ERD is good for precision and review
• ERD is easier to validate as a group

• Conceptual model of the database
• Shows entities and their attributes
• Illustrates relationships between entities
• Independent of any database system

Attributes

• Entities are not enough
• Each entity has a list of attributes
• Attributes become columns

Primary Key Attribute

• Mandatory technical attribute
• Unique per row
• Often integer

• Each entity includes a primary key
• Primary key uniquely identifies each record
• Primary key cannot be NULL
• Primary key remains stable over time

Optional Attributes

• Extra details keeps the system useful
• Not needed to identify the entity
• Often nullable

• Entities can have multiple descriptive attributes
• Attributes store detailed information
• Not all attributes are required for identification
• Attributes refine the meaning of an entity

Data Types

• Each attribute has a data type
• Data types impact validation and storage
• Keep types aligned across layers

• Each attribute is assigned a data type
• Data types define how values are stored
• Correct types prevent invalid data
• Data types must align with application logic

Constraints

• Rules that protect the database
• Improve correctness
• Make errors visible early

• Constraints enforce data correctness
• Primary keys ensure uniqueness
• NOT NULL prevents missing values
• UNIQUE avoids duplicate data

CREATE TABLE Customer (
  CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
  Name VARCHAR(100) NOT NULL,
  Email VARCHAR(100) UNIQUE,
  Phone VARCHAR(15)
);

CREATE TABLE Bike (
  BikeID INTEGER PRIMARY KEY AUTOINCREMENT,
  Model VARCHAR(100) NOT NULL,
  Color VARCHAR(50),
  Condition VARCHAR(50),
  Availability BOOLEAN DEFAULT TRUE
);

CREATE TABLE RentalTransaction (
  RentalID INTEGER PRIMARY KEY AUTOINCREMENT,
  CustomerID INTEGER NOT NULL,
  BikeID INTEGER NOT NULL,
  BookingDate DATE NOT NULL,
  ReturnDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
  FOREIGN KEY (BikeID) REFERENCES Bike(BikeID)
);

Misaligned Data Types

• Same data, different types across layers
• Causes validation failures and data loss
• DB rejects invalid formats

ERD Quiz (1/3)

• Question: What does an entity represent?
• A. A database table
• B. A real world object or concept
• C. A relationship between tables
• D. A data attribute

ERD Quiz (2/3)

• Question: What is the purpose of a relationship?
• A. To store data
• B. To define data types
• C. To show how entities connect
• D. To create unique identifiers

ERD Quiz (3/3)

• Question: What is the purpose of a primary key?
• A. Allow duplicate rows
• B. Uniquely identify each row
• C. Establish relationships
• D. Enforce data types

• Travel booking application domain
• Users search and compare options
• Bookings include flights, hotels, and rental cars
• Attributes capture price, date, and location

Why Minimum Relationships?

• Performance: less joins
• Simplicity: easier to maintain
• Clarity: easier to explain and test
• Consistency: less places for mismatch

Querying Relationships

• One relationship → one join
• More relationships → more joins
• Start from the filtering table

Module 2 Recap

• Requirements drive the model
• Entities (nouns) become tables
• Relationships (verbs) connect entities
• Attributes need types and constraints
• ERD becomes SQL schema

• Draft an ERD for a bike rental store
• Identify core entities (e.g., Bike, Customer, Rental)
• List key attributes for each entity
• Mark primary key attributes
• Decide which entities are connected

Agenda

• Gather requirements
• Extract entities and relationships
• Build ERM (PlantUML)
• Convert to SQL tables
• Insert sample data
• Query and maintain data

Requirements

• One store location (for now)
• Basic customer info
• Orders with line items
• Inventory count per product

Nouns → Entities

• Customer
• Product
• Order
• OrderItem
• Inventory

Key Modeling Decision

• We want max 3 tables
• Orders still need multiple items
• We store each sale as a row with two foreign keys

Relationships (conceptual)

• Customer buys Products (N:N)
• Sales rows store qty and unit_price at purchase time
• Product stock quantity (1:1 inventory)

ERM Diagram (PlantUML)

• 3 tables only
• Sales captured as rows with 2 foreign keys
• Still shows true business relationships

Sales row format

• Each row = one purchased product
• Records customer_id and product_id (two FKs)
• Captures qty and unit_price at purchase time

Primary Keys

• Customer: customer_id
• Product: product_id
• CustomerProductSales: sale_id

Constraints (simple)

• sku UNIQUE
• qty_on_hand NOT NULL DEFAULT 0
• Sales.customer_id must exist
• Sales.product_id must exist

From ERM → Tables

• Entities become tables
• Attributes become columns
• Relationships become foreign keys

Schema: Customer

• Keep identity separate from email
• Email is unique but can change
• Phone stored as text

Schema: Product

• sku is a business identifier (unique)
• qty_on_hand defaults to 0
• reorder_level helps restocking

Schema: CustomerProductSales

• References Customer and Product
• Stores qty and unit_price per purchase
• Status kept simple for demo

Sample Data Plan

• 2 customers
• 6 products
• Sales rows represent purchases
• Multiple rows can share the same sale_date

Insert Customers

• Keep it realistic
• Email optional but unique when present

Insert Products (part 1)

• sku is the business identifier
• qty_on_hand reflects current stock

Insert Products (part 2)

• Mix high and low cost items
• Reorder level supports planning

Create Sales

• Each row captures one purchased product
• unit_price captured at purchase time
• Multiple rows on same date can represent one "order"

Create Sales (more)

• Multiple items represented as multiple rows
• Different customer

Read: List Products

• Start with simple SELECT
• Show inventory and reorder level

Read: Low Stock Report

• Common operational query
• Used by staff daily

Read: Orders per Customer

• Simple join (Customer → Sales)
• Counts sales rows, shows totals

Update: Restock Inventory

• Staff receives shipment
• Increase qty_on_hand

Update: Price Change

• Price changes over time
• Sales keep old unit_price per row

Update: Sale Status

• Sale rows move through states
• NEW → PAID → FULFILLED

Delete: Remove a Test Sale

• Common during development
• Only delete safe rows

Data Quality Check

• Never allow negative stock
• Never allow negative prices
• Use constraints or checks when available

Why 3 tables is a good start?

• Shows minimum viable schema
• Makes tradeoffs explicit
• Sets up normalization discussion

Optional Extension (later)

• Delivery address per order
• Payments table
• Warehouses and multi location stock
• Returns and refunds

Recap

• Requirements define scope
• ERM clarifies entities and relationships
• Tables implement the model
• Data and CRUD prove the design works

• Born: August 19, 1923
• Known for: Relational Database Model
• Key Contribution: Relational Algebra (1970)
• Impact: Foundation of SQL and modern databases
• Award: ACM Turing Award (1981)

• Practice Questions
• Set Theory
• Relational Algebra
• Core Operations
• Advanced Operations
• Optimization and Efficiency
• Application Use Case

• No concept of "set"
• No way to guarantee uniqueness
• Only lists or bags
• Duplicates unavoidable
• No math for collections

items = []
items.append("chair")
items.append("table")
items.append("chair")  # duplicate allowed!
print(items)  # ['chair', 'table', 'chair']

• Union (∪) - 1873 - Georg Cantor
• Intersection (∩) - 1873 - Georg Cantor
• Difference (−) - 1873 - Georg Cantor
• Complement (ᶜ) - 1888 - Richard Dedekind
• Logic operators (∨, ∧, ¬) - 1879–1890s - Frege / Peirce

• Union (∪) ↔ OR (∨)
• Intersection (∩) ↔ AND (∧)
• Complement (ᶜ) ↔ NOT (¬)
• Difference (−) ↔ AND NOT (∧¬)

• Set: collection of things
• No duplicates allowed
• Union: combine sets
• Intersection: find common
• Difference: remove one
• Compare set sizes
• Organize & count clearly

• Developed by Georg Cantor (late 19th century)
• No duplicates – ensures clarity
• Key operations: union, intersection, difference
• Foundation for relational algebra

Ordered sequence of elements

• (1, 'Trek', 'Mountain', 1200)
• (1, 'Trek', 'Mountain', 1200)
• (1, 'Trek', 'Mountain', 1200)
• (2, 'Giant', 'Road', 1500)
• (3, 'Cannondale', 'Hybrid', 1000)

• No duplicate rows
• All tuples have the same attributes
• Example schema: Bikes(bikeId, brand, type, price)

• Filters rows based on condition
• Reduces number of tuples
• Example: σ(price < 1200)(Bicycles)

• Selects specific columns
• Removes unwanted attributes & duplicates
• Example: π(brand, price)(Bicycles)

• Combines two compatible relations
• Removes duplicates
• Example: Available ⋃ Sold

• Tuples in first but not in second
• Example: Stock − Sold

• All possible combinations of tuples
• Example: Bikes × Stores

• Cartesian + equality on shared attribute names
• Example: Orders ⨝ Customers

• Tuples in left that match ALL values in right
• Example: Students ÷ RequiredCourses

• Renames relation or attributes
• Example: ρ(Inv)(Bicycles)

• Reduce rows early
• Reduce columns early
• Delay large joins

• Logical order of operations
• Goal is small intermediates

• Apply filters before joins
• Joins touch less tuples

Slow (filter late)

Step 1: Bicycles ⨝ Stores
  → large intermediate result

Step 2: σ price < 500
  → many rows discarded late

π brand
      

• Nested projections collapse
• Remove unused columns once

• No attributes used from table
• No predicates applied

• Associative
• Commutative
• Cost changes, result does not

• Orders (O): 1,000,000
• Customers (C): 100,000
• Regions (R): 10

• Large intermediate result
• Filtering happens late

• Filter small relation first
• Join reduced inputs

• less rows
• less columns

• Flowers over $10
• Only roses
• Local suppliers

• Join first
• Filter later

• Filter first
• Join reduced sets

• Flowers: 1,000,000
• Suppliers: 200,000
• Supply: 5,000,000

• Deeply nested joins
• Multiple selections

• Selections pushed down
• Smaller joins

• Logical rules
• Query equivalence

• .timer on shows execution time
• journal_mode OFF avoids disk logging
• synchronous OFF skips fsync
• temp_store MEMORY keeps temp data in RAM

• Disables rollback journal
• No crash recovery
• Fast writes

• Skips fsync
• OS buffers writes
• Lower latency

• Temporary tables
• Intermediate results
• No temp disk files

• Collects table statistics
• Estimates row counts
• Improves join order

• Generate sequences internally
• No external files
• Large but controlled datasets

• Internal sequence generation
• No external data files
• Deterministic and repeatable

• Single SQL statement
• Many rows inserted
• No application loop
• Set based execution

• Recursive query generates numbers
• COUNT defines table size
• Abort condition stops recursion

-- Define a recursive sequence
WITH RECURSIVE seq(x) AS (
  -- Base case: start at 1
  SELECT 1

  UNION ALL

  -- Recursive step: increment
  SELECT x + 1
  FROM seq

  -- Abort condition
  WHERE x < 200000
)
      

• x is the column name of the recursive table
• seq is a temporary result set
• Each row in seq has one value: x
• seq = table, x = column

• Wall clock = total elapsed time
• User = CPU executing query
• Sys = OS and kernel work

• real: wall-clock time
• user: CPU time in SQLite
• sys: OS-level overhead

• Same query result
• Different execution time

SELECT COUNT(*)
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.region = 'West';
      

• How would you make this faster?
• Which table should be filtered first?

SELECT *
FROM sales s
JOIN products p ON s.product_id = p.id
WHERE p.category = 'Bike';
      

• Where should selection be applied?

SELECT o.id
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN regions r ON c.region_id = r.id
WHERE r.name = 'West';
      

• Which join should happen first?

SELECT *
FROM enrollments e
JOIN students s ON e.student_id = s.id
WHERE s.major = 'CS';
      

• How to reduce intermediate size?

SELECT *
FROM supply sp
JOIN suppliers s ON sp.supplier_id = s.id
JOIN flowers f ON sp.flower_id = f.id
WHERE s.location = 'Local';
      

• Which table is most selective?

SELECT *
FROM logs l
JOIN users u ON l.user_id = u.id
WHERE u.active = 1;
      

• Which relation should be reduced first?

SELECT *
FROM orders o
JOIN items i ON o.id = i.order_id
WHERE o.date > '2025-01-01';
      

• What happens if we filter orders first?

SELECT *
FROM activity a
JOIN conditions c ON a.id = c.activity_id
WHERE c.type = 'once';
      

• Where is the selective predicate?

SELECT *
FROM payments p
JOIN users u ON p.user_id = u.id
JOIN countries c ON u.country_id = c.id
WHERE c.code = 'US';
      

• Which join should be last?

SELECT *
FROM reviews r
JOIN products p ON r.product_id = p.id
WHERE p.rating > 4;
      

• How does early filtering help?

SELECT COUNT(*)
FROM (
  SELECT customer_id
  FROM customers
  WHERE region = 'West'
) c
JOIN orders o ON o.customer_id = c.customer_id;
      

SELECT s.sale_id, s.product_id, s.qty
FROM (
  SELECT id
  FROM products
  WHERE category = 'Bike'
) p
JOIN sales s ON s.product_id = p.id;
      

SELECT o.id
FROM (
  SELECT id
  FROM regions
  WHERE name = 'West'
) r
JOIN customers c ON c.region_id = r.id
JOIN orders o ON o.customer_id = c.customer_id;
      

SELECT e.student_id, e.course_id
FROM enrollments e
JOIN (
  SELECT id
  FROM students
  WHERE major = 'CS'
) s ON e.student_id = s.id;
      

SELECT sp.supplier_id, sp.flower_id
FROM (
  SELECT id
  FROM suppliers
  WHERE location = 'Local'
) s
JOIN supply sp ON sp.supplier_id = s.id
JOIN flowers f ON sp.flower_id = f.id;
      

SELECT l.log_id, l.user_id, l.action
FROM logs l
JOIN (
  SELECT id
  FROM users
  WHERE active = 1
) u ON l.user_id = u.id;
      

SELECT o.id, i.item_id, i.qty
FROM (
  SELECT id
  FROM orders
  WHERE date > '2025-01-01'
) o
JOIN items i ON o.id = i.order_id;
      

SELECT a.id, a.title, c.type
FROM activity a
JOIN (
  SELECT activity_id, type
  FROM conditions
  WHERE type = 'once'
) c ON a.id = c.activity_id;
      

SELECT p.payment_id, p.amount
FROM (
  SELECT id
  FROM countries
  WHERE code = 'US'
) c
JOIN users u ON u.country_id = c.id
JOIN payments p ON p.user_id = u.id;
      

SELECT r.review_id, r.product_id, r.stars
FROM reviews r
JOIN (
  SELECT id
  FROM products
  WHERE rating > 4
) p ON r.product_id = p.id;
      

• Practice Questions
• Relational Database Interfaces:
  • Admin Interface
  • Command-Line Interface (CLI)
  • Application Programming Interface (API)
  • Application User Interface (UI)
• PostgreSQL
• Homework 2, Lab 2

• Relational model introduced
• Research prototypes
• Early query languages
• Interfaces are terminals

• Users worked directly in a terminal
• No graphical tools: only text commands and scripts
• SQL typed manually and executed immediately
• Automation came from shell scripts and batch jobs
• Fast and precise, but less friendly for non technical users

• Relational databases enter mainstream
• DBA role becomes formal
• Vendor tooling grows
• GUIs appear

• Users do not talk to the database directly
• A GUI sits between the user and the database
• The GUI translates user actions into queries
• The database remains protected and structured
• This pattern scales from desktop apps to the web

• SQL becomes the common language across systems
• Scripting and batch jobs become normal operations
• ODBC and JDBC spread database connectivity
• Client tools focus on productivity forDBAs

• Apps standardize frontend, backend, database tiers
• Drivers mature across languages (eg. Java)
• Connection pooling and app servers
• Security push access through backend services

• Users access the system over the internet
• Frontend handles presentation and user interaction
• Backend enforces business logic and security
• Database is never exposed directly to users
• This becomes the dominant web architecture

• Managed database web based admin consoles
• Infrastructure automation becomes routine
• BI dashboards spread to non technical users
• Identity, roles, auditing, and encryption

• Workloads are split into independent cloud services
• Frontend and backend scale separately
• Databases run as managed cloud services
• Infrastructure concerns are abstracted away
• Everything communicates over secure networks

• Hybrid workflows: CLI + web UI + APIs
• Policy driven security and compliance
• Observability becomes first class
• Automation and assistants

• Databases are stable
• Interfaces change often
• Each interface fits a role

• 1970s: Early Databases
• 1980s: Admin Tools Emerge
• 1990s: Command-Line Interface (CLI)
• 2000s: APIs Enable Integration
• 2010s: Web-Based Interfaces
• Today: Hybrid and Automated Systems

• Application connects to the database directly
• Uses a driver and connection string
• Runs SQL without HTTP

• Connects online directly to a database using drivers

• Uses a connection string
• Contains host, database, user, SSL mode

• Direct DB API: Uses drivers for queries
• Lower overhead, higher exposure

• Use authentication (user/pwd, certificates)
• Restrict access with roles and permissions
• Enable SSL and TLS for encryption

• Driver: psycopg2
• Connection string
• Simple query

• Use connection pooling
• Restrict database exposure
• Sanitize inputs to prevent SQL injection

• Clone the repository
• Navigate to the project directory

• Relational Database Admin Interfaces
• Introduction PostgreSQL
• Relational Database Developer Interfaces
• Relational Database User Interfaces
• Practice Questions

• It is the control surface for a database system
• Used by DBAs, platform engineers, and senior devs
• Focus: ownership tasks, safety, repeatability, audit
• Includes roles, backups, perf, schema changes

CLI (traditional)

• Scriptable and repeatable
• Easy to review in change logs
• Works over slow links
• Good for emergency response

psql
pg_dump / pg_restore
SQL scripts
cron / CI jobs

• Admin defines roles first
• Roles get permissions (not individual users)
• Apps run with service accounts
• Auditing requires predictable role design

• Backups are only real if restore is tested
• Prefer scheduled, automated backups
• Keep copies separated from the primary system
• Track: RPO (data loss) and RTO (recovery time)

• Schema is a contract between apps and data
• Changes should follow a predictable sequence
• Prefer migration scripts under version control

• Slow queries and query volume
• Locks and long transactions
• Disk growth and bloat
• Connection counts and errors
• Replication lag (if used)

• Users should not reach the database directly
• Apps should access data through a backend
• Admins may access DBMS directly, with strict controls
• Admin access should be logged and limited

• Admin UI for hosted PostgreSQL
• Useful for quick setup and inspection

• Admin GUI for local SQLite databases
• Open .db files and inspect tables
• Run SQL queries and export results (CSV)
• You can see schema and data side by side

• SQLite is useful for learning and local work
• PostgreSQL is used for multi user, production systems
• PostgreSQL specific query behavior

• Uses API key for authentication
• Connects to PostgreSQL database
• Ensures secure communication with SSL

• Exposes endpoints for frontend access
• Uses HTTP methods (GET, POST, etc.)
• Requires authentication for secure access

• Fetching data from APIs
• Displaying database records in UI
• Sending user input to backend

• System overview: Flower store
• Interfaces and responsibilities
• Local DB (SQLite) vs Remote DB (Postgres)
• Team project setup
• Role based implementation

ERD goals

We want to keep track of the flower inventory.

• Identify the main entity
• Choose a primary key
• Select essential attributes

Start on paper before using any tool.

• What must be unique?
• What must be not null?
• What should have a default value?

Constraints protect the data, not the code.

SQLite

• Local database file
• No server process
• Easy to set up
• Good for learning and prototyping

Starter code

# first time only
git clone https://github.com/SE4CPS/2026-COMP-163.git

# every class / work session
cd 2026-COMP-163
git pull
      

• 550e8400 - Time based or random value
• e29b - Time mid segment
• 41d4 - Version and variant identifier

• Prevents ID conflicts in distributed databases
• Harder to guess compared to auto-increment IDs

• Aggregates: COUNT MIN MAX AVG SUM
• Sort and limit: ORDER BY LIMIT
• Group and filter: GROUP BY HAVING
• Patterns: top N summaries checks
• Practice questions: intermediate

• Familiar data for advanced practice
• One table supports many patterns
• Later: joins across multiple tables

• Sort happens after row filtering
• ASC increases, DESC decreases
• Use multiple columns for ties
• Sorting costs time, plan

• Goal: most expensive first
• Use DESC for highest first
• Add tie breaker by name

In the logical order of a query, ORDER BY is applied:

• LIMIT restricts returned row count
• Pair with ORDER BY for top N
• Useful for paging and dashboards
• Without ORDER BY, results mislead

• Top N needs stable sort
• Sort by price descending
• Limit to three rows

Pick the best explanation:

• COUNT(*) counts rows
• COUNT(column) ignores NULLs
• Common for dashboard metrics
• Often paired with GROUP BY

• Goal: one number
• COUNT(*) counts every row
• Useful for inventory size

Choose the correct statement:

• MIN returns smallest column value
• Works on numbers dates text
• Spot low price or stock

• Goal: smallest price
• Return one value
• Useful for pricing checks

Pick the best answer:

• MAX returns largest column value
• Common for highest price stock
• Use ORDER BY to get row

• Goal: largest price value
• Aggregate returns one value
• Next: find matching row

You want the full flower record with the highest price.

• SUM adds values across rows
• Total stock and total value
• Watch NULLs and numeric types

• Goal: total inventory units
• Use SUM on stock
• Result is one number

Pick the most fitting use case:

• AVG computes the mean value
• Useful for price baselines
• Use GROUP BY for averages
• Round for display

• Goal: average price
• Return one value
• Round for readability

What does AVG compute?

• GROUP BY forms row buckets
• Aggregates computed per bucket
• SELECT must match grouped columns
• Foundation for reports and summaries

• One row per category
• Count items, average price, stock
• Classic reporting pattern

When you GROUP BY category, each result row represents:

• WHERE filters rows before grouping
• HAVING filters groups after grouping
• HAVING often uses aggregates
• Keep only meaningful buckets

• Group by category
• Keep categories with enough items
• Use HAVING with COUNT(*)

You want categories where COUNT(*) is at least 2. Which clause?

• DISTINCT removes duplicate output rows
• COUNT DISTINCT counts unique values
• Useful for checks and coverage
• Traditional habit: verify uniqueness first

• Question: how many suppliers?
• Use COUNT DISTINCT supplier
• Result is one number

What is being counted?

• Real data contains ties
• Second column stabilizes ordering
• Traditional habit: define consistent ordering

• Same price appears multiple times
• Sort by price then name
• Output becomes stable

Choose the best reason:

• WHERE removes rows before grouping
• Focus subset: in stock only
• Aggregates run on remaining rows

• Filter rows: stock > 0
• Then compute AVG(price)
• Round for readability

WHERE changes the aggregate results because it:

• Many queries are sanity checks
• Find groups violating expectations
• Example: weak supplier inventory

• Group by supplier
• Compute SUM(stock)
• Keep only low totals

When does HAVING filter happen?

• Rank groups, not just rows
• Compute metric, then sort
• Limit shows top few groups

• Compute group totals
• Sort totals descending
• Limit to top two

Pick the most correct query pattern:

• NULL means missing or unknown
• Some aggregates ignore NULL values
• COALESCE replaces NULL with default
• Traditional habit: state missing rules

• Some rows have missing stock
• COALESCE converts NULL to zero
• SUM becomes consistent

Choose the correct interpretation:

• CASE creates categories inside queries
• Useful for cheap versus premium
• Often used with GROUP BY

• Create tier label with CASE
• Group by tier label
• Count items per tier

Pick the best description:

• OFFSET skips rows after sorting
• Used for pagination by pages
• Always pair with ORDER BY
• Big data prefers keyset paging

• Assume page size equals two
• Page one uses OFFSET zero
• Page two uses OFFSET two

Choose the correct use:

• UUID means universally unique identifier
• 128 bit identifier value
• Designed for global uniqueness
• Safe across systems and services

• No collision across databases
• Good for distributed systems
• IDs created outside database
• Harder to guess identifiers

• UUID is native data type
• Stored efficiently internally
• Readable text representation
• Works with indexes and keys

• Use built in extensions
• Random values generated automatically
• No application side logic needed

• UUID generated on insert
• No value passed explicitly
• Good for primary keys
• Common modern schema practice

SELECT category, COUNT(*) AS items
FROM flowers
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY items DESC
LIMIT 5;
        

You want to filter rows where stock > 0 before computing SUM(stock).

SELECT COUNT(DISTINCT category)
FROM flowers;
        

SELECT category, price, COUNT(*)
FROM flowers
GROUP BY category;
        

What is wrong?

Goal: return the row for the cheapest flower with stock > 0

• Compute several metrics together
• Useful for status snapshots
• Traditional habit: keep core metrics

• One row output
• Many metrics at once
• Good for dashboard tiles

SELECT COUNT(*), MIN(price), MAX(price)
FROM flowers;
        

• HAVING can filter by AVG
• Find expensive categories quickly
• Keep only important groups

• Group by category
• Compute AVG(price)
• Keep groups with avg ≥ 10

Which level is filtered?

• Groups can use multiple attributes
• This creates finer buckets
• Typical: category plus supplier

• Two keys define each group
• SUM stock per pair
• Sort for readability

Each result row represents:

• Keep groups above or below totals
• Useful for low stock categories
• Classic threshold reporting

• Group by category
• Compute totals per category
• Keep totals below forty

You already grouped and computed SUM(stock). Now you filter by that sum using:

• Order grouped results by aggregates
• Example: highest average categories
• Use alias or expression

• Aggregate per category
• Order by AVG(price) descending
• Top categories appear first

This ordering is based on:

Pick the clause that defines the buckets:

Pick the clause that filters aggregated results:

Select the correct query:

COUNT(*) produces:

Choose the best answer:

• One table only for practice
• Use aggregates and grouping
• Use ordering and limiting
• Traditional habit: start small first

• We will use PostgreSQL syntax in all samples
• Core idea: JOIN combines rows from multiple tables
• Why: Answer questions without duplicating data
• From ERD to SQL: relationship → FK → JOIN
• Types: INNER LEFT RIGHT FULL CROSS SELF
• Rule: ON vs WHERE matters for outer joins

• Business questions span tables, not one table
• Examples you will actually run in PostgreSQL:
• Books with publisher name
• Books with author list (STRING_AGG)
• Publishers with zero books (quality)
• Store facts once, query them many times

• ERD shows relationships between entities
• SQL tables store those relationships using keys
• One to Many: put FK on the many side
• Many to Many: create a bridge table
• Traditional habit: let the ERD guide the join path

• Relational design keeps data normalized
• Joins answer questions across tables
• Example: books and authors live in different tables
• Traditional habit: avoid duplicated facts

• authors(author_id,..).
• publishers(publisher_id,...)
• books(book_id, publisher_id, ...)
• book_authors(book_id, author_id) many to many

• Keep only rows that match on both sides
• Unmatched rows are dropped
• Most common join in reporting
• Traditional habit: start with INNER to validate keys

• Match books.publisher_id to publishers.publisher_id
• Books with publisher_id NULL will not appear

Pick the best statement:

• Keep all rows from the left table
• Match rows from the right table when possible
• Unmatched right side becomes NULL
• Traditional habit: use LEFT JOIN for coverage checks

• Shows "Systems Thinking" even publisher_id is NULL
• Publisher fields become NULL when missing

Filter in WHERE (❌ risky)

SELECT
  b.book_id,
  b.title,
  p.name AS publisher
FROM books b
LEFT JOIN publishers p
  ON b.publisher_id = p.publisher_id
WHERE p.name = 'Classic Press'
ORDER BY b.book_id;
      

WHERE runs after the join. Rows where p.name is NULL are removed.

⚠ Books without a publisher are gone.
LEFT JOIN behaves like INNER JOIN.

• Filters in WHERE run after the join
• LEFT JOIN, WHERE on right table can erase NULL
• Put right side filters in ON for outer joins

• Keep all books, attach publisher when "Classic Press"
• Filter belongs in ON

What can happen?

• Mirror of LEFT JOIN
• Keep all rows from the right table
• Less common because LEFT JOIN works
• Traditional habit: prefer LEFT JOIN for readability

• Same result, clearer reading
• Place the preserved table on the left

• Keep all rows from both sides
• Match when possible
• Unmatched fields become NULL on the missing side
• Useful for reconciliation reports
• Note: supported in PostgreSQL

• Books w/o publishers and publishers w/o books
• Common for data quality audits

• Every row from A paired with every row from B
• Creates a Cartesian product
• Can explode row counts fast
• Useful for generating combinations
• Risk if run by accident

• This is usually not what you want for business queries
• Good for synthetic test data or matrix reports

• A book can have many authors
• An author can write many books
• Bridge table book_authors connects them
• Join through the bridge, not by guessing

• Join books to book_authors
• Join book_authors to authors
• Produces one row per (book, author)

• Many to many joins can duplicate book rows
• Then you group to summarize
• Example: count authors per book

After joining books to book_authors, book rows may appear multiple times because:

• Data quality check
• Left join to bridge and filter unmatched
• Traditional habit: check for missing relationships

• Anti join returns rows with no match
• Common as LEFT JOIN then IS NULL
• Also possible with NOT EXISTS

• Often clearer for complex keys
• Good habit: use EXISTS for yes or no questions

• Return left rows that have at least one match
• Does not duplicate left rows
• Useful when only need books, not author columns