Acceso a datos. Oracle Database. Ejercicios

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

                                                                              )

);