AUTOR: Fco Manuel Martín Carrión
ESPECIALIDAD: I.T.I.Gestión

-------------------------------------------------------------------------------
Ejercicio 1:
-------------------------------------------------------------------------------

a) Relación General ==> R (A,B,C,D,E,F,G,H,I) con PK(A,B,D,H,I)

Descomposición ==> Todas las relaciones en 5 Forma Normal.
                R1 (A,B,C,E,F)   PK(A,B), E es unique y check F > 0
                R2 (C,G)   PK(C)
                R3(D,H)    PK(D,H)
                R4(D,I)    PK(D,I)
                R5(A,B,D)  PK(A,B,D)
                R6(D)      PK(D) y check D > 0

Creación de Tablas ==>

CREATE TABLE R1(
  A NUMBER(2),
  B NUMBER(2),
  C NUMBER(2),
  E NUMBER(2),
  F NUMBER(2),
  CONSTRAINT pk_r1 PRIMARY KEY(A,B),
  CONSTRAINT unico_e_r1 UNIQUE(E),
  CONSTRAINT fk_r1_c FOREIGN KEY ( C) REFERENCES R2,
  CONSTRAINT r1_f_no_neg CHECK (F>0)
);

CREATE TABLE R2(
  C NUMBER(2) CONSTRAINT pk_r2 PRIMARY KEY,
  G NUMBER(2));

CREATE TABLE R3(
  D NUMBER(2),
  H NUMBER(2),
  CONSTRAINT pk_r3 PRIMARY KEY (D,H),
  CONSTRAINT fk_d_r3 FOREIGN KEY (D) REFERENCES R6,
);

CREATE TABLE R4(
  D NUMBER(2),
  I  NUMBER(2),
  CONSTRAINT pk_r4 PRIMARY KEY(D,I),
  CONSTRAINT fk_d_r4 FOREIGN KEY (D) REFERENCES R6,
);

CREATE TABLE R5 (
  A NUMBER(2),
  B NUMBER(2),
  D NUMBER(2),
  CONSTRAINT pk_r5 PRIMARY KEY (A,B,D),
  CONSTRAINT fk_d_r5 FOREIGN KEY (D) REFERENCES R6,
  CONSTRAINT fk_ab_r5 FOREIGN KEY (A,B) REFERENCES R1);

CREATE TABLE R6(
  D NUMBER(2) PRIMARY KEY,
  CONSTRAINT r6_d_no_nega CHECK (D > 0)
);

Orden de Creación ==> R2, R1, R6, R3, R4, R5.


b) Añado un atributo a R6:

Alter table R6 ADD (J NUMBER(2));

Creación del trigger para que se cumplan las restricción que pide el enunciado:

CREATE OR REPLACE TRIGGER apartado2
BEFORE INSERT OR UPDATE ON R6 FOR EACH ROW
DECLARE
  num_h  Number;
BEGIN
  SELECT count(*) into num_h FROM R3
  WHERE D = :new.D;

  If (num_h > 6) and (:new.J <= 5) then
      Raise_application_error(-20100,'ERROR_APARTADO2');
  End if;
END apartado2;


c) Creación del usuario asken (usuario system):
         create user asken identified by asken;
         grant connect to asken;

i) Creación de la vista que le permitirá ver lo que pide el enunciado (usuario gestor):
         create or replace view vista_1 as(
             SELECT R1.E, R2.G
             FROM R1, R2
             WHERE (R1.C = R2.C) and (R1.F > 99)
         );

Concesión de permisos al usuario asken mediante el usuario gestor:
         grant SELECT on vista_1 to asken;

ii) Creación de la vista materializada:
        CREATE MATERIALIZED VIEW VISTA_2 AS(
         SELECT R1.A, R1.B, R1.C, R5.D
         FROM R1,R5,R6
         WHERE (R1.A = R5.A) AND (R1.B = R5.B)
           AND (R5.D = R6.D) AND (R6.J > 10)) ;

Concesión de permisos al usuario asken:
         grant SELECT on vista_2 to asken;


-------------------------------------------------------------------------------
Ejercicio 2:
-------------------------------------------------------------------------------

a) Relación General ==> R (A,B,C,D,E,F,G,H) con PK(A,B,D,H)

    Descomposición ==> Todas en 5FN

                R1 (A,B,C,D) con PK(A)
                    Esta en FNBC ya que A es superllave, y en 5FN ya que no hay DMV.
                R2 (F,E) con PK(F)
                R3 (D,F) con PK(D,F)
                R4 (D,G) con PK(D)
                R5 (G,H) con PK(G) y H como llave candidata (UNIQUE)


Conclusión ==> R se encuentra en 1FN (hay DF parciales) y su llave primaria es:  A,D,F,G.

Creación de Tablas==>

CREATE TABLE R1(
  A NUMBER(2) PRIMARY KEY,
  B NUMBER(2),
  C NUMBER(2),
  D REFERENCES R3
);

CREATE TABLE R2(
  F NUMBER(2) PRIMARY KEY,
  E NUMBER(2),
  CONSTRAINT f_r2_nega CHECK (F>0)
);

CREATE TABLE R3(
  D REFERENCES R4,
  F REFERENCES R2,
  CONSTRAINT PK_r3 PRIMARY KEY (D,F)
);

CREATE TABLE R4(
  D NUMBER(2) CONSRTAINT pk_r4 PRIMARY KEY,
  G REFERENCES R5,
  CONSTRAINT d_r4_nega CHECK (D>0)
);

CREATE TABLE R5(
  G NUMBER(2) CONSRTAINT pk_r4 PRIMARY KEY,
  H NUMBER(2) CONSRTAINT ck_r4 UNIQUE,
);

b)
Alter table R5 (add I number(2));

Alter table R5 add (check (H<=6 OR I>5);


c)
i) Indice basado en función:
CREATE INDEX I1 ON TABLE R1(C/2);

ii) Indice sobre la PK, por lo que se podría haber creado la tabla como una
TABLA ORGANIZADA por INDICE, o bien crear el siguiente índice después de
crear la tabla:

CREATE INDEX I2 ON R5(G);

-------------------------------------------------------------------------------
Ejercicio 3:
-------------------------------------------------------------------------------

a) Relacion General ==> R1(A,B,C,D)    PK(A,B), está en 1FN
                        R2(E,F,G,A,B)  PK(E),   está en 5FN (G llave candidata)

Descomposición de R1 en dos relaciones, ambas en 5FN:
                        R11(A,B,C), con PK(A,B)
                        R12(B,D),   con PK(B)

b) Creación de Tablas==>

CREATE TABLE R11 (
  A NUMBER(3),
  B NUMBER(3) REFERENCES R2,
  C NUMBER(3),
  CONSTRAINT  pk_r1 PRIMARY KEY (A,B),
  CONSTRAINT a_r1_no_nega CHECK (A>0)
);

CREATE TABLE R12(
  B NUMBER(3) PRIMARY KEY,
  D NUMBER(3),
  CONSTRAINT b_r2_no_nega CHECK (B>0)
);

CREATE TABLE R2(
  E NUMBER(3) PRIMARY KEY,
  F NUMBER(3),
  G NUMBER(3) UNIQUE,
  A NUMBER(3),
  B NUMBER(3),
  CONSTRAINT g_no_nulo CHECK (G IS NOT NULL),
  CONSTRAINT t3_a_no_nulo CHECK (A IS NOT NULL),
  CONSTRAINT t3_b_no_nulo CHECK (B IS NOT NULL),
  CONSTRAINT  fk_r3 FOREIGN KEY (A,B) references R1
);

c) Trigger para controlar esa restricción:
         CREATE OR REPLACE TRIGGER apartado3
         BEFORE INSERT OR UPDATE OR R3
         FOR EACH ROW
         DECLARE
         aux  Number;
         BEGIN
          SELECT C into aux
            FROM R1
            WHERE A = :new.A AND B = :new.B;

         If (aux>:new.G) then
         Raise_application_error(-20100,'ERROR_APARTADO2');
                End if;

         END apartado3;