Semântica de Atributos: verifica se o atributo caracteriza (pertence) mesmo aquela entidade.
Informações Redundantes ou Anomalias de Atualização: verifica se os atributos geram redundância e, portanto desperdício de espaço.
Valor Null em Tuplas: uma relação degenerada (“Tabelão”) pode causar valores Null, o que prejudica a interpretação dos dados.
Não Permissão de Tuplas Espúrias: tabelas degeneradas, quando relacionadas podem gerar informações/dados errôneos (Tuplas espúrias).
2-Quais são as métricas de qualidade informal para projeto de esquemas de relações? Explique resumidamente cada uma delas.
São 4 diretrizes:
• Semântica de atributos.
Projete um esquema de relação de tal forma que seja fácil explicar o seu significado semântico.
• Redução de valores redundantes em tuplas.
Minimizam o espaço de armazenamento que as relações ocupam.
• Redução de valores nulos em tuplas.
Se muitos atributos não são aplicados a varias tuplas, ficam com muitos valores nulos, provocando desperdício de espaço.
• Não permissão de tuplas espúrias.
Projete esquemas de relação de tal forma que as junções entre as relações correspondentes possam ser feitas através de condições de igualdade sobre atributos que são chaves primárias ou chaves estrangeiras de forma a garantir a não geraçã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
Dependência funcional: para existir o destino (dependência → chave estrangeira) tem que existir a origem (chave primária). O atributo deve realmente caracterizar a relação.
É uma propriedade da semântica ou do significado dos atributos. Formalmente, uma dependência funcional entre dois conjuntos de atributos, x e y, que são subconjuntos de um esquema de relação R, denotada por x® y é uma restrição que estabelece que para quaisquer tuplas t1 e t2 de uma instância r de R, tal que, se temos t1[x] = t2[x], então também devemos ter que t1[y] = t2[y]. Em outras palavras, os valores do componente y em uma tupla de r dependem de (ou são determinados por) valores do componente x.
Se A determina B então B não é funcionalmente dependente de nenhum subconjunto de A.
Uma dependência funcional é representada por:
A --> B A _ denominado determinante.
4-O que é e para que serve normalização de dados relacionais? Quando será utilizada a normalização na maioria das vezes?
Normalização é o processo pelo qual formamos 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ê?
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 (1FN)
A primeira forma normal é agora genericamente considerada como parte da definição formal de uma relação; historicamente foi definida para não permitir atributos multivalorados, compostos e suas combinações.
Segunda Forma Normal (2FN):
Um esquema de relação está na 2FN se: estiver na 1FN e, além disso, todo atributo que não pertença a alguma de suas chaves for totalmente dependente da sua chave primária. Em outras palavras, para que uma relação esteja na 2FN é preciso que esteja na 1FN e que, havendo uma chave primária composta, todos os dados que não são chaves dependem de toda a chave primária (a chave primária completa).
Terceira Forma Normal (3FN):
Um esquema de relação está na 3FN se: estiver na 2FN e, além disso, nenhum atributo que não pertença a alguma das suas chaves for transitivamente dependente da sua chave primária. Em outras palavras, para que uma relação esteja na 3FN é preciso que esteja na 2FN e todo atributo, que não pertença a alguma chave for não dependente de algum outro atributo, que também não pertença a alguma chave.
6-Dê exemplos de normalizações de uma relação.
FUNCIONÁRIOS = {CODFUNC + NOME + CARGO + {PROJETO + DATAINI + DATA FIM}}
Para colocar na 1FN:
FUNCIONÁRIOS = {CODFUNC + NOME + CARGO}
FUNC_PROJ = {CODFUNC + PROJETO + DATAINI + DATA FIM}
Comandos e Conceitos do 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.
SQL (estructured query language) linguagem de consulta estruturada.
• Desenvolvida na década de 70 pela IBM para bd relacionais com o nome de Sequel.
• Surgiu para padronizar a manipulação dos dados de bd.
• Padronizada pela ANSI e pela ISO em 92,99 e 2003.
• Em 99 incorporou os conceitosde trigger, stored procedure, consultas recursivas e orientação a objetos.
DIVISÃO:
• DDL (data defition linguage) linguagem de definição de dados, responsável pela criação da estrutura do bd
Principais comandos:
• Creat cria
• Drop apaga
• Alter altera
* Objetos (tabelas, views ,procedures e etc)
• DML (data manipulation language) linguagem de manipulação de dados, responsável pela entrada e saída de dados
Principais comandos:
• Insert insere
• Delete deleta
• Update altera
* Dados na tabela
• DCL (data control language) linguagem de controle de dados, responsável pela segurança nos SGBD
Comandos:
• Grant atribuir autoriza
• Revoke retira desautoriza
*permissão a manipulação dos dados.
2_Quais são os tipos de dados que a linguagem SQL padrão (ANSI) suporta? Explique cada um resumidamente.
A SQL estabeleceu-se como linguagem padrão de Banco de Dados Relacional. SQL apresenta uma série de comandos que permitem a definição dos dados, chamada de DDL (Data Definition Language), composta entre outros pelos comandos Create, que é destinado a criação do Banco de Dados, das Tabelas que o compõe, além das relações existentes entre as tabelas. Como exemplo de comandos da classe DDL temos os comandos Create, Alter e Drop. Os comandos da série DML (Data Manipulation Language), destinados a consultas, inserções, exclusões e alterações em um ou mais registros de uma ou mais tabelas de maneira simultânea. Como exemplo de comandos da classe DML temos os comandos Select, Insert, Update e Delete. Uma subclasse de comandos DML, a DCL (Data Control Language), dispõe de comandos de controle como Grant e Revoke.
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.
SELECT: Instrui o programa principal do banco de dados para retornar a informação como um conjunto de registros.
Sintaxe
SELECT [predicado { * | tabela.* | [tabela.]campo1 [AS alias1] [, [tabela.]campo2 [AS alias2] [, ...]]}
FROM expressãotabela [, ...] [IN bancodedadosexterno]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
A cláusula "where" corresponde ao operador restrição da álgebra relacional. Contém a condição que as tuplas devem obedecer a fim de serem listadas. Ela pode comparar valores em colunas, literais, expressões aritméticas ou funções.
Operadores lógicos
Operador Significado
= --> igual a
> --> maior que
>= --> maior que ou igual a
< --> menor que
<= --> menor que ou igual a
Demais Operadores
Operador Significado
between ... and ... entre dois valores ( inclusive )
in ( .... ) lista de valores
like com um padrao de caracteres
is null é um valor nulo
Operadores Negativos
operador descrição
<> diferente
not nome_coluna = diferente da coluna
not nome_coluna > não maior que
not between não entre dois valores informados
not in não existente numa dada lista de valores
not like diferente do padrao de caracteres informado
is not null não é um valor nulo
Funções Agregadas (ou de Agrupamento)
função retorno
avg(n) média do valor n, ignorando nulos
count(expr) vezes que o número da expr avalia para algo nao nulo
max(expr) maior valor da expr
min(expr) menor valor da expr
sum(n) soma dos valores de n, ignorando nulos
As Sub-Consultas
Uma sub-consulta é um comando "select" que é aninhado dentro de outro "select" e que devolve resultados intermediários.
A cláusula "HAVING" pode ser utilizada para especificar quais grupos deverão ser exibidos, portanto restringindo-os.
GROUP BY é opcional. Valores de resumo são omitidos se não houver qualquer função aggregate SQL na instrução SELECT. Os valores Null nos campos GROUP BY são agrupados e não omitidos. No entanto, os valores Null não são avaliados em qualquer função aggregate SQL. Use a cláusula WHERE para excluir linhas que você não quer agrupadas e use a cláusula HAVING para filtrar os registros após eles terem sido agrupados.
ORDER BY é opcional. Entretanto, se você quiser exibir seus dados na ordem classificada, você deve utilizar ORDER BY. O padrão ordem de classificação é ascendente (A a Z, 0 a 9). Os dois exemplos abaixo classificam os nomes dos funcionários pelo sobrenome.
4_O que é uma visão (view) em SQL? Explique suas propriedades, utilidades e os comandos para a sua manipulação.
Uma visão (view) é uma forma alternativa de olhar os dados contidos em uma ou mais tabelas. Para definir uma visão, usa-se um comando SELECT que faz uma consulta sobre as tabelas. A visão aparece depois como se fosse uma tabela.
Visões têm as seguintes vantagens:
* Uma visão pode restringir quais as colunas da tabela que podem ser acessadas (para leitura ou para modificação)
* Uma consulta SELECT que é usada muito freqüentemente pode ser criada como visão. Com isso, a cada vez que ela é necessária, basta selecionar dados da visão.
* Visões podem conter valores calculados ou valores de resumo, o que simplifica a operação.
Uma visão pode ser usada para exportar dados para outras aplicações.
Criando uma visão com comandos SQL
Para criar uma visão através de SQL, use o comando CREATE VIEW. Esse comando tem a seguinte sintaxe:
CREATE VIEW nome_visão [(coluna [,...n])]
[WITH ENCRYPTION]
AS
declaração_SELECT
[WITH CHECK OPTION]
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 ou Stored Procedure é uma coleção de comandos em SQL para dispensamento de Banco de dados. Encapsula tarefas repetitivas, aceita parâmetros de entrada e retorna um valor de status (para indicar aceitação ou falha na execução). O procedimento armazenado pode reduzir o tráfego na rede, melhorar o desempenho, criar mecanismos de segurança, etc.
Stored procedures são criadas através do comando CREATE PROCEDURE que tem a seguinte sintaxe:
CREATE PROCEDURE NomedaProcedure
RETURNS
AS
BEGIN
END
Os parâmetros de entrada permitem à aplicação cliente passar os valores que serão usados para modificar o comportamento da stored procedure. Por exemplo, se o objetivo da stored procedure é calcular o total mensal da folha de pagamento para a um determinado departamento, o número do departamento deverá ser passado para a stored procedure como um parâmetro de entrada. Parâmetros de saída ou de retorno são é o meio pelo qual a
stored procedure retorna informações para a aplicação cliente. Em nosso exemplo, o total da folha de pagamento mensal para o departamento passado deverá ser retornado em um parâmetro de saída. Um parâmetro pode ser de qualquer tipo de dados do Firebird exceto BLOB ou ARRAY.
6_O que são gatilhos (TRIGGERS)? Para que servem, quais as vantagens na sua utilização, como funcionam e como criá-los?
O gatilho define um conjunto de ações a serem executadas quando ocorre um evento de banco de dados em uma determinada tabela. O evento de banco de dados é uma operação de exclusão, inserção ou de atualização. Por exemplo, se for definido um gatilho para exclusão em uma determinada tabela, a ação do gatilho ocorre sempre que se remove uma ou mais linhas da tabela.
Junto com as restrições, os gatilhos podem ajudar a impor regras de integridade com ações como exclusões ou atualizações em cascata. Os gatilhos também podem realizar várias funções como emitir alertas, atualizar outras tabelas, enviar e-mail, e outras ações úteis.
Pode ser definido qualquer número de gatilhos para uma única tabela, inclusive vários gatilhos para a mesma tabela para o mesmo evento.
Pode ser criado gatilho em qualquer esquema, exceto os começados por SYS. O gatilho não precisa residir no mesmo esquema da tabela para a qual é definido.
Se for especificado um nome de gatilho qualificado, o nome do esquema não poderá começar por SYS.
Vantagens
• Redução na manutenção das aplicações cliente.
• Você pode implementar um relatório de modificações para as tabelas.
• Generator's podem ser chamados automaticamente e seus valores atribuídos a campos.
• Notificação automática das aplicações pelo uso de Eventos.
Sintaxe básica
CREATE [OR ALTER] TRIGGER nome FOR nomedatabela
[ACTIVE | INACTIVE]
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
[OR {INSERT | UPDATE | DELETE}
[OR {INSERT | UPDATE | DELETE}]]
[POSITION numero]
AS
Comandos e Conceitos do SQL - Exercícios
Para resolver os exercícios abaixo, será necessário conhecer o programa ERWIN, cujos manuais estão na área de trabalho, conhecer um Sistema (ou) Gerenciador de Banco de Dados (SQL Server, Oracle, Access, Firebird, Interbase e etc). Caso não conheça realize somente a etapa 4.
Traduza ou Realize as consulta dos exercícios feitos em Álgebra Relacional para os comandos SQL, treinando assim a sua utilização.
--> SELEÇÃO
1-Mostre o conteúdo da relação departamento.
R: SELECT * FROM DEPARTAMENTO
2- Mostre os empregados que trabalham no departamento 4.
R: SELECT * FROM EMPREGADO WHERE NDEP = 4
3-Mostre a(s) localização(ões) do departamento de pesquisa (5).
R: SELECT * FROM LOCAIS-DEPTO WHERE DNÚMERO = 5
4-Mostre o(s) projetos(s) do departamento 4.
R: SELECT * FROM PROJETO WHERE DNUM = 4
5- Mostre as informações de relação trabalha_em cujo número do projeto igual a 3.
R: SELECT * FROM TRABALHA_EM WHERE PNRO = 3
--> PROJEÇÃO
1-Mostre o número e o nome dos dependentes
R: SELECT NSSENP, NOMEDEPENDENTE FROM DEPENDENTE
2- Mostre o nome e a localização dos projetos.
R: SELECT PNOME, PLOCALIZAÇÃO FROM PROJETO
3-Mostre o nome e o código do gerente dos departamentos.
R: SELECT DNOME, SNNGER FROM DEPARTAMENTO
4-Mostre o nome e a data de nascimento dos empregados.
R: SELECT PNOME, DATANASC FROM EMPREGADO
5-Mostre o nome e o salário dos empregados.
R: SELECT PNOME, SALARIO FROM EMPREGADO
--> SELEÇÃO + PROJEÇÃO
1-Mostre o código, nome e o salário dos empregados que ganham mais de 2500.
R: SELECT NSS, PNOME, SALARIO FROM EMPREGADO WHERE SALARIO > 2500
2- Mostre o nome e a localização dos projetos do departamento 5.
R: SELECT PNOME, PLOCALIZAÇÃO FROM PROJETO WHERE DNUM = 5
3- Mostre o número do empregado e o número do projeto cujas horas são maiores que 10.
R: SELECT NSSEMP, PNRO FROM TRABALHA_EM WHERE HORAS > 10.0
4-Mostre o nome e o sexo do dependente cujo número do empregado é igual a 123456789.
R: SELECT NOMEDEPENDENTE, SEXO FROM DEPENDENTE WHERE NSSEMP = 123456789
5-Mostre o nome, relação e a data do aniversário dos dependentes cujo número do empregado é igual a 333445555.
R: SELECT NOMEDEPENDENTE, RELAÇÃO, DATANIV FROM DEPENDENTE WHERE NSSEMP = 333445555
--> Junção
1-Mostre o nome e o salário do empregado e o nome e a relação dos seus dependentes.
R: SELECT PNOME, SALARIO FROM EMPREGADO INNER JOIN NOMEDEPENDENTE, RELAÇÃO FROM DEPENDENTE
2-Mostre o nome do departamento e o nome e o salário do empregado que é o seu gerente.
R: SELECT DNOME FROM DEPARTAMENTO INNER JOIN PNOME, SALARIO FROM EMPREGADO WHERE NSS = NSSGER
3-Mostre o nome do departamento e a sua localização.
R: SELECT DNOME FROM DEPARTAMENTO INNER JOIN DLOCALIZAÇÃO FROM LOCAIS_DEPTO
4-Mostre o nome do projeto, sua localização e o departamento ao qual pertence.
R: SELECT PNOME, PLOCALIZAÇÃO FROM PROJETO INNER JOIN DNOME FROM DEPARTAMENTO
5-Mostre o nome do empregado e o nome do projeto nos quais eles trabalharam.
R: SELECT PNOME FROM EMPREGADO INNER JOIN PNOME FROM PROJETO INNER JOIN TRABALHA_EM WHERE NSS = NSSEMP
--> Agregação
1-Mostre a quantidade de empregados do sexo masculino e a quantidade de empregados do sexo feminino.
R: SELECT COUNT SEXO FROM EMPREGADO GROUP BY SEXO
2-Mostre a quantidade de dependentes do sexo masculino e a quantidade de dependentes do sexo feminino do empregado cujo número é 123456789.
R: SELECT COUNT SEXO FROM DEPENDENTE WHERE NSSEMP = 123465789 GROUP BY SEXO
3-Mostre o nome e o salário do empregado e a quantidade de dependentes que possui.
R: SELECT PNOME, SALARIO FROM EMPREGADO INNER JOIN COUNT NOMEDEPENDENTE FROM DEPENDENTE
4-Mostre a despesa total de cada departamento com os salários dos empregados.
R: SELECT SUM SALARIO FROM EMPREGADO INNER JOIN DNUMERO FROM DEPARTAMENT WHERE NDEP =DNUMERO
5-Mostre a soma total das horas empregadas em cada projeto.
R: SELECT SUM HORAS FROM TRABALHA_EM GROUP BY PNRO
