TIA MANIPULACION DE BASES BIBLIOTECA PARTE 2

 TIA MANIPULACIÓN DE BASES BIBLIOTECA PARTE 2

ACÁ PODRÁS VER TODO EL TRABAJO 

https://docs.google.com/presentation/d/1GuLVKM6_tdRi1SduTNakeZ7MVibwG16t/edit?usp=sharing&ouid=106032888771811307855&rtpof=true&sd=true 

ACÁ PODRÁS VER LA DOCUMENTACIÓN 

https://drive.google.com/file/d/1YbedBd5eq-0qQEYoX7FX_r67h7lU9DlM/view?usp=sharing 

ACÁ PODRÁS VER EL VÍDEO DE SUSTENTACIÓN

https://drive.google.com/file/d/1uB0zil14EnZE4uRTH7GFMpEVcRfNf5GL/view?usp=sharing 

CÓDIGO 

/*Creacion Base de Datos*/

create database DBBiblioteca2017

use DBBiblioteca2017

/*Crear tabla Tipo_usuario*/

create table tblTipo_Usuario

(

Cod_tipo int identity (1,1),

Nom_Tipo varchar(30) not null,

Primary Key (Cod_tipo)

)

/*Datos Para Tipo de usuario*/

Insert into tblTipo_Usuario values('Estudiante')

Insert into tblTipo_Usuario values('Profesor')

Insert into tblTipo_Usuario values('Directivo')

Insert into tblTipo_Usuario values('Empleado')

Select * from tblTipo_Usuario

/*Crear tabla usuario*/

create table tblusuario

(

Cedula int,

Nombre varchar(30)not null,

telefono int not null,

Direccion varchar(30) not null,

Cod_Tipo int not null,

Estado_usuario varchar(30) not null,

Primary Key (Cedula),

Foreign key (Cod_Tipo)references

tblTipo_Usuario (Cod_Tipo) on delete cascade on update cascade

)

/*Datos para tabla usuario*/

Insert into tblusuario values(123,'Daniela','9541','751',1,'Vigente')

Insert into tblusuario values(124,'Camilo','9542','752',2,'Betado')

Insert into tblusuario values(125,'Ramon','9543','753',3,'Vigente')

Insert into tblusuario values(126,'Daniel','9541','751',1,'Vigente')

Insert into tblusuario values(127,'Camila','9542','752',2,'Betado')

Insert into tblusuario values(128,'Clara','9543','753',3,'Vigente')

Insert into tblusuario values(129,'Daniel','9541','751',1,'Vigente')

Insert into tblusuario values(130,'Camila','9542','752',2,'Betado')

Insert into tblusuario values(131,'Ramiro','9543','753',3,'Vigente')

Select * from tblusuario

/*Crear Tabla dependencia*/

Create table tbldependencia

(

Cod_Dependencia int identity (1,1),

Nombre_Dependencia varchar(30)not null,

Ubicacion varchar(30)not null,

Primary key (Cod_Dependencia)

)

/*Datos para Dependencia*/

Insert into tbldependencia values('Judicial','B1_103')

Insert into tbldependencia values('Sistemas','B5_203')

Insert into tbldependencia values('Administración','B3_106')

Insert into tbldependencia values('Educación','B3_107')

Insert into tbldependencia values('Agroambiental','B5_101')

Insert into tbldependencia values('electronica','B1_101')

/*Crear tabla Pertenece*/

create table tblPertenece

(

Cedula int,

Cod_Dependencia int not null,

Primary key (Cedula,Cod_Dependencia),

Foreign Key (Cedula) references

tblusuario (Cedula) on delete cascade on update cascade,

Foreign Key (Cod_Dependencia) references

tblDependencia (Cod_Dependencia)on delete cascade on update cascade

)

/*Datos para Pertenece*/

Insert into tblPertenece values(123,1)

Insert into tblPertenece values(124,2)

Insert into tblPertenece values(125,3)

Insert into tblPertenece values(126,1)

Insert into tblPertenece values(127,2)

Insert into tblPertenece values(128,3)

/*crear tabla Tipo Material*/

create table tblTipo_Material

(

CodTipo_Material int identity (1,1),

NombreTipo_Material varchar(30)not null,

CantidadTipo_Material int not null,

Primary key(CodTipo_Material),

)

Insert into tblTipo_Material values('Libro',5000)

Insert into tblTipo_Material values('Revistas',4000)

Insert into tblTipo_Material values('Periodico',3000)

Insert into tblTipo_Material values('Audiovisual',3000)

/*Crear tabla Material*/

create table tblMaterial

(

Cod_material int identity (1,1),

Nombre_material varchar (30) not null,

Valor int not null,

año int not null,

CodTipo_Material int not null,

check (Valor between 1000 and 200000),

check (año between 1930 and 2012),

cantidad int check (cantidad between 1 and 20),

Primary Key (Cod_material),

foreign key (CodTipo_Material) references

tblTipo_Material (CodTipo_Material) on delete cascade on update cascade

)

/*Crear Index Unico para Nombre del material */

create unique index Nombre_material on tblMaterial (Nombre_material)

drop index Nombre_material on tblMaterial;

/*Datos para Material*/

Insert into tblMaterial values('Fundamentos de Bases de

datos',50000,1989,1,4)

Insert into tblMaterial values('Modelos de bases de datos',50000,1990,1,2)

Insert into tblMaterial values('Nueva tecnología',70000,1990,2,2) Insert

into tblMaterial values('colombiano',90000,2012,3,5) Insert into

tblMaterial values('Sql 2008',50000,1999,1,2) Insert into tblMaterial

values('Al día',70000,1990,2,2)

Insert into tblMaterial values('Matemáticas operativas',90000,1991,1,2)

/*Crear tabla Ejemplar*/

create table tblEjemplar

(

Num_Ejemplar int identity (1,1),

Cod_Material int,

estado varchar (30) not null,

check (estado ='Prestado'OR estado ='Disponible'OR estado ='En

reparacion'OR estado ='Reservado'),

Primary key(Num_Ejemplar,Cod_Material),

Foreign key (Cod_Material) references

tblMaterial (Cod_Material)on delete cascade on update cascade

)

/*Datos para Ejemplar*/

Insert into tblEjemplar values(1,'disponible')

Insert into tblEjemplar values(2,'disponible')

Insert into tblEjemplar values(3,'disponible')

Insert into tblEjemplar values(1,'En reparacion')

Insert into tblEjemplar values(2,'En reparacion')

Insert into tblEjemplar values(3,'Reservado')

/*Crear Tabla Prestamo*/

create table tblPrestamo

(

Cod_Prestamo int identity (1,1),

Fecha_Entrega datetime not null default getdate(),

Fecha_Devolucion datetime not null,

Cod_Material int not null,

Num_Ejemplar int not null,

Cedula int not null,

Primary Key(Cod_Prestamo),

Foreign Key (Num_Ejemplar,Cod_Material) references tblEjemplar

(Num_Ejemplar,Cod_Material) on delete cascade on update cascade,

Foreign Key (Cedula) references

tblUsuario (Cedula) on delete cascade on update cascade

)

/*Datos para Prestamo*/

Insert into tblPrestamo values(getdate(),'03/10/2011',1,1,123)

Insert into tblPrestamo values(getdate(),'05/10/2011',2,2,124)

Insert into tblPrestamo values(getdate(),'06/10/2010',3,3,125)

Insert into tblPrestamo values(getdate(),'03/10/2011',1,1,123)

Insert into tblPrestamo values(getdate(),'05/10/2010',2,2,124)

Insert into tblPrestamo values(getdate(),'06/10/2011',3,3,125)

Insert into tblPrestamo values(getdate(),'03/10/2011',1,1,123)

Insert into tblPrestamo values(getdate(),'05/10/2011',2,2,124)

Insert into tblPrestamo values(getdate(),'06/10/2010',3,3,125)

Insert into tblPrestamo values(getdate(),'03/10/2011',1,1,123)

Insert into tblPrestamo values(getdate(),'05/10/2010',2,2,124)

Insert into tblPrestamo values(getdate(),'06/10/2011',3,3,125)

Insert into tblPrestamo values(getdate(),'03/10/2011',1,1,123)

Insert into tblPrestamo values(getdate(),'05/10/2011',2,2,124)

Insert into tblPrestamo values(getdate(),'06/10/2010',3,3,125)

Insert into tblPrestamo values(getdate(),'03/10/2011',1,1,123)

Insert into tblPrestamo values(getdate(),'05/10/2010',2,2,124)

Insert into tblPrestamo values(getdate(),'06/10/2011',3,3,125)

/*Crear tabla Reserva*/

create table tblReserva

(

Cod_reserva int identity (1,1),

Fecha datetime default getdate() not null,

Cedula int not null,

Cod_Material int not null,

Primary key (Cod_reserva),

Foreign Key (Cedula) references

tblUsuario (Cedula) on delete cascade on update cascade,

Foreign Key (Cod_Material) references

tblMaterial (Cod_Material) on delete cascade on update cascade

)

/*Datos para Reserva*/

Insert into tblReserva values(getdate(),123,1)

Insert into tblReserva values(getdate(),124,2)

Insert into tblReserva values(getdate(),125,3)

Insert into tblReserva values(getdate(),123,1)

Insert into tblReserva values(getdate(),124,2)

Insert into tblReserva values(getdate(),125,3)

Insert into tblReserva values(getdate(),123,1)

Insert into tblReserva values(getdate(),124,2)

Insert into tblReserva values(getdate(),125,3)

/*Crear Tabla Devolucion*/

create table tblDevolucion

(

Cod_Devolucion int identity (1,1),

Fecha_Devolucion datetime default getdate() not null,

Num_Prestamo int not null,

Primary key (Cod_Devolucion),

Foreign Key (Num_Prestamo) references

tblPrestamo (Cod_Prestamo) on delete cascade on update cascade

)

/*Datos para Devolucion*/

Insert into tblDevolucion values(getdate(),1)

Insert into tblDevolucion values(getdate(),2)

Insert into tblDevolucion values(getdate(),3)

select * from tblTipo_Usuario

select * from tblusuario

select * from tbldependencia

select * from tblpertenece

select * from tblmaterial

select * from tblejemplar

select * from tblusuario

select * from tblreserva

select * from tblprestamo

select * from tbldevolucion

-- Administración base de datos “Biblioteca” 2

--1.Crear un trigger que cambie el estado del ejemplar cuando se inserta un

préstamo.

--trigger con el nombre TR_Cambiarestado

create trigger TR_Cambiarestado

on tblPrestamo for insert

as

update tblEjemplar

set estado ='Prestado'

where estado ='Disponible'

--insertar datos en la tabla prestamo para que cambie el estado del ejemplar

insert into tblPrestamo values (getdate(),'05/10/2010',2,2,124) select * from

tblEjemplar

select * from tblPrestamo

--2.Crear un trigger que cambie del ejemplar cuando se borra un préstamo.

--trigger con el nombre TR_CambiarEstado2

create trigger TR_CambiarEstado2

on tblPrestamo for delete

as

update tblEjemplar

set estado ='Reservado'

where estado ='En reparacion'

--borramos un dato de prestamo para que el estado del ejemplar cambie

delete from tblPrestamo where Cedula=123

select * from tblEjemplar

select * from tblPrestamo

--3. Crear un trigger que cambie la cantidad del material cuando se inserta un

préstamo.

select * from tblMaterial

--trigger con el nombre TR_CambiarCantidad

create trigger TR_CambiarCantidad

on tblPrestamo for insert

as

update tblMaterial

set cantidad=6

where cantidad=2

--insertamos un dato en la tabla prestamo para que cambie el estado del

material

insert into tblPrestamo values (getdate(),'05/10/2010',2,2,123)

--4. Crear un trigger que cambie la cantidad del material cuando se borra un

préstamo.

select * from tblMaterial

--trigger con el nombre TR_CambiarCantidad2

create trigger TR_CambiarCantidad2

on tblPrestamo for delete

as

update tblMaterial

set cantidad=6

where cantidad=2

--borramos un prestamo para que cambie la cantidad del material

delete from tblPrestamo where Cedula=124

--5. Crear un trigger que cambie el estado del ejemplar a disponible cuando se

realice una devolución.

--trigger con el nombre TR_CambiarEstado3

create trigger TR_CambiarEstado3

on tblDevolucion for insert

as

update tblEjemplar

set estado ='Disponible'

where estado ='Prestado'

select* from tblDevolucion

select * from tblEjemplar

--insertamos un dato para que cambie el estado del ejemplar a disponible

cuando se realiza una devolucion,

--osea que cuando se ingrese un dato en la tabla devolucion

Insert into tblDevolucion values(getdate(),3)

--6.Crear un trigger que evite que se actualice el campo "valor" de la tabla

"material”

--trigger con el nombre Tr_evitar

create trigger Tr_evitar

on tblMaterial for update

as begin

print 'No se puede actualizar'

rollback transaction

end

select * from tblMaterial

--hacemos una actulizacion para que verifique que no se puede realizar esta

misma en la tabla valor

update tblMaterial set Valor = valor-5 from tblMaterial

--7.Crear un trigger que muestra el valor anterior y nuevo valor de los

registros actualizados.

create trigger tr_actualizadas on tblMaterial

after update

as

select * from deleted

select * from inserted

update tblMaterial

set Valor = Valor + 500

--8. Crear un trigger que No permita actualizar el valor del material que tenga

estado prestado.

--trigger con el nombre TTr_evitar2

create trigger Tr_evitar2

on tblMaterial for update

as begin

print 'No se puede actualizar'

rollback transaction

end

select * from tblMaterial

select * from tblEjemplar

--realizamos una actualizacion de un valor para que imprima que esta no se

puede realizar

update tblMaterial set Valor = valor-5 from tblMaterial inner join tblEjemplar

on tblMaterial.Cod_material = tblEjemplar.Cod_material

where tblEjemplar.estado = 'Prestado'

--9. Crear un trigger que cambie el estado del usuario cuando se inserta un

préstamo.

--trigger con el nombre TR_CambiarEstado4

create trigger TR_CambiarEstado4

on tblPrestamo for insert

as

update tblusuario

set Estado_usuario='Vigente'

where Estado_usuario='Betado'

select * from tblusuario

select * from tblPrestamo

--insertar un prestamo para cambiar el estado del usuario

Insert into tblPrestamo values(getdate(),'06/10/2010',3,3,125)

--10.Crear una vista que muestre los datos de los materiales con un valor

mayor a 500.000.

--nueva vista con el nombre materialMay

create view materialMay

as

select Nombre_material, Valor from tblmaterial where Valor > 500000

select * from materialMay

--11.Crear una vista que muestre los datos de los materiales que tienen un

precio mayor que los materiales tipo audiovisual o revista. select * from

tblMaterial

select * from tblTipo_Material

--nueva vista con el nombre materialMay2

create view materialMay2

as

select tblMaterial.* from tblMaterial inner join tblTipo_Material on

tblMaterial.CodTipo_Material=tblTipo_Material.CodTipo_Material

where valor > 70000

select * from materialMay2

--12.Crear un procedimiento que muestre los datos de los materiales con un

valor mayor que un valor dado por el usuario.

create procedure mostrar_datos

(

@valor int

)

as

begin

select * from tblMaterial where Valor>@valor

end

execute mostrar_datos 70000

--13.Crear un procedimiento que inserte un material.

select * from tblMaterial

create procedure Insertar_material

(

@Nombre_material varchar (30),

@Valor int,

@año int,

@CodTipo_material int,

@cantidad int

)

as

begin

insert into

tblMaterial(Nombre_material,Valor,año,CodTipo_material,cantidad) select

@Nombre_material,@Valor,@año, @CodTipo_material, @cantidad end

execute Insertar_material santiago,10000,2002,2,6

select * from tblMaterial

--14.Crear un procedimiento que actualice los datos del material.

create procedure actualizar_material

(

@Nombre_material varchar (30),

@Valor int,

@año int,

@CodTipo_material int,

@cantidad int

)

as

begin

update tblMaterial set

Nombre_material=@Nombre_material,Valor=@Valor,año=@año,CodTipo_

Material=@CodTipo_material,cantidad=@cantidad

where Nombre_material='Sql 2008'

end

execute actualizar_material sofia,1500,2003,3,6

select * from tblMaterial

--15.Crear un procedimiento que borre un préstamo.

create procedure borrar_prestamo

(

@Cedula int

)

as

begin

delete from tblPrestamo where Cedula=@Cedula

end

execute borrar_prestamo 124

select * from tblPrestamo

--16.Crear una función que muestre los datos del material con un título

entrado por el usuario.

create function f_datosMaterial(

@Titulo varchar(50)

)

returns table

as

return

(

select * from tblMaterial where Nombre_material=@Titulo

)

select * from f_datosMaterial('colombiano')

--17.Crear una función que muestre los días de retraso después de la fecha de

devolución.

create function funciondias

(

@Fecha_Devolucion date

)

returns int

as

begin

declare @dias int

select @dias = DATEDIFF("y",@Fecha_Devolucion,getdate())

from tblPrestamo

return @dias

end

SELECT DateDiff("y",getdate(),[Fecha_Devolucion]) FROM tblPrestamo

select* from tblPrestamo

--18. Crear una función escalar que muestre el máximo valor de los materiales.

create function Valormaximo2()

returns int

as

begin

declare @maxi int

select @maxi=max(@Valor)

from tblMaterial

return @maxi

end

select* from tblMaterial

select max(Valor) from tblMaterial

select * from Valormaximo2()

--19.Crear un usuario para la base de datos donde solo pueda consultar las

tablas.

GRANT alter ON OBJECT::DBO.tblMaterial TO maria1

GO