Preparatório Caixa TI: Banco de Dados relacional e modelagem ER

Conceitos relacionais fundamentais

Entidades

Entidade é um “objeto” do domínio sobre o qual se deseja armazenar dados. Em um contexto bancário, exemplos comuns são Cliente, Conta, Agência, Transação e Cartão. Na modelagem ER (Entidade-Relacionamento), entidades costumam representar conjuntos (tipos) e cada ocorrência é uma instância (um cliente específico, uma conta específica).

Atributos

Atributos são características das entidades (ou de relacionamentos). Exemplos: Cliente(nome, CPF, data_nascimento), Conta(numero, saldo, data_abertura). Atributos podem ser:

• Simples: indivisíveis (CPF).
• Compostos: podem ser decompostos (endereço → logradouro, número, CEP).
• Multivalorados: mais de um valor por instância (telefones). Em modelo relacional, normalmente viram uma tabela separada.
• Derivados: calculados a partir de outros (idade derivada de data_nascimento). Em geral não se armazena, calcula-se quando necessário.

Relacionamentos

Relacionamento descreve como entidades se associam. Ex.: Cliente possui Conta; Conta ocorre em Agência; Conta realiza Transação. Relacionamentos podem ter atributos, como em “Conta realiza Transação” com atributo valor, data_hora, tipo.

Cardinalidade e participação

Cardinalidade indica quantas ocorrências de uma entidade podem se associar a outra:

• 1:1: uma Agência tem um Gerente responsável e um Gerente responde por uma Agência (exemplo didático).
• 1:N: uma Agência possui várias Contas; cada Conta pertence a uma Agência.
• N:N: um Cliente pode ter várias Contas e uma Conta pode ter vários Clientes (conta conjunta).

Além da cardinalidade, é importante a participação (obrigatória ou opcional). Ex.: uma Conta deve pertencer a uma Agência (obrigatória), mas um Cliente pode ainda não ter Conta (opcional), dependendo da regra de negócio.

Continue em nosso aplicativo

Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.

ou continue lendo abaixo...

Baixar o aplicativo

Chaves: primária, candidata e estrangeira

No modelo relacional, uma tabela representa um conjunto de tuplas (linhas). Para identificar unicamente cada linha, usamos chaves:

• Chave candidata: atributo(s) que identificam unicamente uma tupla e podem ser escolhidos como identificador. Ex.: em Cliente, CPF pode ser chave candidata; em Conta, número da conta pode ser chave candidata.
• Chave primária (PK): uma das chaves candidatas escolhida como identificador principal. Ex.: Cliente(CPF) como PK.
• Chave estrangeira (FK): atributo(s) que referenciam a PK (ou chave única) de outra tabela, representando relacionamento. Ex.: Conta(cod_agencia) referencia Agencia(cod_agencia).

Integridade referencial

Integridade referencial garante que um valor de FK em uma tabela exista na tabela referenciada. Ex.: não pode existir uma Conta com cod_agencia=999 se não existir Agência 999. Em provas, é comum cobrar efeitos de operações:

• Inserção: inserir Conta com FK inexistente viola integridade.
• Atualização: mudar cod_agencia para valor inexistente viola integridade.
• Exclusão: excluir uma Agência referenciada por Contas pode violar integridade, a menos que haja regra (RESTRICT, CASCADE, SET NULL).

Modelagem conceitual (ER): como construir

Passo a passo prático

• 1) Levantar requisitos: identifique dados a armazenar e regras (ex.: conta pode ser conjunta; transação pertence a uma conta; agência possui contas).
• 2) Identificar entidades: substantivos relevantes (Cliente, Conta, Agência, Transação).
• 3) Definir atributos: para cada entidade, liste atributos e identifique possíveis chaves candidatas (CPF, número da conta).
• 4) Definir relacionamentos: verbos/associações (Cliente possui Conta; Conta pertence a Agência; Conta realiza Transação).
• 5) Definir cardinalidades e participação: 1:N, N:N, obrigatória/opcional.
• 6) Tratar atributos multivalorados e compostos: telefones → entidade/tabela própria; endereço composto → pode ser decomposto.
• 7) Validar com cenários: teste mental com exemplos reais (cliente sem conta? conta com dois titulares? transação sem conta?).

Exemplo conceitual (ER) em texto

Considere as regras:

• Um Cliente é identificado por CPF e pode ter zero ou mais Contas.
• Uma Conta é identificada por numero_conta e pertence a exatamente uma Agência.
• Uma Conta pode ter um ou mais Clientes (conta conjunta).
• Uma Conta registra várias Transações; cada Transação pertence a uma única Conta.

Entidades e atributos (exemplo):

• CLIENTE(CPF, nome, data_nascimento)
• AGENCIA(cod_agencia, nome, cidade)
• CONTA(numero_conta, data_abertura, saldo)
• TRANSACAO(id_transacao, data_hora, tipo, valor)

Relacionamentos:

• CLIENTE —(titularidade)— CONTA: N:N (pois conta conjunta é permitida)
• AGENCIA —(possui)— CONTA: 1:N
• CONTA —(gera)— TRANSACAO: 1:N

Transformação ER → modelo lógico relacional (regras claras)

Regras de mapeamento

• Entidade forte → tabela: cada entidade vira uma tabela com seus atributos; a PK é a chave escolhida.
• Relacionamento 1:N: a PK do lado 1 vira FK no lado N.
• Relacionamento 1:1: pode-se colocar FK em uma das tabelas (preferencialmente no lado de participação total) e impor UNIQUE; ou fundir tabelas se fizer sentido.
• Relacionamento N:N: cria-se uma tabela associativa com FKs para as duas entidades; a PK costuma ser composta pelas FKs (ou uma chave substituta, mantendo UNIQUE nas FKs).
• Relacionamento com atributos: atributos do relacionamento vão para a tabela do relacionamento (especialmente em N:N) ou para o lado N (em 1:N) quando o relacionamento é implementado por FK.
• Atributo multivalorado: cria-se tabela separada com FK para a entidade “dona” do atributo; PK pode ser composta (PK da entidade + valor do atributo) ou um identificador.

Aplicando ao exemplo (tabelas resultantes)

Mapeamento das entidades:

CLIENTE(CPF PK, nome, data_nascimento)AGENCIA(cod_agencia PK, nome, cidade)CONTA(numero_conta PK, data_abertura, saldo, cod_agencia FK → AGENCIA.cod_agencia)TRANSACAO(id_transacao PK, data_hora, tipo, valor, numero_conta FK → CONTA.numero_conta)

Mapeamento do relacionamento N:N (titularidade):

TITULARIDADE(CPF FK → CLIENTE.CPF, numero_conta FK → CONTA.numero_conta, PK(CPF, numero_conta))

Observe como:

• AGENCIA—CONTA (1:N) foi resolvido com cod_agencia como FK em CONTA.
• CLIENTE—CONTA (N:N) exigiu a tabela associativa TITULARIDADE.
• CONTA—TRANSACAO (1:N) foi resolvido com numero_conta como FK em TRANSACAO.

Checando integridade referencial no modelo lógico

Exemplos de regras implicadas:

• Não existe CONTA sem AGENCIA válida (CONTA.cod_agencia deve existir em AGENCIA).
• Não existe TRANSACAO sem CONTA válida (TRANSACAO.numero_conta deve existir em CONTA).
• Não existe TITULARIDADE apontando para CLIENTE ou CONTA inexistentes.

Normalização: 1FN, 2FN, 3FN com exemplos e anomalias

Por que normalizar

Normalização organiza tabelas para reduzir redundância e evitar anomalias:

• Anomalia de inserção: dificuldade para inserir um dado sem outro.
• Anomalia de atualização: mesmo dado repetido em vários lugares, exigindo múltiplas atualizações.
• Anomalia de exclusão: ao excluir um registro, perde-se informação não relacionada ao motivo da exclusão.

1FN (Primeira Forma Normal)

Regra prática: atributos devem ser atômicos (sem listas/repetições em uma coluna) e não deve haver grupos repetitivos.

Exemplo não normalizado (telefones em uma coluna):

CLIENTE(CPF, nome, telefones)123, Ana, (11)9999-0000; (11)9888-0000

Problemas: difícil pesquisar por um telefone específico; atualizações e validações ficam inconsistentes.

Em 1FN (telefones em tabela própria):

CLIENTE(CPF PK, nome)CLIENTE_TELEFONE(CPF FK → CLIENTE.CPF, telefone, PK(CPF, telefone))

2FN (Segunda Forma Normal)

Aplica-se quando a tabela tem chave primária composta. Regra prática: todo atributo não-chave deve depender da chave inteira, e não apenas de parte dela (evitar dependência parcial).

Exemplo: suponha uma tabela de titularidade com dados do cliente repetidos:

TITULARIDADE(CPF, numero_conta, nome_cliente, data_abertura_conta)PK(CPF, numero_conta)

Dependências problemáticas:

• nome_cliente depende só de CPF (parte da chave).
• data_abertura_conta depende só de numero_conta (parte da chave).

Anomalias:

• Atualização: mudar nome do cliente exige atualizar várias linhas (uma por conta).
• Inserção: para cadastrar cliente sem conta, não há onde inserir (se só existir essa tabela).
• Exclusão: ao apagar a última conta de um cliente, pode-se perder o nome do cliente.

Decomposição para 2FN:

CLIENTE(CPF PK, nome_cliente)CONTA(numero_conta PK, data_abertura_conta)TITULARIDADE(CPF FK → CLIENTE.CPF, numero_conta FK → CONTA.numero_conta, PK(CPF, numero_conta))

3FN (Terceira Forma Normal)

Regra prática: não deve haver dependência transitiva de atributo não-chave em relação à chave. Em outras palavras, um atributo não-chave não deve depender de outro atributo não-chave.

Exemplo: tabela de conta guardando dados da agência:

CONTA(numero_conta PK, saldo, cod_agencia, nome_agencia)

Se cod_agencia → nome_agencia, então nome_agencia depende de numero_conta via cod_agencia (dependência transitiva). Isso gera:

• Anomalia de atualização: renomear agência exige atualizar todas as contas daquela agência.
• Anomalia de inserção: para cadastrar agência sem conta, não há onde inserir.
• Anomalia de exclusão: ao excluir a última conta de uma agência, perde-se o nome da agência.

Decomposição para 3FN:

AGENCIA(cod_agencia PK, nome_agencia)CONTA(numero_conta PK, saldo, cod_agencia FK → AGENCIA.cod_agencia)

Exercícios (modelagem e conversão ER → relacional) com justificativas

Exercício 1: ER e conversão

Enunciado: Um sistema registra Funcionários e Agências. Cada Funcionário trabalha em exatamente uma Agência. Uma Agência pode ter vários Funcionários. Para cada Funcionário, guardar matrícula (única), nome e cargo. Para cada Agência, guardar código (único), nome e cidade.

Peça: (a) defina entidades, relacionamento e cardinalidade; (b) converta para modelo relacional.

Resposta esperada (justificativa):

• Entidades: AGENCIA, FUNCIONARIO.
• Relacionamento: AGENCIA 1:N FUNCIONARIO (uma agência tem muitos funcionários; funcionário em uma agência).
• Conversão: em 1:N, a PK do lado 1 vira FK no lado N.

AGENCIA(cod_agencia PK, nome, cidade)FUNCIONARIO(matricula PK, nome, cargo, cod_agencia FK → AGENCIA.cod_agencia)

Exercício 2: N:N com atributos

Enunciado: Clientes podem contratar Produtos (ex.: seguro, consórcio). Um Cliente pode contratar vários Produtos e um Produto pode ser contratado por vários Clientes. No ato da contratação, registrar data_contratacao e status.

Peça: modele e converta para relacional.

Resposta esperada (justificativa):

• Relacionamento é N:N, portanto exige tabela associativa.
• Como há atributos no relacionamento (data_contratacao, status), eles ficam na tabela associativa.

CLIENTE(cpf PK, nome)PRODUTO(id_produto PK, nome_produto)CONTRATACAO(cpf FK → CLIENTE.cpf, id_produto FK → PRODUTO.id_produto, data_contratacao, status, PK(cpf, id_produto))

Exercício 3: normalização e anomalias

Enunciado: Considere a tabela:

CONTA_AGENCIA(numero_conta PK, saldo, cod_agencia, nome_agencia, cidade_agencia)

Peça: (a) identifique dependências e anomalias; (b) normalize até 3FN.

Resposta esperada (justificativa):

• Dependência: cod_agencia → (nome_agencia, cidade_agencia). Logo, (nome_agencia, cidade_agencia) dependem transitivamente de numero_conta via cod_agencia.
• Anomalias: atualização (alterar nome/cidade da agência em várias linhas), inserção (não cadastra agência sem conta), exclusão (perde dados da agência ao excluir última conta).
• Decomposição 3FN: separar AGENCIA e manter FK em CONTA.

AGENCIA(cod_agencia PK, nome_agencia, cidade_agencia)CONTA(numero_conta PK, saldo, cod_agencia FK → AGENCIA.cod_agencia)

Exercício 4: conversão completa com conta conjunta e telefone

Enunciado: Modelar um sistema com: Cliente (CPF, nome) e seus Telefones (múltiplos). Conta (numero, saldo). Um Cliente pode ser titular de várias Contas e uma Conta pode ter vários Clientes. Cada Conta pertence a uma Agência (cod, nome).

Peça: (a) indique cardinalidades; (b) gere o esquema relacional com PKs e FKs; (c) justifique a criação de tabelas associativas.

Resposta esperada (justificativa):

• Cliente—Conta é N:N (conta conjunta), então precisa de tabela associativa.
• Cliente—Telefone é 1:N (atributo multivalorado), então precisa de tabela própria para cumprir 1FN.
• Agência—Conta é 1:N, então FK em Conta.

CLIENTE(cpf PK, nome)CLIENTE_TELEFONE(cpf FK → CLIENTE.cpf, telefone, PK(cpf, telefone))AGENCIA(cod_agencia PK, nome)CONTA(numero_conta PK, saldo, cod_agencia FK → AGENCIA.cod_agencia)TITULARIDADE(cpf FK → CLIENTE.cpf, numero_conta FK → CONTA.numero_conta, PK(cpf, numero_conta))