Applied Database Management System

My DBMS, Entity Relationship Diagram, and SQL learnings from Lakshay Kumar & Love Babbar

All Topics Covered:
❇️Three-level architecture (External views/Logical/Physical)
❇️DBMS & DB
❇️DB Schema or Data Model [Conceptual or Logical Level]
❇️Database Languages
-> DDL (Create, alter, drop)
-> DML (insert, delete, update)
-> DQL (select in, not in, distinct, like, is null, group by having, order by)

❇️Weak Entity & relation and Total Participation
❇️Specialization, Generalization, Aggregation
❇️Industry-ER Diagram
❇️Conversion of ER to Relational Model
❇️SQL
❇️DDL constraints - PK/FK/Check/Default/Unique
❇️ 6 JOINS (Inner, Left, Right, Full/full-outer, Cross, Self)
❇️ 3 SUBQUERIES (Single value, co-related, derived)
❇️3 Set Operations (Union, Intersection, Minus)
❇️Views

❇️NORMALIZATION
❇️Functional Dependency [Trivial/Non-Trivial]
❇️Armstrong rules (Reflexive, Augmentation, Transitivity)
❇️Anomalies (Insertion, Deletion, Updation)
❇️Types of normal-forms (1NF, 2NF, 3NF, BCNF)
❇️ACID properties (Atomicity, Consistency, Isolation, Durability)
❇️Transaction States
❇️Atomicity & Durability implementation
-> Shadow copy
-> Log-based recovery [differed, immediate]

❇️Indexing ( Primary(clustering) and Secondary(Non-clustering) )
❇️SQL vs NoSQL (Not Only SQL)
❇️NoSQL Types [key-value store, columnar, document, graph]
❇️Clustering (Replica Sets) Types : Synchronous and Async
❇️Partitioning ( Sharding - a way of horizontal partitioning )
❇️DESIGN Patterns
-> Query Optimization
-> Vertical Scaling or Scale-Up
-> Single Query-Responsibility-Segregation(QRS)
-> Multi-Primary Replication
-> Partitioning of Data by functionality
-> Horizontal Scaling or Sharding or Scale-Out
-> DATA CENTER wise Partition (99% Availability)
❇️CAP Theorem
❇️MASTER-SLAVE Architecture
❇️ACID vs BASE Properties
MongoDB