Database Administration Project (PostgreSQL • MySQL • Datasette)

This project demonstrates end-to-end database administration (DBA) skills across three major database systems — PostgreSQL, MySQL, and Datasette.
It covers provisioning, configuration, restoration, indexing, storage engine management, automation, and performance optimization.

This repository is structured into 3 major parts, each aligned with real-world DBA responsibilities.

---

** Part 1 — PostgreSQL Administration**

This section covers:

Database provisioning

Role & User Management

Privilege Assignment

Backup of a database

Executing restoration scripts

Key Tasks:

• Created a database (tolldata)
• Restored schemas/tables using setup.sql
• Imported CSV data into PostgreSQL
• Created a user (backup_operator) and a role (backup)
• Granted privileges:
  • CONNECT on database
  • SELECT on all tables in schema
• Performed backup using pgAdmin, exported as tolldatabackup.tar

Files:

File	Description
postgres-setup.sh	Script to download data and bootstrap PostgreSQL
user-management.sql	Role, user creation, privilege grants
backup-commands.txt	Commands used to perform PostgreSQL backups
notes.md	Detailed explanations about Part 1 tasks

---

** Part 2 — MySQL Administration**

This section simulates a DBA recovering a MySQL server from a previous backup and optimizing performance.

Database restoration from SQL backup

Query performance measurement

Index creation

Storage engine inspection

Bash automation for database backups

Key Tasks:

• Restored billingdata.sql into a new billing database
• Calculated table data size using information_schema.TABLES
• Ran baseline performance tests
• Created an index on billedamount to optimize queries
• Verified supported storage engines (e.g., MyISAM, InnoDB)
• Wrote an automated backup script:
  • Dumps all databases
  • Creates dated folders under /tmp/mysqldumps/YYYYMMDD/
  • Moves the dump file inside the correct folder

Files:

File	Description
billingdata.sql	Dataset restored into MySQL
indexing.sql	SQL statements for performance optimization
storage-engines.sql	Statements to identify MySQL storage engines
table-size.sql	SQL to compute table size
mybackup.sh	Automated MySQL full-instance backup script
notes.md	Documentation and explanations

---

** Part 3 — SQLite & Datasette**

This part shifts focus to lightweight analysis tools using SQLite + Datasette, commonly used for analytics dashboards and static exploratory data tools.

Restoring CSV data into SQLite

Creating views

Performance testing using timestamps

Indexing for faster queries

Key Tasks:

• Imported billing.csv into SQLite (billing table)
• Verified number of rows imported
• Created a simplified view: basicbilldetails
• Ran a performance benchmark using timestamps
• Created an index on billedamount
• Re-tested performance after indexing

Files:

File	Description
billing.csv	Raw dataset
create-view.sql	SQL for basicbilldetails view
create-index.sql	Index to speed up exact-match queries
performance-test.sql	Timestamp-based performance test
notes.md	Documentation and explanations

---

** Skills Demonstrated**

This project showcases practical DBA skills:

PostgreSQL

• Role management
• Database security
• Importing structured/unstructured data
• Backup & recovery (pgAdmin / CLI)

MySQL

• SQL-based data recovery
• Indexing for performance tuning
• Table size evaluation
• Storage engine management
• Bash scripting & automation (mysqldump)

SQLite / Datasette

• CSV-to-table restoration
• Index creation
• Query profiling
• View creation

---

** Tools Used**

• PostgreSQL 13+
• MySQL Server 8+
• SQLite 3
• Datasette
• pgAdmin
• Bash scripting
• Linux CLI

---