--
-- PostgreSQL database dump
--

-- Dumped from database version 15.1
-- Dumped by pg_dump version 16.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: clientes; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.clientes (
    cpcliente smallint NOT NULL,
    txnomecliente character varying(250) NOT NULL,
    celogradouro integer,
    dtcadcliente date NOT NULL
);


ALTER TABLE public.clientes OWNER TO postgres;

--
-- Name: COLUMN clientes.celogradouro; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.clientes.celogradouro IS 'Chave estrangeira apontando para a tabela logradouros (no campo cplogradouro)';


--
-- Name: consultas; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.consultas (
    cpconsulta integer NOT NULL,
    cemedico integer,
    dtconsulta date,
    hrconsulta time without time zone,
    dtcadconsulta date,
    cepaciente integer
);


ALTER TABLE public.consultas OWNER TO postgres;

--
-- Name: especmedicas; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.especmedicas (
    cpespecmedica smallint NOT NULL,
    txnomeespecmedica character varying(90) NOT NULL,
    dtcadespecmedica date NOT NULL
);


ALTER TABLE public.especmedicas OWNER TO postgres;

--
-- Name: TABLE especmedicas; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.especmedicas IS 'Cadastro das Especialidades Médicas';


--
-- Name: logradouros; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.logradouros (
    cplogradouro integer NOT NULL,
    txnomelogradouro character varying(250),
    dtlogradouro date
);


ALTER TABLE public.logradouros OWNER TO postgres;

--
-- Name: medicos; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.medicos (
    cpmedico smallint NOT NULL,
    txnomemedico character varying(250) NOT NULL,
    ceespecmedica smallint,
    dtcadmedico date
);


ALTER TABLE public.medicos OWNER TO postgres;

--
-- Name: TABLE medicos; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.medicos IS 'Cadastro dos médicos de uma cidade (que participam nas Unidades Básicas de Saúde)';


--
-- Name: pacientes; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.pacientes (
    cppaciente integer NOT NULL,
    txnomepaciente character varying(250),
    dtnascpaciente date,
    dtcadpaciente date
);


ALTER TABLE public.pacientes OWNER TO postgres;

--
-- Name: TABLE pacientes; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.pacientes IS 'Cadastro de pacientes de uma rede municipal de saúde.';


--
-- Data for Name: clientes; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- Data for Name: consultas; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- Data for Name: especmedicas; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- Data for Name: logradouros; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- Data for Name: medicos; Type: TABLE DATA; Schema: public; Owner: postgres
--



--
-- Data for Name: pacientes; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.pacientes VALUES (1, 'José Felisberto de Athaide', '1998-04-15', '2024-08-16');
INSERT INTO public.pacientes VALUES (2, 'Ana Maria De Alburque Lins', '1998-10-05', '2024-08-16');
INSERT INTO public.pacientes VALUES (4, 'Ana Maria De Alburque Lins', '1998-10-05', '2024-08-16');
INSERT INTO public.pacientes VALUES (3, 'José Felisberto de Athaide', '1997-03-10', '2024-08-16');


--
-- Name: clientes pkclientes; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.clientes
    ADD CONSTRAINT pkclientes PRIMARY KEY (cpcliente);


--
-- Name: consultas pkconsultas; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.consultas
    ADD CONSTRAINT pkconsultas PRIMARY KEY (cpconsulta);


--
-- Name: especmedicas pkespecmedica; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.especmedicas
    ADD CONSTRAINT pkespecmedica PRIMARY KEY (cpespecmedica);


--
-- Name: logradouros pklogradouro; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.logradouros
    ADD CONSTRAINT pklogradouro PRIMARY KEY (cplogradouro);


--
-- Name: medicos pkmedicos; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.medicos
    ADD CONSTRAINT pkmedicos PRIMARY KEY (cpmedico);


--
-- Name: pacientes pkpacientes; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.pacientes
    ADD CONSTRAINT pkpacientes PRIMARY KEY (cppaciente);


--
-- Name: consultas fk1consultas; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.consultas
    ADD CONSTRAINT fk1consultas FOREIGN KEY (cemedico) REFERENCES public.medicos(cpmedico) ON UPDATE CASCADE ON DELETE RESTRICT NOT VALID;


--
-- Name: consultas fk2consultas; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.consultas
    ADD CONSTRAINT fk2consultas FOREIGN KEY (cepaciente) REFERENCES public.pacientes(cppaciente) ON UPDATE SET NULL NOT VALID;


--
-- PostgreSQL database dump complete
--

