🧠 Database Administration Learning Module

📘 What Is Database Administration?

Database Administration (DBA) involves managing and maintaining database systems to ensure:

• ✅ Data integrity

• 🚀 Performance

• 🔒 Security

• 📈 Availability

DBAs work with relational databases like MySQL, PostgreSQL, SQL Server, and Oracle.

---

🧰 Core DBA Responsibilities

Task Category	Description
Installation & Setup	Install and configure database software and environments
Backup & Recovery	Ensure regular backups and restore capabilities in case of failure
Security Management	Manage user roles, permissions, and encryption
Performance Tuning	Optimize queries, indexes, and system resources
Monitoring & Alerts	Track uptime, query performance, and system health
Data Integrity	Enforce constraints, validate data, and prevent corruption
Upgrades & Patching	Apply updates and patches to maintain security and compatibility

---

🧮 Essential SQL Commands for DBAs

Command	Category	Description	Example Syntax
SELECT	DML	Retrieve data from tables	SELECT * FROM users WHERE active = 1;
INSERT	DML	Add new records	INSERT INTO users (name, email) VALUES ('Alice', 'a@x.com');
UPDATE	DML	Modify existing records	UPDATE users SET active = 0 WHERE id = 5;
DELETE	DML	Remove records	DELETE FROM users WHERE id = 5;
CREATE TABLE	DDL	Define a new table structure	CREATE TABLE users (id INT, name TEXT);
ALTER TABLE	DDL	Modify table structure	ALTER TABLE users ADD COLUMN email TEXT;
DROP TABLE	DDL	Delete a table	DROP TABLE users;
CREATE INDEX	Performance	Speed up queries on specific columns	CREATE INDEX idx_name ON users(name);
GRANT	Security	Assign privileges to users	GRANT SELECT ON users TO analyst;
REVOKE	Security	Remove privileges	REVOKE SELECT ON users FROM analyst;
BACKUP DATABASE	Maintenance	Create a backup of the database (SQL Server)	BACKUP DATABASE [MyDB] TO DISK = 'C:\\backup.bak';
RESTORE DATABASE	Maintenance	Restore from backup	RESTORE DATABASE [MyDB] FROM DISK = 'C:\\backup.bak';
EXPLAIN	Performance	Analyze query execution plan	EXPLAIN SELECT * FROM users WHERE name = 'Alice';
CREATE USER	Security	Add a new database user	CREATE USER 'admin'@'localhost' IDENTIFIED BY 'pass';
SET TRANSACTION	Integrity	Control transaction behavior	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---

🧪 Suggested Practice Activities

1. Create a sample database with multiple tables and relationships.

2. Perform CRUD operations using SELECT, INSERT, UPDATE, and DELETE.

3. Simulate a backup and restore using SQL Server or PostgreSQL commands.

4. Create and revoke user privileges to understand access control.

5. Use EXPLAIN or QUERY PLAN to analyze performance bottlenecks.

6. Write scripts for automated monitoring (e.g., slow queries, disk usage).

---

📚 Recommended Resources

• Essential SQL commands for database management – Educative

• DBA’s Essential 100 SQL Commands – SQL DBA School

• [SQL Cheat Sheet for SQL Server DBA – MSSQLTips](https://www.mssqltips.com/sqlservertip/7616/sql-cheat-sheet

import ipywidgets as widgets
from IPython.display import display, Markdown, clear_output

### 📋 DBA Responsibility Descriptions
dba_tasks = {
    "Installation & Setup": "Install and configure database software, set up environments, and initialize schemas.",
    "Backup & Recovery": "Schedule backups, test restore procedures, and ensure disaster recovery readiness.",
    "Security Management": "Create users, assign roles, manage permissions, and enforce encryption policies.",
    "Performance Tuning": "Optimize queries, indexes, and resource allocation to improve speed and efficiency.",
    "Monitoring & Alerts": "Track uptime, query latency, disk usage, and set up alerts for anomalies.",
    "Data Integrity": "Enforce constraints, validate input, and prevent corruption or orphaned records.",
    "Upgrades & Patching": "Apply updates, hotfixes, and version upgrades to maintain security and compatibility."
}

### 📦 Dropdown widget
dropdown = widgets.Dropdown(
    options=list(dba_tasks.keys()),
    description='DBA Task:',
    style={'description_width': 'initial'}
)

### 📤 Output area
output = widgets.Output()

### 🔁 Callback function
def show_task(change):
    output.clear_output()
    task = change.new
    with output:
        display(Markdown(f"### 🧠 {task}\n{dba_tasks[task]}"))

dropdown.observe(show_task, names='value')

### 🚀 Display UI
display(dropdown, output)