informe procedimientos almacenados

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