• Author / Uploaded
• Kenny David Tupia Callañaupa

Citation preview

UNIVERSIDAD NACIONAL FEDERICO VILLARREAL ESCUELA DE INGENIERIA DE SISTEMAS TALLER DE BASE DE DATOS SISTEMA DE RESERVA PARA UN HOTEL

INTEGRANTES: - COCHACHI CHIUYARI, JOAN - PAITAN PAUCAR, JHON CESAR - PUJAZÓN LAMAS, HÉCTOR FERNANDO - RAMOS YURA, RUTH - TUPIA CALLAÑAUPA, KENNY

PROFESOR: Ing. Ivan Crispin Sanchez

LIMA – PERÚ 26 DE JULIO, 2018

1

Contenido I. PLANEACIÓN Y ADMINISTRACIÓN DEL PROYECTO ..................................................... 3 1.

Negocio ............................................................................................................................ 3 1.1.

Descripción de la organización ................................................................................. 3

1.2.

Organigrama.............................................................................................................. 4

2.

Alcance del Proyecto ...................................................................................................... 4

3.

Objetivos del Proyecto ................................................................................................... 4 3.1.

Objetivo general ........................................................................................................ 4

3.2.

Objetivos específicos................................................................................................. 4

4.

Justificación ..................................................................................................................... 5

5.

Beneficio .......................................................................................................................... 5

6.

Tareas a realizar .............................................................. Error! Bookmark not defined.

7.

Roles y equipo ................................................................. Error! Bookmark not defined.

8.

Cronograma de actividades ........................................... Error! Bookmark not defined.

9.

Recursos y costos........................................................... Error! Bookmark not defined.

II.

ANÁLISIS DE REQUERIMIENTOS ...................................................................................... 9 1.

Modelo de Negocios........................................................................................................ 9 1.1.

Descripción de los procesos del negocio específicos a modelar .............................. 9

1.2.

Problemas del negocio ............................................................................................ 10

1.3.

Matriz de proceso vs entidad de negocio ................................................................ 11

2.

Resumen de los requerimientos .................................................................................. 11 2.1.

Requerimientos funcionales .................................................................................... 11

2.2.

Requerimientos no funcionales ............................................................................... 12

2.3.

Requerimientos de implementación ........................................................................ 13

III.

DISEÑO LÓGICO: MODELO LÓGICO ............................ Error! Bookmark not defined.

1.

Normalización .................................................................................................................. 13

2.

Relaciones y Atributos ..................................................................................................... 13

3.

Dominios .......................................................................................................................... 13

4.

Restricciones ..................................................................... Error! Bookmark not defined. DISEÑO FISICO: MODELO FISICO………………………………………… …..…..20

IV. 1.

Base de Datos Físico………………………………………………………………16

2.

Sentencias SQL………………………………………………………………….….16 2.1. Procedimientos Almacenados (Store Procedure)………………….………..16 2.2. SP aplicados a la base de datos del sistema……………………………..…16 2.3. Triggers (Disparadores)

…………………………………………………….....21

2.4. Triggers aplicado a la base de datos del sistema…………………………...21 3.

Estrategia de indexación…………………………………………………….

2

……27

3.1.Índice Non-Clustered……………………………………………………………27 3.2.

I.

Índice Clustered…………………………………………………………..…27

PLANEACIÓN Y ADMINISTRACIÓN DEL PROYECTO 1.

Negocio 1.1.

Descripción de la organización

El hotel “EL PICAFLOR” es un lugar ideal para descansar y pasarla tranquilo con tu pareja, familia, amigos. Cuenta con habitaciones sencillas y con ventanas insonorizadas, muebles tradicionales, escritorio y televisión por cable. Hay Wifi con previa solicitud, las habitaciones superiores cuentan con un mayor espacio, y más amplio para su comodidad, el servicio está disponible a las 24 horas del día, brinda servicios extra como el estacionamiento para los clientes que cuenten con vehículos y no tengan un inconveniente en donde dejarlo, ven a disfrutar con nosotros este lugar tranquilo y encantador. “EL PICAFLOR” fue creado el 14 de febrero de 2013, actualmente el dueño es el Sr. Jorge Ramos quien anteriormente y hasta la actualidad tiene otro hotel que está ubicado en el mismo distrito, pero no en la misma zona, donde él también es el propietario junto con su cuñado, ellos siguen administrando hoy en día, donde cada año le toca a uno administrar y llevarse las ganancias según el año que les toca.

Debido a esto uno de los propietarios (Jorge Ramos) decidió abrir otro hotel por donde vive, analizo la necesidad que existía, ya que cerca de su casa no había un hotel y además era una zona muy concurrida, porque está en una avenida y es visible ver el hotel de lejos, aunque al principio no era muy visitada, solo era necesidad de esperar unos meses para que más clientes se alojen, se inició con un poco personal, ya que como recién se iniciaba el negocio, los mismos familiares decidieron trabajar hasta que los ingresos de clientes aumenten y así poder pagar y contratar a un nuevo personal.

Aunque no se tardó mucho en hacerse conocido, debido a la zona en que estaba ubicada y más porque cerca de ahí no había existencia de otro hotel. Actualmente “EL PICAFLOR” cuenta con 6 pisos, donde el último piso se usa para el proceso de secado y tendido, todo el primer piso ocupa la cochera, cada piso cuenta con 6 a 7 cuartos, en el segundo piso se encuentra la atención para los clientes, hace dos años se abrió otro hotel frente a este, siendo su único competidor, aunque las personas ya conocen y saben dónde la atención es mucho mejor, pero el problema no es tan grave, es por eso que hasta estos días siguen siendo el más concurrente y preferido.

3

1.2.

Organigrama

Gerente General Contador

Administrador

Recepcionista 1

2.

Recepcionista 1

Recepción

Recepcionista 1

Recepcionista 1

Alcance del Proyecto

La abundante información que se posee, ya que el dueño de esta empresa es familiar de un integrante de este proyecto, es beneficiosa para poder dar con las necesidades y requerimientos que le falta a este Hotel. Este proyecto podrá ser implementado en una empresa de reservación de habitaciones, manejando principalmente los procesos de un formulario de reservas, como registrar las fechas de entradas y salidas de las reservas de habitaciones, además podrá realizar pagos vía web, así mismo podrá ver el tipo de habitación disponible.

3.

Objetivos del Proyecto 3.1.

Objetivo general  Implementar un sistema de reserva del Hotel que permita automatizar y simplificar aquellas tareas que requieren más tiempo para realizar las búsquedas de alojamientos que consideren, así como realizar las reservas de plazas hoteleras, conociendo la disponibilidad y el precio de estas en el instante de la realización.

3.2.

Objetivos específicos  Identificar las relaciones existentes entre los actores del sistema (hotel).  Identificar relaciones entre las entidades del sistema y sus clientes que nos va a permitan modelar una base de datos.

4

 Ampliar y afianzar nuestros conocimientos en el modelamiento de base de datos respetando cada uno de los pasos y siguiendo las indicaciones propuestas.  Lograr un buen trabajo en equipo que sea capaz de realizar un trabajo eficaz y eficiente, para que se nos permita un buen análisis del sistema (hotel).  Realizar un informe detallado de cómo funciona este sistema, y aportar de manera positiva para que este sistema este actualizado.  Realizar un conjunto de actividades mediante reuniones presenciales para el trabajo en equipo.

4.

Justificación En la presente investigación se va a enfocar en realizar el análisis y estudio del Hotel “El

PicaFlor”, para así poder determinar e identificar los principales procesos que se llevan a cabo en dicho hotel, como es la reservación para poder hospedarse, la atención de los clientes, entre otros procesos, lo cual nos permitirá poder realizar entre otras cosas importantes, la base de datos del hotel; así como también poder poner en práctica los temas vistos y aprendidos, en las clases del curso recibido por parte de un docente altamente calificado y experto. Lo cual se irá demostrando en el desarrollo del contenido del presente informe.

5.

Beneficios Reducir gasto en personal: el principal objetivo de un sistema de reserva del Hotel

es automatizar y simplificar aquellas tareas que requieren más tiempo para su realización. De este modo, el personal de nuestro hotel podrá invertir su tiempo en otras funciones más prioritarias.

6.

Tareas por realizar En este punto en tareas a realizar se va a describir las actividades realizadas para el

desarrollo del proyecto. Para eso cada actividad a realizar se dará mediante reuniones del equipo (ya sea de manera presencial o virtual), en donde el líder distribuirá, asignará funciones y responsabilidades a cada integrante del equipo de trabajo, para que de esta manera poder lograr la interacción e integración, y apoyo de cada miembro del equipo. A continuación, se nombrará las actividades a realizar para el desarrollo de este proyecto, las cuales son: 

Primero se tendrá que definir la empresa, sistema u organización sobre la cual se llevara al desarrollo del trabajo. Aquí tras el aporte de cada uno de los miembros del equipo, que puede ser a través de una lluvia de ideas, se establecerá la empresa u organización.



Después se realizará la planeación del proyecto, según el cronograma de los entregables, lo cual abarca la realización de la descripción, objetivos, beneficios, etc. de

5

la empresa u organización escogida, con el objetivo de obtener un primer informe con la información obtenida. 

Luego se proseguirá a realizar el diseño lógico, para así realizar un segundo entregable.



Después se realizara el diseño físico (diseño de la BD física) y algunas indicaciones y correcciones dada por el docente, donde se obtendrá así un tercer entregable.



Una vez realizado todos los puntos anteriores, se va a proceder a realizar la aplicación para el usuario final, también lograr terminar el resto del entregable del proyecto como las referencias y anexos.

 Finalmente, se culminara el informe con algunas observaciones y correcciones hechas por parte del docente para la presentación del proyecto final.

7. Roles y equipo

LÍDER

Líder - Se encarga de dirigir al grupo responsable del proyecto. Inspira confianza y con respeto. Predica con ejemplo en cuanto a responsabilidad y cumplimiento.

COORDINADOR

Administra dor

Financiador

6

Documen talista

Programad or de aplicación

Coordinador - Esta en constante comunicación con el profesor, comunica si hay algun imprevisto. Comunica los avances entre las persona, al lider

-Administrador, Financiador,documentalis ta,programador de aplicación Encargados de la parte administrativa del trabajo además de la administracion del gasto monetario parte del equipo para llevar a cabo el proyecto y finalmente el documentalista quien se encargara de facilitar la información para el grupo. El programdor es encargado de todo lo relacionado a la interfaz y llevar acabo una adecuada conexión

8. Cronograma de actividades Nombre de tarea definir la empresa

Duración 1 día

Comienzo sáb 09/06/18

Fin sáb 09/06/18

planeación del proyecto

2 días

lun 11/06/18

mar 12/06/18

diseño lógico

4 días

mie 13/06/18

sáb 16/06/18

Realización del segundo entregable diseño físico

2 día

Lun 18/06/18

Mar 19/06/18

5 días

Mie 20/06/18

lun 25/06/18

Aplicación para el usuario

5 días

mar 26/06/18

sáb 30/06/18

Finalizar entregable

3 días

Lun 02/07/18

mie 04/07/18

Informe con correcciones y observaciones

3 días

Jue 05/07/18

sáb 07/07/18

Primera semana

Segunda semana

Nomb sáb lun mar mie jue vie sáb lun mar mie jue vie sáb re de 09/0 11/0 12/0 13/0 14/0 15/0 16/0 18/0 19/0 20/0 21/0 22/0 23/0 tarea 6/18 6/18 6/18 6/18 6/18 6/18 6/18 6/18 6/18 6/18 6/18 6/18 6/18 definir la empre sa planea ción del proye cto diseño lógico Realiz ación del segun do entreg able diseño físico

7

Semana 3

Semana 4

Nombre lun mar mie jue vie sáb lun mar mie jue vie sáb de tarea 25/0 26/0 27/0 28/0 29/0 30/0 02/0 03/0 04/0 05/0 06/0 16/0 6/18 6/18 6/18 6/18 6/18 6/18 7/18 7/18 7/18 7/18 7/18 6/18 Diseño físico Aplicaci ón para el usuario Finalizar entrega ble Informe con correcci ones y observa ciones 9.Recursos y costos DESCRIPCIÓN

UNIDAD

CANT.

COSTO UNITARIO

TOTAL

Pasajes para la búsqueda de la información (empresa)

veces

4

4.00 s/.

16.00 s/.

Hojas bond

ciento

1.0

0.10 s/.

7.00 s/.

páginas

100

0.20 s/.

20.00 s/.

Folder

unidades

3

2.50 s/.

7.50 s/.

Fastener

unidades

3

0.50 s/.

1.50 s/.

Kw/h

70

1.50 s/.

105.00 s/.

Monto

157.00 S/.

Impresión

Luz eléctrica

8

II.

ANÁLISIS DE REQUERIMIENTOS 1. Modelo de Negocios 1.1 Descripción de los procesos del negocio específicos a modelar Para la construcción del modelo de negocio del hotel el Picaflor se utilizará como referencia el modelo CANVAS desarrollado por los autores Osterwalder y Pigneur. Es importante recordar que un modelo de negocio es una herramienta conceptual que, a través de un conjunto de elementos y sus relaciones, permite expresar la lógica mediante la cual una compañía intenta ganar dinero generando y ofreciendo valor a uno o varios segmentos de clientes, la arquitectura de la firma, su red de aliados y el capital relacional para generar fuentes de ingresos rentables y sostenibles.



Segmentos de clientes -



Propuesta de valor -



Detalles de los servicios que se brinda

Estructura de ingresos -



Presencialmente Telefónica

Relaciones con el cliente -



Trato digno y amigable Instalaciones cómodas, limpias y agradables Buena y rápida atención. Cómodas tarifas según la necesidad

Canales -



Clientes Mayores de 18 años

Ingreso por servicio o Simple o Doble o Triple o Matrimonial

Recursos clave -

9

Infraestructura del Hotel “EL PICAFLOR” Tecnología de Seguridad Recursos Financieros Personal Confiable



Actividades clave -



Socios -



Proceso de Gestión de Recursos Humanos Proceso de Gestión de Financiación Proceso de Gestión de Clientes Proceso de Gestión de Seguridad

Familia Proveedores de Equipos Sistemas de Información (en desarrollo)

Estructura de costos -

Costos Variables o

-

Costos Fijos o o o o

-

Tiempo de estadía de los clientes

Servicio de Hospedaje Simple Servicio de Hospedaje Doble Servicio de Hospedaje Trio Servicio de Hospedaje Matrimonial

Gastos o o o o o o

Personal Impuestos Servicio de Limpieza Servicio de Energía Servicio de Agua Imprevistos

1.2 Problemas del negocio La información obtenida dentro del Hotel EL PICAFLOR es que al ser un servicio que las personas optan para su estadía por motivos personales, no cuenta con un apoyo administrativo para que pueda administrar y registrar a los clientes ya que estos se da de una forma manual y eso hace que el proceso de atención al cliente sea más tedioso y requiera más tiempo para registrar, cobrar y atender la información de entrada de los clientes al momento de que realizar una reserva ya sea interna o externa. Otro factor dentro del Hotel EL PICAFLOR es que no cuenta con un sistema el cual le permita acelerar el proceso los servicios que ofrece el hotel, así mismo no cuenta con una base de datos en el que puedan saber más sobre el cliente para ofrecerles promociones. Es por ello que se desarrollara un sistema web para mejorar el proceso de control y atención del cliente en el Hotel EL PICAFLOR, lo cual nos lleva a una mejor calidad.

10

1.3 Matriz de proceso vs entidad de negocio

Crea Data Mayor Uso Menor Uso

Leyenda ENTIDADES

CLIENTE

PERSONAL

PROCESOS PROCESO APOYO ADMINISTRATIVO PROCESOS DE APOYO LOGÍSTICO ATENCIÓN AL USUARIO ATENCION WEB

2.

Resumen de los requerimientos 2.1.

Requerimientos funcionales

Nro.

Requerimiento

Descripción

1

Registrar los atributos de las habitaciones

Almacenar el número de habitación, precio, tipo de habitación, estado (disponibilidad) en el que se encuentran las habitaciones, entre otros.

2

Buscar Habitaciones

Realizar

la

búsqueda

de

habitaciones

disponibles y mostrarlas en pantalla.

3

Registrar los datos del usuario

Almacenar lo datos de los usuario que han solicitado reservas vía telefónicas o presencial.

11

4

Registrar el consumo del usuario

Almacenar los diferentes consumos hechos por el usuario durante su estadía en el hotel dentro de una factura para ser impresa.

5

Generar comprobantes de pago

Generar un comprobante de pago, en el cual figura el precio total de los servicios brindados al usuario.

2.2.

Nro. 1

Requerimientos no funcionales

Requerimiento Restricciones del diseño

Descripción Se desarrollará el sistema con la tecnología de Java Application (Plataforma Netbeans).

2

Acceso

Se proporcionará un usuario y contraseña a la persona encargada del hotel.

3

4

5

Manipulación de Datos

Los datos de la aplicación solo podrán ser modificados por aquellas personas autorizadas (administrador, recepcionista).

Disponibilidad

El software funcionará offline, para visualizar los servicios de los hoteles y precios.

Desempeño

El tiempo de respuesta del sistema para operaciones de búsqueda y registro debe ser como máximo de 1 minuto.

6

Usabilidad

12

Facilidad de uso en cada interface.

2.3.

Nro. 1

Requerimientos de implementación

Requerimiento Lenguaje de programación

Descripción El sistema se desarrollará en el lenguaje de programación Java junto con Netbeans 8.2.

2

Interfaz del sistema

El diseño de la interfaz, se desarrollará utilizando el lenguaje JavaScript y estilo en cascada (CSS).

3

Gestor de base de datos

4

Hardware del Sistema

Se utilizara MySQL. Procesador core – i3 o superior, 4 GB de RAM y disco duro de 500GB.

5

Servidor de página web

Se utilizara el servidor de páginas web Apache.

B III.

DISEÑO LÓGICO: MODELO LOGICO

1. Normalización • reserva (idreserva, idhabitacion, idcliente, idtrabajador) • persona (idpersona)

2. Relaciones y Atributos    

13

Persona (IdPersona, Nombre, apaterno, amaterno, tipo_documento, num_documento, Direccion, Telefono, email) Cliente (IdPersona, Codigo_Cliente) Trabajador (IdPersona, Sueldo, acceso, logi, Password, Estado) Habitacion (IdHAbitacion, Numero, piso, Descripcion, características, Precio_diario, Estado, tipo_habitacion)

     

Reserva (IdReserva, IdCliente, IdTrabajador, FechaReserva, tipo_reserva, Costo_total_alojamiento, cant_hab, Estado) Pago (IdPago, IdREserva, tipo_Comporbante, num_comprobante, Igv, total_pago, Fecha_pago) Producto (IdProducto, Nombre, Descripcion,unidad_medida Precio_venta) Consumo (IdConsumo, IdReserva, total_consumo, fecha_consumo) Detconsumo (iddetconsumo, idconsumo, idproducto, cantidad, importe) Detreserva (iddetreserva, idreserva, idhabitacion, fecha_ingresa, fecha_salida, importe)

3. Dominios 





Persona: -

idpersona int identity NOT NULL nombre varchar(20) NOT NULL apaterno varchar(20) NOT NULL amaterno varchar(20) NOT NULL tipo_documento varchar(15) NOT NULL num_documento varchar(8) NOT NULL direccion varchar(100) DEFAULT NULL telefono varchar(10) DEFAULT NULL email varchar(35) DEFAULT NULL

Cliente: -

idpersona int NOT NULL codigo_cliente varchar(10) NOT NULL UNIQUE

detconsumo:

-

14

idconsumo int NOT NULL iddetconsumo int NOT NULL idproducto int NOT NULL cantidad int NOT NULL importe decimal(7,2) NOT NULL



Habitacion: - idhabitacion int NOT NULL identity(1,1) - numero int NOT NULL - piso int NOT NULL - descripcion varchar(255) DEFAULT NULL - caracteristicas varchar(512) DEFAULT NULL - precio_diario decimal(7,2) NOT NULL - estado varchar(15) NOT NULL - tipo_habitacion varchar(20) NOT NULL



trabajador idpersona int NOT NULL, sueldo decimal(7,2) NOT NULL, acceso varchar(25) NOT NULL,



Reserva: -

-

login varchar(15) NOT NULL, password varchar(20) NOT NULL, estado varchar(1) NOT NULL,

idreserva int NOT NULL identity idcliente int NOT NULL idtrabajador int NOT NULL tipo_reserva varchar(20) NOT NULL fecha_reserva date NOT NULL cant_hab int NOT NULL costo_total_alojamiento decimal(7,2) NOT NULL estado varchar(15) NOT NULL



detreserva: - idreserva int NOT NULL - iddetreserva int NOT NULL - idhabitacion int NOT NULL - fecha_ingresa date NOT NULL - fecha_salida date NOT NULL - importe decimal (7,2) NOT NULL



Pago: -



Producto:



idpago int NOT NULL identity idreserva int NOT NULL tipo_comprobante varchar(20) NOT NULL num_comprobante int NOT NULL igv decimal(4,2) NOT NULL total_pago decimal(7,2) NOT NULL fecha_pago date NOT NULL

idproducto int NOT NULL identity nombre varchar(45) NOT NULL descripcion varchar(255) DEFAULT NULL unidad_medida varchar(20) NOT NULL precio_venta decimal(7,2) NOT NULL

Consumo: - idconsumo int NOT NULL IDENTITY(1,1) - idreserva int NOT NULL - total_consumo decimal (7,2) NOT NULL - fecha_consumo date NOT NULL

4. Restricciones 4.1 Integridad de Entidad  reserva(idreserva, idtrabajador, idcliente,idhabitacion, tipo_reserva, fecha_reserva,fecha_ingresa, fecha_salida, costoalojamiento, estado)

15

idtrabajador,idcliente,idhabitacion son ajenas a la tabla reserva por eso se aceptan valores nulos Además, tipo_reserva, fecha_reserva, fecha_ingresa, fecha_salida, costoalojamiento, estado acepta nulos si es parcial 4.2 Integridad Referencial Tabla producto

Campo (PK)

Tablas relacionadas (FK) consumo

Detalles En una tabla consumo no se puede hacer referencia a un producto, si es que no existe la tabla producto.

idpproducto

Tabla reserva Campo (PK)

Tablas relacionadas (FK) consumo

idreserva pago

IV.

DISEÑO FISICO: MODELO FISICO

1.

Base de Datos Físico

16

Detalles En una tabla consumo no se puede hacer referencia a una reserva, si es que no existe la tabla reserva. En una tabla consumo no se puede hacer referencia a un reserva, si es que no existe la tabla reserva.

2.

Sentencias SQL 2.1. Procedimientos Almacenados (Store Procedure) Es un programa almacenado físicamente en una base de datos, el cual, al ser ejecutado, en respuesta a una petición de usuario, es ejecutado directamente en el motor de bases de datos. Como tal, posee acceso directo a los datos que necesita manipular y sólo necesita enviar sus resultados de regreso al usuario, deshaciéndose de la sobrecarga resultante de comunicar grandes cantidades de datos salientes y entrantes.

2.2. SP aplicados a la base de datos del sistema

create proc usp_mantenimiento_persona @idpersona int, @nombrepersona varchar(20), @apematerno varchar(20), @apepaterno varchar(20), @tipodoc varchar(15), @numdoc varchar(8), @direccionpersona varchar(100), @telefonopersona varchar(8), @emailpersona varchar(25), @opcion int As

if(@opcion=1) begin

17

insert persona values(@idpersona,@nombrepersona,@apematerno,@apepaterno,@tipodoc,@nu mdoc,@direccionpersona,@telefonopersona,@emailpersona) end

else if(@opcion=2) begin delete from persona where idpersona=@idpersona end

else if(@opcion=3) begin update persona set nombre=@nombrepersona, amaterno=@apematerno, apaterno=@apepaterno, tipo_documento=@tipodoc,num_documento=@numdoc, direccion=@direccionpersona, telefono=@telefonopersona, email=@emailpersona where idpersona=@idpersona end

--

create proc usp_mantenimiento_habitacion

@idhabitacion int , @numero varchar(4), @piso varchar(2), @descripcion varchar(255), @caracteristicas varchar(512), @precio_diario decimal(7,2), @estado varchar(15), @tipo_habitacion varchar(20), @opcion int as if(@opcion=1) begin insert habitacion values(@idhabitacion,@numero,@piso,@descripcion,@caracteristicas,@precio_di ario,@estado,@tipo_habitacion)

18

end

else if(@opcion=2) begin delete from habitacion where idhabitacion=@idhabitacion end

else if(@opcion=3) begin update habitacion set numero=@numero,piso=@piso,descripcion=@descripcion,caracteristicas=@caracteris ticas,precio_diario=@precio_diario,estado=@estado,tipo_habitacion=@tipo_habitacion where idhabitacion=@idhabitacion end -create proc usp_mantenimiento_producto

@idproducto int, @nombre varchar(45), @descripcion varchar(255), @unidad_medida varchar(20), @precio_venta decimal(7,2), @opcion int as if(@opcion=1) begin insert producto values(@idproducto,@nombre,@descripcion,@unidad_medida,@precio_venta) end

else if(@opcion=2) begin delete from producto where idproducto=@idproducto end

else if(@opcion=3) begin

19

update producto set nombre=@nombre,descripcion=@descripcion,unidad_medida=@unidad_medida,p recio_venta=@precio_venta where idproducto=@idproducto end

/* 1. Crear un Store Procedure que liste las habitaciones reservadas entre una fecha XXX y YYY que tengan un estado ZZZZ. Debe mostrar el id,descripcion,caracteristicas,precio,fecha reservada,estado */ create procedure usp_listado_reserva_x_fecha_x_estado @fecha1 date, @fecha2 date, @estado varchar(20) as select h.idhabitacion,h.descripcion,h.descripcion,h.precio_diario,r.fecha_reserva,r.estado from habitacion as h inner join reserva as r on h.idhabitacion=r.idhabitacion where fecha_reserva between @fecha1 and @fecha2 and r.estado=@estado

/* 2. Crear un Store Procedure que liste todos los clientes que han realizado alguna reserva en un mes AAAA */ create procedure usp_listado_clientes_reserva_x_mes @mes int as select c.idpersona,nombre,apaterno,tipo_documento, p.num_documento ,MONTH(r.fecha_reserva) as NumeroMesReserva from reserva as r inner join cliente as c on r.idcliente=c.idpersona inner join persona as p on c.idpersona=p.idpersona where MONTH(r.fecha_reserva)=@mes

/* 3. Crear un Store Procedure que liste el consolidado del gasto en servicios realizado por las reservas de un cliente ZZZZ entre un año XXX e YYYY. */ create procedure usp_listado_consolidado_gasto_x_reserva_x_años

20

@anno1 int, @anno2 int, @idcliente int as select r.idcliente,p.nombre,p.tipo_documento,p.num_documento,YEAR(r.fecha_reserva ) as AñoReserva, SUM(r.costo_alojamiento) as TotalGasto from reserva as r inner join cliente as c on r.idcliente=c.idpersona inner join persona as p on c.idpersona=p.idpersona where r.idcliente=@idcliente and YEAR(fecha_reserva) between @anno1 and @anno2 group by r.idcliente,p.nombre,p.tipo_documento,p.num_documento,YEAR(r.fecha_reserva )

2.3. Triggers (Disparadores) Son objetos que se asocian con tablas y se almacenan en la base de datos, los cuales se ejecutan cuando sucede algún evento sobre las tablas a las que se encuentra asociado. Los eventos que hacen que se ejecute un Triggers son las operaciones de inserción (INSERT), borrado (DELETE) o actualización (UPDATE), ya que modifican los datos de una tabla.

2.4. Triggers aplicado a la base de datos del sistema *Crear un trigger que registre los cambios en el historial de reserva (que registra cambios de habitación, fecha de ingreso, fecha de salida y costos) /*Existiendo la tabla previamente*/

CREATE TABLE tb_historial_reserva ( nro int, idreserva int NOT NULL, idhab_ant int NOT NULL, idhab_nu int NOT NULL, fecha_in_ant date NOT NULL, fecha_in_nu date NOT NULL,

21

fecha_sal_ant date NOT NULL, fecha_sal_nu date NOT NULL, costo_ant decimal NOT NULL, costo_nu decimal NOT NULL, fecha_de_cambio date not null, PRIMARY KEY (nro) )

INSERT INTO tb_historial_reserva (nro, idreserva, idhab_ant, idhab_nu,fecha_in_ant, fecha_in_nu, fecha_sal_ant,fecha_sal_nu,costo_ant,costo_nu,fecha_de_cambio) VALUES (1,1, 101,102,'2016-02-15','2016-02-17','2016-02-16','2016-02-18', '100.00','100.00',getdate()); go

*creamos el trigger para registrar los cambios

CREATE TRIGGER tr_cambios_reserva on reserva for update as INSERT INTO tb_historial_reserva SELECT (SELECT MAX(nro)+1 from tb_historial_reserva),ins.idreserva,del.idhabitacion,ins.idhabitacion, del.fecha_ingresa,ins.fecha_ingresa,del.fecha_salida,ins.fecha_salida, del.costo_alojamiento,ins.costo_alojamiento,getdate() FROM INSERTED ins inner join DELETED del on ins.idreserva=del.idreserva GO ----select * from tb_historial_reserva;

----update reserva SET fecha_ingresa='2016-02-15', fecha_salida='2016-02-17' WHERE idreserva=2

*Crear un trigger que registre los cambios en el historial de reserva(que registra cambios de habitación,fecha de ingreso,fecha de salida y costos)

/*Existiendo la tabla previamente*/

22

CREATE TABLE tb_historial_consumo ( nrocambiocons int, idconsumo int NOT NULL, idpro_ant int NOT NULL, idpro_nu int NOT NULL, cant_ant decimal NOT NULL, cant_nu decimal NOT NULL, pr_venta_ant decimal NOT NULL, pr_venta_nu decimal NOT NULL, fecha_cambio date, campo_cambiado varchar(40), PRIMARY KEY (nrocambiocons) ) INSERT INTO tb_historial_consumo (nrocambiocons, idconsumo, idpro_ant, idpro_nu,cant_ant, cant_nu, pr_venta_ant,pr_venta_nu,fecha_cambio,campo_cambiado) VALUES (1,1, 1,1,'3.00','5.00','100.00','100.00',getdate(),'cantidad'); go --select * from tb_historial_consumo—

*Creamos el trigger para registrar los cambios. Usamos @tipo1, @tipo2, @tipo3 para registrar los campos que ha podido haber cambios

CREATE TRIGGER tr_cambios_consumo on consumo for update as declare @tipo1 varchar(15), @tipo2 varchar(15), @tipo3 varchar(15) select @tipo1='',@tipo2='',@tipo3='' if update(idproducto) begin set @tipo1='producto' end if update(cantidad) begin set @tipo2='cantidad'

23

end if update(precio_venta) begin set @tipo3='precio' end

INSERT INTO tb_historial_consumo SELECT (SELECT MAX(nrocambiocons)+1 from tb_historial_consumo),ins.idconsumo,del.idproducto,ins.idproducto, del.cantidad,ins.cantidad,del.precio_venta,ins.precio_venta,getdate(),cast(@tipo1 as varchar(15))+' '+ cast(@tipo2 as varchar(15))+' '+cast(@tipo3 as varchar(15)) FROM INSERTED ins inner join DELETED del on ins.idconsumo=del.idconsumo GO

*Crear un trigger que registre las actualizaciones en la tabla persona en el historial de persona (que registra cambios de direccion, telefono,email) y mencione los campos que hayan cambiado /*Existiendo la tabla previamente*/

CREATE TABLE tb_historial_persona ( nrohistp int, idpersona int NOT NULL, dir_ant varchar(100) DEFAULT NULL, dir_nu varchar(100) DEFAULT NULL, tel_ant varchar(10) DEFAULT NULL, tel_nu varchar(10) DEFAULT NULL, email_ant varchar(40) DEFAULT NULL, email_nu varchar(40) DEFAULT NULL, fecha_cambiop date, campo_cambiadop varchar(60), PRIMARY KEY (nrohistp), ) GO

INSERT INTO tb_historial_persona (nrohistp, idpersona, dir_ant, dir_nu,tel_ant, tel_nu, email_ant,email_nu,fecha_cambiop,campo_cambiadop) VALUES

24

(1,1, 'Chiclayo - Ca.Angamos 34','Chiclayo - Ca. Angamos 34', '98745698','98745698','','[email protected]',getdate(),'e-mail'); go

*Creamos el trigger para registrar los cambios Usamos @tipo1, @tipo2, @tipo3 para registrar los campos que ha podido haber cambios

CREATE TRIGGER tr_cambios_persona on persona for update as declare @tipo1 varchar(15), @tipo2 varchar(15), @tipo3 varchar(15) select @tipo1='',@tipo2='',@tipo3='' if update(direccion) begin set @tipo1='direccion' end if update(telefono) begin set @tipo2='telefono' end if update(email) begin set @tipo3='e-mail' end

INSERT INTO tb_historial_persona SELECT (SELECT MAX(nrohistp)+1 from tb_historial_persona),ins.idpersona, del.direccion,ins.direccion, del.telefono,ins.telefono,del.email,ins.email,getdate(),cast(@tipo1 as varchar(15))+' '+ cast(@tipo2 as varchar(15))+' '+cast(@tipo3 as varchar(15)) FROM INSERTED ins inner join DELETED del on ins.idpersona=del.idpersona GO --update persona set email='[email protected]' where idpersona=1 --use basereserva

25

--drop table tb_historial_persona --drop trigger tr_cambios_persona

*Crear un trigger que registre en las reservas los registros insertados en pago en el asumiendo por defecto una habitación(101) y que no esté pagada

*/creamos el trigger para registrar los cambios*/

CREATE TRIGGER tr_efectuar_pago on pago for insert as INSERT INTO reserva SELECT 101,103,201,'reserva',ins.fecha_emision,ins.fecha_emision, ins.fecha_pago,ins.total_pago ,'No pagada' FROM INSERTED ins GO

3.

Estrategia de indexación.

Un índice es una estructura asociada a una tabla que acelera la recuperación de filas de la tabla. Un índice contiene claves generadas a partir de una o varias columnas de la tabla. Dichas claves están almacenadas en una estructura (árbol) que permite que SQL Server busque de forma rápida y eficiente la fila o filas asociadas a los valores de cada clave. 3.1. Índice Non-Clustered Los índices no clúster tienen una estructura separada de las filas de datos. Un índice no clúster contiene los valores de clave de índice no clúster y cada entrada de valor de clave tiene un puntero a la fila de datos que contiene el valor clave. 

Se presentan ordenados, pero no definen físicamente el orden de los datos.



Pueden haber más de uno.

3.2. Índice Clustered Los índices clúster ordenan y almacenan las filas de los datos de la tabla de acuerdo con los valores de la clave del índice. Son columnas incluidas en la definición del índice. Solo puede

26

haber un índice clúster por cada tabla, porque las filas de datos solo pueden estar ordenadas de una forma. 

Definen físicamente el orden de los datos.



Solo puede haber un índice Clustered por tabla.

Se aplicarán índices en las siguientes tablas:



En la tabla Habitación se realizarán mayormente las búsquedas por el número de habitación para consultar la disponibilidad de una habitación, es por ello que se creará un índice Non-Clustered en el campo “numero”.



En la tabla Reserva, también se realizará la mayor cantidad de consultas mediante la fecha en el que se hizo la reserva, es por ello que se creará un índice Non-Clustered en el campo “fecha_reserva”.

27