search

YuttyAkiko / vision

0 stars 1 forks source link

Modelo Físico do Banco de Dados da Clínica Vision - SQL #8

Closed YuttyAkiko closed 5 months ago

YuttyAkiko commented 5 months ago

Criação das tabelas

CREATE TABLE Cargo (
    id_cargo INTEGER UNIQUE PRIMARY KEY NOT NULL,
    tipo_cargo TEXT NOT NULL,
    nome_cargo TEXT NOT NULL,
    descricao TEXT NULL,
    entrada NUMERIC NULL,
    saida NUMERIC NULL
);

CREATE TABLE Funcionario (
    id_funcionario INTEGER UNIQUE PRIMARY KEY NOT NULL,
    nome_func TEXT NOT NULL,
    sobrenome_func TEXT NOT NULL,
    genero_func CHAR NOT NULL,
    cpf_func INT UNIQUE NOT NULL,
    nasc_func CURRENT_DATE NOT NULL,
    tel_func_1 INT NOT NULL,
    tel_func_2 INT NULL,
    cep_func INT NOT NULL,
    end_func TEXT NOT NULL,
    bairro_func TEXT NOT NULL,
    cidade_func TEXT NOT NULL,
    email_func TEXT NOT NULL,
    id_cargo INT NOT NULL,
    status_cad_func INT NOT NULL,
    FOREIGN KEY (id_cargo) REFERENCES Cargo(id_cargo)
);

CREATE TABLE Especialidade (
    id_especialidade INTEGER UNIQUE PRIMARY KEY NOT NULL,
    tipo_especialidade TEXT NOT NULL,
    valor_consulta REAL NOT NULL
);

CREATE TABLE Medico (
    id_medico INTEGER UNIQUE PRIMARY KEY NOT NULL,
    cnpj_med INT UNIQUE NULL,
    crm INT UNIQUE NOT NULL,
    id_especialidade INT NOT NULL,
    id_funcionario INT NOT NULL,
    FOREIGN KEY (id_especialidade) REFERENCES Especialidade(id_especialidade),
    FOREIGN KEY (id_funcionario) REFERENCES Funcionario(id_funcionario)
);

CREATE TABLE Convenio (
    id_convenio INTEGER UNIQUE PRIMARY KEY NOT NULL,
    num_carteirinha INT NULL,
    nome_convenio TEXT NULL,
    titular TEXT NULL,
    cpf_titular INT NOT NULL
);

CREATE TABLE Paciente (
    id_paciente INTEGER UNIQUE PRIMARY KEY NOT NULL,
    nome_pac TEXT NOT NULL,
    sobrenome_pac TEXT NOT NULL,
    genero_pac TEXT NOT NULL,
    cpf_pac INT UNIQUE NOT NULL,
    nasc_pac CURRENT_DATE NOT NULL,
    tel_pac_1 INT NOT NULL,
    tel_pac_2 INT NULL,
    cep_pac INT NOT NULL,
    end_pac TEXT NOT NULL,
    bairro_pac TEXT NOT NULL,
    cidade_pac TEXT NOT NULL,
    email_pac TEXT NOT NULL,
    id_convenio INT UNIQUE NULL,
    status_cad_pac INT NOT NULL,
    FOREIGN KEY (id_convenio) REFERENCES Convenio(id_convenio)
);

CREATE TABLE Consulta (
    id_consulta INTEGER UNIQUE PRIMARY KEY NOT NULL,
    data_cons CURRENT_DATE NOT NULL,
    hora_cons CURRENT_TIME NOT NULL,
    id_paciente INT NOT NULL,
    id_medico INT NOT NULL,
    tipo_pag_cons TEXT NOT NULL,
    status_pag_cons INT NOT NULL,
    status_cons INT NOT NULL,
    motivo TEXT NOT NULL,
    sintomas TEXT NULL,
    observacoes TEXT NULL,
    laudo TEXT NULL,
    FOREIGN KEY (id_paciente) REFERENCES Paciente(id_paciente),
    FOREIGN KEY (id_medico) REFERENCES Medico(id_medico)
);

CREATE TABLE Receita (
    id_receita INTEGER UNIQUE PRIMARY KEY NOT NULL,
    id_consulta INT UNIQUE NOT NULL,
    data_rec CURRENT_TIMESTAMP NOT NULL,
    L_esf_OD TEXT NULL,
    L_esf_OE TEXT NULL,
    L_cil_OD TEXT NULL,
    L_cil_OE TEXT NULL,
    L_eixo_OD TEXT NULL,
    L_eixo_OE TEXT NULL,
    L_dp_OD TEXT NULL,
    L_dp_OE TEXT NULL,
    P_esf_OD TEXT NULL,
    P_esf_OE TEXT NULL,
    P_cil_OD TEXT NULL,
    P_cil_OE TEXT NULL,
    P_eixo_OD TEXT NULL,
    P_eixo_OE TEXT NULL,
    P_dp_OD TEXT NULL,
    P_dp_OE TEXT NULL,
    FOREIGN KEY (id_consulta) REFERENCES Consulta(id_consulta)
);

CREATE TABLE Exame (
    id_exame INTEGER UNIQUE PRIMARY KEY NOT NULL,
    id_consulta INT NULL,
    tipo_exame TEXT NOT NULL,
    valor_exame REAL NOT NULL,
    tipo_pag_ex TEXT NOT NULL,
    status_pag_ex INT NOT NULL,
    FOREIGN KEY (id_consulta) REFERENCES Consulta(id_consulta)
)

Criação da restrição para a tabela funcionário

--Criando restrição de apenas inserir novo medico se na tabela de funcionario o medico tiver 
--o id_cargo referente ao perfil médico, neste caso é o numero 3.

CREATE TRIGGER check_cargo_funcionario_medico
BEFORE INSERT ON Medico
WHEN (
    SELECT id_cargo FROM Funcionario WHERE id_funcionario = NEW.id_funcionario
) != 3
BEGIN
    SELECT RAISE(ABORT, 'O funcionário não tem o cargo adequado para ser médico.');
END;

Inserindo dados nas tabelas

INSERT INTO Cargo (tipo_cargo, nome_cargo, descricao, entrada, saida)
VALUES 
    ('Administrativo', 'Administrador', 'Responsável pela gestão e supervisão das operações administrativas da empresa. Desenvolve e implementa políticas e procedimentos, coordena equipes e garante o funcionamento eficiente dos processos administrativos.', '08:30:00', '17:30:00'),
    ('Administrativo', 'Assistente Administrativo', 'Responsável por tarefas administrativas gerais, como gerenciamento de documentos, atendimento telefônico e suporte administrativo.', '08:00:00', '17:00:00'),
    ('Médico', 'Clínico Geral', 'Responsável por fornecer cuidados médicos primários a pacientes de todas as idades. Realiza exames físicos, diagnósticos, prescreve tratamentos e encaminha pacientes a especialistas quando necessário.', '08:00:00', '18:00:00');

INSERT INTO Funcionario (nome_func, sobrenome_func, genero_func, cpf_func, nasc_func, tel_func_1, cep_func, end_func, bairro_func, cidade_func, email_func, id_cargo, status_cad_func)
VALUES 
    ('João', 'Silva', 'Masculino', '123.456.789-00', '1990-05-15', '(XX)XXXXX-XXXX', '12345-678', 'Rua das Flores, 123', 'Centro', 'São Paulo', 'joao.silva@example.com', 1, 'Ativo'),
    ('Maria', 'Santos', 'Feminino', '987.654.321-00', '1985-10-20', '(XX)XXXXX-XXXX', '54321-876', 'Avenida dos Sonhos, 456', 'Jardim América', 'Rio de Janeiro', 'maria.santos@example.com', 2, 'Ativo'),
    ('Pedro', 'Oliveira', 'Masculino', '456.789.123-00', '1992-03-25', '(XX)XXXXX-XXXX', '98765-432', 'Travessa das Palmeiras, 789', 'Boa Vista', 'Belo Horizonte', 'pedro.oliveira@example.com', 3, 'Ativo');

INSERT INTO Especialidade (tipo_especialidade, valor_consulta)
VALUES  
    ('SEGMENTO ANTERIOR','45,00'),
    ('OFTALMOPEDIATRIA','45,00'),
    ('OFTALMOLOGIA GERAL','35,00');

INSERT INTO Medico (cnpj_med, crm, id_especialidade, id_funcionario) 
VALUES ('45678901234489', 'CRM62360', 3, 2);

INSERT INTO Convenio (num_carteirinha, nome_convenio, titular, cpf_titular)
VALUES 
    ('1234567890', 'Seguro Saúde Plus', 'Sim', ''),
    ('9876543210', 'Plano de Saúde Flex', 'Sim', ''),
    ('2468101214', 'Unimed Nacional', 'Não', '000-555-666-85');

INSERT INTO Paciente (nome_pac, sobrenome_pac, genero_pac, cpf_pac, nasc_pac, tel_pac_1, cep_pac, end_pac, bairro_pac, cidade_pac, email_pac, id_convenio, status_cad_pac)
VALUES 
    ('Maria', 'Silva', 'Feminino', '123.456.789-00', '1990-03-15', '(XX)XXXXX-XXXX', '12345-678', 'Rua das Flores, 123', 'Centro', 'São Paulo', 'maria.silva@example.com', 1, 'Ativo'),
    ('José', 'Oliveira', 'Masculino', '987.654.321-00', '1985-08-20', '(XX)XXXXX-XXXX', '54321-876', 'Avenida dos Sonhos, 456', 'Jardim América', 'Rio de Janeiro', 'jose.oliveira@example.com', 2, 'Ativo'),
    ('Ana', 'Santos', 'Feminino', '456.789.123-00', '1992-11-25', '(XX)XXXXX-XXXX', '98765-432', 'Travessa das Palmeiras, 789', 'Boa Vista', 'Belo Horizonte', 'ana.santos@example.com', 3, 'Ativo');

INSERT INTO Consulta (data_cons, hora_cons, id_paciente, id_medico, tipo_pag_cons, status_pag_cons, status_cons, motivo, sintomas, observacoes, laudo)
VALUES 
    ('2024-02-18', '09:00:00', 1, 1, 'Cartão de Crédito', 'Pago', 'Concluída', 'Exame de rotina', 'Paciente se queixando de cansaço e falta de energia.', 'Nenhum', 'O paciente está em boa saúde.'),
    ('2024-02-19', '10:30:00', 2, 1, 'Dinheiro', 'Pendente', 'Agendada', 'Consulta de acompanhamento', 'Paciente relata melhora após tratamento.', 'Recomendou continuar com medicação prescrita.', 'Melhora geral observada no paciente.'),
    ('2024-02-20', '14:00:00', 3, 1, 'Convênio', 'Pago', 'Concluída', 'Consulta de emergência', 'Paciente apresenta febre alta e dor de cabeça intensa.', 'Receitou medicamentos para reduzir a febre.', 'Paciente aconselhado a repousar e monitorar a febre.');

INSERT INTO Receita (id_consulta, data_rec, L_esf_OD, L_esf_OE, L_cil_OD, L_cil_OE, L_eixo_OD, L_eixo_OE, L_dp_OD, L_dp_OE, P_esf_OD, P_esf_OE, P_cil_OD, P_cil_OE, P_eixo_OD, P_eixo_OE, P_dp_OD, P_dp_OE)
VALUES 
    (1, '2024-02-18', -2.25, -1.75, -1.50, -1.25, 180, 175, 22, 23, -1.75, -1.50, -0.75, -1.00, 5, 10, 20, 25),
    (2, '2024-02-19', -1.50, -1.25, -1.00, -0.75, 175, 170, 21, 22, -1.25, -1.00, -0.50, -0.75, 10, 15, 21, 22),
    (3, '2024-02-20', -3.00, -2.50, -1.75, -1.50, 180, 175, 23, 24, -2.50, -2.25, -1.00, -1.25, 15, 20, 25, 30);

INSERT INTO Exame (tipo_exame, valor_exame, tipo_pag_ex, status_pag_ex)
VALUES 
    ('Hemograma Completo', 150.00, 'Cartão de Crédito', 'Pago'),
    ('Ultrassonografia Abdominal', 250.00, 'Dinheiro', 'Pendente'),
    ('Radiografia de Tórax', 180.00, 'Convênio', 'Pago');
YuttyAkiko commented 5 months ago

Banco de Dados Clínica Vision

Estas imagens mostram dados fictícios, que foram inseridos nas tabelas do banco de dados da Clínica Vision usados para teste.

Tabela Cargo

Image

Tabela Consulta

Image

Tabela Convênio

Image

Tabela Especialidade

Image

Tabela Exame

Image

Tabela Funcionário

Image

Tabela Médico

Image

Tabela Paciente

Image

Tabela Receita

Image