/********************* ROLES **********************/

/********************* UDFS ***********************/

/********************* FUNCTIONS ***********************/

/****************** SEQUENCES ********************/

/******************** DOMAINS *********************/

CREATE DOMAIN DOM_CARACTER
 AS char(20)
 NOT NULL
 COLLATE NONE;
CREATE DOMAIN DOM_NUMERO
 AS integer
 DEFAULT 1
 NOT NULL
 CHECK (VALUE > 0)
;
/******************* PROCEDURES ******************/

/******************* PACKAGES ******************/

/******************** TABLES **********************/

CREATE TABLE AUTORIDAD
(
  APENOM DOM_CARACTER NOT NULL,
  CONSTRAINT PK_AUTORIDAD PRIMARY KEY (APENOM)
);
CREATE TABLE EQUIPO
(
  NOMBRE DOM_CARACTER NOT NULL,
  CONSTRAINT PK_EQUIPO PRIMARY KEY (NOMBRE)
);
CREATE TABLE JUGADOR
(
  APENOM DOM_CARACTER NOT NULL,
  NOMBRE DOM_CARACTER,
  CONSTRAINT PK_JUGADOR PRIMARY KEY (APENOM)
);
CREATE TABLE PARTIDO
(
  NRO DOM_NUMERO NOT NULL,
  FHINICIO timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
  FHFIN timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
  JUEZL1 DOM_CARACTER,
  JUEZL2 DOM_CARACTER,
  ARBITRO DOM_CARACTER,
  EQLOCAL DOM_CARACTER,
  EQVISITANTE DOM_CARACTER,
  NOMBRE DOM_CARACTER,
  CONSTRAINT PK_PARTIDO PRIMARY KEY (NRO),
  CONSTRAINT CC_PARTIDO_ARBITRO UNIQUE (ARBITRO,FHINICIO),
  CONSTRAINT CC_PARTIDO_JUEZL1 UNIQUE (JUEZL1,FHINICIO),
  CONSTRAINT CC_PARTIDO_JUEZL2 UNIQUE (JUEZL2,FHINICIO),
  CONSTRAINT CC_PARTIDO_LOCAL UNIQUE (EQLOCAL,FHINICIO),
  CONSTRAINT CC_PARTIDO_SEDE1 UNIQUE (NOMBRE,FHINICIO),
  CONSTRAINT CC_PARTIDO_SEDE2 UNIQUE (NOMBRE,FHFIN),
  CONSTRAINT CC_PARTIDO_VISITANTE UNIQUE (EQVISITANTE,FHINICIO)
);
CREATE TABLE PART_JUG
(
  NRO DOM_NUMERO NOT NULL,
  APENOM DOM_CARACTER NOT NULL,
  GOLES integer DEFAULT 0,
  NOMBRE_PUE DOM_CARACTER,
  CONSTRAINT PK_PART_JUG PRIMARY KEY (NRO,APENOM)
);
CREATE TABLE PUESTO
(
  NOMBRE DOM_CARACTER NOT NULL,
  CONSTRAINT PK_PUESTO PRIMARY KEY (NOMBRE)
);
CREATE TABLE SEDE
(
  NOMBRE DOM_CARACTER NOT NULL,
  CONSTRAINT PK_SEDE PRIMARY KEY (NOMBRE)
);
/********************* VIEWS **********************/

CREATE VIEW V_PARTIDO (NRO, FHINICIO, FHFIN, JUEZL1, JUEZL2, ARBITRO, EQLOCAL, EQVISITANTE, NOMBRE, CANGL, CANGV, RESULTADO)
AS SELECT P.NRO,P.FHINICIO,P.FHFIN,P.JUEZL1,P.JUEZL2,P.ARBITRO,P.EQLOCAL,P.EQVISITANTE,P.NOMBRE,V.CANGL,V.CANGV,V.RESULTADO
FROM PARTIDO P INNER JOIN V_PARTIDO_R V ON P.NRO = V.NRO;
CREATE VIEW V_PARTIDO_GL (NRO, CANGL)
AS --CALCULO LOS ATRIBUTOS DERIVADOS CANGL,CANGV,RESULTADO
SELECT P.NRO,SUM(PJ.GOLES) AS CANGL
FROM PARTIDO P INNER JOIN PART_JUG PJ ON P.NRO = PJ.NRO INNER JOIN JUGADOR J ON PJ.APENOM = J.APENOM
WHERE P.EQLOCAL = J.NOMBRE
GROUP BY P.NRO;
CREATE VIEW V_PARTIDO_GV (NRO, CANGV)
AS --CALCULO LOS ATRIBUTOS DERIVADOS CANGL,CANGV,RESULTADO
SELECT P.NRO,SUM(PJ.GOLES) AS CANGV
FROM PARTIDO P INNER JOIN PART_JUG PJ ON P.NRO = PJ.NRO INNER JOIN JUGADOR J ON PJ.APENOM = J.APENOM
WHERE P.EQVISITANTE = J.NOMBRE
GROUP BY P.NRO;
CREATE VIEW V_PARTIDO_R (NRO, CANGL, CANGV, RESULTADO)
AS --CALCULO LOS ATRIBUTOS DERIVADOS CANGL,CANGV,RESULTADO
SELECT PL.NRO,PL.CANGL,PV.CANGV,IIF(PL.CANGL = PV.CANGV,'E',IIF(PL.CANGL > PV.CANGV,'L','V')) AS RESULTADO
FROM V_PARTIDO_GL PL INNER JOIN V_PARTIDO_GV PV ON PL.NRO = PV.NRO;
/******************* EXCEPTIONS *******************/

/******************** TRIGGERS ********************/

/******************** DB TRIGGERS ********************/

/******************** DDL TRIGGERS ********************/


ALTER TABLE JUGADOR ADD CONSTRAINT FK_JUGADOR_EQUIPO
  FOREIGN KEY (NOMBRE) REFERENCES EQUIPO (NOMBRE) ON UPDATE CASCADE;
ALTER TABLE PARTIDO ADD CONSTRAINT FK_PARTIDO_ARBITRO
  FOREIGN KEY (ARBITRO) REFERENCES AUTORIDAD (APENOM);
ALTER TABLE PARTIDO ADD CONSTRAINT FK_PARTIDO_JUEZL1
  FOREIGN KEY (JUEZL1) REFERENCES AUTORIDAD (APENOM);
ALTER TABLE PARTIDO ADD CONSTRAINT FK_PARTIDO_JUEZL2
  FOREIGN KEY (JUEZL2) REFERENCES AUTORIDAD (APENOM);
ALTER TABLE PARTIDO ADD CONSTRAINT FK_PARTIDO_LOCAL
  FOREIGN KEY (EQLOCAL) REFERENCES EQUIPO (NOMBRE);
ALTER TABLE PARTIDO ADD CONSTRAINT FK_PARTIDO_SEDE
  FOREIGN KEY (NOMBRE) REFERENCES SEDE (NOMBRE);
ALTER TABLE PARTIDO ADD CONSTRAINT FK_PARTIDO_VISITANTE
  FOREIGN KEY (EQVISITANTE) REFERENCES EQUIPO (NOMBRE);
ALTER TABLE PARTIDO ADD CONSTRAINT CK_PARTIDO_AUTORIDAD
  check (JUEZL1 <> JUEZL2 AND JUEZL1 <> ARBITRO AND ARBITRO <> JUEZL2);
ALTER TABLE PARTIDO ADD CONSTRAINT CK_PARTIDO_EQUIPO
  CHECK  (EQLOCAL <> EQVISITANTE);
ALTER TABLE PART_JUG ADD CONSTRAINT FK_PART_JUG_JUGADOR
  FOREIGN KEY (APENOM) REFERENCES JUGADOR (APENOM);
ALTER TABLE PART_JUG ADD CONSTRAINT FK_PART_JUG_PARTIDO
  FOREIGN KEY (NRO) REFERENCES PARTIDO (NRO);
ALTER TABLE PART_JUG ADD CONSTRAINT FK_PART_JUG_PUESTO
  FOREIGN KEY (NOMBRE_PUE) REFERENCES PUESTO (NOMBRE);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON AUTORIDAD TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON EQUIPO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON JUGADOR TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PARTIDO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PART_JUG TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PUESTO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON SEDE TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_PARTIDO TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_PARTIDO_GL TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_PARTIDO_GV TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON V_PARTIDO_R TO  SYSDBA WITH GRANT OPTION;

