Nishant Munjal

Code For Humanity

Mastering Database Management

Published on 13 January 2024

This course is tailored for beginners and intermediate learners who seek a deep understanding of how databases work and want to master the art of managing data efficiently. Whether you’re a Python enthusiast or a PHP developer, we provide a versatile learning experience that caters to both programming languages.

Introduction to Databases

Lessons

Overview of DBMS Understanding the Importance of Data Types of databases (relational, NoSQL, etc.) Definition of DBMS Characteristics of DBMS ACID Properties DBMS Architecture

Relational Models

Lessons

Understanding Entity-Relationship Diagrams (ERD) Data Models Entity-Relationship Model Types of Attributes Entity Set and Keys Relationships & Mapping Cardinalities Quiz 1 – Relationship Sets Generalization and Specialization Relational Data Model Relational Integrity Constraints Quiz 2 – Key Constraints Relational Algebra Relational Calculus

Setting Up the Environment

Lessons

Installation of necessary software (MySQL Database) Creating the first database and tables

Basic SQL Commands with CRUD Operations

Lessons

Introduction to SQL and its variations SQL Statement Lanugauge SELECT, INSERT, UPDATE, DELETE Filtering and Sorting Data Joins Subqueries

Lab Assignment/Tutorial

Lessons

Lab Tutorial 1 Lab Tutorial 2 Assignment 1 Lab Tutorial 3 Lab Tutorial 4 : ORDER BY & AGGREGATE Functions Lab Tutorial 5: Scaler Functions Lab Tutorial 6: IN & BETWEEN Lab Tutorial 7: Joins Lab Tutorial 8: SQL Operators Lab Tutorial 9: Nested Queries

Data Modeling

Lessons

Dependencies in Database Normalization Quiz: Dependencies Normalization and denormalization First Normal Form Second Normal Form Third Normal Form Fourth Normal Form Boyce-Codd Normal Form Quiz: Normalization

How to think of a Solution…

Lessons

Translating ERD to Database Schema

Project: MySql with PHP

First you need to complete the steps given in the module “Setting Up the Environment“

Lessons

MySql with PHP

Advanced SQL Concepts

Lessons

Indexing and optimization Stored procedures and triggers Transactions and ACID properties

Project: MySQL with Python using Flask

Lessons

MySQL with Python and Flask

Project: Inventory Managment

Lessons

Think of an Inventory Management Define Entities and Relations among them Designing the database schema for the project Implementing CRUD operations for the project Entire Project with PYTHON CODE Answers of the questions

Transaction Processing Concept

Lessons

What is Transaction? Serializability Equivalence Schedules States of Transaction Recovery and Atomicity Deadlock

Concurrency Control Techniques

Lessons

Concurrency Control Lock Based Protocol Timestamp based Protocol Recovery with Concurrent Transaction Transaction Processing in Distributed Systems

Data Backup and Recovery

Lessons

Strategies for data backup Point-in-time recovery Data Replication and Allocation

Security and Permissions

Lessons

Database user roles and permissions Securing against SQL injection and other attacks

Data Migration

Lessons

Importing and exporting data Database versioning and migration tools

Final Project

Lessons

Building a comprehensive database-backed project Presenting and documenting the project

Downloads

Lessons

PPT