memorex banco de dados

MEMOREX BANCO DE DADOS
por Paulo Marcelo
[email protected]
10/10/2009
Esse é o terceiro memorex destinado aos concurseiros de TI. Neste material, foram compilados os
principais conceitos sobre banco de dados e as cláusulas SQL avançadas (grupos, funções agregadas,
subconsultas, etc) que costumam confundir até os candidatos mais experientes sobre o assunto. Questões
comentadas encontram-se ao final do material. Agradecimento ao concurseiro Diogo Fagundes [diofagor]
que pacientemente comentou a maioria das questões. Bons estudos a todos!
Memorex anteriores:
http://www.scribd.com/doc/16787717/Memorex-Redes
http://www.scribd.com/doc/17101886/Memorex-PMBOK-2004
Errata:
No Memorex de Redes a seguinte questão elaborada pelo cespe: “O criptossistema Diffie Hellman
define uma forma segura de troca de chaves” está errada devido a vulnerabilidade ao ataque man-in-themiddle e nada tem a ver com termo “criptossistema” como consta no material.
O texto a seguir foi compartilhado por um membro da lista timaster. Fica a reflexão.
Um Meio ou uma Desculpa
Não conheço ninguém que conseguiu realizar seu sonho, sem sacrificar feriados e domingos pelo
menos uma centena de vezes, da mesma forma, se você quiser construir uma relação amiga com seus filhos,
terá que se dedicar a isso, superar o cansaço, arrumar tempo para ficar com eles, deixar de lado o orgulho e
o comodismo. Se quiser um casamento gratificante, terá que investir tempo, energia e sentimentos nesse
objetivo.
O sucesso é construído à noite! Durante o dia você faz o que todos fazem.
Mas, para obter um resultado diferente da maioria, você tem que ser especial. Se fizer igual a todo
mundo, obterá os mesmos resultados, não se compare à maioria, pois infelizmente ela não é modelo de
sucesso, se você quiser atingir uma meta especial, terá que estudar no horário em que os outros estão
tomando chopp com batatas fritas. Terá de planejar, enquanto os outros permanecem à frente da televisão.
Terá de trabalhar enquanto os outros tomam sol à beira da piscina.
A realização de um sonho depende de dedicação, há muita gente que espera que o sonho se realize
por mágica, mas toda mágica é ilusão, e a ilusão não tira ninguém de onde está, em verdade a ilusão é o
combustível dos perdedores, pois....
'Quem quer fazer alguma coisa, encontra um meio'. 'Quem não quer fazer nada, encontra uma
desculpa'.
Roberto Shinyashiki
Paulo Marcelo
Memorex Banco de Dados
[email protected]
CHAVES
Especifica como entidades e relacionamentos são identificados.
Conceitualmente, entidades e relacionamentos individuais são distintos, mas
numa perspectiva de banco de dados a diferença entre eles precisa ser expressa
em termos de seus atributos.
O conceito de super-chave permite-nos fazer tais distinções. Uma superchave é um conjunto de um ou mais atributos que, tomando coletivamente,
permite-nos identificar unicamente uma entidade no conjunto de entidades. Por
exemplo, o atributo SeguridadeSocial do conjunto de entidades cliente é
suficiente para distinguir uma entidade cliente das outras. Desta forma,
seguridade-social é uma super-chave. De forma semelhante, a combinação
NomeCliente e SeguridadeSocial é uma super-chave para o conjunto de
entidades cliente. O atributo NomeCliente de cliente não é uma super-chave, pois diversas pessoas podem ter o mesmo
nome.
O conceito de super-chave não é suficiente para nossos propósitos, pois ela pode ter atributos redundantes (ou seja
atributos não necessários para serem únicos). Ex: se K é uma super-chave, então um subconjunto de K também poderá ser.
Toda a relação tem, no mínimo, uma super-chave que é a junção de todos os seus atributos.
Frequentemente, procuramos super-chaves que não contenham nenhum subconjunto próprio que seja uma superchave. Tais super-chaves mínimas são chamadas chaves candidatas.
Utilizaremos o termo chave primária para denotar uma chave candidata que é escolhida por um projetista de
banco de dados como meio principal de identificação de entidades dentro de um conjunto de entidades.
É possível que diversos conjuntos distintos de atributos possam servir como uma chave candidata. Suponha que uma
combinação de NomeCliente e rua seja suficiente para distinguir entre membros do conjunto de entidades cliente. Então
ambos, {SeguridadeSocial} e {NomeCliente, rua}, são chaves candidatas. Embora os atributos SeguridadeSocial e
NomeCliente juntos possam distinguir entidades de cliente, sua combinação não forma uma chave candidata, uma vez que
o atributo SeguridadeSocial sozinho é uma chave candidata.
Chaves estrangeiras podem ser definidas como sendo um conjunto de atributos pertencentes a um esquema de
relação que constituem chaves primárias ou candidatas em outros esquemas independentes. Diferentemente da chave
primária, uma chave estrangeira pode conter valores nulos.
Chave primária e chave estrangeira não podem ser multivaloradas
Normatização - Um aspecto importante na modelagem de dados consiste na normalização de dados, realizada com o
objetivo de eliminar redundâncias e possíveis inconsistências no modelo de dados. Várias tabelas estreitas com menos
colunas são características de um banco de dados normalizado. Poucas tabelas largas com mais colunas são características
de um banco de dados não normalizado.
Diz-se que uma tabela está na primeira forma normal quando ela não contém tabelas aninhadas (grupo repetido
1FN:
ou coluna multivalorada ou coluna não atômica)
Uma tabela está na 2FN se, além de estar na 1FN, não possuir dependências funcionais parciais, ou seja, todos
2FN:
os atributos não-chave devem depender funcionalmente da chave primária inteira. Só ocorre com chaves
primárias compostas
Uma tabela está na 3FN se, além de estar na 2FN, não possuir dependências transitivas entre os atributos.
3NF:
Dependências transitivas ocorrem quando um atributo não-chave depende de outro que não é chave da relação
BCNF : Uma tabela está na BCNF, se e somente se, estiver na 3FN e houve dependências funcionais não triviais.
Alguns benefícios da normalização incluem o seguinte:
• Classificação e criação mais rápidas dos índices.
• Um número maior de índices clusterizados
• Índices mais estreitos e mais compactados.
• Menos índices por tabela. Isto melhora o desempenho das instruções INSERT, UPDATE e DELETE.
• Menos valores nulos e menos oportunidades de inconsistências. Isto aumenta a densidade do banco de
dados.
Paulo Marcelo
Memorex Banco de Dados
[email protected]
Uma associação NxM viola a 1FN devido a ocorrência de repetições de tuplas do lado da chave primária.
CESPE: No modelo ER, um atributo cujos valores são distintos para cada entidade individual de um conjunto de
entidades de determinado tipo é denominado atributo-chave. [CORRETO]
CESPE: O grau de um tipo de relacionamento é o número de tipos de entidades participantes desse tipo de
relacionamento. [CORRETO] Trata do conceito de grau para o modelo entidade-relacionamento que é o número de
entidades que participam de relação, por exemplo, no relacionamento ternário o grau é 3, uma vez que três entidades se
relacionam.
CESPE: No modelo de banco de dados relacional, o grau de uma relação é o número de atributos do esquema dessa
relação [CORRETO] O conceito de grau no Modelo Relacional é diferente do de Modelo E/R. No Modelo Relacional o grau é
dado pelo número de atributos, enquanto no E/R é dado pelo número de entidades
Figura: MER x Modelo Relacional
Paulo Marcelo
Memorex Banco de Dados
[email protected]
Figura: No modelo relacional, ao número de atributos que constituem o esquema de uma relação dá-se o nome de
grau da relação. Por sua vez, o número de tuplas de uma relação designa-se por cardinalidade da relação.
Regras de transformação de um modelo entidaderelacionamento (conceitual) para o modelo relacional lógico:
1) Uma entidade forte será representada por uma relação (tabela);
2) Para cada relacionamento binário M:N, será criado três relações: duas
relações a partir das entidades envolvidas e uma nova relação (tabela auxiliar).
A tabela auxiliar terá duas chaves estrangeiras referenciando as demais
relações, cada chave estrangeira corresponderá a chave primária das relações
referenciadas.
CESPE - Os relacionamentos muitos-para-muitos entre dois tipos de entidades, tais como relacionamentos entre
funcionários e projetos, não podem ser representados diretamente na modelagem E-R.
[ERRADO] O modelo entidade-relacionamento pode representar todas as restrições de cardinalidade: um para um,
um para muitos, muitos para um e muitos para muitos.
CESPE - O modelo relacional clássico não permite a realização de relacionamentos com multiplicidade de muitos para
muitos.
[CORRETO] Deve-se adicionar uma tabela para deixar o modelo relacional na forma 1:N
Entidade fraca é uma entidade dependente de
outra entidade. Geralmente tem uma associação de
1:N e a entidade fraca caracteriza-se por não possuir
um atributo chave. Por exemplo, observe o seguinte
diagrama que pretende modelar os funcionários de
uma empresa e o seu agregado familiar.
Paulo Marcelo
Memorex Banco de Dados
[email protected]
Em um conjunto de entidades sem os atributos necessários para formar uma chave primária, ou seja, em um conjunto de
entidades fracas, a chave primária é formada pela chave primária do conjunto de entidades fortes ao qual a existência do
conjunto de entidades fracas está vinculada e por um identificador do conjunto de entidades fracas.
[CORRETA] Imagine que você queira identificar filhos de uma entidade Pessoa! Como identificá-los (escolher o mais velho,
por exemplo) sem um identificador como a ordem de nascimento deles? Você realmente precisa de um identificador da
entidade fraca! Além, é claro, da chave primária da entidade forte! [WelkeyCosta]
Em um modelo entidade-relacionamento, um relacionamento entre duas entidades é sempre bidirecional.
[CORRETO] No MER não há unidirecinamento como na UML, entretanto esse não é o único conceito que diferencia o MER
da UML, na UML podemos representar a visibilidade de um atributo e representar os métodos, no MER não; etc.
Dependência Funcional: A à B,
A funcionalmente determina B
lê-se:
B funcionalmente depende de A
B é função de A
Se o valor de uma atributo A permite descobrir o valor de outro atributo B, dizemos que A determina funcionalmente B
(A à B). Por exemplo: NumeroMatricula à NomeAluno, idade, curso. Assim, sempre que o NumeroMatricula se repetir
tem-se a repetição de NomeAluno, idade e curso;
Dependência Não Transitiva: Ocorre quando cada atributo for funcionalmente dependente apenas dos atributos
componentes da chave primária ou se todos os seus atributos não chave forem independentes entre si.
RESTRIÇÕES/ INTEGRIDADES
Uma restrição de unicidade (também chamada “integridade de entidade” ou unique constraint) é uma regra que previne
valores duplicados em uma ou mais colunas em uma tabela. Restrições de unicidade são chaves únicas e chaves primárias.
Por exemplo, uma restrição de unicidade pode ser definida em um identificador de fornecedor (supplier identifier) em
uma tabela de fornecedor (supplier table) para assegurar que o mesmo identificador de fornecedor não seja atribuído a
dois fornecedores (suppliers).
Uma restrição referencial é uma regra sobre valores em uma ou mais colunas em uma ou mais tabelas. Por exemplo, um
conjunto de tabelas compartilha informação sobre fornecedores de uma corporação. Ocasionalmente, um identificador
(ID) de fornecedor muda. Pode-se definir uma restrição referencial ditando que o ID do fornecedor na tabela deve (must)
igualar-se ao ID do fornecedor nas informações sobre o fornecedor. A restrição previne operações de inserção, atualização
ou deleção que poderiam de outro modo resultar no desaparecimento de informações do fornecedor.
Uma restrição de checagem (também chamada integridade de domínio) determina restrições em dados adicionados a
uma tabela específica. Por exemplo, a restrição pode ser adicionada para definir que o nível salarial de um empregado
nunca seja menor que um determinado valor quando os dados salariais forem adicinados ou atualizados em uma tabela
de informações de pessoal.
(CESPE/STF2008) Integridade referencial pode ser definida como uma condição imposta a um conjunto de atributos de uma
relação para que valores que apareçam nesse conjunto também apareçam em um certo conjunto de atributos de uma
outra relação. GAB: C
Paulo Marcelo
Memorex Banco de Dados
[email protected]
Uma restrição de integridade UNIQUE KEY requer que cada valor em uma coluna ou
conjunto de colunas (chave) seja exclusivo — ou seja, duas linhas de uma tabela não
podem ter valores duplicados em uma coluna específica ou conjunto de colunas. A
coluna (ou conjunto de colunas) incluída na definição da restrição UNIQUE KEY é
chamada de chave exclusiva. Se a chave UNIQUE contiver mais de uma coluna, tal grupo
de colunas é considerado uma chave exclusiva composta. As restrições UNIQUE KEY
permitem a entrada de valores nulos a menos que você defina as restrições NOT NULL
para as mesmas colunas. Na realidade, qualquer número de linhas pode incluir valores
nulos para colunas sem restrições NOT NULL porque os valores nulos não são
considerados. Um valor nulo em uma coluna (ou em todas as colunas de uma chave
UNIQUE composta) sempre satisfaz uma restrição de UNIQUE KEY.
Paulo Marcelo
Memorex Banco de Dados
[email protected]
OBS: Sem a opção ON DELETE CASCADE, a linha da tabela mãe não poderá ser deletada quando houver
referência a ela na tabela filha.
Paulo Marcelo
Memorex Banco de Dados
[email protected]
A restrição CHECK (restrição de checagem ou domínio) define uma condição que cada linha deve satisfazer
Projeção: você pode usar o recurso de projeção da
linguagem SQL para escolher as colunas de uma tabela que
devem ser retornadas por uma consulta. É possível escolher
o número de colunas que for necessário da tabela. É
representada pela letra grega Pi. A projeção é alcançada
com a cláusula SELECT
Seleção ou Restrição: você pode usar o recurso de seleção da
linguagem SQL para escolher as linhas de uma tabela que
devem ser retornadas por uma consulta e pode usar vários
critérios para restringir as linhas exibidas. É representada
pela letra grega Sigma. A Seleção é obtida através da cláusula
WHERE
Junção: você pode usar o recurso de junção da linguagem SQL para reunir dados armazenados em diferentes tabelas,
criando um vínculo entre eles.
A álgebra relacional consiste de operações cujos nomes vêm da teoria de conjuntos e as principais são:
UNIÃO (UNION)
Que produz uma tabela resultado da união das tabelas operadas
INTERSEÇÃO (INTERSECT)
Que cria uma tabela resultado da interseção das tabelas operadas
Paulo Marcelo
Memorex Banco de Dados
[email protected]
DIFERENÇA (EXCEPT)
Que cria uma tabela contendo tuplas que pertencem à primeira tabela operada, mas não pertencem à segunda.
PRODUTO CARTESIANO
Que gera todas as combinações possíveis entre as t-uplas de duas tabelas.
Esquema
Descrição (textual ou gráfica) da estrutura de um banco de dados de acordo com um determinado modelo de
dados
Instância
Conjunto de dados armazenados em um banco de dados em um determinado instante de tempo.
Paulo Marcelo
Memorex Banco de Dados
[email protected]
TRASAÇÃO (MUDANÇA DE ESTADO/COMPORTAMENTO)
Mudanças de estado em um banco de dados são efetuadas por transações. Uma transação é um conjunto de operações
que levam o banco de dados de um estado consistente a outro estado consistente. Mudanças de estado de um banco de
dados representam o comportamento desse banco de dados.
CONSISTÊNCIA
Na propriedade de Consistência, a execução de uma transação isolada (isto é, sem a execução concorrente de outra
transação) preserva a consistência do banco de dados. A Consistência garante que uma transação deve ser um programa
correto, e suas ações não devem resultar em violações de restrições de integridade definidas para o banco de dados.
Assegurar a propriedade de Consistência após uma transação é tarefa do programador que codifica a transação.
Paulo Marcelo
Memorex Banco de Dados
[email protected]
PERSITÊNCIA (DURABILIDADE)
Depois de uma transação completar com sucesso (commit), as mudanças que ela fez no banco de dados persistem,
até mesmo se houver falhas no sistema. A Durabilidade é assegurada também pelo próprio sistema de banco de dados,
mais especificamente pelo componente de Gerenciamento de Recuperação.
Paulo Marcelo
Memorex Banco de Dados
[email protected]
MODELO ENTIDADE RELACIONAMENTO (MER)
Paulo Marcelo
Memorex Banco de Dados
[email protected]
A notação pé-de-galinha (crow’s foot) é usada em metodologias alternativas e é representada:
Paulo Marcelo
Memorex Banco de Dados
[email protected]