Actividad 3 - Segunda Parte Caso Practico

Administración de Sistemas Gestores de Bases de Datos
Actividad 3: Segunda Parte: Caso Practico
Fundación Universitaria Compensar.

Facultad de Ingeniería.
Josué Roa Pinzón y Iván Esteban Mateus Soche.

Marzo 2023.
2
Tabla de Contenidos
Segunda Parte: Caso Practico ......................................................................................................... 3

1. Construir las consultas que corresponden a los siguientes requerimientos: ....................... 3
2. En el grupo de trabajo determinar 10 consultas donde se realicen subconsultas, entre las
tablas, definirlas y mostrar el resultado. ..................................................................................... 7
3. Definir y crear trigger de importancia para el manejo de la base de datos. ...................... 12
4. Cree un usuario llamado PERNO (Personal y Nomina) otorgándole permisos de resource,
connect, y dba. .......................................................................................................................... 13
5. Cree un perfil, Cree el PROFILE, PRO_FUNCIONARIO. ................................................. 13
6. Cree los usuarios. (JPINZON,SAGUIRRE,PPEREZ) .......................................................... 14
7. Construir los procedimientos almacenados para las siguientes necesidades. ....................... 16
3
Segunda Parte: Caso Practico
Teniendo en cuenta el caso propuesto para la IPS, dado en el desarrollo del curso,
vamos a agregar el módulo administrativo, donde tenemos la información de los
empleados y sus datos (Identificación, nombres, apellidos, teléfono, salario) y los
departamentos (Identificación, nombre)
Realizado el modelo lógico (modelo entidad relación y relacional) y el modelo físico
(script para la creación de tablas), ahora le corresponde atender las siguientes necesidades
que se han determinado para la empresa en mención de acuerdo, al levantamiento de
información
1. Construir las consultas que corresponden a los siguientes requerimientos:
• Un listado de todos los empleados que pertenecen a los departamentos de
Administración o IT.
SELECT B.*,C.*
FROM Empleados B
INNER JOIN Departamento C ON C.Iddep=B.dept
WHERE C.Nomdep='ADMINISTRATION' OR C.Nomdep='TI';
Figura 1. Resultado Listado de empleados departamentos de administración e IT.

4
• El reporte de la historia laboral de un empleado especifico.
SELECT b.*,c.nombre, c.apellido

FROM Historial b
INNER JOIN Empleados c ON c.nuempl=b.IdEmple
WHERE c.nombre='ERIKA' AND c.apellido='PULIDO'
Figura 2. Resultado Reporte Historial Laboral de un Empleado.
• La cantidad total de salarios que se deben pagar por cada uno de los
departamentos.
SELECT dept, SUM(salario) AS "Total Salario por Departamento"

FROM Empleados
GROUP BY dept;
Figura 3. Resultado Resumen total de pago por Departamento.

5
• Una consulta que muestre el promedio de los salarios, el menor salario, el
mayor salario.
SELECT AVG(SALARIO)AS "Promedio Salario", max (Salario)as "Maximo Salario",

min(salario) as "Minimo Salario"
FROM Empleados
Figura 4. Resultado Promedio, Mínimo y Máximo de todos los Salarios.
• Pacientes atendidos en un rango de fecha determinado
SELECT ID_PACIENTE
FROM Historial
WHERE FechaCita BETWEEN ‘2022/08/01’ AND ‘2022/09/25’;
Figura 5. Resultado Documentos de Pacientes Atendidos en un Rango de Fecha.

6
• Lista de médicos con sus especialidades
SELECT NOMBRE_MEDICO, APELLIDO_MEDICO, ESPECIALIDAD_MEDICO

FROM Medicos
Figura 6. Resultado Lista médicos y su Especialidades.
• Lista de pacientes que asisten a consulta con un motivo determinado
SELECT B.NOMBRE, B.APELLIDO , A.ESPECIALIDAD

FROM Historial A
INNER JOIN Paciente B ON A.ID_Paciente = B.ID_Paciente
Figura 7. Resultado Lista de Pacientes con Especialidad A Consultar.

7
• Pacientes a quienes se les asigna orden médica, bien sea para laboratorios,
imágenes diagnósticas y otro tipo de exámenes.
SELECT C.ID_PACIENTE, C.TIPO_DOCUMENTO, C.NOMBRE, C.APELLIDO,

C.FECHA_NACIMIENTO, C.EMAIL,C.DEPARTAMENTO,
C.LOCALIDAD,C.BARRIO,C.DIRECCION,C.TELEFONO,C.TIPO_AFILIACION
FROM Historial A
INNER JOIN OrdenMedica B ON A.ID_Orden = B.ID_Orden
INNER JOIN Paciente C ON A.ID_Paciente = C.ID_Paciente
Figura 8. Resultado Pacientes a los que se les asignan Ordenes medicas.
2. En el grupo de trabajo determinar 10 consultas donde se realicen
subconsultas, entre las tablas, definirlas y mostrar el resultado.
• Nombre y apellido de los pacientes con ordenes médicas y Tipo Examen.
SELECT C.NOMBRE , C.APELLIDO, B.TIPO_EXAMEN

FROM Historial A
INNER JOIN Paciente C ON A.ID_Paciente = B.ID_Paciente
Figura 9. Resultado Consulta Nombres pacientes con Tipo de Examen.

8
• Pacientes con Orden Medica que viven en la localidad 18.
SELECT C.NOMBRE , C.APELLIDO, C.LOCALIDAD,A.DIAGNOSTICO

FROM Historial A
WHERE C.LOCALIDAD = 18
Figura 10. Resultado Consulta Pacientes con Orden Medica que Viven en la
localidad 18.
• Pacientes con Orden Medica y que tienen Tipo de Documento CC-Cedula.
SELECT C. NOMBRE , C.APELLIDO, C.LOCALIDAD

FROM Historial A
WHERE C.TIPO_DOCUMENTO = ‘CC’
Figura 11. Resultado Consulta Pacientes con tipo de documento Cedula.

9
• Pacientes con Orden Medica que viven en Barrio Chapinero.
SELECT C. NOMBRE, C.APELLIDO, C.BARRIO

FROM Historial A
WHERE C.BARRIO = ‘Chapinero
Figura 12. Resultado Consulta Pacientes que residen en Barrio Chapinero.
• Pacientes con Orden Medica con Residencia Diferente a Bogotá.
SELECT C. NOMBRE, C.APELLIDO, C.DEPARTAMENTO

FROM Historial A
WHERE C.DEPARTAMENTO <> ‘BOGOTA’
Figura 13. Resultado Consulta Pacientes Con Residencia diferentes a BOG.
• Pacientes con Teléfono Finalizado en 1.
SELECT NOMBRE, APELLIDO, TELEFONO

FROM Paciente
WHERE RIGHT(TELEFONO,1) = ‘1’
Figura 14. Resultado Consulta Pacientes con teléfono de Contacto Terminado en 1.

10
• Pacientes con Orden Medica Registrados bajo el Tipo afiliación POS.
SELECT C. NOMBRE, C.APELLIDO, C.TIPO_AFILIACION

FROM Historial A
WHERE C.TIPO_AFILIACION =’POS’
Figura 15. Resultado Consulta Pacientes Con tipo afiliación POS.
• Pacientes con Orden Medica que tienen Email con Dominio @gmail.com.
SELECT C. NOMBRE, C.APELLIDO, C.EMAIL

FROM Historial A
WHERE C.EMAIL LIKE ‘%@gmail.com’
Figura 16. Resultado Consulta Pacientes con email del Dominio @gmai.coml.
11
• Pacientes con Orden Medica solicitada a la hora 13:00(01:00 pm).
SELECT C. NOMBRE, C.APELLIDO, CONVERT(DATE,

B.FECHA_SOLICITUD,3)AS FECHA_SOLICITUD, LEFT(CONVERT(TIME,
B.FECHA_SOLICITUD),5)AS HORA_SOLICITUD
FROM Historial A
WHERE CONVERT(TIME, B.FECHA_SOLICITUD) = ’13:00:00’
Figura 17. Resultado Consulta Orden medica solicitada a la hora 13:00.

12
3. Definir y crear trigger de importancia para el manejo de la base de datos.
Figura 18. Creación de Trigger Audit_empleados.

13
4. Cree un usuario llamado PERNO (Personal y Nomina) otorgándole permisos de
resource, connect, y dba.
Figura 19. Creación Usuario “Perno” & Asignación Permisos Indicados.
5. Cree un perfil, Cree el PROFILE, PRO_FUNCIONARIO.
Con las siguientes características:
Sessions_per_user 3
Connect_time 20 horas
Idle_time 15 minutos
Failed_login_attempts 3
Password_life_time 60 dias
Password_reuse_max 2
Password_lock_time 1 dia
14
Password_grace_time 8 dias
Figura 20. Creación Profile: PRO_FUNCIONARIO.
6. Cree los usuarios. (JPINZON,SAGUIRRE,PPEREZ)
JPINZON ---- ROL ADMINISTRADOR

SAGUIRRE --- ROL_FUNCIONARIO
PPEREZ --- ROL_AUDITOR
15
Figura 21. Creación de Usuarios Indicados.
Figura 22. Asignación de Roles a Usuarios Creados.

16
7. Construir los procedimientos almacenados para las siguientes necesidades.
A. Una función (FN_CALCULA_FPC) que calcule el 16% de Fondo de
Pensiones y Cesantias.
-- CREACION FUNCION FN_CALCULA_FPC

CREATE FUNCTION FN_CALCULA_FPC
(
@Sueldo DECIMAL(18,2)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
RETURN (@Sueldo*0.16)
END
GO
-- CONSUMO DE LA FUNCION FN_CALCULA_FPC
SELECT Nombre , Apellido,Salario,dbo.FN_CALCULA_FPC(Salario)AS
Descuento_FPC
FROM Empleados
Figura 23. Resultado de la creación y consumo de la función FN_CALCULA_FPC.

17
B. Una función (FN_CALCULA_FPC) que calcule el 16% de Fondo de
Pensiones y Cesantias.
--CREACION FUNCION FN_CALCULA_SENA

CREATE FUNCTION FN_CALCULA_SENA
(
@Sueldo DECIMAL(18,2)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
RETURN (@Sueldo*0.01)
END
GO
-- CONSUMO DE LA FUNCION FN_CALCULA_SENA
SELECT Nombre , Apellido,Salario,dbo.FN_CALCULA_SENA(Salario)AS
Descuento_SENA
FROM Empleados
Figura 24. Resultado de la creación y consumo de la función FN_CALCULA_SENA .

Actividad 3 - Segunda Parte Caso Practico

Cargado por

Copyright:

Formatos disponibles

Actividad 3 - Segunda Parte Caso Practico

Cargado por

Información del documento

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Actividad 3 - Segunda Parte Caso Practico

Cargado por

Copyright:

Formatos disponibles

Administración de Sistemas Gestores de Bases de Datos

Actividad 3: Segunda Parte: Caso Practico

Fundación Universitaria Compensar.

Josué Roa Pinzón y Iván Esteban Mateus Soche.

Segunda Parte: Caso Practico ......................................................................................................... 3

Segunda Parte: Caso Practico

vamos a agregar el módulo administrativo, donde tenemos la información de los

empleados y sus datos (Identificación, nombres, apellidos, teléfono, salario) y los

departamentos (Identificación, nombre)

Realizado el modelo lógico (modelo entidad relación y relacional) y el modelo físico

que se han determinado para la empresa en mención de acuerdo, al levantamiento de

1. Construir las consultas que corresponden a los siguientes requerimientos:

• Un listado de todos los empleados que pertenecen a los departamentos de

Figura 1. Resultado Listado de empleados departamentos de administración e IT.

• El reporte de la historia laboral de un empleado especifico.

SELECT b.*,c.nombre, c.apellido

Figura 2. Resultado Reporte Historial Laboral de un Empleado.

SELECT dept, SUM(salario) AS "Total Salario por Departamento"

Figura 3. Resultado Resumen total de pago por Departamento.

• Una consulta que muestre el promedio de los salarios, el menor salario, el

SELECT AVG(SALARIO)AS "Promedio Salario", max (Salario)as "Maximo Salario",

Figura 4. Resultado Promedio, Mínimo y Máximo de todos los Salarios.

• Pacientes atendidos en un rango de fecha determinado

Figura 5. Resultado Documentos de Pacientes Atendidos en un Rango de Fecha.

• Lista de médicos con sus especialidades

SELECT NOMBRE_MEDICO, APELLIDO_MEDICO, ESPECIALIDAD_MEDICO

Figura 6. Resultado Lista médicos y su Especialidades.

• Lista de pacientes que asisten a consulta con un motivo determinado

SELECT B.NOMBRE, B.APELLIDO , A.ESPECIALIDAD

Figura 7. Resultado Lista de Pacientes con Especialidad A Consultar.

imágenes diagnósticas y otro tipo de exámenes.

SELECT C.ID_PACIENTE, C.TIPO_DOCUMENTO, C.NOMBRE, C.APELLIDO,

Figura 8. Resultado Pacientes a los que se les asignan Ordenes medicas.

2. En el grupo de trabajo determinar 10 consultas donde se realicen

subconsultas, entre las tablas, definirlas y mostrar el resultado.

• Nombre y apellido de los pacientes con ordenes médicas y Tipo Examen.

SELECT C.NOMBRE , C.APELLIDO, B.TIPO_EXAMEN

Figura 9. Resultado Consulta Nombres pacientes con Tipo de Examen.

• Pacientes con Orden Medica que viven en la localidad 18.

SELECT C.NOMBRE , C.APELLIDO, C.LOCALIDAD,A.DIAGNOSTICO

• Pacientes con Orden Medica y que tienen Tipo de Documento CC-Cedula.

SELECT C. NOMBRE , C.APELLIDO, C.LOCALIDAD

Figura 11. Resultado Consulta Pacientes con tipo de documento Cedula.

• Pacientes con Orden Medica que viven en Barrio Chapinero.

SELECT C. NOMBRE, C.APELLIDO, C.BARRIO

Figura 12. Resultado Consulta Pacientes que residen en Barrio Chapinero.

• Pacientes con Orden Medica con Residencia Diferente a Bogotá.

SELECT C. NOMBRE, C.APELLIDO, C.DEPARTAMENTO

Figura 13. Resultado Consulta Pacientes Con Residencia diferentes a BOG.

• Pacientes con Teléfono Finalizado en 1.

SELECT NOMBRE, APELLIDO, TELEFONO

Figura 14. Resultado Consulta Pacientes con teléfono de Contacto Terminado en 1.

• Pacientes con Orden Medica Registrados bajo el Tipo afiliación POS.

SELECT C. NOMBRE, C.APELLIDO, C.TIPO_AFILIACION

Figura 15. Resultado Consulta Pacientes Con tipo afiliación POS.

SELECT C. NOMBRE, C.APELLIDO, C.EMAIL

• Pacientes con Orden Medica solicitada a la hora 13:00(01:00 pm).

SELECT C. NOMBRE, C.APELLIDO, CONVERT(DATE,

Figura 17. Resultado Consulta Orden medica solicitada a la hora 13:00.

3. Definir y crear trigger de importancia para el manejo de la base de datos.