Skip to content

GitLab

banco_dados · Changes

Page history
Update banco_dados authored by Adriana Anacleto Serpa's avatar Adriana Anacleto Serpa
Hide whitespace changes
Inline Side-by-side
banco_dados.md
View page @ 8d11b1fc
...@@ -34,4 +34,343 @@ Aqui se encontra toda a modelagem do banco de dados da plataforma Doação. ...@@ -34,4 +34,343 @@ Aqui se encontra toda a modelagem do banco de dados da plataforma Doação.
<img src="./uploads/4047b68b6a83345c631c17cd1a287fac/image.png" width="2000px" align="center"> <img src="./uploads/4047b68b6a83345c631c17cd1a287fac/image.png" width="2000px" align="center">
Clique na imagem para ampliar. Clique na imagem para ampliar.
Optamos por usar um banco relacional, pois seria melhor, tendo em vista que o banco iria possuir várias relações entre as diferentes tabelas existentes, então, para facilitar o trabalho optamos por usar o PostgreSQL. Optamos por usar um banco relacional, pois seria melhor, tendo em vista que o banco iria possuir várias relações entre as diferentes tabelas existentes, então, para facilitar o trabalho optamos por usar o PostgreSQL.
\ No newline at end of file
## Scripts de criação para as tabelas
<table align="left"><tr><td align="left" >
### Tabela Administrator
-- public.administrator definition
-- Drop table
-- DROP TABLE public.administrator;
CREATE TABLE public.administrator (
id_user int4 NOT NULL,
name varchar(200) NOT NULL,
email varchar(200) NOT NULL,
id_adm int4 NOT NULL,
CONSTRAINT administrator_pk PRIMARY KEY (id_adm)
);
-- public.administrator foreign keys
ALTER TABLE public.administrator ADD CONSTRAINT fk_id_user FOREIGN KEY (id_user) REFERENCES public.users(id_user);
### Tabela Institution
-- public.institution definition
-- Drop table
-- DROP TABLE public.institution;
CREATE TABLE public.institution (
id_institution int4 NOT NULL,
id_user int4 NOT NULL,
email varchar(200) NOT NULL,
"name" varchar(200) NOT NULL,
cnpj varchar(14) NULL,
whatsapp varchar(20) NULL,
street varchar(200) NOT NULL,
city varchar(200) NOT NULL,
state varchar(50) NOT NULL,
status varchar(1) NULL,
created_time date NULL,
phone varchar(255) NOT NULL,
complement varchar(255) NULL,
website varchar(255) NULL,
social_media varchar(255) NULL,
other_social_media varchar(255) NULL,
description varchar(255) NOT NULL,
cep varchar(255) NOT NULL,
district varchar(255) NOT NULL,
CONSTRAINT pk_institution PRIMARY KEY (id_institution)
);
-- public.institution foreign keys
ALTER TABLE public.institution ADD CONSTRAINT fk_user_institution FOREIGN KEY (id_user) REFERENCES public.users(id_user);
### Tabela Item_Solicitation
-- public.item_solicitation definition
-- Drop table
-- DROP TABLE public.item_solicitation;
CREATE TABLE public.item_solicitation (
id_item_solicitation int4 NOT NULL,
id_institution int4 NOT NULL,
created_time date NOT NULL,
item varchar(255) NOT NULL,
status varchar(1) NOT NULL,
CONSTRAINT pk_item_solicitation PRIMARY KEY (id_item_solicitation)
);
-- public.item_solicitation foreign keys
ALTER TABLE public.item_solicitation ADD CONSTRAINT fk_id_institution FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
### Tabela Itens
-- public.itens definition
-- Drop table
-- DROP TABLE public.itens;
CREATE TABLE public.itens (
id_item int4 NOT NULL,
"name" varchar(255) NOT NULL,
limit_itens int4 NOT NULL,
unit_measurement varchar(255) NOT NULL,
CONSTRAINT pk_itens PRIMARY KEY (id_item)
);
### Tabela Product_Details_Order
-- public.product_details_order definition
-- Drop table
-- DROP TABLE public.product_details_order;
CREATE TABLE public.product_details_order (
id_product_details_order int4 NOT NULL,
id_institution int4 NOT NULL,
is_urgent bool NOT NULL,
created_time date NOT NULL,
limit_date date NOT NULL,
status varchar(1) NOT NULL,
description varchar(512) NULL,
CONSTRAINT pk_id_product_details_order PRIMARY KEY (id_product_details_order)
);
-- public.product_details_order foreign keys
ALTER TABLE public.product_details_order ADD CONSTRAINT fk_id_institution FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
### Tabela Product_Interest
-- public.product_interest definition
-- Drop table
-- DROP TABLE public.product_interest;
CREATE TABLE public.product_interest (
id_product_interest int4 NOT NULL,
id_item int4 NOT NULL,
quantity int4 NOT NULL,
id_product_order_interest int4 NULL,
CONSTRAINT product_interest_pkey PRIMARY KEY (id_product_interest, id_item)
);
-- public.product_interest foreign keys
ALTER TABLE public.product_interest ADD CONSTRAINT fk8uaemq3seveakutup5mmcb5mb FOREIGN KEY (id_product_order_interest) REFERENCES public.product_order_interest(id_product_order_interest);
ALTER TABLE public.product_interest ADD CONSTRAINT product_interest_id_item_fkey FOREIGN KEY (id_item) REFERENCES public.itens(id_item);
### Tabela Product_Order_Interest
-- public.product_order_interest definition
-- Drop table
-- DROP TABLE public.product_order_interest;
CREATE TABLE public.product_order_interest (
id_product_order_interest int4 NOT NULL,
active bool NULL,
email varchar(255) NULL,
"name" varchar(255) NULL,
phone varchar(255) NULL,
id_institution int4 NULL,
id_product_details_order int4 NULL,
CONSTRAINT product_order_interest_pkey PRIMARY KEY (id_product_order_interest)
);
-- public.product_order_interest foreign keys
ALTER TABLE public.product_order_interest ADD CONSTRAINT fk26gn4wcp72y1uioei71qai8jc FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
ALTER TABLE public.product_order_interest ADD CONSTRAINT fkjarf6gmmnmylutf00f8br8pxc FOREIGN KEY (id_product_details_order) REFERENCES public.product_details_order(id_product_details_order);
### Tabela Product_Order_Interest_Lines
-- public.product_order_interest definition
-- Drop table
-- DROP TABLE public.product_order_interest;
CREATE TABLE public.product_order_interest (
id_product_order_interest int4 NOT NULL,
active bool NULL,
email varchar(255) NULL,
"name" varchar(255) NULL,
phone varchar(255) NULL,
id_institution int4 NULL,
id_product_details_order int4 NULL,
CONSTRAINT product_order_interest_pkey PRIMARY KEY (id_product_order_interest)
);
-- public.product_order_interest foreign keys
ALTER TABLE public.product_order_interest ADD CONSTRAINT fk26gn4wcp72y1uioei71qai8jc FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
ALTER TABLE public.product_order_interest ADD CONSTRAINT fkjarf6gmmnmylutf00f8br8pxc FOREIGN KEY (id_product_details_order) REFERENCES public.product_details_order(id_product_details_order);
### Tabela Product_Quantity_Order
-- public.product_quantity_order definition
-- Drop table
-- DROP TABLE public.product_quantity_order;
CREATE TABLE public.product_quantity_order (
id_product_details_order int4 NOT NULL,
id_institution int4 NOT NULL,
id_item int4 NOT NULL,
qtd_products_received int4 NOT NULL,
qtd_products_missing int4 NOT NULL,
qtd_products_solicited int4 NOT NULL,
id_product_quantity_order int4 NOT NULL,
observations varchar(500) NULL,
CONSTRAINT pk_id_product_quantity_order PRIMARY KEY (id_institution, id_product_details_order, id_item)
);
-- public.product_quantity_order foreign keys
ALTER TABLE public.product_quantity_order ADD CONSTRAINT fk5p6yfpfrrmtcu7vmsgy00mx0a FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
ALTER TABLE public.product_quantity_order ADD CONSTRAINT fk_id_item FOREIGN KEY (id_item) REFERENCES public.itens(id_item);
ALTER TABLE public.product_quantity_order ADD CONSTRAINT fkq6rwtnw2dcsmxiylhk91ha29r FOREIGN KEY (id_product_details_order) REFERENCES public.product_details_order(id_product_details_order);
### Tabela Service
-- public.service definition
-- Drop table
-- DROP TABLE public.service;
CREATE TABLE public.service (
id_service int4 NOT NULL,
"name" varchar(255) NOT NULL,
limit_service int4 NOT NULL,
CONSTRAINT pk_service PRIMARY KEY (id_service)
);
### Tabela Service_Details_Order
-- public.service_details_order definition
-- Drop table
-- DROP TABLE public.service_details_order;
CREATE TABLE public.service_details_order (
id_service_details_order int4 NOT NULL,
id_institution int4 NOT NULL,
is_urgent bool NOT NULL,
created_time date NOT NULL,
limit_date date NOT NULL,
status varchar(1) NOT NULL,
description varchar(512) NULL,
is_active bool NULL,
CONSTRAINT pk_id_service_details_order PRIMARY KEY (id_service_details_order)
);
-- public.service_details_order foreign keys
ALTER TABLE public.service_details_order ADD CONSTRAINT fk_id_institution FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
### Tabela Service_Interest
-- public.service_interest definition
-- Drop table
-- DROP TABLE public.service_interest;
CREATE TABLE public.service_interest (
id_service_interest int4 NOT NULL,
quantity int4 NULL,
id_service int4 NULL,
id_service_order_interest int4 NULL,
CONSTRAINT service_interest_pkey PRIMARY KEY (id_service_interest)
);
-- public.service_interest foreign keys
ALTER TABLE public.service_interest ADD CONSTRAINT fk217hs1gec0eerdr2uvy0301ks FOREIGN KEY (id_service_order_interest) REFERENCES public.service_order_interest(id_service_order_interest);
ALTER TABLE public.service_interest ADD CONSTRAINT fkso5g3wlnhy0s6h70a3murhm3w FOREIGN KEY (id_service) REFERENCES public.service(id_service);
### Tabela Service_Order_Interest
-- public.service_order_interest definition
-- Drop table
-- DROP TABLE public.service_order_interest;
CREATE TABLE public.service_order_interest (
id_service_order_interest int4 NOT NULL,
active varchar(255) NULL,
email varchar(255) NULL,
"name" varchar(255) NULL,
phone varchar(255) NULL,
id_institution int4 NULL,
id_service_details_order int4 NULL,
CONSTRAINT service_order_interest_pkey PRIMARY KEY (id_service_order_interest)
);
-- public.service_order_interest foreign keys
ALTER TABLE public.service_order_interest ADD CONSTRAINT fk1ae2kg6aqtjwoygscfpq0g4b2 FOREIGN KEY (id_service_details_order) REFERENCES public.service_details_order(id_service_details_order);
ALTER TABLE public.service_order_interest ADD CONSTRAINT fkksrqtdfah0im5bay2xbtou6wv FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
### Tabela Service_Quantity_Order
-- public.service_quantity_order definition
-- Drop table
-- DROP TABLE public.service_quantity_order;
CREATE TABLE public.service_quantity_order (
id_service_quantity_order int4 NOT NULL,
id_service_details_order int4 NOT NULL,
id_institution int4 NOT NULL,
id_service int4 NOT NULL,
qtd_service_received int4 NOT NULL,
qtd_service_missing int4 NOT NULL,
qtd_service_solicited int4 NOT NULL,
observations varchar(500) NULL,
CONSTRAINT pk_id_service_quantity_order PRIMARY KEY (id_institution, id_service_quantity_order, id_service, id_service_details_order)
);
-- public.service_quantity_order foreign keys
ALTER TABLE public.service_quantity_order ADD CONSTRAINT fk_id_service FOREIGN KEY (id_service) REFERENCES public.service(id_service);
ALTER TABLE public.service_quantity_order ADD CONSTRAINT fkjvrbu0h6psnqanw5yslqtu6xv FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
ALTER TABLE public.service_quantity_order ADD CONSTRAINT fkrnho2x8h2w1a25vxsnuf1crau FOREIGN KEY (id_service_details_order) REFERENCES public.service_details_order(id_service_details_order);
### Tabela Service_Solicitation
-- public.service_solicitation definition
-- Drop table
-- DROP TABLE public.service_solicitation;
CREATE TABLE public.service_solicitation (
id_service_solicitation int4 NOT NULL,
id_institution int4 NOT NULL,
created_time date NOT NULL,
service varchar(255) NOT NULL,
status varchar(255) NULL,
CONSTRAINT pk_service_solicitation PRIMARY KEY (id_service_solicitation)
);
-- public.service_solicitation foreign keys
ALTER TABLE public.service_solicitation ADD CONSTRAINT fk_id_institution FOREIGN KEY (id_institution) REFERENCES public.institution(id_institution);
### Tabela Users
-- public.users definition
-- Drop table
-- DROP TABLE public.users;
CREATE TABLE public.users (
id_user int4 NOT NULL,
login varchar(150) NOT NULL,
"password" varchar(150) NOT NULL,
isadmin bool NULL,
CONSTRAINT pk_users PRIMARY KEY (id_user)
);
\ No newline at end of file