This project demonstrates key relational database administration tasks using PostgreSQL, MySQL, and Datasette (SQLite-based). It simulates real-world DBA responsibilities including user and role management, backups, data restoration, indexing, storage engine inspection, and performance optimization. It was completed as the final project for Course 7 – Relational Database Administration (DBA) in the IBM Data Engineering Professional Certificate.
This project was designed to apply hands-on skills in relational database administration across multiple platforms. The core objectives include:
- User and Role Management: Create and manage users, roles, and privileges in PostgreSQL.
- Database Backups and Restoration:
- Restore MySQL databases from .sql dumps.
- Perform backups using both GUI (pgAdmin) and shell scripts (pg_dump, mysqldump).
- Data Import and Validation: Load CSV data into SQLite (Datasette) and validate successful import through SQL queries.
- Indexing and Optimization: Create indexes to optimize query performance and compare execution times before and after.
- Storage Engine Inspection: Identify and inspect storage engines supported by MySQL and determine the engine in use for specific tables.
- Automation with Bash: Automate MySQL and PostgreSQL backup routines using scripts that include timestamped directory creation.
- Performance Benchmarking: Log timestamps around query execution to evaluate the impact of indexing in both MySQL and SQLite environments.
- PostgreSQL – User/role management, permission grants, backup via CLI and pgAdmin
- MySQL – Data recovery, indexing, table inspection, automation via Bash
- Datasette – SQLite-based querying, indexing, view creation
- SQL – Administrative queries, data manipulation, optimization
- Bash – Backup automation scripts for PostgreSQL and MySQL
- pgAdmin / CLI / Datasette UI – Database interfaces used
- Linux Environment – Script execution, file management
- Checked system settings using SQL (max_connections)
- Created users and roles with privilege management
- Granted role-based access to schema and database
- Performed database backup:
- via pgAdmin (.tar format)
- via Bash script using pg_dump
- Restored database using provided SQL dump (billingdata.sql)
- Verified table structure and data size using information_schema
- Optimized queries with indexing on billedamount
- Measured performance before and after indexing
- Inspected supported storage engines and table engine types
- Wrote Bash script (mybackup.sh) to automate full-database backups with timestamped folders
- Restored billing.csv via Datasette UI
- Verified row count using SQL
- Created a view basicbilldetails with selected columns
- Indexed the billedamount column to improve performance
- Measured query performance using strftime() before/after indexing
- All SQL and Bash files are documented with inline comments and expected outputs where relevant.
- Screenshots are included only when visually demonstrating performance, backup dialogs, or interface-based tasks that cannot be shown in code.
DBA_PostgreSQL_MySQL_Datasette_Project/
├── README.md # Project overview and structure (this file)
├── data/ # Provided input data files
│ ├── billing.csv # CSV file used to restore Datasette billing table
│ ├── billingdata.sql # SQL dump used to restore MySQL billing database
│ └── vehicle-data.csv # Dataset used by setup.sql to populate PostgreSQL tables
├── images/ # Screenshots of selected command outputs
│ ├── create_users_roles_output.png # CREATE USER, ROLE, GRANT results (PostgreSQL)
│ ├── database_restore.png # Table list after MySQL DB restoration
│ ├── query_performance_baseline.png # Query runtime before indexing (Datasette)
│ ├── query_performance_indexed.png # Query runtime after indexing (Datasette)
│ ├── query_optimization_indexing.jpg # Side-by-side performance comparison (MySQL)
│ ├── storage_engine_type.png # Output of table storage engine check (MySQL)
│ ├── storage_engines.png # Output of supported MySQL engines (MySQL)
│ └── tolldatabackup_pgadmin.png # PostgreSQL backup GUI window (pgAdmin)
├── postgresql/ # PostgreSQL administrative tasks
│ ├── create_users_roles.sql # SQL to create users, roles, permissions
│ ├── postgres-setup.sh # Provided shell script for PostgreSQL setup
│ ├── postgres_backup.sh # Bash script to back up tolldata DB via pg_dump
│ ├── setup.sql # Provided SQL script to create and populate a table
│ └── show_max_connections.sql # SQL to show max_connections setting
├── mysql/ # MySQL tasks: backup, recovery, optimization
│ ├── mybackup.sh # Bash script for full MySQL dump into dated folder
│ ├── query_performance_indexing.sql # Query + CREATE INDEX + re-test query
│ ├── show_billing_tables.sql # SQL to list restored tables in billing DB
│ ├── show_storage_engines.sql # SQL to list supported storage engines
│ ├── show_table_engine.sql # SQL to display storage engine of billdata
│ └── table_size_query.sql # SQL to compute table size in MB
└── datasette/ # Datasette (SQLite) tasks
├── create_basicbilldetails_view.sql # SQL to create view with 3 selected columns
├── create_index_billedamount.sql # CREATE INDEX on billedamount
├── query_performance_indexed.sql # Query performance after indexing
├── query_performance_indexing.sql # Query + index + comparison
└── validate_restored_table.sql # SQL to verify billing.csv import by row count
This project was completed as part of the IBM Data Engineering Professional Certificate and is intended for educational use.