John David N. Dionisio, PhD

Assignments

• Assignment 0914 Database fiddle tutorial Setup
  • DB Fiddle
  • DB Fiddle example
  • Super Basic SQL crib sheet
  • Super Basic Database Diagramming crib sheet
• Assignment 0928 File database mini-stack Setup
  • Finding Things on the command line—getting started with grep and wc (find is in there too, but won’t be as applicable for our purposes)
  • A tutorial for the legendary, beastly (in a good way) awk
• Assignment 1019 Relational database mini-stack Setup
  • “PostgreSQL setup day” guide
  • PostgreSQL home page
  • PostgreSQL documentation site: Come for the tutorial, stay for the deep-dive information on everything else
  • The E. F. Codd paper that (generally) started it all: A relational model of data for large shared data banks
• Assignment 1109 Document-centric database mini-stack Setup
  • “MongoDB/Elasticsearch setup day” guide
  • MongoDB home page
  • Elasticsearch home page
• Assignment 1123  extended to 1125  Graph database mini-stack Setup
  • “Neo4j setup day” guide
  • Neo4j home page
• Assignment 1216 Full database SDK Setup

Additional Content

• Relational schema design notes
• Database transactions
• Other database paradigms
• Book alert! Indexing structures for files and physical database design
  • The name’s B, B-tree: Balanced, not binary
  • SQL: CREATE INDEX
  • MongoDB: Indexes
  • Elasticsearch: That’s why the core storage unit is called an index! Instead, there are tips for tuning search speed and indexing speed
  • Neo4j: Indexes for search performance
• Notes on the theory of relations
  • Underlying structure
  • Core Albgebraic Operations
  • More Algebraic Operations Operations
  • Functional Dependencies and Normal Forms
  • Functional Dependency Theory

Dataset Sources

It’s hard to appreciate the power and potential of general database management systems when you don’t have seed data to populate it—so we’ll want to do our work with a database that can be fully populated from the get-go.

Our case study dataset is the one from the Netflix Prize competition from over a decade (a decade!!!) ago: https://www.kaggle.com/netflix-inc/netflix-prize-data—find one to call your own from one of these sites, or feel free to identify one independently:

• Kaggle datasets are generally positioned for data science or machine learning, but sometimes also applicable to pure database work:
• The Awesome Public Datasets collection is similar in purpose and applicability
• For the health-/medically-minded: the Drugs@FDA database file set is actually freely available