Database Management Systems: Core Concepts and SQL

Posted on Apr 3, 2026 in Computers

E.F. Codd’s 12 Rules for RDBMS

E.F. Codd, a pioneer in the field of relational databases, defined 12 rules (numbered 0 to 12) to determine whether a Database Management System (DBMS) can be considered a truly Relational Database Management System (RDBMS).

The 12 Rules Breakdown

Rule 0: The Foundation Rule: A system must use its relational facilities exclusively to manage the database.
Rule 1: The Information Rule: All information must be represented as values in tables (rows and columns).
Rule 2: Guaranteed Access Rule: Every data element must be logically accessible via Table Name, Primary Key, and Column Name.
Rule 3: Systematic Treatment of Null Values: The DBMS must support NULL values to represent missing or inapplicable information.
Rule 4: Dynamic Online Catalog: Metadata must be stored in the same relational format as ordinary data.
Rule 5: Comprehensive Data Sublanguage Rule: The system must support a language for DDL, DML, integrity constraints, and transaction management.
Rule 6: View Updating Rule: All theoretically updatable views must be updatable by the system.
Rule 7: High-Level Insert, Update, and Delete: The system must support set-at-a-time operations.
Rule 8: Physical Data Independence: Applications must remain unimpaired by changes to storage or access methods.
Rule 9: Logical Data Independence: Applications must remain unimpaired by changes to table structures.
Rule 10: Integrity Independence: Integrity constraints must be stored in the catalog, not in application code.
Rule 11: Distribution Independence: Users should not be aware of whether the database is distributed.
Rule 12: Non-Subversion Rule: Low-level interfaces cannot bypass high-level security or integrity constraints.

Relational Algebra Fundamentals

Relational algebra is a procedural query language used to manipulate relations. It provides the theoretical foundation for SQL.

Fundamental Operations

Selection (σ): Selects a subset of rows based on a condition.
Projection (π): Selects specific columns and eliminates duplicates.
Union (∪): Combines all tuples from two union-compatible relations.
Set Difference (−): Finds tuples present in the first relation but not the second.
Cartesian Product (×): Combines every tuple of one relation with every tuple of another.
Rename (ρ): Renames output relations or attributes.

Derived Operations

Join (⋈): Combines related tuples from two relations based on a common attribute.
Intersection (∩): Returns tuples common to both relations.
Division (÷): Used for queries involving the “all” condition.

SQL Data Manipulation Language (DML)

DML commands manage and modify data within existing database tables.

INSERT: Adds new rows of data.
UPDATE: Modifies existing records (use with WHERE).
DELETE: Removes specific rows based on a condition.
SELECT: Retrieves data from the database.

SQL Data Definition Language (DDL)

DDL commands define and manage the structure (schema) of database objects.

CREATE: Defines new database objects.
ALTER: Modifies existing object structures.
DROP: Deletes an entire object and its data.
TRUNCATE: Removes all records while keeping the structure.
RENAME: Changes the name of an object.

Database Triggers

A trigger is a stored procedure that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table or view.

Key Classifications

Timing: BEFORE, AFTER, or INSTEAD OF triggers.
Level: Row-level (fires per row) or Statement-level (fires per command).

PL/SQL Block Structure

PL/SQL combines SQL with procedural logic. A block consists of:

Header (Optional): For named blocks.
Declaration (Optional): Defines variables and constants.
Execution (Mandatory): Contains the logic and SQL statements.
Exception Handling (Optional): Manages errors.

Types of PL/SQL Blocks

Anonymous Blocks: Unnamed, compiled at runtime, not stored.
Named Blocks: Stored objects including Procedures, Functions, Triggers, and Packages.