Trabalho BD

-- =====================================
-- 1. CRIAÇÃO DAS TABELAS
-- =====================================

-- Tabela Partido
CREATE TABLE Partido (
numero_tse INT PRIMARY KEY,
nome_oficial VARCHAR(255) NOT NULL,
sigla VARCHAR(10) NOT NULL,
cnpj VARCHAR(14) UNIQUE NOT NULL
);

-- Tabela Candidato
CREATE TABLE Candidato (

numero_registro INT PRIMARY KEY,
nome_completo VARCHAR(255) NOT NULL,
cpf VARCHAR(11) UNIQUE NOT NULL,
data_nascimento DATE NOT NULL,
filiacao_partido INT NOT NULL,
FOREIGN KEY (filiacao_partido) REFERENCES Partido(numero_tse)

);

-- Tabela Coligação
CREATE TABLE Coligacao (
id_coligacao INT PRIMARY KEY,
data_inicio DATE NOT NULL,
data_termino DATE NOT NULL
);

-- Tabela associativa ColigacaoPartido (Corrigindo atributo multivalorado)
CREATE TABLE ColigacaoPartido (
id_coligacao INT NOT NULL,
numero_tse INT NOT NULL,
PRIMARY KEY (id_coligacao, numero_tse),
FOREIGN KEY (id_coligacao) REFERENCES Coligacao(id_coligacao),
FOREIGN KEY (numero_tse) REFERENCES Partido(numero_tse)
);

-- Tabela Zona Eleitoral
CREATE TABLE ZonaEleitoral (
codigo_zona INT PRIMARY KEY,
descricao_zona VARCHAR(255) NOT NULL
);

-- Tabela Urna
CREATE TABLE Urna (
id_urna INT PRIMARY KEY,
codigo_zona INT NOT NULL,
FOREIGN KEY (codigo_zona) REFERENCES ZonaEleitoral(codigo_zona)
);

-- Tabela Seção Eleitoral
CREATE TABLE SecaoEleitoral (
codigo_secao VARCHAR(50) PRIMARY KEY,
endereco_fisico VARCHAR(255) NOT NULL,
id_urna INT NOT NULL,
FOREIGN KEY (id_urna) REFERENCES Urna(id_urna)
);

-- Tabela Mesário (Corrigindo atributo multivalorado)
CREATE TABLE Mesario (
cpf_mesario VARCHAR(11) PRIMARY KEY,
nome_completo VARCHAR(255) NOT NULL
);

-- Tabela associativa SecaoMesario
CREATE TABLE SecaoMesario (
codigo_secao VARCHAR(50) NOT NULL,
cpf_mesario VARCHAR(11) NOT NULL,
PRIMARY KEY (codigo_secao, cpf_mesario),
FOREIGN KEY (codigo_secao) REFERENCES SecaoEleitoral(codigo_secao),
FOREIGN KEY (cpf_mesario) REFERENCES Mesario(cpf_mesario)
);

-- Tabela Eleitor (Removendo endereço multivalorado)
CREATE TABLE Eleitor (
titulo_eleitoral VARCHAR(20) PRIMARY KEY,
nome_completo VARCHAR(255) NOT NULL,
cpf VARCHAR(11) UNIQUE NOT NULL,
data_nascimento DATE NOT NULL,
codigo_zona INT NOT NULL,
codigo_secao VARCHAR(50) NOT NULL,
FOREIGN KEY (codigo_zona) REFERENCES ZonaEleitoral(codigo_zona),
FOREIGN KEY (codigo_secao) REFERENCES SecaoEleitoral(codigo_secao)
);

-- Tabela Endereco
CREATE TABLE Endereco (
id_endereco SERIAL PRIMARY KEY,
titulo_eleitoral VARCHAR(20) NOT NULL,
logradouro VARCHAR(255) NOT NULL,
numero VARCHAR(10) NOT NULL,
complemento VARCHAR(255),
bairro VARCHAR(100) NOT NULL,
cidade VARCHAR(100) NOT NULL,
estado VARCHAR(2) NOT NULL,
cep VARCHAR(10) NOT NULL,
FOREIGN KEY (titulo_eleitoral) REFERENCES Eleitor(titulo_eleitoral)
);

-- Tabela Voto (Adicionando tipo de voto)
CREATE TABLE Voto (
id_voto SERIAL PRIMARY KEY,
titulo_eleitoral VARCHAR(20) NOT NULL,
numero_candidato INT,
tipo_voto VARCHAR(10) NOT NULL CHECK (tipo_voto IN ('valido', 'branco', 'nulo')),
data_hora TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
codigo_secao VARCHAR(50) NOT NULL,
FOREIGN KEY (titulo_eleitoral) REFERENCES Eleitor(titulo_eleitoral),
FOREIGN KEY (numero_candidato) REFERENCES Candidato(numero_registro),
FOREIGN KEY (codigo_secao) REFERENCES SecaoEleitoral(codigo_secao)
);

-- =====================================
-- 2. INSERÇÃO DE DADOS DE EXEMPLO
-- =====================================

-- Inserir partidos
INSERT INTO Partido (numero_tse, nome_oficial, sigla, cnpj) VALUES
(123, 'Partido Democrático', 'PD', '12345678901234'),
(456, 'Partido Republicano', 'PR', '98765432109876');

-- Inserir candidatos
INSERT INTO Candidato (numero_registro, nome_completo, cpf, data_nascimento, filiacao_partido) VALUES
(1, 'João Silva', '12345678901', '1980-01-01', 123),
(2, 'Maria Oliveira', '98765432101', '1985-05-15', 456);

-- Inserir coligações
INSERT INTO Coligacao (id_coligacao, data_inicio, data_termino) VALUES
(1, '2023-01-01', '2023-12-31');

-- Associar partidos à coligação
INSERT INTO ColigacaoPartido (id_coligacao, numero_tse) VALUES
(1, 123), (1, 456);

-- Inserir zonas eleitorais
INSERT INTO ZonaEleitoral (codigo_zona, descricao_zona) VALUES
(1, 'Zona Sul'),
(2, 'Zona Norte');

-- Inserir urnas
INSERT INTO Urna (id_urna, codigo_zona) VALUES
(1, 1),
(2, 2);

-- Inserir seções eleitorais
INSERT INTO SecaoEleitoral (codigo_secao, endereco_fisico, id_urna) VALUES
('1-001', 'Escola Municipal', 1),
('2-001', 'Centro Comunitário', 2);

-- Inserir mesários
INSERT INTO Mesario (cpf_mesario, nome_completo) VALUES
('11122233344', 'João Mesário'),
('55566677788', 'Maria Mesária');

-- Associar mesários às seções eleitorais
INSERT INTO SecaoMesario (codigo_secao, cpf_mesario) VALUES
('1-001', '11122233344'),
('1-001', '55566677788');

-- Inserir eleitores
INSERT INTO Eleitor (titulo_eleitoral, nome_completo, cpf, data_nascimento, codigo_zona, codigo_secao) VALUES
('123456789012', 'Carlos Souza', '11122233344', '1990-03-20', 1, '1-001'),
('987654321098', 'Ana Lima', '55566677788', '1988-07-10', 2, '2-001');

-- Inserir endereços
INSERT INTO Endereco (titulo_eleitoral, logradouro, numero, bairro, cidade, estado, cep) VALUES
('123456789012', 'Rua das Flores', '123', 'Centro', 'São Paulo', 'SP', '01010-000'),
('987654321098', 'Avenida Central', '456', 'Centro', 'Rio de Janeiro', 'RJ', '20020-000');

-- Inserir votos
INSERT INTO Voto (titulo_eleitoral, numero_candidato, tipo_voto, codigo_secao) VALUES
('123456789012', 1, 'valido', '1-001'),
('987654321098', NULL, 'branco', '2-001');

-- =====================================
-- 3. CONSULTAS DE EXEMPLO
-- =====================================

-- Listar candidatos e seus partidos
-- =====================================
-- 1. CONSULTA: Listar todos os eleitores e seus respectivos endereços
-- =====================================
SELECT e.nome_completo AS Eleitor, en.logradouro, en.numero, en.bairro, en.cidade, en.estado, en.cep
FROM Eleitor e
JOIN Endereco en ON e.titulo_eleitoral = en.titulo_eleitoral;

-- =====================================
-- 2. CONSULTA: Listar todas as seções eleitorais e os mesários responsáveis por cada uma
-- =====================================
SELECT se.codigo_secao, se.endereco_fisico, m.nome_completo AS Mesario
FROM SecaoEleitoral se
JOIN SecaoMesario sm ON se.codigo_secao = sm.codigo_secao
JOIN Mesario m ON sm.cpf_mesario = m.cpf_mesario;

-- =====================================
-- 3. CONSULTA: Contar o número de votos válidos, brancos e nulos por seção eleitoral
-- =====================================
SELECT codigo_secao, tipo_voto, COUNT(id_voto) AS Total_Votos
FROM Voto
GROUP BY codigo_secao, tipo_voto;

-- =====================================
-- 4. CONSULTA: Listar todos os candidatos e a coligação a qual pertencem (se houver)
-- =====================================
SELECT c.nome_completo AS Candidato, p.nome_oficial AS Partido, co.id_coligacao
FROM Candidato c
JOIN Partido p ON c.filiacao_partido = p.numero_tse
LEFT JOIN ColigacaoPartido cp ON p.numero_tse = cp.numero_tse
LEFT JOIN Coligacao co ON cp.id_coligacao = co.id_coligacao;

-- =====================================
-- 5. CONSULTA: Listar todos os eleitores que votaram em uma determinada seção eleitoral
-- =====================================
SELECT e.nome_completo AS Eleitor, v.codigo_secao
FROM Eleitor e
JOIN Voto v ON e.titulo_eleitoral = v.titulo_eleitoral
WHERE v.codigo_secao = '1-001'; -- Substitua '1-001' pela seção desejada