/********************* 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 EMPLEADO
(
  DNI DOM_NUMERO NOT NULL,
  APENOM varchar(100) NOT NULL,
  NOMBRE DOM_CARACTER,
  CONSTRAINT PK_EMPLEADO PRIMARY KEY (DNI)
);
CREATE TABLE PRODUCTO
(
  CODIGO DOM_NUMERO NOT NULL,
  DESCRIPCION varchar(100) NOT NULL,
  STOCK integer DEFAULT 0 NOT NULL,
  CONSTRAINT PK_PRODUCTO PRIMARY KEY (CODIGO)
);
CREATE TABLE SEVENDE
(
  CODIGO DOM_NUMERO NOT NULL,
  NOMBRE DOM_CARACTER NOT NULL,
  PRECIO double precision DEFAULT 0 NOT NULL,
  STOCK integer DEFAULT 0 NOT NULL,
  CONSTRAINT PK_SEVENDE PRIMARY KEY (CODIGO,NOMBRE)
);
CREATE TABLE SUCURSAL
(
  NOMBRE DOM_CARACTER NOT NULL,
  DIRECCION varchar(100) NOT NULL,
  CONSTRAINT PK_SUCURSAL PRIMARY KEY (NOMBRE)
);
/********************* VIEWS **********************/

CREATE VIEW ART_LUJAN_Y_NO_CHIVILCOY (CODIGO, DESCRIPCION, STOCK)
AS SELECT *
FROM PRODUCTO 
WHERE CODIGO IN ( SELECT CODIGO FROM SEVENDE WHERE NOMBRE = 'LUJAN' ) AND 
      CODIGO NOT IN ( SELECT CODIGO FROM SEVENDE WHERE NOMBRE = 'CHIVILCOY' );
/******************* EXCEPTIONS *******************/

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

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

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


ALTER TABLE EMPLEADO ADD CONSTRAINT FK_EMPLEADO_SUCURSAL
  FOREIGN KEY (NOMBRE) REFERENCES SUCURSAL (NOMBRE);
ALTER TABLE SEVENDE ADD CONSTRAINT FK_SEVENDE_PRODUCTO
  FOREIGN KEY (CODIGO) REFERENCES PRODUCTO (CODIGO);
ALTER TABLE SEVENDE ADD CONSTRAINT FK_SEVENDE_SUCURSAL
  FOREIGN KEY (NOMBRE) REFERENCES SUCURSAL (NOMBRE);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON EMPLEADO TO  SYSDBA WITH GRANT OPTION;

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

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

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

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

