TIA FORMATIVA
EJECUCIÓN EJEMPLOS
BD BRIGADAS
Este documento contiene ejemplos de trigger, vistas, funciones, procedimientos almacenados y seguridad, aplicados a una bases de datos llamada “Brigadas”
Analice estos ejemplos de programación de base de datos propuestos en este documento y ejecute cada ejercicio en su computador, a través del SGBD SQL y una vez realizado correctamente capture la pantalla. Tenga presente que los ejercicios los debe ir realizando progresivamente en la medida de lo que se vaya indicando en las orientaciones dadas en la AEAE 2: Administrando Bases de Datos.
Tenga en cuenta que la imagen de cada ejercicio, debe ser copiada en la diapositiva asignada para tal fin.
2
INSTRUCCIONES
/*crear un trigger para que cada vez que un empleado participe en una brigada se le sume a la bonificación el 15% del valor de la cantidad de medicamento utilizada en la brigada*/
create trigger tr_actualiza
on empleado
for update
as
update medicamento
set medicamento.cantidad=medicamento.cantidad*inserted.bonificacion+0.15
from participa inner join inserted on participa.cedula=inserted.cedula
3
Trigger - Ejercicio 1
/*crear un trigger para que cada vez que ingrese una brigada se actualice la cantidad de brigadas en proyecto*/
create trigger tr_noactualiza
on proyecto
for update
as
if update(cantidad_brigadas)
begin
print 'no se puede actualizar'
rollback transaction
end
update proyecto
set cantidad_brigadas=cantidad_brigadas +1
4
Trigger - Ejercicio 2
/*Crear un trigger que permita controlar la cantidad del medicamento cuando estos se borran de una brigada*/
create trigger tr_borrarBrid_med_controlCantidadMED
on bri_med
for delete
as update medicamento
set medicamento.cantidad=medicamento.cantidad+deleted.canti_utilizada
from medicamento inner join deleted
on medicamento.cod_med=deleted.cod_med
5
Trigger - Ejercicio 3
/*Crear una vista actualizable que muestre los datos de los medicamentos con un valor mayor que 50000*/
create view v_medicamento
as
select medicamento.*
from medicamento
where valor>50000
6
Vistas - Ejercicio 1
/Crear una vista no actualizable que muestre los datos de los empleados y un aumento del 10%sobre su bonificación y su salario si este está entre 600000 y 2000000 y han participado en más de 2 brigadas*/
create view v_empleadoCondicion
as
select empleado.*, salario+(salario*0.1) as salarioAunmento,bonificacion+(bonificacion*0.1) as bonificacionAumento
from empleado inner join participa
on empleado.cedula=participa.cedula
where salario between 600000 and 2000000
group by participa.cedula,empleado.cedula,empleado.bonificacion,empleado.nom_emp,empleado.salario,empleado.telefono
having COUNT(*)>2
7
Vistas - Ejercicio 2
/Crear un procedimiento que le permita mostrar los datos de los medicamentos que tengan un valor mayor que el promedio de todos los valores de los medicamentos con una cantidad entre dos valores dados por el usuario*/
Create procedure Pr_Promedio
@val1 int,
@val2 int,
@total int
as
select medicamento.*, AVG(medicamento.valor) from medicamento
where medicamento.cantidad between @val1 and @val2
group by medicamento.cantidad,medicamento.cod_med,medicamento.nom_med,medicamento.forma_uso,medicamento.valor
having AVG(medicamento.valor)>@total
exec Pr_Promedio 15,21,20000
8
Procedimientos almacenados Ejercicio 1
/*Crear un procedimiento que le permita mostrar los datos de los medicamentos con un valor dado por el usuario y un total utilizado mayor que un valor dado por el usuario*/
create procedure Pr_Mostrar_Med
@Valor int,@Valorusu int
As
select medicamento.*, sum(cant_utilizada)
from medicamento inner join bri_med
on medicamento.cod_med = bri_med.cod_med
where medicamento.cod_med=@Valorusu
group by bri_med.cod_med,bri_med.canti_utilizada,medicamento.cod_med, medicamento.nom_med, medicamento.forma_uso,
medicamento.cantidad, medicamento.valor having sum(*)>@Valor
exec Pr_Mostrar_Med 001,500
9
Procedimientos almacenados Ejercicio 2
/*Crear una función lineal que muestre los datos de las brigadas realizadas entre las fechas dadas por el usuario con un total mayor que un valor dado por el usuario*/
create function F_Brigadas
(@Fechai datetime,
@Fechaf datetime, @Valor int)
returns table
as
return(select *
from Participa inner join brigada
on participa.cod_bri= brigada.cod_bri
inner join Bri_med
on brigada.cod_bri inner join Bri_med.cod_bri
where Participa.fecha between @Fechai and @Fechaf
group by Bri_med.cod_bri, brigada.cod_bri , brigada.nom_bri, brigada.cod_proy, brigada.fecha_b having sum(canti_utilizada*)>@Valor)
select * from F_Brigadas('12/07/2010','01/10/2012',501)
10
Funciones
Crear un usuario de Windows llamado JOSE
1. Abrir el MS Management Studio
2. Buscamos en el Explorador de Objetos, la Carpeta Security, Logins.
3. Aquí vamos a definir un nuevo Login, el cual tendrá acceso al Servidor pero no a una base específica.
4. Nuevo Login. Search. Escribimos el nombre de nuestro usuario “JOSE”. Comprobamos. Finalmente Aceptar
5. Buscamos nuestra Base de Datos “Demo”. Folder Security. Folder Users. Nuevo User.en la base de datos
6. Aquí vamos a definir el nombre del User, que puede ser o no el mismo del Logín. Escribimos JOSE y buscamos el Login equivalente. Click en OK
11
Seguridad
7. Iniciemos sesión de Windows con el Login JOSE.
8. Verifiquemos que no tenemos acceso a otras bases más que a “Demo”, adicional no podemos visualizar ninguna Tabla, solo vistas, pero al ejecutarla nos regresaran 0 Registros.
9.Regresemos a la sesión de Administrador y asignemos roles al User JOSE. Vamos a la Base de Datos “Demo”. Users y buscamos a “JOSE”. Asignaremos db_datareader y db_datawriter.
10.Nuevamente regresamos a la sesión de Windows “JOSE”. Y verificamos los cambios. Podemos ver las Tablas. Y podemos hacer consulta de búsqueda e inserción de datos.
11.En la sesión Administrador. Buscamos el user “JOSE” y vamos a la sección Securables.
12
Seguridad (continuación)
12. Los Securables o “Asegurables” nos van a dar la opción de asignar permisos al usuario a determinados objetos y determinadas acciones.
13. En securables seleccionamos Search, “All Objects of the types”, “Tables”. Por ejemplo: Para tabla Productos definimos que no tendrá permisos para borrar.
14. Regresamos a la sesión “JOSE” y verificamos esta acción.
13
Seguridad (continuación)
Coloque aquí la captura de la pantalla de la ejecución del ejercicio 1 sobre trigger
(Duplique la diapositiva cuantas veces requiera para colocar las imágenes de la captura de las pantallas)
14
IMAGEN DE LA PANTALLA DEL EJERCICIO SOBRE TRIGGER - Ejercicio 1
Coloque aquí la captura de la pantalla de la ejecución del ejercicio 2 sobre trigger
15
IMAGEN DE LA PANTALLA DEL EJERCICIO SOBRE TRIGGER - Ejercicio 2
Coloque aquí la captura de la pantalla de la ejecución del ejercicio 3 sobre trigger
16
IMAGEN DE LA PANTALLA DEL EJERCICIO SOBRE TRIGGER - Ejercicio 3
Coloque aquí la captura de la pantalla de la ejecución del ejercicio 1 sobre vistas
17
IMAGEN DE LA PANTALLA DEL EJERCICIO SOBRE VISTAS - Ejercicio 1
Coloque aquí la captura de la pantalla de la ejecución del ejercicio 2 sobre vistas
18
IMAGEN DE LA PANTALLA DEL EJERCICIO SOBRE VISTAS - Ejercicio 2
Coloque aquí la captura de la pantalla de la ejecución del ejercicio 1 sobre procedimientos almacenados
19
IMAGEN DE LA PANTALLA SOBRE PROCEDIMIENTOS ALMACENADOS - Ejercicio 1
Coloque aquí la captura de la pantalla de la ejecución del ejercicio 2 sobre procedimientos almacenados
20
IMAGEN DE LA PANTALLA SOBRE PROCEDIMIENTOS ALMACENADOS - Ejercicio 2
Coloque aquí la captura de la pantalla de la ejecución del ejercicio sobre funciones
21
IMAGEN DE LA PANTALLA DEL EJERCICIO SOBRE FUNCIONES
Coloque aquí la captura de la pantalla de la ejecución del ejercicio sobre seguridad.
22
IMAGEN DE LAS PANTALLAS DEL EJERCICIO SOBRE SEGURIDAD