clic aquí : VIDEO DE SUSTENTACION
Realizar este portafolio es una gran experiencia ya que podré tener todo lo estudiado en un lugar donde podré acceder en cualquier momento que necesite recordar mis conocimientos, y no solo para mi estoy seguro de que aquellas personas que deseen visitar mi blog van aprender mucho ya que al ser un estudiante me puedo hacer entender fácilmente de otros estudiantes
/*Creacion Base de Datos*/
create database DB_Biblioteca2016
use DB_Biblioteca2016
/*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')
Select * from tbldependencia
/*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)
Select * from tblPertenece
/*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)
Select * from tblTipo_Material
/*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)
Select * from tblMaterial
/*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')
Select * from tblEjemplar
/*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)
Select * from tblPrestamo
/*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)
Select * from tblReserva
/*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_Material
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
/* 1. Insertar una reserva para el usuario Carlos con cédula 8888 de la dependencia judicial para el
material libro el Buen vendedor con un valor de 30000, año 2013 y cantidad 1. Si el usuario y el
material no están en la base de datos también los debe insertar y asumir los datos faltantes */
/* seleccionamos la tabla Material y eliminamos el constrain que nos impide hacer el ingreso de el año 2013*/
alter table tblmaterial
drop constraint CK__tblMaterial__año__440B1D61
GO
/* agregamos el nuevo constrain */
alter table tblmaterial with nocheck
add constraint CK__tblMaterial__año__440B1D61 check(año between 1000 and 2013)
GO
/* insertamos los datos requeridos para la reserva */
insert into tblusuario values (8888,'Carlos','8320','830',2,'Vigente')
insert into tblpertenece values (8888,1)
insert into tblmaterial values ('El Buen vendedor',30000,2013,1,1)
/* insertamos la reserva con el usuario de cedula 8888 */
insert into tblReserva values (GETDATE(),8888,1)
select * from tblreserva
-- 2. Insertar en una tabla llamada tblDatos losregistros de los usuarios con préstamos vigentes
select a.Cedula, Nombre, telefono, Direccion, Cod_Tipo, Estado_usuario, Cod_Prestamo, Fecha_Entrega,
Fecha_Devolucion, Cod_Material, Num_Ejemplar
into tblDatos from tblusuario as a inner join tblPrestamo as b on b.Cedula=a.Cedula
where Estado_Usuario = 'Vigente'
select * from tblDatos
-- 3. Mostrar los datos de los materiales que no han devuelto los usuarios de sistemas o de administración
Select d.Num_Ejemplar, e.Cod_material, Nombre_material, Valor, año,NombreTipo_Material,Nombre_Dependencia
from tblDevolucion as a inner join tblPrestamo as b on b.Cod_Prestamo = a.Num_Prestamo
inner join tblusuario as c on b.Cedula = c.Cedula
inner join tblEjemplar as d on d.Cod_Material = b.Cod_Material
inner join tblMaterial as e on e.Cod_material = d.Cod_Material
inner join tblTipo_Material as f on f.CodTipo_Material = e.CodTipo_Material
inner join tblPertenece as g on g.Cedula = c.Cedula
inner join tbldependencia as h on h.Cod_Dependencia = g.Cod_Dependencia
where Nombre_Dependencia in ('Sistemas','Administración')
/* 4. Mostrar los nombres de los materiales y su cantidad de préstamos solo si esta cantidad es mayor que
el promedio de todas las cantidades de los materiales */
with sentencia as
(
select Cod_Material, count(*) as Cantidad_Prestamo from tblPrestamo
group by Cod_Material
)
select a.Cod_material, b.Nombre_material, Cantidad_Prestamo, Cantidad as Cantidad_Disponible
from sentencia as a full outer join tblMaterial as b on b.Cod_material = a.Cod_Material
where Cantidad_Prestamo is not null
-- 5. Mostrar los datos de los usuarios con estado vetado que pertenecen a todas las dependencias
select a.Cedula, a.Nombre, a.telefono, a.Direccion, a.Estado_usuario, d.Nom_Tipo, Nombre_Dependencia
from tblusuario as a full outer join tblPertenece as b on b.Cedula = a.Cedula
full outer join tbldependencia as c on c.Cod_Dependencia = b.Cod_Dependencia
full outer join tblTipo_Usuario as d on d.Cod_tipo = a.Cod_Tipo
where Estado_usuario = 'Betado'
/* 6. Actualizar el estado de los ejemplares de los materiales tipo película o juegos para estado reservado */
select * from tblmaterial
select * from tblejemplar
select * from tblTipo_Material
update tblEjemplar
set tblejemplar.estado = 'Reservado'
from tblEjemplar as a inner join tblMaterial as b on b.Cod_material = a.Cod_Material
inner join tblTipo_Material as c on c.CodTipo_Material = b.Cod_material
where c.NombreTipo_Material in ('película','juegos')
/* 7. Actualizar el valor de los materiales en una disminución del 5% con año menor que 2000 y se han
prestado más de 5 veces */
with sentencia as
(
select Cod_Material, count(*) as Cantidad_Prestamo from tblPrestamo
group by Cod_Material
)
update tblMaterial
set valor = valor - (Valor * 0.05)
from sentencia as a full outer join tblMaterial as b on b.Cod_material = a.Cod_Material
where Cantidad_Prestamo > 5 and año < 2000
/* 8. Actualizar el estado de los usuarios Carlos Camilo y Camila a vigente si pertenecen a las
dependencias Judicial */
select * from tblusuario
update tblusuario
set Estado_usuario = 'Vigente'
from tblusuario as a inner join tblPertenece as b on b.Cedula = a.Cedula
inner join tbldependencia as c on c.Cod_Dependencia = b.Cod_Dependencia
where a.Nombre in ('Carlos', 'Camila', 'Camilo') and c.Nombre_Dependencia = 'Judicial'
-- 9. Borrar las reservas de los usuarios Carlos Camilo y Camila
select * from tblreserva
select * from tblusuario
delete tblReserva
from tblReserva as a inner join tblusuario as b on b.Cedula = a.Cedula
where Nombre in ('Carlos', 'Camila', 'Camilo')
-- 10. Borrar los préstamos de los ejemplares de los materiales libros
select * from tblprestamo
select * from tblTipo_Material
delete tblPrestamo
from tblPrestamo as a inner join tblMaterial as b on b.Cod_material = a.Cod_Material
inner join tblTipo_Material as c on c.CodTipo_Material = b.CodTipo_Material
where NombreTipo_Material = 'Libro'
Comentarios
Publicar un comentario