Conclusiones del Proyecto

A continuación se presentaran las conclusiones y resultados finales del proyecto "Control_Cine" elaborado en el curso Bases de Datos de la carrera Ingeniería en Computación del Instituto Tecnológico de Costa Rica, a cargo del Profesor Franco Quiros.

La elaboración del proyecto anterior fue muy provechosa a nivel académico, ya que este es el primer curso de Bases de Datos que cursamos algunos de los estudiantes, en este caso estamos incluidos, por lo tanto al inicio del curso no contábamos con casi ningún conocimiento del tema, conocimientos que se fueron adquiriendo con el transcurso del curso, pero con mas intensidad en la elaboración del proyecto ya que teníamos que enfrentarnos a los problemas en un "ambiente real".

A lo largo del proyecto se tuvieron varios inconvenientes por la falta de experiencia y conocimiento, pero fueron solucionados consultando conocimiento experto del profesor, así como de profesionales en el tema. También hubo la necesidad de acudir a fuentes de información en Internet para poder capacitarse en la solución del problema.

Para el desarrollo del proyecto se decidió programar la aplicación web en ASP.NET, por lo que se tuvo que llevar a cabo una capacitación en ASP.NET, la cual resulto un poco complicada al inicio para generar la conexión con la Base de Datos.

El desarrollo de la aplicación de escritorio no causo mayor problema del que se tenia planeado, ya que se contaban con los conocimientos suficientes para su elaboración.

Por ultimo, la generación de la Base de Datos y en especial de los Store Procedures fue la parte que se complico mas, por la falta de conocimientos señalada anteriormente, como también algunos aspectos en los cálculos, que no se tenían claros en la especificación. Uno de los mayores inconvenientes fue el constante cambio de la versión de la Base de Datos no normalizada que había que migrar, ya que esto no deja avanzar correctamente el proyecto y había que estar retrocediendo a generar cambios en el diseño de la Base de Datos y el los script de migración y procedimientos.

Los resultados finales del proyecto fueron positivos, se pudo completar el proyecto al 90%, fue un trabajo muy laborioso por todos los aspectos en los que hubo que capacitarse tanto en el uso de la herramienta de SQL, como en los lenguajes SQL y ASP.NET. La duracion total del proyecto fue de 33 horas

Modificaciones a la consulta nº3

Se modifica la consulta numero 3 debido a que no se había implementado en la base de datos y no se habrían hecho las pruebas respectivas para probar que funcionaran correctamente las consultas asi quedo la modificación de la primera parte:

CREATE PROC FBSP_GetEstaciones2Prioridad
@fecha DATE
AS
BEGIN


SELECT E.Nombre,COUNT(F.Id_Ficha) Tiquetes, S.Nombre,EXS.Prioridad
FROM FB_Estacion E
INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion
INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio AND EXS.Prioridad=2
INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio
GROUP BY E.Nombre,S.Nombre,EXS.Prioridad,F.TiempoInicioServicio,S.Nombre
HAVING (DATEPART(WEEK,F.TiempoInicioServicio))=(DATEPART(WEEK,@fecha))
ORDER BY Tiquetes DESC

END

La segunda parte que no se había realizado quedo de la siguiente manera  probado he implementado:

CREATE PROC FBSP_GetCajeros3Prioridad
@fecha DATE
AS
BEGIN

--declare @fecha date= '2013-01-14'
SELECT DISTINCT C.Nombre Cajero,E.Nombre Estacion,COUNT(F.Id_Ficha) Tiquetes,S.Nombre TipoServicio,EXS.Prioridad
,(DATEPART(WEEK,F.TiempoInicioServicio)) Fecha
FROM FB_Cajero C
INNER JOIN FB_CajeroXEstacion CXE ON CXE.FK_Cajero=C.Id_Cajero
INNER JOIN FB_Estacion E ON E.Id_Estacion=CXE.FK_Estacion
INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion
INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio AND EXS.Prioridad=3
INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio
GROUP BY C.Nombre,E.Nombre,F.TiempoInicioServicio,S.Nombre,EXS.Prioridad
HAVING (DATEPART(WEEK,F.TiempoInicioServicio))=(DATEPART(WEEK,@fecha))
ORDER BY Tiquetes DESC

END

Duracion 4 horas

Consulta nº2


Para la consulta numero 2 se noto que en esta no se devolvían lo que solicitaba por esto se analizo el problema y se decidió modificar la primera parte y realizar la segunda en base a esta. Asi quedo la primera parte:






CREATE PROC FBSP_getEstacionEsperaLargaPromedio
AS
BEGIN
SELECT DISTINCT E.Nombre Estacion,C.Nombre Cajero,SUM(EXE.Duracion) Duracion
,AVG(F.TiempoEspera) Espera

FROM FB_EventoXEstacion EXE
INNER JOIN FB_CajeroXEstacion CXE ON CXE.FK_Cajero= EXE.FK_CajeroXEstacion
INNER JOIN FB_Cajero C ON C.Id_Cajero=CXE.FK_Cajero
INNER JOIN FB_Estacion E ON E.Id_Estacion=CXE.FK_Estacion
INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion
INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio AND EXS.Prioridad=3
INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio
GROUP BY E.Nombre,C.Nombre
ORDER BY Espera DESC
END





Asi quedo la segunda parte :






CREATE PROC FBSP_get3CajeroEsperaLargaPromedio
AS
BEGIN

SELECT DISTINCT C.Nombre Cajero,E.Nombre Estacion,SUM(EXE.Duracion) Duracion
,AVG(F.TiempoEspera) Espera
FROM FB_EventoXEstacion EXE
INNER JOIN FB_CajeroXEstacion CXE ON CXE.FK_Cajero= EXE.FK_CajeroXEstacion
INNER JOIN FB_Cajero C ON C.Id_Cajero=CXE.FK_Cajero
INNER JOIN FB_Estacion E ON E.Id_Estacion=CXE.FK_Estacion
INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion
INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio AND EXS.Prioridad=3
INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio
GROUP BY C.Nombre,E.Nombre
ORDER BY Estacion

END






Duracion 3 horas

Consulta Nº3

Consulta 3:
Entradas: Una fecha
Partiendo de lunes anterior mas cercano, para cada dia de la semana,
a) liste las estaciones que recibe más tiquetes o fichas que corresponden al tramite de segunda prioridad.
b) Liste los funcionarios a cargo que recibe más tiquetes o fichas que corresponden al tramite de tercera prioridad, respecto la estacion en donde laboran durante el dia.

La cual queda asi en SQL:

/* 2HORAS

Consutla 3: 

Entradas: Una fecha

Partiendo de lunes anterior mas cercano, para cada dia de la semana, 

a) liste las estaciones que recibe más tiquetes o fichas que corresponden al tramite de segunda prioridad.

*/

ALTER PROC FBSP_GetEstaciones2Prioridad

@fecha DATE

AS

BEGIN

SELECT E.Nombre,COUNT(F.Id_Ficha) Tiquetes, S.Nombre,EXS.Prioridad

FROM FB_Estacion E 

INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion

INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio AND EXS.Prioridad=2

INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio

GROUP BY E.Nombre,S.Nombre,EXS.Prioridad,F.TiempoInicioServicio,S.Nombre

HAVING (DATEPART(WEEK,F.TiempoInicioServicio))=(DATEPART(WEEK,@fecha))

ORDER BY Tiquetes DESC

END

/*

b) Liste los funcionarios a cargo que recibe más tiquetes o fichas que corresponden al

 tramite de tercera prioridad, respecto la estacion en donde laboran durante el dia.

*/

ALTER PROC FBSP_GetCajeros3Prioridad

@fecha DATE

AS

BEGIN

--declare @fecha date= '2013-01-14'

SELECT DISTINCT C.Nombre Cajero,E.Nombre Estacion,COUNT(F.Id_Ficha) Tiquetes,S.Nombre TipoServicio,EXS.Prioridad

,(DATEPART(WEEK,F.TiempoInicioServicio)) Fecha

FROM FB_Cajero C 

INNER JOIN FB_CajeroXEstacion CXE ON CXE.FK_Cajero=C.Id_Cajero

INNER JOIN FB_Estacion E ON E.Id_Estacion=CXE.FK_Estacion

INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion

INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio AND EXS.Prioridad=3

INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio

GROUP BY C.Nombre,E.Nombre,F.TiempoInicioServicio,S.Nombre,EXS.Prioridad

HAVING (DATEPART(WEEK,F.TiempoInicioServicio))=(DATEPART(WEEK,@fecha))

ORDER BY Tiquetes DESC

END

Duración 4horas

Consulta Nº2

Para la consulta nº 2 se solicito lo siguiente:

Consulta 2:
Entradas: Un rango de fechas.
a) Para cada estacion, listar los nombres 3 funcionarios que estando a cargo de esa estacion de trabajo, producen las esperas mas largas y el tiempo de espera promedio, ordenadas e mayor a menor respecto del tiempo de espera/
b) Los 3 funcionarios que estando a cargo de esa estacion de trabajo, producen las esperas mas largas y el tiempo de espera promedio

La cual quedo asi en sql:

/*SELECT  E.Nombre, C.Nombre

FROM FB_Estacion E 

INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion

INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio

INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio

INNER JOIN FB_CajeroXEstacion CXE ON CXE.FK_Estacion = E.Id_Estacion

INNER JOIN FB_Cajero C ON CXE.FK_Cajero = C.Id_Cajero

GROUP BY E.Nombre, C.Nombre

ORDER BY E.Nombre

--HAVING COUNT(C.Nombre)= 3

*/

DECLARE @TEMP TABLE (NombreEstacion varchar(50),NombreEmpleado varchar(50))

DECLARE @Estaciones int

set @Estaciones = (SELECT COUNT(*)

FROM FB_Estacion

)

While(@Estaciones!=0)

BEGIN

--INSERT INTO @TEMP

SELECT TOP 3 E.Nombre,C.Nombre,MAX(F.TiempoEspera),AVG(F.TiempoEspera)

FROM FB_Estacion E 

INNER JOIN FB_EstacionXServicio EXS ON EXS.FK_Estacion=E.Id_Estacion

INNER JOIN FB_TipoServicio S ON S.Id_TipoServicio=EXS.FK_TipoServicio

INNER JOIN FB_Ficha F ON F.FK_TipoServicio=S.Id_TipoServicio

INNER JOIN FB_CajeroXEstacion CXE ON CXE.FK_Estacion = E.Id_Estacion

INNER JOIN FB_Cajero C ON CXE.FK_Cajero = C.Id_Cajero

WHERE E.Id_Estacion = @Estaciones

GROUP BY E.Nombre,C.Nombre

SET @Estaciones = @Estaciones -1 

END

SELECT * FROM @TEMP

Duracion 4 horas

Simulación de la Operación de Bases de Datos

Para la simulación de la base de datos se acordó realizar varios store procedures estas son las especificaciones necesarias:


Datos de operación. Usada para la simulación de la operación de la BD. Tiene las siguientes operaciones:

a) Un cliente solicitud de ficha: Entrada: Fecha y hora de la solicitud, tipo de solicitud. Operación: Se registra acción en la BD, se asigna una ficha (ej:S25).

b) Cajero (empleado) solicita cliente: Entrada: Identificador de estación, Fecha y hora. Operación: El sistema (un procedimiento almacenado que ejecuta la operación) saca de la cola de “fichas” sin atender y asigna el tiquete a la estación, tomando en cuenta reglas según tipo de servicio de la estación y prioridades. La asignación de la “ficha” a la estación tiene una hora de inicio y una hora de fin. Cuando el cajero solicita cliente, asigna hora de fin a la “ficha” previa y hora de inicio a la nueva ficha asignada.

c) Cierre de Estación: Entrada: Fecha y hora, Identificador de Estación. Operación: la estación se cierra, en la base de datos se registra la fecha/hora fin de operación de la estación. Si la estación tenia asignada un cajero, este automáticamente queda “fuera”, de tal manera que se actualiza la fecha-hora-fin, del empleado asignado a la estación.

d) Inicio servicio de empleado en estación. Entrada: Identificador de empleado, Identificador de estación. Operación: Si la estación esta cerrada, esta se abre (fecha-hora-inicio), se asigna al empleado (actualizando adecuadamente la fecha-hora-inicio del empleado en la estación). Si la estación está abierta y asignada a otro empleado, se cierra la asignación empleado – estación (actualizando adecuada la fecha-hora-cierre del empleado en la estación), y se asigna al nuevo empleado.

Se logro escribir el siguiente store procedure:

--CREATE PROCEDURE FBCP_AgregarFicha

Declare @Prefijo varchar(1),@TipodeServicio varchar(20), @Numero int, @codigo varchar(10),

@FKTipoServico INT

SET @Prefijo = (Select TS.Prefijo FROM FB_TipoServicio TS WHERE TS.Nombre = @TipodeServicio)

SET @Numero = (SELECT COUNT(*) FROM FB_Ficha F INNER JOIN FB_TipoServicio TS ON F.FK_TipoServicio = TS.Id_TipoServicio 

AND TS.Prefijo = @Prefijo

WHERE GETDATE()=F.TiempoSolicitud)

SET @codigo = @Prefijo + @Numero

SET @FKTipoServico = (SELECT TS.Id_TipoServicio FROM FB_TipoServicio TS WHERE TS.Prefijo = @Prefijo)

INSERT INTO FB_Ficha(FK_TipoServicio,Codigo,TiempoSolicitud,TiempoInicioServicio,TiempoFinServicio)

VALUES(@FKTipoServico,@codigo,GETDATE(),DEFAULT,DEFAULT)

Duración 3 horas

Migración de la Base de Datos no normalizada

Creación del script de migración de la base de datos no normalizada a la base de datos que se utilizar en el proyecto a continuación se presenta el codigo escrito en MS SQL:

DECLARE @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT,

@Message VARCHAR(200)

BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN

BEGIN TRY

INSERT INTO FB_Cajero(Nombre)

SELECT DISTINCT E.EMPLEADO

FROM Fila.dbo.asignacion E

INSERT INTO FB_TipoServicio(Nombre,Prefijo)

VALUES('USUARIO_ESPECIAL','E')

INSERT INTO FB_TipoServicio(Nombre,Prefijo)

VALUES('UN_SOLO_TRAMITE','A')

INSERT INTO FB_TipoServicio(Nombre,Prefijo)

VALUES('MULTIPLES_TRAMITES','M')

INSERT INTO FB_TipoServicio(Nombre,Prefijo)

VALUES('SERVICIO_CLIENTE','S')

INSERT INTO FB_Estacion(Nombre)

SELECT C.ESTACION

FROM Fila.dbo.configuracion C

 --Prioridad Uno Estacion

 INSERT INTO FB_EstacionXServicio(FK_Estacion,FK_TipoServicio,Prioridad)

 SELECT E.Id_Estacion,TP.Id_TipoServicio,1

 FROM Fila.dbo.configuracion C INNER JOIN FB_Estacion E ON C.ESTACION = E.Nombre

 INNER JOIN FB_TipoServicio TP ON TP.Nombre = C.TIPO_TRAMITE_PRIMERA_PRIORIDAD

  --Prioridad Dos Estacion

  

 INSERT INTO FB_EstacionXServicio(FK_Estacion,FK_TipoServicio,Prioridad)

 SELECT E.Id_Estacion,TP.Id_TipoServicio,2

 FROM Fila.dbo.configuracion C INNER JOIN FB_Estacion E ON C.ESTACION = E.Nombre

 INNER JOIN FB_TipoServicio TP ON TP.Nombre = C.TIPO_TRAMITE_SEGUNDA_PRIORIDAD

   --Prioridad Tres Estacion

   

 INSERT INTO FB_EstacionXServicio(FK_Estacion,FK_TipoServicio,Prioridad)   

 SELECT E.Id_Estacion,TP.Id_TipoServicio,3

 FROM Fila.dbo.configuracion C INNER JOIN FB_Estacion E ON C.ESTACION = E.Nombre

 INNER JOIN FB_TipoServicio TP ON TP.Nombre = C.TIPO_TRAMITE_TERCERA_PRIORIDAD

INSERT INTO  FB_CajeroXEstacion(FK_Estacion,FK_Cajero,FechaHoraAbre,FechaHoraCierra)

 SELECT  E.Id_Estacion,C.Id_Cajero ,

 (CONVERT (varchar(10),A.FECHA) +' '+CONVERT(varchar(10), A.HORA_INICIO)),

 (CONVERT (varchar(10),A.FECHA) +' '+CONVERT(varchar(10), A.HORA_FIN))

 FROM Fila.dbo.asignacion A INNER JOIN FB_Cajero C ON C.Nombre = A.EMPLEADO

 INNER JOIN FB_Estacion E ON E.Nombre = A.ESTACION

  

INSERT INTO FB_Ficha(FK_TipoServicio,Codigo,TiempoSolicitud,TiempoInicioServicio,TiempoFinServicio,)

SELECT DISTINCT

TS.Id_TipoServicio,

AF.FICHA,

(CONVERT (varchar(10),AF.FECHA) +' '+CONVERT(varchar(10), AF.HORA_EMISION)),

(CONVERT (varchar(10),AF.FECHA) +' '+CONVERT(varchar(10), AF.HORA_ATENCION)),

(CONVERT (varchar(10),AF.FECHA) +' '+CONVERT(varchar(10), AF.HORA_FIN))

  FROM Fila.dbo.atencion_ficha AF 

  INNER JOIN FB_TipoServicio TS ON AF.FICHA LIKE TS.Prefijo+'%'

  

 INSERT INTO FB_EventoXEstacion(FK_Ficha,FK_Estacion,Codigo,Duracion)

SELECT DISTINCT F.Id_Ficha,

E.Id_Estacion,

AF.FICHA,

DATEDIFF(MINUTE,AF.HORA_ATENCION,AF.HORA_EMISION)

  FROM Fila.dbo.atencion_ficha AF 

  INNER JOIN FB_TipoServicio TS ON AF.FICHA LIKE TS.Prefijo+'%'

  INNER JOIN FB_Ficha F ON AF.FICHA = F.Codigo

  INNER JOIN FB_Estacion E ON AF.ESTACION = E.Nombre

  

 COMMIT

  

END TRY

BEGIN CATCH

SET @ErrorNumber = ERROR_NUMBER()

SET @ErrorSeverity = ERROR_SEVERITY()

SET @ErrorState = ERROR_STATE()

SET @Message = ERROR_MESSAGE()

ROLLBACK

RAISERROR(@Message, @ErrorSeverity, @ErrorState)

END CATCH

END

Duración 4 horas