Gestión de usuarios y privilegios en MySQL

En esta entrada os dejo los enlaces de los tres vídeos, en los que se muestra como crear, modificar y eliminar usuarios, así como la forma de gestionar sus permisos.

Mysql: usuarios parte1 - www.ofimaticaparatorpes.com
http://www.youtube.com/watch?v=Lf5eg2J-Xf0&feature=related

Mysql: usuarios parte 2 - www.ofimaticaparatorpes.com
http://www.youtube.com/watch?v=Z6Pp2OTWCi0&feature=related

Mysql: usuarios parte 3 - www.ofimaticaparatorpes.com
http://www.youtube.com/watch?v=yLFTj9c7aq8&feature=relmfu

Resolución ejercicio final (3)

En este caso y como complemento al post anterior (http://bbdd2012.blogspot.com.es/2012/11/resolucion-ejercicio-final-2.html) os dejo un volcado (dump) de la Base de Datos del ejercico del Cash.

En este volcado, se incluyen las instrucciones de creación de las tablas, así como las de inserción de datos, para poder proceder a crear las consultas que se han solicitado en el enunciado del proyecto.

Descargar volcado de Base de Datos del ejercicio del Cash

Nota:
Se trata de un fichero SQL, con el volcado de los datos.

El proceso para utilizarlo, pasa por crear una nueva Base de Datos, e importar los datos desde este fichero, proceso que se puede realizar fácilmente desde PhpMyAdmin.

Paso 1:

Paso 2:

Resolución ejercicio final (2)

En esta segunda parte trataremos la creación de las tablas de la base de datos.

En un primer momento crearemos las tablas asignando los tipos de datos correctos a cada tabla, pero sin preocuparnos por los índices ni las restricciones (no se piden para este ejercicio).

El primer paso es crear la base de datos:

create database cash;

En segundo lugar se creará la tabla ivas, que contendrá dos campos, un id y el valor del IVA, ambos datos de tipo entero.

create table ivas (codigo_iva int, tipo_iva int);

Otra tabla sencilla es la de almacenes, en la que se almacena un id entero y una descripción de tipo varchar.

create table almacenes (id_almacen int, nombre varchar(50));

Otra tabla importante es la de artículos:


create table articulos
(
id_articulo int,
nombre varchar(50),
codigo_barras char(17),
familia varchar(15),
neto float,
codigo_iva int,
descatalogado bool
);

NOTA:

Tal como comentamos en clase, cabe puntualizar que el menor espacio a ocupar por un dato en una base de datos es el equivalente a un byte (8 bits).

Este tipo de datos se implementa en MySql como tinyint.

Por lo tanto, aunque deseemos almacenar una variable que solo trate dos valores (true, false), como es el caso de una variable booleana, el Sistema Gestor de la Base de Datos (SGBD), lo implementará como un byte.

Este caso se puede ver en la siguiente captura, como en el punto 1, se crea el campo como bool mediante la instrucción SQL Create Table, mientras que en el punto 2 se puede ver como este campo ha sido implementado en la Base de Datos, mediante un Tinyint. 

Para poder gestionar los stocks de los distintos almacenes, es preciso crear una tabla que almacene el valor del stock como elemento de tipo float (es posible almacenar medio metro cúbico de arena) y un índice para el artículo y otro para el almacén.

create table ubicacion (id_articulo int, id_almacen int, stock float);

Las tablas de clientes y proveedores son similares, la única diferencia es el nombre del campo id.

create table clientes (id_cliente int, nombre varchar(50), nif varchar(15), direccion varchar(50), poblacion varchar(50), provincia varchar(50), email varchar(100));

Las facturas se dividen en dos tablas, por una parte la cabecera y por otra las líneas:

create table cab_fv

(

id_fv int, 

id_cliente int, 

fecha date

);

create table lin_fv

(

id_fv int, 

id_articulo int,

descripcion varchar(50),

cantidad float,

iva int,

neto float

);

Las tablas para los albaranes de compra son idénticas a las de las facturas de venta:

create table cab_ac

(

id_ac int, 

id_proveedor int, 

fecha date

);

create table lin_ac

(

id_ac int, 

id_articulo int,

descripcion varchar(50),

cantidad float,

iva int,

neto float

);

Quedando finalmente las siguientes tablas:

Resolución ejercicio final (1)

Buenas tardes:

Tal como hablamos en casa, os publico el esquema ER de la base de datos del ejercicio final.

Se puede ver un poco más claro en el siguiente enlace.

https://docs.google.com/drawings/d/1B6sQYsLJf7yCRJPwJ4tmoZncRoNoHt3hbBQvTFY3kjs/edit

Where y having

Explicación de la diferencia entre WHERE y HAVING.

La cláusula WHERE filtra datos antes de agrupar, o sea líneas (registros).
La cláusula HAVING filtra los grupos una vez creados.

Se procederá a explicar la siguiente consulta SQL, en la que se mezclan ambas cláusulas:

 SELECT product_cd, SUM(avail_balance) prod_balance
 FROM account
 WHERE last_activity_date >= '2004-12-01'
 GROUP BY product_cd
 HAVING MIN(avail_balance) >= 1000
  AND MAX(avail_balance) <= 10000; 


Se mostrará una tabla con los campos product_cd, SUM(avail_balance) de la tabla account.

De los datos de la tabla, se descartarán los registros que no cumplan la condición WHERE, esto es, sólo se tendrán en cuenta los registros con last_activity_date mayor que el 1 de Diciembre de 2004.

En este caso de todos los registros de la tabla, se queda solo con los que cumplen el criterio de la condición WHERE (en la imagen superior resaltados en amarillo).

Con los registros seleccionados, se crean grupos usando para ello la columna product_cd GROUP BY product_cd.

Este paso genera cinco grupos, y sería el resultado de la consulta sin la subcláusula HAVING.

La subcláusula HAVING se aplica sobre estos grupos creados.

En concreto en este caso, se comprobará que en estos grupos, el mínimo de las cuentas sea mayor o igual de 1000 y el máximo sea menor o igual de 10000.


 HAVING MIN(avail_balance) >= 1000
  AND MAX(avail_balance) <= 10000; 


Observando la imagen anterior, se puede observar que los grupos CHK y SAV no cumpen la condición del mínimo mayor o igual que 1000; mientras que los grupos CHK y SBL no cumplen la condición del máximo menor o igual que 10000.

Por lo tanto la subcláusula HAVING eliminará los grupos CHK, SAV y SBL del conjunto de resultados (resultset).

Quedando solo los grupos que cumplen las condiciones, en este caso CD y MM;

En este caso ambos grupos cumplen las dos condiciones, teniendo casualmente solo una cuenta que cumpla el filtro de la fecha impuesto por la cláusula WHERE.

Ejercicio final curso BBDD

Como ejercicio final en el que podáis practicar la mayor parte de los conocimientos que estamos estudiando, os propongo un proyecto que deberéis desarrollar de forma individual.

Os propongo la creación de una Base de Datos para la gestión de un CASH.

Este ejercicio, permite crear un producto bastante parecido a lo que tendría una empresa real, pero sin las complicaciones de la mayoría de las empresas.

Nuestro Cash, tendrá como características las siguientes:

• La mercancía se recibe con albaranes valorados.
• Al realizar la venta siempre se generan facturas.
• Todos los productos se venden al mismo IVA al que se compran.
• Todas las facturas de venta tienen números correlativos (no se reinician anualmente)
• Solo existe un proveedor por cada artículo.
• Se tratarán tres tipos de IVA (normal, reducido y seperreducido) actualmente (21, 10 y 4)

Requisitos del proyecto:

• La base de datos debe permitir gestionar, al menos, Clientes, Proveedores, Artículos  Facturas de Venta, Albaranes de Compra.
• Crear las tablas necesarias para gestionar la Base de Datos.
• Normalizar las tablas.
• Identificar claves primarias y foráneas.
• Crear los índices necesarios.
• Crear las restricciones oportunas.
• Se deberán incluir subconsultas.
• Se deberán incluir algún tipo de Join.
• Se deberán incluir consultas de agrupación.

Crear consultas que respondan a las siguientes cuestiones:

1. Buscar un Artículo por nombre, familia, código y código de barras.
2. Buscar un Cliente por nombre, código y DNI/CIF.
3. Buscar un Proveedor por nombre, código y DNI/CIF.
4. Listar Clientes sin email.
5. Listar Proveedores sin email.
6. Crear un único listado que incluya los emails de Clientes y Proveedores.
7. Ver líneas de factura.
8. Ver cabecera de factura.
9. Listar Facturas entre fechas.
10. Listar Facturas de un Cliente.
11. Listar Albaranes de un Proveedor.
12. Ver totales de cliente.
13. Ver totales de cliente entre fechas.
14. Ver stock de un producto.
15. Ver stock de todos los productos.
16. Ver stock por almacén.
17. Localizar Artículos en almacenes.
18. Listar artículos de un familia.
19. Actualizar precios de Artículos.
20. Descatalogar artículos.
21. Eliminar artículos descatalogados.
22. Generar listado de Clientes y Proveedores para generar el modelo 347 (3005,06 €).
23. Listar los proveedores de los que ha comprado artículos un cliente determinado.
24. Listar los clientes que han comprado un artículo determinado.
25. Listar los clientes de una población determinada.
26. Listar ventas de artículos entre fechas.
27. Listar ventas de artículos entre fechas a un cliente determinado a partir de su id.
28. Listar ventas de artículos entre fechas a un cliente determinado a partir de su DNI/CIF.
29. Listar Artículos de un tipo de IVA.

Ejercicio conocidos

Partiendo de una base de datos con solo dos tablas, obtener la siguiente información:

• Nombres de conocidos a los que les guste el fútbol.
• Aficiones del amigo numero 1.
• Aficiones de Pepe.
• Numero de amigos.
• Numero de aficiones distintas.
• Numero de amigos por afición.
• Amigos y número de aficiones que tiene cada uno.
• Amigos a los que les guste cualquier modalidad de fútbol (fútbol, fútbol sala, futbito, futbolín, etc).
• Amigos a los que no les guste el fútbol.
• Amigos a los que les guste el fútbol y el tenis.

Se trata de una base de datos de conocidos, que consta de dos tablas, una de conocidos y otra de aficiones.

Por ahora no se aplicarán ni campos clave, ni índices.

Descripción de las tablas.

Descripción de la tabla conocidos:

Descripción de la tabla aficiones:

Contenido de las tablas.

Contenido de la tabla conocidos.

Contenido de la tabla aficiones.

Consultas a realizar a la base de datos.

Nombres de conocidos a los que les guste el fútbol.

En esta consulta solo se nos piden datos de la tabla conocidos, por lo que tenemos dos opciones para obtener ese dato.

Mediante un JOIN.

Mediante una SUBCONSULTA.

Aficiones del amigo numero 1.

En la tabla aficiones, se encuentra tanto el id, como la afición, por lo que para obtener esta información es suficiente con un consulta a la base de datos aficiones, filtrando el id = 1.

Aficiones de Pepe

En este caso a diferencia de la consulta anterior, la información que queremos obtener (la afición) y el dato por el que queremos filtrar ("Pepe") están en tablas distintas.

Al igual que en el caso de los conocidos a los que les gusta el fútbol  solo se piden datos de una tabla, por lo que se vuelven a presentar dos posibilidades para obtener la información, ya sea mediante una subconsulta o un Join.

Mediante un JOIN.

Mediante una SUBCONSULTA.

Numero de amigos

En realidad lo que se pide es contar cuantas filas tiene la tabla conocidos.

En este caso se usará una función de agregado, pero sin utilizar Group By.

Numero de aficiones distintas

Similar a la consulta anterior, pero en este caso lo que se pide en realidad es contar cuantas filas distintas hay en la tabla, por lo que se añadirá el modificador DISTINCT

Numero de amigos por afición

Similar a las anteriores, pero en este caso se pide información para cada afición, por lo que habrá que realizar grupos de filas (registros), utilizando para ellos GROUP BY.

Amigos y número de aficiones que tiene cada uno.

En este caso nos piden también información agrupada, pero que proviene de varias tablas, por lo que será preciso utilizar un join.

Amigos a los que les guste cualquier modalidad de fútbol (fútbol, fútbol sala, futbito, futbolín, etc).

Nuevamente se pide información proveniente de una tabla filtrandolos por los datos de otra tabla.

En este caso se filtrará mediante el operador LIKE.

Mediante un JOIN.

Mediante una SUBCONSULTA.

Amigos a los que no les guste el fútbol.

Para obtener este tipo de consultas, es suficiente con añadir el operador NOT a una consulta que muestre los datos que no se desean.

Amigos a los que les guste el fútbol y el tenis.

Una posible solución, es filtrar a los amigos que tengan una de las aficiones (en este caso tenis), y usar otra consulta para reducir más los resultados, seleccionando los que tienen como afición futbol, pero solo entre los que también tiene tenis.

Trabajo ejemplo bbdd

Vamos a realizar un ejercicio conjunto.

En la hoja de calculo que enlazo, cada uno añadirá ejemplos de cosas que usen bases de datos. Si es posible incluyendo un enlace a la web.

https://docs.google.com/spreadsheet/ccc?key=0AqWlzM8W-gAEdGVGQW42Z1hyVkpQSjdfXzdCVzJuSFE

Base datos banco

Tablas de la base de datos de ejemplo en Excel.
https://docs.google.com/open?id=0B6WlzM8W-gAEcnY5VEdyY2V0Y3M