UNIVERSIDAD DE SAN CARLOS DE GUATEMALA
EVALUACIÓN FINAL DE ARCHIVOS
HENRI LEONIDES YAXCAL RIVEIRO – 201844519
JOSÉ ANDRÉS TAROTT TENÍ – 201844818
INGENIERÍA EN CIENCIAS Y SISTEMAS
TABLAS YA NORMALIZADAS
CREACION DE LA BASE DE DATOS “CREDITOS”
DIAGRAMA DE BASE DE DATOS
TABLA PRESTAMO
TABLA SALDO
TABLA ESTADO
TABLA CLIENTES
SCRIPTS
TABLA CLIENTE
CREATE TABLE [dbo].[Cliente](
[Id_Cliente] [int] NOT NULL,
[Nombre] [varchar](50) NOT NULL,
CONSTRAINT [PK_Cliente] PRIMARY KEY CLUSTERED
(
[Id_Cliente] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
[PRIMARY]
) ON [PRIMARY]
TABLA ESTADO
CREATE TABLE [dbo].[Estado](
[Id_Estado] [int] NOT NULL,
[Tipo] [varchar](50) NOT NULL,
CONSTRAINT [PK_Estado] PRIMARY KEY CLUSTERED
(
[Id_Estado] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
[PRIMARY]
) ON [PRIMARY]
TABLA PRESTAMO
CREATE TABLE [dbo].[Prestamo](
[Id_Prestamo] [int] NOT NULL,
[Id_Cliente] [int] NOT NULL,
[Id_Estado] [int] NOT NULL,
[Fecha_p] [date] NOT NULL,
[fecha_dev] [date] NOT NULL,
[Periodo] [int] NOT NULL,
[Monto] [int] NOT NULL,
[Total] [int] NOT NULL,
[Saldo_pago] [int] NOT NULL,
CONSTRAINT [PK_Prestamo] PRIMARY KEY CLUSTERED
(
[Id_Prestamo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
[PRIMARY]
) ON [PRIMARY]
TABLA SALDO
CREATE TABLE [dbo].[Saldo](
[Id_Saldo] [int] NOT NULL,
[Monto] [int] NOT NULL,
[Fecha] [date] NOT NULL,
[Id_Prestamo] [int] NOT NULL,
CONSTRAINT [PK_Saldo] PRIMARY KEY CLUSTERED
(
[Id_Saldo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
[PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Prestamo] WITH CHECK ADD CONSTRAINT [FK_Prestamo_Cliente]
FOREIGN KEY([Id_Cliente])
REFERENCES [dbo].[Cliente] ([Id_Cliente])
GO
ALTER TABLE [dbo].[Prestamo] CHECK CONSTRAINT [FK_Prestamo_Cliente]
GO
ALTER TABLE [dbo].[Prestamo] WITH CHECK ADD CONSTRAINT [FK_Prestamo_Estado]
FOREIGN KEY([Id_Estado])
REFERENCES [dbo].[Estado] ([Id_Estado])
GO
ALTER TABLE [dbo].[Prestamo] CHECK CONSTRAINT [FK_Prestamo_Estado]
GO
ALTER TABLE [dbo].[Prestamo] WITH CHECK ADD CONSTRAINT [FK_Prestamo_Saldo] FOREIGN
KEY([Id_Cliente])
REFERENCES [dbo].[Saldo] ([Id_Saldo])
GO
ALTER TABLE [dbo].[Prestamo] CHECK CONSTRAINT [FK_Prestamo_Saldo]
GO
INSERCIÓN DE DATOS
INSERT INTO CLIENTE VALUES ('Pedro Pérez');
INSERT INTO CLIENTE VALUES ('Juan Carlos');
INSERT INTO PRESTAMO VALUES (1,1,'17-02-2020', '17-05-2020', 3, 500, 505, 522);
INSERT INTO PRESTAMO VALUES (2,2,'28-01-2020', '28-12-2020', 11, 1500, 1515, 1259);
INSERT INTO SALDO VALUES (169, '05-03-2020', 1);
INSERT INTO SALDO VALUES (169, '05-04-2020', 1);
INSERT INTO SALDO VALUES (184, '22-05-2020', 1);
INSERT INTO SALDO VALUES (137, '08-02-2020', 2);
INSERT INTO SALDO VALUES (137, '04-03-2020', 2);
INSERT INTO SALDO VALUES (150, '22-04-2020', 2);
INSERT INTO SALDO VALUES (137, '09-05-2020', 2);
INSERT INTO SALDO VALUES (137, '02-06-2020', 2);
INSERT INTO SALDO VALUES (137, '02-07-2020', 2);
INSERT INTO SALDO VALUES (150, '29-08-2020', 2);
INSERT INTO SALDO VALUES (137, '01-09-2020', 2);
INSERT INTO SALDO VALUES (137, '05-10-2020', 2);
INSER INTO ESTADO VALUES ('SALDADO');
INSER INTO ESTADO VALUES ('NO SALDADO');
FUNCIONES Y VISTAS
CREATE FUNCTION PAGO_POR_MES(
@PERIODO INT,
@TOTAL INT
)
RETURNS INT
AS
BEGIN
RETURN CEILING(@TOTAL / @PERIODO) ;
END;
-----------------------------------------------------
CREATE PROCEDURE NUEVO_SALDO
@ID int,
@SALDO int,
@FECHA datetime
AS
BEGIN
INSERT INTO SALDO VALUES (@SALDO, @FECHA, @ID);
UPDATE Prestamo SET Saldo_pagado = Saldo_pagado + @SALDO
WHERE ID < @ID;
END
GO
---------------------------------------------
CREATE VIEW vista1
as
SELECT MONTO, FECHA FROM SALDO;
SELECT * FROM vista1 ORDER BY FECHA
---------------------------------------------------
CREATE TRIGGER MORA ON SALDO AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
if (SELECT DAY(Fecha) FROM INSERTED) > 10
UPDATE INSERTED SET Monto = Monto*1.1;
END
SQL Server no nos permitió conectar la base de datos con Java
