CREATE DATABASE "C:\Arquivos de programas\ComputerLife\policial\bco_pm.gdb"
 USER "SYSDBA" PASSWORD "masterkey";

/* DOMINIOS */

/*  Type DANO  */
CREATE DOMAIN DANO AS
  INTEGER
  CHECK ((VALUE >= 0 AND VALUE <= 9999) OR VALUE IS NULL);

/*  Type DBOOLEAN  */
CREATE DOMAIN DBOOLEAN AS
  CHAR(1)
  DEFAULT 'S'
  NOT NULL
  CHECK (VALUE = 'S' OR VALUE = 'N');

/*  Type DCARROPLACA  */
CREATE DOMAIN DCARROPLACA AS
  CHAR(7);

/*  Type Dnome  */
CREATE DOMAIN Dnome AS
  CHAR(60);


/*  Type DCPF  */
CREATE DOMAIN DCGCCPF AS
  CHAR(11);

/*  Type DSTATUS  */
CREATE DOMAIN DCONTASTATUS AS
  SMALLINT
  DEFAULT 1
  CHECK (VALUE IN (0, 1));

/*  Type Dstatuscom  */
CREATE DOMAIN Dstatuscom AS
  SMALLINT
  DEFAULT 0
  CHECK ((VALUE >= 0) AND (VALUE <= 9));

/*  Type DCONTATIPO  */
CREATE DOMAIN DCONTATIPO AS
  SMALLINT
  DEFAULT 0
  CHECK ((VALUE >= 0) AND (VALUE <= 7));

/*  Type DDOCVENDASTATUS  */
CREATE DOMAIN DDOCVENDASTATUS AS
  SMALLINT
  CHECK (VALUE IN (0, 1, 2, 3, 4));

/*  Type DDOCVENDATIPO  */
CREATE DOMAIN DDOCVENDATIPO AS
  SMALLINT
  CHECK ((VALUE >= 0 AND VALUE <= 5));

/*  Type DENDCEP  */
CREATE DOMAIN DENDCEP AS
  CHAR(8);

/*  Type DID  */
CREATE DOMAIN DID AS
  CHAR(10);

/*  Type DLOTE  */
CREATE DOMAIN DLOTE AS
  INTEGER;

/*  Type DMONETARIO  */
CREATE DOMAIN DMONETARIO AS
  NUMERIC(15, 4)
  DEFAULT 0;

/*  Type DPERCENTUAL  */
CREATE DOMAIN DPERCENTUAL AS
  SMALLINT
  DEFAULT 0
  CHECK (VALUE >= 0 AND VALUE <= 100);

/*  Type DPERFIL  */
CREATE DOMAIN DPERFIL AS
  SMALLINT
  CHECK ((VALUE >= 0 AND VALUE <= 8) OR VALUE IS NULL);

/*  Type DREAL  */
CREATE DOMAIN DREAL AS
  DOUBLE PRECISION
  DEFAULT 0;

/*  Type DUF  */
CREATE DOMAIN DUF AS
  CHAR(2)
  DEFAULT 'RJ'
  CHECK (VALUE IN ('AM', 'PA', 'RO', 'RR', 'AC', 'AP', 'TO', 'GO', 'DF', 'MS', 'MT', 'RS',
                   'PR', 'SC', 'SP', 'RJ', 'MG', 'ES', 'BA', 'SE', 'AL', 'PE', 'CE', 'PI',
                   'MA', 'RN')
  OR VALUE IS NULL);

/* Tabelas */

CREATE TABLE PESSOA (
  pessoa_id integer NOT NULL, 
  nome char(60), 
  identidade char(11), 
  CPF char(11), 
  pis_pasep char(11), 
  inss char(11), 
  ctps CHAR(20), 
  titulo_eleitor CHAR(15), 
  DATA_CADASTRO char(8),
  CADASTRADO integer,
  status integer default 1
);


CREATE TABLE ENDERECO (
  endereco_id integer,
  pessoa_id integer, 
  TIPO_LOGR CHAR(6), 
  logradouro CHAR(40), 
  NUM CHAR(10), 
  COMPLEMENTO CHAR(30), 
  BAIRRO CHAR(50), 
  CIDADE CHAR(50), 
  UF CHAR(2), 
  PAIS CHAR(50), 
  CEP CHAR(10),
  status integer default 1
);

CREATE TABLE DELITO (
  delito_id integer, 
  pessoa_id integer, 
  delito char(20), 
  descricao char(200), 
  statuscom CHAR(1), 
  status CHAR(1) default 1
);

CREATE TABLE CONTATO (
  contato_id integer, 
  pessoa_id integer, 
  contato CHAR(100), 
  tipo_cont CHAR(1), 
  status_com CHAR(1), 
  status CHAR(1) default 1
);

CREATE TABLE SITUACAO (
  situacao_id integer, 
  pessoa_id integer, 
  situacao char(20), 
  descricao char(200), 
  status_com CHAR(1), 
  status CHAR(1)  default 1
);


/*  CREATE ASCENDING  INDEX IDOC_VENDA_STATUS */
/*   ON DOC_VENDA (STATUS); */
/*  Visões */
/* CREATE VIEW CLIENTE AS
/*   SELECT   */
/*     FROM PESSOA */
/*     WHERE CLIENTE = 'S' AND ATIVO = 'S'; */
/*  Geradores */
/* CREATE GENERATOR G_PESSOA;*/
/* SET GENERATOR G_PESSOA TO 0;*/
/*  Procedimentos */


SET TERM ^ ;

EXIT;