Map the keys and relationships in the practice database

short · 35 min · Objective 1.1

Task

Open the course's SQLite database, record its tables and their definitions, identify the primary and foreign keys, and prove that every order refers to a customer who exists. The result is a small map of the database that the rest of the course's SQL relies on.

Steps

  1. Using Python's sqlite3 module, run SELECT sql FROM sqlite_master WHERE type = 'table' against data/shop.db and save every statement it returns to lab/db/schema.txt.
  2. From the schema, identify each table's primary key and each foreign key.
  3. Record every foreign key relationship in lab/db/relationships.csv with header from_table,from_column,to_table,to_column,cardinality, where cardinality is one-to-many or many-to-many, written from the side of the table the key points to.
  4. Count the orders whose customer_id does not appear in the customers table, and the orders whose product_id does not appear in the products table.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -c 'CREATE TABLE' lab/db/schema.txt
awk -F, 'NR>1 {print $1"."$2" -> "$3"."$4" ("$5")"}' lab/db/relationships.csv
python3 -c "import sqlite3; c=sqlite3.connect('data/shop.db'); print(c.execute('SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM customers)').fetchone()[0], 'orphaned order(s)')"

Three tables, two relationships -- orders to customers and orders to products, each one-to-many -- and zero orphaned orders. A foreign key that the data honours is what makes a join trustworthy; the joins lesson shows what happens when one does not.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.