Tecnologias e Linguagens para Banco de Dados

(1)

Tecnologias e Linguagens

para Banco de Dados

Prof. Bruno Guedes

E-mail: brunofguedes@gmail.com

Modelo Relacional

Nomenclatura Correspondência Relação Tabela

Tupla Linha, registro Atributo Coluna, campo

Código Nome Cidade

10 Pessoal RJ

20 Jurídico RJ

30 Financeiro SP

40 Técnico BH

DEPARTAMENTO PK

Atributos

Tuplas

(2)

Propriedades •Não existem linhas duplicadas

• Não existe ordenamento de linhas

• Não existe ordenamento de coluna

Regras de integridade do modelo

1 - Integridade da entidade:

Nenhum campo que faça parte da chave primária (PK) pode conter valor nulo.

Modelo Relacional

2 - Integridade Referencial

Se uma relação R2 possuir uma chave estrangeira (FK), correspondente a uma PK em R1, então para toda linha de R2 o valor de FK pode:

a) Ser igual a algum valor de PK em R1

(3)

Modelo Relacional

Exemplo:

Código Nome Cidade

10 Pessoal RJ 20 Jurídico RJ 30 Financeiro SP 40 Técnico BH

DEPARTAMENTO (R1)

FUNCIONÁRIO (R2)

Matrícula Nome Depto

200 Ivo 40

750 Maria 40

930 Pedro

880 Paula 20

300 Ana 10

270 Luiz 30

PK

PK FK

Integridade Referencial

Normalização

É um processo que visa substituir um conjunto de relações com anomalias de modificação, por outro sem anomalias(problemas).

1ª Forma normal (1FN)

(4)

Exemplo:

R

Matrícula Atividade Taxa

200 Natação 80,00 100 Aeróbica 80,00 300 Volei 100,00 100 Musculação 60,00 200 Aeróbica 80,00

• Não é possível incluir uma nova atividade a não

ser que exista aluno matriculado para ela.

• A exclusão de um registro pode acarretar em

perda de informação relevante, por exemplo, se o aluno 300 for excluído, se perderá a informação que vôlei custa 100,00 reais.

PK = (matrícula, atividade)

* Será que R está em 1FN? R: Sim

* Será que R possui anomalias de Modificação?

R: Sim

Normalização

Exemplo:

Dependência Funcional

Seja R (A, B, C, D ) uma relação. Existe uma dependência funcional entre atributos, se conhecendo o valor de um atributo é possível determinar um único valor para outro atributo.

(5)

Normalização

Exemplo Continuação:

Matrícula Nome Cargo

300 Ana Analista 100 Pedro Program 400 Ana Program 200 Luiz Analista

Funcionário = (matrícula, nome, cargo)

Matrícula _Nome Matrícula _Cargo Nome Matrícula Cargo Matrícula Nome Cargo Cargo Nome Dependências Funcionais: X X X X Ok Ok

Normalização

2ª Formal Normal (2FN)

Uma relação está em 2FN se está em 1FN e não existir dependência funcional parcial entre a chave e os atributos que não fazem parte da chave.

Seja R (A,B,C,D,E) onde a PK = (A,B)

Dependências Funcionais Parciais: A→C; A → E; B → D; A → D, B → C; B → E.

(6)

Solução

R não está em 2 FN, porque existe uma

dependência funcional parcial da PK em relação

ao resto da tabela. Atividade → Taxa

Normalizando: R (matrícula, atividade); R1 (atividade, taxa)

Obs: Toda tabela que possui uma chave primária simples, estará sempre em 2FN.

Normalização

Passos para obter a 2ª formal normal

1 - Analisar a relação e verificar se existe dependência funcional parcial entre a PK e algum outro atributo. 2 - Se existir alguma dependência funcional em (1) então

•As dependências funcionais existentes formarão novas tabelas tendo com PK os determinantes.

•As colunas determinadas são excluídas da relação original.

Senão

(7)

Normalização

R – PK (Médico)

Médico Especialidade Valor

300 Clínico 80,00 200 Pediatra 60,00 100 Otorrino 80,00 400 Clínico 80,00 3ª forma normal (3FN)

Uma relação está em 3FN se está em 2 FN e não existir dependência funcional entre atributos que não participam da PK.

Em que forma normal está R?

Normalização

2 FN. Não está em 3 FN, porque especialidade_→valor

Normalizando: R (médico, especialidade); R1 (especialidade, valor)

(8)

Passos para obter a 3ª forma normal

1º Analisar a relação e verificar se existe dependência funcional entre atributos que não participam da PK. 2º Se existir alguma dependência funcional em (1) então

•As dependências funcionais existentes formarão novas tabelas tendo como PK os determinantes.

•As colunas determinadas são excluídas da relação original.

Senão

•A relação já está em 3 FN.

Normalização

Exercícios

1) A relação abaixo tem como PK= Matrícula

Projeto Nome Matrícula Cargo Salário

(9)

Normalização

Exercícios - Continuação

a) Dizer em que forma normal a relação se encontra, justificando a resposta.

b) Normalizar até 3FN, caso seja necessário.

2 - Considere R (A,B,C,D) uma relação com as

seguintes dependências funcionais: (A,B) _→D; C _→D. Em que forma normal se encontra R? Justifique a

resposta

Normalização

Exercícios - Continuação

3 - Considere R (A,B,C,D,E,F) uma relação com as seguintes dependências funcionais:

A → C; B→ D; (A,B) → E; E → F

a) Em que forma normal esta R? Justifique. b) Normalizar até 3FN, caso seja necessário.

4 - Estudante Disciplina Professor

(10)

Exercícios - Finalizando Sabendo-se que:

1 - PK=( estudante, disciplina)

2 - Cada professor ensina somente uma disciplina 3 - Uma disciplina pode ser ensinada por vários professores.

Pede-se:

a) Dizer em que forma normal está a tabela. Justifique. b) Normalizar até 3FN, caso seja necessário.

SQL- Structured Query Language

• Desenvolvido na IBM na década de 70(sequel)

•Anos 80 (SQL)

•Ansi (American Standard Institute)

Sublinguagens:

DDL- Data Description Language

(11)

SQL- Structured Query Language

Principais comandos DDL

• Create - Serve para criar tabelas, visões e índices.

• Alter - Serve para alterar tabelas.

• Drop - Serve para eliminar tabelas, visões e índices.

Sintaxe –Comando Create 1) Create table nome-tabela

(coluna 1, coluna 2,..., Definição-coluna N)

Onde

Definição-coluna →nome-coluna Tipo dados [Not Null] Ex: Create table Fornecedor

(12)

Sintaxe – Comando Alter e Drop

2) Alter Table Nome-Tabela

a) [ADD definição-coluna]

b) [ADD constraint nome-restrição cláusulas] c) [DROP constraint nome-restrição]

Exemplos:

a) Alter table fornecedor ADD telefone char (12);

b) Alter Table Fornecedor

ADD Constraint Forn_PK → Definindo a PK

(13)

Exemplos:

c) Alter Table Fornecimento ADD Constraint Forn-FK

Foreign Key (cod-forn) references Fornecedor (código);

3) Drop Table nome-tabela Ex: Drop Table Fornecedor;

Definindo a FK

SQL- Structured Query Language Principais comandos DML

Comandos de atualização

Insert → Insere linhas

Update→Atualiza colunas Delete →Exclui linhas

Comando Insert

a)Insert into nome-da-tabela [(coluna)],…,coluna n)

values (valor1,...,valor n)];

(14)

1) Insert into peça (código, nome,cidade) values („P7‟, „parafuso‟, „RJ‟);

2) Insert into peça values („P8‟, „parafuso‟,‟preto‟,10, „SP‟);

3) Insert into peça – SP select * from peça where

cidade = „SP‟ ;

SQL- Structured Query Language Comando delete:

Delete from nome-da-tabela [where condição]

1) delete from fornecedor where código = „54‟; → Exclui uma linha.

2) delete from fornecedor; → Exclui todas as linhas

3) Excluir os fornecimentos realizados por fornecedores da cidade de SP.

(15)

Comando Update

Update nome-da-tabela set campo 1 = valor … campo

N=valor [where condição];

1)Alterar a cidade para Null e a cor para preto na peça P2. Update peça set cidade = NULL cor = ‟Preto‟ where código = „P2‟

2) Atualizar para 0(zero) a quantidade nos fornecimentos feitos por fornecedores do Rio.

Update fornecimento set Qtde=0 (zero) where cod-forn in (select código from fornecedor where cidade= „RJ‟);

→ Comando Select Sintaxe

Select [Distinct] coluna 1,..., coluna n From Tabela 1, ..., Tabela n

[Where condição]

[Group By Coluna1,.... coluna n] [Having condição]

(16)

1) Recuperação simples

Obter o código das peças que tiveram fornecimento Select cod-peça From Fornecimento;

Resultado:

2) Recuperação simples sem duplicação

Obter o código das peças que tiveram fornecimento Select distinct Cod-peça From Fornecimento

Resultado:

SQL- Structured Query Language 3) Recuperação simples de toda a tabela

Select * From Fornecimento; Resultado:

4) Recuperação Qualificada

Obter o código dos fornecedores da cidade de SP Select código from fornecedor where cidade =SP; Resultado:

(17)

5) Obter o código dos fornecedores da cidade de SP que tenham status maior que 20.

Select Código From Fornecedor Where cidade = “SP” and status > 20;

Resultado:

6) Obter o nome, cor e peso das peças localizadas no Rio em ordem decrescente de peso.

Select nome, cor, peso from peça where cidade=”Rio” order by peso desc;

Resultado: Nome Cor Peso

Rebite Vermelho 19 Parafuso Vermelho 14 Prego Vermelho 12

SQL- Structured Query Language 7) Obter o nome dos fornecedores que forneceram peças.

Select nome from fornecedor, fornecimento where cod-forn=código;

Resultado:

8) Obter o nome dos fornecedores que fornecem a peça P2:

(18)

9) Obter o nome das peças fornecidas por fornecedores do rio:

Select distinct peça-nome from peça, fornecedor, fornecimento where peça-código = cod-peça and cod-forn = Fornecedor.Código and

fornecedor.Cidade = “Rio”; Resultado:

Funções Embutidas

Count– Retorna o número de linhas.

SUM– Retorna a soma dos valores de uma coluna. MAX – Retorna o maior valor de uma coluna.

MIN- Retorna o menor valor de uma coluna.

AVG- Retorna a média dos valores de uma coluna.

(19)

10) Obter o número de fornecedores existentes Select count (*) from fornecedor;

Resultado:

Select count (*) as total from fornecedor;

Resultado:

11) Obter o número de fornecimentos para a peça P2. Select count (*) from fornecimento where

cod-peça=”P2”; Resultado:

12) Obter a quantidade total de peças fornecidas: Select SUM (QTDE) from fornecimento;

Resultado:

Group By

A cláusula Group By é usada em conjunto com

(20)

13) Obter a quantidade total de cada peça fornecida: Select SUM (QTDE), cod-peça from fornecimento Group By cod-peça;

Resultado:

Having

Funciona para cláusula Group By, como o “where” para o “select”, ou seja, especifica uma condição que será aplicada ao resultado de um “Group By”.

14) Obter a quantidade total de cada peça fornecida, somente para as peças com qtde total maior que 500: Select SUM (QTDE), cod-peça from fornecimento Group by cod-peça Having SUM (QTDE) > 500;

(21)

Like

% (porcentagem) → Cadeia de caracteres _ (underline) →1 Caracter

OBS: no MySQL

15) Obter o código das peças com o nome começando por R.

Select código from peça where nome like „ R%‟ ;

Resultado:

16) Obter o código das peças com o nome começando por R e terminando por E.

Select código from peça where nome like „R%E‟

Resultado:

17) Obter o código das peças com o nome

começando por R, terminando por E, e com a letra B na 3ª posição.

Select código from peça where nome like „ R _ B % E‟

(22)

IN

Operador que busca o valor dentro de uma lista de valores.

18) Obter peso das peças que são vermelhas ou azuis.

Select peso from peça where cor IN („ vermelho‟, „azul‟);

Resultado:

19) Obter o nome dos fornecedores que fornecem a peça P2

Select nome from fornecedor, fornecimento where cod-peça = „P2‟ and código = cod-forn ;

Resultado:

Resolvendo por sub-consulta:

(23)

Sub-consulta

É uma consulta dentro de uma cláusula where. São usadas para representar uma lista de valores a serem pesquisados pelo operador IN. O sistema resolve primeiro a sub-consulta, e depois a consulta.

20) Obter o nome dos fornecedores que fornecem peças vermelhas:

Select nome from fornecedor where código in (select cod-forn from fornecimento where cod-peça in (select

código from peça where cor = „vermelho‟); Resultado:

21) Obter o nome dos fornecedores que tem status menor que o maior status existente:

Select nome from fornecedor where status < ( select Max (status) from fornecedor);

Resultado:

Valor Nulo

22) Obter o código dos fornecedores com status nulo. Select código from fornecedor where status is NULL;

(24)

Between

Verifica se o valor de uma coluna pertence a um intervalo.

23) Obter o nome das peças com peso entre 14 e 20. select nome from peça where peso between 14 and 20;