Skip to main content

​
What is Relational Database Concepts?

Relational Database Concepts refer to the foundational principles and structures that organize data in a relational database management system (RDBMS) like Oracle Database. Also known as the Relational Model, it represents data as mathematical n-ary relations (tables) with defined relationships between them. The core purpose is to provide a logical, structured approach to data storage that eliminates redundancy, ensures data integrity, and enables efficient querying through SQL operations. This model solves the problem of disorganized, redundant data storage by introducing rigorous structure and mathematical foundations to data management, allowing for declarative data manipulation rather than procedural navigation.

​
Tables and Columns

Tables are the fundamental storage structures that represent entities in the relational model. Columns define the attributes or properties of these entities, each with a specific data type and constraints. 
-- Creating a table with various column definitions
CREATE TABLE employees (
    -- Column definitions with data types and constraints
    employee_id   NUMBER(6)     NOT NULL,
    first_name    VARCHAR2(20) NOT NULL,
    last_name     VARCHAR2(25) NOT NULL,
    email         VARCHAR2(25) NOT NULL,
    hire_date     DATE         DEFAULT SYSDATE,
    salary        NUMBER(8,2),
    department_id NUMBER(4)
);

-- PL/SQL block inserting data into table columns
DECLARE
    v_emp_id employees.employee_id%TYPE := 1001;
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, email, salary)
    VALUES (v_emp_id, 'John', 'Doe', '[email protected]', 50000);
    
    COMMIT;
END;
/

​
Rows and Records

Rows represent individual instances or records within a table. In PL/SQL, we often work with records - composite data types that mirror table rows, allowing us to manipulate entire row structures programmatically. 
-- Using %ROWTYPE to declare a record variable matching table structure
DECLARE
    emp_record employees%ROWTYPE;  -- Record variable matching employees table
    CURSOR emp_cur IS 
        SELECT * FROM employees WHERE employee_id = 1001;
BEGIN
    OPEN emp_cur;
    FETCH emp_cur INTO emp_record;  -- Entire row fetched into record variable
    
    IF emp_cur%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.first_name || ' ' || emp_record.last_name);
        DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_record.salary);
    END IF;
    
    CLOSE emp_cur;
END;
/

​
Primary Keys

A primary key uniquely identifies each row in a table. It enforces entity integrity by preventing duplicate entries and cannot contain NULL values.
-- Creating a table with primary key constraint
CREATE TABLE departments (
    department_id   NUMBER(4)   PRIMARY KEY,  -- Primary key column
    department_name VARCHAR2(30) NOT NULL UNIQUE,
    manager_id      NUMBER(6)
);

-- PL/SQL demonstrating primary key usage for data integrity
DECLARE
    v_dept_id NUMBER(4) := 60;
BEGIN
    -- Primary key prevents duplicate department_ids
    INSERT INTO departments (department_id, department_name)
    VALUES (v_dept_id, 'Research and Development');
    
    COMMIT;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Primary key violation: Department ID already exists');
        ROLLBACK;
END;
/

​
Foreign Keys

Foreign keys create relationships between tables by referencing the primary key of another table. They enforce referential integrity, ensuring that relationships between tables remain consistent. 
-- Adding foreign key constraint to employees table
ALTER TABLE employees 
ADD CONSTRAINT fk_emp_dept 
FOREIGN KEY (department_id) 
REFERENCES departments(department_id);

-- PL/SQL demonstrating referential integrity enforcement
DECLARE
    v_invalid_dept NUMBER(4) := 999;  -- Non-existent department
BEGIN
    -- This will fail due to foreign key constraint
    INSERT INTO employees (employee_id, first_name, last_name, email, department_id)
    VALUES (1002, 'Jane', 'Smith', '[email protected]', v_invalid_dept);
    
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -2291 THEN  -- Integrity constraint violated error
            DBMS_OUTPUT.PUT_LINE('Foreign key violation: Invalid department ID');
            ROLLBACK;
        END IF;
END;
/

​
Indexes

Indexes are database objects that improve query performance by creating optimized access paths to data.
While primarily managed through DDL, PL/SQL developers must understand index usage patterns for performance optimization.
-- Creating indexes for performance
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_emp_name ON employees(last_name, first_name);

-- PL/SQL demonstrating index impact on query performance
DECLARE
    CURSOR fast_cur IS
        SELECT employee_id, first_name, last_name 
        FROM employees 
        WHERE last_name = 'Doe'  -- Benefits from idx_emp_name index
        AND department_id = 60;  -- Benefits from idx_emp_dept index
    
    emp_rec fast_cur%ROWTYPE;
BEGIN
    OPEN fast_cur;
    LOOP
        FETCH fast_cur INTO emp_rec;
        EXIT WHEN fast_cur%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('Found: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
    END LOOP;
    CLOSE fast_cur;
END;
/

​
Normalization Forms

Normalization is the process of organizing data to reduce redundancy and improve integrity. PL/SQL implements business logic that relies on properly normalized database structures. 
-- Example demonstrating 3rd Normal Form (3NF) compliance
-- Tables are normalized to eliminate transitive dependencies

CREATE TABLE projects (
    project_id     NUMBER(6) PRIMARY KEY,
    project_name   VARCHAR2(50) NOT NULL,
    department_id  NUMBER(4) NOT NULL,
    -- department_name is NOT stored here (would violate 3NF)
    -- Instead, it's retrieved via join when needed
    CONSTRAINT fk_proj_dept FOREIGN KEY (department_id) 
    REFERENCES departments(department_id)
);

-- PL/SQL procedure that leverages normalized structure
CREATE OR REPLACE PROCEDURE get_project_details (p_project_id IN NUMBER) IS
    CURSOR proj_cur IS
        SELECT p.project_id, p.project_name, d.department_name
        FROM projects p
        JOIN departments d ON p.department_id = d.department_id  -- Proper normalized join
        WHERE p.project_id = p_project_id;
    
    v_project_details proj_cur%ROWTYPE;
BEGIN
    OPEN proj_cur;
    FETCH proj_cur INTO v_project_details;
    
    IF proj_cur%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Project: ' || v_project_details.project_name);
        DBMS_OUTPUT.PUT_LINE('Department: ' || v_project_details.department_name);
    END IF;
    
    CLOSE proj_cur;
END;
/

​
Relationships Between Tables

Relationships define how tables connect to each other. PL/SQL code frequently navigates these relationships through joins and foreign key constraints to retrieve related data. 
-- Demonstrating one-to-many relationship navigation
DECLARE
    CURSOR dept_emp_cur IS
        SELECT d.department_name, e.employee_id, e.first_name, e.last_name
        FROM departments d
        LEFT JOIN employees e ON d.department_id = e.department_id  -- One-to-many join
        ORDER BY d.department_name, e.last_name;
    
    v_current_dept VARCHAR2(30);
BEGIN
    FOR rec IN dept_emp_cur LOOP
        -- Demonstrating hierarchical relationship traversal
        IF v_current_dept IS NULL OR v_current_dept != rec.department_name THEN
            DBMS_OUTPUT.PUT_LINE('--- Department: ' || rec.department_name || ' ---');
            v_current_dept := rec.department_name;
        END IF;
        
        IF rec.employee_id IS NOT NULL THEN
            DBMS_OUTPUT.PUT_LINE('   Employee: ' || rec.first_name || ' ' || rec.last_name);
        ELSE
            DBMS_OUTPUT.PUT_LINE('   No employees in department');
        END IF;
    END LOOP;
END;
/

​
Why is Relational Database Concepts Important?

Relational database concepts form the foundation for all database operations in Oracle.

​
Key Benefits

  1. ACID Compliance - Ensures transactional integrity through Atomicity, Consistency, Isolation, and Durability principles, preventing data corruption.
  2. DRY Principle (Don’t Repeat Yourself) - Normalization eliminates data redundancy, storing each fact only once and reducing storage costs and update anomalies.
  3. Scalability - Proper indexing and relational design enable efficient data retrieval even as database size grows exponentially.

​
Advanced Nuances

​
Composite Primary Keys

Composite primary keys and Index-Organized Tables require careful design to avoid performance pitfalls.
-- Composite primary key example
CREATE TABLE order_items (
    order_id NUMBER(12) NOT NULL,
    line_item_id NUMBER(3) NOT NULL,
    product_id NUMBER(6) NOT NULL,
    quantity NUMBER(8),
    -- Composite primary key using multiple columns
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_item_id)
);

​
Deferred Constraint Checking

-- Creating deferrable constraint
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept_defer
FOREIGN KEY (department_id) 
REFERENCES departments(department_id)
DEFERRABLE INITIALLY DEFERRED;  -- Validation occurs at commit time

-- In transaction:
SET CONSTRAINT fk_emp_dept_defer DEFERRED;
-- Can insert employees before departments temporarily

​
Function-Based Indexes

-- Function-based index for case-insensitive search
CREATE INDEX idx_emp_email_upper ON employees(UPPER(email));

-- PL/SQL using function-based index
DECLARE
    CURSOR emp_cur IS
        SELECT * FROM employees 
        WHERE UPPER(email) = UPPER('[email protected]');  -- Uses function-based index
BEGIN
    NULL;
END;
/

​
How This Fits the Roadmap

Within the “Foundations of Databases” section, Relational Database Concepts serves as the cornerstone prerequisite for all subsequent topics.

​
This Foundation Enables:

  • Advanced SQL Techniques - Understanding joins, subqueries, and set operations requires solid grasp of table relationships
  • Performance Tuning - Index strategies and query optimization depend entirely on understanding table structures and relationships
  • Data Modeling - More advanced physical and logical database design builds upon these core relational principles
  • Advanced PL/SQL Features - Concepts like bulk operations, REF CURSORS, and object-relational features all assume mastery of basic relational structures

Build docs developers (and LLMs) love

Get started for freeTalk to us