Una versión
simplificada del esquema de esta base de datos puede ser el siguiente:
Usuarios (user_id,
user_tipo_codigo,
user_nombre,
user_apellido,
user_telefono,
user_email,
user_direccion,
user_otros_datos)
Tipos_Usuario (user_tipo_codigo,
user_tipo_descrip)
Problemas (problem_id,
Equipamiento_id,
user_id,
problem_fechahora,
problem_descrip)
Status_Problemas (problem_status_codigo,
problem_status_descrip)
Personal_Soporte (personal_id,
personal_ingreso,
personal_egreso,
personal_nombre,
personal_sueldo,
personal_telefono,
personal_email,
personal_zona,
personal_otros_datos)
Tipos_Equipamiento (TipoEq_codigo,
TipoEq_descrip)
Equipamiento (Equipamiento_id,
TipoEq_codigo,
Equipamiento_comprado,
Equipamiento_disponible,
Equipamiento_numserie,
Equipamiento_nombre,
Equipamiento_descrip,
Equipamiento_fabricante,
otros_detalles)
Niveles_Prioridad (codigo_nivel_prioridad,
descrip_nivel_prioridad)
Historia_Problemas (Historia_Problemas_id,
codigo_nivel_prioridad,
problem_id,
problem_status_codigo,
personal_asignado,
fechahora_solucionado)
Soluciones (solucion_id,
Historia_Problemas_id,
solucion_descrip)
Tipos_Habilidades (Habil_codigo,
Habil_descrip)
Habilidades_Personal (personal_id,
Habil_codigo,
fecha_titulo)
Usuarios
Son los usuarios registrados en el sistema
que pueden hacer los reclamos, se tienen todos sus datos personales, se sabe
que su dirección de mail no se puede repetir para otros usuarios
Tipos Usuario
Cada usuario que hace un reclamo está
catalogado con un tipo, este tipo puede ser Administrador, Gerente, Mando
Medio, Usuario Final
Problemas
Son los diferentes tipos de problemas que
se reportan, en el se registra el equipamiento involucrado, el usuario que hace
el reclamo, la fecha y la hora del mismo y una descripción detallada.
Status de Problemas
Son los diferentes estados por los que
puede pasar un problema, este estado varía en el tiempo y es registrado en la
historia de los problemas.
Los diferentes estados pueden ser por
ejemplo Pendiente, Asignado, en Estudio, Cerrado, etc.
Personal de Soporte
Son los funcionarios de la empresa que
brindan el soporte a los usuarios, se conoce la fecha de ingreso a la empresa,
la fecha de egreso (si ya no trabaja mas debe ser mayo o igual a la de ingreso)
y el resto de los datos personales completos.
Se sabe que la dirección de correo de una
persona de soporte no se puede repetir en otra y que el sueldo debe ser mayor
que $ 10.000 y menor que $ 70.000.
Tipos de Equipamiento
Esta tabla registra los diferentes tipos de
equipamiento catalogados, por ejemplo algunos tipos definidos podrían ser PDA,
TABLET, TELEFONOS, PC, MONITOR, etc.
Equipamiento
Son los equipos a los que se les da el
soporte, se conoce la fecha en que fue comprado, la fecha desde cuando está
disponible para su uso, el número de serie que debe ser único para cada equipo,
el nombre del fabricante y otros datos importantes.
Es importante destacar que la fecha en la
que fue comprado debe ser menor o igual a la fecha en que queda disponible.
Niveles de Prioridad
Son los diferentes niveles de prioridad que
puede tomar un problema, los mismos pueden ser del tipo Alto, Medio o Bajo
Historia de Problemas
Es la tabla donde queda un registro
histórico de seguimiento de un problema reportado, en ella se registra la fecha
de solucionado solamente en caso de que exista una solución efectiva.
Soluciones
Una vez que se encuentra la solución a un
problema, en el momento de marcar la fecha de solución en el histórico, se debe
registrar en esta tabla una descripción detallada de la solución.
Esta tabla tiene un identificador auto
numérico.
Tipos de Habilidades
Son los diferentes tipos de conocimientos
que la empresa requiere de su personal, estos conocimientos están catalogados y
pueden ser por ejemplo Ingeniero, Técnico Superior, Sistemas Operativos, Redes,
etc.
Habilidades del Personal
En esta tabla se registra para cada
integrante del personal que habilidades tiene y la fecha en que obtuvo dicha
habilidad.
Se pide:
1.
Crear las
restricciones de integridad que surjan del análisis de la letra, sobre el
script de creación de tablas proporcionado (publicado en Aulas) (6 puntos)
a. Restricción para que dirección de mail no se puede repetir para
otros usuarios
USE HELPDESK;
GO
ALTER TABLE Usuarios
ADD CONSTRAINT UQ_user_email UNIQUE (user_email);
GO
2.Creación de índices que considere puedan
ser útiles para optimizar las consultas (según criterio establecido en el
curso) (2 puntos)
3.
Ingreso
de un juego completo de datos de prueba (será más valorada la calidad de los
datos más que la cantidad. El mismo debería incluir ejemplos que deban ser
rechazados por no cumplir con las restricciones implementadas. (2 puntos)
4. Crear procedimiento o funciones, según
corresponda, para:
a.
Crear el procedimiento Ejercicio4_a que dado un
rango de fecha se emita un listado de todos los problemas sin solución, el
listado debe tener por lo menos los siguientes datos: numero de problema,
descripción del equipamiento, fecha, hora, nombre y mail del usuario que
denuncia, descripción del problema y la descripción del tipo de equipamiento. (2
puntos)
b.
Crear la funcion Ejercicio4_b que para un
tipo de equipamiento dado, muestre la
cantidad de problemas reportados de dicho tipo en el año actual. (2 puntos)
c.
Dado un
tipo de habilidad, mostrar el sueldo total de los empleados con dicho tipo, la función se
debe llamar Ejercicio4_c (2 puntos)
5.
Crear disparadores
que permitan realizar las siguientes actividades:
a.
Disparador Ejercicio5_a que una
vez que se ingresa un problema se debe crear en forma automática una línea en
la tabla Historia_Problemas en un
estado ‘Pendiente’ con el nivel de prioridad más bajo y asignado a la persona
que tenga menos problemas asignados. (3 puntos)
b.
Crear una
tabla Auditoria(idAudit, fchAudit, usrAudit, problema_id, tiempoAudit) y un disparador Ejercicio5_b donde,
una vez cerrado un problema se genere un registro con la cantidad de horas que
demoró en cerrarse dicho problema desde que fue abierto, el identificador
de auditoria debe ser autonumerico.
(3 puntos)
c.
Implementar
un control Ejercicio5_c que no permita ingresar una solución de un
problema si la fecha de solucionado de la historia del problema está vacía, de
ser así marcar la fecha con la fecha del dia y luego si ingresar la solución. (3
puntos)
5.
Resolver
utilizando SQL las siguientes consultas:
a.
Para cada
persona de soporte, implementar una consulta que muestre su identificador, su
nombre, la cantidad de problemas resueltos, la cantidad de problemas sin
resolver y la fecha de el último problema asignado, si la persona nunca tuvo
problemas asignados igual debe figurar su identificador y su nombre dejando en
blanco (o nulo) el resto de los datos. (3 puntos)
b.
Mostrar
los datos de los equipamientos que tuvieron este año más problemas que la
cantidad promedio de problemas de todos los equipamientos en el año anterior. (3
puntos)
c.
Mostrar
los datos de los usuarios que reportaron más de 10 problemas en el último año
pero que no reportaron más de 5 problemas en todos los años anteriores. (3
puntos)
d.
Mostrar
nombre, teléfono, mail y descripción de habilidades para todos aquellos
técnicos que resolvieron problemas en la fecha mas reciente. (3 puntos)
6.
Crear una
vista Ejercicio7 que
permita mostrar a cada momento para cada tipo de usuario que hace reclamos la
cantidad de problemas reportados, la fecha mas antigua en la que se solucionó
un problema y la fecha mas antigua en que un problema sigue sin solución. (3
puntos)
El script completo solicitar a:
