Universidade Federal de Alagoas – UFAL Campus Arapiraca
Banco de Dados I Professor Mário Hozano
Jayane Vieira Silva Fernandes José Rodrigo Oliveira Lima Merkianny Almeida de Melo Walysson Vital Barbosa Yana Kellen Dioclécio Mendes
Lista de Exercícios - Normalização
Arapiraca – Alagoas, Março de 2013.
Lista de Exercícios 1. O que é normalização? Respos Resposta: ta: Normal Normaliza ização ção é o proces processo so de associ associar ar atribu atributos tos à relaçõe relações s de forma a eliminar redundância de dados quando executada propriamente, assim eliminando as anomalias e problemas de integridade de dados causados por tais redundâncias e facilitando o desenvolvimento de um banco de dados bem formulado. 2. Trata-se de uma regra que, se observada no processo de normalização de dados, considera o banco de dados na terceira forma normal: a) Identificar cada conjunto de dados relacionados com uma chave primária. b) Eliminar os grupos de repetição transformando-os em tabelas individuais. c) Eliminar os campos que não dependem da chave. d) Criar uma tabela separada para cada conjunto de dados relacionados. e) Relacionar as tabelas separadas com uma chave externa. 3. Cons Consid ider ere e um banc banco o de dado dados s com com as rela relaçõ ções es R1, R1, R2 e R3 e as respectivas dependências funcionais abaixo, onde valor_pago é o valor pago de imposto de renda no ano e atributos sublinhados formam a chave primária. R1 = {cpf, nome, cpf_conjuge, nome_conjuge} R2 = {cpf, nome, cidade, numero_dependentes} R3 = {cpf, ano, nome, valor_pago} Dependências funcionais: R1 cpf → nome, cpf_conjuge cpf_conjuge → nome_conjuge R2 cpf → nome, cidade, numero_dependentes numero_dependentes R3 cpf → nome cpf, ano → valor_pago R1, R2 e R3 estão, respectivamente, na: a) primeira, terceira e segundaformas normais. b) primeira, segunda e terceira formas normais. c) segunda, terceira e primeira formas normais. d) segunda, primeira e terceira formas normais. e) terceira, primeira e segunda formas normais.
4. Analisando Analisando a tabela a seguir é possível aplicar quais Formas Normais.
Venda(Codvenda, Codproduto, Codcliente, Codcidade, Quantidade, Valortotal) Dependências funcionais: Codvenda → Codcliente Codcliente → Codcidade Codvenda, Codproduto → Quantidade, Valortotal
a) A 1FN apenas. b) A 2FN apenas. c) A 3FN apenas. d) A 2FN e a 3FN. e) Todas Todas as FN. 5. Considere a tabela a seguir:
- Verifique se a tabela está na 1º Forma Normal, caso não esteja normalize. Reso Resolu luçã ção: o: Todos odos os clie client ntes es poss possue uem m Rua, Rua, CEP CEP e Bair Bairro ro,, e essa essas s informações estão na mesma célula da tabela (atributo composto), logo ela não primeira forma forma normal normal. Para está está na primeira Para normal normaliza izarr, devere deveremos mos coloc colocar ar os atributos que compõem o atributo composto em uma colunas diferentes.
Mesm Mesmo o com com o ajus ajuste te acima acima,, a tabe tabela la aind ainda a não não está está na primeira forma normal, pois há clientes com mais de um telefone e os valores estão em uma mesma célula (atributo multivalorado). Para normalizar será necessário criar uma nova tabela para armazenar os números dos telefones e o campo-chave da tabela cliente.
6. De acordo com a normalização, se necessário, transforme para 1FN a tabela baixo, onde idCliente é chave primária: Cliente idCliente
nomeCliente
endereco
1 Joã João da Silva Rua Ju Justin stiniiano, 33 3300 Emanuel el Bezer Bezerra ra Trave Travessa ssa Quin Quinze, ze, 200 200 2 Emanu 3 João João dos San Santos tos Av. Bra Brasil, sil, 220 220 Av. Ban B ande deira irantes, ntes, 2980 2980 4 Maria Mariana
telefones 2342-2342 / 2242-2210 2345-3344 2422-9878 / 2445-3322 / 2444-9755 2355-9875
Devemos Devemos observar observar se temos atributos atributos compostos compostos ou multivalora multivalorados. dos. Neste caso caso,, perc perceb ebem emos os a pres presen ença ça de um atrib atribut uto o mult multiv ival alor orad ado o na colu coluna na Telefon elefones, es, logo logo obedec obedecemo emos s a regra: regra: criare criaremos mos uma tabela tabela a parte parte para para organizá-lo da melhor maneira. Cliente i dCli ente
nomeCli ente
1 Joã João da Silva 2 Eman Emanue uell Bezer Bezerra 3 Joã João do dos San Santos tos 4 Maria Marian iana
TelefoneCliente idC idCli lieente tele leffone 1 1 2 3 3 3 4
2342-2342 2242-2210 2345-3344 2422-9878 2445-3322 2444-9755 2355-9875
endereco
Rua Just Justiiniano, 3300 Trave Travessa ssa Qui Quinze nze,, 200 200 Av. Av. Bra Brasil, sil, 22 220 Av. Ba Bandeirantes, tes, 2980
7. Normalize a tabela abaixo para a 3FN, onde idPedido e CodProduto formam a chave da tabela: Pedido idPe idPedi dido do 1 2 3 4
dat dataPedd Peddo o codPr odProd odut uto o nome nomePr Prod odut uto o 01/07/09 1234 HD 250GB 01/07/09 1235 HD 180GB 03/07/09 1235 HD 180GB 05/07/09 1234 HD 250GB
qtde qtde 2 1 4 6
valor alorU Unit nitario ario valorT lorTot ota al R$ 100 R$ 200 R$ 80 R$ 80 R$ 80 R$ 320 R$ 100 R$ 600
Resolução: Dependências funcionais: idPedido → dataPedido idPedido, codProduto → qtde, valorTotal codProduto → nomeProduto, valorUnitário
Passo 1: 1ª Forma Normal Já se encontra na 1FN por não possuir atributos multivalorados ou compostos.
Passo 2: 2ª Forma Normal O enunciado da questão nos confirma que possui uma chave composta e pelas depe de pend ndên ênci cias as fu func ncio iona nais is ob obse serv rvam amos os qu que e al algu guns ns at atrib ribut utos os de depe pend ndem em parcia par cialme lmente nte da cha chave ve pri primár mária. ia. Assim, Assim, pod podem emos os apl aplica icarr a 2FN 2FN,, dev devemo emos s deixar cada atributo atributo juntamente com sua chave em uma tabela, logo: logo: Pedido idPedido ido 1 2 3 4
codProduto 1234 1235 1235 1234
qtde 2 1 4 6
DataPedido idPed idPedido ido 1 2 3 4
data dataPe Pedd ddo o 01/07/09 01/07/09 03/07/09 05/07/09
Produto codProduto codProduto nomeProduto valorUnitario valorUnitario 1234 HD 250GB R$ 100 1235 HD 180GB R$ 80 1235 HD 180GB R$ 80 1234 HD 250GB R$ 100
Passo 3: 3ª Forma Normal
valorTotal R$ 200 R$ 80 R$ 320 R$ 600
Já se encon encontra tra na 3FN 3FN por não possui possuirr transi transitiv tivida idade de nas depen dependên dência cias s funcionais. 8. (POSCOMP-2010) O processo de normalização baseia-se no conceito de forma normal, que é uma regra que deve ser obedecida por uma relação para que seja seja consid considera erada da bem bem projet projetada ada.Co .Com m base base nos nos conhe conhecim ciment entos os sobre sobre normalização, considere as afirmativas a seguir. I.
A Prime Primeira ira Forma Forma Norma Normall (1FN) (1FN) define define que a relaçã relação o não deve deve conter conter atributos não atômicos ou as relações aninhadas. A ação que deve ser tomada para deixar uma relação na 1FN é formar uma nova relação para cada atributo não atômico ou para cada relação aninhada. II. A Segunda Forma Normal Normal (2FN) define que, que, além de estar na 1FN, para as relações relações que possuam possuam chaves chaves primárias primárias com vários vários atributos, atributos, nenhum atributo externo à chave deve ser funcionalmente dependente de parte da chave primária. A ação que deve ser tomada é decompor e montar uma nova relaç lação para cada chave parci rcial com seu(s) atrib ributo(s) dependente(s). III. III. A Terc Terceir eira a Forma Forma Normal Normal (3FN) (3FN) define define que, que, além além de estar estar na 2FN, as rela relaçõ ções es não não deve devem m ter ter atri atribu buto tos s que que não não pert perten ença çam m a uma uma chav chave, e, funcionalmente determinados por outro atributo que também não pertença a uma chave (ou por um conjunto de atributos não chave). A ação que deve ser tomada é decompor e montar uma relação que contenha o(s) atribu atributo( to(s) s) não não chave chave que determ determina ina(m) (m) funcio funcional nalmen mente te o(s) o(s) outro(s outro(s)) atributo(s). IV. IV. Uma dependência dependência parcial ocorre ocorre quando um atributo, além de depender depender da chave primária, depende de outro atributo ou conjunto de atributos da relação. Uma dependência transitiva ocorre quando um atributo depende apenas de parte de uma chave primária composta. Assinale a alternativa correta. a) Somente as afirmativas I e IV são corretas. b) Somente as afirmativas II e III são corretas. c) Somente as afirmativas III e IV são corretas. d) Somente as afirmativas I, II e III são corretas. e) Somente as afirmativas I, II e IV são corretas. 9. Normalize a ficha médica abaixo seguindo os conceitos de normalização de dados.
Resolução: - Passo 1 - Criando a tupla apartir da ficha.
PACIENTE
(nuP (nuPac acie ient nte, e, noPa noPaci cien ente te,, dtNa dtNasc scim imen ento to,, sexo sexo,, conv conven enio io,, estadoCivi estadoCivil, l, rg, telefone, telefone, endereco endereco,, (nuConsul (nuConsulta, ta, data, data, medico, medico, diagnostic diagnostico, o, (exame, data))) - Passo 2 – Eliminando tabelas aninhadas, assim fazendo a 1FN.
PACIENTE
(nuP (nuPac acie ient nte, e, noPa noPaci cien ente te,, dtNa dtNasc scime iment nto, o, sexo sexo,, estadoCivil, rg, telefone, endereco) CONSULTA (nuPaciente, nuConsulta, data, medico, diagnostico) EXAME (nuPaciente, nuConsulta, nuExame, noExame, data)
conv conven enio io,,
- Passo Passo 3 – Verific erificand ando o se existe existe depen dependên dência cia parcia parciall na relaçã relação, o, assim assim aplicando a 2Fn.
PACIENTE
(nuP (nuPac acie ient nte, e, noPa noPaci cien ente te,, dtNa dtNasc scime iment nto, o, sexo sexo,, estadoCivil, rg, telefone, endereco) CONSULTA (nuPaciente, nuConsulta, crm, data, diagnostico) CONSULTA_EXAME (nuPaciente, nuConsulta, nuExame, data) MEDICO (crm, nome) EXAME (nuExame, noExame)
conv conven enio io,,
- Passo 4 – Eliminando as dependências transitivas, aplicando a 3FN.
PACIENTE (nuP (nuPac acie ient nte, e, noPa noPaci cien ente te,, dtNa dtNasc scim imen ento to,, sexo sexo,, rg, rg, tele telefo fone ne,, endereco, nuConvenio, nuEstadoCivil) CONSULTA (nuPaciente, nuConsulta, crm, data, diagnostico) CONSULTA_EXAME (nuPaciente, nuConsulta, nuExame, data) MEDICO (crm, nome) EXAME (nuExame, noExame) CONVENIO (nuConvenio, nome) ESTADO_CIVIL (nuEstadoCivil, nome)
10. Examine a relação abaixo: Filial
num numFili Filial al ender nderec eco oFili Filial al tele telefo fone ness
num numGere erente nte nome omeGe Gerrente ente
B001
Rua Jefferson
1
Tomas
B002
City Center Plaza 8th Avenue 14th Avenue
2
Ana
3 4
Maria Carlos
B003 B004
503-555-3618, 503555-2727, 503-5556534 206-555-6756, 206555-8836 212-371-3000 206-555-3131, 206555-4112
Dependências funcionais: numFilial -> enderecoFilial, telefones, numGerente, nomeGerente numGerente -> nomeGerente a) Porque a relação não está na 3FN? Respos Resposta: ta: Pois Pois não está está na 1FN que não permit permite e atribu atributos tos multiv multivalo alorad rados os (tele (telefo fone nes) s).. Como Como não não está está na 1FN, 1FN, tamb também ém não não se enco encont ntra ra na 2FN, 2FN, consequentemente não está na 3FN, que também não admite transitividade entre dependências.
b) Demonstre o processo de normalização dos dados mostrados na relação para a 3FN. Resolução: A tabela tabela não se encontra na 1FN, 1FN, pois possui o atributo telefones telefones que é multivalorado que não pode estar presente na 1FN. Assim seguimos os seguintes passos:
Passo 1: 1º Forma Normal Extrair atributo multivalorado telefones da tabela Filial, criando uma nova tabela TelefoneFilial com chave primária composta pelos atributos numFilial da tabela Filial e telefone, sendo assim possível armazenar todos os telefones de cada filial: Filial
numF numFil ilial ial ende endere reco coFi Fili lial al
numG numGer eren ente te nome nomeGe Gere rent ntee
B001
Rua Jefferson
1
B002
City Center Plaza 2
Tomas Ana
B003 B004
8th Avenue 14th Avenue
3 4
Maria Carlos
TelefoneFilial
numFil numFilial ial telefo telefone ne B001 B001 B001 B002 B002 B003 B004 B004
503-555-3618 503-555-2727 503-555-6534 206-555-6756 206-555-8836 212-371-3000 206-555-3131 206-555-4112
Passo 2: 2º Forma Normal A tabela tabela já se encontra na 2FN.
Passo 3: 3º Forma Normal Existe transitividade de dependência na tabela Filial (numFilial → numGerente → NomeGerente), então criaremos a tabela Gerente, extraindo o atributo nomeGerente da tabela Filial e o colocando na nova tabela criada juntamente com o atributo numGerente que passa a ser a chave primária da tabela Gerente. Filial
numF numFil ilial ial end ender erec ecoF oFil ilia iall
numG numGer eren ente te
B001
Rua Jefferson
1
B002 B003 B004
City Center Plaza 2 8th Avenue 3 14th Avenue 4
TelefoneFilial
numFil numFilial ial telefo telefone ne B001 B001
503-555-3618 503-555-2727
B001 B002 B002
503-555-6534 206-555-6756 206-555-8836
B003 B004 B004
212-371-3000 206-555-3131 206-555-4112
Gerente
numGerent numGerente e nomeGere nomeGerente nte 1
Tomas Santos
2 3 4
Ana Maria Maria José Carlos André
c) Identifique as chaves primárias e estrangeiras nas r elações na 3FN. chave primária: Filial: numFilial TelefoneFilial: numFilial, telefone Gerente: numGerente
chave estrangeira:
Fiilial: numGerente TelefoneFIlial: numFilial
REFERÊNCIAS Normalização de dados e as formas normais. [online] Disponível em: > Normalização em Banco de Dados. [online] Disponível em: dados> PIVETTA, PIVETTA, Elisa Maria. Modelagem de Dados – Normalização . [online] Disponível em: Database Design: Normalization note & exercises (Up to 3NF). [online] Disponível em:
