Database Systems

Spring 2024

Documents

• Syllabus and Schedule - revised January 14, 2024
• History and Advice
• About Laptops and Phones in Class
• Course Policies
• General Academic Calendar
• Academic Honesty
• School of Computer Science and Mathematics Department of Computing Technology Goals
• Writing Center
• Counseling Services
• Title IX at Marist
• Title IX Student Bill of Rights and IX Reporting Options
• Academic Learning Center for tutoring and more

Books

• Database Systems: The Complete Book, second edition
  • Authors' site
  • Amazon.com
• Architecture of a Database System (2007) by Joseph M. Hellerstein, Michael Stonebraker, and James Hamilton
• Database: Principles, Programming, and Performance by Patrick O'Neil and Elizabeth O'Neil
• The Internals of PostgreSQL

Readings

• 50 Years of Queries (2024) by Donald Chamberlin - CACM August 2024
• Readings In Database Systems - 5th edition (2015) edited by Peter Bailis, Joseph M. Hellerstein, and Michael Stonebraker
• Readings in Databases from Reynold Xin
• Class Readings from Jeong-Hyon Hwang's Database Class at U. Albany.
• A Brief History of Database Systems from the Very Early Days to Big Data Today (2016) - by the great Robert X. Cringley
  1. Early History
  2. Recent History
  3. Big Data

Labs

1. Setting up PostgreSQL
2. Loading our CAP database
3. Getting started with SQL queries
4. SQL Queries: the subqueries Sequel
5. SQL Queries: the joins Three-quel
6. Interesting and Painful SQL Queries
7. Normalization One
8. Normalization Two
9. Normalization Three
10. Stored Procedures - Here is the courses and prerequisites script.

Projects

• Text-to-SQL Project
• Relational Database Project

Past Project Hall of Fame

Resources

• The CAP database we'll use in class
• CAP database snapshot
• CAP SQL scripts
• Database Software
• PostgreSQL - The World's Best Free Database - Here's pgAdmin by itself (and its documentation) And here's PgHero a performance dashboard.
• DB2 Community Edition (free)
• MySQL (free, but not worth the price)
• SQL Server Express 2022
• Git
• GitHub Desktop
• A Gentle Introduction to Git and GitHub from Alan
• Git from the Bottom up by John Wiegley
• Git Basics
• Try Github
• GitHub Guides
• Advanced: Git From the Inside Out
• Visualizing Git Concepts
• How to Write a Git Commit Message - pretty good advice
• The Issues Feature on GitHub
• Dangers of GitHub and Amazon

Notes

• History and Types of Databases
• The Relational Model
• Entity-Relationship Modeling and Referential Integrity
• Indexes and Index Structures
• Locking, Transactions, and the WAL

SELECT course material
FROM this class
ORDER BY class

• Class 0
• Origins of the Data Base Management System - 2006 SIGMOD paper
• Relational Database Genealogy from https://hpi.de/naumann/projects/rdbms-genealogy.html
• Remembering Codd I: So Help Me Codd
• Remembering Codd II: A Tribute by Chris Date
• Databases - Early History by Robert X. Cringley
• Databases - Recent History by Robert X. Cringley
• Databases - Big Data by Robert X. Cringley
• History: The Gamma Database Machine
• History: IBM Relational Database Code Bases
• Distinguished Profiles in Databases presents Michael Stonebraker
• A one size fits all database doesn't fit anyone
• WIRED: Data Deluge
• Class 1
• A Brief History of SQL
• Relational Database Genealogy from https://hpi.de/naumann/projects/rdbms-genealogy.html
• An excellent case for artificial keys
• Our CAP database snapshot
• Our CAP database SQL scripts
• Can we do better than SQL? I'm not yet convinced.
• 50 Years of SQL My Codd! An interview with Donald Chamberlin and Michael Stonebraker
• Class 2
• E/R Diagram Example #1 - I like this format a lot.
• E/R Diagram Example #2 - I like this format a little.
• E/R Diagram Example #3 - This is a popular format I do not like at all.
• Referential Integrity: With and Without
• Students, Teachers, Subjects, Grades example
• Count Distinct with Subqueries
• Class 3
• A (very) Brief History of SQL
• Truth Tables for Three-valued Logic
• Alan's Stack Overflow discussion on NULLs and UNIQUE from 2011, and a brand new feature in PostgreSQL 15 from 2022 (See the "indexes" subsection.)
• What's the deal with NULLs? It's worth looking at again.
• NULLs examples
• Nulls, Three-Valued Logic, and Ambiguity in SQL: Critiquing Date’s Critique by Claude Rubinson
• Create Table example in Oracle
• SQL Server Check Constraints
• SQL Server Check Constraints Case Sensitivity or lack thereof
• Class 4
• People, Departments, Fiscal Years example
• Venn Diagrams DO NOT explain Joins So just STOP IT!
• Join Forms from PostgreSQL Internals 2012 by Bruce Momjian
• Understanding Hash Joins in Ms-SQL Server
• Set Operations in SQL
• Students, Teachers, Subjects, Grades example
• Oracle Database Examples - a variety of examples demonstrating how to use Oracle
• Class 5
• PostgreSQL System Catalogs
• PostgreSQL Observability - statistics and data about internal states
• A curated list of awesome PostgreSQL software, libraries, tools and resources
• View examples in CAP2 (an old version of the CAP database)
• View examples in Oracle
• View examples in Ms-SQL Server
• View Query Processing - query rewriting from Tom Lane's PostgreSQL Internals
• G* Introduction
• G* Technical Overview
• G* Studio
• Class 6 -- Mid-term Exam
• Class 7
• PostgreSQL B-tree Index Scan from PostgreSQL Internals 2012
• PostgreSQL Index Page Structure from PostgreSQL Internals 2012
• Why databases use ordered indexes but programming uses hash tables
• Modern B-Tree Techniques
• PostgreSQL B-tree Indexes
• Class 8 -- Spring Break
• Class 9
• Database Design Points to Ponder
• A 3NF Database with Transitive Dependencies or, How I learned to stop denormalizing and love BCNF
• Class A
• Decomposition to 3NF from my old HRPC project
• Lossless Join Example
• Class B
• Access Control with GRANT and REVOKE
• PostgreSQL documentation for GRANT
• PostgreSQL Security from IBM developerWorks
• Securing PostgreSQL from External Attack - by Bruce Momjian
• SQL Injection Security Threats from the Open Web Application Security Project
• More about SQL Injection from Troy Hunt
• Class C
• CAP2 city customers example - customers in a city (T-SQL)
• CAP2 city customers code - customers in a city (T-SQL)
• CAP2 city customers code - customers in a city (PL/pgSQL)
• Simple SQL-Server stored proc - get next in sequence (T-SQL)
• No-so-simple SQL-Server stored proc - logic and computation (T-SQL)
• Some PostgreSQL Stored Procedures (PL/pgSQL)
• A complex PostgreSQL Stored Procedure (PL/pgSQL)
• PostgreSQL Stored Procedures calling other Stored Procedures (PL/pgSQL)
• Two PostgreSQL Stored Procedures and a Trigger (PL/pgSQL)
• Triggers and the Log File - code (T-SQL)
• Triggers and the Log File - output (T-SQL)
• Class D
• Eight Transaction Papers by Jim Gray - Summaries of some of the amazing work of the amazing Jim Gray
• Postgres Two-Phase Commit - more Jepsen from Kyle Kingsbury
• Locks and Multi-Version Concurrency Control from PostgreSQL Internals 2012 by Bruce Momjian
• Testing CAP Scenarios for Various Data Stores with Jepsen by Kyle Kingsbury
• PostgreSQL Locking Revealed
• A Short Primer on Causal Consistency
• PostgreSQL Inner Workings
• Scaling Postgres with Read Replicas (and Using Write-Ahead Log to Counter Stale Reads)
• Deadlock Tracing in SQL-Server
• Transactions and Locking Isolation Levels in PostgreSQL
• Class E
• The CAP FAQ
• Eric Brewer: The CAP Theorem, Then and Now
• On CAP, ACID, and HAT - research on Highly Available Transactions from Peter Bailis
  • Part one, on ACID
  • Part two, HAT
  • Peter's Lightning Talk from RICON2012.
• Beating The CAP Theorem - an anti-checklist
• Causal Consistency Research by Wyatt Lloyd
• An Illustrated Proof of the CAP Theorem
• JEPSEN - Distributed Systems Safety Analysis
• Consistency, Serializability, and Linearizability from Jepsen, about VoltDB
• Testing the "I" in ACID
• Class F -- Review for the Final Exam

SELECT course material
FROM this class
GROUP BY topic

• Background and History
• Origins of the Data Base Management System - 2006 SIGMOD paper
• Remembering Codd I: So Help Me Codd
• Remembering Codd II: A Tribute by Chris Date
• Databases - Early History by Robert X. Cringley
• Databases - Recent History by Robert X. Cringley
• Databases - Big Data by Robert X. Cringley
• Relational Database Genealogy from https://hpi.de/naumann/projects/rdbms-genealogy.html
• History: The Gamma Database Machine
• History: IBM Relational Database Code Bases
• Distinguished Profiles in Databases presents Michael Stonebraker
• A one size fits all database doesn't fit anyone
• WIRED: Data Deluge
• How Relational Databases Work from Coding-Geek.com
• Chapter 4 of Architecture of a Database System by Joseph M. Hellerstein, Michael Stonebraker, and James Hamilton
• SQL
• A Brief History of SQL
• Can we do better than SQL? I'm not yet convinced.
• Nulls, Three-Valued Logic, and Ambiguity in SQL: Critiquing Date’s Critique by Claude Rubinson
• 50 Years of SQL My Codd! An interview with Donald Chamberlin and Michael Stonebraker
• SQL: NULLs
• Truth Tables for Three-valued Logic
• Alan's Stack Overflow discussion on NULLs and UNIQUE
• What's the deal with NULLs? It's worth looking at again.
• NULLs examples
• SQL: Joins
• Venn Diagrams DO NOT explain Joins So just STOP IT!
• Join Forms from PostgreSQL Internals 2012 by Bruce Momjian
• Understanding Hash Joins in Ms-SQL Server
• SQL: Misc. Examples
• Create Table example in Oracle
• Set Operations in SQL
• SQL Server Check Constraints
• SQL Server Check Constraints Case Sensitivity or lack thereof
• Students, Teachers, Subjects, Grades example
• People, Departments, Fiscal Years example
• Count Distinct with Subqueries
• Oracle Database Examples - a variety of examples demonstrating how to use Oracle
• SqlPad- Run SQL in your browser
• Views
• View examples in CAP2(an old version of the CAP database)
• View examples in Oracle
• View examples in Ms-SQL Server
• View Query Processing - query rewriting from Tom Lane's PostgreSQL Internals
• Indexes
• PostgreSQL B-tree Index Scan from PostgreSQL Internals 2012
• PostgreSQL Index Page Structure from PostgreSQL Internals 2012
• Why databases use ordered indexes but programming uses hash tables
• Modern B-Tree Techniques
• PostgreSQL B-tree Indexes
• E/R Diagrams
• E/R Diagram Example #1 - I like this format a lot.
• E/R Diagram Example #2 - I like this format a little.
• E/R Diagram Example #3 - This is a popular format I do not like at all.
• Database Design
• An excellent case for artificial keys
• A 3NF Database with Transitive Dependencies or, How I learned to stop denormalizing and love BCNF
• Database Design Points to Ponder
• Decomposition to 3NF from my old HRPC project
• Lossless Join Example
• Referential Integrity: With and Without
• Authorization, and Security
• Access Control with GRANT and REVOKE
• PostgreSQL documentation for GRANT
• PostgreSQL Security from IBM developerWorks
• Securing PostgreSQL from External Attack - by Bruce Momjian
• SQL Injection Security Threats from the Open Web Application Security Project
• More about SQL Injection from Troy Hunt
• Administration
• PostgreSQL Database Administration book - Free!
• Mastering PostgreSQL Administration - by Bruce Momjian
• Don't Do These Things in PostgreSQL
• Monitoring PostgreSQL
• A curated list of awesome PostgreSQL software, libraries, tools and resources
• Troubleshooting SQL Server: A Guide for Accidental DBAs
• SQL Server Best Practices - Part I: Configuration
• SQL Server Best Practices Part II: Virtualized Environments
• Big Data Governance
• New Jersey Nearly Sold Secret Data
• OpenRefine - a tool for dealing with messy data
• SQL Tricks of an Application DBA by Haki Benita
• A good article on sharding from Stack Overflow
• Stored Procedures and Triggers
• CAP2 city customers example - customers in a city (T-SQL)
• CAP2 city customers code - customers in a city (T-SQL)
• CAP2 city customers code - customers in a city (PL/pgSQL)
• Simple SQL-Server stored proc - get next in sequence (T-SQL)
• No-so-simple SQL-Server stored proc - logic and computation (T-SQL)
• Some PostgreSQL Stored Procedures (PL/pgSQL)
• A complex PostgreSQL Stored Procedure (PL/pgSQL)
• PostgreSQL Stored Procedures calling other Stored Procedures (PL/pgSQL)
• Two PostgreSQL Stored Procedures and a Trigger (PL/pgSQL)
• Triggers and the Log File - code (T-SQL)
• Triggers and the Log File - output (T-SQL)
• Query Processing
• The PostgreSQL Query Optimizer by Bruce Momjian
• Reading a Postgres Explain-Analyze Query Plan
• A PostgreSQL Query Plan Visualizer
• PostgreSQL's explain analyze made readable
• The Unofficial MySQL 8.0 Optimizer Guide
• The SQLite Query Planner
• Text Search
• Article about PostgreSQL full-text search
• PostgreSQL Full Text Search Documentation
• Time Series Data
• Timescale - a time-series database built on PostgreSQL
• Natural Language to SQL (NL2SQL)
• Natural Language to SQL: Where are we today?
• TypeSQL: Knowledge-based Type-Aware Neural Text-to-SQL Generation
• Relation-aware Schema Encoding for NL2SQL
• Relation-aware Parsing for NL2SQL
• WikiSQL
• CAP: Consistent, Available, and Partitioned
• The CAP FAQ
• Eric Brewer: The CAP Theorem, Then and Now
• On CAP, ACID, and HAT - research on Highly Available Transactions from Peter Bailis
  • Part one, on ACID
  • Part two, HAT
  • Peter's Lightning Talk from RICON2012.
• Beating The CAP Theorem - an anti-checklist
• Causal Consistency Research by Wyatt Lloyd
• An Illustrated Proof of the CAP Theorem
• JEPSEN - Distributed Systems Safety Analysis
• Consistency, Deadlock, and ACID
• Eight Transaction Papers by Jim Gray - Summaries of some of the amazing work of the amazing Jim Gray
• Postgres Two-Phase Commit - more Jepsen from Kyle Kingsbury
• Locks and Multi-Version Concurrency Control from PostgreSQL Internals 2012 by Bruce Momjian
• Testing CAP Scenarios for Various Data Stores with Jepsen by Kyle Kingsbury
• Consistency, Serializability, and Linearizability from Jepsen, about VoltDB
• Testing the "I" in ACID
• PostgreSQL Locking Revealed
• A Short Primer on Causal Consistency
• PostgreSQL Inner Workings
• Scaling Postgres with Read Replicas (and Using Write-Ahead Log to Counter Stale Reads)
• Deadlock Tracing in SQL-Server
• Transactions and Locking Isolation Levels in PostgreSQL
• CALM - Consistency As Logical Monotonicity
• Keeping CALM: When Distributed Consistency Is Easy
• SQL Implementations
• SQL Implementation Comparisons
• Why Postgres part 1 and Why Postgres part 2
• PostgreSQL Internals from Bruce Momjian
• PostgreSQL Internals from Hironobu Suzuki
• PostgreSQL Physical Storage Details here and here.
• What PostgreSQL has over other open source SQL databases part one and part two
• PostgreSQL System Catalogs
• PostgreSQL Observability - statistics and data about internal states
• NoSQL data holes and the garbage that is MongoDB and MySQL
• MySQL is still garbage 2021
• Another area where MongoDB is terrible 2021
• MongoDB - STILL broken by design 2015
• MongoDB - broken by design 2013
• MongoDB - for when your data is worthless 2013
• Why You Should Never Use MongoDB unless your data is meaningless. 2013
• MongoDB - Marketing, Lies, and more lies 2013
• Dubious MongoDB 2013
• NoSQL Design(?) tips 2012
• Stonebraker on NoSQL and enterprises 2011
• SQL databases v. NoSQL databases 2010
• MongoDB -now powered by PostgreSQL
• MySQL is not ACID compliant
• MongoDB is web scale
• Did you make the right database choice?
• Uber and Postgres - An Interesting discussion
  1. Why Uber Engineering Switched from Postgres to MySQL
  2. From the pgsql-hackers list
  3. On Uber’s Choice of Databases
  4. Thoughts on Uber’s List of Postgres Limitations
• Other
• Graph vs Relational - from The Register
• MapReduce and parallel DBMSs: friends or foes? 2010
• The Relational Model is Dead Or is it?
• SQL Server 2014's in-memory transaction engine
• SQL Server High-performance Inserts
• Famous (and old) Funny: If Architects Had to Work Like Systems Designers
• alanBASE ][
• Seven Databases Song

Database Haiku

• These are from Hugh Scott at SQL Server Central
• • Server is lifeless
    The customer is distraught
    Where is the backup?
  • Performance is slow
    All the disk drives are busy
    Where was the where clause?
  • The server is built
    Everything is now installed
    What was the password?
• These are from former students. Got one?
• • I did the homework
    As difficult as it was
    Do I get an A?