Presentación de PowerPoint - catedras.facet.unt.edu.ar · El lenguaje de Definición de Datos...
Transcript of Presentación de PowerPoint - catedras.facet.unt.edu.ar · El lenguaje de Definición de Datos...
Catedra de Base de Datos
Facultad de Ciencias Exactas y Tecnología
Universidad Nacional de Tucumán
Ciclo Lectivo 2017
“El algebra relacional es un conjunto de operaciones que describen paso a paso como calcular una respuesta sobre las relaciones componiendo un lenguaje formal basado en operadores y que utiliza para ello relaciones” Tipificación En las operaciones binarias existen restricciones referidas al GRADO(atributos) o a la CARDINALIDAD(tuplas). Por lo que cada Relación seria una tabla bidimensional en donde cada renglón o tupla, tiene datos que pertenecen a alguna cosa, y en donde cada columna o atributo pertenecen a esta tabla.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Operador Selección
“Se encuentra simbolizado mediante σ, permite seleccionar un subconjunto de tuplas de una
relación (R), todas aquellas que cumplan la(s) condición(es) P”, esto es:
σP(R)
Ejemplo: Tenemos la siguiente Tabla
σFecha_Alta '01/01/1998'(Clientes)
NroCli Nombre Fecha_Alta Saldo
1 Enrique 14/09/1996 100
3 German 19/12/1994 500
4 Alejandro 02/01/1999 90
NroCli Nombre Fecha_Alta Saldo
4 Alejandro 02/01/1999 90
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Operador Proyección
“Se encuentra simbolizado mediante π, permite extraer columnas (atributos) de una relación,
dando como resultado un subconjunto vertical de atributos de la relación”, esto es:
Π A1,A1,…,An
donde A1, A2,…, An son atributos de la relación R .
Ejemplo: Tenemos la siguiente Tabla
Πnombre, saldo(Clientes)
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Operadores
Operador Unión
“La unión se denota por A + B” .
Operador Diferencia
“La diferencia se denota por A - B” .
Operador Intersección
“La intersección se denota por A ∩ B” .
Operador Producto
“El producto se denota por A x B” .
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Primera Forma Normal (1NF): Una tabla está en Primera Forma Normal si:
• Todos los atributos son atómicos. Un atributo es atómico si los elementos del dominio son
indivisibles, mínimos.
• La tabla contiene una llave primaria única, y no contiene valores nulos.
• No debe existir variación en el número de columnas.
• Los Campos no llave deben identificarse por la llave (Dependencia Funcional).
• Debe Existir una independencia del orden tanto de las filas como de las columnas, es decir,
si los datos cambian de orden no deben cambiar sus significados.
• Una tabla no puede tener múltiples valores en cada columna. Los datos son atómicos. (Si a
cada valor de X le pertenece un valor de Y , y viceversa).
• Esta forma normal elimina los valores repetidos dentro de una BD.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Segunda Forma Normal (2NF): Dependencia Funcional. Una relación está en 2FN si está en
1FN y si los atributos que no forman parte de ninguna clave dependen de forma completa de la
clave principal. Es decir que no existen dependencias parciales. (Todos los atributos que no son
clave principal deben depender únicamente de la clave principal).
Por ejemplo {DNI, ID_PROYECTO} HORAS_TRABAJO (con el DNI de un empleado y el ID de un
proyecto sabemos cuántas horas de trabajo por semana trabaja un empleado en dicho
proyecto) es completamente dependiente dado que ni DNI HORAS_TRABAJO ni ID_PROYECTO
HORAS_TRABAJO mantienen la dependencia. Sin embargo {DNI, ID_PROYECTO}
NOMBRE_EMPLEADO es parcialmente dependiente dado que DNI NOMBRE_EMPLEADO
mantiene la dependencia.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Tercera Forma Normal (3NF): Una relación La tabla se encuentra en 3FN si es 2FN y si no
existe ninguna dependencia funcional transitiva entre los atributos que no son clave.
Un ejemplo de este concepto sería que, una dependencia funcional X->Y en un esquema de
relación R es una dependencia transitiva si hay un conjunto de atributos Z que no es un
subconjunto de alguna clave de R, donde se mantiene X->Z y Z->Y.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL
Definición 1: “El SQL (Structured Query Languaje, Lenguaje de Consulta Estructurado) es un
lenguaje de acceso a bases de datos que explota la flexibilidad y potencia de los sistemas
relacionales permitiendo gran variedad de operaciones sobre los mismos, comportandose
como un lenguaje declarativo de alto nivel, que gracias a su fuerte base teórica y su orientación
al manejo de conjunto de registros, permite una alta productividad en codificación“
(dr. Carlos Torres Gastelu)
Definición 2: ” SQL es un lenguaje que permite expresar diversas operaciones , por ejemplo
aritméticas, combinatorias, lógicas, de selección y ordenamiento, con datos almacenados en
Base de Datos relacionales, cuya información esta contenida en estructuras llamadas tablas, y
donde los datos están dispuestos en filas y columnas”
(Enrique Rivero et al)
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Arquitectura Estándar de Tres niveles
Los sistemas de gestión base de datos relacional soportan la arquitectura estándar en tres
niveles.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Nivel Lógico El nivel lógico para las bases de datos relacionales consiste en tablas base que se almacenan físicamente. Estas tablas se crean mediante el Administrador de Base de Datos (ABD) con el uso del comando CREATE TABLE (crear tabla). Una tabla base puede tener cualquier numero de índices, creados por el ABD usando el comando CREATE INDEX(crear índice). Un índice se usa para acelerar la recuperación de registros con base en el valor en una o más columnas . Un índice menciona los valores que existen para la columna indexada y la ubicación de los registros que tienen dichos valores. Nivel Físico En el nivel físico, las tablas base se representan, junto con sus índices, en archivos. La representación física de las tablas puede no corresponder exactamente con la noción de una tabla como un objeto bidimensional que consiste en filas y columnas. Sin embargo, las filas de la tabla si corresponden con los registros almacenados físicamente, aunque su orden y otros detalles de almacenamiento pueden ser diferentes del concepto de ellos. Vistas Una vez creadas las tablas bases, se pueden crear “VISTAS” para los usuarios, con el uso del comando CREATE VIEW (crear vista). Una vista puede ser un subconjunto de una sola tabla base, o bien crearse al combinar tablas bases.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL consta de varios componentes
• Lenguaje de Definición de Datos (DDL): Proporciona comandos para la definición de
esquemas de relación, borrado de relaciones y modificaciones a los esquemas de relación.
• Lenguaje Interactivo de Manipulación de Datos (DML): Incluye un lenguaje de consultas
basado tanto en el algebra relacional, como en el calculo relacional de tuplas.
• Integridad: El DDL incluye comandos para especificar las restricciones de integridad que
deben cumplir los datos almacenados en la base de datos.
• Control de Transacciones: Incluye comandos para especificar el comienzo y el final de las
transacciones.
• Autorización: Incluye comandos para especificar los derechos de acceso a las relaciones y a
las vistas.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
El lenguaje de Definición de Datos (DDL)
Permite:
• Especifica un conjunto de relaciones.
• Proporcionar información relativa a un conjunto de relaciones.
Permite Conocer:
• El esquema de cada relación.
• El dominio de valores asociados a cada atributo.
• Las restricciones de integridad.
• El conjunto de índices que se deben mantener para cada relación.
• La información de seguridad y de autorización de cada relación.
• A estructura de almacenamiento físico de cada relación en el disco
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Los comandos más importantes en DDL son: CREATE TABLE CREATE TABLE nombre_tabla (nombre_columna tipo_columna, [restricciones – NULL/NOT NULL, DEFAULT, UNIQUE, CHECKJ],……, [restricciones tabla – PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK….]) CREATE INDEX CREATE INDEX nombre_indice ON nombre_tabla (nombre_columna); ALTER TABLE ALTER TABLE nombre_tabla ADD nombre_columna tipo_columna; DROP TABLE DROP TABLE nombre_tabla; DROP INDEX DROP INDEX nombre_indice;
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Tipos de Datos –DDL
Los tipos de datos disponibles concluyen varios tipos numéricos, cadenas de caracteres de
longitud fija y de longitud variable, cadenas de bits y tipos definidos por el usuario. Los tipos
de datos disponibles varían de DBMS a DBMS.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Manipulación de la Base de Datos (DML)
El lenguaje de consulta de SQL es declarativo, también llamado no procedural, lo que significa
que permite especificar cuales datos se recuperan sin dar los procedimientos para
recuperarlos. Se puede usar como un lenguaje interactivo para consultas, incrustado en un
lenguaje de programación huésped, o como un lenguaje completo en si para cálculos con el uso
de SQL/PSM (Persistent Stored Modules= Modulos de almacenamiento persistentes).
Los comandos DML son:
SELECT
UPDATE
INSERT
DELETE
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Los comandos DML son: UPDATE UPDATE nombre_tabla SET “nombre_columna1”=“nuevo_valor,” [“nombre_columna2”= “nuevo_valor2,….”] WHERE condición; INSERT INSERT INTO nombre_tabla (“nombre_columna1”,”[nombre_columna2…..]”) VALUES (“valor1”, “[valor2...]”); DELETE DELETE FROM nombre_tabla WHERE nombre_columna1=valor1; SELECT
SELECT [ALL | DISTINCT ] nombre_columna [,nombre_columna…]
FROM nombre_tabla|nombre_vista [nombre_tabla|nombre_vista…]
[WHERE condicion [AND|OR condicion]]
[GROUP BY nombre_columna [nombre_columna …]]
[HAVING condicion [AND|OR condicion]] [ORDER BY nombre_columna|índice_columna]]]
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Procedimientos Almacenados
Definición
- Un procedimiento almacenado es un conjunto de sentencias SQL y de control de flujo.
- Un procedimiento almacenado es una colección de instrucciones guardadas en la Base de
Datos.
- La ventaja de un procedimiento almacenado es que al ser ejecutado , este se ejecuta
directamente en el motor de base de datos, el cual usualmente corre en un servidor por
separado.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Procedimientos Almacenados
Ventajas
- Simplifican la ejecución de tareas repetitivas.
- Corren mas rápido que las mismas instrucciones ejecutadas en forma interactiva.
- Reducen el trafico a través de la red.
- Pueden capturar errores antes que ellos puedan entrar a la base de datos.
- Establece consistencia porque ejecuta las tareas de la misma forma.
- Permite el desarrollo modular de aplicaciones.
- Ayuda a proveer seguridad.
- Puede forzar reglas y defaults complejos de los negocios.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Procedimientos Almacenados
Tipificación
- Procedimientos almacenados definidos por el usuario: son procedimientos definidos por el
usuario que se debe llamar explícitamente.
- Triggers: Son procedimientos definidos por el usuario que se ejecutan automáticamente
cuando se modifica un dato en un tabla.
- Procedimientos del sistema: Procedimientos suministrados por el sistema.
- Procedimientos Extendidos: Procedimientos que se hacen llamadas al sistema operativo y
ejecutan tareas a ese nivel.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Procedimientos Almacenados
Ventajas en el rendimiento
- Un procedimiento almacenado se ejecuta más rápido que un batch porque:
- El procedimiento almacenado ya ha sido analizado.
- Ya se han resuelto las referencias a los objetos referenciados en el procedimiento
almacenado.
- No se necesita construir el árbol de búsqueda, el usa el que se hace en el momento de
compilarlo.
- No se necesita crear un plan de búsqueda, porque ya el procedimiento tiene uno.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Procedimientos Almacenados
Crear y borrar procedimientos almacenados - Sintaxis de creación:
CREATE PROCEDURE nombre_procedimiento (IN parametros) BEGIN Sentencias. END Ejemplo: CREATE PROCEDURE `prueba`.`ps_verpais`() BEGIN SELECT * FROM pais; END$$
- Sintaxis de borrado: DROP PROCEDURE nombre_procedimiento
Ejemplo:
DROP PROCEDURE ps_verpais
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Procedimientos Almacenados
VARIABLES Los procedimientos almacenados pueden crear y usar variables locales, las cuales solo existen mientras exista el procedimiento. Las variables no las puede usar otro proceso. SENTENCIAS VALIDAS Un procedimiento almacenado puede: - Seleccionar y modificar datos; - Crear tablas temporales y permanentes. - Llamar a otros procedimientos almacenados. - Referenciar objetos de base de datos. SENTENCIA INVALIDAS Un procedimiento no puede ejecutar: - Use database. - Create view. - Create default. - Create rule. - Create procedure. - Create trigger.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Trigger
Un trigger es un procedimiento almacenado asociado con una tabla, el cual se ejecuta automáticamente
cuando se modifica un dato de esa tabla.
Un trigger se define asociado con una tabla para una o mas sentencias de manipulación de datos. Se puede
definir para insert, update, o delete o cualquier combinación de ellos.
APLICACIONES TÍPICAS
- Hace modificaciones en cascada sobre tablas relacionadas.
- Deshacer cambios que violan la integridad de los datos.
- Forzar restricciones que son muy complejas para reglas y restricciones.
- Mantener datos duplicados.
- Mantener columnas con datos derivados.
- Hacer ajustes de registros.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Trigger
Un trigger pueden:
- Declarar variables locales.
- Invocar procedimientos almacenados.
Un trigger no puede:
- Llamarse directamente.
- Usar parámetros.
- Definirse sobre tablas temporales o vistas.
- Crear objetos permanentes de la base de datos.
Las operaciones con registro minimo (como select into) no disparan los triggers.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Trigger
SENTENCIA SQL OLD NEW
Insert No definido; todos los campos toman valor NULL
Valores que serán insertados cuando se complete la orden.
Update Valores originales de la fila, antes de la actualización
Nuevos valores que serán escritos cuando se complete la orden.
Delete Valores antes del borrado de la fila.
No definido; todos los campos toman el valor NULL.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Trigger
Ejemplo CREATE TABLE clientes( id_cliente int not null auto_increment, Nombre varchar(20), Seccion varchar(20), Accion varchar(10) default ‘insertado’, PRIMARY KEY(id_cliente)) ENGINE = innoDB; CREATE TABLE auditoria_clientes ( Id int not null auto_increment, Nombre_anterior varhcar(20), Seccion_anterior varchar(20), Nombre_nuevo varchar(20), Seccion_nuevo varchar(20), Usuario varchar(20), Modificado datetime, Prceso varchar(10), Id_cliente int(4), PRIMARY KEY (Id)) ENGINE = innoDB;
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Trigger
CREATE TRIGGER inserta_auditoria_clientes AFTER INSERT ON clientes FOR EACH ROW INSERT INTO auditoria_clientes(nombre_nuevo, sección_nuevo. Usuario, modificado, proceso, id_cliente) VALUES (NEW.nombre, NEW.sección, CURRENT_USER(), NOW(), NEW.acción, NEW.acción, NEW.id_clente); CREATE TRIGGER modifica_auditoria_clientes BEFORE UPDATE ON clientes FOR EACH ROW INSERT INTO auditoria_clientes(nombre_anterior, sección_anterior, nombre_nuevo, sección_nuevo. Usuario, modificado, proceso, id_cliente) VALUES (OLD.nombre, OLD.sección, NEW.nombre, NEW.sección, CURRENT_USER(), NOW(), NEW.acción, NEW.acción, NEW.id_clente); CREATE TRIGGER elimina_auditoria_clientes AFTER DELETE ON clientes FOR EACH ROW INSERT INTO auditoria_clientes(nombre_anterior, sección_anterior. Usuario, modificado, id_cliente) VALUES (OLD.nombre, OLDsección, CURRENT_USER(), NOW(), NEW.acción, OLD.id_clente);
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
SQL – Vistas
Una vista es un SELECT almacenado. El motor distingue dos tipos de tabla, las base y las derivadas. Una tabla base es una tabla existente en el motor de almacenamiento. Una tabla derivada, es la tabla que surge de cualquier combinación de tablas base, literales y/o funciones.
Funciones de una vista
- Proporcionar un nivel de seguridad, ya que permiten excluir datos para que ciertos usuarios no los vean.-
- Proporcionan un mecanismo para que los usuarios vean los datos en el formato que deseen.-
- Representan una imagen consistente y permanente de la base de datos, incluso si la base de datos cambia su estructura.
Sintaxis CREATE VIEW nombre_vista AS <sentencia sql> END
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Gene Spafford, profesor de ciencias informáticas en la Universidad Purdue (Indiana, EEUU) y experto en seguridad de datos, dijo que: “El único sistema seguro es aquel que está apagado y desconectado, enterrado en un
refugio de cemento, rodeado por gas venenoso y custodiado por guardianes bien pagados y muy bien armados. Aún así, yo no apostaría mi vida por él”
Amenazas a las bases de datos • Pérdida de integridad: Está referida a la protección contra las modificaciones de la
información por el atacante.
• Pérdida de disponibilidad: Tanto para un usuario como un programa se debe garantizar el acceso cuando se requiera.
• Pérdida de confidencialidad: Relacionada con la protección al acceso no autorizado a los datos.
Seguridad
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Un DBMS además de estar preparado contra un ataque y de poder detectarlo en caso de que suceda, debería ser capaz de realizar lo siguiente: • Aislamiento: Eliminar el acceso del atacante y aislar o contener el problema
evitando que se extienda. • Evaluación de los daños: Determinar el alcance del problema. • Reconfiguración: Permitir que continúe el funcionamiento aún estando
dañado mientras se lleva a cabo la recuperación. • Reparación: Tanto de los datos perdidos o corrompidos como de las
funciones dañadas del sistema. • Tratamiento de los fallos: Identificar las debilidades que han sido
aprovechadas por el ataque.
Seguridad
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
El administrador de la base de datos es el responsable principal de la gestión de un sistema de bd por lo tanto es el encargado de: • Creación de cuentas. • Concesión de privilegios. • Retirada de privilegios. • Asignación del nivel de seguridad. Medidas de Control Existen 3 medidas principales que se utilizan para proporcionar seguridad a los datos: • Control de Accesos • Control de Flujo • Cifrado de datos
Seguridad – Rol de Administrador
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Discrecional: Basado en concesión, revocación de privilegios y propagación de privilegios. • Nivel de cuenta: El DBA especifica los privilegios en particular que posee
cada cuenta, independientemente de las relaciones existentes en la base de datos.
• Nivel de relación: El DBA puede controlar el privilegio de Acceso a cada relación en la base de datos. Existe la concesión de privilegios. (Grant Option)
Existen tres tipos de privilegios que se pueden conceder: • Privilegio de Selección • Privilegio de modificación • Privilegio de referencia
Seguridad – Control de Accesos
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Comandos para concesión de privilegios: • Create Schema ejemplo Authorization usuario1
• Grant Insert, Delete On empleado, Departamento To Usuario2
• Grant Select On empleado, Departamento To usuario3 With Grant Option
• Revoke Select On Empleado From usuario3
• Grant Update On Empleado(sueldo) To usuario4
Seguridad – Control de Accesos
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
El control de acceso obligatorio apunta a clasificar la información contenida dentro de la bases de datos en distintos niveles, por ej: Ultra Secreto – Secreto – Confidencial – No clasificado.- Seguridad – Control de Flujo El control de flujo regula distribución o flujo de información entre objetos accesibles. Cuando un programa lee valores en X y escribe valores en Y. Una política de flujo sencilla especifica dos clases de información Confidencial y No confidencial permitiendo todos los flujos excepto aquellos que fluyen de C a N.
Seguridad – Control de Acceso Obligatorio
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
El cifrado se utiliza para el caso en que el atacante tenga acceso a la lectura de los datos, estos vayan desvirtuados de tal forma que el atacante no pueda descifrarlo. Cifrado con clave pública: Cada Usuario genera un par de claves a utilizar. Cada Usuario coloca una clave en algún lugar público. El emisor cifra el mensaje con la clave pública del receptor. El receptor descifra el mensaje con su clave privada Firmas Digitales: Sirve para autenticar el usuario que generó el mensaje, utilizados por ejemplo en el comercio electrónico, deben ser:
• Únicas • Infalsificables • Verificables • Innegables • Viables
Seguridad – Cifrado de Datos
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Seguridad
Cifrado de datos
David redacta un mensaje David firma digitalmente el mensaje con su clave privada David envía el mensaje firmado digitalmente a Ana a través de internet, ya sea por correo electrónico, mensajería instantánea o cualquier otro medio Ana recibe el mensaje firmado digitalmente y comprueba su autenticidad usando la clave pública de David Ana ya puede leer el mensaje con total seguridad de que ha sido David el remitente
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Fundamentos de bases de datos / Abraham Silberschatz, Henry F. Korth /y/ S. Sudarshan.—(Tra. Fernándo Sáenz Pérez, Antonio García Cordero /y/ Jesús Correas Fernández.-- Rev. Tca. Luis Grau Fernández). McGraw Hill. Madrid /c.2008/5a. Edic.
Introducción al SQL para Usuarios y Programadores / Cornelio et al / THOMSON / 2003 / 2da. Edic
Fundamentos de sistemas de bases de datos / Ramez Elmasri /y/ Shamkant B. Navathe.—(Tra. Verónica Canivell Castillo, Beatriz Galán Espiga /y/ Gloria Zaballa Pérez.--Rev. Tca. Alfredo Goñi Sarriguren , Arturo Jaime Elizondo /y/ Tomás A. Pérez Fernández) Pearson Educación. Madrid /c.2002/3a. ed.
Bibliográficas Principal
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Bibliografía
• Paper “A relational model for a large shared data banks”, E. F. Codd. ” – ACM – 1970.
• “Procesamiento de bases de datos. fundamentos, diseño e implementación” - David M. Kroenke — (Tra.
Ana Elizabeth García Hernández.--Rev. Tca. Juan Raúl Esparza Martínez). Pearson Educación – México -
c.2003 – 8ª Edic.
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez
Sitio Web de la Cátedra http://catedras.facet.unt.edu.ar/bd
BASES DE DATOS
Bases de Datos Ing. Franco D. Menendez