Database Administration: Design, Performance, and Recovery

• What SQL Does: Databases, Tables, Rows, and Columns

  Identify how SQL works with databases, tables, rows, and columns.

• Install PostgreSQL and Connect with pgAdmin

  Install PostgreSQL and pgAdmin, then connect to a local database server.

• Create a Database and Table

  Create a PostgreSQL database and a table with appropriately chosen column types.

• Read Table Data with SELECT

  Retrieve all rows from a table using a SELECT statement.

• Choose Columns and Add Aliases

  Write a SELECT statement that returns chosen columns with clear aliases.

• Filter Rows with WHERE

  Filter table rows using comparison conditions in a WHERE clause.

• Combine Filters with AND and OR

  Retrieve rows that match multiple conditions using AND and OR.

• Sort and Limit Query Results

  Sort query results with ORDER BY and restrict their number with LIMIT.

• Project: Build a Searchable Inventory List

  Build an inventory lookup by loading provided starter data and writing queries to find, filter, and sort items.

• Handle Missing Values with NULL

  Find rows with missing values using IS NULL and IS NOT NULL.

• Summarize Data with Aggregate Functions

  Calculate row counts and numeric summaries using COUNT, SUM, AVG, MIN, and MAX.

• Group and Filter Summaries

  Group rows with GROUP BY and filter grouped results with HAVING.

• Combine Related Tables with INNER JOIN

  Retrieve related information from two tables using an INNER JOIN.

• Add Rows with INSERT

  Add valid rows to a table using INSERT.

• Change Existing Rows with UPDATE

  Update selected rows safely by applying a WHERE condition.

• Remove Rows with DELETE

  Delete selected rows using a WHERE condition and verify which rows remain.

• Protect Changes with Transactions

  Apply a database change and either save it with COMMIT or undo it with ROLLBACK.

• Capstone Project: Build a SQL-Based Store Report

  Build a PostgreSQL store report that creates tables, adds and updates data, joins related records, and answers business questions with queries.

Explore relational database concepts, relationships, constraints, and data types, then use these foundations to model application requirements. You’ll be able to turn a set of requirements into a clear, consistent relational schema.

• Set Up a PostgreSQL Schema Design Workspace

  Configure PostgreSQL, a SQL client, and an ERD tool for designing and validating relational schemas.

• Translate Application Requirements into Entities and Relationships

  Map a written application brief to candidate entities, attributes, and relationships in a conceptual model.

• Define Keys and Referential Integrity

  Specify primary keys, candidate keys, and foreign-key rules for a relational model.

• Model Many-to-Many and Time-Dependent Relationships

  Represent many-to-many and historical relationships using appropriate associative and temporal patterns.

• Normalize Transactional Schemas Through Third Normal Form

  Decompose a sample schema into third normal form while preserving its required relationships.

• Choose PostgreSQL Data Types and Null Semantics

  Assign PostgreSQL data types and nullability rules to application attributes based on their meaning and constraints.

• Encode Business Rules with Database Constraints

  Implement domain, uniqueness, check, default, and referential constraints in PostgreSQL DDL.

• Build and Evolve a Schema with Versioned Migrations

  Create a versioned migration that builds a relational schema and safely applies a subsequent change.

• Capstone Project: Design and Implement an Application Schema

  Build a PostgreSQL schema from an application brief, delivering an ERD, normalized DDL, constraints, and versioned migrations.

Develop SQL skills for querying and changing data, including joins, views, and, in MySQL, stored procedures and triggers. Apply normalisation and indexing principles to create maintainable schemas and understand how structure affects query behavior.

• Configure a MySQL Development Environment

  Configure a MySQL 8 instance, MySQL Workbench, and command-line client for repeatable database development.

• Normalize Data into Maintainable Relational Schemas

  Transform a denormalized business dataset into a relational schema that satisfies appropriate normal forms and preserves key dependencies.

• Implement Schemas with MySQL DDL and Constraints

  Create normalized MySQL tables with primary keys, foreign keys, unique constraints, and check constraints.

• Manage Data Changes with Transactions

  Execute multi-statement data changes using transactions and rollback strategies that preserve consistency.

• Query Related Data with Joins and Advanced SQL

  Compose queries using joins, common table expressions, subqueries, and window functions to answer business questions.

• Build Reusable Views for Reporting and Access

  Create and validate MySQL views that simplify reporting queries and expose only the required data.

• Diagnose Query Plans and Design Indexes

  Use EXPLAIN and representative queries to select indexes that improve performance without unnecessary write overhead.

• Automate Database Logic with Procedures and Triggers

  Implement a MySQL stored procedure and trigger that enforce a defined business workflow and data-integrity rule.

• Evolve Schemas with Versioned Migrations

  Write and apply versioned migration scripts that change a schema safely while preserving existing data.

• Capstone Project: Build and Optimize an Order Management Database

  Deliver a normalized, migrated MySQL order-management database with transactional operations, reporting views, automated logic, and measured query improvements.

Practice administration workflows for MySQL and SQLite, including backup planning, restore validation, disaster recovery, and query optimisation. You’ll be able to investigate performance issues and provision managed MySQL databases using Amazon RDS or Azure Database for MySQL.

• Set Up the MySQL, SQLite, and Administration Tooling Lab

  Configure a reproducible lab with MySQL, SQLite, command-line clients, and database administration tools.

• Establish Operational Baselines and Monitoring

  Record configuration, storage, connection, and workload baselines for a MySQL and SQLite environment.

• Design and Automate Database Backup Plans

  Implement scheduled MySQL and SQLite backups with documented retention and storage policies.

• Validate Restores and Measure Recovery Objectives

  Restore MySQL and SQLite backups into isolated environments and measure recovery time and data loss.

• Build Disaster Recovery Runbooks

  Create a recovery runbook covering MySQL point-in-time recovery and SQLite WAL-based recovery.

• Investigate Query Performance with Production-Style Diagnostics

  Diagnose a slow workload using MySQL slow query logs, EXPLAIN plans, and SQLite query plans.

• Optimize Queries and Indexes Through Benchmarking

  Improve a measured workload by revising SQL and indexes, then compare its before-and-after performance.

• Provision and Secure Managed MySQL on AWS or Azure

  Provision a managed MySQL instance on Amazon RDS or Azure Database for MySQL with secure connectivity and backups.

• Operate Managed MySQL for Availability and Performance

  Configure and verify managed-service monitoring, maintenance, and availability controls on the provisioned instance.

• Project: Deliver a Recoverable, Performance-Tuned Database Service

  Deliver a documented MySQL service with automated backups, a validated recovery procedure, and benchmarked query improvements.

Bring together modeling, SQL, deployment, backup and recovery, and performance tuning in a realistic database project. You’ll deliver and justify an operational plan for a database-backed application, including a tested recovery approach and performance improvements.

• Provision a PostgreSQL Administration Workspace

  Configure a reproducible PostgreSQL environment with Docker Compose, Git, and database client tools.

• Translate Application Requirements into Database Service Objectives

  Define measurable availability, recovery, security, and performance objectives for a database-backed application.

• Model Transactional Data for Integrity and Operational Growth

  Design a normalized PostgreSQL schema that satisfies application workflows, integrity constraints, and expected growth.

• Implement Versioned Schema Changes and Test Data

  Create and validate repeatable PostgreSQL migrations and representative seed data using Flyway.

• Deploy PostgreSQL with Least-Privilege Access

  Deploy the application database with role-based permissions, protected credentials, and documented environment settings.

• Establish Database Monitoring and Workload Baselines

  Capture query, connection, and resource baselines using PostgreSQL statistics and monitoring dashboards.

• Build and Verify a Point-in-Time Recovery Procedure

  Restore the database to a specified recovery point using tested backups and write-ahead log archiving.

• Diagnose and Improve Application Query Performance

  Use EXPLAIN ANALYZE and workload measurements to implement and verify targeted query and index improvements.

• Document Operational Controls and Incident Runbooks

  Produce actionable runbooks for deployment, monitoring, access reviews, backup validation, and recovery incidents.

• Capstone Project: Deliver an Operational Database Service

  Build and present a deployed PostgreSQL service with versioned schema, measured performance improvements, and a demonstrated recovery plan.