Skip to main content

What is a Relational Database?​

A relational database stores data in tables made up of rows and columns. Each table focuses on one type of data, and relationships between tables are created using keys.

Example:

  • Customers Table: ID, Name, Email
  • Orders Table: Order ID, Customer ID, Product ID
  • Products Table: Product ID, Name, Price

These tables link together using Primary Keys and Foreign Keys, which helps avoid duplicate data and keeps everything consistent.

What is RDBMS?​

An RDBMS (Relational Database Management System) is the software used to create, manage, and query relational databases using SQL.

Key Features​

  • Stores data in structured tables (rows and columns).
  • Supports ACID transactions (safe and reliable).
  • Uses SQL for data handling.
  • Enforces data accuracy with rules like Primary and Foreign Keys.
  • Allows multiple users with secure access.

Used in: Banking, e-commerce, enterprise systems, and more.

Components of an RDBMS​

  • Database Engine
    • Runs the core database operations like reading/writing data.
    • Ensures ACID compliance.
  • Schema
    • Blueprint of the database: tables, keys, constraints, and relationships.
  • SQL Query Processor
    • Understands and runs SQL commands.
    • Optimizes queries for speed.
  • Storage Manager
    • Handles data storage on disk.
    • Uses indexes and cache to speed up access.
  • Transaction Manager
    • Manages commits, rollbacks, and user conflicts to keep data reliable.
  • Security Manager
    • Controls who can access or change what.
  • Backup & Recovery
    • Prevents data loss and helps recover from crashes.
  • Monitoring & Reporting Tools
    • Tracks system performance and provides logs.

IBM DB2 Architecture​

An IBM DB2 system has several important parts that work together to store, manage, and retrieve data. Here's a simple overview of the main components:

DB2 Instance / Address Space​

The DB2 instance is the environment where DB2 runs. It includes system processes, memory, and settings needed to manage databases. It works using several address spaces:

  • MSTR (System Services Address Space) Handles system-wide tasks like logging, startup/shutdown, recovery, and thread management.

  • DBM1 (Database Services Address Space) Manages buffer pools, locks, database I/O, SQL execution, caching, and sorting.

  • IRLM (Inter-System Resource Lock Manager) Provides locking services to ensure data consistency, avoid deadlocks, and control concurrent access.

  • DDF (Distributed Data Facility Address Space) Manages connections from remote clients and supports distributed transactions using DRDA.

Database Engine (SQL Processor)​

This is the core of DB2. It:

  • Runs and interprets SQL queries.
  • Manages transactions, locks, and concurrency.
  • Works with the query optimizer to find efficient ways to run queries.

Memory Structures and Buffer Pools​

These are memory areas used to store frequently accessed data. They reduce the need for disk reads and improve performance.

System Catalog (Data Dictionary)​

A collection of system tables that store information about database objects like tables, indexes, and user permissions. It helps with query planning and database integrity.

Databases​

Each DB2 database includes:

  • Tablespaces and Containers – Logical and physical storage areas.
  • Tables, Indexes, Views, etc. – The actual objects used to store and organize data.

Transaction Logs​

These logs keep a record of all changes made in the database. They help recover data in case of failure and maintain data integrity.

Utilities and Tools​

DB2 comes with many tools for managing and maintaining the database, such as:

  • Backup and restore tools
  • Data load, import/export utilities
  • Diagnostic and performance tuning tools

Client Connectivity Interfaces​

DB2 supports many client APIs like CLI, ODBC, JDBC, and .NET. These let applications connect to and work with the database.

Communication Manager​

This part handles network communication, managing how DB2 receives and responds to client requests.

Here's a simplified and clear version of your content, keeping all key ideas intact:

Introduction to SQL​

SQL (Structured Query Language) is a standard language used to manage and work with relational databases. It allows both users and database administrators (DBAs) to perform a variety of tasks.

IBM Docs - Categories of SQL statements

What SQL Can Do​

  • Query data – Retrieve specific information from one or more tables.
  • Manipulate data – Insert, update, or delete records in tables.
  • Define data structures – Create or change tables, indexes, views, etc.
  • Control access – Set user permissions and manage data security.

SQL is declarative. This means you tell the database what you want (e.g., "give me all customers in New York"), and the system figures out how to get it. You don’t need to specify the steps.

Before SQL: Older Database Models​

Before relational databases and SQL became popular, databases used different models:

  • Hierarchical Databases – Data was organized in a tree-like structure.
    • Example: IBM’s IMS (Information Management System)
    • Accessing data required a step-by-step, navigational approach, like using DL/1.

These older systems were more rigid and harder to use compared to modern relational models.

SQL Alternatives and Extensions​

While SQL is the main language for relational databases, other tools and approaches exist:

  • ORM (Object-Relational Mapping) Tools Tools like Hibernate (Java) and Entity Framework (.NET) let developers work with databases using their programming language, hiding SQL behind the scenes.

  • Proprietary SQL Extensions Many databases offer their own extensions to SQL, such as:

    • PL/SQL in Oracle
    • T-SQL in Microsoft SQL Server

These extensions add features like loops and conditions, making SQL more powerful.

Even with these tools and extensions, most of the real work in relational databases still happens using SQL underneath.

A Brief History of SQL​

  • 1970 – Edgar F. Codd introduced the relational model, a new way to organize and query data using mathematics and logic.
  • Early 1970s – IBM built System R, an early relational database system. It used a language called SEQUEL (Structured English Query Language), which later became SQL.
  • Late 1970s – Early 1980s – SQL was adopted by commercial database systems and became standardized by ANSI and ISO.
  • Following Decades – Major systems like Oracle, SQL Server, MySQL, PostgreSQL, and IBM DB2 adopted and improved SQL, helping it become the standard language for working with relational data.

Here’s a simplified and clear version of your content on the Benefits of Using SQL, with all important points preserved:

Benefits of Using SQL​

Standardization & Portability​

SQL follows international standards (ANSI/ISO), so basic commands work similarly across most database systems. This makes it easier to move applications between different databases with minimal changes.

Declarative Nature​

With SQL, you simply state what you want, not how to get it.

example

“Get all products where price is above 100.” The database engine figures out the best way to do this, making queries easier to write and more efficient.

Rich Functionality​

SQL can handle:

  • Simple data lookups
  • Complex joins across tables
  • Aggregations (like sums and averages)
  • Transactions that involve multiple steps

It’s a powerful language for both small and large tasks.

Efficient Data Handling​

SQL engines are optimized for large volumes of data. They use smart ways to parse, optimize, and run queries quickly and efficiently.

Wide Adoption & Community Support​

Because SQL is so widely used:

  • There are many tools and libraries available.
  • You can easily find help, guides, and tutorials.
  • It’s well supported by developers and database vendors.

Easy Integration with Other Technologies​

SQL works well with most programming languages and frameworks (like Python, Java, .NET). This makes it easy to build powerful applications that interact with databases.