--
-- 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 smallint NOT NULL,
    cepaciente integer,
    dtconsulta date NOT NULL,
    hrconsulta time without time zone NOT NULL,
    dtcadconsulta date NOT NULL
);


ALTER TABLE public.consultas OWNER TO postgres;

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

COMMENT ON TABLE public.consultas IS 'Cadastro das possíveis consultas em um clinica com vários médicos.';


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

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


ALTER TABLE public.especiliadesmedicas OWNER TO postgres;

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

COMMENT ON TABLE public.especiliadesmedicas IS 'cadastro de especialidades médicas atendidadas em um rede municipal de saúde.';


--
-- 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 NOT NULL
);


ALTER TABLE public.medicos OWNER TO postgres;

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

COMMENT ON TABLE public.medicos IS 'Cadastro do médicos da rede municipal de saúde';


--
-- Name: COLUMN medicos.cpmedico; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.medicos.cpmedico IS 'Chave primária da tabela. ';


--
-- Name: COLUMN medicos.txnomemedico; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.medicos.txnomemedico IS 'Texto com 250 caracteres para conter o Nome do médico.';


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

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


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 cidade que usam os serviços da rede de saúde pública';


--
-- 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: especiliadesmedicas; 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 (2, 'Ana Maria da Silva', '1995-03-15', '2024-08-21');
INSERT INTO public.pacientes VALUES (1, 'Mario de Andrade', '1998-03-25', '2024-08-21');
INSERT INTO public.pacientes VALUES (3, 'Mario de Andrade', '1995-03-25', '2024-08-21');


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

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


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

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


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

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


--
-- Name: especiliadesmedicas pkespeciliadesmedicas; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.especiliadesmedicas
    ADD CONSTRAINT pkespeciliadesmedicas 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 pkmedico; Type: CONSTRAINT; Schema: public; Owner: postgres
--

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


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

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


--
-- Name: medicos fk1medicos; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.medicos
    ADD CONSTRAINT fk1medicos FOREIGN KEY (ceespecmedica) REFERENCES public.especiliadesmedicas(cpespecmedica) ON UPDATE CASCADE ON DELETE RESTRICT NOT VALID;


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

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


--
-- PostgreSQL database dump complete
--

