• Author / Uploaded
• Segundo Salvador Perez Castañeda

• Categories
• Servidor SQL de Microsoft
• SQL
• Hotel
• Datos
• Software

Citation preview

UNIVERSIDAD NACIONAL DE TRUJILLO

Base de datos Avanzadas

LABORATORIO Nº 12 TAREA DE APLICACIONES CON TRIGGERS 1. INDICACIONES GENERALES    

Los estudiantes podrán formar equipos de trabajo conformado por no más de 5 alumnos. Se pide elaborar un informe conteniendo el Script en T-SQL de SQL Server que solucione el caso planteado y las imágenes del diagrama de la base de datos desarrollado en SQL Server. El trabajo se califica con puntaje máximo de 2 puntos que serán agregados a la nota de la práctica calificada 1. La fecha de presentación será el día de la evaluación de la Unidad I. Por favor, se solicita que, si el trabajo no está lista en la fecha señalada, no insistir para ser presentado en fecha posterior.

2. DESCRIPCION DEL CASO: SISTEMA DE HOSPEDAJE Y SERVICIOS DE UNA HOSTAL

RESERVACION

Y

CONTROL

DE

La empresa “Hotel Las Flores”, es una compañía que es propietaria de una cadena de Hoteles, Hostales y Hospedajes a nivel del norte del Perú. En la compañía se ha encontrado algunos problemas en la administración; por ello se solicita la implementación de la base de datos para automatizar el proceso de reservación y alquiler de habitaciones, así como el control de estado y asignación a los huéspedes y el servicio consumo. Las operaciones del negocio se detallan a continuación: a. La oficina de recepción lleva a cabo el registro y reservación de habitaciones de los huéspedes ingresando sus datos personales tales como sus nombres y apellidos, edad, estado civil, procedencia, país, sexo, ciudad, teléfono, documento de identidad, fecha de ingreso y fecha de salida y el número de habitación asignada según los requerimientos: simple, doble, triple, matrimonial, familiar y suite, así como la cantidad de habitaciones que desea reservar. Asimismo, esta oficina efectúa las modificaciones o cancelaciones de las reservaciones cuando el cliente lo solicite. b. El proceso de asignación de hospedaje se realiza con la verificación del estado de una habitación, la que puede ser: “reservado”, “libre”, “ocupado”. c. La atención en las habitaciones tiene además algunos servicios adicionales, tales como: Televisor con cable, agua caliente, baños turcos, lavado y planchado de ropa, entre otros. Asimismo, se cuenta con los siguientes servicios: cafetería, restaurant, bar, auditorio, recepciones, entre otros. d. Los clientes pueden alquilar el Auditorio, el cual tiene una capacidad para 600 personas cómodamente sentadas, para ello harán sus reservaciones con anticipación indicando además los servicios adicionales requeridos, tales como; equipos de sonido, equipos audiovisuales (Televisor, VHS, Internet, computadora, Proyector de transparencias, cañón multimedia, Ecram, entre otros). Asimismo, se puede solicitar el servicio de Buffet y atención de mozos. e. Los clientes pueden acceder a cualquiera de estos servicios como un adicional, o también pueden solicitarlos sin necesidad de hospedarse.

DR. LUIS BOY CHAVIL

Páá giná 1

UNIVERSIDAD NACIONAL DE TRUJILLO

Base de datos Avanzadas

3. APLICACIONES A DESARROLLAR: 3.1 SCRIPT EN T-SQL PARA LA BASE DE DATOS CON RESTRICCIONES DE INTEGRIDAD DE DATOS



 

Implementar la Base de datos en T-SQL de SQL Server, teniendo en cuenta que se programarán las siguientes restricciones de integridad de datos: En la Tabla: Habitación; el dominio que se refiere al TipoHabitación será: “simple”,”doble”, “triple”, “matrimonial”, “familiar” y “suite”; prevaleciendo como valor por defecto el tipo “Simple”. En la Tabla: Habitación; el dominio que se refiere al EstadoHabitación será: “reservado”, “libre”, “ocupado”; prevaleciendo el estado “libre”. Programar las restricciones de integridad de las referencias (Relaciones entre tablas).

3.2 IMPLEMENTACIÓN DE FUNCIONES, TRIGGER Y CURSORES 1. Implementar un Trigger para que al ingresar un nuevo Huésped se pueda crear su Usuario cuyas características, son las siguientes: o Huésped es el cliente que Alquila un Servicio de Habitación del Hotel; más no es el cliente que solamente hace Reservaciones. o Estructura de la tabla USUARIO: USUARIO(UsuarioID, Login, Contraseña) Autogenerar la columna UsuarioID, teniendo en cuenta que su valor será igual a HuespedID. o El valor de Login, será:  1° Letra del ApPaterno del Huésped + Primera palabra del Nombre del Huésped + “@hotel.com” o El valor de la contraseña, será:  1° Letra del ApPaterno(en minúscula)+ última Letra del ApMaterno(en mayúscula)+N1°N2°N3° (N1:Cantidad de vocales que hay en Nombre del Cliente, N2: Cantidad de vocales que hay en ApPaterno del Cliente y N3: Cantidad de vocales que hay en ApMaterno del Cliente) o Ejemplo: Supongamos que el Huésped se llama: Nombre=“Juan Alberto”; ApPaterno=”Pérez”; y, ApMaterno=”López” Entonces: Login=”[email protected]” Contraseña=”pZ522” o

2. Implementar un Trigger para que al ingresar el registro del alquiler o Reserva de una Habitación, se cambie el Estado de dicha habitación que pasará de “libre” a “ocupada” o “reservada”; según sea el caso. En ambas situaciones, se requiere saber cuál será la fecha de inicio de alquiler o reserva y cuál será la fecha de finalización del alquiler o reserva.

DR. LUIS BOY CHAVIL

Páá giná 2

UNIVERSIDAD NACIONAL DE TRUJILLO

Base de datos Avanzadas

3.3 DIAGRAMA DE BASE DE DATOS EN SQL SERVER Implementar el diagrama de la base de datos anterior en SQL Server.

Solución: use master go create database BDHLasFlores001 go use BDHLasFlores001 go create table HABITACION( Id_Habitacion integer not null primary key identity(1,1), Nombre varchar(30) not null, Estado varchar(10) null check(Estado in('Libre','Ocupada','Reservada')) default('Libre'), TipoHabitacion varchar(15) null, Precio float not null ) GO

create table SERVICIO ( Id_Servicio int not null primary key identity(1,1), Nombre varchar(30) not null, DescripcionServ varchar(100) not null, Precio float not null ) Go create table TURNO( Id_Turno integer not null primary key identity(1,1), NombreTur Varchar (30)not NULL, HoraEntrada datetime not null, HoraSalida datetime not null, Sueldo float not null, Descripcion varchar(150) null ) go

create table PERSONA( Id_Persona int not null primary key identity(1,1), Nombre Varchar (30)not NULL, ApellidoP varchar(30) not null, ApellidoM varchar(30) not null, Sexo varchar(01) null check(Sexo in('F','M')) default('M'), Edad int not null, EstadoCivil varchar(10) null check(EstadoCivil in('Soltero','Casado','Viudo','Divorciado')) default('Soltero'), Telefono int NULL, Dni varchar(08) NULL, Loginn varchar(25) not null, Pass varchar(7) not null ) go Create table DIRECCION ( Id_direccion int not null primary key identity(1,1),

DR. LUIS BOY CHAVIL

Páá giná 3

UNIVERSIDAD NACIONAL DE TRUJILLO

Base de datos Avanzadas

Id_Persona integer not null foreign key references PERSONA(Id_Persona), Pais varchar(30) not null, Ciudad varchar(30) null, Provincia varchar(30) null, Distrito varchar(30) null, Calle varchar(30) null, Lote varchar(30) null, Rereferencia varchar(30) null, ) go

create table RECEPCIONISTA( Id_Recepcionista integer not null primary key identity(1,1), Codigo_Recepcionista Varchar (30)not NULL, Id_Persona int not null foreign key references PERSONA(Id_Persona), Id_Turno integer not null foreign key references TURNO(Id_Turno) ) go create table CONSUMO ( Id_Consumo int not null primary key identity(1,1), Id_Recepcionista integer not null foreign key references RECEPCIONISTA(Id_Recepcionista), Id_Persona integer not null foreign key references PERSONA(Id_Persona), Id_Habitacion integer not null foreign key references HABITACION(Id_Habitacion), FechaReservación Date NULL default (getdate()), FechaIngreso Date NULL, FechaSalida Date NULL, TipoConsumidor varchar(15) null check(TipoConsumidor in('Cliente','Huesped')) default('Huesped') ) go

create table DETALLE_CONSUMO ( Id_DetConsumo int not null primary key identity(1,1), Id_Consumo int not null foreign key references CONSUMO(Id_Consumo), Id_Sevicio int not null foreign key references SERVICIO(Id_Servicio), ) go

create table PAGO ( Id_Pago int not null primary key identity(1,1), Id_Consumo int not null foreign key references CONSUMO(Id_Consumo), Tipo_Pago varchar(15) null check(Tipo_Pago in('Efectivo','Tarjeta')) default('Efectivo'), Monto_Pagado float null default(00), Deuda float null, fecha_Pago Date NULL default (getdate()) ) go

use BDHLasFlores001

DR. LUIS BOY CHAVIL

Páá giná 4

UNIVERSIDAD NACIONAL DE TRUJILLO

Base de datos Avanzadas

go ------------------------------PROCEDIMIENTO ALMACENADO PARA INGRESAR PERSONA create procedure sp_NuevaPersona @Nombres varchar(30), @ApellidoP varchar(30), @ApellidoM varchar(30), @Sexo varchar(1), @Edad int , @EstadoCivil varchar(10), @Telefono int, @Dni varchar(8) as begin

declare @Loginn varchar(25) declare @Pass varchar(7)

set @Loginn=SUBSTRING(@ApellidoP, 1, 1) + @Nombres+'@hotel.com' set @Pass = LOWER(LEFT(@ApellidoP, 1))+UPPER(RIGHT(@ApellidoM, 1))+'' +(convert( varchar(7),dbo.fnContarVocales(@Nombres,0,0)))+'' +(convert( varchar(7),dbo.fnContarVocales(@ApellidoP,0,0)))+'' +(convert( varchar(7),dbo.fnContarVocales(@ApellidoM,0,0))) insert into PERSONA values ( @Nombres, @ApellidoP,@ApellidoM,@Sexo,@Edad, @EstadoCivil, @Telefono, @Dni,@Loginn,@Pass) end go

---FUNCION PARA CONTAR VOCALES-if OBJECT_ID('dbo.fnContarVocales', 'FN') is not null drop function dbo.fnContarVocales go create function dbo.fnContarVocales(@frase varchar(30),@Suma int,@Cont int) returns int AS Begin declare @Vocal varchar(1) Set @Vocal= SUBSTRING(@frase,@Cont,1) if @Vocal ='a' set @Suma= @Suma+1 else if @Vocal ='e' set @Suma= @Suma+1 else if @Vocal ='o' set @Suma= @Suma+1 else if @Vocal ='i' set @Suma= @Suma+1 else if @Vocal ='u' set @Suma= @Suma+1

End go

if @Cont