What is the difference between DBMS and RDBMS?

A DBMS (Database Management System) stores data as flat files or hierarchical trees with no formal enforcement of relationships between tables (e.g., MS Access, XML). An RDBMS stores data in structured two-dimensional tables (relations) and enforces data integrity through primary keys, foreign keys, and ACID compliance.

What are the 4 pillars of ACID in database transactions?

ACID stands for Atomicity (all operations succeed or all rollback), Consistency (data obeys all schema constraints and foreign keys), Isolation (concurrent transactions execute without interference), and Durability (committed writes persist permanently on disk even during power failure).

What is the difference between Cardinality and Degree of a relation?

The Degree of a relation is the total number of columns (attributes). The Cardinality of a relation is the total number of rows (tuples) present in the table.

RDBMS Concepts & Architecture: Relations, Attributes & ACID Model

A solid conceptual grasp of Relational Database Management System (RDBMS) theory is essential for passing university examinations and designing robust enterprise software architectures. In this class, we break down the terminology of the relational model, compare DBMS vs RDBMS, examine table structures, and explore the legendary ACID transaction model.

RDBMS Relational Table Architecture and ACID Transaction Properties

Figure 1.2: Relational Schema Architecture showing tuples, attributes, domain constraints, and the 4 pillars of ACID.

Core Relational Database Terminology (Theory vs SQL)

Dr. Edgar Codd formulated relational database theory using rigorous mathematical terminology. In production software engineering, practical SQL terms are used interchangeably:

Mathematical Relational Term	Standard SQL Term	Technical Definition & Meaning
Relation	Table	A two-dimensional grid of rows and columns representing a specific entity (e.g., `students`, `orders`).
Tuple	Row / Record	A single horizontal record representing an individual instance of that entity.
Attribute	Column / Field	A named vertical column holding a specific data property (e.g., `student_name`, `gpa`).
Domain	Data Type & Constraints	The allowable set of atomic values for an attribute (e.g., valid integer, email string).
Degree	Column Count	The total number of columns present in a table.
Cardinality	Row Count	The total number of rows currently stored in a table.

DBMS vs RDBMS: A Comprehensive Comparison

Criteria	Traditional DBMS	Modern RDBMS
Data Storage	Data stored as isolated hierarchical files or flat documents.	Data stored in tabular relations with predefined columns.
Relational Keys	No concept of foreign keys or referential integrity.	Enforces Primary Keys and Foreign Keys.
Data Redundancy	High data redundancy and risk of inconsistent duplicates.	Minimized through database normalization (1NF, 2NF, 3NF).
Transaction Safety	Little or no ACID support; prone to corruption during crashes.	Full ACID transaction guarantees with Write-Ahead Logging (WAL).
Examples	XML, Flat text files, Microsoft Access (basic mode).	MySQL, PostgreSQL, Oracle Database, SQLite, MS SQL Server.

The 4 Pillars of ACID Transaction Safety

In database engineering, a Transaction is a sequence of one or more SQL operations treated as a single indivisible unit of work. Every reliable RDBMS guarantees the ACID model:

• Atomicity ("All or Nothing"): Guarantees that either all statements in a transaction execute successfully, or the entire transaction is rolled back to its original state if an error or crash occurs. Example: In a bank transfer, deducting money from Account A and crediting Account B must either both succeed or neither occurs.

• Consistency (Integrity Preservation): Ensures that a transaction transitions the database from one valid state to another valid state, strictly obeying all declared schema rules, foreign keys, uniqueness, and check constraints.

• Isolation (Concurrent Independence): Ensures that multiple transactions executing concurrently in parallel do not see each other's partial, uncommitted state. This prevents concurrency bugs like Dirty Reads and Phantom Reads.

• Durability (Permanent Storage): Guarantees that once a transaction is committed, its written data will never be lost, even in the event of an immediate server crash or sudden power failure, thanks to Write-Ahead Logs (WAL) flushed to non-volatile disk.

BCA Exam Sample Question: "Explain ACID properties with a real-world banking example."
Answer Outline: Present an online funds transfer scenario: Account A has $1000 and sends $200 to Account B. Step 1: Debit Account A ($800). Step 2: Credit Account B ($1200). If the network fails between Step 1 and Step 2, Atomicity rolls back Account A so money isn't lost in limbo. Consistency ensures balances cannot be negative. Isolation ensures Account A cannot be double-spent simultaneously. Durability ensures the update is persisted to disk immediately.

In the next class, we install and configure your SQL environment (Installation & Setup) using MySQL and SQLite!