Database Management Systems
Class Outline and Teaching Materials

Part I: sqlite and mysql sample databases

  1. Question: managing contacts / classroom assignment / university registration system / library
  2. Essential SQL Terms: A / B /
  3. SQLite Tutorial
  4. LiteCLI : color themes, the less pager, GNU readline key cheat sheet
  5. about quoting: A, B, C
  6. regexp for sqlite3
  7. mysql setup + DBeaver
  8. The Barrier to Exit - Scott McNealy on the #1 Reason for Choosing Open Source Software
  9. Embrace, Extend and Extinguish
  10. Export SQLite Database To a CSV File
  11. Transactions:
  12. DDL/DML/DQL/DCL/TCL

Part II: Theoretical Concepts in Relational Databases

  1. basic math terminology: terms about sets; Cartesian products; binary relations
  2. Relational Algebra
  3. The Entity-Relationship Model
  4. Crow's Foot Notation: Vertabelo, Regina
  5. compound key vs composite key
  6. integrity constraints 1 / integrity constraints 2 : referencial integrity and cascading actions of foreign keys; why omit foreign keys?
  7. mysql indexing
  8. Conceptual Modeling
  9. SQL
  10. join: w3schools, stackoverflow, picture, multiple join, practice: dofactory (join on non-unique columns)
  11. Why normal forms? example: students and professors,
  12. functional dependency: OpenTextBC
  13. reading functional dependency from an ER diagram
  14. 3 normal forms: example
  15. multivalued dependency: simplified explanation, original paper
  16. Normalization: vertabelo, Gordon, Colostate, Qvarnström
  17. Difference between 3NF and BCNF: A, B, C
  18. Relational Database Design Rules of Thumb: Grayson / MIT

Part III: Other Kinds of Databases

  1. Relational DB vs ORDB: differences / differences / Gordon
  2. NoSQL Tutorial
  3. Cassandra Data Modelling - Tables
  4. Cassandra Data Modelling - Primary Keys

Books and Lecture Notes

  1. Greg's Supplementary Notes
  2. SQLite Tutorial
  3. Db2 SQL Cookbook
  4. Gordon College 2019 / Gordon College 2015
  5. George Washington Univ. 2020
  6. unibz 2010
  7. Open textbook: Database Design by Adrienne Watt

Tools

  1. Database Tools Catalog (some are outdated)
  2. sqlectron
  3. omnidb
  4. dbeaver: ER Diagrams
  5. 5 Open-Source SQL IDEs for You to Learn and Explore

Sample Databases

  1. chinook record company (sqlite)
  2. Sakila DVD rental (mysql)
  3. airport (mysql)
  4. employees (mysql)
  5. classic cars (mysql)
  6. School Database (mysql)
  7. bike store (MS)
  8. database schema design exercise: CD catalog
  9. database schema design exercise: library
  10. database schema design exercises: manufacturer ; car dealership
  11. many database schema design exercises

(back to course homepage)