Control semana 5 Vanessa Gómez M Fundamento de base de datos Instituto IACC 10 de febrero de 2018
Fase I: Revise el siguiente caso de estudio y responda a los requerimientos: 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 director, 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 co mandos de SQL que podrían servir para el caso, ad emás de esbozar, de acuerdo con 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 asumir los registros y campos que tengan cada una de las tablas. Desarrollo.
Primero comenzare con un cuadro en donde se identificarán (DCL, DML, DDL y TCL).
DCL: lenguaje de control de datos.
DML: Lenguaje de manipulación de datos.
DDL: lenguaje de definición de datos o comandos.
TCL: Lenguaje de control de transacciones.
SQL Language Statements.
TCL
DCL
DML
DDL
GRANT
SELECT
CREATE
BEGIN
REVOKE
INSERT
ALTER
TRAN
UPDATE
DROP
COMMIT
DELETE
TRAN RLLBSCK
DCL: controla el nivel de acceso que tienen los usuarios sobre la base de datos. Conceder: permite leer y escribir sobre los objetos de la base de datos. Revocar: permiso sobre la lectura y escritura en los objetos de la base de datos.
DML: operaciones básicas, para seleccionar un par de registro en una tabla, se puede insertar, eliminar, actualizar y modificar registros, estos son: Seleccionar: selecciona registros de una tabla. Insertar: se insertan nuevos registros. Actualizar: actualizar y modificar los registros que ya existen. Borrar: elimina registros que ya existen.
DDL: estas operaciones se utilizan para alterar/ modificar un esquema y estructura de una base de datos o tabla. Crear: crea una nueva tabla. Alterar: modifica la tabla existente, agregando o eliminando. Soltar: elimina los objetos ya existentes de la base de datos.
TCL: le permiten controlar y gestionar las transacciones para man tener la integridad de los datos dentro de las sentencias SQL.
Para la seguridad lo primero que debemos hacer, es asignar atributos para que los perfiles manejen los datos necesarios, evitando así que otro trabajador pueda manipular la información, y solo trabaje con la información que a él se le ha asignado. Ahora identificare los comandos necesarios: Le daré permiso al director ya que él debe tener todos los permisos necesarios en caso de necesitar, es por eso que le daré privilegio en dos de las cuatro tablas y en las otras tendrá los privilegios de DML. Estos privilegios que se le darán, no se podrán pasar a otros trabajadores. GRANT ALL PRIVILEGES Estudiantes TO director; GRANT ALL PRIVILEGES ON colaboradores TO director; GRANT SELECT, INSERT, UPDATE, DELETE ON Tickets TO director; GRANT SELECT, INSERT, UPDATE, DELETE ON Motivos_Contacto TO director;
Supervisores.
En el caso de los supervisores, se les dará acceso completo a todas las tablas, ya que el supervisor será como un administrador.
GRANT ALL PRIVILEGES ON estudiantes TO superv_01, superv_02 WITH GRANT OPTION; GRANT ALL PRIVILEGES ON colaboradores TO superv:01, superv_02 WITH GRANT OPTION; GRANT ALL PRIVILIGES ON Tickets TO superv_01, superv_02 WITH GRANT OPTION; GRANT ALL PRIVILEGES ON Motivos_Contacto TO superv_01, superv_02 WITH GRANT OPTION;
Ejecutivos
Con respecto a los permisos para los ejecutivos de atención, le dejaremos los mismos permisos con los que cuenta un supervisor debido a su trabajo en la atención de los estudiantes. GRANT ALL PRIVILEGES ON superv_01 TO ejecutivo_01, ejecutivo_02 WITH GRANT OPTION; GRANT ALL PRIVILEGES ON Tickets TO ejecutivo_01, ejecutivo_02 WITH GRANT OPTION; GRANT ALL PRIVILEGES ON Motivos_Contacto TO ejecutivo_01, ejecutivo_02 WITH GRANT OPTION;
Fase II: 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. SELECT COUNT (*) FROM Tabla_Libro
2. Indique el promedio de precios de los libros perte necientes al autor con código = ‘0001’. Select AVG (PRECIO) From Tabla_Libros Where código_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 sun (precio) from libros;
4. Indique el libro con el menor precio. SELECT MIN(PRECIO) FROM Tabla_Libro
5. Indique el libro con el mayor precio. SELECT MAX(PRECIO) FROM Tabla_Libros
6. Tomando en cuenta el porcentaje de descuento, liste todos los libros que se encuentran en la librería, y de acuerdo con el 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;
