Esquema de Base de Datos - Futuro Seguro CA
1. Tablas
Tabla Socios
CREATE TABLE Socios (
id INT PRIMARY KEY IDENTITY(1,1),
nombre VARCHAR(100) NOT NULL,
email VARCHAR(100),
fecha_ingreso DATE NOT NULL,
estado VARCHAR(20) NOT NULL
);
Tabla Prestamos
CREATE TABLE Prestamos (
id INT PRIMARY KEY IDENTITY(1,1),
socio_id INT NOT NULL,
monto DECIMAL(10,2) NOT NULL,
tasa_interes DECIMAL(5,2) NOT NULL,
fecha_inicio DATE NOT NULL,
plazo_meses INT NOT NULL,
saldo_pendiente DECIMAL(10,2) NOT NULL,
estado VARCHAR(20) NOT NULL,
fecha_ultimo_pago DATE,
FOREIGN KEY (socio_id) REFERENCES Socios(id)
);
Tabla Pagos
CREATE TABLE Pagos (
id INT PRIMARY KEY IDENTITY(1,1),
prestamo_id INT NOT NULL,
fecha_pago DATE NOT NULL,
monto_pagado DECIMAL(10,2) NOT NULL,
FOREIGN KEY (prestamo_id) REFERENCES Prestamos(id)
);
Tabla Bitacora
CREATE TABLE Bitacora (
id INT PRIMARY KEY IDENTITY(1,1),
usuario VARCHAR(50) NOT NULL,
accion VARCHAR(100) NOT NULL,
tabla_afectada VARCHAR(50) NOT NULL,
id_registro INT NOT NULL,
fecha DATETIME NOT NULL DEFAULT GETDATE()
);
Tabla ProyeccionIngresos
CREATE TABLE ProyeccionIngresos (
id INT PRIMARY KEY IDENTITY(1,1),
prestamo_id INT NOT NULL,
socio_id INT NOT NULL,
nombre_socio VARCHAR(100) NOT NULL,
mes_proyectado VARCHAR(20) NOT NULL,
monto_estimado DECIMAL(10,2) NOT NULL,
FOREIGN KEY (prestamo_id) REFERENCES Prestamos(id),
FOREIGN KEY (socio_id) REFERENCES Socios(id)
);
2. Inserts
Datos de Socios
INSERT INTO Socios (nombre, email, fecha_ingreso, estado) VALUES
('Ana Morales', '[email protected]', '2022-01-15', 'Activo'),
('Luis García', '[email protected]', '2021-03-10', 'Activo'),
('Marta Díaz', '[email protected]', '2020-06-20', 'Inactivo'),
('Carlos Pérez', '[email protected]', '2019-11-05', 'Activo'),
('Sofía Herrera', '[email protected]', '2023-02-01', 'Activo'),
('Pedro Sánchez', '[email protected]', '2021-09-12', 'Activo'),
('Lucía Gómez', '[email protected]', '2020-12-25', 'Inactivo'),
('Diego López', '[email protected]', '2021-07-30', 'Activo'),
('Elena Ruiz', '[email protected]', '2022-05-18', 'Activo'),
('Manuel Torres', '[email protected]', '2020-03-14', 'Activo'),
('Andrea Romero', '[email protected]', '2023-01-10', 'Activo'),
('Tomás Navarro', '[email protected]', '2022-09-28', 'Activo'),
('Clara Mendoza', '[email protected]', '2019-08-17', 'Inactivo'),
('Mario Vargas', '[email protected]', '2021-04-03', 'Activo'),
('Daniela Paredes', '[email protected]', '2020-02-11', 'Activo'),
('Jorge Rivas', '[email protected]', '2023-03-07', 'Activo'),
('Valentina Castro', '[email protected]', '2021-06-26', 'Activo'),
('Héctor Soto', '[email protected]', '2020-10-01', 'Activo'),
('Mariana León', '[email protected]', '2022-12-05', 'Activo'),
('Raúl Silva', '[email protected]', '2019-05-21', 'Inactivo');
Datos de Pagos
INSERT INTO Pagos (prestamo_id, fecha_pago, monto_pagado) VALUES
(1, '2023-01-15', 2500.00),
(2, '2022-10-10', 1000.00),
(3, '2022-02-20', 7000.00),
(5, '2023-03-15', 3000.00),
(7, '2022-11-01', 4000.00),
(8, '2023-04-05', 1000.00),
(10, '2020-10-10', 1500.00),
(11, '2022-09-12', 3000.00),
(12, '2023-03-01', 2000.00),
(14, '2022-08-03', 1400.00),
(15, '2023-05-15', 300.00),
(18, '2022-09-18', 1800.00),
(19, '2022-11-12', 4000.00),
(20, '2023-04-10', 2000.00),
(4, '2023-05-01', 1000.00),
(6, '2021-06-10', 2000.00),
(9, '2023-06-01', 500.00),
(13, '2021-07-07', 5500.00),
(16, '2021-03-09', 2000.00),
(17, '2023-05-10', 200.00);
INSERT INTO Bitacora (usuario, accion, tabla_afectada, id_registro, fecha) VALUES
('admin', 'INSERT', 'Socios', 1, GETDATE()),
('admin', 'INSERT', 'Prestamos', 1, GETDATE()),
('admin', 'INSERT', 'Pagos', 1, GETDATE()),
('user1', 'UPDATE', 'Prestamos', 5, GETDATE()),
('user2', 'DELETE', 'Pagos', 8, GETDATE()),
('admin', 'INSERT', 'Socios', 20, GETDATE()),
('admin', 'INSERT', 'ProyeccionIngresos', 3, GETDATE());
INSERT INTO ProyeccionIngresos (prestamo_id, socio_id, nombre_socio, mes_proyectado, monto_estimado) VALUES
(1, 1, 'Ana Morales', '2024-06', 2500.00),
(2, 2, 'Luis García', '2024-07', 1000.00),
(5, 5, 'Sofía Herrera', '2024-06', 1500.00),
(8, 8, 'Diego López', '2024-08', 2000.00),
(11, 11, 'Andrea Romero', '2024-06', 1000.00);
3. Usuarios, Roles y Permisos
Creación de Logins
CREATE LOGIN admin_db WITH PASSWORD = 'Admin123!';
CREATE LOGIN cajero_db WITH PASSWORD = 'Cajero123!';
CREATE LOGIN lector_db WITH PASSWORD = 'Lector123!';
GO
Asignación de Permisos
USE [Futuro seguro CA];
GO
Crear usuarios de base de datos
CREATE USER admin_db FOR LOGIN admin_db;
CREATE USER cajero_db FOR LOGIN cajero_db;
CREATE USER lector_db FOR LOGIN lector_db;
GO
Crear roles
CREATE ROLE rol_admin;
CREATE ROLE rol_cajero;
CREATE ROLE rol_lector;
GO
Asignar usuarios a roles
EXEC sp_addrolemember 'rol_admin', 'admin_db';
EXEC sp_addrolemember 'rol_cajero', 'cajero_db';
EXEC sp_addrolemember 'rol_lector', 'lector_db';
GO
Asignar permisos a los roles
rol_admin: Control total sobre la base de datos
GRANT CONTROL ON DATABASE::[Futuro seguro CA] TO rol_admin;
rol_cajero: SELECT, INSERT, UPDATE en Socios, Prestamos, Pagos
GRANT SELECT, INSERT, UPDATE ON dbo.Socios TO rol_cajero;
GRANT SELECT, INSERT, UPDATE ON dbo.Prestamos TO rol_cajero;
GRANT SELECT, INSERT, UPDATE ON dbo.Pagos TO rol_cajero;
Crear vista para lector (si no existe aún)
IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'vista_PrestamosConSocio')
BEGIN
EXEC ('CREATE VIEW vista_PrestamosConSocio AS
SELECT p.id AS prestamo_id, s.nombre AS socio, p.monto, p.fecha_inicio
FROM Prestamos p
JOIN Socios s ON p.socio_id = s.id');
END
GO
rol_lector: SELECT solo sobre vistas
GRANT SELECT ON OBJECT::vista_PrestamosConSocio TO rol_lector;
GO
4. Procedimientos Almacenados
sp_crear_prestamo
CREATE PROCEDURE sp_crear_prestamo
@socio_id INT,
@monto DECIMAL(10, 2),
@tasa DECIMAL(5, 2),
@plazo INT
AS
BEGIN
DECLARE @fecha_inicio DATE = GETDATE();
INSERT INTO Prestamos (socio_id, monto, tasa_interes, fecha_inicio, plazo_meses, saldo_pendiente, estado)
VALUES (@socio_id, @monto, @tasa, @fecha_inicio, @plazo, @monto, 'Activo');
PRINT 'Préstamo creado exitosamente.';
END;
GO
sp_registrar_pago
CREATE PROCEDURE sp_registrar_pago
@prestamo_id INT,
@monto DECIMAL(10, 2)
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Prestamos WHERE id = @prestamo_id AND estado = 'Activo')
BEGIN
PRINT 'El préstamo no existe o no está activo.';
RETURN;
END;
INSERT INTO Pagos (prestamo_id, fecha_pago, monto_pagado)
VALUES (@prestamo_id, GETDATE(), @monto);
UPDATE Prestamos
SET saldo_pendiente = saldo_pendiente - @monto,
fecha_ultimo_pago = GETDATE()
WHERE id = @prestamo_id;
PRINT 'Pago registrado exitosamente.';
END;
GO
sp_recalcular_saldo
CREATE PROCEDURE sp_recalcular_saldo
@prestamo_id INT
AS
BEGIN
DECLARE @monto_total DECIMAL(10, 2);
DECLARE @monto_pagado DECIMAL(10, 2);
SELECT @monto_total = monto
FROM Prestamos
WHERE id = @prestamo_id;
SELECT @monto_pagado = ISNULL(SUM(monto_pagado), 0)
FROM Pagos
WHERE prestamo_id = @prestamo_id;
UPDATE Prestamos
SET saldo_pendiente = @monto_total - @monto_pagado
WHERE id = @prestamo_id;
PRINT 'Saldo recalculado exitosamente.';
END;
GO
sp_generar_proyeccion
CREATE PROCEDURE sp_generar_proyeccion
@meses INT
AS
BEGIN
DECLARE @prestamo_id INT, @socio_id INT, @nombre_socio VARCHAR(100), @monto_estimado DECIMAL(10, 2);
DECLARE @mes_proyectado VARCHAR(20), @contador INT = 1;
DECLARE cursor_prestamos CURSOR FOR
SELECT p.id, s.id, s.nombre, p.monto / p.plazo_meses AS monto_mensual
FROM Prestamos p
JOIN Socios s ON p.socio_id = s.id
WHERE p.estado = 'Activo';
OPEN cursor_prestamos;
FETCH NEXT FROM cursor_prestamos INTO @prestamo_id, @socio_id, @nombre_socio, @monto_estimado;
WHILE @@FETCH_STATUS = 0 AND @contador <= @meses
BEGIN
SET @mes_proyectado = FORMAT(DATEADD(MONTH, @contador, GETDATE()), 'MMMM yyyy');
INSERT INTO ProyeccionIngresos (prestamo_id, socio_id, nombre_socio, mes_proyectado, monto_estimado)
VALUES (@prestamo_id, @socio_id, @nombre_socio, @mes_proyectado, @monto_estimado);
SET @contador = @contador + 1;
FETCH NEXT FROM cursor_prestamos INTO @prestamo_id, @socio_id, @nombre_socio, @monto_estimado;
END;
CLOSE cursor_prestamos;
DEALLOCATE cursor_prestamos;
PRINT 'Proyección de ingresos generada exitosamente.';
END;
GO
5. Funciones
fn_calcular_interes
CREATE FUNCTION fn_calcular_interes(
@monto DECIMAL(10, 2),
@tasa DECIMAL(5, 2),
@plazo INT
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
-- Fórmula de interés simple: Total = Monto + (Monto * Tasa * (Plazo / 12))
DECLARE @total DECIMAL(10, 2);
SET @total = @monto + (@monto * @tasa / 100 * (@plazo / 12.0));
RETURN @total;
END;
GO
fn_estado_prestamo
CREATE FUNCTION fn_estado_prestamo(
@prestamo_id INT
)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @estado VARCHAR(20);
-- Consultar el estado del préstamo desde la tabla Prestamos
SELECT @estado = estado
FROM Prestamos
WHERE id = @prestamo_id;
-- Retornar el estado encontrado, o 'DESCONOCIDO' si no se encuentra
RETURN ISNULL(@estado, 'DESCONOCIDO');
END;
GO
6. Triggers Obligatorios
trg_prevenir_pagos_duplicados
CREATE TRIGGER trg_prevenir_pagos_duplicados
ON Pagos
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (
SELECT 1
FROM Pagos p
JOIN inserted i ON p.prestamo_id = i.prestamo_id AND p.fecha_pago = i.fecha_pago
)
BEGIN
RAISERROR ('No se puede registrar más de un pago en el mismo día para el mismo préstamo.', 16, 1);
ROLLBACK TRANSACTION;
END;
END;
GO
trg_bloquear_prestamo_en_mora
CREATE TRIGGER trg_bloquear_prestamo_en_mora
ON Prestamos
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (
SELECT 1
FROM Socios s
JOIN Prestamos p ON s.id = p.socio_id
WHERE p.estado = 'En Mora' AND p.socio_id IN (SELECT socio_id FROM inserted)
)
BEGIN
RAISERROR ('No se pueden crear nuevos préstamos para socios con préstamos en mora.', 16, 1);
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
INSERT INTO Prestamos (socio_id, monto, tasa_interes, fecha_inicio, plazo_meses, saldo_pendiente, estado, fecha_ultimo_pago)
SELECT socio_id, monto, tasa_interes, fecha_inicio, plazo_meses, saldo_pendiente, estado, fecha_ultimo_pago
FROM inserted;
END;
END;
GO
trg_log_eliminacion_prestamo
CREATE TRIGGER trg_log_eliminacion_prestamo
ON Prestamos
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Bitacora (usuario, accion, tabla_afectada, id_registro, fecha)
SELECT SYSTEM_USER, 'DELETE', 'Prestamos', id, GETDATE()
FROM deleted;
RAISERROR ('No se permite eliminar préstamos.', 16, 1);
ROLLBACK TRANSACTION;
END;
GO
trg_actualizar_estado_pago
CREATE OR ALTER TRIGGER trg_actualizar_estado_pago
ON Pagos
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- Actualiza el estado del préstamo si el saldo llega a cero
UPDATE Prestamos
SET estado = 'PAGADO'
FROM Prestamos p
JOIN inserted i ON p.id = i.prestamo_id
WHERE p.saldo_pendiente = 0;
END;
7. Vistas
vw_prestamos_activos
CREATE VIEW vw_prestamos_activos AS
SELECT
p.id AS prestamo_id,
s.nombre AS socio,
p.monto,
p.saldo_pendiente,
p.fecha_inicio,
p.plazo_meses,
p.estado
FROM
Prestamos p
JOIN
Socios s ON p.socio_id = s.id
WHERE
p.estado = 'Activo'
AND p.saldo_pendiente > 0;
vw_socios_morosos
CREATE VIEW vw_socios_morosos AS
SELECT
s.id AS socio_id,
s.nombre,
s.email,
p.id AS prestamo_id,
p.saldo_pendiente,
p.estado
FROM
Socios s
JOIN
Prestamos p ON s.id = p.socio_id
WHERE
p.estado = 'En Mora';
vw_historial_pago
CREATE VIEW vw_historial_pagos AS
SELECT
p.id AS pago_id,
pr.id AS prestamo_id,
s.nombre AS socio,
p.fecha_pago,
p.monto_pagado
FROM
Pagos p
JOIN
Prestamos pr ON p.prestamo_id = pr.id
JOIN
Socios s ON pr.socio_id = s.id;
vw_bitacora_acciones
CREATE VIEW vw_bitacora_acciones AS
SELECT
id AS accion_id,
usuario,
accion,
tabla_afectada,
id_registro,
fecha
FROM
Bitacora;
8. Proyección Financiera (Cursor)
DECLARE @mes_actual DATE = GETDATE();
DECLARE @meses INT;
DECLARE @prestamo_id INT, @socio_id INT, @nombre_socio NVARCHAR(100);
DECLARE @monto_estimado DECIMAL(10, 2);
-- Cursor para calcular la proyección
DECLARE cur_proyeccion CURSOR FOR
SELECT id, socio_id, (SELECT nombre FROM Socios WHERE id = Prestamos.socio_id), monto / plazo_meses
FROM Prestamos
WHERE estado = 'VIGENTE';
-- Abre y recorre el cursor
OPEN cur_proyeccion;
FETCH NEXT FROM cur_proyeccion INTO @prestamo_id, @socio_id, @nombre_socio, @monto_estimado;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Genera la proyección para N meses
DECLARE @i INT = 1;
WHILE @i <= @meses
BEGIN
INSERT INTO ProyeccionIngresos (prestamo_id, socio_id, nombre_socio, mes_proyectado, monto_estimado)
VALUES (@prestamo_id, @socio_id, @nombre_socio, DATEADD(MONTH, @i, @mes_actual), @monto_estimado);
SET @i = @i + 1;
END;
FETCH NEXT FROM cur_proyeccion INTO @prestamo_id, @socio_id, @nombre_socio, @monto_estimado;
END;
-- Cierra y libera el cursor
CLOSE cur_proyeccion;
DEALLOCATE cur_proyeccion;