PROYECTO SISTEMAS GESTORES DE BASES DE DATOS

(1)

Sergio Santos Guerra

2º ASIR

| TORRE DE JUAN ABAD

PROYECTO

SISTEMAS

GESTORES DE BASES

DE DATOS

ELABORACION DE UNA BASE DE DATOS, FUNCIONES,

(2)

SERGIO SANTOS GUERRA 1

EXPLICACION DEL PROYECTO

3 ESQUEMA ENTIDAD-RELACION

4 USUARIOS

5 CONSULTAS

8 FUNCIONES

11 PROCEDIMIENTOS

16 TRIGGERS

24 EVENTOS

27 VISTAS

29

(3)

Trataremos de elaborar una base de datos para un comercio el cual va a ser un taller de reparaciones de instrumentos además de una tienda de instrumentos. Nos centraremos en sobre todo vender al público diferente material que previamente nosotros compramos a mayoristas.

La base de datos consta de varias tablas entre las cuales se encuentran:

 Para llevar todo a cabo tendremos un taller, donde se almacenaran todos los

instrumentos.

 También tendremos ciertos empleados de los cuales guardaremos todos los datos y

también una referencia al taller en el que trabajan.

 Se crearan fichas sobre los clientes que compren en nuestro establecimiento, también

haciendo referencia con el empleado que les atendió en la compra y en el taller en el cual realizaron la compra.

 Tenemos una serie de proveedores los cuales mediante transacciones nos proveen el

stock de artículos que tenemos en el taller.

 Guardaremos una lista de artículos que tenemos en el taller, además de su stock y sus

características tanto precio como descripciones, etc…

 En el taller se tendrán estanterías, en las cuales se guardaran los instrumentos para su

correcta colocación.

 También se llevara a cabo mediante una tabla específica un conteo de todo el dinero

que han invertido los clientes en los productos que nos han comprado.

Como entidades tendríamos esas, pero también tenemos algunas especificas relacionales donde llevaremos la facturación de las compras de la tienda, la facturación de los proveedores, etc…

(4)

ALMACENARSE (COD_ALMACEN, COD_ESTANTERIA, COD_ARTICULO)

ARTICULOS (COD_ARTICULO, NOMBRE, PRECIO, MARCA, TIPO_INSTRUMENTO, DESCRIPCION, STOCK)

CLIENTES (DNI, NOMBRE, APELLIDOS, LOCALIDAD, DIRECCION, COD_TALLER)

EMPLEADOS (COD_EMPLEADO, NOMBRE, APELLIDOS, LOCALIDAD, DIRECCION, EMAIL, NUMERO_TELEFONO, CUENTA, COD_TALLER)

ESTANTERIAS (COD_ESTANTERIA, TIPO)

FACTURAN (COD_FACTURA, CANTIDAD, IMPORTE, COD_EMPLEADO, COD_ARTICULO, DNI) LOGS (IDUSUARIO, DESCRIPCION)

PROVEEDORES (CIF_PROVEEDORES, PAIS, EMAIL, COD_POSTAL, DIRECCION, NOMBRE) SUMINISTRAN (COD_COMPRA, IMPORTE, CANTIDAD, FECHA, CIF_PROVEEDORES, COD_ARTICULO)

TALLER (COD_TALLER, COD_POSTAL, NOMBRE, LOCALIDAD, DIRECCION) TOTALVENTAS (ID, DNI, TOTAL)

(5)

Procedemos a crear dos usuarios con los que podremos trabajar en nuestra base de datos mediante la siguiente sintaxis:

CREATE USER 'sergio'@'localhost' IDENTIFIED BY 'inves'; CREATE USER 'invitado'@'localhost' IDENTIFIED BY 'clave';

Aquí podemos ver que esta correcto y que podemos introducirlos dentro del gestor.

Ahora procedemos a darle privilegios a cada usuario, que en mi caso a Sergio le daremos todos y a invitado solo le daremos el permiso de SELECT en la tabla de artículos.

GRANT ALL PRIVILEGES ON * . * TO 'sergio'@'localhost';

GRANT SELECT ON taller.articulos TO 'invitado'@'localhost';

(6)

Ahora procedemos a ver los permisos que tiene cada uno para ver que todo es funcional. SHOW GRANTS for 'sergio'@'localhost';

SHOW GRANTS for 'invitado'@'localhost';

Para actualizar los permisos de cada uno realizamos un FLUSH PRIVILEGES.

Si nos vamos a PHPMyADMIN podemos ver los permisos también.

(7)

Si hacemos un select podemos sacar los datos que tenemos en la base de datos.

(8)

Sacar el nombre, precio, marca y descripción de artículos el cual tenga el código de articulo 1 (consulta simple).

mysql> select nombre, precio, marca, descripcion from articulos where cod_articulo=3;

Sacar el nombre, precio, marca y descripción de los artículos sin ningún requerimiento (consulta simple).

mysql> select nombre, precio, marca, descripcion from articulos;

Sacar el nombre del cliente y los datos de dicho cliente que ha sido atendido en el taller con el código 1 (subconsulta con join).

mysql> select DISTINCT clientes.nombre, clientes.apellidos, clientes.localidad from clientes join taller on taller.cod_taller=taller.cod_taller where clientes.cod_taller=1;

Sacar el nombre y datos del empleado de la tabla de empleados que esté trabajando en el taller con el código 1 (subconsulta con join).

mysql> select empleados.nombre, empleados.apellidos, empleados.localidad from empleados join taller on empleados.cod_taller=taller.cod_taller where empleados.cod_taller=1;

(9)

Sacar el tipo de estantería donde se encuentra el artículo con el código de artículo 2 (subconsulta con subconsultas).

mysql> select tipo from estanterias where cod_estanteria in (select cod_estanteria from almacenarse where cod_articulo in (select cod_articulo from articulos where cod_articulo=2));

Sacar el nombre y los detalles de dicho artículo que se encuentra en el tipo 2 de estantería (parecido al anterior) (subconsulta con subconsultas).

mysql> select nombre, precio, marca, descripcion from articulos where cod_articulo in (select cod_articulo from almacenarse where cod_estanteria in (select cod_estanteria from

estanterias where tipo=2));

Lo mismo, pero con join sacando más datos.

mysql> select estanterias.tipo, articulos.nombre, articulos.precio, articulos.marca, articulos.descripcion from articulos join almacenarse on

articulos.cod_articulo=almacenarse.cod_articulo join estanterias on estanterias.cod_estanteria=almacenarse.cod_estanteria where tipo=2;

(10)

Sacar los modelos que hay en artículos suministrados por el proveedor con el código postal 59789 (subconsulta con subconsultas)

mysql> select nombre, precio, marca from articulos where cod_articulo in (select cod_articulo from suministran where cif_proveedores in (select cif_proveedores from proveedores where cod_postal=59789));

(subconsulta con join y subconsulta con subconsultas todo junto)

mysql> select estanterias.tipo, articulos.nombre, articulos.precio, articulos.marca, articulos.descripcion from articulos join almacenarse on

articulos.cod_articulo=almacenarse.cod_articulo join estanterias on

estanterias.cod_estanteria=almacenarse.cod_estanteria where articulos.cod_articulo in (select cod_articulo from almacenarse where cod_estanteria in (select cod_estanteria from

(11)

Esta función lo que hace es registrar en una tabla externa todos los clientes que han comprado en nuestra tienda.

DROP FUNCTION registro //

CREATE FUNCTION registro() returns int BEGIN

DECLARE resultado int;

DECLARE dniexistente, dniventas varchar(9); DECLARE fin bool default 0;

DECLARE cursor1 CURSOR FOR select dni from clientes; DECLARE cursor2 CURSOR FOR select dni from totalventas; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

OPEN cursor1; OPEN cursor2;

repeat

FETCH cursor1 INTO dniexistente; FETCH cursor2 INTO dniventas; IF fin=0 THEN

IF dniexistente=dniventas THEN SET resultado=1;

ELSE

INSERT into totalventas (dni, total) VALUES (dniexistente, 0); SET resultado=0;

(12)

END IF;

until fin end repeat; CLOSE cursor1; CLOSE cursor2; return resultado; END//

Nos devuelve 1 si están todos, y si hay alguno nuevo lo inserta. Esto nos sirve para más adelante con un trigger poder llevar un conteo de todo lo que han gastado en la tienda.

(13)

Esta función lo que realiza es una comprobación de la tabla de suministran donde están las facturas de los proveedores en los cuales vemos al precio al que lo venden y la cantidad. Lo que hace es actualizar los datos de la tabla de artículos dependiendo del suministro que haya de un artículo. Si se ha comprado al proveedor un artículo esta función lo actualiza, y si no, establece que no se ha comprado nada y no se tiene en stock.

DROP FUNCTION calcularstock //

CREATE FUNCTION calcularstock() returns int BEGIN

DECLARE resultado int; DECLARE fin BOOL default 0;

DECLARE articulosstock, articulossuministrados, micantidad, consulta int; DECLARE cursor1 CURSOR FOR select cod_articulo from articulos;

DECLARE cursor2 CURSOR FOR select cod_articulo, cantidad from suministran; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

OPEN cursor1; OPEN cursor2; repeat

FETCH cursor1 INTO articulosstock;

FETCH cursor2 INTO articulossuministrados, micantidad;

(14)

IF articulosstock=articulossuministrados THEN

UPDATE articulos SET stock=micantidad where cod_articulo=articulosstock; SET resultado=1;

ELSE

UPDATE articulos SET stock=0 where cod_articulo=articulosstock; SET resultado=0;

END IF; END IF;

until fin end repeat; CLOSE cursor1; CLOSE cursor2;

return resultado; END//

(15)

Estos son los datos de suministran y actualiza la cantidad de artículos que tenemos, y de los que no se han suministrado ninguno lo pone a 0.

(16)

Vamos a crear un procedimiento en el cual crearemos un cursor donde se podrá ver en pantalla la facturación que hemos hecho. Hay que tener en cuenta que la tabla de facturan solo toma foráneas de otras tablas y eso es algo más difícil de entender a alguien que no sabe cómo es la tabla, así que con esto podemos interpretarlo mucho mas fácil.

DELIMITER //

DROP procedure facturacion// CREATE procedure facturacion() BEGIN

DECLARE mifactura, miempleado, miarticulo, midni, miimporte int; DECLARE var1, var2, var3 varchar(30);

DECLARE fin bool default 0;

DECLARE cursor1 CURSOR FOR select cod_factura, cod_empleado, cod_articulo, dni, importe from facturan;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

open cursor1;

repeat

fetch cursor1 into mifactura, miempleado, miarticulo, midni, miimporte;

IF fin=0 then

SET var1=(select nombre from empleados where cod_empleado=miempleado);

SET var2=(select nombre from articulos where cod_articulo=miarticulo);

(17)

select concat ("La factura con codigo de factura ", mifactura, " ha sido tramitada por el empleado ", var1, " , el cual vendió el articulo ", var2, " al cliente ", var3, " por el importe de ", miimporte, " euros") as 'Resultado';

end if;

until fin end repeat;

close cursor1;

(18)

Si lo llamamos nos interpreta la información que vemos debajo de una manera mas legible al usuario.

Aquí procedemos a crear un procedimiento en el cual nos dice cual es el articulo mas caro de todos los que tenemos.

DELIMITER //

DROP procedure precioscaros// CREATE procedure precioscaros() BEGIN

DECLARE maxprecio int default 0; DECLARE miprecio int;

(19)

DECLARE fin BOOL default 0;

DECLARE cursor1 CURSOR FOR select precio, nombre from articulos; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

OPEN cursor1;

repeat

fetch cursor1 into miprecio, minombre; IF fin=0 THEN IF miprecio>maxprecio then set maxprecio=miprecio; set maxnombre=minombre; END IF; END IF;

until fin end repeat;

CLOSE cursor1;

select concat ("El articulo con el precio más alto es ", maxnombre, " , el cual vale ", maxprecio, " euros ") as 'Resultado';

(20)

Con este procedimiento podemos ver lo que tenemos de stock en el taller agrupado por marcas.

DELIMITER //

DROP procedure stock// CREATE procedure stock() BEGIN

DECLARE minombre varchar(15); DECLARE mimarca int;

(21)

DECLARE fin BOOL default 0;

DECLARE cursor1 CURSOR FOR select marca, count(marca) from articulos group by marca; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

OPEN cursor1;

repeat

fetch cursor1 into minombre, mimarca;

IF fin=0 then

IF mimarca > 1 then

select concat ("De la marca ", minombre, " tenemos ", mimarca, " unidades ") as 'Stock del almacen';

ELSEIF mimarca = 1 then

select concat ("De la marca ", minombre, " tenemos ", mimarca, " unidad ") as 'Stock del almacen';

ELSEIF mimarca = 0 then

select concat ("De la marca ", minombre, " no hay ninguna unidad ") as 'Stock del almacen';

end if; END IF;

until fin end repeat; CLOSE cursor1; END//

(22)

(23)

Este procedimiento es un select de un trigger que posteriormente veremos en el cual podemos sacar registros aleatorios de la tabla de logs donde nos dice la información de cada

modificación en la tabla articulos. DELIMITER //

DROP procedure logsaleatorios // CREATE procedure logsaleatorios() BEGIN

DECLARE resultado varchar(200);

select * from logs order by rand() limit 1; END//

(24)

Crearemos este trigger para despues de una modificacion en la table articulos se inserte en la table logs un concat de informacion con los valores antiguos y los nuevos, para asi llevar un control de lo que se modifica en la tabla.

DROP trigger logs //

CREATE TRIGGER logs AFTER UPDATE ON articulos FOR EACH ROW BEGIN

DECLARE fechaactual DATE; DECLARE horaactual TIME;

SET fechaactual=(select curdate()); SET horaactual=(select time(now()));

INSERT INTO logs VALUES (user( ),CONCAT('Se modificó el registro ','(',OLD.cod_articulo,',', OLD.nombre,',',OLD.precio,',',OLD.marca,',',OLD.tipo_instrumento,',',OLD.descripcion,',', OLD.stock,') por ','(',NEW.cod_articulo,',',

NEW.nombre,',',NEW.precio,',',NEW.marca,',',NEW.tipo_instrumento,',',NEW.descripcion,',',NE W.stock,')'), fechaactual, horaactual);

END//

(25)

Nos vamos al log y nos dice que el registro se ha modificado a fecha de hoy.

Este trigger lo que hace es que después de una venta, en la tabla facturan, calcule el importe y la cantidad que se ha facturado y se sume al total que hay en la tabla totalventas. Además también se encarga de descontar en el stock de artículos la cantidad vendida.

DROP TRIGGER facturacion //

CREATE TRIGGER facturacion AFTER INSERT ON facturan FOR EACH ROW BEGIN

DECLARE importeacalcular, cantidadacalcular, cantidadtotal, numerostock int; DECLARE clienteexistente varchar(9);

DECLARE cursor1 CURSOR FOR SELECT dni from totalventas; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

SET importeacalcular=(select DISTINCT importe from facturan where importe=NEW.importe); SET cantidadacalcular=(select DISTINCT cantidad from facturan where

cantidad=NEW.cantidad);

SET cantidadtotal=importeacalcular*cantidadacalcular; SET numerostock=(select DISTINCT stock from articulos where cod_articulo=NEW.cod_articulo);

OPEN cursor1; repeat

FETCH cursor1 into clienteexistente;

IF fin=0 THEN

(26)

UPDATE totalventas SET total=total+cantidadtotal where dni=clienteexistente; ELSE

INSERT INTO totalventas (dni, total) VALUES (NEW.dni, cantidadtotal); END IF;

END IF;

until fin end repeat; CLOSE cursor1;

IF numerostock<cantidadacalcular THEN SET cantidadacalcular=numerostock; END IF;

UPDATE articulos SET stock=numerostock-cantidadacalcular where cod_articulo=NEW.cod_articulo;

(27)

Nos vamos a la tabla de artículos donde vemos que después de haber insertado en la tabla facturan resta la cantidad de artículos que se han facturado al stock de artículos que tenemos.

Tambien vemos que suma al cliente con dni 11111111 que ha sido quien lo ha comprado la cantidad por la cual ha sido vendido.

Este trigger lo que hace es descontar el importe de un artículo devuelto en el total que tenemos en la tabla totalventas de un cliente.

DROP TRIGGER anulacion //

CREATE TRIGGER anulacion AFTER DELETE ON facturan FOR EACH ROW BEGIN

DECLARE importeacalcular, cantidadacalcular, cantidadtotal int; DECLARE clienteexistente varchar(9);

DECLARE cursor1 CURSOR FOR SELECT dni from totalventas; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

SET importeacalcular=(select importe from facturan where cod_factura=OLD.cod_factura); SET cantidadacalcular=(select cantidad from facturan where cod_factura=OLD.cod_factura); SET cantidadtotal=(cantidadacalcular*importeacalcular);

OPEN cursor1; repeat

(28)

FETCH cursor1 into clienteexistente; IF fin=0 THEN

IF clienteexistente=OLD.dni THEN

UPDATE totalventas SET total=total-cantidadtotal where dni=clienteexistente;

UPDATE articulos SET stock=stock+cantidadacalcular where cod_articulo=OLD. cod_articulo; END IF;

END IF;

until fin end repeat; CLOSE cursor1; END//

(29)

Si nos vamos a la tabla actualiza el valor restando la cantidad por la cual se facturo la factura que se ha devuelto.

(30)

Tambien tenemos un trigger para cuando se elimina un cliente de la tabla de clientes se haga un registro backup de sus datos.

DROP TRIGGER backup //

CREATE TRIGGER backup AFTER DELETE ON clientes FOR EACH ROW BEGIN

INSERT INTO backup VALUES (OLD.dni, OLD.nombre, OLD.apellidos, OLD.localidad, OLD.direccion, OLD.cod_taller);

END;//

Procedemos a crear un usuario el cual será el siguiente:

(31)

Si ahora queremos eliminarlo le damos a borrar y a OK.

Nos vamos a la tabla backup para ver que efectivamente se han registrado sus datos anteriores.

(32)

Proponemos que una vez al mes se haga el día sin IVA en productos aleatorios. Eso saca una marca aleatoria y es la que aplica a la hora de hacer la reducción, con lo que crearemos dos eventos para ello. Uno de ellos aplicara el descuento del 16% de IVA a los productos que sean de la marca ESP y otro que al día siguiente lo restaure

CREATE EVENT descuentos ON SCHEDULE EVERY 1 MONTH STARTS '2015-05-20 09:00:00' DO UPDATE articulos SET precio=precio-(precio*0.16) where marca in (select aleatorio());//

Dentro de dicho evento a la hora de calcularlo llamamos a aleatorio, que es una función que genera una marca aleatoria de artículos.

DROP function aleatorio //

CREATE function aleatorio() returns varchar(30) BEGIN

DECLARE var varchar(30);

SET var=(select marca from articulos order by rand() limit 1); return var;

(33)

CREATE EVENT nodescuentos ON SCHEDULE EVERY 1 MONTH STARTS '2015-05-21 09:00:00' DO call recalcular; //

Lo que vamos a hacer el día siguiente será recalcular los precios mediante un cursor en un procedimiento:

DROP PROCEDURE recalcular // CREATE PROCEDURE recalcular() BEGIN

DECLARE fin BOOL default 0; DECLARE miprecio, miarticulo int;

DECLARE cursor1 CURSOR FOR select precio, cod_articulo from suministran; DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

OPEN cursor1; Repeat

(34)

IF fin=0 THEN

UPDATE articulos SET precio=miprecio where cod_articulo=miarticulo; END IF;

Until fin end repeat; CLOSE cursor1; END //

(35)

Procedemos a crear una vista de una consulta anterior, en la cual haremos un join de varias tablas y el identificador común será una subconsulta de una subconsulta de una subconsulta. Así que procedemos a introducirla para ver su funcionamiento.

CREATE VIEW complicada AS select estanterias.tipo, articulos.nombre, articulos.precio, articulos.marca, articulos.descripcion from articulos join almacenarse on

articulos.cod_articulo=almacenarse.cod_articulo join estanterias on

estanterias.cod_estanteria=almacenarse.cod_estanteria where articulos.cod_articulo in (select cod_articulo from almacenarse where cod_estanteria in (select cod_estanteria from

estanterias where tipo=2));

Ahora una vez la hemos introducido y no tiene errores sintácticos procedemos a ejecutarla:

Ahora procedemos a crear una vista llamada articulos2 donde sacaremos mediante

subconsultas sacar el nombre y los detalles de dicho artículo que se encuentra en el tipo 2 de estantería.

CREATE VIEW articulos2 AS select nombre, precio, marca, descripcion from articulos where cod_articulo in (select cod_articulo from almacenarse where cod_estanteria in (select cod_estanteria from estanterias where tipo=2));