exemplo_09

DROP SCHEMA IF EXISTS trabalho;
CREATE SCHEMA trabalho;

USE trabalho;

SET SQL_SAFE_UPDATES = 0;

CREATE TABLE pessoa ( id INT(4) PRIMARY KEY AUTO_INCREMENT , nome VARCHAR(100) NOT NULL , cidade VARCHAR(100) NOT NULL , cpf CHAR(14) NOT NULL UNIQUE, rg INT(4) NOT NULL UNIQUE );
CREATE UNIQUE INDEX idx_cpf ON pessoa(cpf);
CREATE UNIQUE INDEX idx_rg ON pessoa(rg);
CREATE INDEX idx_nome ON pessoa(nome);

CREATE TABLE medico( pessoa_id INT PRIMARY KEY, especialidade VARCHAR(100) NOT NULL, FOREIGN KEY (pessoa_id) REFERENCES pessoa(id) ON DELETE RESTRICT ON UPDATE CASCADE );

CREATE TABLE paciente( pessoa_id INT PRIMARY KEY, doenca VARCHAR(100) NOT NULL, FOREIGN KEY (pessoa_id) REFERENCES pessoa(id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);

CREATE TABLE consulta( medico_id INT NOT NULL, paciente_id INT NOT NULL, data_hora DATETIME NOT NULL,
PRIMARY KEY(medico_id, paciente_id, data_hora),

FOREIGN KEY (medico_id) REFERENCES medico(pessoa_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,

FOREIGN KEY (paciente_id) REFERENCES paciente(pessoa_id)
ON UPDATE CASCADE
ON DELETE RESTRICT

);
CREATE UNIQUE INDEX idx_medico_data_hora ON consulta (medico_id, data_hora);
CREATE UNIQUE INDEX idx_paciente_data_hora ON consulta (paciente_id, data_hora);

CREATE TABLE conta( id INT PRIMARY KEY AUTO_INCREMENT, numero INT(11) NOT NULL, agencia INT(11) NOT NULL, saldo FLOAT NOT NULL, pessoa_id INT NOT NULL,

FOREIGN KEY (pessoa_id) REFERENCES pessoa(id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
CREATE UNIQUE INDEX idx_numero_agencia ON conta(numero, agencia);

INSERT INTO pessoa (nome, cidade,cpf, rg) VALUES
('Arthur Barbosa', 'Poço Branco', '111.111.111-11', 2222);
INSERT INTO medico (pessoa_id, especialidade) VALUES
(LAST_INSERT_ID(), 'Anestegiologia');
INSERT INTO pessoa (nome, cidade,cpf, rg) VALUES
('Anna Souza', 'Natal', '999.999.999-99', 1234);
INSERT INTO medico (pessoa_id,