Lenguaje de control de datos en SQL Buri Jimenez Cortes Fundamentos de bases de datos Instituto IACC 30-07-2018
Revise el siguiente caso de estudio y responda a los requerimientos: 1-El Instituto Profesional Estudio recientemente ha implementado un sistema de tickets para llevar registro y seguimiento de las solicitudes que hacen los estudiantes al Departamento de Atención al Estudiante (DAE). Dentro del departamento, existe un directo r, dos supervisores y “n” ejecutivos de atención al estudiante. El sistema recién implementado no cuenta con los perfiles de seguridad necesarios, por lo que cualquier colaborador del departamento puede realizar lo que desee con la base de datos. Usted como conocedor del área debe elaborar un informe en el cual exponga los comandos de SQL que podrían servir para el caso, además de esbozar, de acuerdo a los cargos que se tienen en el departamento, cómo deberían ser las instrucciones que otorguen o retiren ciertos permisos sobre la base de datos. Asuma que se tienen las siguientes tablas: Estudiantes Colaboradores, Tickets, Motivos_Contacto. Usted puede determinar los registros y campos que tendrán cada una de las tablas. 2- Se tiene la siguiente tabla que registra para una librería el código del autor, el código del libro,
nombre del libro, código de la editorial, precio y porcentaje de descuento:
A partir de las funciones abordadas durante la semana de estudio, entregue las siguientes consultas en SQL: 1. Liste la cantidad de libros que tiene la librería actualmente. 2. Indique el promedio de precios de los libros pertenecientes al autor con código = ‘0001’. 3. Indique el total que recibiría la librería si vende en este momento todos los libros, sin aplicar ningún tipo de descuento.
4. Indique el libro con el menor precio. 5. Indique el libro con el mayor precio. 6. Tomando en cuenta el porcentaje de descuento, liste todos los libros que se encuentran en la librería, y de acuerdo al descuento, imprima lo que indica el siguiente rango: Porcentaje de Descuento < = 10 imprimir mensaje “Descuento de hasta un 10%”. Porcentaje de Descuento > 10 y < = 25 imprimir mensaje “Descuento de hasta un 25%”. Porcentaje de Descuento > 25 imprimir mensaje “Descuento de más del 25%”.
Desarrollo En primera instancia debemos crear la base de datos con las tablas otorgadas anteriormente y se complementaran los campos y registros necesarios para finalizar correctamente el informe. Tablas con las que debemos de contar: Estudiantes Colaboradores Nombre Apellido 1 Rosa Fuentes Patricio Marambio Elizabeth Contreras Raimundo Sotomayor Carlos Silva Ernesto Contreras Eleonor Cifuentes
Apellido 2 Morales Lorca Gallardo Fuentes Mora Galleguillo Perez
Ticket Nro_Ticket 10001 10002 10003 10004 10005 10006 10007 10008 10009
Rut 12.546.258-5 13.524.365-K 12-985.357-6 14.658.354-9 13.658.985-0 11.258.000-3 12.546.258-5 12.365.200-K 13.658.985-0
Motivo_Contacto Nro_Ticket 10001 10002 10003 10004 10005 10006 10007 10008 10009
Motivo Solicitud de certificado de estudiante Solicitud de pase escolar Prorroga en pago mensual Solicitud Pase escolar Solicitud Pase escolar Solicitud certificado estudiante Solicita cambio fecha pago Solicitud de certificado de estudiante Solicitud de certificado de estudiante
Rut 12.546.258-5 13.524.365-K 12-985.357-6 14.658.354-9 13.658.985-0 11.258.000-3 12.365.200-K
Una vez que tenemos nuestras tablas ya creadas, y en consideración los campos y registros ingresados, podemos realizar el informe solicitado.
En este caso el informe es entregado y utilizado para conocer las falencias con las que cuenta esta base de datos y proponer cambios que van en pro de la seguridad de los datos que esta contiene. Falencias de la base de datos: Específicamente una base de datos tiene la función de almacenar información de forma ordenada según como fue creada, para este caso en tablas con registros necesarios para tener la información de lo que se desea. Esto aplica en todos los sectores y no solo en una gran empresa, una base de datos tendrá una importancia relevante, también en una pequeña empresa o base pequeña. Junto a la creación de la base de datos, de acuerdo a las tablas y orden de estas, es paralelamente importante limitar los accesos, de forma que solo tengan permitido el ingreso a estas bases o sus registros, las personas establecidas y que necesiten realizar algún trabajo con estos datos. Por lo que se debe aplicar seguridad a los datos (tablas o registros), de lo contrario se está propenso a la eliminación o modificación de estos.
Propuesta de configuración de seguridad en las tablas Debido a que en el departamento DAE encontramos un director, dos supervisores y una cantidad ilimitada de ejecutivos de atención. Los permisos deberían ser programados como sigue: Primero, aunque no debería ser, dado que solo se debe otorgar privilegios según cargo y datos que corresponden a su función laboral, se le dará todos los privilegios en las tres tablas al
director y que este pueda otorgar sus permisos a otros usuarios, por lo que se usara With Grant Option: -
Grant All Privileges on Estudiantes_Colaboradores to director With Grant Option; Aunque esta tabla, no se debería tocar ya que corresponde a los datos del estudiante por lo que se puede usar en todas las áreas del Instituto, no sólo en el DAE.
-
Grant All Privileges on Ticket to director With Grant Option;
-
Grant All Privileges on Motivo_Contacto to director With Grant Option;
A los dos Supervisores se les dará privilegios de actualización y eliminación de datos solo a las tablas Ticket y Motivo_Contacto ya que la tabla Estudiante_Colaborador debe ser una tabla general y no del área DAE. -
Grant Update on ticket to Supervisor
-
Grant Update on Motivo_Contacto to Supervisor
-
Grant Delete on ticket to Supervisor
-
Grant Delete on Motico_Contacto to Supervisor
También puede ser: -
Grant Update, Delete on ticket to Supervisor;
-
Grant Update, Delete on Motivo_Contacto to Supervisor;
Por último a los E jecutivos se les dará privilegios de insertar y consulta d e datos. -
Grant Insert, Select on ticket to Ejecutivos;
-
Grant Insert, Select on Motivo_Contacto to Ejecutivos;
Fase II 1- Liste la cantidad de libros que tiene la librería actualmente. SELECT COUNT(*) FROM libros;
2- Indique el promedio de precios de los libros perte necientes al autor con código = ‘0001’. select avg(precio) from libros where codigo_autor like '%0001%';
3- Indique el total que recibiría la librería si vende en este momento todos los libros, sin aplicar ningún tipo de descuento. select sum(precio) from libros;
4- Indique el libro con el menor precio. select min(precio) from libros
5- Indique el libro con el mayor precio. select max(precio) from libros
6. Tomando en cuenta el porcentaje de descuento, liste todos los libros que se encuentran en la librería y, de acuerdo al descuento, imprima lo que indica el siguiente rango :
Porcentaje de Descuento < = 10 imprimir mensaje “Descuento de hasta un 10%”. select porc_descuento from libros where precio <= 10;
SELECT NOMBRE, PORC_DESCUENTO, IF (PORC_DESCUENTO <= 10, "DESCUENTO HASTA 10%","") AS COMENTARIOS FROM libros
Porcentaje de Descuento > 10 y < = 25 imprimir mensaje “Descuento de hasta un 25%”. select porc_descuento from libros where precio > 10 <= 25;
Porcentaje de Descuento > 25 imprimir mensaje “Descuento de más del 25%”. select porc_descuento from libros where precio > 25;
Bibliografía [Contenidos semana 5, Fundamentos de bases de datos, IACC 2018.]
