Database Management Systems
Class Outline and Teaching Materials

sqlite and mysql sample databases

  1. Question: managing contacts / classroom-hour-arrangement / 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. regular expressions, regexp for sqlite3
  7. Generating ER Diagrams directly from *.sql files without even installing mysql
  8. mysql setup + DBeaver
  9. The Barrier to Exit - Scott McNealy on the #1 Reason for Choosing Open Source Software
  10. Embrace, Extend and Extinguish
  11. Export SQLite Database To a CSV File
  12. Transactions:
  13. DDL/DML/DQL/DCL/TCL

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. DBMS keys, compound key vs composite key
  6. integrity constraints 1 / integrity constraints 2 : referencial integrity and cascading actions of foreign keys; when to omit foreign keys?
  7. mysql indexing
  8. Conceptual Modeling
  9. SQL
  10. join: w3schools, left/right/inner/outer join, picture, cross join vs full outer join, multiple join, practice: dofactory (join on non-unique columns)
  11. Why normal forms? example: students and professors,
  12. functional dependency: jvp / OpenTextBC / g4g: def, types
  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, "Nothing but the key"
  17. Difference between 3NF and BCNF: A, B, C
  18. Relational Database Design Rules of Thumb: Grayson / MIT

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); sakila for sqlite/postgresql/...
  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
  12. Collection: SQL Server Sample Databases

(back to course homepage)