Download Diapositiva 1 - Docencia FCA-UNAM

UNIVERSIDAD LATINA
BASES DE DATOS
CONSTRUCCIÓN DE BASES DE DATOS
Roles del implementador de la Base de Datos
1)
2)
3)
4)
5)
6)
7)
Crear el esquema de la Base de Datos a partir de las instrucciones
SQL
Para la implementación de una Base de Datos se deben conocer las
instrucciones mínimas de SQL que son de creación (CREATE
DATABASE nombreBD) crear la Base de Datos
Crear las tablas de la Base de Datos (CREATE TABLE nombreTabla)
crea las llaves primarias y los campos relacionados.
Implementar el esquema de la BD en el Manejador de la Base de Datos
Inserción de datos (INSERT INTO nombreTabla)
Selección de datos (SELECT campos FROM nombreTabla)
Agregar llaves foraneas (ALTER TABLE tablas ADD CONSTRAINT
fk_compras_1 FOREIGN KEY fk_compras_(cliente_cod) REFERENCES
cliente(cliente_cod)
Nota: En la actualidad existen herramientas en los manejadores de Bases
de Datos que evitan el trabajo en línea de comandos.
Tablas
Tabla en las bases de datos, se refiere al tipo de modelado de datos, donde se guardan los
datos recogidos por un programa. Su estructura general se asemeja a la vista general de un
programa de hoja de cálculo.
Una tabla es utilizada para organizar y presentar información. Las tablas se componen de filas
y columnas de celdas que se pueden rellenar con textos y gráficos Las tablas se componen de
dos estructuras:
•Registro: es cada una de las filas en que se divide la tabla. Cada registro contiene datos de
los mismos tipos que los demás registros. Ejemplo: en una tabla de nombres y direcciones, cada
fila contendrá un nombre y una dirección.
•Campo: es cada una de las columnas que forman la tabla. Contienen datos de tipo diferente a
los de otros campos. En el ejemplo anterior, un campo contendrá un tipo de datos único, como
una dirección, o un número de teléfono, un nombre, etc.
A los campos se les puede asignar, además, propiedades especiales que afectan a los registros
insertados. El campo puede ser definido como índice o autoincrementable, lo cual permite que los
datos de ese campo cambien solos o sean el principal indicar a la hora de ordenar los datos
contenidos.
Cada tabla creada debe tener un nombre único en la cada Base de Datos, haciéndola accesible
mediante su nombre o su seudónimo (Alias) (dependiendo del tipo de base de datos elegida) La
estructura de las tablas viene dado por la forma de un archivo plano, los cuales en un inicio se
componían de un modo similar.
Tipos de Tablas
Tablas con particiones.- son tablas cuyos datos se han dividido horizontalmente
entre unidades que pueden repartirse por más de un grupo de archivos de una base
de datos.
Tablas temporales. Hay dos tipos de tablas temporales: locales y globales. Las
tablas temporales locales son visibles sólo para sus creadores. Las tablas temporales
globales están visibles para cualquier usuario y conexión una vez creadas, y se
eliminan cuando todos los usuarios que hacen referencia a la tabla se desconectan
Tablas del sistema.- SQL Server almacena los datos que definen la configuración
del servidor y de todas sus tablas en un conjunto de tablas especial, conocido como
tablas del sistema.
Tablas persistentes.- Son aquellas que permiten que los registros sean eliminados o
borrados manualmente y tenemos de tres tipos:
Base.- Es en donde se encuentra toda la información de todos los registros sin que
se haga ninguna validación adicional.
Vistas.- Es una vista o relación que se hace en referencia a una fila o columna
específica.
Instantáneos.- Son aquellos registros que se los puede ver de manera inmediata con
solo una referencia.
Tablas de datos.- Tabla en las bases de datos, se refiere al tipo de modelado de
datos, donde se guardan los datos recogidos por un programa. Su estructura general
se asemeja a la vista general de un programa de Hoja de cálculo.
Índices
• El índice de una base de datos es una estructura de datos
que mejora la velocidad de las operaciones, permitiendo
un rápido acceso a los registros de una tabla en una base
de datos. Al aumentar la velocidad de acceso, se suelen
usar sobre aquellos campos sobre los cuales se hacen
frecuentes búsquedas.
• Los índices pueden ser creados usando una o más
columnas, proporcionando la base tanto para búsquedas
rápidas al azar como de un ordenado acceso a registros
de forma eficiente.
• Create index pk_empleado1 on asistencia(id_empleado);
Vistas
•
Una vista es la facilidad de almacenar o predefinir consultas como si fuera un objeto de la base de
datos para su posterior uso. Las tablas consultadas en una vista son llamadas tablas bases. Con
algunas excepciones, una sentencia SELECT puede ser llamada y almacenada como una vista. Los
ejemplos mas comunes de vistas incluyen.
•
•
•
•
•
Un extracto de campos y registros de una tabla base.
La unión de dos o más tablas bases.
Un join de dos o más tablas bases.
Resultados estadísticos de una tabla base.
Una parte de una vista o una combinación de vistas y tablas base.
Ejemplo 1. Este ejemplo crea la vista VistaEmpleados en la base de datos nómina. La vista despliega 4
campos de la tabla empleados
CREATE VIEW VistaEmpleados
AS
SELECT ID_EMPLEADO, A_PATERNO,A_MATERNO,NOMBRE
FROM EMPLEADOS;
Para ver los resultados
SELECT * FROM VistaEmpleados;
id_empleado | a_paterno | a_materno | nombre
-------------+-----------+-----------+-------1 | Cabrera | Lopez
| Mario
2 | Gonzalez | nario
| Jose
3 | Martinez | Perez
| Maria
4 | Ordaz
| Mana
| Mario
Join
•
•
•
La sentencia join en SQL permite combinar registros de dos o más tablas en una base de datos
relacional. En el Lenguaje de Consultas Estructurado (SQL) hay tres tipo de JOIN: interno,
externo y cruzado.
Combinación interna (INNER JOIN).- Con esta operación se calcula el producto cruzado de todos
los registros; así cada registro en la tabla A es combinado con cada registro de la tabla B; pero
sólo permanecen aquellos registros en la tabla combinada que satisfacen las condiciones que se
especifiquen. Este es el tipo de JOIN más utilizado, por lo que es considerado el tipo de
combinación predeterminado.
Combinación externa (OUTER JOIN).- Mediante esta operación no se requiere que cada registro en
las tablas a tratar tenga un registro equivalente en la otra tabla. El registro es mantenido en la
tabla combinada si no existe otro registro que le corresponda.
•
Cruzada (Cross join).- Presenta el producto cartesiano de todos los registros de las dos tablas.
•
El código SQL para realizar este producto cartesiano enuncia las tablas que serán combinadas,
pero no incluye algún predicado que filtre el resultado.
Ejemplo de combinación cruzada explícita:
SELECT *
FROM empleado CROSS JOIN departamento
Ejemplo de combinación cruzada implícita:
SELECT *
FROM empleado, departamento;
•
Matemáticamente, JOIN es composición relacional, la operación fundamental en el álgebra
relacional, y, generalizando, es una función de composición.
Stored Procedures
• Los Stored Procedures son un método de encapsulación
repetitiva de tareas que se ejecutan eficientemente.
Los Stored Procedures soportan variables definidas por
el usuario, condiciones de ejecución otras poderosas
herramientas de programación. Pueden:
1.Contener sentencias que realicen operaciones en la base
de datos, incluyendo la habilidad de llamar otros Stored
Procedures.
2.Acepta parámetros de entrada.
3.Regresa un valor del status de la transacción indica sí
esta falla y el motivo del fallo.
4.Regresa múltiples valores en forma de parámetros de
salida.
Stored Procedures
Creación:
CREATE PROC[EDURE] Nombre_del_procedimiento
[@] parameter tipo_de_dato ]
AS
Sentecias_sql
Ejemplo:
CREATE PROCEDURE CONSULTA_EMPLEADOS
AS
SELECT * FROM EMPLEADOS
o tambien
CREATE PROC CONSULTA_EMPLEADOS
AS
SELECT * FROM EMPLEADOS
Ejecución:
EXECUTE CONSULTA_EMPLEADOS
o
EXEC CONSULTA_EMPLEADOS
Stored Procedures
Este ejemplo crea y ejecuta un Stored Procedure dentro de una sentencia INSERT.
CREATE PROCEDURE SP_EMPLEADOS
AS
SELECT SUBSTRING(A_PATERNO,1,3)+
SUBSTRING(A_MATERNO,1,2)+NOMBRE,
SUBSTRING(COLONIA,1,1)
FROM EMPLEADOS
EJECUTANDO EL STORED PROCEDURE
INSERT CAT_CONCEPTOS EXEC SP_EMPLEADOS
Triggers
•
Son objetos relacionados con tablas y almacenados en la base datos que se ejecutan o se muestran
cuando sucede algún evento sobre sus tablas asociadas.
•
Los eventos pueden ser las sentencias INSERT, DELETE, UPDATE que modifican los datos de una
tabla. Los triggeres se pueden ejecutar antes (BEFORE) y/o después (AFTER) de que sean
modificados los datos.
•
Los triggers tienen dos palabras clave, OLD y NEW que se refieren a los valores que tienen las
columnas antes y después de la modificación. Los INSERT permiten NEW, los DELETE sólo OLD y
los UPDATE ambas.
•
Un ejemplo de trigger seria uno asociado a la sentencia DELETE en una tabla de clientes, para
impedir que se elimine uno que tenga un saldo distinto de cero. Otro trigger seria guardar los datos
que se modifican de un cliente en otra base de datos que serviria de auditoria.
CREATE TRIGGER trigger_auditoria_clientes AFTER UPDATE ON clientes
FOR EACH ROW
INSERT INTO auditoria_clientes(name, anterior_seccion, usuario, modificado )
VALUES (OLD.nombre, OLD.seccion, CURRENT_USER(), NOW() );
Triggers
-- SQL Server Syntax
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML
Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
Ejemplo de Triggers
CREATE TRIGGER dbo.clientes_Trigger ON dbo.Clientes
AFTER INSERT
AS
PRINT 'Se modificaron filas en Clientes';
-- Luego que este creado el Trigger insertamos una fila en la tabla Clientes:
INSERT INTO Clientes (Cod_id,Nombre, Apellido)
VALUES ('342','Pirata','Fuentes');
Manejo de Transacciones
•
Una transacción en un Sistema de Gestión de Bases de Datos (SGBD), es un conjunto
de órdenes que se ejecutan formando una unidad de trabajo, es decir, en forma
indivisible o atómica.
•
Un SGBD se dice transaccional, si es capaz de mantener la integridad de los datos,
haciendo que estas transacciones no puedan finalizar en un estado intermedio. Cuando
por alguna causa el sistema debe cancelar la transacción, empieza a deshacer las
órdenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de
integridad), como si la orden de la transacción nunca se hubiese realizado.
•
Para esto, el lenguaje de consulta de datos SQL (Structured Query Language), provee
los mecanismos para especificar que un conjunto de acciones deben constituir una
transacción.
BEGIN TRAN: Especifica que va a empezar una transacción.
COMMIT TRAN: Le indica al motor que puede considerar la transacción completada
con éxito.
ROLLBACK TRAN: Indica que se ha alcanzado un fallo y que debe restablecer la base
al punto de integridad.
•
•
•
Manejo de Transacciones
•
EJEMPLO:
USE empresa;
GO
BEGIN TRANSACTION;
GO
DELETE FROM Empleados
WHERE id_empleado = 2;
GO
COMMIT TRANSACTION;
GO
Recuperación
• Un sistema de recuperación consiste en restaurar
una Base de Datos a un estado que se sepa
correcto, tras cualquier fallo que la haya dejado
en un estado incorrecto
• Es devolver a la Base de datos a un estado
consistente
Programación de la Base de Datos
Es la generación de aplicaciones utilizando
lenguajes de programación y conectandolos con una
Base de Datos para lograr los resultados esperados
en la misma.