Persistencia-de-datos-ITBANK

Este repositorio es una manipulacion y actualizacion a una base de datos BD propuesta por la escuela de innovación del ITBA en el SPRINT 6 de su curso de desarrollador fullstack.

ITBANK tiene un modelo de datos definido para la operación de su negocio. El modelo de datos permite conocer:

• Los tipos de los datos que hay en la base y la forma en que se relacionan.
• Las restricciones de integridad
• Entender cómo llevar adelante operaciones de manipulación de los datos

Table of contents

• Diagrama Entidad-Relacion Inicial

• Diagrama Entidad-Relacion Reestructurado

• Problematica 1

  • Requerimientos 1

• Problematica 2

• Problematica 3

• Problematica 4

• Detalles del desafio

  • Tipos de Clientes
  • Tipos de Cuentas
  • Atributos entidad tarjeta
  • Direcciones

• Requerimientos especificos

• Aclaraciones y acotaciones

  • Referencias

• Links

• Autores

Diagrama Entidad-Relacion Inicial

Diagrama Entidad-Relacion Reestructurado

Problematica 1

Se solicita reestructurar la Base de Datos. Se partio de una BD que consta de 5 tablas:

• Cliente
• Prestamos
• Cuenta
• Sucursal
• Empleado Estas tablas tienen sus campos Foreign Key FK creados pero sin su restriccion asignada.
  Ver los requerimientos.

Requerimientos 1

• Se debe crear en la base de datos los tipos de cliente, tipo de cuenta y marcas de tarjeta. Ademas, agregar la entidad Tarjeta y Direccion.

• Se debe corregir el problema actual de no tener asignada la restriccion de FK en las tablas iniciales.

• Se debera ingresar parte de los nuevos valores según la información provista en el Sprint 5, se detalla en el apartado Detalles del desafio.

• Se necesita ampliar el alcance de la entidad cuenta para que identifique el tipo de la misma.

• Corregir el campo employee_hire_date de la tabla empleado con la fecha en formato YYYY-MM-DD.

• Es necesario crear las siguientes relaciones mediante FK:

  • Las tarjetas con las marcas de tarjeta
  • Las tarjetas con el cliente al que pertenecen

• Es necesario insertar:

  • 500 tarjetas de crédito con sus respectivos datos asociándolas a los clientes de forma aleatoria.
  • 500 direcciones, asignando del lote inicial a empleados, clientes o sucursal de forma aleatoria.

• Por ultimo, es necesario generar los datos que correspondientes a todos lo FK nuevos correspondientes a las nuevas tablas.

Problematica 2

Una vez realizado los cambios en la base de datos, se pide entregar un archivo SQL con los siguientes requerimientos de consulta frecuente:

Crear una vista con las columnas id, numero sucursal, nombre, apellido, DNI y edad de la tabla cliente, calculada a partir de la fecha de nacimiento.

• Mostrar las columnas de los clientes, ordenadas por el DNI de menor a mayor y cuya edad sea superior a 40 años.
• Mostrar todos los clientes que se llaman “Anne” o “Tyler” ordenados por edad de menor a mayor

Dado el JSON suministrado en la carpeta data. Insertar 5 nuevos clientes en la base de datos y verificar que se haya realizado con éxito la inserción.

• Actualizar 5 clientes recientemente agregados en la base de datos dado que hubo un error en el JSON que traía la información, la sucursal de todos es la 10.
• Eliminar el registro correspondiente a “Noel David” realizando la selección por el nombre y apellido.
• Consultar sobre cuál es el tipo de préstamo de mayor importe.

Problematica 3

Realice las siguientes consultas y operaciones individuales en archivos SQL:

• Seleccionar las cuentas con saldo negativo.
• Seleccionar el nombre, apellido y edad de los clientes que tengan en el apellido la letra Z.
• Seleccionar el nombre, apellido, edad y nombre de sucursal de las personas cuyo nombre sea “Brendan”, el resultado ordenarlo por nombre de sucursal.
• Seleccionar de la tabla de préstamos, los préstamos con un importe mayor a $80.000 y los préstamos prendarios utilizando la unión de tablas/consultas (recordar que en las bases de datos la moneda se guarda como integer, en este caso con 2 centavos).
• Seleccionar los prestamos cuyo importe sea mayor que el importe medio de todos los prestamos.
• Contar la cantidad de clientes menores a 50 años.
• Seleccionar las primeras 5 cuentas con saldo mayor a 8.000$.
• Seleccionar los préstamos que tengan fecha en abril, junio y agosto, ordenándolos por importe.
• Obtener el importe total de los prestamos agrupados por tipo de préstamos. Por cada tipo de préstamo de la tabla préstamo, calcular la suma de sus importes. Renombrar la columna como loan_total_accu.

Problematica 4

Realice las siguientes consultas y operaciones individuales en archivos SQL:

• Listar la cantidad de clientes por nombre de sucursal ordenando de mayor a menor.

• Obtener la cantidad de empleados por cliente por sucursal en un número real.

• Obtener la cantidad de tarjetas de crédito por tipo por sucursal.

• Obtener el promedio de créditos otorgado por sucursal.

• Mediante índices mejorar la performance la búsqueda de clientes por DNI.

• La información de las cuentas resulta critica para la compañía, por eso es necesario crear una tabla denominada “auditoria_cuenta” para guardar los datos movimientos, con los siguientes campos: old_id, new_id, old_balance, new_balance, old_iban, new_iban, old_type, new_type, user_action, created_at.

  • Crear un trigger que después de actualizar en la tabla cuentas los campos balance, IBAN o tipo de cuenta registre en la tabla auditoria.
  • Restar $100 a las cuentas 10,11,12,13,14.

• Crear la tabla “movimientos” con los campos de identificación del movimiento, número de cuenta, monto, tipo de operación y hora.

  • Mediante el uso de transacciones, hacer una transferencia de 1000$ desde la cuenta 200 a la cuenta 400.
  • Registrar el movimiento en la tabla movimientos.
  • En caso de no poder realizar la operación de forma completa, realizar un ROLLBACK.

Detalles del desafio

Tipos de clientes

• Classic

  • Tiene solamente una tarjeta de débito que se crea junto con el cliente.
  • Solo tiene una caja ahorro en pesos creada cuando se dio de alta el cliente.
  • Como no tiene cuenta en dólares, no puede comprar y vender dólares.
  • Solo se le permite retirar hasta un máximo de $10.000 diarios por cajero.
  • No tienen acceso a tarjetas de crédito, ni chequeras.
  • La comisión por transferencias hechas es de 1%.
  • No puede recibir transferencias mayores a $150.000 sin previo aviso.

• Gold

  • Tiene una tarjeta de débito que se crea con el cliente.
  • Tiene una cuenta corriente con un descubierto de $10.000. Hay que tener presente que como tiene cuenta corriente el saldo en la cuenta podría ser negativo y hasta -$10.000 si tiene cupo diario para la operación que se quiera realizar.
  • Tiene una caja de ahorro en dólares, por lo que puede comprar dólares.
  • Puede tener solo una tarjeta de crédito.
  • Las extracciones de efectivo tienen un máximo de $20.000 por día.
  • Pueden tener una chequera.
  • La comisión por transferencias hechas es de 0,5%.
  • No puede recibir transferencias mayores a $500.000 sin previo aviso.

• Black

  • Los clientes Black tienen una caja de ahorro en pesos, cuenta corriente en pesos, y una caja de ahorro en dólares.
  • Pueden tener un descubierto en su cuenta corriente de hasta $10.000.
  • Pueden tener hasta 5 tarjetas de crédito.
  • Pueden extraer hasta $100.000 por día.
  • Pueden tener hasta dos chequeras.
  • No se aplican comisiones a las transferencias.
  • Pueden recibir transferencias por cualquier monto sin previa autorización.

Tipos de cuentas

• Caja de ahorro en pesos
• Caja de ahorro en dólares
• Cuenta Corriente

Atributos entidad Tarjeta

La entidad tarjeta debe contener los atributos necesarios para la operación del home banking: - Numero (único e irrepetible, con una restricción ante cada inserción que no debe superar 20 números/espacios) - CVV - Fecha de otorgamiento - Fecha Expiración - Tarjeta de crédito o débito.

Direcciones

La entidad direccion puede ser usada por los clientes, empleados y sucursales con los campos utilizados en el SPRINT 5:

• Calle
• Numero
• Ciudad
• Provincia
• Pais Tener en cuenta que un cliente o empleado puede tener múltiples direcciones, pero la sucursal, solo una. A su vez, las direcciones que ocupen las sucursales son exclusivas, a diferencia de las ocupadas por clientes o empleados que se pueden contemplar excepciones, tales como que haya familiares de compartan domicilio dentro de una de las tablas o dentro de ambas.

Requerimientos especificos

Se pide resolver todas las problematicas partiendo de resoluciones en SQL.

Aclaraciones y acotaciones

La generacion de datos aleatoria respecto a tipo de cuenta en la tabla cuenta, o los datos suminitrados a la nueva entidad tarjeta; debe contemplar que no todos los clientes estan habilitados a tener cualquier tipo de tarjeta o cualquier tipo de cuenta dado que esto se restringe al relacionarse con el tipo de cliente que el mismo es.

Referencias

• Generador de valores

Links del proyecto

• Solution URL: (https://github.com/EliasUpstein/Persistencia-de-datos-ITBANK)

Autores

Korzusehec, Nicolás

• GitHub - @NicolasKorzusehec
• LinkedIn - Nicolás Korzusehec

Upstein, Elias Román

• GitHub - @EliasUpstein
• LinkedIn - /

Ledesma, Juan Ignacio

• GitHub - @juanignacio97
• LinkedIn - Juan Ignacio Ledesma

Molinas, Nicolás

• GitHub - @NicolasGabM
• LinkedIn - Nicolas Gabriel Molinas