SUMMARY OF LESSON 5 · MOVING ON TO PRACTICAL APPLICATION
What to Take Away from the Class and What We'll Do in Practice 5
Use this presentation and the Lesson 5/dba/ stand as a guide when deploying your team's own database.
Architect and DBA Checklist
Assignment for Lab 5 (10 points, by teams)
• Physical DDL is stricter than the diagram: we use GENERATED ALWAYS AS IDENTITY, timestamptz, named CHECK constraints, and composite foreign keys.
• Carefully chosen ON DELETE behavior: RESTRICT for money and checks, SET NULL for optional groups, CASCADE for internal match details.
• Visual and console verification: \d+ and \dp in psql + reverse ER diagram in DBeaver.
• Three RBAC roles: owner (DDL), app (DML + append-only on checks), and readonly (SELECT and VIEW).
• Backups are verified only after restoration: pg_dump -Fc → pg_restore into a clean test database.
Set up a local relational database for your game project (from Exercise 2):
1. Docker + DDL + Seed (3 points): Start PostgreSQL on 127.0.0.1, populate all tables and relationships in the game/app schema, and fill them with seed data.
2. Visualization and verification (2 points): Connect DBeaver, take a screenshot of the reverse ER diagram, and verify that CHECK and FK constraints are working.
3. RBAC Roles and Permissions (2.5 points): Configure the owner, app, and readonly roles; prevent DELETE operations on the log table; demonstrate a “permission denied” error.
4. Backup and CSV (2.5 points): Run `pg_dump -Fc`, restore to `restore_check`, and export a report using `\copy`.
Next, on the following slides (S1–S6): an interactive PostgreSQL demo right in your browser—click "Run" and test the queries in real time!