Tema+9.+Consultas+SQL+Universidad

UNIVERSIDAD CARLOS I Se desea tener una base de datos con la siguiente información acerca de los investigadores y los equipos que reservan: • Información acerca de los investigadores, que constará de su nombre y apellidos, un código único para cada investigador, su dirección, fecha de nacimiento y sexo (‘H’ o ‘M’). • Estos investigadores estarán adscritos a una sola facultad. Cada facultad tendrá un nombre, un código único que lo identifica. • Los equipos de una facultad, de los que almacenaré su código y su nombre. • Las reservas de los equipos se van registrando, con la fecha de inicio y fin.

1. Generar las siguientes tablas para guardar esta información Facultad (IdFacultad, NomFacultad) Investigador (DNI, NomInvestigador, ApellInvestigador, IdFacultad, FechaNacimiento) Equipo (IdEquipo, IdFacultad, Descripción) Reserva (IdReserva, DNI, IdEquipo, FechaInicio, FechaFin

(Identificar las relaciones entre las tablas)

En las definiciones establecer las siguientes restricciones: • No es posible dar de alta un investigador si no está adscrito a una facultad. • La información de la fecha de nacimiento de un investigador es imprescindible para almacenarlo. • Dos facultades no pueden llamarse igual. Lo mismo le pasa a los investigadores. • Cumplir la relación normal entre fecha comienzo y fecha fin (orden cronológico). • Se ha de mantener la regla de integridad de referencia y definir las acciones referenciales.

2. Insertar las siguientes tuplas: ( Crear un script único para introducir todos los datos ) Tabla Facultad

IdFacultad NomFacultad 1 Ciencias Exactas 2 Ciencias Naturales 3 Ciencia y Tecnología4 Bioquímica y Ciencias Biológicas

Tabla Investigador DNI NomInvestigador ApellInvstigador Edad IdFacultad 38486831X ESTEFANIA LÓPEZ DE PABLO GARCIA UCEDA 45 3 56234233K PAULA ANGUERA VILAFRANCA 34 1 23435343P JUAN BASTARDES SOTO 53 2 X3543098R RAQUEL RAYA GAVILAN 58 3 32544333I EMILIO BIDAULT CULLERÉS 39 4 37879998D LUIS VISO GILABERT 48 4

Tabla Equipo

IdEquipo IdFacultad Descripción H503 1 Telemetro laser SICK H235 1 Multimetro digital FLUKE M342 4 Fuente de Voltaje TEKTRONIX M234 3 __[|Cámara digital]__ SONY P340 1 Lente para camara FUJINON-TV

Tabla Reserva

IdReserva DNI IdEquipo FechaInicio FechaFin 1 37879998D H235 09/09/2005 23/09/2005 2 38486831X H324 09/09/2005 14/09/2005 3 32544333I C342 13/09/2005 21/09/2005 4 38486831X P340 16/09/2005 01/10/2005 5 56234233K H324 20/09/2005 25/09/2005 6 38486831X M234 16/10/2005 25/10/2005

3. Añadir el campo sexo en la tabla Investigador, además añade la restricción para que el atributo solo sea rellenado con M ó H (en mayúsculas).

4. Añadir las siguientes restricciones, teniendo en cuenta que deben crearse sin modificar los datos almacenados en las tablas: • No se puede añadir un curso si su número de alumnos máximo es menor que 10. • El número de horas de los __[|cursos]__ debe ser mayor que 100. • Inserte restricción no nula en el campo FECHA_INICIO de Reserva. • La edad de los investigadores está entre 24 y 65 años.

5. Actualizar datos en tuplas • La fecha de inicio de la reserva “2” está equivocada. La verdadera es 10 de septiembre de 2005. • Cambie la edad de EMILIO BIDAULT CULLERÉSa 24.

6. Eliminar tuplas • Elimine los investigadores cuyo primer apellido empiece por la letra 'V'.

7.Tablas temporales

Cree una tabla (de uso temporal) llamada Nombre_de_Investigador que tenga un solo atributo (Nombre_Completo) de tipo cadena de caracteres y con el contenido de la tabla Investigador en esos campos. Observe que no hay restricción de clave primaria para esta tabla. Inserte una fila en la tabla Investigador, y observe si se modifica la nueva tabla. Compruebe la diferencia entre una tabla normal y una temporal.

8.Crear vistas Para que la tabla siempre esté actualizada, borre dicha tabla y cree una vista con esa información.

9.Más vistas. • Nombre de la Facultad y nombre de los investigadores adscritos, así como la edad. • Número de equipos reservados por facultad. Como verá, los equipos están asignados a una facultad, al igual que los investigadores. Esta vista debe reflejar el número de equipos de la facultad reservados a sus investigadores en una columna y en otra columna el número de equipos reservados a investigadores de otras facultades. • Investigadores que han reservado más equipos de las facultades que no son suyas que de la que está adscritos. • Investigadores que son menores que la edad media de la de los investigadores de su facultad, clasificados por facultad.

Ver solución