Distributed Database System for Spanish Wine Distribution

Oracle Database Project - Academic Year 2025-2026

Note: This project was developed in Spanish as coursework for a Distributed Database Systems course. The original work and documentation are in Spanish, though this README has been translated to English to showcase the technical achievements to a broader audience

Authors: Felipe Poviña Migliavacca & Alberto Jiménez Torres

---

📋 Table of Contents

• Executive Summary
• Business Problem
• Technical Architecture
• Database Design
• Distribution Strategy
• Advanced Features
• SQL Competencies Demonstrated
• Project Structure
• Implementation Highlights
• Query Examples
• Testing & Validation

---

🎯 Executive Summary

This project implements a fully distributed Oracle database system for managing wine distribution across Spain's 19 autonomous communities through 4 regional delegations. The system demonstrates:

• Advanced distributed database design with horizontal fragmentation
• Complex business logic through 25+ triggers and stored procedures
• SQL expertise including PL/SQL, distributed queries, and transaction management
• Real-world problem-solving for inventory management and multi-region coordination

Key Achievements:

• 95% query locality through intelligent data fragmentation
• Zero-downtime cross-region operations
• Distributed referential integrity without native FK constraints
• Real-time inventory tracking across 4 nodes

---

💼 Business Problem

The Challenge

A Spanish wine distribution company operates:

• 4 Regional Delegations: Madrid, Barcelona, La Coruña, Granada
• 19 Autonomous Communities across Spain
• Multiple branches per region serving different client types
• Complex inter-delegation ordering for wines not locally distributed

Business Requirements

1. Regional Specialization: Each delegation only distributes wines from their autonomous communities
2. Cross-region Coordination: Clients can order any wine, but orders route through local branches
3. Inventory Management: Real-time stock tracking with distributed updates
4. 95% Local Operations: Queries must predominantly access local data for performance
5. Business Rules Enforcement: 25+ integrity constraints including temporal ordering, stock limits, and territorial restrictions

---

🏗️ Technical Architecture

Database Infrastructure

4-Node Distributed Architecture

Node	Location	Region	Autonomous Communities
sobresaliente1	Madrid	Central Spain	Madrid, Castilla-La Mancha, Castilla y León, Aragón, La Rioja
sobresaliente2	Barcelona	Levante	Cataluña, Baleares, País Valenciano, Murcia
sobresaliente3	La Coruña	Northern Spain	Galicia, Asturias, Cantabria, País Vasco, Navarra
sobresaliente4	Granada	Southern Spain	Andalucía, Extremadura, Canarias, Ceuta, Melilla

Technologies

• Database: Oracle Database 19c+
• Language: PL/SQL for stored procedures and triggers
• Tools: SQL Developer 24.3.1.347.1826
• Distribution: Database Links for cross-node communication

---

📊 Database Design

Entity-Relationship Model

Core Entities

PRODUCTOR (Producer)

codigo_productor PK
dni UNIQUE NOT NULL
nombre NOT NULL
domicilio

Represents wine producers. Fully replicated across all nodes for read optimization.

VINO (Wine)

codigo_vino PK
marca (brand)
anio_cosecha (vintage year)
denominacion_origen (D.O.)
graduacion (alcohol content)
comunidad_autonoma (region of origin)
cantidad_producida (production volume)
cantidad_stock (current stock)
codigo_productor FK → PRODUCTOR

Key Constraints:

• Stock = Production - Total Supplies (automatically calculated)
• Stock cannot be negative
• Wines with supply history cannot be deleted
• Vintage year cannot exceed current year

CLIENTE (Client)

codigo_cliente PK
dni UNIQUE NOT NULL
nombre NOT NULL
tipo (A/B/C: Supermarket/Retail/Restaurant)
comunidad_autonoma (client's region)

SUCURSAL (Branch)

codigo_sucursal PK
nombre NOT NULL
ciudad (city)
comunidad_autonoma NOT NULL
director UNIQUE FK → EMPLEADO

Business Rule: Each employee can only manage one branch at a time.

EMPLEADO (Employee)

codigo_empleado PK
dni UNIQUE NOT NULL
nombre NOT NULL
fecha_comienzo (start date)
salario (salary - can only increase)
codigo_sucursal FK → SUCURSAL

Special Constraint: Salaries can only increase, never decrease (enforced by trigger).

Transactional Tables

SOLICITA (Client Request)

Primary key: (codigo_cliente, codigo_vino, codigo_sucursal, fecha)

Tracks client wine requests through specific branches.

PEDIDO (Inter-branch Order)

Primary key: (codigo_sucursal_solicitante, codigo_vino, fecha)

Manages orders between branches when local stock is unavailable.

SUMINISTRA (Supply Record)

Primary key: (codigo_sucursal, codigo_vino, fecha)

Records actual wine deliveries, triggers automatic stock updates.

---

🗂️ Distribution Strategy

Fragmentation Approach

Horizontal Primary Fragmentation

CLIENTE - By client's comunidad_autonoma

• Rationale: 95% of queries from local branches
• Benefit: Complete locality for client management

SUCURSAL - By branch location

• Rationale: Branches operate within their region
• Benefit: Local branch operations don't require cross-node access

VINO - By wine's origin region

• Rationale: Stock updates occur locally where wine is produced
• Benefit: High-frequency inventory operations stay local

Horizontal Derived Fragmentation

EMPLEADO - Derived from SUCURSAL via codigo_sucursal

• Benefit: Employee management operations are fully local

SOLICITA - Derived from CLIENTE via codigo_cliente

• Benefit: Maintains client-request affinity, reduces remote access

PEDIDO - Derived from SUCURSAL via requesting branch

• Benefit: Minimizes inter-branch network traffic during order tracking

SUMINISTRA - Derived from SUCURSAL via supplying branch

• Benefit: Local stock update management

Full Replication

PRODUCTOR (Producers)

• Rationale: Small table with infrequent updates
• Benefit: Wine-producer joins remain local

Global Views for Transparency

All fragmented tables are accessible through UNION ALL views:

CREATE OR REPLACE VIEW V_VINO AS
  SELECT * FROM sobresaliente1.vino@link_to_node1
  UNION ALL
  SELECT * FROM sobresaliente2.vino@link_to_node2
  UNION ALL
  SELECT * FROM sobresaliente3.vino@link_to_node3
  UNION ALL
  SELECT * FROM sobresaliente4.vino@link_to_node4;

Views provide: V_CLIENTE, V_SUCURSAL, V_EMPLEADO, V_VINO, V_SOLICITA, V_PEDIDO, V_SUMINISTRA, V_PRODUCTOR

---

⚙️ Advanced Features

1. Distributed Referential Integrity

Challenge: Oracle doesn't support foreign keys across database links.

Solution: Custom triggers with global view validation

CREATE OR REPLACE TRIGGER trg_solicita_vino_exists
BEFORE INSERT OR UPDATE OF codigo_vino ON solicita
FOR EACH ROW
DECLARE
  v_count NUMBER;
BEGIN
  -- Validate wine exists across all nodes
  SELECT COUNT(*) INTO v_count 
  FROM V_VINO 
  WHERE codigo_vino = :NEW.codigo_vino;
  
  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20050, 
      'El vino con código ' || :NEW.codigo_vino || 
      ' no existe en ninguna región.');
  END IF;
END;

2. Complex Business Rules (25+ Triggers)

Stock Management

-- Real-time stock calculation
TRIGGER trg_suministra_recalc_stock
-- Stock = Production - Total Supplies
-- Uses delta-based updates to avoid mutating table errors

Territorial Restrictions

-- RESTRICCIÓN 9: Clients must use branches in their delegation
TRIGGER trg_solicita_delegacion

-- RESTRICCIÓN 19: Orders must target branches that distribute the wine
TRIGGER trg_pedido_proveedor_correcto

Temporal Constraints

-- RESTRICCIÓN 10: Supply dates must be chronological per client
TRIGGER trg_solicita_fecha_ordenada

-- RESTRICCIÓN 20: Order dates must follow request dates
-- (Implemented in stored procedure to avoid mutating table)

Data Protection

-- RESTRICCIÓN 6: Salaries can only increase
TRIGGER trg_empleado_salario_no_disminuir

-- RESTRICCIÓN 15: Wines with supply history cannot be deleted
TRIGGER trg_vino_no_eliminar_suministrado

3. Distributed Stored Procedures (15+)

Fragment Migration - Employee Transfer

CREATE OR REPLACE PROCEDURE trasladar_empleado(
    p_codigo_empleado IN empleado.codigo_empleado%TYPE,
    p_nueva_sucursal  IN sucursal.codigo_sucursal%TYPE,
    p_nueva_direccion IN empleado.direccion%TYPE DEFAULT NULL
) IS
    v_nodo_orig NUMBER; 
    v_nodo_dest NUMBER;
    v_datos     V_EMPLEADO%ROWTYPE;
BEGIN
    -- Determine source and destination nodes
    SELECT s.comunidad_autonoma 
    INTO v_com_orig
    FROM V_EMPLEADO e 
    JOIN V_SUCURSAL s ON e.codigo_sucursal = s.codigo_sucursal 
    WHERE e.codigo_empleado = p_codigo_empleado;
    
    v_nodo_orig := get_delegacion(v_com_orig);
    v_nodo_dest := get_delegacion(v_com_dest);
    
    -- If cross-node transfer, migrate data
    IF v_nodo_orig != v_nodo_dest THEN
        -- Fetch complete employee record
        SELECT * INTO v_datos FROM V_EMPLEADO 
        WHERE codigo_empleado = p_codigo_empleado;
        
        -- Delete from source node
        CASE v_nodo_orig
            WHEN 1 THEN DELETE FROM sobresaliente1.empleado 
                        WHERE codigo_empleado = p_codigo_empleado;
            -- ... other nodes
        END CASE;
        
        -- Insert into destination node  
        CASE v_nodo_dest
            WHEN 1 THEN INSERT INTO sobresaliente1.empleado VALUES (...);
            -- ... other nodes
        END CASE;
    END IF;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN ROLLBACK; RAISE;
END;

Global Uniqueness Validation

-- Ensure employee DNI is unique across all nodes
TRIGGER trg_empleado_unicidad_global
-- Queries V_EMPLEADO to check existence before insert

---

💻 SQL Competencies Demonstrated

Advanced PL/SQL

• ✅ Exception Handling: Try-catch blocks with specific error codes
• ✅ Dynamic SQL: CASE statements for multi-node operations
• ✅ Cursors: Implicit and explicit row processing
• ✅ Variables & Types: %TYPE, %ROWTYPE for type safety
• ✅ Control Structures: IF-ELSIF-ELSE, CASE, loops

Distributed Database Operations

• ✅ Database Links: Cross-node data access
• ✅ Global Views: UNION ALL for transparent distribution
• ✅ Transaction Management: COMMIT/ROLLBACK across nodes
• ✅ Distributed Queries: Multi-table JOINs spanning nodes

Trigger Expertise

• ✅ Row-level Triggers: BEFORE/AFTER INSERT/UPDATE/DELETE
• ✅ Statement-level Triggers: Batch operations
• ✅ Mutating Table Workarounds: Delta-based calculations
• ✅ Cross-schema Triggers: Distributed integrity enforcement

Complex Query Patterns

• ✅ Multi-table JOINs: 4+ tables with distributed data
• ✅ Subqueries: Correlated and non-correlated
• ✅ Aggregations: GROUP BY, SUM, COUNT with HAVING
• ✅ Set Operations: UNION ALL for data reconstruction
• ✅ Date Range Filtering: Temporal query optimization
• ✅ Parameterized Queries: Substitution variables

---

📁 Project Structure

├── 01_schema_base.sql                 # Core DDL (8.9 KB)
├── 02_sobresaliente1.sql              # Madrid node initial data (6.5 KB)
├── 02_sobresaliente2.sql              # Barcelona node initial data (6.1 KB)
├── 02_sobresaliente3.sql              # La Coruña node initial data (6.3 KB)
├── 02_sobresaliente4.sql              # Granada node initial data (6.3 KB)
├── 03_vistas_globales.sql             # Global view definitions (2.7 KB)
├── 04_logica_distribuida.sql          # Triggers & procedures (109 KB)
├── 05_consultas.sql                   # Complex query examples (3.4 KB)
├── diseño_conceptual.md               # Conceptual model documentation
├── diseño_logico.md                   # Logical model documentation
├── diseño_fragmentacion_asignacion.md # Fragmentation strategy
├── enunciado.md                       # Original requirements (Spanish)
├── entidad-relacion-esquema.png       # ER diagram
├── test_datos_iniciales.sql           # Initial data validation (3.8 KB)
├── test_final.sql                     # Integration tests (1.2 KB)
└── Documentación BDD Prácticas.pdf    # Complete technical report (Spanish)

Total Code: ~110 KB of SQL/PL-SQL

---

🌟 Implementation Highlights

1. Performance Optimization

95% Query Locality

• Fragmentation by comunidad_autonoma ensures most queries stay local
• Database links only invoked when business rules require cross-region data

Read Optimization

• Full replication of PRODUCTOR table
• Indexed foreign keys for join performance

Trade-offs Made

• Global uniqueness checks (employee DNI, director) require cross-node queries
• Distributed FK validation via triggers instead of native constraints
• Accept latency for writes requiring global coordination

2. Data Integrity

25+ Business Rules Enforced

• Territorial restrictions (clients use local branches)
• Temporal ordering (sequential dates for requests/orders)
• Stock validation (non-negative, within production limits)
• Employee constraints (salary increases only, unique director per branch)

Error Messaging All triggers provide specific, actionable error messages in Spanish:

RAISE_APPLICATION_ERROR(-20009, 
  'El cliente debe solicitar pedidos a una sucursal de su misma delegación territorial.' ||
  ' (Cliente: ' || v_comunidad_cliente || ' → Delegación ' || v_delegacion_cliente ||
  ', Sucursal: ' || v_comunidad_sucursal || ' → Delegación ' || v_delegacion_sucursal || ')');

3. Automation

Automatic Stock Updates Supply insertions/updates trigger delta-based stock recalculation:

-- Insert reduces stock
v_delta := :NEW.cantidad_suministrada;
UPDATE vino SET cantidad_stock = cantidad_stock - v_delta
WHERE codigo_vino = v_codigo_vino;

Cross-node Existence Validation All distributed foreign keys validated automatically on DML operations.

---

📝 Query Examples

Query 1: Regional Client Analysis

Requirement: List clients from Andalucía or Castilla-La Mancha who received "Tablas de Daimiel" wine between Jan-Sep 2025.

SELECT DISTINCT 
    c.nombre AS nombre_cliente, 
    c.domicilio AS direccion_cliente, 
    s.nombre AS nombre_sucursal, 
    s.ciudad AS ciudad_sucursal
FROM V_SOLICITA sol
JOIN V_CLIENTE c ON sol.codigo_cliente = c.codigo_cliente
JOIN V_SUCURSAL s ON sol.codigo_sucursal = s.codigo_sucursal
JOIN V_VINO v ON sol.codigo_vino = v.codigo_vino
WHERE v.marca = 'Tablas de Daimiel'
  AND (c.comunidad_autonoma = 'Andalucía' 
       OR c.comunidad_autonoma = 'Castilla-La Mancha')
  AND sol.fecha BETWEEN TO_DATE('01/01/2025', 'DD/MM/YYYY') 
                    AND TO_DATE('01/09/2025', 'DD/MM/YYYY');

Query 2: Producer Performance by Region

Requirement: For a given producer, list all wines and total quantities supplied to clients in Baleares, Extremadura, or País Valenciano.

SELECT 
    v.codigo_vino, 
    v.marca, 
    v.anio_cosecha, 
    NVL(regional_totals.total_suministrada, 0) AS cantidad_total
FROM V_VINO v
LEFT JOIN (
    SELECT 
        s.codigo_vino, 
        SUM(s.cantidad_solicitada) AS total_suministrada
    FROM V_SOLICITA s
    JOIN V_CLIENTE c ON c.codigo_cliente = s.codigo_cliente
    WHERE c.comunidad_autonoma IN ('Baleares', 'Extremadura', 'Pais Valenciano')
    GROUP BY s.codigo_vino
) regional_totals ON v.codigo_vino = regional_totals.codigo_vino
WHERE v.codigo_productor = &p_codigo_productor;

Query 3: D.O. Preference Analysis

Requirement: For a given branch, list clients, their type, and total Rioja/Albariño wine supplied to each.

SELECT 
    c.nombre, 
    c.tipo, 
    client_totals.cantidad_total
FROM (
    SELECT 
        s.codigo_cliente, 
        SUM(s.cantidad_solicitada) AS cantidad_total
    FROM V_SOLICITA s
    JOIN V_VINO v ON v.codigo_vino = s.codigo_vino
    WHERE s.codigo_sucursal = &p_codigo_sucursal
      AND (v.denominacion_origen = 'Rioja' 
           OR v.denominacion_origen = 'Albariño')
    GROUP BY s.codigo_cliente
) client_totals
JOIN V_CLIENTE c ON client_totals.codigo_cliente = c.codigo_cliente;

---

🧪 Testing & Validation

Initial Data Consistency Tests (test_datos_iniciales.sql)

• ✅ Stock calculations match expected values
• ✅ Delegation assignments are correct
• ✅ Cross-node constraints properly enforced

Procedure Tests (test_final.sql)

Employee Management

• Transfer between nodes (data migration)
• Salary updates (non-decreasing validation)
• Director assignment (global uniqueness check)

Order Processing

• Client request creation (delegation validation)
• Inter-branch order creation (proper routing)
• Supply recording (stock update verification)

Deletion Workflows

• Wine deletion (supply history check)
• Producer deletion (aggregate validation)
• Employee deletion (director status check)

Constraint Validation

Temporal Constraints

-- Test sequential date enforcement for client requests
-- Expected: Error if new request date < last request date

Territorial Constraints

-- Test client ordering wine from wrong delegation
-- Expected: Error with delegation mismatch details

Stock Constraints

-- Test supply quantity exceeding available stock
-- Expected: Error with calculated stock deficit

---

🎓 Learning Outcomes

This project demonstrates advanced proficiency in:

Database Design

• Normalization: 3NF schema design
• ER Modeling: Complex many-to-many relationships
• Distributed Design: Fragmentation and replication strategies

SQL Development

• PL/SQL Programming: Procedures, functions, packages
• Trigger Development: Row and statement-level automation
• Query Optimization: Join strategies, index usage

Distributed Systems

• CAP Theorem: Consistency vs. Availability trade-offs
• Data Locality: Partition strategy for 95% local access
• Network Efficiency: Minimizing cross-node communication

Business Analysis

• Requirements Translation: Business rules to technical constraints
• Data Modeling: Real-world entities to relational schema
• Process Automation: Trigger-based workflows

---

📈 Key Statistics

• 4 Distributed Nodes across Spain
• 19 Autonomous Communities served
• 8 Core Entities with 3 transactional tables
• 25+ Business Rules enforced via triggers
• 15+ Stored Procedures for distributed operations
• 7 Global Views for data transparency
• 109 KB of PL/SQL code for logic layer
• 95% Query Locality achieved through fragmentation

---

📜 License & Attribution

Course: Distributed Database Systems (Base de Datos Distribuidas)
Academic Year: 2025-2026, Q1
Institution: Universidad de Granada Project: Wine Distribution Database System

The business scenario and requirements were provided as part of the course assignment (enunciado.md). The database design, implementation, and all code are original work by the project authors.