create database bancoespanyol2;

use bancoespanyol2;

create table cliente
(nombre_cliente varchar(20),
calle_cliente varchar(30),
ciudad_cliente varchar(30),
primary key (nombre_cliente));

create table sucursal
(nombre_sucursal varchar(15),
ciudad_sucursal varchar(30),
activo integer,
primary key (nombre_sucursal),
check (activo >= 0));

create table cuenta
(numero_cuenta varchar(10),
nombre_sucursal varchar(15),
saldo integer,
primary key (numero_cuenta),
foreign key (nombre_sucursal) references sucursal(nombre_sucursal)
on delete cascade
on update cascade,
check (saldo >= 0));

create table impositor
(nombre_cliente varchar(20),
numero_cuenta varchar(10),
primary key (nombre_cliente, numero_cuenta),
foreign key (nombre_cliente) references cliente(nombre_cliente)
on delete cascade
on update cascade,
foreign key (numero_cuenta) references cuenta(numero_cuenta)
on delete cascade
on update cascade
);

create table prestatario
(nombre_cliente varchar(20),
numero_prestamo varchar(10),
primary key (nombre_cliente, numero_prestamo),
foreign key (nombre_cliente) references cliente(nombre_cliente)
on delete cascade
on update cascade,
foreign key (numero_prestamo) references prestamo(numero_prestamo)
on delete cascade
on update cascade
);

create table prestamo
(numero_prestamo varchar(10),
nombre_sucursal varchar(15),
importe integer,
primary key (numero_prestamo),
foreign key (nombre_sucursal) references sucursal(nombre_sucursal)
on delete cascade
on update cascade,
check (saldo >= 0));

insert into cliente values ("Santos","Mayor","Peguerinos");
insert into cliente values ("Gómez","Carretas","Cerceda");
insert into cliente values ("López","Mayor","Peguerinos");
insert into cliente values ("Pérez","Carretas","Cerceda");
insert into cliente values ("Rupérez","Ramblas","León");
insert into cliente values ("Abril","Preciados","Valsaín");
insert into cliente values ("Valdivieso","Goya","Vigo");
insert into cliente values ("Fernández","Jazmín","León");
insert into cliente values ("González","Arenal","La Granja");
insert into cliente values ("Rodríguez","Yeserías","Cádiz");
insert into cliente values ("Amo","Embajadores","Arganzuela");
insert into cliente values ("Badorrey","Delicias","Valsaín");

insert into sucursal values ("Centro","Arganzuela",9000000);
insert into sucursal values ("Moralzarzal","La Granja",2100000);
insert into sucursal values ("Navacerrada","Aluche",1700000);
insert into sucursal values ("Becerril","Aluche",400000);
insert into sucursal values ("Collado Mediano","Aluche",8000000);
insert into sucursal values ("Navas de la Asunción","Alcalá de enares",300000);
insert into sucursal values ("Segovia","Cerceda",3700000);
insert into sucursal values ("Galapagar","Arganzuela",7100000);

insert into prestamo values ("P-17","Centro",1000);
insert into prestamo values ("P-23","Moralzarzal",2000);
insert into prestamo values ("P-15","Navacerrada",1500);
insert into prestamo values ("P-14","Centro",1500);
insert into prestamo values ("P-93","Becerril",500);
insert into prestamo values ("P-11","Collado Mediano",900);
insert into prestamo values ("P-29","Navas de la Asunción",1200);
insert into prestamo values ("P-16","Segovia",1300);
insert into prestamo values ("P-18","Centro",2000);
insert into prestamo values ("P-25","Navacerrada",2500);
insert into prestamo values ("P-10","Galapagar",2200);

insert into prestatario values ("Fernández","P-16");
insert into prestatario values ("Gómez","P-93");
insert into prestatario values ("Gómez","P-15");
insert into prestatario values ("López","P-14");
insert into prestatario values ("Pérez","P-17");
insert into prestatario values ("Santos","P-11");
insert into prestatario values ("Sotoca","P-23");
insert into prestatario values ("Valdivieso","P-17");

insert into impositor values ("Abril","C-102");
insert into impositor values ("Gómez","C-101");
insert into impositor values ("González","C-201");
insert into impositor values ("González","C-217");
insert into impositor values ("López","C-222");
insert into impositor values ("Rupérez","C-215");
insert into impositor values ("Santos","C-305");

insert into cuenta values ("C-101","Centro",500);
insert into cuenta values ("C-102","Navacerrada",400);
insert into cuenta values ("C-201","Galapagar",900);
insert into cuenta values ("C-215","Becerril",700);
insert into cuenta values ("C-217","Galapagar",750);
insert into cuenta values ("C-222","Moralzarzal",700);
insert into cuenta values ("C-305","Collado Mediano",350);

en lugar de permitir saldos de cuenta negativos, el banco trata los descubiertos dejando a cero el saldo de las cuentas y creando un préstamo por el importe del descubierto. Este préstamo recibe un número de préstamo idéntico al número de cuenta que ha tenido el descubierto.

create trigger descubierto before update on cuenta
for each row
  begin
  if new.saldo<0 then
    insert into prestatario
      (select nombre_cliente, numero_cuenta
       from impositor
       where new.numero_cuenta = impositor.numero_cuenta);
    insert into prestamo values
      (new.numero_cuenta,new.nombre_sucursal,-new.saldo);
    set new.saldo = 0;
  end if;
end;

update cuenta set saldo = -250 where numero_cuenta = "C-101";