No es un bug, es una característica no documentada

jueves, 25 de febrero de 2016

Acceso a datos. Oracle Database. Ejercicios

15:34 Posted by Inazio Claver , No comments
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