Oracle Distributed Database: Vertical Fragmentation, Database Links, and MySQL Backup Restoration

1. Distributed Database Configuration and Vertical Fragmentation in Oracle

1.1 Network Configuration

File name: tnsnames.ora
Command to execute: tnsping
Database link to TOLOSA:

CREATE DATABASE LINK TOLOSA
CONNECT TO "ASR32aa"
IDENTIFIED BY "ASR32aa"
USING 'OracleSqlserver';

Fragmentation type: Vertical fragmentation

1.2 Data Insertion into Fragments

INSERT INTO dbo.CLIENTES1@TOLOSA
SELECT cod_cli, Nombre, Apellido, Email, Codigo_postal, Poblacion
FROM CLIENTES;

INSERT INTO dbo.CLIENTES2@EIBAR
SELECT cod_cli, Pelicula, Hobby, Otros_intereses
FROM CLIENTES;

1.3 Reconstructing the Data from Fragments

SELECT z1.cod_cli, z1.Nombre, z1.Apellido, z1.Email, z1.Codigo_postal, z1.Poblacion,
       z2.Pelicula, z2.Hobby, z2.Otros_intereses
FROM dbo.CLIENTES1@TOLOSA z1
JOIN dbo.CLIENTES2@EIBAR z2 ON z1.cod_cli = z2.cod_cli;

1.4 Anonymous PL/SQL Block

DECLARE
  v_cod_cli INTEGER;
BEGIN
  v_cod_cli := &Introduce_Cod_Cliente;

  DELETE FROM dbo.CLIENTES1@TOLOSA z1
  WHERE z1.cod_cli = v_cod_cli;

  DELETE FROM dbo.CLIENTES2@EIBAR z2
  WHERE z2.cod_cli = v_cod_cli;
END;

1.5 Horizontal Distribution by Client Code Parity

INSERT INTO dbo.CLIENTES3@TOLOSA
SELECT *
FROM CLIENTES
WHERE MOD(cod_cli, 2) = 0;

INSERT INTO dbo.CLIENTES4@EIBAR
SELECT *
FROM CLIENTES
WHERE MOD(cod_cli, 2) = 1;

1.6 Querying Clients from Irun and Oiartzun

SELECT *
FROM dbo.CLIENTES3@TOLOSA
WHERE Poblacion = 'Oiartzun' OR Poblacion = 'Irun'
UNION
SELECT *
FROM dbo.CLIENTES4@EIBAR
WHERE Poblacion = 'Oiartzun' OR Poblacion = 'Irun';

1.7 Function to Change the Film

CREATE OR REPLACE FUNCTION USF_change_Film (
  p_cod_cli IN INTEGER,
  p_pelicula IN VARCHAR
) RETURN VARCHAR AS
  v_pelicula_vieja VARCHAR(30);
BEGIN
  IF MOD(p_cod_cli, 2) = 0 THEN
    SELECT Pelicula INTO v_pelicula_vieja
    FROM dbo.CLIENTES3@TOLOSA
    WHERE cod_cli = p_cod_cli;

    UPDATE dbo.CLIENTES3@TOLOSA
    SET Pelicula = p_pelicula
    WHERE cod_cli = p_cod_cli;
  ELSE
    SELECT Pelicula INTO v_pelicula_vieja
    FROM dbo.CLIENTES4@EIBAR
    WHERE cod_cli = p_cod_cli;

    UPDATE dbo.CLIENTES4@EIBAR
    SET Pelicula = p_pelicula
    WHERE cod_cli = p_cod_cli;
  END IF;

  RETURN v_pelicula_vieja;
END;

2. Backup and Restoration from MySQL

2.1 Creating the Backup

mysqldump -h 10.14.4.125 -u backupadmin -p --databases BD1 BD2 > D:\examen\copiaseguridad.sql

2.2 Restoring the Backup

mysql -h 10.14.4.125 -u backupadmin BD2 < D:\examen\copiaseguridad.sql