B-Tree

🌳 B-Tree Nomenclature

• Order (m) → max children = m
• Max keys → m − 1
• Min keys (non-root) → ⌈m/2⌉ − 1
• Children vs Keys → keys = children − 1
• Root → minimum 1 key
• Internal node → has children
• Leaf node → no children
• All leaves → same level (balanced)
• Height → O(logₘ n)

---

Insert Operation

Insert Operation another example

B-Tree of Order 5 Insert Keys 7, 4, 14, 25, 3, 10, 12, 15, 17, 9, 29, 1, 38, 2, 11

Definition

A transaction in DBMS is a finite sequence of database operations that is executed as one logical unit of work, meaning either all operations are performed successfully or none are performed.

-- Example for "logical unite of work"
Read(A)
A = A − 100
Write(A)
Read(B)
B = B + 100
Write(B)
Commit

A transaction must satisfy the ACID properties to ensure database correctness and consistency.

---

Key Points

• A transaction groups multiple database operations into a single logical task
• Partial execution is not allowed
• Failure of any operation causes the entire transaction to rollback
• Ensures reliability in concurrent and failure-prone environments

---

ACID Properties (Overview)

• Atomicity – All operations execute or none execute
• Consistency – Database remains in a valid state
• Isolation – Transactions do not interfere with each other
• Durability – Committed changes persist permanently

---

Schedule

A schedule is the execution order of operations of multiple transactions on the same database, possibly executed concurrently.

What is PL/SQL?

1. PL/SQL stands for Procedural Language Extension to SQL
2. It is a Extension of SQL developed by Oracle.
3. It adds SQL capabilities of procedural programming like variables, loops and conditional statements in SQL.

PL/SQL Architecture

Physical Architecture

• PL/SQL engine processes the entire PL/SQL block of code.
• It Separate SQL statements and Procedural statements.

Simple Architecture Flow

PL/SQL Logical Architecture

• Corporates with SQL Engine.
• Enables Subprograms -> Allows re-usability of code/script like variables and functions.
• Dynamic Queries-> We can modify or create new Queries.
• Case Insensitivity
• Optimizer-> It optimizes our code for better performance.
• Enables Object-Oriented Programming
• Web Development #Extras Context Switching: When you write SQL Query in PL/SQL the PL/SQL send the query to SQL Engine and the result will be returned this operation is called Context Switching.

---

Pluggable Database