Database Administration and Management

• Course Title: Database Administration and Management
• Credit Hours: 03
• Pre-requisite: Database Systems

Acknowledgment:

• Parts of this content have been generated or refined using ChatGPT (by OpenAI) to support clarity, structure, and accessibility.
• All material has been reviewed and curated by the author.
• Learners are encouraged to actively engage with the content—verify facts, explore alternative perspectives, and deepen their understanding through further research.
• Curiosity and critical thinking are essential companions to any tool or source.

Course contents:

• Overview and Advanced Data Models
  • Basic relational models revisited
  • Object-Relational Databases
  • Object-Oriented Databases
  • Lab 1: installation of WSL in windows and setting up mysql and postgreSQL
  • Lab 2: Data Models mysql JSON vs PostgreSQL UDTs
  • Lab 3: Object Relational Data Modeling
• File Organization Concepts
  • Heap, Sorted, and Hashed files
  • Indexing (B+ Trees, Hash Indexes)
  • Cost considerations in access
• Transaction Processing and Concurrency Control
  • ACID properties
  • Serializability
  • Lock-based protocols, Timestamps, Deadlocks
• Recovery Techniques
  • Log-based recovery
  • Checkpoints
  • Shadow paging
• Query Processing and Optimization
  • Parsing, translation, and optimization
  • Heuristics and cost-based optimization
  • Join algorithms and access paths
• Database Programming
  • Embedded SQL (e.g., SQL with C/C++)
  • Stored procedures and triggers
  • Using APIs (like JDBC/ODBC or Python’s DB-API)
• Data Integrity and Security
  • Constraints (PK, FK, domain)
  • Authorization and authentication
  • SQL injection and mitigation
  • Encryption techniques
• Database Administration
  • Roles and responsibilities
  • User management
  • Backup and recovery planning
  • Monitoring and auditing
• Physical Database Design and Tuning
  • Storage planning
  • Indexing strategies
  • Query tuning with EXPLAIN plans
  • Partitioning, denormalization for performance
• Distributed Database Systems
  • Fragmentation and replication
  • Distributed transactions
  • CAP theorem and eventual consistency
• Emerging Trends
  • NoSQL databases
  • NewSQL
  • Cloud-based DBMS (like AWS RDS, Google Spanner)
  • In-memory databases

Labs:

MySQL Server Installation and Configuration

Recommended Books:

1. Fundamentals of Database Systems, by Ramez Elmasri and Shamkant Navathe, Addison Wesley, 5th Edition.
2. Database System Concepts by Henry F. Korth and Abraham Silberschatz, 4th edition, McGraw Hill, 2002, ISBN: 0-07-12268-0

Useful Online Resources:

MySQL 8.0 Reference Manual