Joins, Relationships, Constraints, Normalization, and Data Modeling

Relational Thinking

The real strength of a relational database appears when tables connect meaningfully. Customers place orders. Orders contain items. Employees belong to departments. Students register for courses. Instead of copying the same facts many times, Oracle DB lets you model relationships cleanly and query them later.

Primary Keys and Foreign Keys

CREATE TABLE order_items (
    order_item_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id NUMBER NOT NULL,
    product_id NUMBER NOT NULL,
    quantity NUMBER NOT NULL,
    unit_price NUMBER(10,2) NOT NULL,
    CONSTRAINT fk_order_items_orders
        FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_order_items_products
        FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Foreign keys protect referential integrity and prevent broken relationships from entering the system.

Joining Tables for Useful Results

SELECT
    o.order_id,
    c.full_name,
    o.order_status,
    o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_status = 'PENDING';

Joins are essential for enterprise reporting, operational dashboards, and application services. Deep join understanding marks a big step from beginner to intermediate SQL skill.

Normalization and Design Tradeoffs

Normalization reduces unnecessary duplication and helps preserve data quality. Customer information belongs in the customer table rather than duplicated across every order. At the same time, some duplication may be intentional for history or performance reasons. Advanced design is about understanding those tradeoffs clearly.