TIA: Administración base de datos “Biblioteca
videos
create database DBBiblioteca_Duvian
use DBBiblioteca_Duvian
/*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 tblTipo_Usuario
/*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)
/*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,'disponible')
Insert into tblEjemplar values(2,'disponible')
Insert into tblEjemplar values(3,'disponible')
Insert into tblEjemplar values(3,'Prestado')
Insert into tblEjemplar values(1,'prestado')
Insert into tblEjemplar values(2,'Prestado')
Insert into tblEjemplar values(1,'prestado')
/*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 tblusuario
select * from tblreserva
select * from tblprestamo
select * from tblejemplar
select * from tbldevolucion
/*1. Crear un trigger que cambie el estado del ejemplar cuando se inserta un préstamo.*/
drop trigger TR_InsertPrestamo
create trigger TR_InsertPrestamo
on tblPrestamo for insert
as
declare @cod_Material int
select @cod_Material=cod_Material from inserted
update tblEjemplar set estado='Prestado'
where tblEjemplar.Cod_Material=@cod_Material
Insert into tblPrestamo values(getdate(),'05/10/2010',1,1,124)
select * from tblprestamo
select * from tblejemplar
/*2. Crear un trigger que cambie el estado del ejemplar cuando se borra un préstamo.*/
create trigger TR_BorraPrestamo
on tblPrestamo for delete
as
declare @Num_Ejemplar int
select @Num_Ejemplar=@Num_Ejemplar from deleted
update tblEjemplar set estado='Disponible'
where tblEjemplar.Num_Ejemplar=Num_Ejemplar
delete tblPrestamo where Cod_Prestamo=19
select * from tblprestamo
select * from tblejemplar
/*3. Crear un trigger que cambie la cantidad del material cuando se inserta un préstamo.*/
select * from tblmaterial
create trigger TR_UpdateCantidad
on tblPrestamo for insert
as
declare @Cod_Material int
select @Cod_Material=@Cod_Material from inserted
update tblMaterial set cantidad=cantidad-1
where tblMaterial.Cod_material=Cod_material
Insert into tblPrestamo values(getdate(),'05/10/2010',1,1,124)
select * from tblmaterial
/*4. Crear un trigger que cambie la cantidad del materialcuando se borra un préstamo.*/
select * from tblprestamo
create trigger TR_ActualizaCantidad
on tblPrestamo for delete
as
declare @Cod_Material int
select @Cod_Material=@Cod_Material from deleted
update tblMaterial set cantidad=cantidad+1
where tblMaterial.Cod_material=Cod_material
delete tblPrestamo where Cod_Prestamo=20
select * from tblmaterial
select * from tblprestamo
/*5. Crear un trigger que cambie el estado del ejemplar a disponible cuando se realice una devolución.*/
select * from tblDevolucion
select * from tblejemplar
select * from tblPrestamo
create trigger TR_CambioEstado
on tblDevolucion for insert
as
begin
update tblEjemplar set estado='Disponible'
from tblPrestamo as a inner join inserted on inserted.Num_Prestamo=a.Cod_Prestamo
inner join tblEjemplar as b on a.Cod_Material=b.Cod_Material
end
Insert into tblDevolucion values(getdate(),1)
--/*6. Crear un trigger que evite que se actualice el campo "valor" de la tabla "material”*/
drop trigger TR_EvitaActualizacion
create trigger TR_EvitaActualizacion
on tblMaterial for update
as
if update(valor)
begin
raiserror('El valor no puede cambiarse',10,1)
--rollback transaction
end
update tblMaterial set Valor=30000 where Cod_material=1
drop trigger TR_EvitaActualizacion
/*7. Crear un trigger que muestra el valor anterior y nuevo valor de los registros actualizados.*/
create trigger TR_after_update_reistros
on tblMaterial
FOR UPDATE
as
if(update(Cod_material) or update(Nombre_Material) or update(Año) or update(CodTipo_Material) or update(Cantidad)) and not (update(valor))
begin
select d.Cod_Material,(d.Nombre_Material+ '-'+ cast(d.Año as varchar(30))+ '-'+ cast(d.Cantidad as varchar(30))) as 'Registro anterior',
(I.Nombre_Material+ '-'+ cast(I.Año as varchar(30))+ '-'+ cast(d.Cantidad as varchar(30))) as 'Registro actualizado'
from deleted as d join inserted as i on d.cantidad=I.Cod_material
end
else begin
raiserror('el valor no puede cambiarse',10,1)
end;
update tblMaterial set cantidad = 3 where Cod_material=2
select * from tblMaterial
/*8. Crear un trigger que No permita actualizar el valor del material que tenga estado prestado.*/
create trigger TR_NOactualiza
on tblMaterial
for update
as
set nocount on
if update(valor) and 'prestado'=(select 'prestado'
from tblEjemplar as a inner join tblMaterial as b on a.Cod_Material=b.Cod_material and a.Cod_Material like '%no disponible%' and b.Cod_material=a.Num_Ejemplar)
begin
raiserror('el valor no puede modificarse',10,1)
end;
update tblMaterial set Valor= 50000 where Cod_material=1
select * from tblMaterial
select * from tblEjemplar
/*9. Crear un trigger que cambie el estado del usuario cuando se inserta un préstamo.*/
select * from tblusuario
select * from tblPrestamo
create trigger TR_EstedoUsuario
on tblPrestamo for insert
as
begin
update tblusuario set Estado_usuario='Vigente'
from tblPrestamo as a inner join inserted on inserted.Cod_Prestamo=a.Cod_Prestamo
inner join tblusuario as b on a.Cedula=b.Cedula
end
Insert into tblPrestamo values(getdate(),'06/10/2011',1,1,124)
/*10. Crear una vista que muestre los datos de los materiales con un valor mayor a 500000.*/
drop view VistaMateriales
select * from tblMaterial
create view VistaMateriales
as
select * from tblMaterial as valor
where valor.Valor > 50000
select * from VistaMateriales
/*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
create view VistaPrecioMateriales
as
select * from tblMaterial as a
where a.Valor > (select max(a.Valor)
from tblMaterial as a inner join tblTipo_Material as b
on a.CodTipo_Material=b.CodTipo_Material and a.CodTipo_Material=2 or a.CodTipo_Material=4)
select * from VistaPrecioMateriales
/*12. Crear un procedimiento que muestre los datos de los materiales con un valor mayor que un valor dado por el usuario.*/
select * from tblMaterial
create procedure PR_MuestraMateriales
@valor int
as
select * from tblMaterial as a
where a.Valor > Valor
exec PR_MuestraMateriales 100000
/*13. Crear un procedimiento que inserte un material.*/
create procedure PR_InsertarMaterial
@Nombre_material varchar(30),
@Valor int,
@año int,
@CodTipo_Material int,
@Cantiad int
as
insert into tblMaterial values(@Nombre_material,@Valor,@año,@CodTipo_Material,@Cantiad)
exec PR_InsertarMaterial 'python',30000,2011,3,2
select * from tblMaterial
/*14. Crear un procedimiento que actualice los datos del material.*/
select * from tblMaterial
create procedure PR_ActualizarMaterial
@Cod_Material int,
@Nombre_Material varchar(30),
@Año int,
@CodTipo_Material int,
@Cantidad int
as update tblMaterial set
Nombre_material=@Nombre_Material,
año=@Año,
CodTipo_Material=@CodTipo_Material,
cantidad=@Cantidad
where Cod_material=@Cod_Material
exec PR_ActualizarMaterial 4,'Programacion',2011,2,2
exec PR_ActualizarMaterial 8,'c#',2011,2,2
/*15. Crear un procedimiento que borre un préstamo.*/
select * from tblPrestamo
create procedure PR_BorrarPrestamo
@Cod_Prestamo int
as
delete tblPrestamo
where Cod_Prestamo=@Cod_Prestamo
exec PR_BorrarPrestamo 2
/*16. Crear una función que muestre los datos del material con un título entrado por el usuario.*/
create function MostrarMaterial
( @Titulo varchar(30) )
returns table
return
( select * from tblMaterial where Nombre_material like '%'+@Titulo+'%' )
select * from MostrarMaterial ('c#')
/*17. Crear una función que muestre los días de retraso después de la fecha de devolución.*/
create function Retraso
(@fecha int)
returns table
return
(select Cod_Prestamo,Fecha_Entrega,Fecha_Devolucion,DATEDIFF(day,x.Fecha_Entrega,x.Fecha_Devolucion)Retraso
from tblPrestamo as x where(x.Fecha_Devolucion-x.Fecha_Entrega)>0);
select * from Retraso(0)
/*18. Crear una función escalar que muestre el máximo valor de los materiales.*/
create function ValorMaximo
(@valor int)
returns int
as begin
declare @mayor int
select @mayor=MAX(valor) from tblMaterial
return @mayor
end
select dbo.ValorMaximo(0)
select * from tblMaterial
Comentarios
Publicar un comentario