Tema8_Ejercicio5

=Tema 8=


 * ESQUEMA E-R**

Una compañía aseguradora de tipo sanitario desea diseñar una BD para informatizar parte de su gestión hospitalaria. En una primera fase sólo quiere contemplar los siguientes supuestos semánticos: Los hospitales de su red pueden ser propios o concertados; además de unos datos comunes a todos ellos como son el código de hospital (Cod_H), su nombre (N_H), su número de camas (Num_C), etc., cuando el hospital es propio se tienen otros específicos como el presupuesto (P), tipo de servicio (TS), etc. Una póliza, que se identifica por un número de póliza (Cod_P), tiene varios atributos que, en principio, no interesa especificar y que se agrupan bajo el nombre de datos de póliza (Datos_P). Una póliza cubre a varios asegurados, los cuales se identifican por un número correlativo (Num), añadido al código de la póliza, y tienen un nombre (NA), fecha de nacimiento (FN), etc. Los asegurados cubiertos por una misma póliza pueden ser de distintas categorías. Mientras los asegurados de primera categoría (A1C) pueden ser hospitalizados en cualquier hospital, los de segunda categoría sólo pueden ser hospitalizados en hospitales propios. Aunque las otras categorías no tienen derecho a ser hospitalizados, en la BD se guardan todos los sea cual sea su categoría. Interesa saber en qué hospitales han estado (o están) hospitalizados los asegurados, el médico que prescribió la, así como las fechas de inicio y de fin de la misma. Existen áreas, identificadas por un código y con datos sobre su superficie, número de habitantes, etc. Los hospitales concertados tienen que estar asignados a una única área, que no puede cambiar, mientras que los propios no están asignados a áreas. Los médicos, que se identifican por un código, tienen un nombre, teléfonos de contacto, etc. Interesa conocer las áreas a las que está adscrito un médico. Existe una dependencia jerárquica entre médicos de forma que un médico tiene un único jefe.


 * ESQUEMA RELACIONAL**
 * CONTENIDO DE LAS TABLAS**
 * Hospitales.




 * Propio.




 * Concertado.




 * Area.




 * Medico.




 * Area_medico.




 * Poliza.




 * Asegurado.




 * A1c.




 * A2c.




 * Hospitalizaciona.




 * Hospitalizacionb.




 * CONSULTAS**

** 1. ****Obtener los asegurados que tienen de medico a Ricardo.** Select * from asegurado where exists (Select numero from hospitalizaciona where hospitalizaciona.numero = asegurado.numero and exists (Select cod_medico from medico where hospitalizaciona.cod_medico = medico.cod_medico and medico.nombre = ‘Ricardo’)) or exists (Select numero from hospitalizacionb where hospitalizacionb.numero = asegurado.numero and exists (Select cod_medico from medico where hospitalizacionb.cod_medico = medico.cod_medico and medico.nombre = ‘Ricardo’)); Resultado:

** 2. ****Obtener los asegurados que su médico esta en el área 5.**

Select * from asegurado where exists (select numero from hospitalizaciona where hospitalizaciona.numero = asegurado.numero and exists (select cod_medico from medico where hospitalizaciona.cod_medico = medico.cod_medico and exists (select cod_medico from area_medico where area_medico.cod_medico = medico.cod_medico and exists (Select cod_area from area where area_medico.cod_area = area.cod_area and area.cod_area = 5)))) or exists (select numero from hospitalizacionb where hospitalizacionb.numero = asegurado.numero and exists (select cod_medico from medico where hospitalizacionb.cod_medico = medico.cod_medico and exists (select cod_medico from area_medico where area_medico.cod_medico = medico.cod_medico and exists (Select cod_area from area where area_medico.cod_area = area.cod_area and area.cod_area = 5))));

Resultado:

** 3. ****Obtener los asegurados del médico Felipe que son de primera categoría cuyo nombre es Lucia y ordenarlos por su número de póliza.** Select * from asegurado where nombre = ‘Lucia’ and exists (Select numero from hospitalizaciona where hospitalizaciona.numero = asegurado.numero and exists (Select cod_medico from medico where hospitalizaciona.cod_medico = medico.cod_medico and medico.nombre = ‘Felipe’)) order by cod_poliza; Resultado:

** 4. ****Obtener el código de los médicos que trabajan en algunas de las áreas, que trabaja el médico cuyo código es 2.** Select distinct cod_medico from area_medico where cod_area in (Select cod_area from area_medico where cod_medico = 2); Resultado:

 ** 5. ****Obtener asegurados que ingresaron el 1/2/2005 y ordenarlas por su nombre.** Select * from asegurado where exists (select numero from hospitalizaciona where hospitalizaciona.numero = asegurado.numero and hospitalizaciona.fecha_inicio = ‘1-2-2005’) or exists (select numero from hospitalizacionb where hospitalizacionb.numero = asegurado.numero and hospitalizacionb.fecha_inicio = ‘1-2-2005’) order by nombre; Resultado:

 Select * from medico where exists (Select cod_medico from area_medico where area_medico.cod_medico = medico.cod_medico and exists (Select cod_area from area where area_medico.cod_area = area.cod_area and area.cod_area = 10)); Resultado:
 * 6. Obtener los médicos que están en hospitales concertados y que trabajan en el área 10.**

** 7. ****Obtener los médicos que trabajan en todas las áreas.** Select cod_medico from area_medico group by cod_medico having count(*) = (Select count(*) from area); Resultado: ** 8. ****Obtener el nombre de los médicos que tienen asegurados de segunda categoría y cuyo hospital se llama Manacor y ordenar por su código de médico.** Select nombre from medico where exists (Select cod_medico from hospitalizacionb where hospitalizacionb.cod_medico = medico.cod_medico and exists (Select cod_hospital from hospitales where hospitalizacionb.cod_hospital = hospitales.cod_hospital and hospitales.nombre = ‘Manacor’)) order by cod_medico; <span style="color: #ff0000; display: block; line-height: 150%; margin-bottom: 0.0001pt; text-align: justify;">Resultado: <span style="display: block; line-height: 150%; margin: 0cm 0cm 0.0001pt 17.85pt; text-align: justify; text-indent: 0cm;">** 9. ****Eliminar los asegurados que cuyo nombre sea Rosa y médico se llame Alberto.** Delete from asegurado where nombre = ‘Rosa’ and exists (Select nombre from medico where nombre = ‘Alberto’); <span style="color: #ff0000; display: block; line-height: 150%; margin-bottom: 0.0001pt; text-align: justify;">Resultado:

<span style="color: #ff0000; display: block; line-height: 150%; margin-bottom: 0.0001pt; text-align: justify;"> <span style="display: block; line-height: 150%; margin: 0cm 0cm 0.0001pt 17.85pt; text-align: justify; text-indent: 0cm;">** 10. ****Obtener hospitales cuyo presupuesto sea mayor que la media.** Select * from propio where presupuesto > (select avg(presupuesto) from propio);

Resultado: