Práctica 2. Universidad Alfonso VI
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 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).
2.
Insertar las siguientes tuplas:
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.
5.
Eliminar las restricciones siguientes.
• que controla los valores permitidos para el atributo sexo. • NOT NULL del atributo Edad.
6.
Modificar las siguientes restricciones
• Cambie la clave primaria de Investigador al nombre y apellidos (dejarlo luego como estaba)
7.
Insertar las siguientes tuplas
Dadas
Tabla Investigador
DNI NomInvestigador ApellInvstigador Edad IdFacultad
75609277K PILAR VILLA ANTEQUERA 54 3 35647365X ANTONIO JÉREZ PARRA 41 4
Tabla Equipo
IdEquipo IdFacultad Descripción
H505 2 Equipo de revelado Shneider H215 3 Analizador de Partículas
Mastersizer 2000
Inventadas
Inserte tuplas en las tablas:
Tabla Investigador Al menos 3 tuplas Tabla Equipo Al menos 3 tuplas Tabla Facultad Al menos 1 tupla Tabla Reserva
Al menos todos los equipos están reservados por alguien
Las tuplas deben cumplir que
o Hay por lo menos 1 equipo reservado 2 veces
o Inserte tupla que no cumplan las restricciones impuestas; 1 por cada
8.
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ÉS a 24.
9.
Eliminar tuplas
• Elimine los investigadores cuyo primer apellido empiece por la letra 'V'.
10.
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.
11.
Crear vistas
Para que la tabla siempre esté actualizada, borre dicha tabla y cree una vista con esa información.
12.
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.
13.
Listados en SQL*PLUS
Realice listados en SQL*PLUS para las vistas 1 y 2 del ejercicio anterior.
Vista1.html
Investigadores por facultad =====================
Facultad Investigador Edad ... ... ... ... ... Edad media: ... ... ... ... Edad media: ...
Realiza la salida por página web en Vista1.html
Vista2.html
Reservas por facultad =====================
Facultad Reservas1 Reservas2 ... ... ... ... ... Total reservas ... ... ... ... Total reservas ...
Realiza la salida por página web en Vista2.html
