Skip to content

Instantly share code, notes, and snippets.

@sergiohdljr

sergiohdljr/Clinica.sql

Last active August 30, 2023 15:17
Download ZIP

Revisions

  1. sergiohdljr revised this gist Aug 30, 2023. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion Clinica.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -45,4 +45,16 @@ mysql> CREATE TABLE tbl_Consultas (
    -> PRIMARY KEY (codm, codp, data, hora),
    -> FOREIGN KEY (codm) REFERENCES tbl_Medicos(codm),
    -> FOREIGN KEY (codp) REFERENCES tbl_Pacientes(codp)
    -> );
    -> );

    --- Remover as colunas cargo e nroada tabela de Funcionarios
    mysql> ALTER TABLE tbl_Funcionarios
    -> ADD COLUMN nroa INT,
    -> ADD CONSTRAINT fk_nroa FOREIGN KEY (nroa) REFERENCES tbl_Ambulatorios(nroa);

    --- Crie os seguintes índices: - Medicos: CPF (único), Pacientes: doenca
    mysql> CREATE UNIQUE INDEX idx_unique_CPF_Medicos ON tbl_Medicos (CPF);
    mysql> CREATE INDEX idx_doenca_Pacientes ON tbl_Pacientes (doenca);

    --- Remover o índice doencaemPacientes
    mysql> DROP INDEX idx_doenca_Pacientes ON tbl_Pacientes;
  2. sergiohdljr revised this gist Aug 30, 2023. 1 changed file with 47 additions and 0 deletions.
    47 changes: 47 additions & 0 deletions Clinica.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1 +1,48 @@
    -- Criando Banco de Dados
    mysql> CREATE DATABASE bd_clinica;
    -- Usando Banco de Dados
    mysql> USE bd_clinica;
    -- Criando tabelas
    mysql> CREATE TABLE tbl_Ambulatorios (
    -> nroa INT PRIMARY KEY,
    -> andar NUMERIC(3) NOT NULL,
    -> capacidade SMALLINT );

    mysql> CREATE TABLE tbl_Medicos (
    -> codm INT PRIMARY KEY,
    -> nome VARCHAR(40) NOT NULL,
    -> idade SMALLINT NOT NULL,
    -> especialidade CHAR(20),
    -> CPF NUMERIC(11) UNIQUE,
    -> cidade VARCHAR(30),
    -> nroa INT,
    -> FOREIGN KEY (nroa) REFERENCES tbl_Ambulatorios(nroa));

    mysql> CREATE TABLE tbl_Pacientes (
    -> codp INT PRIMARY KEY,
    -> nome VARCHAR(40) NOT NULL,
    -> idade SMALLINT NOT NULL,
    -> cidade CHAR(30),
    -> CPF NUMERIC(11) UNIQUE,
    -> doenca VARCHAR(40) NOT NULL
    -> );

    mysql> CREATE TABLE tbl_Funcionarios (
    -> codf INT PRIMARY KEY,
    -> nome VARCHAR(40) NOT NULL,
    -> idade SMALLINT,
    -> CPF NUMERIC(11) UNIQUE,
    -> cidade VARCHAR(30),
    -> salario NUMERIC(10),
    -> cargo VARCHAR(20)
    -> );

    mysql> CREATE TABLE tbl_Consultas (
    -> codm INT,
    -> codp INT,
    -> data DATE,
    -> hora TIME,
    -> PRIMARY KEY (codm, codp, data, hora),
    -> FOREIGN KEY (codm) REFERENCES tbl_Medicos(codm),
    -> FOREIGN KEY (codp) REFERENCES tbl_Pacientes(codp)
    -> );
  3. sergiohdljr created this gist Aug 30, 2023.
    1 change: 1 addition & 0 deletions Clinica.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1 @@
    mysql> CREATE DATABASE bd_clinica;