clinica.sql

-- MySQL Script generated by MySQL Workbench
-- Thu Jun  1 23:11:28 2023
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `clinica` ;

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `clinica` DEFAULT CHARACTER SET utf8 ;
USE `clinica`;

-- -----------------------------------------------------
-- Table `mydb`.`lista_pacientes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `clinica`.`lista_pacientes` ;

CREATE TABLE IF NOT EXISTS `clinica`.`lista_pacientes` (
  `id_paciente` INT NOT NULL AUTO_INCREMENT,
  `cpf` VARCHAR(11) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `medico_cpf` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`id_paciente`),
  FOREIGN KEY (`medico_cpf`)
    REFERENCES `clinica`.`medico` (`cpf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


INSERT INTO lista_pacientes(cpf,nome,medico_cpf) VALUES('4','dudu','1');
SELECT * FROM lista_pacientes;
DELETE FROM lista_pacientes WHERE cpf = '6';


-- -----------------------------------------------------
-- Table `mydb`.`medico`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `clinica`.`medico` ;

CREATE TABLE IF NOT EXISTS `clinica`.`medico` (
  `cpf` VARCHAR(11) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `telefone` VARCHAR(11) NOT NULL,
  `dt_nasc` VARCHAR(11) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `especialidade` VARCHAR(30) NOT NULL,
  `hr_atendimento` VARCHAR(10) NOT NULL,
  `crm` INT NOT NULL,
  `senha` VARCHAR(45) NOT NULL,
  
  
  PRIMARY KEY (`cpf`))
ENGINE = InnoDB;




SELECT * FROM medico;
DELETE FROM medico WHERE cpf = '33';
UPDATE medico SET qtd_vagas = 12 WHERE cpf = '2';

-- -----------------------------------------------------
-- Table `mydb`.`guiche`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `clinica`.`guiche` ;

CREATE TABLE IF NOT EXISTS `clinica`.`guiche` (
  `id_guiche` INT NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(30) NOT NULL,
  `senha` INT NOT NULL,
  `modo` VARCHAR(20) NOT NULL,
  `recepcionista_cpf` VARCHAR(11) NOT NULL,
  
  PRIMARY KEY (`id_guiche`),
  FOREIGN KEY (`recepcionista_cpf`) 
  REFERENCES `clinica`.`recepcionista`(`cpf`))
ENGINE = InnoDB;

select * from guiche;
INSERT INTO guiche(status,senha,modo,recepcionista_cpf) VALUES('livre',0,'ativo','1');
UPDATE guiche SET status = 'livre' WHERE id_guiche = 1;


-- -----------------------------------------------------
-- Table `mydb`.`recepcionista`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `clinica`.`recepcionista` ;

CREATE TABLE IF NOT EXISTS `clinica`.`recepcionista` (
  `cpf` VARCHAR(11) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `telefone` VARCHAR(11) NOT NULL,
  `dt_nasc` VARCHAR(11) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `senha` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`cpf`))
ENGINE = InnoDB;

ALTER TABLE recepcionista DROP FOREIGN KEY fk_recepcionista_guiche1;

INSERT INTO recepcionista(cpf,nome,telefone,dt_nasc,email,senha) VALUES('3','3','3','3','3','3');
SELECT * FROM recepcionista;
DELETE FROM recepcionista WHERE cpf= '5';


-- -----------------------------------------------------
-- Table `mydb`.`consulta`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `clinica`.`consulta`;


CREATE TABLE IF NOT EXISTS `clinica`.`consulta` (
  `id_consulta` INT NOT NULL AUTO_INCREMENT,
  `cpf_paciente` VARCHAR(11) NOT NULL,
  `nome_paciente` VARCHAR(45) NOT NULL,
  `telefone` VARCHAR(20) NOT NULL,
  `dt_nasc` VARCHAR(11) NOT NULL,
  `medico` VARCHAR(45) NOT NULL,
  `crm` INT NOT NULL,
  `tipo_consulta` VARCHAR(20) NOT NULL,
  `medico_cpf` VARCHAR(11) NOT NULL,
  `recepcionista_cpf` VARCHAR(11) NOT NULL,

  PRIMARY KEY (`id_consulta`),
  INDEX `fk_consulta_medico1_idx` (`medico_cpf` ASC),
  INDEX `fk_consulta_recepcionista1_idx` (`recepcionista_cpf` ASC) ,

  CONSTRAINT `fk_consulta_medico1`
    FOREIGN KEY (`medico_cpf`)
    REFERENCES `clinica`.`medico` (`cpf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_consulta_recepcionista1`
    FOREIGN KEY (`recepcionista_cpf`)
    REFERENCES `clinica`.`recepcionista` (`cpf`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

ALTER TABLE consulta DROP FOREIGN KEY fk_consulta_recepcionista1;
SELECT * FROM consulta;
delete FROM consulta WHERE id_consulta = 2;



-- -----------------------------------------------------
-- Table `mydb`.`paciente`

-- -----------------------------------------------------
-- DROP TABLE IF EXISTS `clinica`.`paciente` ;

-- CREATE TABLE IF NOT EXISTS `clinica`.`paciente` (
--  `cpf` VARCHAR(11) NOT NULL,
--  `nome` VARCHAR(45) NOT NULL,
--  `telefone` VARCHAR(11) NOT NULL,
--  `dt_nasc` VARCHAR(11) NOT NULL,
--  `endereco` VARCHAR(45) NOT NULL,
--  `email` VARCHAR(45) NOT NULL,
--  `medico_cpf` VARCHAR(11) NOT NULL,
--  `consulta_cpf_paciente` VARCHAR(11) NOT NULL,
--  PRIMARY KEY (`cpf`),
--  INDEX `fk_paciente_medico_idx` (`medico_cpf` ASC),
--  INDEX `fk_paciente_consulta1_idx` (`consulta_cpf_paciente` ASC),
--  CONSTRAINT `fk_paciente_medico`
--    FOREIGN KEY (`medico_cpf`)
--    REFERENCES `clinica`.`medico` (`cpf`)
--    ON DELETE NO ACTION
--    ON UPDATE NO ACTION)
-- ENGINE = InnoDB;
--

-- -----------------------------------------------------
-- Table `mydb`.`admin`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `clinica`.`admin` ;

CREATE TABLE IF NOT EXISTS `clinica`.`admin` (
  `cpf_admin` VARCHAR(11) NOT NULL,
  `nome` VARCHAR(45) NOT NULL,
  `senha` VARCHAR(11) NOT NULL,
  PRIMARY KEY (`cpf_admin`))
ENGINE = InnoDB;

INSERT INTO admin(cpf_admin,nome,senha) VALUES('1','luis','1');
SELECT * FROM admin;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;