Modelo Físico de BD

From Grupo6 Especoo

Criação das tabelas

---

CREATE TABLE "Gerentes" (

   "idGer" serial NOT NULL

) WITHOUT OIDS;

CREATE TABLE "Atendentes" (

   "idAtend" serial NOT NULL,
   "Especialidade" character varying(25)

) WITHOUT OIDS;

CREATE TABLE "Avaliacao" (

   "idAvaliacao" serial NOT NULL,
   "Descricao" character varying(40)

) WITHOUT OIDS;

CREATE TABLE "Pecas" (

   "Codigo" character varying(20) NOT NULL,
   "Descricao" character varying(40),
   "Quantidade" integer,
   "idStatus" integer

) WITHOUT OIDS;

CREATE TABLE "Rel_usa_peca" (

   "NroChamado" integer,
   "CodigoPeca" character varying(20),
   "Quantidade" integer

) WITHOUT OIDS;

CREATE TABLE "StatusChamado" (

   "idStatus" serial NOT NULL,
   "Descricao" character varying(25)

) WITHOUT OIDS;

CREATE TABLE "StatusPeca" (

   "idStatus" serial NOT NULL,
   "Descricao" character varying(25)

) WITHOUT OIDS;

CREATE TABLE "Chamado" (

   "Nro" serial NOT NULL,
   "idFunc" integer,
   "Prioridade" character varying(15),
   "Departamento" character varying(25),
   "Descricao" character varying(50),
   "Solucao" character varying(50),
   "DataStatus" date,
   "idStatus" integer,
   "idAvaliacao" integer,
   "idGer" integer,
   "DataChamado" date,
   "idAtend" integer

) WITHOUT OIDS;

Criação das PK

---

ALTER TABLE ONLY "Gerentes"

   ADD CONSTRAINT gerentes_pkey PRIMARY KEY ("idGer");

ALTER TABLE ONLY "Atendentes"

   ADD CONSTRAINT atendentes_pkey PRIMARY KEY ("idAtend");

ALTER TABLE ONLY "Avaliacao"

   ADD CONSTRAINT "Avaliacao_pkey" PRIMARY KEY ("idAvaliacao");

ALTER TABLE ONLY "Pecas"

   ADD CONSTRAINT "Pecas_pkey" PRIMARY KEY ("Codigo");

ALTER TABLE ONLY "StatusChamado"

   ADD CONSTRAINT "StatusChamado_pkey" PRIMARY KEY ("idStatus");

ALTER TABLE ONLY "StatusPeca"

   ADD CONSTRAINT "StatusPeca_pkey" PRIMARY KEY ("idStatus");

ALTER TABLE ONLY "Chamado"

   ADD CONSTRAINT "Chamado_pkey" PRIMARY KEY ("Nro");

Criação das FK

---

ALTER TABLE ONLY "Chamado"

   ADD CONSTRAINT avaliacao_chamado_fkey FOREIGN KEY ("idAvaliacao") REFERENCES "Avaliacao"("idAvaliacao") ON DELETE RESTRICT;

ALTER TABLE ONLY "Chamado"

   ADD CONSTRAINT atendente_chamado_fkey FOREIGN KEY ("idAtend") REFERENCES "Atendentes"("idAtend") ON DELETE RESTRICT;

ALTER TABLE ONLY "Chamado"

   ADD CONSTRAINT gerente_chamado_fkey FOREIGN KEY ("idGer") REFERENCES "Gerentes"("idGer") ON DELETE RESTRICT;

ALTER TABLE ONLY "Chamado"

   ADD CONSTRAINT status_chamado_fkey FOREIGN KEY ("idStatus") REFERENCES "StatusChamado"("idStatus") ON DELETE RESTRICT;

ALTER TABLE ONLY "Pecas"

   ADD CONSTRAINT status_peca_fkey FOREIGN KEY ("idStatus") REFERENCES "StatusPeca"("idStatus") ON DELETE RESTRICT;

ALTER TABLE ONLY "Rel_usa_peca"

   ADD CONSTRAINT nro_chamado_fkey FOREIGN KEY ("NroChamado") REFERENCES "Chamado"("Nro") ON DELETE RESTRICT;

ALTER TABLE ONLY "Rel_usa_peca"

   ADD CONSTRAINT codigo_peca_fkey FOREIGN KEY ("CodigoPeca") REFERENCES "Pecas"("Codigo") ON DELETE RESTRICT;