DataBases

MariaDB Course Details

Module 1: Introduction to MariaDB

• What is MariaDB?
  • Overview of MariaDB and its origin
  • MariaDB vs MySQL: Key Differences and Features
  • Why use MariaDB? Benefits for Developers
  • MariaDB’s Open-Source Philosophy and Community Support

Module 2: Setting Up MariaDB

• Installation of MariaDB
  • Installing MariaDB on Windows, Linux, and macOS
  • Configuring MariaDB server settings
  • Accessing MariaDB via command line (CLI) and GUI (phpMyAdmin, HeidiSQL)
• MariaDB Basics
  • Introduction to MariaDB environment and tools
  • Connecting to MariaDB from CLI and MySQL Workbench
  • Creating and managing databases

Module 3: Working with Databases and Tables

• Creating Databases and Tables
  • Basic SQL commands (CREATE DATABASE, CREATE TABLE)
  • Data types in MariaDB
  • Setting primary keys, indexes, and foreign keys
• Manipulating Data
  • Inserting, updating, and deleting data (INSERT, UPDATE, DELETE)
  • SELECT query basics (WHERE, AND, OR, LIKE)
  • Using aggregate functions (COUNT, SUM, AVG, MIN, MAX)

Module 4: Advanced Queries

• JOINs in MariaDB
  • INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
  • Using JOIN with multiple tables
  • Using aliases in queries
• Subqueries and Nested Queries
  • Writing subqueries (IN, EXISTS, ANY, ALL)
  • Correlated subqueries

Module 5: Performance Optimization

• Indexing and Query Optimization
  • Understanding indexing in MariaDB
  • Optimizing queries with EXPLAIN command
  • Using query cache for performance
• Database Normalization
  • Introduction to normalization (1NF, 2NF, 3NF)
  • Denormalization and its use cases

Module 6: Database Security and User Management

• MariaDB User Management
  • Creating and managing user accounts
  • Granting and revoking privileges
  • Using roles and permissions
• Data Security
  • Enabling SSL/TLS for secure connections
  • Backing up and restoring databases
  • Encrypting data at rest

Module 7: Advanced Features in MariaDB

• Replication in MariaDB
  • Master-slave replication
  • Multi-source replication
  • Setting up Galera Cluster for high availability
• Backup and Restore
  • Using mysqldump for backups
  • Restoring databases from backups

---

MongoDB Course Details

Module 1: Introduction to MongoDB

• What is MongoDB?
  • Introduction to NoSQL databases
  • Key differences between MongoDB and relational databases
  • Understanding MongoDB’s flexible data model
• MongoDB Installation
  • Installing MongoDB on Windows, Linux, and macOS
  • Using MongoDB Compass for graphical interface
  • MongoDB shell and tools setup

Module 2: MongoDB Basics

• CRUD Operations in MongoDB
  • Inserting data (insertOne, insertMany)
  • Retrieving data (find, findOne)
  • Updating data (updateOne, updateMany, replaceOne)
  • Deleting data (deleteOne, deleteMany)
• Working with MongoDB Collections
  • Understanding Collections and Documents
  • Schema-less structure in MongoDB
  • Data types in MongoDB (String, Number, Date, Boolean, Arrays, Objects)

Module 3: Querying in MongoDB

• Basic Queries
  • Using filters to query data
  • Sorting and limiting query results
  • Regular expressions and text search
• Advanced Querying
  • Projection (selecting specific fields)
  • Using aggregation framework (group, match, project, sort)
  • Working with embedded documents

Module 4: Data Modeling and Design

• Designing Schema in MongoDB
  • Embedding vs. Referencing documents
  • One-to-many and many-to-many relationships
  • When to use embedding and when to use referencing
• Indexing in MongoDB
  • Creating and managing indexes (single field, compound, geospatial)
  • Indexing best practices for performance optimization
  • Query performance analysis with explain()

Module 5: Performance and Scaling in MongoDB

• Sharding in MongoDB
  • Horizontal scaling with sharding
  • Shard key selection and data distribution
  • Balancing data across shards
• Replication and High Availability
  • Understanding replica sets
  • Configuring primary and secondary nodes
  • Write concern and read preference settings

Module 6: Security in MongoDB

• Authentication and Authorization
  • Enabling authentication in MongoDB
  • Role-based access control (RBAC)
  • Setting up user privileges
• Encryption
  • Encryption at rest and in transit
  • Enabling SSL/TLS for secure connections

Module 7: MongoDB Administration

• Backup and Restore in MongoDB
  • Using mongodump and mongorestore
  • Backup strategies for MongoDB
  • Managing and automating backups
• Monitoring and Troubleshooting
  • Monitoring MongoDB with the MongoDB Atlas
  • Identifying performance bottlenecks and slow queries
  • Handling replication lag

---

MySQL Course Details

Module 1: Introduction to MySQL

• What is MySQL?
  • Overview of MySQL database management system
  • MySQL vs MariaDB: Key Differences
  • Features of MySQL (ACID compliance, transaction support, etc.)
• Setting Up MySQL
  • Installing MySQL on Windows, Linux, and macOS
  • Configuring MySQL server and accessing MySQL Workbench

Module 2: Basic SQL Commands in MySQL

• Creating Databases and Tables
  • Basic commands to create databases and tables
  • Understanding MySQL data types
  • Setting primary keys, foreign keys, and indexes
• Data Manipulation
  • Inserting, updating, and deleting data in tables
  • SELECT queries with WHERE, ORDER BY, LIMIT
  • Working with aggregate functions (SUM, AVG, COUNT)

Module 3: Advanced SQL Queries

• Advanced Queries in MySQL
  • JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
  • GROUP BY, HAVING, and Aggregate Functions
  • Subqueries and nested queries
• Stored Procedures and Functions
  • Creating stored procedures in MySQL
  • Using functions and triggers in MySQL
  • Handling exceptions with error handling

Module 4: Performance Optimization

• Indexing and Query Optimization
  • Creating and managing indexes in MySQL
  • Query optimization using EXPLAIN
  • Using query cache to speed up performance
• Normalization and Data Integrity
  • Database normalization (1NF, 2NF, 3NF)
  • Ensuring data integrity using foreign keys and constraints

Module 5: MySQL Replication and Clustering

• Replication in MySQL
  • Master-Slave replication setup
  • GTID-based replication and failover
  • Configuring master-master replication for high availability
• MySQL Clustering
  • MySQL Cluster setup and management
  • Using NDB Cluster for high availability and scalability

Module 6: Backup and Security

• MySQL Backup and Recovery
  • Using mysqldump for backups
  • Point-in-time recovery with binary logs
  • Restoring databases from backups
• Security in MySQL
  • User management and access control
  • Securing MySQL with SSL/TLS encryption
  • Auditing MySQL access and queries

Module 7: Administration and Maintenance

• MySQL Server Administration
  • Managing MySQL server configurations
  • Monitoring MySQL performance with tools (Percona, MySQL Enterprise Monitor)
  • Error logs, slow query logs, and troubleshooting
• Maintenance and Updates
  • Upgrading MySQL versions
  • Automating maintenance tasks and backups
  • Database tuning and performance monitoring