Aula 07. Informática para Auditor Fiscal do ICMS-RJ Prof. Arthur Mendonça. 1 de 72 Prof. Arthur Mendonça Aula 07

Aula 07

Informática para Auditor Fiscal do ICMS-RJ

Prof. Arthur Mendonça

Sumário

SUMÁRIO 2

INTRODUÇÃO 3

TEORIA DA AULA 4

OMODELO RELACIONAL 4

Relações 4

Tuplas 5

Atributos 6

Domínio 8

CHAVES 9

Chave Primária 10

Chave Estrangeira 11

Chave Candidata 13

RELACIONAMENTOS 14

Autorrelacionamentos 18

INTEGRIDADE,VIEWS E NORMALIZAÇÃO 19

Integridade 19

Índices 20

Views (visões) 22

Normalização 25

QUESTÕES COMENTADAS PELO PROFESSOR 29

LISTA DE QUESTÕES COMENTADAS 54

GABARITO 68

RESUMO DIRECIONADO 69

BIBLIOGRAFIA 72

Introdução

Olá, caro aluno! Bem-vindo a mais uma aula de Informática para o ICMS/RJ. Na aula de hoje vamos contemplar o seguinte assunto, que tem boas chances de estar presente no seu edital:

6. Modelo relacional de dados; 7. Conceitos de Tabelas, views, chaves primárias e estrangeiras.

Ao final da aula, encerraremos a nossa visão geral a respeito desses bancos de dados convencionais, que estão presentes nos mais diversos negócios e aplicações. O assunto de hoje é complementar ao da última aula e é bem importante, aparecendo bastante em provas da área fiscal.

Vamos lá?

Teoria da Aula

O Modelo Relacional

Já vimos o básico a respeito de bancos de dados em sentido amplo na última aula, mas ainda restou a dúvida:

o que é um banco de dados relacional?

O modelo relacional foi introduzido por um americano chamado Ted Codd (1970). Esse modelo de dados se baseia em coisas complicadas como teoria dos conjuntos e lógica de predicados para formalizar os conceitos básicos, mas vou tentar apresentar de uma maneira mais simples e didática para você.

O modelo relacional apresenta três características marcantes:

1. Os dados estão sempre armazenados em tabelas;

2. Há um conjunto de restrições de integridade em relação a essas tabelas;

3. Há o uso de operadores relacionais para trabalhar com as tabelas de dados, que são implementados na linguagem SQL.

A seguir, vamos começar falando um pouco sobre cada um dos principais componentes de um BD relacional.

Você vai ver que diversos dos conceitos que mostraremos já foram trabalhados na aula anterior. A diferença é que, na aula anterior, falamos de tudo no nível de modelo conceitual, de forma mais abstrata. Hoje vamos ter um enfoque em como cada um desses conceitos está representado nas tabelas de um banco de dados.

Relações

Os bancos de dados são exemplos de coleções de dados estruturados. Isso acontece porque os dados estão armazenados em estruturas fixas, que seguem um esquema predefinido. Com isso em mente, temos que os bancos de dados relacionais recebem esse nome porque os dados ficam armazenados em relações.

Essas relações de um BD relacional são frequentemente chamadas de tabelas. Trata-se de estruturas fixas compostas por linhas e colunas, onde cada linha é chamada de tupla e cada coluna corresponde a um atributo.

Numa tabela de clientes, por exemplo, cada tupla diz respeito a um registro de um determinado cliente do mundo real e o valor presente em cada atributo guarda relação com alguma característica desse cliente.

É mais ou menos assim:

Como acabamos de ver, as tuplas são as linhas ou registros dentro de uma relação em um BD relacional.

Portanto, de acordo com o modelo matemático que serve de base para a modelagem relacional, uma relação pode ser definida formalmente como sendo um conjunto não ordenado de tuplas.

As relações têm uma propriedade chamada grau. O grau de uma relação é dado pela quantidade de atributos de suas tuplas. A relação que mostramos acima tem quatro colunas ou atributos, portanto tem grau 4. Esse grau, teoricamente, pode variar de 0 a N. Na prática, os SGBDs costumam impedir que sejam criadas tabelas sem colunas, de grau 0. Para fins de prova, considere a teoria mesmo.

Observe que as relações geralmente guardam alguma similaridade com o conceito de entidade na modelagem conceitual.

De fato, as entidades costumam ser implementadas diretamente em tabelas dos bancos de dados relacionais, cada entidade na sua tabela.

Contudo, essa “tradução” do modelo conceitual para o físico nem sempre é exata, já que existem tabelas em um banco de dados relacional que não têm correspondência com nenhuma entidade específica do modelo conceitual. Da mesma maneira, nem todos os atributos do modelo conceitual viram algum atributo específico de uma tabela.

Essas diferenças se devem ao fato de que o modelo conceitual se preocupa muito mais em representar os aspectos do mundo real, do negócio, do que em mostrar como os dados estarão representados no banco de dados.

Tuplas

Por sua vez, as tuplas podem ser definidas como sendo os conjuntos de valores de atributos que compõem uma relação. Assim, a primeira tupla da nossa tabela de exemplo mais acima seria o conjunto Tupla1 = {CPF:

204.776.124-22, Nome: José da Silva, Dt. Nascimento: 01/05/1990, Cidade: Rio de Janeiro}.

Em uma relação, vários ordenamentos de tuplas são possíveis sem que se descaracterize sua natureza original. Na nossa tabela de exemplo, poderíamos ordenar os clientes pelo número do CPF, pela data de nascimento, pela ordem alfabética do nome ou pela cidade, mas mesmo com essas ordenações diferentes, os objetos resultantes continuariam sendo considerados a mesma relação.

Entenda que, como os registros de um banco de dados estão fisicamente armazenados em um disco, eles naturalmente vão seguir alguma ordem de armazenamento: primeiro inserimos um registro, depois outro, depois outro e assim sucessivamente. Contudo, como apontam Elmasri & Navathe (2011), uma relação não é “sensível” a

Tuplas

Atributos

Relação

essa ordem. Se essas linhas estivessem armazenadas no disco em qualquer outra ordem, a tabela seria considerada a mesma.

Assim, grave que, tanto no modelo relacional formal (teórico) quanto na prática, a ordem das tuplas (linhas) em uma relação (tabela) não é relevante.

Para resumir o que mais precisamos saber a respeito das tuplas, temos que o autor C. J. Date (2004) definiu algumas propriedades interessantes que podem ser alvo de cobrança na sua prova:

• Toda tupla contém exatamente um valor para cada um de seus atributos;

• Não há ordenação da esquerda pra direita nos componentes de uma tupla. Isso ocorre porque uma tupla é um conjunto de componentes (os valores de atributos), e conjuntos em matemática não possuem ordenação em seus elementos.

• Todo subconjunto de uma tupla é uma tupla.

• O grau de uma tupla varia de 0 – n, onde n é um número inteiro. O grau é dado pela quantidade de atributos que compõem a tupla.

Essa segunda propriedade, a respeito da ordenação dos componentes da tupla, vale somente para o modelo formal. Quando estivermos falando da prática nos SGBDs relacionais, a história será outra. Veremos mais em breve.

Atributos

Agora que você sabe que cada tupla é um conjunto de valores de atributos nomeados, grave que, enquanto as relações representam as entidades do mundo real, os atributos dizem respeito às características dessas entidades.

Assim como as relações, cada atributo de uma relação tem um nome único. Não poderíamos ter duas colunas chamadas “Cidade” em uma mesma relação. Isso traria grandes complicações posteriores para acessar os dados e poderia comprometer a integridade do nosso BD.

Veja os tipos de atributos existentes a seguir:

Atributos multivalorados

Vimos nas propriedades das tuplas que cada valor de atributo em cada tupla deve ser atômico, ou seja, deve carregar exatamente um valor, nem mais, nem menos. Isso está correto e funciona assim mesmo na prática, mas você precisa lembrar que existe também o conceito de atributos multivalorados.

Esses atributos que possuem mais de um valor existem nos bancos de dados relacionais, mas sem quebrar a regra da atomicidade que acabamos de ver. Na prática, a representação de um atributo multivalorado requer que se crie uma tabela auxiliar para guardar os múltiplos valores. Por exemplo, é muito comum que uma pessoa possua mais de um número de telefone, não é? Para representar essa situação, podemos criar a seguinte tabela

“Telefones”, que faz referência à tabela principal “Clientes” através do número de CPF de cada um.

CPFCliente DDD Telefone

123.980.771-15 11 2020-1010 123.980.771-15 11 99123-4567 450.689.123-04 81 3756-4321

Observe que o mesmo cliente (identificado pelo seu CPF) pode possuir mais de um telefone, mas sem que isso quebre a regra da atomicidade. Cada número de telefone está registrado em uma linha diferente dessa tabela auxiliar. Quando precisarmos consultar o(s) número(s) de um cliente, é só procurarmos pelo seu CPF na tabela que serão retornados os números correspondentes. Entendeu?

Atributos compostos

Além dos atributos multivalorados, temos os atributos compostos, que são aqueles que estão espalhados por mais de uma coluna. O exemplo mais comum desse tipo de atributo é o endereço, que costuma estar dividido em partes como logradouro, número, complemento, cidade, etc. Veja que o conjunto dos vários atributos corresponde a uma só informação do mundo real:

CPFCliente Logradouro Número Complemento CEP

204.776.124-22 Av. Paulista 1578 Bloco B 01311-929

450.689.123-04 Av. Cais do Apolo 925 NULL 50030-908

123.980.771-15 Rua Afonso Cavalcanti 455 Sala 10 20211-200

Um atributo pode ser ao mesmo tempo composto e multivalorado, nada impede que isso aconteça. No nosso exemplo de atributo multivalorado, aquele dos números de telefone, se considerarmos o DDD como parte integrante do número, esses dois campos também poderiam ser considerados um atributo composto.

Repare que isso não tem muito efeito prático em um banco de dados, já que os atributos compostos são representados por diversos atributos individuais. Essa classificação é algo mais conceitual, da etapa de modelagem, e é representada no Diagrama Entidade-Relacionamento (DER) através do desmembramento de um atributo em outros.

Mais propriedades

Como você viu, os valores dos atributos em uma relação nunca são vazios. Sempre há exatamente um valor para cada atributo em cada tupla. Para representar os casos onde o valor é desconhecido ou não é aplicável, se utiliza um valor especial chamado NULL (nulo).

No final das contas, é possível afirmar que uma tupla sempre contém o conjunto de todos os atributos presentes na relação, mas que alguns desses atributos podem assumir valores nulos, a depender que foi definido no projeto do BD.

Por fim, ao contrário da ordem das linhas em uma tabela, a ordem das colunas (atributos) em uma tabela é relevante para o banco de dados. Há algumas situações em que essa ordem tem certa influência para o banco, seja na hora de realizar inserções de dados ou questões internas de performance.

Esse último parágrafo pode ter te deixado confuso, já que vimos na seção anterior que o autor C.J. Date diz que não existe ordenação da esquerda para direita nos componentes de uma tupla. Veja que as propriedades do Date dizem respeito ao modelo relacional teórico, que tem seus fundamentos na matemática. Enquanto isso, essa última afirmativa, a de que a ordem tem sim uma certa relevância, diz respeito à prática, em um sistema de bancos de dados relacionais.

Fique atento para essa distinção sutil entre teoria do modelo relacional e prática nos bancos de dados. Caso seja cobrado, observe os elementos do contexto da questão para saber se o examinador está falando da teoria ou da prática e, assim, responder corretamente!

Domínio

Por fim, temos que o domínio é o conjunto de todos os valores atômicos possíveis para um determinado atributo. Por exemplo, em nosso banco de dados, para o nome de cada cliente, o domínio poderia ser o conjunto de cadeias de caracteres de tamanho máximo 100. Veja o exemplo de atributos e seus domínios a seguir:

Atributo Domínio

Nome Cadeias de caracteres de 0 a 100 dígitos Data de Nascimento Datas a partir de 1900

Sexo Caracteres M ou F

No caso do nome, o domínio não seria o conjunto de todos os nomes de pessoas do planeta terra que respeitem aquele tamanho máximo, não é isso. O conceito de domínio está associado às limitações impostas pelo projetista ao banco de dados e não necessariamente a limitações semânticas do mundo real. Dessa forma, a cadeia de caracteres “@#$% 1234 da Silva” poderia ser considerada um nome válido, mesmo que não exista ninguém no mundo que tenha esse nome.

Para compreender bem essas restrições para os valores dos atributos, é importante que você se lembre do conceito de tipo de dados que já citamos anteriormente. O tipo é uma classificação dos dados que define o que o computador “espera” encontrar em uma determinada coluna de uma tabela: valores numéricos (inteiros ou decimais), cadeias de caracteres, bits (valores 0 ou 1), booleano (verdadeiro ou falso), grandes objetos (imagens, arquivos de vídeo...), dentre outros possíveis.

Uma observação importante é que o valor NULL não é parte de nenhum domínio de dados, ou seja, ele não tem tipo. Portanto, ele não é considerado um valor convencional, seja ele zero ou a cadeia de caracteres vazia, mas na verdade um marcador ou placeholder que serve para mostrar que o valor de um determinado campo não se aplica ou está ausente para aquela tupla.

(FCC – Prefeitura de Teresina/PI – 2016)

Em um banco de dados relacional existe a figura do valor nulo, ou seja, atributos de um conjunto de entidades podem ter esse valor. O valor nulo indica

a) ausência de valor para um atributo de uma entidade de um conjunto de entidades.

b) que em sua composição deve constar o caractere %.

c) existência de múltiplos valores para um atributo de uma entidade de um conjunto de entidades.

d) que o conteúdo do atributo de uma entidade de um conjunto de entidades foi preenchido com o valor 0 (zero).

e) que em sua composição deve constar a sequência de caracteres //

RESOLUÇÃO:

O valor nulo indica a ausência de um valor ou que um determinado valor não se aplica. Ele não possui sequências de caracteres em sua composição, como afirma as alternativas B e E, ou mesmo qualquer conteúdo, como indicam as alternativas C e D.

Quero que você compreenda que o NULL é diferente da cadeia de caracteres que forma a palavra “NULL”. Ele não é uma cadeia de caracteres ou um valor propriamente dito, é apenas um marcador especial que indica que o valor do atributo para aquele registro não está presente. Dessa maneira, nossa resposta é a assertiva A.

Gabarito: A

Chaves

Até aqui, você já foi apresentado às estruturas relacionadas ao armazenamento de dados em um BD relacional e suas características. Mas, conforme vimos na última aula, um banco de dados é uma coleção de dados relacionados

Assim, não basta guardar os dados em tabelas. De modo a se controlar a redundância de dados e manter a integridade do BD, é necessário que se realize a implementação dos relacionamentos entre as diversas tabelas que o compõem. Para possibilitar esses relacionamentos, existem as chaves, que identificam as tuplas e permitem as interconexões entre diferentes tabelas ou até mesmo entre diferentes tuplas de uma mesma tabela.

Como já é de praxe, vamos ilustrar nossa teoria com um exemplo visual para você entender melhor. A seguir trago um pedaço de um modelo lógico bem simplificado de um banco de dados de uma determinada empresa comercial:

Esse trecho de diagrama traz diversos tipos de relacionamento e de chaves. Vamos falar sobre cada um deles a seguir.

Chave Primária

Vamos começar com o conceito mais básico, o de chave primária. Nos modelos lógicos, as chaves primárias costumam estar marcadas com as iniciais PK (Primary Key) ou com um pequeno ícone de chave.

Uma chave primária é uma escolha de um conjunto de atributos que identificam unicamente uma tupla dentro de uma relação. Esse conjunto de atributos pode ser também unitário, caso em que somente um atributo faz o papel de PK. Assim, as chaves primárias podem ser classificadas em simples ou compostas.

Outra característica importante é que a chave primária pode ter correspondência com algum elemento do mundo real, como o CPF, caso em que é considerada uma chave natural. A chave pode também assumir um valor aleatório gerado pelo SGBD, caso em que será chamada de surrogate key, ou chave substituta. O único propósito de uma surrogate key é agir como chave primária, ela não tem correspondência com algum dado do mundo real ou qualquer significado implícito.

No nosso exemplo, idCliente, idSetor, idReunião, etc. são chaves substitutas. Nos projetos de bancos de dados é comum que se nomeie essas chaves substitutas com o prefixo “id”, de “identificação” ou “identificador”.

Essas chaves substitutas muitas vezes são do tipo autoincremento, ou seja, são valores numéricos que vão sendo incrementados pelo próprio SGBD à medida que adicionamos registros. Assim, o usuário não precisa se preocupar com os valores das chaves.

Veja um exemplo:

IdPessoa Nome

1 José Silva Pereira 2 João Silva Pereira 3 Joaquim Silva Pereira ... ...

O campo idPessoa começa no número 1. Quando mais uma pessoa é adicionada, o SGBD atribui o valor da chave como sendo o anterior mais um, ou seja, 1+1 = 2, e assim sucessivamente.

Seja chave natural ou substituta, o que importa mesmo é que o valor da PK seja único dentro de uma mesma tabela, para que possa agir como um identificador. Por consequência disso, temos também que nenhuma chave primária pode conter o valor NULL.

Ainda a respeito das chaves primárias, quero que você guarde que podem existir campos que contêm restrições de unicidade, que só permitem valores únicos, mas que não são chaves primárias. São campos que contam com a restrição UNIQUE da linguagem SQL.

Uma diferença entre um atributo marcado com UNIQUE e uma PK é que o UNIQUE podem permitir valores nulos, desde que todos os valores válidos sejam diferentes entre si.

Chave Estrangeira

Tome como exemplo a tabela “Venda”. Veja que ela possui um campo chamado CPFVendedor, marcado com FK, e uma seta apontando para a tabela que contém os funcionários do nosso estabelecimento comercial.

Essa marcação FK (Foreign Key) indica que um ou mais atributos da tabela compõem uma chave estrangeira.

Esse tipo de chave identifica unicamente uma tupla em outra tabela ou outra tupla da mesma tabela. A chave estrangeira da tabela de origem referencia a chave primária da tabela de destino, indicando o relacionamento entre as duas tabelas.

No nosso exemplo, veja como cada valor no campo CPFVendedor da tabela Venda faz referência a um registro da tabela Funcionário, indicando que aquele funcionário foi o vendedor que realizou determinada venda:

Atenção !

É importante que você saiba que uma chave estrangeira não necessariamente referencia uma chave primária, apesar de ser o caso mais comum. A chave estrangeira pode referenciar qualquer conjunto de atributos, desde que esses também possam identificar unicamente um registro da tabela de destino.

Por fim, temos que, ao contrário da chave primária, a chave estrangeira pode sim assumir o valor nulo, desde que o projeto defina que a participação da entidade no relacionamento associado não é total. Por exemplo, se um cliente realizasse uma compra online, esta transação não teria um vendedor associado. Assim, o campo CPFVendedor da venda poderia assumir o valor NULL, indicando que o relacionamento não se aplica àquele registro de venda.

(FCC – DPE/AM – 2018)

Considere duas tabelas S (A, B, C) e T (X, Y, Z, A) de um banco de dados relacional, sendo A e X chaves primárias das tabelas S e T, respectivamente, e A chave estrangeira na tabela T, com origem em S. Dessa forma, é correto afirmar que

a) T é chamada de tabela referenciadora.

b) B e C formam uma superchave da tabela S.

c) T é chamada de tabela indicativa.

d) S é chamada de tabela prevalente.

e) Y, Z e A formam uma chave candidata da tabela T.

RESOLUÇÃO:

a) Bem, se, em um relacionamento, dizemos que uma chave estrangeira referencia outra tabela, podemos afirmar que a tabela de origem é a referenciadora e a de destino é a referenciada. Quando o examinador fala que a chave estrangeira está em T, com origem em S, ele quer dizer que a chave estrangeira está na tabela T (ou seja, T faz a referência) e o atributo original referenciado está na tabela S. A redação é um pouco confusa, mas essa é a resposta! CERTA

b) Veja que a chave primária da tabela S é o atributo A. B e C poderiam até ser chaves candidatas, mas isso não tem nada ver com “superchave” e não há nenhuma indicação nesse sentido na questão. ERRADA

c) T é a tabela referenciadora, pois é a tabela de origem do relacionamento. ERRADA d) S, por sua vez, é a tabela referenciada, pois é o destino do relacionamento. ERRADA

e) Não há nenhum indicativo nesse sentido no enunciado da questão. Aliás, A é, na verdade, chave estrangeira da tabela, como a própria assertiva diz. ERRADA

Gabarito: A

Chave Candidata

As chaves candidatas, ao contrário das primárias e estrangeiras, não são graficamente representadas no modelo lógico. Elas guardam mais relação com o processo de escolha da chave primária na etapa de projeto do BD, já que são, como o nome já diz, meras candidatas a ser chave primária.

As chaves candidatas consistem em atributos ou combinações de atributos que não contêm valores nulos e que servem para identificar unicamente uma tupla – ou seja, têm as mesmas características de uma chave primária. Contudo, elas não foram escolhidas para ser a PK de uma tabela por alguma decisão do projeto.

Na tabela Setor do nosso exemplo, poderíamos considerar o atributo “NomeSetor” como chave candidata, já que provavelmente não há dois setores com o mesmo nome em uma empresa. Contudo, escolhemos a chave substituta idSetor para ser a nossa chave primária.

Relacionamentos

Como já dissemos na aula anterior, temos três possibilidades de cardinalidades para os relacionamentos: 1:1, 1:N e M:N. A maneira com que esses relacionamentos vão ser implementados em um banco de dados relacional vai variar, mas, de modo geral, iremos utilizar chaves estrangeiras e tabelas auxiliares para fazê-lo. Veja:

Um para um (1:1)

Figura: Trecho de um diagrama Entidade-Relacionamento na notação de Chen indicando um relacionamento (1:1)

Nesta cardinalidade, como já sabemos, um registro em uma tabela A só pode referenciar no máximo um registro em outra tabela B e vice-versa. Como exemplo, temos o relacionamento entre Funcionário e Setor.

Observe no diagrama que um funcionário só pode gerenciar um setor e um setor só pode ser gerenciado por no máximo um funcionário.

No nosso caso, poderíamos definir que o atributo CPFGerente, da tabela Setor, é uma chave estrangeira que referencia Funcionário, e que o valor do atributo CPFGerente tem que ser único. Desta maneira, cada Setor só pode ter no máximo um gerente associado e cada funcionário só pode gerenciar no máximo um setor. Veja os exemplos de dados nessas tabelas a seguir:

Funcionário:

CPF Nome

394.036.580-70 Alexandre Silva 713.716.000-07 Marina Almeida 221.621.540-64 Frederico Souza

Setor:

NomeSetor CPFGerente

Vendas 394.036.580-70

Contabilidade 713.716.000-07

Somente criar a chave estrangeira não seria suficiente para definir esse relacionamento como sendo 1:1. Veja que temos também que dizer ao banco de dados que o valor do atributo “CPFGerente” é único na tabela Setor. Se não o fosse, poderíamos ter vários setores com o mesmo gerente. Observe essa situação hipotética:

Setor:

NomeSetor CPFGerente

Vendas 394.036.580-70

Contabilidade 394.036.580-70 Marketing 394.036.580-70

Assim, temos que utilizar uma restrição de unicidade. Dizemos ao SGBD: veja bem, esse atributo CPFGerente não é uma chave primária, mas também não pode ter seus valores repetidos! Na linguagem SQL, vamos criar essa condição ou restrição através do comando UNIQUE.

Um para muitos (1:N):

Figura: Trecho de diagrama Entidade-Relacionamento indicando um relacionamento (1:N)

Nessa cardinalidade, um registro em uma tabela A pode se ligar a múltiplos registros em outra tabela B, mas um registro da tabela B só pode estar associado com um registro da tabela A. O exemplo mais simples desse tipo é o relacionamento entre Venda e Funcionário, através do campo CPFVendedor. Seguindo a lógica, sabemos que um funcionário pode realizar nenhuma ou diversas vendas, mas uma venda está atribuída somente a um funcionário vendedor.

Esse tipo de relacionamento também pode ser implementado através da inserção de uma chave estrangeira na tabela do lado “muitos”, ou “N”, do relacionamento. Só que dessa vez não precisamos colocar aquela restrição UNIQUE, já que poderá haver vários registros de vendas atribuídos a um mesmo CPF do vendedor. Veja:

Funcionário

CPF Nome

562.099.970-07 Pedro Guimarães 245.494.170-80 Mariana Andrade 221.621.540-64 Frederico Souza

Venda

idVenda CPFVendedor Data/Hora Valor 7555 562.099.970-07 11/10/2018 – 08:45 R$ 450,00 7556 562.099.970-07 11/10/2018 – 09:20 R$ 58,90 3889 562.099.970-07 21/05/2018 – 15:10 R$ 315,20 5943 245.494.170-80 09/07/2018 – 10:55 R$ 980,00

Muitos para muitos (M:N)

Figura: Trecho de diagrama Entidade-Relacionamento indicando um relacionamento (M:N)

Esse tipo de relacionamento é um pouco mais complexo de se implementar. Como o nome já diz, ele ocorre quando um registro de uma tabela pode se referir a vários da outra tabela e vice-versa. Repare no diagrama acima como um relacionamento M:N é representado na modelagem conceitual, indicando que um funcionário pode participar de várias reuniões, enquanto que uma reunião também pode contar com a participação de diversos funcionários.

Agora veja como os dados ficariam nas tabelas. Destaquei alguns registros para você perceber as repetições.

Em laranja, veja como um funcionário pode participar de múltiplas reuniões, em azul, observe que uma reunião conta com a participação de diversos funcionários.

Funcionário:

CPF Nome

562.099.970-07 Pedro Guimarães 245.494.170-80 Mariana Andrade 221.621.540-64 Frederico Souza 394.036.580-70 Alexandre Silva

713.716.000-07 Marina Almeida

Reunião:

idReunião Data/Hora Sala 120 11/10/2018 – 10:00 204 398 21/05/2018 – 09:30 801 243 09/07/2018 – 13:00 1102

ParticipaReunião:

idReunião CPFFuncionário 120 562.099.970-07

120 245.494.170-80 120 394.036.580-70 398 713.716.000-07 398 245.494.170-80 243 394.036.580-70 243 562.099.970-07 243 713.716.000-07

Essa tabela intermediária que criamos foi chamada de ParticipaReunião no nosso diagrama. Ela é uma tabela de ligação em que cada registro representa a participação de um funcionário em uma reunião. A chave primária dessas tabelas auxiliares geralmente é representada por uma composição entre as chaves estrangeiras que referenciam as tabelas envolvidas no relacionamento. No nosso caso, idReunião e CPFFuncionário.

Em teoria, relacionamentos de todas as três cardinalidades poderiam ser implementados se utilizando de uma tabela auxiliar como a que acabamos de mostrar. Para representar as outras cardinalidades (1:1 e 1:n), seria necessário somente manipular as restrições de unicidade de valores (UNIQUE) de acordo com o relacionamento. Contudo, não é recomendado que se adote essa abordagem a não ser que seja preciso, já que em alguns casos estaríamos aumentando a redundância de dados e tornando o modelo mais complexo ao adicionar uma tabela sem necessidade.

Atenção !

Por exemplo, uma instância do relacionamento ParticipaReunião diz respeito à participação do Funcionário de CPF nº 562.099.970-07 em uma Reunião de id 120.

Essa instância pode ser identificada a partir da conjunção entre as chaves das entidades participantes do relacionamento, ou seja, o idReunião da Reunião X e o CPF de Fulano.

Como sabemos, um relacionamento também pode ter seus próprios atributos. Dessa maneira, poderíamos também incluir algum atributo descritivo que diga respeito ao relacionamento nessa tabela auxiliar, ou seja, que caracterize o evento representado pelo relacionamento, e não as entidades participantes.

Veja um exemplo disso:

idReunião CPFFuncionário Situação 120 562.099.970-07 Presente 120 245.494.170-80 Presente 120 394.036.580-70 Ausente

Autorrelacionamentos

Figura: Trecho de diagrama Entidade-Relacionamento indicando um autorrelacionamento

Você sabe que existem os relacionamentos recursivos, aqueles de grau 1, ou seja, que envolvem apenas um conjunto de entidades. Esses relacionamentos, também conhecidos como autorrelacionamentos, podem ser representados se criando uma chave estrangeira na própria tabela criada a partir da entidade. Veja um exemplo:

Funcionário:

CPF Nome CPFChefe

562.099.970-07 Pedro Guimarães 245.494.170-80 245.494.170-80 Mariana Andrade 713.716.000-07 221.621.540-64 Frederico Souza 245.494.170-80 394.036.580-70 Alexandre Silva 245.494.170-80

713.716.000-07 Marina Almeida NULL

Como mostramos, o autorrelacionamento do nosso exemplo é de cardinalidade 1:N. Contudo, poderíamos criar um relacionamento deste tipo com cardinalidade 1:1 ou M:N sem problemas. Os autorrelacionamentos podem assumir qualquer cardinalidade.

Integridade, Views e Normalização

Bom, agora que já vimos alguns conceitos básicos dos BDs relacionais, vamos discutir alguns pontos um pouco mais avançados. Mas não se assuste, o grau de cobrança desses conceitos no seu concurso não deve ser tão aprofundado. Leia o conteúdo com atenção e revise quantas vezes achar necessário.

Integridade

Um banco de dados relacional possui um conjunto de restrições de integridade. Essas restrições, como o nome já diz, visam a manter o banco de dados em um estado íntegro, consistente, ao longo de todo o ciclo de vida dos dados.

Essa integridade pode ser física, que diz respeito ao armazenamento dos dados em um meio físico, como em um HD ou fitas de backup (sim, ainda se usam fitas magnéticas como as VHS!). A integridade física pode ser comprometida por corrosão, falhas elétricas, catástrofe natural no local onde os servidores ficam, dentre outras inúmeras possibilidades.

O outro tipo de integridade é a integridade lógica, que é o que realmente interessa para o nosso estudo no momento. A integridade lógica tem a ver com a correção dos dados armazenados e o respeito às regras de negócio definidas quando do projeto do sistema de banco de dados.

Veremos a seguir que há três grandes tipos de restrições de integridade lógica:

Existem na literatura mais dois tipos de integridade que podemos citar:

Integridade de vazio

o Na verdade é um subtipo da integridade de domínio, que determina se os atributos podem ou não assumir valores nulos.

Integridade definida pelo usuário (ou de negócio)

o Diz respeito às regras definidas pelos desenvolvedores do banco de dados que não fazem parte dos três tipos de integridade que apresentamos mais acima.

o Por exemplo, pode-se criar uma regra que diz que, a cada registro adicionado ou removido de uma tabela, será criado um registro em uma tabela de auditoria indicando o que foi adicionado ou removido. Essa tabela de auditoria servirá para controlar quem está fazendo o que no banco de dados.

Índices

Os SGBDs são responsáveis, como você bem sabe, por diversas funcionalidades atribuídas aos sistemas de bancos de dados. Uma dessas atribuições é a de gerenciar a performance do acesso aos dados, permitindo que se trabalhe com tabelas bastante volumosas sem que isso cause uma demora excessiva na realização das tarefas.

Pode ser muito custoso computacionalmente para encontrar o que se deseja em um BD. Grandes organizações possuem bases de dados gigantescas, então é necessário que se organize os registros de alguma maneira para facilitar essa busca.

•Esse tipo de restrição está relacionado com aschaves primárias. A integridade de entidade define que cada chave primária deve ser única e não nula. Também é conhecida como integridade primária ou de chave primária.

Integridade de entidade (entity integrity)

•A integridade referencial é o conceito de chave estrangeira. Ela determina que uma chave estrangeira só pode exisit em dois estados:

•1) Se referindo a uma outra tabela (ou à mesma tabela);

•2) Sendo o valor NULL, caso em que explicitamente estamos dizendo que o relacionamento não existe para determinado registro.

•Ou seja, não é possível que exista uma chave estrangeira que possua um valor diferente de nulo, mas que este valor não tenha correspondência na tabela referenciada. Se a chave estrangeira na tabela A que referencia a tabela B tem o valor “1”, necessariamente deve existir um registro com valor “1” no atributo referenciado da tabela B.

Integridade referencial (referential integrity)

•Muito simples. Você já sabe que o domínio é o conjunto dos valores possíveis para um determinado atributo. Dessa forma, a integridade de domínio determina que os valores de todas as colunas, em todos os registros de um banco de dados fazem parte de seus domínios.

Integridade de domínio (domain integrity)

Imagine que determinada tabela de um banco de dados é uma grande biblioteca, e que, portanto, os dados contidos nesta tabela são os livros. Como você já sabe, as tuplas em uma relação não respeitam uma ordem definida, então os livros nessa biblioteca também estão guardados de forma desordenada. Seria uma tarefa dificílima encontrar qualquer livro nesse local sabendo somente seu título, não é? No pior caso, teríamos que consultar os títulos de todos os livros para encontrar o que estamos procurando por último.

Agora imagine que uma bibliotecária muito zelosa implementa um sistema de classificação dos livros por assunto, criando um documento que indica em quais estantes ficam os livros a respeito de cada matéria. Por sua vez, os títulos dentro de cada assunto passam a ser ordenados por ordem alfabética. Percebeu como encontrar o que queremos ficou mais fácil?

É mais ou menos isso que acontece com os índices em uma tabela. Os índices são estruturas que contêm uma chave de busca e um ponteiro, indicando a localização de determinado bloco de dados dentro da estrutura de armazenamento. A chave de busca é composta por um ou mais atributos de uma tupla, e o ponteiro contém o endereço virtual do bloco de dados onde cada faixa de registros vai estar.

Assim, para dar mais um exemplo, você pode considerar uma empresa que possui uma grande lista de clientes. O administrador de banco de dados verificou que os clientes são frequentemente buscados pelo seu nome e pelo estado onde residem, então ele criou um índice em cima dessas duas colunas. O índice, nessa situação, seria uma estrutura mais ou menos assim:

Chave Ponteiro

Estado: Acre; Nome: Ana Posição 120 Estado: Acre; Nome: Carlos Posição 121

... ...

Estado: Tocantins; Nome: Zelda Posição 850

Tenha em mente que essa é uma simplificação bastante grosseira do que é um índice, mas que o objetivo aqui é somente mostrar a você a lógica de funcionamento dessas estruturas!

Os índices podem assumir diferentes tipos (clustered, non-clustered, primary...), o que diferencia um pouco a maneira como a busca é realizada, mas o propósito geral é o mesmo. Não vamos entrar em detalhes a respeito de cada um no momento, pois teremos um momento específico do curso para isso.

Por fim, guarde que os SGBDs geralmente indexam a chave primária das tabelas automaticamente, pois assume-se que as tuplas serão frequentemente acessadas através desses identificadores únicos.

(CESPE – ANTAQ - 2014)

Acerca de sistemas de gerenciamento de banco de dados relacional (SGBDR), julgue o item a seguir.

A criação de índices, mesmo em tabelas grandes e com atributos pouco esparsos, aumenta as chances de serem obtidas consultas com respostas rápidas.

RESOLUÇÃO:

A questão requer um raciocínio um pouco mais elaborado a respeito de índices. Você lembra que eu citei que os índices apontam para as localizações de determinados blocos de dados, correto? Bom, se os atributos são pouco esparsos, ou seja, estão todos mais ou menos no mesmo bloco, a eficiência do índice é diminuída.

Imagine criar um índice no campo “Sexo” de uma tabela muito grande que contém informações a respeito de Clientes. O atributo em questão só tem as opções “Masculino” ou “Feminino”.

Para uma tabela com milhões de registros, faz pouquíssima diferença organizá-los por sexo, já que cada uma das duas partições vai continuar tendo um volume muito grande de tuplas. Ou seja, a criação do índice não vai facilitar muito nossa vida na hora de encontrar uma tupla específica.

Gabarito: E

Views (visões)

Como você já viu, a estrutura básica dos BDs relacionais é a tabela (relação). Essas tabelas estão persistidas no banco de dados, ou seja, armazenadas de uma forma não volátil, fixa, até que alguém mande um comando

“mandando” alterar ou remover os dados de lá.

Contudo, na maioria das vezes os usuários ou aplicações precisam realizar análises com os dados de uma forma não estática, ou seja, querem visualizar os dados de maneira diferente da forma que estão armazenados lá nas tabelas. Isso diz respeito à característica de suporte a múltiplas visões da abordagem de BD.

Para exemplificar como isso ocorre, vamos trazer um trecho de uma tabela bem simplificada que armazena os alunos de uma escola. Ela segue a seguinte estrutura:

Aluno Dt. Nascimento Turma José 01/10/2004 1ª Ano A Maria 20/02/2006 7ª Série B Pedro 15/06/2003 2º Ano D

... ... ...

Agora imagine que temos esta tabela acima, mas não queremos somente visualizar a lista de alunos, e sim queremos saber quantos estudam em cada turma. Contar manualmente registro a registro qual aluno pertence a qual turma é um trabalho maçante e pode ser quase impossível, dependendo da quantidade de registros na tabela e da frequência com que ela é atualizada.

Para obter o resultado que queremos, poderíamos simplesmente executar a seguinte consulta SQL:

SELECT Turma

,COUNT(Aluno) AS QuantidadeAlunos FROM Alunos

GROUP BY Turma

O resultado, então, seria retornado da seguinte maneira:

Turma QuantidadeAlunos

1ª Ano A 20

1º Ano B 18

2º Ano A 25

... ...

Veja que essa coluna de quantidade de alunos não está explícita em nenhuma tabela, mas foi “criada” por nós no momento em que executamos nossa consulta. Esse resultado não é gravado no banco de dados, somente é exibido para que o usuário realize sua análise ou posteriormente salve em um arquivo, por exemplo.

Continuando com o exemplo, imagine que frequentemente precisamos acessar os dados assim como fizemos nessa consulta acima. Contudo, não é prático que criemos uma nova tabela com essa estrutura diferente, pois estaríamos criando uma redundância desnecessária de dados – a mesma informação estaria representada de formas diferentes em dois locais distintos do banco. Para esses casos, temos as views (visões).

As views são estruturas que podem ser acessadas ilimitadas vezes, possibilitando que reutilizemos as consultas que executamos com frequência. Para criar uma view a partir da consulta mostrada acima, poderíamos rodar o seguinte comando SQL:

CREATE VIEW vw_AlunosPorTurma AS

SELECT Turma

,COUNT(Aluno) AS QuantidadeAlunos FROM Alunos

GROUP BY Turma

Veja que, depois do elemento “AS”, o comando utilizado para criar uma view é idêntico ao que rodamos para realizar nossa consulta que extraiu os dados pela primeira vez – não se preocupe em memorizar esses códigos ainda!

Eu fiz essa observação para mostrar que as views são nada mais que consultas armazenadas.

As views, portanto, são tabelas virtuais. Ou seja, por serem meras consultas salvas, elas não armazenam dados fisicamente no banco de dados. Para fins de consulta posteriores ao banco de dados, contudo, as views são indistinguíveis de tabelas reais, ou seja, pode-se realizar comandos SQL fazendo referência às views como se elas fossem tabelas.

Há ainda a figura das views materializadas. Como as views guardam apenas a consulta a ser realizada, mas não dados, os resultados têm que ser calculados e extraídos de suas respectivas tabelas no momento em que a consulta é executada. Nos casos de views que contêm consultas complexas, isso pode ter um impacto de performance elevado em relação a tabelas físicas.

Assim, para algumas finalidades, existem as views materializadas, que, ao invés de armazenar as consultas, já armazenam os resultados delas. Esses resultados são modificados toda vez que há uma mudança nas tabelas das quais a view extrai seus dados.

(FCC – DPE/AM – 2018)

Visões constituem um tipo de estrutura possível de ser criada em um banco de dados relacional e apresentam como característica:

a) Permitir a consulta apenas a tabelas que possuam somente atributos do tipo numérico armazenados.

b) Possuir o armazenamento somente do comando de criação de sua estrutura, sendo os dados consultados a partir de tabelas com dados fisicamente armazenados.

c) Não comportar a visualização de atributos do tipo booleano.

d) Exibir apenas as chaves primária e estrangeira das tabelas especificadas em sua estrutura.

e) Ser consultadas um número restrito de vezes, conforme seja o sistema gerenciador de banco de dados utilizado.

RESOLUÇÃO:

a) Veja que a FCC gosta de colocar algumas restrições de tipo que não existem. Isso não tem nada a ver, a view é uma consulta armazenada que pode consultar as mais diversas tabelas com atributos de tipos distintos. ERRADA b) A view é uma tabela virtual, ou seja, pode ser acessada como se fosse uma tabela, mas é somente uma consulta armazenada. Os dados são extraídos das tabelas de origem no momento em que se realiza uma consulta à view.

Em outras palavras, é o que a alternativa está afirmando. CERTA

c) Uma view comporta sim a realização de consultas com atributos booleanos, não há motivo para ser diferente.

ERRADA

d) Nada disso! Dessa maneira as views teriam sua utilidade bastante reduzida. Elas podem, inclusive, consultar e exibir de tabelas que não possuam chave alguma especificada. ERRADA

e) As views são plenamente reutilizáveis, não têm um prazo de validade. A consulta fica armazenada no banco de dados até que o usuário com permissões ou administrador do banco de dados a remova ou altere. ERRADA Gabarito: B

Normalização

A normalização foi proposta por Codd em 1970 como sendo parte do modelo relacional. Ela consiste em algumas técnicas utilizadas para eliminar valores não atômicos e redundância de dados. Essas redundâncias, como já discutimos, poderiam causar problemas como a necessidade de manutenção dos mesmos dados em diversas tabelas diferentes, o desperdício de espaço em disco e perda de performance na escrita no banco de dados.

Dessa forma, os objetivos da normalização podem ser sistematizados assim:

1. Reduzir a redundância de dados, organizando as tabelas de modo a facilitar a manutenção, a atualização de dados e a evolução do modelo; e

2. Garantir a integridade dos dados.

A normalização é frequentemente empregada nos bancos de dados relacionais, pois estes costumam servir a aplicações transacionais que lidam com um grande volume de inserções e atualizações de dados. As formas normais são os padrões e regras a respeito da organização das tabelas que fazem parte desse processo.

Essas formas normais são cumulativas, ou seja, para um banco de dados estar na terceira forma normal (3FN), ele também tem que estar na primeira forma normal (1FN) e na segunda (2FN), e assim sucessivamente.

Codd inicialmente propôs essas três formas normais, que são as mais difundidas até hoje nos ambientes de bancos de dados. Assim, um banco de dados pode ser dito normalizado se atende à 3FN.

O modelo posteriormente se expandiu com contribuições de outros especialistas e hoje há uma série de formas de normalização propostas. Para sua prova, creio ser mais que suficiente entender as três primeiras formas normais:

Uma tabela está na 1FN somente se todos os seus atributos são atômicos;

Ou seja, seus atributos compostos devem ser desmembrados em suas múltiplas partes, cada uma virando um novo atributo, e os atributos multivalorados representados em uma tabela auxiliar ligada à principal que possua uma linha por registro, como mostramos durante a aula.

Uma tabela está na 2FN somente se está na 1FN e seus atributos não-chave dependam da chave primária inteira. Ou seja, eles não podem depender somente de parte da chave;

Exemplo de violação desta regra:

Fabricante Produto Preço Unitário País Fabricante

Empresa A Bola R$ 15 China

Empresa B Lápis R$ 3 Brasil

Veja que a chave da tabela é composta pelos campos sublinhados Fabricante e Produto. Agora tome o atributo País Fabricante. Esse atributo diz respeito ao fabricante, somente. Não importa qual o produto, desde que o fabricante pertença a determinado país.

Dessa maneira, o atributo “País Fabricante” depende só de parte da chave, a parte do atributo “Fabricante”.

Do ponto de vista desse atributo, a outra parte da chave, composta pelo atributo “Produto”, é absolutamente irrelevante.

Uma tabela está na 3FN somente se está na 2FN e nenhum dos seus atributos não-chave apresente dependência transitiva em relação à chave - ou seja, que nenhum atributo não chave apresente dependência funcional em relação a outro atributo não chave!

Essa forma normal tem um exemplo muito clássico que acredito que vai te ajudar a resolver a maior parte das questões de prova a respeito, então vamos direto a ele!

Vendedor Produto Data Qtd. Valor Unitário Valor Total

Vendedor A Bola 10/01/2018 3 R$ 15 R$ 45

Vendedor B Lápis 15/02/2018 10 R$ 3 R$ 30

Veja que, na tabela acima, o campo Valor Total depende de um cálculo de multiplicação entre os campos Qtd. e Valor Unitário, ou seja, seu valor pode ser obtido através dos valores desses outros atributos. Dessa forma, o atributo Valor Total é dependente de outros atributos não-chave!

Para formalizar melhor esses conceitos, podemos dizer que um atributo (ou conjunto de atributos) Y apresenta dependência funcional em relação a um atributo (ou conjunto de atributos) X quando o valor de X é capaz de determinar o valor de Y.

Veja que, no exemplo da 3FN, quando tivermos um valor qtd para a quantidade e um valor $ para o valor unitário, o valor total vai ser sempre dado pelo cálculo de qtd * $.

Dessa maneira, a informação do valor total é absolutamente redundante no nosso modelo, já que podemos inferi-la através dos valores de outros atributos. Essas dependências são representadas por X -> Y, em que X determina Y.

A dependência do atributo Valor Total em relação à chave primária é dita transitiva pois é uma forma de dependência indireta. Isso se dá porque todos os atributos de uma tabela na 2FN dependem da chave primária inteira. Assim, como Qtd. e Valor Unitário dependem da chave primária e Valor Total, por sua vez, depende de Qtd. e Valor Unitário, temos uma dependência transitiva. Veja o esquema:

Uma observação que deve ser feita é que a normalização traz uma perda de performance nas consultas, já que cria mais tabelas no modelo, fazendo com que o usuário tenha que realizar múltiplas junções entre tabelas na hora de extrair os dados.

(FCC – DPE/AM – 2018)

Uma tabela de um banco de dados relacional está na primeira forma normal se

a) o número de atributos dessa tabela for limitado pelo sistema gerenciador de banco de dados.

b) os atributos do tipo caractere tiverem comprimento máximo de 30 caracteres.

c) o número de registros dessa tabela for limitado pelo sistema gerenciador de banco de dados.

d) os domínios de todos atributos dessa tabela forem atômicos.

e) os domínios de todos atributos dessa tabela forem compostos por números inteiros.

RESOLUÇÃO:

Para começar, temos que a normalização diz respeito à modelagem formal do banco, não estando presa a regras de SGBDs específicos. Logo, poderíamos eliminar as alternativas a e c. As formas normais não têm nada a ver com tipo de dados ou com tamanho de registros, então as alternativas b e e também estão incorretas.

A primeira forma normal diz que os atributos devem conter exatamente um valor. A assertiva que mais se amolda a esse conceito, por eliminação, é a letra D. Mas veja que a redação está um pouco imprecisa. O domínio dos atributos não vai ser atômico, se fosse assim cada atributo só teria um valor possível. Os domínios são, na verdade, conjuntos de valores atômicos. Ou seja, o domínio é um conjunto com múltiplos valores, esses valores é que devem ser atômicos. Contudo, não vamos brigar com a banca, temos sempre que trabalhar com as opções que nos foram dadas.

Gabarito: D

(FCC – DPE/AM – 2018 - ADAPTADA)

Ao participar de uma reunião sobre Bancos de Dados Relacionais, um Técnico Programador afirmou, corretamente, que

a) o Diagrama de Entidade e Relacionamento, no qual são definidas as chaves primárias e estrangeiras, a normalização, a integridade referencial etc., é essencial para a compreensão do modelo físico de dados.

b) para estar na 2ª Forma Normal, todos os atributos não chaves da tabela do Banco de Dados não devem depender unicamente da chave primária, mas podem depender apenas de parte dela.

c) para deixar uma tabela na 1ª Forma Normal é preciso identificar a sua chave primária, identificar as colunas que têm dados repetidos e removê-las; criar uma nova tabela com a chave primária para armazenar os dados repetidos e criar uma relação entre a tabela principal e a tabela secundária.

RESOLUÇÃO:

a) O diagrama ER faz parte da modelagem conceitual, ou seja, não inclui os conceitos de chaves, normalização ou integridade. Se trocássemos “Diagrama de Entidade e Relacionamento” para “modelo lógico” ou “diagrama lógico”, a resposta estaria correta. ERRADA

b) É exatamente o contrário! Os atributos devem depender da chave por inteiro, ou seja, ter dependência funcional completa em relação à chave. ERRADA

c) A 1FN diz respeito à atomicidade dos atributos. Dessa forma, para implementar um atributo multivalorado em uma tabela normalizada, devemos criar uma nova tabela que possua relacionamento com a tabela principal através de sua chave primária. É essa nossa resposta. CERTA

Gabarito: C

* * *

Ficamos por aqui com nossa teoria! Trago a seguir questões da sua provável banca, a FCC. Veja que alguns itens se repetem bastante. Tente memorizar a maneira com que a banca cobra os assuntos!

Bons estudos!

Questões comentadas pelo professor

1.

O modelo de dados fictícios ilustrados abaixo mostra a relação lógica entre duas entidades em um banco de dados:

Contribuinte e Imposto.

Um Auditor Fiscal da área de Tecnologia da Informação precisa implementar em um Sistema Gerenciador de Banco de Dados relacional um conjunto de tabelas, a partir da observação do modelo acima. Precisará incluir um atributo aliquotaImposto, considerando que cada imposto pode ter alíquotas diferentes, dependendo do contribuinte, e que cada contribuinte pode pagar alíquotas diferentes, dependendo do imposto. O campo aliquotaImposto deverá ser incluído

a) na tabela Imposto como um campo comum.

b) como parte da chave primária em uma tabela de ligação que deverá ser criada entre as tabelas Imposto e Contribuinte.

c) na tabela Contribuinte como um campo comum.

d) como um campo comum em uma tabela de associação que deverá ser criada entre as tabelas Contribuinte e Imposto.

e) na tabela Contribuinte como parte da chave primária.

RESOLUÇÃO:

Do enunciado, extraímos também que o atributo a ser criado, aliquotaImposto, pode ter valores distintos para o mesmo contribuinte, por isso não pode fazer parte da tabela Contribuinte. Da mesma maneira, aliquotaImposto não pode ficar na tabela que guarda os impostos, pois um mesmo imposto pode ter alíquotas diferentes para cada contribuinte.

Tanto pelo enunciado quanto pela imagem, podemos verificar que se trata de um relacionamento m:n, ou muitos para muitos. Com isso, deduzimos que o atributo aliquotaImposto é um atributo descritivo que guarda relação com o fato, não com uma ou outra entidade envolvida no relacionamento. Nesse caso, esse atributo estará representado na tabela de ligação entre as duas entidades, necessária para a implementação dos atributos m:n.

Gabarito: D

2.

Considere a seguinte estrutura de dados:

Considere os seguintes requisitos técnicos:

- A estrutura Faturas_Cliente está inserida na estrutura do cliente ao qual pertence.

- Cod_Cliente é o atributo identificador de Cliente.

- Número_Fatura é o atributo identificador de Fatura.

- Um cliente é cobrado em uma ou mais faturas e uma fatura cobra um e somente um cliente.

- O mapeamento relacional fará com que os atributos identificadores (grifados nas estruturas apresentadas acima) componham as chaves primárias (Primary Key - PK), nas tabelas correspondentes dos bancos de dados relacionais.

Após a aplicação da normalização (até a Terceira Forma Normal - 3FN),

a) a estrutura Cliente formará uma tabela e terá o Número_Fatura compondo sua chave primária (Primary Key - PK).

b) a estrutura Cliente formará uma tabela e terá o Número_Fatura como chave estrangeira (Foreign Key - FK).

c) serão criadas três tabelas: Cliente, Faturas_Cliente e Cliente_Faturas cuja chave primária (Primary Key - PK) será uma composição de Cod_Cliente, Número_Fatura e Dt_Venc_Fatura.

d) a estrutura Faturas_Cliente formará uma tabela e terá o Cod_Cliente como chave estrangeira (Foreign Key - FK).

e) a estrutura Faturas_Cliente formará uma tabela e terá o Cod_Cliente compondo sua chave primária (Primary Key - PK).

RESOLUÇÃO:

Observe que temos um modelo conceitual no formato textual, com as entidades Cliente e Faturas_Cliente presentes. Até aí tudo certo, mas quando seguimos para os requisitos técnicos, parecemos ter uma contradição:

Veja este requisito, que parece indicar que Faturas_Cliente faz parte da própria tabela Cliente:

- A estrutura Faturas_Cliente está inserida na estrutura do cliente ao qual pertence.

Agora veja este outro requisito, que indica que Cod_Cliente será chave de Cliente e Número_Fatura será a chave de Faturas_Cliente:

- O mapeamento relacional fará com que os atributos identificadores (grifados nas estruturas apresentadas acima) componham as chaves primárias (Primary Key - PK), nas tabelas correspondentes dos bancos de dados relacionais.

Fica um pouco confuso, pois não sabemos se, antes da normalização, temos os dados de faturas dentro da tabela cliente ou se as faturas já estão em sua própria estrutura.

De todo modo, conseguimos resolver a questão! Como o enunciado fala que o modelo passa por uma normalização até a 3FN, a 2FN já obrigaria que Faturas_Cliente estivesse isolada em sua própria tabela, já que os campos Vlr_Fatura e Dt_Venc_Fatura dependem somente de Número_Fatura, enquanto que Nome_Cliente e Endereço_Cliente só dependem de Cod_Cliente.

Assim, se fatura e cliente estivessem juntos em uma só tabela, com uma chave primária composta por Cod_Cliente e Número_Fatura, veja que teríamos uma dependência parcial em relação a esses atributos descritivos.

Feitas essas considerações, temos que, em um relacionamento 1:n (um para muitos), como é o caso em tela, podemos implementá-lo através de uma chave estrangeira do lado “muitos” do relacionamento. Nesse caso, como um cliente pode ter múltiplas faturas associadas, esse lado “muitos” é Faturas_Cliente. Assim, Faturas_Cliente terá uma chave estrangeira que referencia a chave primária de Cliente.

Isso tudo que apresentamos está resumidamente descrito na alternativa da letra D!

Gabarito: D

3.

Atenção: Para responder à questão, considere o seguinte caso hipotético:

Uma adequada modelagem de dados é necessária antes da construção dos bancos de dados para que estes sejam suficientemente consistentes enquanto fontes de consulta pela fiscalização.

Um modelo de dados-exemplo para atender o controle de arrecadação tributária contém:

Considerando as entidades Contribuinte e Arrecadação, que serão convertidas para tabelas relacionais e o correspondente relacionamento entre elas, também a ser convertido em tabela relacional, bem como os atributos envolvidos, o projeto de banco de dados relacional normalizado deve, no mínimo, prever em sua estrutura, uma ligação

a) 1:n entre Contribuinte e Exigível e uma ligação n:1 entre Exigível e Arrecadação. A chave primária de Exigível será composta pelas chaves primárias de Contribuinte e Arrecadação.

b) n:m entre Contribuinte e Exigível e uma ligação m:n entre Exigível e Arrecadação. A chave primária de Exigível será composta pelas chaves primárias de Contribuinte e Arrecadação.

c) n:1 entre Contribuinte e Exigível e uma ligação m:n entre Exigível e Arrecadação. A chave primária de Exigível será composta pelas chaves primárias de Contribuinte e Arrecadação.

d) m:n entre Contribuinte e Exigível e uma ligação n:1 entre Exigível e Arrecadação. A chave primária de Exigível será composta por uma chave própria e pelas chaves primárias de Contribuinte e Arrecadação.

e) 1:1 entre Contribuinte e Arrecadação e m:n entre Exigível e Arrecadação. A chave primária de Exigível será composta por uma chave própria e pelas chaves primárias de Contribuinte e Arrecadação.

RESOLUÇÃO:

Vamos lá. O fato mais importante aqui é que o relacionamento exigível possui seus próprios atributos. Dessa maneira, em uma estrutura normalizada, temos que criar uma tabela própria para ele. Isso está dado pelo próprio enunciado da questão:

“e o correspondente relacionamento entre elas, também a ser convertido em tabela relacional, bem como os atributos envolvidos”

O relacionamento entre Contribuinte e Arrecadação, conforme a regra de negócio, é de cardinalidade 1:N. Como queremos criar uma tabela também para representar o relacionamento Exigível e seus atributos, precisamos desmembrar esse relacionamento em dois pedaços.

Agora vamos pensar, temos um determinado contribuinte. Esse contribuinte, segundo as regras de negócio, se associa a múltiplas arrecadações dos mais diversos tributos. É bastante lógico, não é? Uma pessoa paga vários impostos distintos, e esses impostos muitas vezes se desdobram em várias parcelas ou cotas.

Podemos perceber que o relacionamento Exigível, a ser traduzido em uma tabela auxiliar, vai representar justamente a ligação entre o contribuinte e suas diversas arrecadações. Essa tabela vai ter uma estrutura mais ou menos assim:

Contribuinte Arrecadação Demais atributos

562.099.970-07 Arrecadação_1 ...

562.099.970-07 Arrecadação_2 ...

562.099.970-07 Arrecadação_3 ...

394.036.580-70 Arrecadação_4 ...

394.036.580-70 Arrecadação_5 ...

Veja que o mesmo contribuinte pode estar representado várias vezes na tabela, já que há várias arrecadações para o mesmo contribuinte, mas uma arrecadação só pertence a uma instância do relacionamento, já que ela pertence exclusivamente a um contribuinte. Dessa maneira, o relacionamento ficará assim:

A resposta dada como correta pela banca foi a letra A, mas essa não faz muito sentido, pois se uma arrecadação pudesse se repetir para múltiplas instâncias de exigível ela poderia se repetir para múltiplos contribuintes. Esse relacionamento iria deixar de ser 1:n e passaria a ser m:n, o que infringiria as regras de negócio propostas.

Assim, penso que a questão deveria ter sido anulada, pois não possui nenhuma alternativa correta.

Gabarito: A (sem resposta)

4.

Suponha que um Auditor foi encarregado de modelar e criar um banco de dados para um pequeno sistema de pedidos de produtos de informática. Para realizar essa tarefa, desenvolveu o modelo mostrado na figura abaixo.

Após criar o modelo, implementou o banco de dados em um Sistema de Gerenciamento de Banco de Dados, criou as tabelas e cadastrou as seguintes informações:

O Auditor tentou incluir os dados abaixo na tabela Pedido.

Ao executar a operação de inclusão, ocorreu um erro porque:

a) não há um cliente com id 12 cadastrado na tabela Cliente.

b) já existe um pedido cadastrado com id 13.

c) a data cadastrada não existe, já que o formato correto é dd/mm/yyyy.

d) já existe um pedido criado para o cliente 12.

e) não existe um pedido cadastrado com id 13.

RESOLUÇÃO:

a) Realmente, não há um cliente com o id 12 na tabela cliente. Como existe uma FK em Pedido, no campo idCli, que referencia o campo de mesmo nome na tabela Cliente, uma inserção de um valor que não existe na tabela referenciada viola a integridade referencial. Essa é a resposta, mas não pare de ler aqui! Veja as demais resoluções. CERTA

b) Essa assertiva está errada. Ainda não existe um pedido com esse id cadastrado. ERRADA

c) Você pode até ter achado que essa era a alternativa, mas não é! Os formatos de data padrão dos bancos de dados costumam ser no formato AAAA-MM-DD (ano, mês e dia). Essa padronização é útil porque evita ambiguidades entre os formatos brasileiro e americano, por exemplo, já que as posições de dia e mês são invertidas nessas duas regiões. De qualquer forma, daria para ver de acordo com os registros já presentes na tabela Produto que esse era o formato aceito. ERRADA

d) Não é o caso, mas mesmo que já houvesse pedido cadastrado para esse cliente, não haveria problema, já que é um relacionamento um para muitos. O examinador usou a notação Pé-de-Galinha em seu modelo. Nessa notação, o tridente indica que aquele lado do relacionamento é o lado “muitos”, enquanto que o tracinho vertical aponta que a cardinalidade máxima da entidade é “um”.

Veja o resumo dessa notação:

ERRADA

e) Não existe mesmo, mas é justamente o que estamos tentando inserir! Se já existisse, haveria violação à integridade de entidade, ou à restrição de unicidade da chave primária da tabela. ERRADA

Gabarito: A

5.

Ainda considerando o modelo lógico e as tabelas da questão anterior, considere também as seguintes tabelas:

Considere que o Auditor digitou um comando para inserir os valores abaixo na tabela ItemPedido:

O resultado da operação foi:

a) Inserido com sucesso b) Erro de sintaxe SQL

c) Erro de tipo no campo qtdIteped d) Erro de integridade referencial e) Erro de integridade primária

RESOLUÇÃO:

Bom, não podemos falar em erro de sintaxe SQL, já que não estamos vendo o comando. Então a b já não é a nossa resposta. O campo qtdItePed recebe um número inteiro, representado por INT no modelo, então não há o que se falar em erro de tipo, logo, a alternativa c também não é nossa resposta.

A integridade referencial está mantida, já que realmente existem o idPed = 7 na tabela Pedido e o idPro = 14 na tabela de Produto. Dessa maneira, nos restam as alternativas a e e. Avaliando a alternativa e, vemos no modelo lógico que a chave primária da tabela ItemPedido é composta pelos campos idItePed e idPed. Dessa maneira, a combinação desses campos deve ser única dentro da tabela. Não é o que acontece, já existe um registro com esses valores na relação. Logo, temos um erro de integridade primária ou integridade de entidade.

Gabarito: E

6.

Suponha que um Auditor foi encarregado de modelar e criar um banco de dados para um pequeno sistema de pedidos de produtos de informática. Para realizar essa tarefa, desenvolveu o modelo mostrado na figura abaixo.

Considere e avalie as asserções a seguir e a relação proposta entre elas.

I. O relacionamento entre as entidades Pedido e ItemPedido é um relacionamento identificado PORQUE

II . idPed, que é chave estrangeira na entidade ItemPedido, faz parte da chave primária desta entidade.

É correto afirmar que

a) as duas asserções são proposições verdadeiras, mas a segunda não é justificativa correta da primeira.

b) a primeira asserção é uma proposição falsa, e a segunda, uma proposição verdadeira.

c) as duas asserções são proposições verdadeiras, e a segunda é uma justificativa correta da primeira.

d) a primeira asserção é uma proposição verdadeira, e a segunda, uma proposição falsa.

e) tanto a primeira quanto a segunda são proposições falsas.

RESOLUÇÃO

Veja no diagrama lógico que a entidade ItemPedido tem o campo idPed, que é a chave da entidade Pedido, como parte de sua chave primária – fato indicado pelo ícone de uma chave do lado do nome do atributo. Essa composição da chave primária indica que a entidade ItemPedido é a entidade fraca de Pedido. O relacionamento entre uma entidade fraca e uma forte é chamado de relacionamento identificador, que é a primeira assertiva.

Podemos concluir, dessa maneira, que podemos afirmar que as duas assertivas são verdadeiras, e que é a segunda assertiva (a respeito da composição da chave) que nos indica que esse é um relacionamento identificador. Logo, a segunda assertiva justifica a primeira.

Gabarito: C

7.

Suponha que um Auditor foi encarregado de modelar e criar um banco de dados para um pequeno sistema de pedidos de produtos de informática. Para realizar essa tarefa, desenvolveu o modelo mostrado na figura abaixo.

No modelo apresentado a entidade ItemPedido:

a) deveria conter o campo PreUniPro, pois o preço unitário do produto deve ser incluído em cada item do pedido.

b) está relacionada com as entidades Pedido e Produto usando a notação Integrated DEFinition for Information Modelling - IDEF1X.

c) possui relação com cardinalidade n :n com a entidade Produto e 1 : n com a entidade Pedido.

d) possui chave primária composta, mas não possui chave estrangeira, logo, não garante integridade referencial.

e) possui uma chave primária composta pelos atributos idItePed e idPed, sendo que os atributos qtdItePed e idPro possuem dependência funcional completa com relação à chave primária.

RESOLUÇÃO