smattingly/database-tutorial

Databases in Context: A Just-in-Time Tutorial. CC-BY-SA 4.0

Databases in Context: A Just-in-Time Tutorial

Steve Mattingly, Assistant Professor of Computer Science, Davis & Elkins College.

Databases in Context: A Just-in-Time Tutorial by Stephen S. Mattingly is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.

This is a work in progress.

database-tutorial's Issues

NoSQL sections

Advanced constraints, triggers

Move nondefault referential integrity actions here.

Joins

• natural joins

• outer joins

• self-joins (use staff)

Appendix: setting up work environment

Referenced in "Exploring a MySQL installation"

Cardinality examples

• illustrate 1:m with location:staff
• illustrate m:m in 1nf section using student_major; leave student_sport as exercise

Cover help command and online docs

Use ENUMs

• Use ENUM for domain of {Yes, No, Unknown}

• Possibly for pseudo-Boolean staff.slp_instructor

• Others?

Commit sections

Break monolithic pre-git file into one file per section, and commit them.

ALTER TABLE

In 2nf section, cover alter table to replace visit with student in fk constraints

Verify referential integrity behavior

Make sure description is accurate.

Enable strict NULL handling

Must set sql_mode = 'STRICT_ALL_TABLES'; How/where for student workspaces?

LaTeX

Some set notation still needs LaTeX for formatting.

MySQL datatypes

Combine coverage of 3NF, BCNF

Use staff's location determinant as BCNF violation example

Build process

Implement a build/make process. See backbone-fundamentals for examples.
Should support include/exclude exercises to support pseudo-flipped classroom.

Aggregate functions

Lack of numeric fields may be a problem.

More exercises throughout