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


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


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


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


CREATE DOMAIN MON$SEC_DATABASE
 AS char(7) CHARACTER SET ASCII
 NOT NULL
 COLLATE ASCII;

CREATE DOMAIN SEC$KEY
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;

CREATE DOMAIN SEC$NAME_PART
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;

CREATE DOMAIN SEC$USER_NAME
 AS varchar(10) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;

CREATE DOMAIN SEC$VALUE
 AS varchar(85) CHARACTER SET UNICODE_FSS
 COLLATE UNICODE_FSS;

/******************* PROCEDURES ******************/


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


CREATE TABLE TBL_CLIENTE

(

  IDC integer NOT NULL,

  DESCR varchar(100) NOT NULL,
  
  SALDO double precision NOT NULL,
  
  CONSTRAINT PK_TBL_CLIENTE PRIMARY KEY (IDC)

);

CREATE TABLE TBL_DETALLE

(

  NRO integer NOT NULL,

  IDP integer NOT NULL,

  CANTIDAD integer DEFAULT 1 NOT NULL,

  PRECIO double precision NOT NULL,

  CONSTRAINT PK_TBL_DETALLE PRIMARY KEY (NRO,IDP)

);

CREATE TABLE TBL_FACTURA

(

  NRO integer NOT NULL,

  FECHA date DEFAULT 'TODAY' NOT NULL,

  CLIENTE integer NOT NULL,

  CONFIRMADA char(1) DEFAULT 'N' NOT NULL,

  PAGADA char(1) DEFAULT 'N' NOT NULL,

  MONTO double precision NOT NULL,

  CONSTRAINT PK_TBL_FACTURA PRIMARY KEY (NRO)

);

CREATE TABLE TBL_PRODUCTO

(

  IDP integer NOT NULL,

  DESCR varchar(100) NOT NULL,

  PRECIO double precision NOT NULL,

  STOCK integer DEFAULT 0 NOT NULL,

  CONSTRAINT PK_TBL_PRODUCTO PRIMARY KEY (IDP)

);

/********************* VIEWS **********************/


/******************* EXCEPTIONS *******************/


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



ALTER TABLE TBL_DETALLE ADD SUBTOTAL COMPUTED BY (CANTIDAD * PRECIO);

ALTER TABLE TBL_DETALLE ADD CONSTRAINT FK_TBL_DETALLE_FACTURA
  FOREIGN KEY (NRO) REFERENCES TBL_FACTURA (NRO);

ALTER TABLE TBL_DETALLE ADD CONSTRAINT FK_TBL_DETALLE_PRODUCTO
  FOREIGN KEY (IDP) REFERENCES TBL_PRODUCTO (IDP);

ALTER TABLE TBL_FACTURA ADD CONSTRAINT FK_TBL_FACTURA_CLIENTE
  FOREIGN KEY (CLIENTE) REFERENCES TBL_CLIENTE (IDC);

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

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

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

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

