ITMO DB lesson 5

No jumps between slides yet: add a click action "Slide" to a block. Links to the previous or the next slide are not shown.

Lesson 5. Database Administration and Physical Implementation1
Lesson Plan2
Glossary 1/2: Physical Schema, DDL, and Integrity3
Glossary 2/2: Clients, Access Rights, and Backups4
From a diagram in dbdiagram.io to physical DDL5
Surrogate Keys and Choosing Physical Types in PostgreSQL6
Integrity Constraints and Composite Foreign Keys7
Delete Policy: ON DELETE RESTRICT, SET NULL, and CASCADE8
Procedure for Creating Tables and Auto-Initialization in Docker9
The Evolution of the Schema: The Original `init.sql` vs. Migrations10
An Engineer's Tools: When to Use the CLI and When to Use the GUI11
Inspecting the live database in psql: metacommands and strict mode12
Reverse ER Diagram in DBeaver: A Comparison of Ideal and Reality13
Secure Connection: Why You Shouldn't Open 0.0.0.0:543214
Why the service should not access the database under the "postgres" role15
The three roles of a healthy project: owner, app, and readonly16
Configuring Permissions in SQL and Protecting Purchase Logs17
Views (VIEW) as a Showcase and Security Boundary18
Why a Docker volume and a replica are NOT a backup19
pg_dump formats: text-based SQL (-Fp) and compressed archive (-Fc)20
The DBA's Golden Rule: Verifying a Restore Using pg_restore21
Bulk Data Transfer: Server-Side COPY and Client-Side \copy22
Common Mistakes in Scheme Implementation and Administration23
What to Take Away from the Class and What We'll Do in Practice 524
Review Questions for Lesson 525
P1. Review of the physical schema and ON DELETE policies>_26
P2. Checking named CHECK rules in the system catalog>_27
A3. Testing DB protection: CHECK and composite FK>_28
P4. Testing ON DELETE RESTRICT and SET NULL in a live environment>_29
P5. RBAC Permission Matrix and Purchase Log Protection (Append-Only)>_30
P6. VIEW Analytics Dashboard and Report on Purchases and Matches>_31