View
228
Download
2
Category
Preview:
DESCRIPTION
SQL Server 2005
Citation preview
Informe de BD & SIASistema : Gestión de Biblioteca
DocenteJosé Souza
AlumnosFaundez Salinas José Javier
Olivares Nain Freddy FranciscoVásquez Toledo Ramón Antonio
Código de AsignaturaBSRC300-1
Fecha de Entrega28/09/2010
Índice
0.- SQL SERVER 0.1 ¿Que es SQL Server?0.2 Características de SQL Server
1.- Metodología de Modelamiento de Datos.
1.1 Diseño y Diagrama de Base de Datos1.2 Poblamiento de Base de Datos
2.- Vistas
2.1.- Listar todos los libros por Materia2.2.- Listar todos los libros por Autor2.3.- Listar todos los libros prestados y no devueltos por usuario.2.4.- Calcular los días de atraso que tiene los libros no devueltos a la fecha.2.5.- Contar y calcular el dinero correspondiente a los libros no devueltos considerando que cada Libro tiene un precio
3.- Procedimientos Almacenados
3.1.- Insertar un Libro3.2.- Consultar todos los Libros por Materia (la materia se recibe como un parámetro)3.3.- Actualizar el libro como devuelto3.4.- Eliminar todos los Libros prestados no devueltos del año pasado (2009)3.5.- Consultar todos los Libros prestados por Materia3.6.- Listar todos los Libros prestados por Carrera. La carrera se ingresa como un parámetro.
2
0.- SQL SERVER
0.1 ¿Que es SQL Server?
Microsoft SQL Server es un sistema para la gestión de bases de datos producido por Microsoft basado en el modelo relacional. Sus lenguajes para consultas son T-SQL y ANSI SQL. Microsoft SQL Server constituye la alternativa de Microsoft a otros potentes sistemas gestores de bases de datos como son Oracle o MySQL.
0.2 Características de SQL Server
Soporte de transacciones. Escalabilidad, estabilidad y seguridad.
Soporta procedimientos almacenados.
Incluye también un potente entorno gráfico de administración, que permite el uso de comandos DDL y DML gráficamente.
Permite trabajar en modo cliente-servidor, donde la información y datos se alojan en el servidor y los terminales o clientes de la red sólo acceden a la información.
Además permite administrar información de otros servidores de datos.
También este sistema incluye una versión reducida, llamada MSDE con el mismo motor de base de datos pero orientado a proyectos más pequeños, que en sus versiones 2005 y 2008 pasa a ser el SQL Express Edition, que se distribuye en forma gratuita.
3
1. Metodología de modelamiento de datos
1.1 Diseño y Diagrama de Base de Datos
Representación del diseño en un diagrama de base de datos. Considerando las siguientes tablas y campos:
TABLAS y CAMPOS
Libros Prestamo_devolucion Usuario Autor materia Carrera
Cod_libro Cod_pedido Cod_usuario Cod_autor Cod_materia Cod_carrera
Cod_materia Cod_libro Cod_carrera Nombre_autor materia Nombre_carrera
Cod_autor Cod_usuario Nombre_usuario
Titulo Fecha_prestamo Direccion
Precio_libro Fecha_maxima_entrega telefono
Editorial Fecha_devolucion
Fecha_publicacion
4
1.2 Poblamiento de Base de Datos
Se procede al Poblamiento de la Base de Datos la cual debe contener la siguiente cantidad de datos:
8 Libros, 12 Usuarios, 25 Prestamos, 3 Autores, 3 Materias, 2 Carreras.
Poblamiento de la Tabla Libros
Poblamiento de la Tabla Usuarios
5
Poblamiento de la Tabla prestamo_devolucion
Poblamiento de la Tabla Autor
6
Poblamiento de la Tabla materia
Poblamiento de la Tabla carrera
7
2. Vistas
2.1- Listar todos los libros por Materia
Código
SELECT dbo.Libros.cod_libro, dbo.Libros.titulo, dbo.materia.materiaFROM dbo.Libros INNER JOIN dbo.materia ON dbo.Libros.cod_materia = dbo.materia.cod_materia
Vista
Vista generada de forma Visual
8
2.2.- Listar todos los libros por Autor
Código
SELECT dbo.Autor.nombre_autor, dbo.Libros.cod_libro, dbo.Libros.tituloFROM dbo.Autor INNER JOIN dbo.Libros ON dbo.Autor.cod_autor = dbo.Libros.cod_autor
Vista
Vista generada de forma Visual
9
2.3.- Listar todos los libros prestados y no devueltos por usuario.
Código
SELECT dbo.Usuarios.nombre_usuario, dbo.prestamo_devolucion.fecha_prestamo, dbo.prestamo_devolucion.fecha_devolucion, dbo.Libros.cod_libro, dbo.Libros.titulo, dbo.materia.materia
FROM dbo.Usuarios INNER JOIN dbo.prestamo_devolucion ON dbo.Usuarios.cod_usuario = dbo.prestamo_devolucion.cod_usuario INNER JOIN dbo.Libros ON dbo.prestamo_devolucion.cod_libro = dbo.Libros.cod_libro INNER JOIN dbo.materia ON dbo.Libros.cod_materia = dbo.materia.cod_materia
WHERE (dbo.prestamo_devolucion.fecha_devolucion IS NULL)
Vista
Vista generada de forma Visual
10
2.4.- Calcular los días de atraso que tiene los libros no devueltos a la fecha.
Código
SELECT dbo.prestamo_devolucion.fecha_prestamo, dbo.prestamo_devolucion.fecha_maxima_entrega, DATEDIFF(day, dbo.prestamo_devolucion.fecha_maxima_entrega, GETDATE()) AS dias_atraso, dbo.Libros.titulo, dbo.Usuarios.nombre_usuario
FROM dbo.prestamo_devolucion INNER JOIN dbo.Usuarios ON dbo.prestamo_devolucion.cod_usuario = dbo.Usuarios.cod_usuario INNER JOIN dbo.Libros ON dbo.prestamo_devolucion.cod_libro = dbo.Libros.cod_libro
WHERE (dbo.prestamo_devolucion.fecha_devolucion IS NULL)
Vista
Vista generada de forma Visual
11
2.5.- Contar y calcular el dinero correspondiente a los libros no devueltos considerando que cada Libro tiene un precio
Código
SELECT COUNT(dbo.Libros.cod_libro) AS libros_perdidos, SUM(dbo.Libros.precio_libro) AS valor_librosFROM dbo.Libros INNER JOIN dbo.prestamo_devolucion ON dbo.Libros.cod_libro = dbo.prestamo_devolucion.cod_libro INNER JOIN dbo.Usuarios ON dbo.prestamo_devolucion.cod_usuario = dbo.Usuarios.cod_usuario
WHERE (dbo.prestamo_devolucion.fecha_devolucion IS NULL)
Vista
Vista generada de forma Visual
12
3. Procedimientos Almacenados
3.1.- Insertar un Libro
Creación de Procedimiento
CREATE PROCEDURE insertar_libro @cod_libro varchar(5), @cod_materia varchar(5), @cod_autor varchar(5),
@titulo varchar(30), @precio_libro numeric(18,0), @editorial varchar(30), @fecha_publicacion datetime
asinsert into Libros(cod_libro,cod_materia,cod_autor,titulo,precio_libro,editorial,fecha_publicacion)values (@cod_libro,@cod_materia,@cod_autor,@titulo,@precio_libro,@editorial,@fecha_publicacion)
goexec insertar_libro 'po4','po','1','Martin Rivas',8000,'Ercilla','15-05-60'
Vistas
Vista antes del procedimiento
13
Vista Después del procedimiento
3.2.- Consultar todos los Libros por Materia (la materia se recibe como un parámetro)
Creación de Procedimiento
Create Procedure libros_por_materia @materia varchar(30)asselect materia.cod_materia, materia.materia, libros.cod_libro, libros.titulo, libros.editorial from libros inner join materia on
libros.cod_materia = materia.cod_materiawhere materia.materia = @materiago exec libros_por_materia 'poesia'
Vistas
Vista del procedimiento
14
3.3.- Actualizar el libro como devuelto
Creación de Procedimiento
Create procedure actualizar_devolucion @cod_pedido varchar(30), @fecha_devolucion datetimeasupdate prestamo_devolucion set fecha_devolucion = @fecha_devolucionwhere cod_pedido=@cod_pedido and fecha_devolucion is NULL
goexec actualizar_devolucion '003','07-03-2009'
Vistas
15
Vista antes del procedimiento
16
Vista Después del procedimiento
3.4.- Eliminar todos los Libros prestados no devueltos del año pasado (2009)
17
Creación de Procedimiento
Create procedure eliminar_prestamos_2009asdelete from prestamo_devolucion where fecha_maxima_entrega <= '31/12/2009 23:59:59' andfecha_maxima_entrega >= '01/01/2009 00:00:01' and fecha_devolucion is NULL
goexec eliminar_prestamos_2009
Vistas
Vista del procedimiento
3.5.- Consultar todos los Libros prestados por Materia
18
Creación de Procedimiento
create procedure libros_prestados_materiaasselect materia.cod_materia,materia.materia,libros.cod_libro,libros.titulo,prestamo_devolucion.fecha_prestamofrom prestamo_devolucion inner join libros on prestamo_devolucion.cod_libro = libros.cod_libro inner joinmateria on libros.cod_materia = materia.cod_materia order by materia.materia ASC
goexec libros_prestados_materia
Vistas
Vista del procedimiento
3.6.- Listar todos los Libros prestados por Carrera. La carrera se ingresa como un parámetro.
19
Creación de Procedimiento
create procedure consulta_prestamos_carrera @carrera varchar(50)asselect carrera.nombre_carrera, prestamo_devolucion.cod_libro,libros.titulo,prestamo_devolucion.cod_usuario,usuarios.nombre_usuariofrom prestamo_devolucion inner join usuarios on prestamo_devolucion.cod_usuario = usuarios.cod_usuarioinner join libros on prestamo_devolucion.cod_libro = libros.cod_libro inner join carrera on usuarios.cod_carrera = carrera.cod_carrera where carrera.nombre_carrera = @carrera
go exec consulta_prestamos_carrera 'Arte'
Vistas
Vista del procedimiento
20
Recommended