Tecnofumigacion de Occidente

📄

Planteamiento del Problema

Contexto del sistema de base de datos

⚠ Caso de Estudio

La empresa "Tecnofumigacion de Occidente", tiene como objetivo la fumigacion de parcelas agricolas utilizando una flotilla de 3 drones.

Para llevar un registro de sus datos, requiere una base de datos que le permita administrar la informacion de sus clientes (agricultores), los drones utilizados en fumigacion, las parcelas agricolas, y los servicios de fumigacion realizados en el ciclo anual.

Cada servicio se lleva a cabo en una parcela determinada, con un dron especifico, y es solicitado por un cliente, que a su vez puede poseer mas de una parcela.

Objetivo: Convertir el modelo entidad relacion proporcionado a modelo relacional utilizando las reglas de transformacion necesarias.

📊

Modelo Entidad-Relacion (E-R)

Diagrama conceptual del sistema

Diagrama E-R - Tecnofumigacion de Occidente

💡 Relaciones Identificadas

Dueno - Parcela (Tiene): Un dueno puede tener muchas parcelas (1:N)
Dueno - Servicio (Solicita): Un dueno solicita servicios (1:N)
Dron - Servicio (Atiende): Un dron atiende un servicio (1:1)
Parcela - Servicio (Para): Un servicio es para una parcela (1:1)

🗃

Modelo Relacional

Conversion del modelo E-R a tablas relacionales

✈ DRON

🔑 idDron (PK) - INT
Modelo - VARCHAR(50)
Capacidad - DECIMAL(5,2)
Estado - VARCHAR(20)

👤 DUENO (Cliente)

🔑 RFC (PK) - VARCHAR(13)
Nombre - VARCHAR(100)
Contacto - VARCHAR(15)
Domicilio - VARCHAR(200)

🌾 PARCELA

🔑 IdParcela (PK) - INT
Siembra - VARCHAR(50)
Ubicacion - VARCHAR(200)
Extension - DECIMAL(10,2)
🔗 RFC_Dueno (FK) - VARCHAR(13)

📋 SERVICIO

🔑 idServicio (PK) - INT
Fecha - DATE
TipoFumigacion - VARCHAR(50)
Costo - DECIMAL(10,2)
🔗 idDron (FK) - INT
🔗 IdParcela (FK) - INT
🔗 RFC_Dueno (FK) - VARCHAR(13)

💻

Script de Creacion de Base de Datos

Codigo SQL para crear la estructura y datos de prueba

Crear Base de Datos y Tablas

CREATE DATABASE tecnofumigacion CHARACTER SET utf8mb4;
USE tecnofumigacion;

-- Tabla Dron
CREATE TABLE Dron (
    idDron INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Modelo VARCHAR(50) NOT NULL,
    Capacidad DECIMAL(5,2) NOT NULL,
    Estado VARCHAR(20) NOT NULL
);

-- Tabla Dueno (Cliente/Agricultor)
CREATE TABLE Dueno (
    RFC VARCHAR(13) PRIMARY KEY,
    Nombre VARCHAR(100) NOT NULL,
    Contacto VARCHAR(15),
    Domicilio VARCHAR(200)
);

-- Tabla Parcela
CREATE TABLE Parcela (
    IdParcela INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Siembra VARCHAR(50) NOT NULL,
    Ubicacion VARCHAR(200) NOT NULL,
    Extension DECIMAL(10,2) NOT NULL,
    RFC_Dueno VARCHAR(13) NOT NULL,
    FOREIGN KEY (RFC_Dueno) REFERENCES Dueno(RFC)
);

-- Tabla Servicio
CREATE TABLE Servicio (
    idServicio INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Fecha DATE NOT NULL,
    TipoFumigacion VARCHAR(50) NOT NULL,
    Costo DECIMAL(10,2) NOT NULL,
    idDron INT UNSIGNED NOT NULL,
    IdParcela INT UNSIGNED NOT NULL,
    RFC_Dueno VARCHAR(13) NOT NULL,
    FOREIGN KEY (idDron) REFERENCES Dron(idDron),
    FOREIGN KEY (IdParcela) REFERENCES Parcela(IdParcela),
    FOREIGN KEY (RFC_Dueno) REFERENCES Dueno(RFC)
);

Insertar Datos de Prueba

-- Insertar Drones
INSERT INTO Dron VALUES (1, 'DJI Agras T30', 30.00, 'Activo');
INSERT INTO Dron VALUES (2, 'DJI Agras T10', 10.00, 'Activo');
INSERT INTO Dron VALUES (3, 'XAG P100', 40.00, 'Mantenimiento');

-- Insertar Duenos
INSERT INTO Dueno VALUES ('GARA850101ABC', 'Juan Garcia Ramirez', '3331234567', 'Av. Hidalgo 123, Guadalajara');
INSERT INTO Dueno VALUES ('LOPM900215XYZ', 'Maria Lopez Mendoza', '3339876543', 'Calle Morelos 456, Zapopan');
INSERT INTO Dueno VALUES ('HECS780530DEF', 'Carlos Hernandez Soto', '3335551234', 'Blvd. Vallarta 789, Tlaquepaque');
INSERT INTO Dueno VALUES ('RORS650812GHI', 'Rosa Rodriguez Silva', '3337778899', 'Av. Patria 321, Tonala');

-- Insertar Parcelas
INSERT INTO Parcela VALUES (1, 'Maiz', 'Km 15 Carretera a Chapala', 25.50, 'GARA850101ABC');
INSERT INTO Parcela VALUES (2, 'Aguacate', 'Km 8 Carretera a Tequila', 15.00, 'GARA850101ABC');
INSERT INTO Parcela VALUES (3, 'Agave', 'Km 20 Carretera a Amatitan', 50.00, 'LOPM900215XYZ');
INSERT INTO Parcela VALUES (4, 'Tomate', 'Km 5 Carretera a Tlajomulco', 8.75, 'HECS780530DEF');
INSERT INTO Parcela VALUES (5, 'Chile', 'Km 12 Carretera a Chapala', 12.30, 'HECS780530DEF');
INSERT INTO Parcela VALUES (6, 'Frijol', 'Km 25 Carretera a Lagos', 30.00, 'RORS650812GHI');

-- Insertar Servicios
INSERT INTO Servicio VALUES (1, '2024-03-15', 'Herbicida', 2500.00, 1, 1, 'GARA850101ABC');
INSERT INTO Servicio VALUES (2, '2024-03-18', 'Insecticida', 1800.00, 2, 3, 'LOPM900215XYZ');
INSERT INTO Servicio VALUES (3, '2024-03-20', 'Fungicida', 3200.00, 1, 2, 'GARA850101ABC');
INSERT INTO Servicio VALUES (4, '2024-04-01', 'Herbicida', 1200.00, 2, 4, 'HECS780530DEF');
INSERT INTO Servicio VALUES (5, '2024-04-05', 'Fertilizante', 4500.00, 1, 3, 'LOPM900215XYZ');
INSERT INTO Servicio VALUES (6, '2024-04-10', 'Insecticida', 2800.00, 1, 6, 'RORS650812GHI');
INSERT INTO Servicio VALUES (7, '2024-04-15', 'Herbicida', 1500.00, 2, 5, 'HECS780530DEF');
INSERT INTO Servicio VALUES (8, '2024-04-20', 'Fungicida', 2000.00, 2, 1, 'GARA850101ABC');

✍

Ejercicio 1: Comandos DML

Identificacion de comandos de manipulacion de datos

Comandos DML en Tecnofumigacion

Valor: 1 punto

Pregunta: Menciona que comando DML utilizarias para cada una de las siguientes operaciones en la base de datos de Tecnofumigacion:

a) Agregar un nuevo dron a la flotilla

b) Consultar todos los servicios realizados en abril

c) Cambiar el estado de un dron a "Mantenimiento"

d) Eliminar una parcela que ya no sera fumigada

✅ Respuesta:

Operacion	Comando DML
a) Agregar nuevo dron	INSERT
b) Consultar servicios	SELECT
c) Cambiar estado	UPDATE
d) Eliminar parcela	DELETE

🔢

Ejercicio 2: Tipos de Datos

Identificacion de tipos de datos en SQL

Tipos de Datos en las Tablas

Valor: 1 punto

Pregunta: Observa la estructura de las tablas de Tecnofumigacion e indica que tipo de dato corresponde a cada columna:

✅ Respuesta:

Columna	Tabla	Tipo de Dato
RFC	Dueno	VARCHAR(13)
Fecha	Servicio	DATE
idDron	Dron	INT
Capacidad	Dron	DECIMAL(5,2)
Nombre	Dueno	VARCHAR(100)
Extension	Parcela	DECIMAL(10,2)
Costo	Servicio	DECIMAL(10,2)

🗑

Ejercicio 3: TRUNCATE vs DELETE

Diferencias entre comandos de eliminacion

Eliminacion de Registros

Valor: 1 punto

Pregunta: El administrador de Tecnofumigacion necesita eliminar todos los registros de servicios del ano anterior para iniciar el nuevo ciclo. ¿Que diferencia existe entre usar TRUNCATE y DELETE para esta operacion? ¿Cual recomendarias y por que?

✅ Respuesta:

Aspecto	DELETE	TRUNCATE
Recuperacion	Permite ROLLBACK	No permite ROLLBACK individual
Velocidad	Mas lento (fila por fila)	Mas rapido
Log transacciones	Registra cada eliminacion	Solo marca paginas
Clausula WHERE	Permite condiciones	No permite condiciones
Triggers	Activa triggers	No activa triggers

💡 Recomendacion:

Si necesita eliminar TODOS los servicios y esta seguro: TRUNCATE TABLE Servicio; es mas eficiente.

Si solo quiere eliminar servicios de un ano especifico o quiere poder deshacer: DELETE FROM Servicio WHERE YEAR(Fecha) = 2023;

🔒

Ejercicio 4: Atributo UNIQUE

Restriccion de valores unicos

Restriccion UNIQUE

Valor: 1 punto

Pregunta: En la tabla Dueno, el RFC ya es PRIMARY KEY. Sin embargo, queremos asegurar que el Contacto (telefono) tambien sea unico para cada cliente. Escribe la sentencia SQL para agregar esta restriccion.

✅ Respuesta:

ALTER TABLE Dueno
ADD CONSTRAINT unique_contacto UNIQUE (Contacto);

Pregunta adicional: ¿Para que se utiliza el atributo UNIQUE?

Respuesta: Se utiliza para garantizar que no se inserten valores duplicados en una columna especifica, permitiendo valores NULL (a diferencia de PRIMARY KEY que no permite NULL).

📝

Ejercicio 5: VARCHAR vs CHAR

Diferencias entre tipos de cadenas

Tipos de Cadenas de Caracteres

Valor: 1 punto

Pregunta: En la tabla Dueno, el RFC tiene exactamente 13 caracteres siempre. El Nombre puede variar entre 10 y 100 caracteres. Explica por que RFC deberia ser CHAR(13) y Nombre deberia ser VARCHAR(100).

✅ Respuesta:

Columna	Tipo Recomendado	Justificacion
RFC	CHAR(13)	El RFC siempre tiene exactamente 13 caracteres. CHAR es mas eficiente para longitudes fijas porque no necesita almacenar informacion sobre la longitud.
Nombre	VARCHAR(100)	El nombre varia en longitud. VARCHAR solo ocupa el espacio necesario + 1-2 bytes para almacenar la longitud, ahorrando espacio.

📊 Ejemplo de Almacenamiento:

CHAR(13) con "GARA850101ABC" → Ocupa 13 bytes
VARCHAR(100) con "Juan Garcia" → Ocupa 11 + 1 = 12 bytes
CHAR(100) con "Juan Garcia" → Ocuparia 100 bytes (desperdicio de 89 bytes)

📚

Ejercicios 6-7: Conceptos SQL

Definiciones y herramientas

Ejercicio 6: Significado de SQL

Valor: 0.5 puntos

Pregunta: ¿Que significan las siglas SQL y para que se utiliza en el contexto de Tecnofumigacion de Occidente?

✅ Respuesta:

SQL = Structured Query Language (Lenguaje de Consulta Estructurada)

En Tecnofumigacion: Se utiliza para crear, modificar y consultar la base de datos que administra la informacion de clientes, drones, parcelas y servicios de fumigacion.

Ejercicio 7: MySQL Workbench

Valor: 0.5 puntos

Pregunta: El equipo de Tecnofumigacion necesita una herramienta visual para administrar su base de datos MySQL. Define que es MySQL Workbench y menciona 3 tareas que podrian realizar con esta herramienta.

✅ Respuesta:

MySQL Workbench: Es una herramienta visual de diseno de bases de datos que integra desarrollo, administracion, diseno y mantenimiento para MySQL.

Tareas que pueden realizar:

Disenar visualmente el diagrama E-R de la base de datos
Ejecutar consultas SQL para generar reportes de servicios
Administrar usuarios y permisos de acceso a la base de datos
Realizar respaldos (backups) de la informacion
Monitorear el rendimiento del servidor de base de datos

🔍

Ejercicio 8: Consultas Basicas

Practica de sentencias SELECT

Consultas SELECT

Valor: 2 puntos

Instrucciones: Escribe las consultas SQL para obtener la siguiente informacion:

a) Listar todos los drones de la flotilla:

SELECT * FROM Dron;

Resultado:

idDron	Modelo	Capacidad	Estado
1	DJI Agras T30	30.00	Activo
2	DJI Agras T10	10.00	Activo
3	XAG P100	40.00	Mantenimiento

b) Mostrar nombre y contacto de todos los clientes:

SELECT Nombre, Contacto FROM Dueno;

Resultado:

Nombre	Contacto
Juan Garcia Ramirez	3331234567
Maria Lopez Mendoza	3339876543
Carlos Hernandez Soto	3335551234
Rosa Rodriguez Silva	3337778899

c) Obtener las parcelas que cultivan Maiz:

SELECT * FROM Parcela WHERE Siembra = 'Maiz';

Resultado:

IdParcela	Siembra	Ubicacion	Extension	RFC_Dueno
1	Maiz	Km 15 Carretera a Chapala	25.50	GARA850101ABC

d) Listar servicios con costo mayor a $2000:

SELECT * FROM Servicio WHERE Costo > 2000;

Resultado:

idServicio	Fecha	TipoFumigacion	Costo	idDron	IdParcela
1	2024-03-15	Herbicida	2500.00	1	1
3	2024-03-20	Fungicida	3200.00	1	2
5	2024-04-05	Fertilizante	4500.00	1	3
6	2024-04-10	Insecticida	2800.00	1	6

e) Mostrar servicios realizados en abril de 2024:

SELECT * FROM Servicio
WHERE Fecha BETWEEN '2024-04-01' AND '2024-04-30';

Resultado:

idServicio	Fecha	TipoFumigacion	Costo	idDron	IdParcela
4	2024-04-01	Herbicida	1200.00	2	4
5	2024-04-05	Fertilizante	4500.00	1	3
6	2024-04-10	Insecticida	2800.00	1	6
7	2024-04-15	Herbicida	1500.00	2	5
8	2024-04-20	Fungicida	2000.00	2	1

🛠

Ejercicio 9: INSERT, UPDATE, DELETE

Practica de manipulacion de datos

Operaciones DML

Valor: 2 puntos

Instrucciones: Escribe las sentencias SQL para las siguientes operaciones:

a) Insertar un nuevo dron a la flotilla:

INSERT INTO Dron (Modelo, Capacidad, Estado)
VALUES ('DJI Agras T40', 50.00, 'Activo');

b) Actualizar el estado del dron 3 a "Activo":

UPDATE Dron
SET Estado = 'Activo'
WHERE idDron = 3;

c) Eliminar el servicio con id 8:

DELETE FROM Servicio
WHERE idServicio = 8;

d) Actualizar el contacto del cliente con RFC 'GARA850101ABC':

UPDATE Dueno
SET Contacto = '3332221100'
WHERE RFC = 'GARA850101ABC';

e) Insertar una nueva parcela para el cliente Maria Lopez:

INSERT INTO Parcela (Siembra, Ubicacion, Extension, RFC_Dueno)
VALUES ('Limon', 'Km 30 Carretera a Colima', 18.50, 'LOPM900215XYZ');

f) Actualizar el costo de todos los servicios de tipo 'Herbicida' aumentando 10%:

UPDATE Servicio
SET Costo = Costo * 1.10
WHERE TipoFumigacion = 'Herbicida';