FERRAMENTA OLAP

Exercicios Álgebra Relacional

Seleção













Projeção











Seleção e Projeção










Junção









Agregação

Normalização

1) Quais são as diretrizes informais para o projeto de esquema de relações? Explique resumidamente cada uma.

Semântica de atributos.
• Redução de valores redundantes em tuplas.
• Redução de valores nulos em tuplas.
• Não permissão de tuplas espúrias.

Semântica de atributos.

Assume-se que certo significado esteja associado aos atributos, para todo agrupamento de
atributos que formam uma relação esquema. Intuitivamente, verifica-se que cada relação pode
ser interpretada como um conjunto de fatos ou declarações. Este significado, ou semântica,
especifica como podem ser interpretados os valores de atributos armazenados em uma tupla
da relação, em outras palavras, como os valores de atributos estão relacionados uns com os
outros. Em geral, é mais simples descrever a semântica de relações, ao invés da semântica de
atributos de uma relação.
Diretriz 1ª: Projetar um esquema de relação de maneira que seja simples descrever seu
significado. Normalmente, isso significa que não se pode combinar atributos de múltiplos tipos
de entidades e tipos de relacionamentos numa simples relação. Intuitivamente, se um esquema
de relação corresponde a um tipo de entidade ou tipo de relacionamento, o significado tende a
ser claro. Por outro lado, tende ser uma mistura de múltiplas entidades e relacionamentos e,
assim, semanticamente não-clara.

Redução de valores redundantes em tuplas
Deve-se minimizar o espaço de armazenamento utilizado pelas relações básicas:
_ Agrupe atributos em esquemas de relações;
_ Deve-se evitar situações que caiam em problemas de Anomalias na Atualização.
Modelar esquemas de relações básicas de forma que nenhuma anomalia de atualização possa ocorrer nas relações. Se houver a possibilidade de ocorrer alguma anomalia, registre-a claramente e tenha certeza de que os programas que atualizam o banco de dados operarão
corretamente.
_ Pode-se violar uma diretriz em prol do desempenho de uma consulta, entretanto, as medidas cabíveis devem ser tomadas para que os dados estejam sempre
consistentes e íntegros.

Redução de valores nulos em tuplas.
A existência de muitas possibilidades de uso do valor null causa desperdício de espaço no armazenamento e gera problema de entendimento do significado do atributos e da especificações de joins, e funções agregadas.
_ Motivos para uso do null:
_ O atributo não se aplica à tupla;
_ O valor do atributo para a tupla é desconhecido;
_ O valor do atributo para a tupla é conhecido, mas ausente, ou seja, ainda não foi registrado
Até onde for possível, evite colocar os atributos em uma relação básica cujos valores freqüentemente possam ser nulos. Se os nulls forem inevitáveis, tenha certeza de que eles se aplicam somente em casos excepcionais e não na maioria das tuplas da relação.
_ Ex: se só 10% dos empregados tiverem escritórios particulares, há pouca justificativa para incluir um atributo ESCRITORIO-NRO na relação EMPREGADO; pode ser criada uma relação EMP_ESCRITÖRIOS (ESSN, ESCRITORIO_NRO) que contenha apenas as tuplas dos empregados que possuírem escritórios particulares.


Não permissão de tuplas espúrias.
Considere os esquemas de relação EMP_LOCS e EMP_PROJ1 que pode
substituir a relação EMP_PROJ da Figura 9.1b.
Uma tupla em EMP_LOCS significa que o empregado cujo nome é ENOME trabalha em algum
projeto cuja localização é PLOCALIZAÇÂO. Uma tupla em EMP_PROJ1 significa que o
empregado cujo número do seguro social é NSS trabalha HORAS por semana em um projeto
cujo nome, número e localização são PNOME, PNUMERO e PLOCALIZAÇÂO. A Figura 9.4b
mostra a extensão de EMP_LOCS e EMP_PROJ1 correspondente à relação extensão
EMP_PROJ da Figura 9.3, aplicando-se operações de projeção (¶) adequadas.
Suponha agora que EMP_PROJ1 e EMP_LOCS sejam utilizadas como relações base ao invés
de EMP_PROJ. Isto seria, particularmente, um projeto ruim, pois não se pode recuperar as
informações que existiam originalmente em EMP_PROJ a partir de EMP_PROJ1 e
EMP_LOCS. Se uma operação JOIN-NATURAL for aplicada em EMP_PROJ1 e EMP_LOCS,
surgirão mais tuplas que existiam em EMP_PROJ. A Figura 9.5 ilustra o resultado obtido
aplicando-se o join, considerando apenas as tuplas existentes acima da linha pontilhada, para
reduzir o tamanho da relação resultante. As tuplas adicionais são chamadas tuplas espúrias
porque elas representam informações espúrias ou erradas, e por isso elas não válidas. As
tuplas espúrias estão marcadas por asteriscos (*) na Figura 9.5.
A decomposição de EMP_PROJ em EMP_PROJ1 e EMP_LOCS é ruim porque quando é
aplicada a operação JOIN-NATURAL, não são obtidas as informações originais corretas. Isto
porque foi escolhido PLOCALIZAÇÃO como o atributo que relaciona EMP_LOCS e
EMP_PROJ1, e PLOCALIZAÇÃO não é nem uma chave-primária


2)Quais são as métricas de qualidade informal para projeto de esquemas de relações? Explique resumidamente cada uma delas.

R: São quatro métricas (Semântica de Atributos, Informação redundante ou anomalias de atribuição, valor null em tuplas e não permissão de tuplas espúrias).


3)O que é e para que serve o conceito de dependência funcional? Quais são os tipos de dependência? Explique-os

R: dependência funcional: Dependências Funcionais são restrições ao conjunto de relações válidas. Elas permitem expressar determinados fatos em banco de dados relativos ao empreendimento que se deseja modelar. Anteriormente foi definido o conceito de superchave. Para existir o destino (dependência -->chave estrangeira) tem que existir a origem (chave primaria). O atributo deve realmente caracterizar a relação.


4)O que é e para que serve normalização de dados relacionais? Quando será utilizada a normalização na maioria das vezes?

R: Eliminar redundâncias e inconsistências de um banco de dados, com reorganização mínima dos dados, é o processo pelo qual transformamos um BD fora do padrão do m-rel, num BD normalizado (dentro do padrão m-rel). Normalmente é usado em BD antigos ou criado por pessoa não técnica.


5)O que são e quantas são as formas formais de relação? Explique-as resumidamente. Para manter eficiência e a simplicidade de processamento em certos casos podemos normalizar as relações até a 3ºFN por quê?

R: O objetivo da normalização de um banco de dados é evitar os problemas que podem provocar falhas no projeto do banco de dados, bem como eliminar a mistura de assuntos e as correspondentes redundâncias dos dados desnecessárias. O processo de normalização aplica uma série de regras sobre as tabelas (também chamadas de relações) de um banco de dados, para verificar se estão corretamente projetadas.
Primeira Forma Normal: A primeira forma normal enuncia que cada atributo de uma entidade ou relacionamento pode armazenar apenas um valor. Tabelas com atributos multi-valorados não são consideradas em 1NF.
Segunda Forma Normal: A segunda forma normal (2NF) descreve que todo atributo deve ser determinado unicamente pela chave primária. Se existem atributos que dependem apenas de parte da chave, estes devem ser separados em tabelas onde a 2NF seja obedecida.
Terceira Forma Normal: A terceira forma normal (3NF) exige que a tabela esteja em 2NF e que todos os atributos que não são chave sejam mutuamente independentes, isto é, que não existam funções que definam um ao outro. Portanto, sempre a chave por inteiro deve definir toda a tabela.
Forma normal Boyce/Codd (BCNF): Definição que engloba as outras formas normais, e define que uma tabela está em BCNF se, e somente se, todo determinante funcional for em relação a uma chave candidata. Na prática, uma tabela está em BCNF se estiver em 3NF e não existir dependência funcional dentro da chave primária.


6- Dê exemplos de normalizações de uma relação.

Tabela normalizada
Atributos não atômicos ou contém tabelas aninhadas
Exemplo: Tabela de alocação de funcionários a projetos
Código do Projeto: 1
Tipo: Desenvolvimento
Descrição: Vagas




A seguinte tabela descreveria os dados acima apresentados: Projetos(codp, tipo, descrição, empregados(code, nome, categ, salário,data_início, tempo_aloc)).
Tabela não normalizada empregados é um atributo não atômico.
[editar] Primeira Forma Normal
Definição (note que relacionamentos como são definidos acima são necessariamente na 1NF)
"Uma tabela está na 1FN, se e somente se, não possuir atributos multivalor."
Definir relações NFNF
• como transformar relações NFNF (também chamadas relações UNF) em relações 1NF
o como transformar as restrições chave de relações aninhadas
o como transformar as dependências funcionais de relações aninhadas
Passagem à 1FN:
• Gerar uma única tabela com colunas simples
• Chave primária : id de cada tabela aninhada
Exemplo: Projetos(codp, tipo, descrição, code, nome, categ, salário, data_início, tempo_aloc)
Problemas:
• Redundância
• Anomalias de Atualização
[editar] Segunda Forma Normal
Definição:
Uma relação está na 2FN se, e somente se, estiver na 1FN e cada atributo não-chave for dependente
da chave primária inteira, isto é, cada atributo não-chave não poderá ser dependente de apenas
parte da chave.
No caso de tabelas com chave primária composta. Se um atributo depende apenas de uma parte da chave primária, então esse atributo deve ser colocado em outra tabela.
Passagem à 2FN:
• Geração de novas tabelas com DFs completas
• Análise de DFs:
* tipo e descrição - DF de codp
* nome, categ e salário - DF de code
* data_início e tempo_aloc - DF de toda a chave
Resultado:
• Projetos(codp, tipo, descrição)
• Empregados(code, nome, categ, salário)
• ProjEmp(codp, code, data_início, tempo_aloc)
Conclusões:
• Maior independência de dados (não há mais repetição de empregados por projeto, por exemplo)
• Redundâncias + Anomalias - DF indiretas
[editar] Terceira Forma Normal
• definição
“ Uma relação R está na 3NF, se ela estiver na 2NF e cada atributo não chave de R
não possui dependência transitiva, para cada chave candidata de R.”
Passagem à 3FN:
• Geração de novas tabelas com DF diretas
• Análise de DFs entre atributos não chave:
- salário - DF de categ
Resultado:
• Projetos(codp, tipo, descrição)
• Empregados(code, nome, categ)
• Categorias(categ, salário)
• ProjEmp(codp, code, data_início, tempo_aloc)
Conclusões:
• Maior independência de dados
• 3FN gera representações lógicas finais na maioria das vezes
• Redundâncias + Anomalias - DF multivaloradas

O que significa a sigla SQL

1) O que significa a sigla SQL? Qual a finalidade dessa linguagem? Como ela se divide? Quais são os comandos principais de cada divisão? Explique-os resumidamente.

R:Structured Query Language ou Linguagem de consulta estruturada ou SQL, é uma linguagem de pesquisa declarativa para banco de dados relacional. Muitas das características originais do SQL foram inspiradas na álgebra relacional.
O SQL foi desenvolvido originalmente no inicio dos anos 70 no laboratório da IBM em San Jose, dentro do projeto System R, que tinha por objetivo demonstrar a viabilidade da implementação do modelo relacional proposto por E. F. Cold.
A DML é um subconjunto da linguagem usada para inserir, atualizar e apagar dados.
INSERT – é usado para inserir um registro (formalmente tupla) a uma tabela existente.
DELETE – para mudar os valores de dados em uma ou mais linhas da tabela existente.


2) Quais são os tipos de dados que a linguagem SQL padrão (ANSI) suporta? Explique cada um resumidamente.

DML – linguagem de manipulação de Dados
DDL – linguagem de definição de dados
DCL – linguagem de controle de dados
DTL – linguagem de transação de dados
DQL – linguagem de consulta de dados


3) Explique o comando SELECT. Sua sintaxe, características básicas, Cláusula Where, operadores, conectores, subconsultas, funções de agregação, cláusula order by, group by, having e compute by.

O comando SELECT permite o usuário especificar uma consulta como uma descrição do resultado desejado.

Clausulas

FROM – utilizada para especificar a tabela que se vai selecionar os registros
WHERE – utilizada para especificar as condições que devem reunir os registros que serão selecionados
GROUP BY - utilizada para separar os registros selecionados em grupos específicos
HAVING – utilizada para expressar a condição que deve satisfazer cada grupo
ORDER BY – utilizada para ordenar os registros selecionados com uma ordem especifica
DISTINCT – utilizada para selecionar dados sem repetição

Operadores lógicos

AND E – avalia as condições e devolve um valor verdadeiro caso ambos sejam corretos
OR OU – avalia as condições e devolve um valor verdadeiro se algum for correto
NOT negação – devolve o valor contrario a expressão

Operadores Relacionais

<>maior que
<> diferente de
<= menor ou igual que >=maior ou igual que
= igual a

BETWEEN – é utilizado para especificar um intervalo de valores
LIKE – é utilizado na comparação de um modelo e para especificar registros de um banco de dados.

4 - o que é uma visão (view) em SQL? Explique suas propriedades, utilidades e os comandos para a sua manipulação.

Uma visão ou view no contexto dos bancos de dados é uma relação que não armazena dados, composta dinamicamente por uma consulta que é previamente analisada e otimizada.
Basicamente VIEW é uma tabela virtual gerada a partir do resultado de uma instrução SELECT.
Uma VIEW contem linhas e colunas, como se fosse uma tabela real, os campos na view são capôs obtidos em uma tabela de um banco de dados.


5 - O que são procedimentos armazenados (stored procedure)? Para que servem, o que eles permite fazer e quais comandos podemos utilizar no seu corpo e para criá-los?

Procedimento armazenado é uma coleção de comando SQL para dispensamento de banco de dados, encapsula tarefas repetitivas, aceita parâmetros de entrada e retorna um valor de status. O procedimento armazenado pode reduzir o tráfego na rede, melhorar a performance, criar mecanismos de segurança.

Exemplo (MS-SQL Server)

Create procedure busca
@nomedebusca varchar (50)
As
Select nome1, nome2
From nome_da_tabela
Where nome = @nomedebusca

6 - O que são gatilhos (TRIGGERS)? Para que servem, quais as vantagens na sua utilização, como funcionam e como criá-los?

Gatilho é um recurso de programação executado sempre que o evento associado ocorrer, é muito utilizado para ajudar a manter a consistência dos dados ou para propagar alterações em um determinado dado de uma tabela para outra. Um bom exemplo é um gatilho criado para controle de quem alterou a tabela, nesse caso, quando a alteração for efetuada o gatilho é disparado e grava em uma tabela de histórico de alteração, o usuário e data/hora de alteração.

Exemplo (MS-SQL Server)

CREATE TRIGGER nome_do_gatilho ON dono.Nome_da_tabela
FOR INSERT (ou UPDATE ou DELETE)
AS
Código para execução

Álgebra Relacional

1) Quantos e Quais são os grupos nos quais a álgebra relacional está dividida? Identifique quais operações pertencem a cada um deles.

R: As operações da álgebra relacional são normalmente divididas em dois grupos. O primeiro
deles inclui um conjunto de operações da teoria de conjuntos. As operações são UNION,
INTERSECTION, DIFFERENCE e CARTESIAN PRODUCT. O segundo grupo consiste de
operações desenvolvidas especificamente para bases de dados relacionais, tais como:

SELECT, PROJECT e JOIN entre outras.


2) Explique a Operação SELECT e PROJECT. Quando usar, Para que usar, Como usar, diferenças, etc.

R: A operação SELECT é usada para selecionar um subconjunto de tuplas de uma relação. Estas
tuplas devem satisfazer uma condição de seleção. Por exemplo, a seleção de um subconjunto
de tuplas da relação EMPREGADOS que trabalham para o departamento 4 ou que tenham
salário maior que 3000. Cada uma dessas condições é especificada individualmente usando a
operação SELECT como segue:

NDEP = 4 (EMPREGADO)

SALÁRIO > 3000 (EMPREGADO)

Em geral, a operação SELECT é denotada por:

()

onde o símbolo é usado para denotar o operador SELECT, e a condição de seleção é uma
expressão Booleana especificada sobre atributos da relação especificada.
A relação resultante da operação SELECT tem os mesmos atributos da relação especificada
em . A expressão Booleana especificada em é
construída a partir de cláusulas da forma:
, ou

O Operador PROJECT:

Pensando na relação como uma tabela, então o operador SELECT seleciona algumas linhas da tabela enquanto descarta outras. O operador PROJECT, por outro lado, seleciona certas colunas da tabela e descarta outras. Se existir o interesse sobre certos atributos da relação pode-se usar o PROJECT para “projetar” a relação sobre esses atributos. Por exemplo, suponha a necessidade de listar, para cada empregado, os atributos PNOME, SNOME e SALÁRIO; então pode-se usar o PROJECT como segue:

SNOME, PNOME, SALÁRIO (EMPREGADO)

A forma geral do operador PROJECT é:

()

onde é o símbolo usado para representar o operador PROJECT e é uma lista de atributos da relação especificada por . A relação resultante tem apenas os atributos especificados em e aparecem na mesma ordem que aparecem na lista. Assim, o grau é igual ao número de atributos em .


3) Explique a Operação UNION, INTERSECTION e DIFFERENCE. Quando usar, Para que usar, Como usar, etc.

R: Existem várias operações da teoria de conjuntos que são utilizadas para agrupar elementos de
dois conjuntos, entre elas estão: UNION, INTERSECTION e DEFFERENCE. Estas operações
são binárias; isto é, elas necessitam de dois conjuntos. Quando essas operações são adaptadas para a base de dados relacional deve-se assegurar que essas operações resultem sempre em relações válidas.
Pode-se definir as três operações UNION, INTERSECTION e DIFFERENCE sobre duas relações que sejam união compatível R e S:

· UNION ¾ O resultado da operação, denotado por R È S, é uma relação que inclui todas as tuplas de R e todas as tuplas de S. Tuplas duplicadas são eliminadas.

· INTERSECTION ¾ O resultado desta operação, denotado por R Ç S, é a relação que inclui todas as tuplas que são comuns a R e S.

· DIFFERENCE ¾ O resultado desta operação, denotado por R - S, é a relação que inclui todas as tuplas de R, mas que não estão em S.


4) Explique a Operação CARTESIAN PRODUCT. Quando usar, Para que usar, Como usar, etc.

R: A operação CARTESIAN PRODUCT, denotada por c, é também uma operação de conjunto
binária, mas as relações sobre as quais são aplicadas não necessitam ser união compatível.
Esta operação é usada para combinar tuplas de duas relações tal que tuplas relacionadas possam ser identificadas.

O CARTESIAN PRODUCT cria tuplas com atributos combinados de duas relações. Pode-se então selecionar apenas as tuplas que estejam relacionadas especificando uma condição de
seleção apropriada, como foi feita no exemplo. Devido à seqüência: CARTESIAN PRODUCT
seguido de SELECT, ser muito comum para se identificar tuplas relacionadas de duas relações,
uma operação especial JOIN foi criada para especificar esta seqüência como uma única operação. Assim, a operação CARTESIAN PRODUCT é raramente utilizada isoladamente.



5) Explique as Operações de junção (JOIN) e divisão (DIVISION). Quando usar, Para que usar, Como usar, etc.

R: A operação JOIN, denotada por x, é usada para combinar tuplas relacionadas de relações em
uma única tupla. Esta operação é muito importante para quaisquer bases de dados relacionais,
pois permite processar relacionamentos entre relações. Para ilustrar a operação JOIN, suponha
que se deseja recuperar os nomes dos gerentes de cada departamento. Para obter-se o nome
dos gerentes, é necessário combinar cada tupla de departamento com tuplas de empregados
cujo valor NSS seja igual ao valor de SNNGER na tupla departamento. Isto é feito usando a operação JOIN, então projeta-se o resultado sobre aqueles atributos necessários:
55

DEPT_GER¬DEPARTAMENTO xSNNGER=NSS EMPREGADO

RESULT¬pDNOME, SNOME, PNOME (DEPT_GER)


A operação DIVISION é útil para um tipo especial de consulta que ocorre freqüentemente em
aplicações de base de dados. Esta requisição pode ser ilustrada pela seguinte consulta:
“Recupere os nomes dos empregados que trabalham em todos os projetos em que ‘John Smith’ trabalha. Para expressar esta consulta usando DIVISION deve-se fazer o seguinte:
primeiro recuperar a lista de números de projetos em que ‘John Smith’ trabalha em uma relação
intermediária SMITH_PNOS:

SMITH¬s PNOME = ‘John’ AND SNOME = ‘Smith’ (EMPREGADO)

SMITH_PNOS¬p PNRO (TRABALHA_EM * NSSEMP = NSS SMITH)

Depois, criar uma relação que inclua tuplas da forma que lista todos os empregados, cujo número do segura social é NSSEMP, que trabalham num determinado projeto PNRO:

NSS_PNRO¬p PNRO, NSSEMP (TRABALHA_EM)

Finalmente, aplicar a operação DIVISION para as relações obtidas a fim de obter os números dos seguros sociais desejados:

NSS_DESEJADO (NSS)¬NSS_PNRO ¸ SIMTH_PNOS

RESULT¬p PNOME, SNOME (NSS_DESEJADO * EMPREGADO)



6) Explique as Operações de AGREGAÇÃO. Quando usar, Para que usar, Como usar, etc.

R: O primeiro tipo de consulta que não pode ser expressa na álgebra relacional é conhecido como
funções agregadas sobre coleções de valores da base de dados. Por exemplo, pode-se querer recuperar a média ou total salarial de todos os empregados ou o número de tuplas de
empregados. As funções normalmente aplicadas para coleções de valores numéricos são:

SUM, AVERAGE, MAXIMUM e MINIMUM. A função de contagem de tuplas é normalmente chamada COUNT. Cada uma destas funções pode ser aplicada a todas as tuplas de uma relação.

Banco de Dados - DB2 IBM