1. Definir
un tipo profesor con la siguiente estructura
-- Tipo Direccion
create or replace type T_DIRECCION as object(
calle
varchar2(15),
numero
varchar2(20),
ciudad
varchar2(10),
codigo_postal varchar2(5)
);
-- Tipo Telefono
create or replace type T_TELEFONO as varray(5)
of varchar2(9);
-- Tipo Profesor
create or replace type T_PROFESOR as object(
nombre
varchar2(20),
direccion T_DIRECCION,
salario
number,
telefono T_TELEFONO
) not final;
2. Definir dos extensiones para el tipo
profesor que cumplan las siguientes restricciones:
-- Tipo Profesor contratado
create or replace type T_CONTRATADO under
T_PROFESOR();
-- Tipo Profesor titular
create or replace type T_TITULAR under
T_PROFESOR();
-- Tabla Profesor
create table PROFESOR of T_PROFESOR(
primary key(nombre)
);
-- Tabla Profesor contratado
create table PROFESOR_CONTRATADO of
T_CONTRATADO(
primary key(nombre),
check(salario <= 166386)
);
-- Tabla Profesor titular
create table PROFESOR_TITULAR of T_TITULAR(
primary key(nombre),
check(salario > 166386),
check(direccion is not null)
);
3. Insertar los siguientes datos
Donde los valores vacíos representan un
valor nulo en el campo teléfono (no en un o cinco de los teléfonos)
--
Inserciones de profesores titulares
insert
into PROFESOR_TITULAR values('Jose Mª', T_DIRECCION('Alcalá', '3', 'Madrid',
'28020'), 200000, T_TELEFONO('6647401', '4556478', '606754321', null,
'914445556'));
insert
into PROFESOR_TITULAR values('Jorge', T_DIRECCION('Butarque', '15', 'Leganés',
'28911'), 250000, T_TELEFONO('6647401', '4557486', null, '964321236', null));
insert
into PROFESOR_TITULAR values('Belen', T_DIRECCION(null, null, null, null),
200000, T_TELEFONO('6647402', null, '606896310',null, null));
insert
into PROFESOR_TITULAR values('Esperanza', T_DIRECCION('Serrano', '56',
'Madrid', '28010'), 250000, T_TELEFONO('6647403', '4557486', '606312890',
'987348675', null));
insert
into PROFESOR_TITULAR(nombre, direccion, salario) values('Paloma',
T_DIRECCION('Tulipan', '10', 'Mostoles', '28933'), 300000);
--
Inserciones de profesores contratados
insert
into PROFESOR_CONTRATADO values('Pepe', T_DIRECCION('Gran via', '8', 'Madrid',
'28009'), 150000, T_TELEFONO('6647405', '4676478', '606757651', '964398736',
'914481096'));
insert
into PROFESOR_CONTRATADO values('Susana', T_DIRECCION(null, null, 'Leganes',
null), 150000, T_TELEFONO('6647405', '4554586', null, '934876823', null));
insert
into PROFESOR_CONTRATADO values('Ana', T_DIRECCION(null, null, 'Getafe', null),
100000, T_TELEFONO('6647405', '4490634', '606856670', null, null));
insert
into PROFESOR_CONTRATADO values('Juan', T_DIRECCION('Velazquez', '88',
'Madrid', '28010'), 110000, T_TELEFONO('6647406', null, null, '987348675',
null));
insert
into PROFESOR_CONTRATADO(nombre, salario, telefono) values('Maria', 145000,
T_TELEFONO(null, null, null, null, null));
4. Consultar la totalidad de las dos tablas
select * from PROFESOR_CONTRATADO,
PROFESOR_TITULAR;
5. Contesta a las siguientes consultas:
à
Nombre y
ciudad de los profesores contratados con un salario superior a 100000 euros
à
Nombre y
teléfonos de los profesores titulares
à
Nombre y
dirección de los profesores titulares cuyo salario sea mayor de 200000 euros.
à
Nombre y
salario de los profesores, contratados o titulares, que vivan en Madrid.
-- Primera
select pc.nombre, pc.direccion.ciudad from
PROFESOR_CONTRATADO pc where salario > 100000;
-- Segunda
select pc.nombre, pc.telefono from
PROFESOR_TITULAR pc;
-- Tercera
select pc.nombre, pc.direccion from
PROFESOR_TITULAR pc where salario > 200000;
-- Cuarta
select pc.nombre, pc.salario from PROFESOR_TITULAR
pc where pc.direccion.ciudad = 'Madrid'
union
select pc.nombre, pc.salario from
PROFESOR_CONTRATADO pc where pc.direccion.ciudad = 'Madrid';
6. Borra las extensiones y tipos de objetos
definidos previamente
drop table
PROFESOR_TITULAR;
drop table PROFESOR_CONTRATADO;
drop table PROFESOR;
drop type T_TITULAR;
drop type
T_CONTRATADO;
drop type
T_PROFESOR;
drop type
T_TELEFONO;
drop type
T_DIRECCION;
7. Modificar el
esquema para incluir un atributo Fecha_nacimiento
en los profesores, así como un método, Edad(),
que permita calcular su edad a partir de la fecha del sistema.
-- Tipo Direccion
create or replace
type T_DIRECCION as object(
calle varchar2(15),
numero varchar2(20),
ciudad varchar2(10),
codigo_postal varchar2(5)
);
-- Tipo Telefono
create or replace
type T_TELEFONO as varray(5) of varchar2(9);
-- Tipo PROFESOR
create or replace
type T_PROFESOR as object(
nombre varchar2(20),
fecha_nacimiento date,
direccion T_DIRECCION,
salario number,
telefono T_TELEFONO,
member function edad return number
) not final;
create or replace
type body T_PROFESOR as
member function edad
return number is
ed number;
begin
ed:= to_char(SYSDATE, 'YYYY') -
to_char(fecha_nacimiento, 'YYYY');
return ed;
end;
end;
8. Repetir los pasos
2 y 3, incluyendo los valores de fecha de nacimiento.
-- Creo tipos
heredados
create or replace
type T_CONTRATADO under T_PROFESOR();
create or replace
type T_TITULAR under T_PROFESOR();
-- Creo tablas
create table
PROFESOR of T_PROFESOR(
primary key(nombre)
);
create table
PROFESOR_CONTRATADO of T_CONTRATADO(
primary key(nombre),
check(salario <= 166386)
);
create table
PROFESOR_TITULAR of T_TITULAR(
primary key(nombre),
check(salario > 166386),
check(direccion is not null)
);
-- Inserciones en
profesores titulares
insert into
PROFESOR_TITULAR (NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('José Mª',
'03/11/1966', T_DIRECCION('Alcalá', '3', 'Madrid', '28020'), 200000,
T_TELEFONO('6647401', '4556478','606754321', NULL, '914445556'));
insert into
PROFESOR_TITULAR (NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('Jorge',
'12/03/1962', T_DIRECCION('Butarque', '15', 'Leganés', '28911'), 250000,
T_TELEFONO('6647401', '4557486',NULL,'964321236', NULL));
insert into PROFESOR_TITULAR
(NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('Belén','07/10/1964',
T_DIRECCION(NULL, NULL, NULL, NULL), 200000, T_TELEFONO('6647402',
NULL,'606896310',NULL, NULL));
insert into
PROFESOR_TITULAR (NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('Esperanza','24/01/1972',
T_DIRECCION('Serrano', '56', 'Madrid', '28010'), 250000, T_TELEFONO('6647403',
'4457834','606312890','987348675', NULL));
insert into
PROFESOR_TITULAR (NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO)
values('Paloma',
'02/07/1970', T_DIRECCION('Tulipán', '10', 'Móstoles', '28933'), 300000);
-- Inserciones en
profesores contratados
insert into
PROFESOR_CONTRATADO (NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('Pepe',
'03/12/1960', T_DIRECCION('Gran Vía', '8', 'Madrid', '28009'), 150000,
T_TELEFONO('6647405', '4676478','606757651', '964398736', '914481096'));
insert into
PROFESOR_CONTRATADO (NOMBRE,
FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('Susana',
'12/11/1964', T_DIRECCION(Null, Null, 'Leganés', Null), 150000,
T_TELEFONO('6647405', '4554586',Null, '934876823', Null));
insert into
PROFESOR_CONTRATADO (NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('Ana',
'04/02/1955', T_DIRECCION(Null, Null, 'Getafe', Null), 100000,
T_TELEFONO('6647405', '4490634', '606856670', Null, '914445576'));
insert into
PROFESOR_CONTRATADO (NOMBRE, FECHA_NACIMIENTO, DIRECCION, SALARIO, TELEFONO)
values('Juan',
'24/10/1963', T_DIRECCION('Velázquez', '88', 'Madrid', '28010'), 110000,
T_TELEFONO('6647406', Null, Null, '987348675', Null));
insert into
PROFESOR_CONTRATADO (NOMBRE, FECHA_NACIMIENTO, SALARIO, TELEFONO)
values('María',
'30/06/1965', 145000, T_TELEFONO(Null, Null, Null, Null, Null));
9. Muestra el nombre
y edad de los profesores de Madrid
select pt.nombre,
pt.edad()
from
PROFESOR_TITULAR pt
where
pt.direccion.ciudad = 'Madrid'
union
select pc.nombre,
pc.edad()
from
PROFESOR_CONTRATADO pc
where
pc.direccion.ciudad = 'Madrid';
10. Borra las
extensiones y tipos de objetos definidos previamente
drop table PROFESOR_TITULAR;
drop table PROFESOR_CONTRATADO;
drop table PROFESOR;
drop type T_TITULAR;
drop type T_CONTRATADO;
drop type T_PROFESOR;
drop type T_TELEFONO;
drop type T_DIRECCION;
11. Definir un tipo de objeto Profesor, con la siguiente estructura:
-- Tipo Direccion
create or replace type T_DIRECCION as object(
calle
varchar2(15),
numero
varchar2(20),
ciudad
varchar2(10),
codigo_postal varchar2(5)
);
-- Tipo Teléfono
create or replace type T_TELEFONO as varray(5)
of VARCHAR2(9);
-- Tipo profesor
create or replace type T_PROFESOR as object(
nombre
varchar2(20),
direccion T_DIRECCION,
salario
number,
telefono T_TELEFONO
) not final;
-- Tabla profesor
create table PROFESOR of T_PROFESOR(
primary
key (nombre)
);
12. Definir una extensión para el tipo
Profesor
-- Tipo profesor titular heredado de profesor
create or replace type T_TITULAR under
T_PROFESOR();
-- Tabla profesor titular
create table PROFESOR_TITULAR of T_TITULAR(
primary key (nombre),
check
(salario > 166386),
check
(direccion is not null)
);
13. Definir un tipo de objeto, y una
extensión asociada, Asignatura, con
la siguiente estructura.
-- Tipo Asignatura
create or replace type T_ASIGNATURA as
object(
nombre varchar2(20),
curso
varchar2(1),
titulacion varchar2(16),
num_creditos number,
prof
ref T_PROFESOR
);
-- Tabla Asignatura
create table ASIGNATURA of T_ASIGNATURA;
14. Insertar los siguientes datos,
suponiendo que el OID asignado por el sistema es el que aparece en las columnas
REF (Titulares) y REF(Asignatura); lógicamente, estas columnas no deberán
declararse, ni se podrán insertar datos en ellas.
Los valores de la columna Profesor deberán
extraerse de la tabla Profesores.
-- Inserciones en la tabla profesores titulares
insert into PROFESOR_TITULAR values('Jose Mª',
T_DIRECCION('Alcalá', '3', 'Madrid', '28020'), 200000, T_TELEFONO('6647401',
'4556478', '6067', null, '914445556'));
insert into PROFESOR_TITULAR values('Jorge',
T_DIRECCION('Butarque', '3', 'Leganés', '28911'), 250000, T_TELEFONO('6647401',
'4557486', null, '964321236', null));
insert into PROFESOR_TITULAR values('Belén',
T_DIRECCION(null, null, null, null), 200000, T_TELEFONO('6647402', '4457834',
'6063', '987348675', null));
insert into PROFESOR_TITULAR
values('Esperanza', T_DIRECCION('Serrano', '56', 'Madrid', '28010'), 250000,
T_TELEFONO('6647403', '4457834', '6063', '987348675', null));
insert into PROFESOR_TITULAR(nombre, direccion,
salario) values('Paloma', T_DIRECCION('Tulipán', '10', 'Móstoles', '28933'),
300000);
-- Inserciones en la tabla asignaturas
insert into ASIGNATURA values('Diseño de BD',
'3', 'I.T.Informática', 9, (select ref(p) from PROFESOR_TITULAR p where
p.nombre = 'Esperanza'));
insert into ASIGNATURA values('Bases de datos',
'1', 'I.Informática', 6, (select ref(p) from PROFESOR_TITULAR p where p.nombre
like 'Esperanza'));
insert into ASIGNATURA values('Aplicaciones de
BD', '2', 'I.Informática', 6, (select ref(p) from PROFESOR_TITULAR p where
p.nombre like 'Jose Mª'));
insert into ASIGNATURA values('BD Avanzadas',
'3', 'I.T.Informática', 6, (select ref(p) from PROFESOR_TITULAR p where
p.nombre like 'Belén'));
insert into ASIGNATURA values('Arquitectura
Softw', '2', 'I.Informática', 6, (select ref(p) from PROFESOR_TITULAR p where
p.nombre like 'Jorge'));
15. Comprobar el contenido de cada una de
las tablas, mediante dos consultas a la totalidad de datos de las mismas.
-- Consultas realizadas sin mostrar los datos
referenciados a otro objeto
select * from PROFESOR_TITULAR;
select * from ASIGNATURA;
16. Insertar una nueva fila en la tabla
asignaturas que contenga exactamente los mismos valores que la asignatura de
Diseño de BD. ¿Es posible? Consultar el OID de los dos objetos cuyo nombre es
‘Diseño de BD’. El OID, ¿es igual? ¿Son dos objetos idénticos? ¿Tienen los
objetos el mismo estado?
/* VALORES ANTES DE INSERCION
Diseño
de BD 3 I.T.Informática 9 [SYSTEM.T_TITULAR]
Bases
de datos 1 I.Informática 6 [SYSTEM.T_TITULAR]
Aplicaciones de BD 2 I.Informática 6 [SYSTEM.T_TITULAR]
BD
Avanzadas 3 I.T.Informática 6 [SYSTEM.T_TITULAR]
Arquitectura Softw 2 I.Informática 6 [SYSTEM.T_TITULAR]
*/
insert into ASIGNATURA values('Diseño de BD',
'3', 'I.T.Informática', 9, (select ref(p) from PROFESOR_TITULAR p where
p.nombre = 'Esperanza'));
/* VALORES DESPUES DE INSERCION
Diseño
de BD 3 I.T.Informática 9 [SYSTEM.T_TITULAR]
Bases
de datos 1 I.Informática 6 [SYSTEM.T_TITULAR]
Aplicaciones de BD 2 I.Informática 6 [SYSTEM.T_TITULAR]
BD Avanzadas 3 I.T.Informática 6 [SYSTEM.T_TITULAR]
Arquitectura Softw 2 I.Informática 6 [SYSTEM.T_TITULAR]
Diseño
de BD 3 I.T.Informática 9 [SYSTEM.T_TITULAR]
*/
-- Sí permite la inserción
17. Eliminar, de la tabla de Profesores, a
los profesores que viven en Leganés.
delete PROFESOR_TITULAR p where
p.direccion.ciudad = 'Leganés';
18. Comprobar nuevamente el contenido de
cada una de las tablas. ¿Qué ha pasado con las asignaturas impartidas por los
profesores eliminados?
select * from PROFESOR_TITULAR;
select * from ASIGNATURA;
-- Tiene una referencia
oracle.sql.REF@f3544ac1, lo muestra vacío
19. Contesta a las siguientes consultas:
à
Nombre y
teléfonos del profesor que imparte la asignatura de Bases de Datos.
à
Nombre de
los profesores que imparten clases en la titulación de I.T.Informática.
-- Nombre y telefonos de profesores de Bases
de datos
select asig.prof.nombre, asig.prof.telefono
from asignatura asig
where asig.nombre = 'Bases de datos';
-- Nombre profesores I.T. Informática
select distinct asig.prof.nombre
from asignatura asig
where asig.titulacion = 'I.T.Informática';
20. Ahora, suponga que una asignatura la
pueden impartir hasta un máximo de 3 profesores. Elimine los tipos y tablas
necesarios y comience de nuevo. Modifique el esquema para recoger esta
modificación sin incluir ninguna tabla nueva y repita, con esta modificación,
los pasos 1, 2 y 3.
-- Borro información que debo construir de
nuevo
drop table ASIGNATURA;
drop type T_ASIGNATURA;
-- Creo VARRAY de profesores
create or replace type T_CONJUNTO_PROFESORES
as varray(3) of ref T_TITULAR;
-- Creo el nuevo tipo asignatura
create or replace type T_ASIGNATURA as
object(
nombre varchar2(20),
curso
varchar2(1),
titulacion varchar2(16),
num_creditos number,
prof
T_CONJUNTO_PROFESORES
);
-- Creo de nuevo tabla asignatura
create table ASIGNATURA of T_ASIGNATURA;
21. Repita el paso 4, modificando los datos
de modo que las asignaturas las impartan los siguientes profesores.
-- Diseño de BD
insert into ASIGNATURA values('Diseño de BD',
'3', 'I.T.Informática', 9, T_CONJUNTO_PROFESORES(
(select ref(p) from PROFESOR_TITULAR p where p.nombre = 'Esperanza'),
(select ref(p) from PROFESOR_TITULAR p where p.nombre = 'Jose Mª'),
(select ref(p) from PROFESOR_TITULAR p where p.nombre = 'Belén')
)
);
-- Bases de datos
insert into ASIGNATURA values('Bases de
datos', '1', 'I.Informática', 6, T_CONJUNTO_PROFESORES(
(select ref(p) from PROFESOR_TITULAR p where p.nombre like 'Esperanza'),
(select ref(p) from PROFESOR_TITULAR p where p.nombre like 'Belén'),
null
)
);
-- Aplicaciones de BD
insert into ASIGNATURA values('Aplicaciones
de BD', '2', 'I.Informática', 6, T_CONJUNTO_PROFESORES(
(select ref(p) from
PROFESOR_TITULAR p where p.nombre like 'Jose Mª'),
(select ref(p) from PROFESOR_TITULAR p where p.nombre like 'Esperanza'),
null
)
);
-- BD Avanzadas
insert into ASIGNATURA values('BD Avanzadas',
'3', 'I.T.Informática', 6, T_CONJUNTO_PROFESORES(
(select ref(p) from PROFESOR_TITULAR p where p.nombre like 'Belén'),
(select ref(p) from PROFESOR_TITULAR p where p.nombre like 'Jose Mª'),
null
)
);
-- Arquitectura Softw
insert into ASIGNATURA values('Arquitectura
Softw', '2', 'I.Informática', 6, T_CONJUNTO_PROFESORES(
(select ref(p) from PROFESOR_TITULAR p where p.nombre like 'Jorge'),
(select
ref(p) from PROFESOR_TITULAR p where p.nombre like 'Paloma'),
null
)
);
0 comentarios:
Publicar un comentario