SQL Magazine 101 - Modelagem de Dados

Sumário 06 – Normalização de dados na prática

Modelagem de Dados m e g a l e d o M / o t e j o r P

[ Roberto de Angelantonio Jr. ]

Minicurso, Projeto de Banco de Dados

14 – Introdução a projeto de banco de dados – Parte 1 [ Ricardo Rezende ]

21 – Os dez erros comuns em projetos de banco de dados – Parte 1

Boas Práticas

[ Ricardo Rezende ]

Banco de Dados

30 – Validando documentos XML através do XML Schema Definition [ Dhiego Piroto e Rodrigo Oliveira Spínola ]

40 – Obtendo melhor desempenho no servidor SQL Server

a i c n ê t s i s r e P / s o d a D e d o c n a B

Banco de Dados

49 – Novidades para desenvolvedores no Oracle 11g – Parte 2

Minicurso

[ Thyago Bernuci ]

Banco de Dados

Boa Ideia

[ Felipe de Assis ]

Olá, eu sou o DevMan! Desta página em diante, eu estarei lhe ajudando a compreender com ainda mais facilidade o conteúdo desta edição. Será um prazer contar com sua companhia! Confira abaixo o que teremos nesta revista:

54 – Questões de banco de dados do concurso da PRODAM (2012) [ Mauro Pichiliani ]

63 – Trabalhando com listas dinâmicas [ Marcelo Josué Telles ]

Brinde na web desta edição 1) Criando uma Stored Procedure Helo World em C# e Registrando no SQL Server - Curso Desenvolvimento para SQL Server com .NET - Parte 3 Nesta vídeo aula será mostrado como criar uma stored procedure simples (ProcHelloWorld), no estilo Olá Mundo, a fim de exemplificar como usar o C# para criar objetos SQL no banco de dados usando linguagem gerenciada. São examinados os namespaces necessários, o template gerado e um método simples. A seguir mostra como registrar a stored procedure no SQL Server para ser rodada em seu contexto (hospedada como se fosse uma procedure normal), sendo então consumida por aplicações clientes, incluindo o SQL Ser ver Management Studio, o próprio Visual Studio e uma aplicação simples em .NET.

1

Vídeo

Para visualizar acesse o link: http://www.devmedia.com.br/articles/listcomp.asp?keyword=sql101&codigobanca=

ochorios

Gostou das vídeo aulas? O portal www.devmedia.com.br possui mais de 2 mil vídeo aulas e dezenas de cursos online sobre desenvolvimento de software! Agora você pode comprar as vídeo aulas que preferir e fazer sua própria combinação de vídeos! Saiba mais em www.devmedia.com.br/creditos

u

e s ê

ac F eedb a k

Dê seu feedback sobre esta edição!

D

o

s

b

r

e

e

s

t a e

i d ç o ã

A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista! Dê seu voto sobre esta edição, artigo por artigo, através do link: www.devmedia.com.br/sqlmagazine/feedback - Para votar, você vai precisar do código de banca desta edição, que é: ochorios

Expediente

Editorial EDITORIAL

A Ano 9 - 101ª Edição 2012 - ISSN 1677918-5 - Impresso no Brasil

Corpo Editorial Atendimento ao Leitor

A DevMedia conta com um departamento exclusivo para o atendimento ao leitor. Se você tiver algum problema no recebimento do seu exemplar ou precisar de algum esclarecimento sobre assinaturas, exemplares anteriores, endereço de bancas de jornal, entre outros, entre em contato com:

Editor Geral

Rodrigo Oliveira Spínola [email protected] Sub Editores

Eduardo Oliveira Spínola e Ricardo Rezende Jornalista Responsável

www.devmedia.com.br/central (21)3382-5038

Kaline Dolabella - JP24185

Publicidade

Na Web

tualmente é muito comum encontrar sistemas com diversos problemas de performance decorrentes de uma modelagem de dados incorreta, com muita redundância e com o negócio mau planejado. Na maioria das vezes, os problemas na estrutura de dados são tão complexos de serem solucionados que a alternativa mais viável é construir um novo sistema ou comprar algo pronto no mercado, abandonando o atual. Mas quais são os fatores negativos que contribuem para um retrabalho tão grande? Em muitos casos o sistema começa a ser planejado pela codificação, ou seja, ao invés de se desenhar o negócio no modelo de dados, o foco é o desenvolvimento, e desta forma, as tabela, colunas, relacionamentos e etc., são criados para atender o código e não o inverso. Neste contexto, a SQL Magazine destaca como tema de capa desta edição Projeto de BD na Prática. São três matérias que apresentam sob diferentes perspectivas preocupações que devemos ter ao realizar esta atividade fundamental no desenvolvimento de projetos de software. Em complemento, a SQL Magazine traz nesta edição outras matérias muito interessantes envolvendo assuntos como SQL Server, Oracle e Prova de Concursos. Uma ótima leitura a todos.

Para informações sobre veiculação de anúncio na revista ou no site e para fechar parcerias ou ações específicas de marketing com a DevMedia, entre em contato com:

www.devmedia.com.br/sqlmagazine Distribuição

FC Comercial e Distribuidora S.A Rua Teodoro da Silva,907 Grajaú - RJ - 206563-900

[email protected]

Rodrigo Oliveira Spínola [email protected] Editor Chefe da SQL Magazine, WebMobile e Engenharia de Software Magazine.

Fale com o Editor!

É muito importante para a equipe saber o que você está achando da revista: que tipo de artigo você gostaria de ler,que artigo você mais gostou e qual artigo você menos gostou.Fique a vontade para entrar em contato com os editores e dar a sua sugestão! Se você estiver interessado em publicar um artigo na

revista ou no site SQL Magazine, entre em contato com os editores, informando o título e mini-resumo do tema que você gostaria de publicar: Rodrigo Oliveira Spínola - Editor da Revista [email protected]

Assine agora e tenha acesso a todo o conteúdo da DevMedia: www.devmedia.com.br/mvp

Doutor e Mestre em Engenharia de Software pela COPPE/UFRJ - o maior centro de ensino e pesquisa em engenharia da América Latina. Autor de diversos artigos científicos sobre Engenharia de Software publicados em revistas e conferências renomadas, dentro e fora do país.

the right-size Database

SQL E Isam Uma Combinação De Sucesso! Deixe o sistema de sua empresa mais leve e rápido. Conheça as novidades do c-tree e faça toda a diferença! ADICIONE processamento de transações, recuperação automática, backups dinâmicos e replicação aos seus aplicativos.



AMPLIE sua escolha de API’s com C, C++, .NET, ODBC, JDBC, PHP e SQL.



FAÇA como muitas empresas no mundo todo, líderes em diversos segmentos como finanças, telecomunicações e saúde, que têm baseado suas soluções críticas na tecnologia c-tree.



Baixe seu SDK grátis n t i o ra o r p o C m o C i r a F 1 1 0 2 ©

www.faircom.com 11-3872-9802

m e g a l e d o M / e r a w t f o S e d o t e j o r P

NESTA SEÇÃO VOCÊ ENCONTRA ARTIGOS SOBRE PROJETO, ANÁLISE OU MODELAGEM DE DADOS

Normalização de dados na prática A

tualmente é muito comum encontrar sistemas com diversos problemas de performance decorrentes de uma modelagem de dados incorreta, com muita redundância e com o negócio mau planejado. Na maioria das vezes, os problemas na estrutura de dados são tão complexos de serem solucionados que a alternativa mais viável é construir um novo sistema ou comprar algo pronto no mercado, abandonando o atual. Mas quais são os fatores negativos que contribuem pa ra um retrabalho tão grande? Em muitos casos o sistema começa a ser planejado pela codificação, ou seja, ao invés de se desenhar o negócio no modelo de dados, o foco é o desenvolvimento, e desta forma, as tabela, colunas, relacionamentos e etc., são criados para atender o código e não o inverso. Então, a melhor opção para aumentar a qualidade da aplicação é criar um modelo de dados que contemple todo o negócio, e para isso, é necessário utilizar as regras de normalização. A normalização se refere a um conjunto de regras de modelagem de dados (ler Nota DevMan 1), que tem por objetivo reduzir os problemas de lógica, auxiliar no desenvolvimento de software, deixando a leitura da estrutura de dados mais fácil, contribuindo assim para melhoria na performance dos sistemas e, principalmente, evitar a redundância.

Nota do DevMan 1 Modelo de Dados: Um banco de dados é projetado para armazenar os dados. A este

projeto damos o nome de modelo de dados, que é uma tarefa do arquiteto de dados. É através da modelagem de dados que serão definidas quais as tabelas com seus respectivos campos e os relacionamentos entre essas tabelas.

Existem várias regras na normalização, mas utilizando a 1FN, 2FN e 3FN (ler Nota DevMan 2) é possível atingir um grau muito elevado de maturidade no modelo de dados. Para construirmos nosso modelo de dados normalizado, vamos iniciar com o modelo lógico e depois, partimos para o modelo físico. O modelo lógico de dados já leva em 6 SQL Magazine Edição 101 •

Resumo DevMan De que se trata o artigo: O objetivo deste artigo é introduzir os conceitos de normalização da estrutura de dados e explicar de uma forma clara e objetiva a maneira de se aplicar isto no momento da elaboração dos modelos de dados. Com base nos exemplos aqui descritos será possível entender facilmente a melhor maneira de se usar cada conceito no dia-a-dia.

Em que situação o tema é útil: A normalização deve ser usada sempre que uma nova demanda apontar a necessidade de criação de um novo projeto, ou mesmo na manutenção das estruturas de dados já existentes em sua empresa.

Normalização de dados na prática: Este artigo foi elaborado para os profissionais que, de alguma maneira, têm a responsabilidade de manter e manipular as estruturas de dados da empresa. A ideia é passar de forma simples algumas dicas de como modelar dados e as principais regras de normalização. O artigo vai ajudar, principalmente, as pessoas que não possuem conhecimento nesta técnica e irá reforçar alguns conceitos para os profissionais que já possuem experiência no processo de modelagem de dados relacional. O uso correto das normas irá agregar qualidade no resultado final do sistema, melhorar o desempenho da base de dados e evitar redundâncias.

conta algumas limitações e implementa recursos como adequação de padrão e nomenclatura. Além disto, neste modelo definimos as chaves primárias e estrangeiras. Já no modelo físico de dados fazemos a modelagem física do modelo de banco de dados. Levamos em conta as limitações impostas pelo SGBD escolhido e deve ser criado sempre com base nas defin ições feitas no modelo lógico de dados.

Entendimento do negócio O primeiro passo para se obter sucesso na construção de um modelo de dados normalizado é conhecer o negócio e as regras que devem ser projetadas.

Uma linguagem muito utilizada para projetar o negócio é a UML (ler Nota DevMan 3), mas como este não é o nosso foco, vamos utilizar o modelo lógico como ponto de partida para chegarmos ao nosso objetivo, que é o modelo físico.

Nota do DevMan 2 1FN, 2FN e 3FN

1FN = Primeira Forma Normal 2FN = Segunda Forma Normal 3FN = Terceira Forma Normal Figura 1. Formulário de Pedido de Cotação

Nota do DevMan 3

Cliente Endereço

UML: A Unified Modeling Language (UML) é uma linguagem de modelagem não proprietária de

E-mail

terceira geração. A UML não é uma metodologia de desenvolvimento, o que significa que ela não diz para você o que fazer primeiro e em seguida ou como projetar seu sistema, mas ela lhe auxilia a visualizar seu projeto e a comunicação entre objetos.

Fone 1

Basicamente, a UML permite que desenvolvedores visualizem os produtos de seus trabalhos em diagramas padronizados. Junto com uma notação gráfica, a UML também especifica significados, isto é, semântica. É uma notação independente de processos, embora o RUP (Rational Unified Process) tenha sido especificamente desenvolvido utilizando a UML.

Código Item

Fone 2 Item Quantidade Unidade de Medida

Um sistema sempre surge de uma necessidade, e para que possamos exemplificar as regras da normalização, utilizaremos um exemplo simples, mas que contém pontos importantes para o entendimento das normas. Observe a Figura 1. Usaremos como exemplo um formulário comum, feito em planilha, mas que por alguma necessidade, será desenvolvido um sistema para seu controle. A Figura 1 representa um formulário de Cotação, que tem por objetivo registrar uma solicitação de preços por parte dos clientes da empresa. Para este exemplo não será necessário definir qual tipo de produto ou o segmento da empresa, pois este é um formulário genérico que pode ser encontrado em muitas empresas. O único ponto importante é que, a partir do modelo de dados seja possível gerar todas as informações contidas no formulário. Mas por onde devemos iniciar a modelagem de dados? Uma forma fácil de iniciar uma normalização deste tipo é agrupar todos os atributos em uma caixa conforme podemos observar na Tabela 1. Com base nas informações extraídas do Formulário de Cotação, seria interessante separar os atributos por assunto, para facilitar a aplicação da normalização. Então criaremos entidades sempre que identificarmos um assunto para agrupar todos os atributos encontrados na Tabela 1. O primeiro atributo é Cliente, mas a verdadeira funcionalidade dele é armazenar o nome do cliente, e este é um ponto muito relevante, identificar a f uncionalidade de cada um dos atributos, ou seja, o que devera ser armazenado na coluna.

Valor Unitário Valor Total do Item Vendedor Data Cotação Data de Envio da Cotação Valor Total da Cotação

Tabela 1. Informações extraidas do Formulário de Cotação

Este primeiro atributo deve ser incorporado em uma Entidade, que vamos definir como PESSOA. Neste artigo usaremos uma generalização de Entidade/Tabela, com isso podemos agrupar todos os tipos de pessoa dentro do mesmo objeto. Com esta primeira definição, devemos encontrar todos os atri butos que pertencem a PESSOA. É fácil identificar que os atributos Endereço, e-mail, Fone 1, Fone 2 e Vendedor também devem incorporar a entidade PESSOA. Não se preocupe ainda com as aplicações das formas normais, lembre-se que estamos separando os assuntos. Observe na Figura 2 como fica a nossa Entidade Pessoa ainda desnormalizada. Continuando a busca pelos assuntos, nos deparamos com o atributo Código do Item, e isto sugere que uma entidade ITEM seja inserida no nosso modelo lógico. Além disso, assim como fizemos com a entidade Pessoa, é preciso encontrar os outros atributos referentes a este assunto. Então teremos os atributos Item, Unidade de Medida e Valor Unitário, que vão formar a nossa entidade ITEM, conforme Figura 3. Edição 101 SQL Magazine 7 •

Normalização de dados na prática

Figura 2. Entidade desnormalizada PESSOA

Figura 3. Entidade desnormalizada ITEM

Agora nos restam poucos atributos sem uma entidade defin ida, e é necessário continuar com a atividade de agrupamento até o fim de todos eles. O próximo atributo é a Quantidade, e como essa quantidade é referente a uma Cotação, devemos criar uma entidade que represente a cotação dos itens do cliente, afinal este é o tema principal de nosso exemplo. Então vamos nomear esta entidade provisoriamente de ITEM_CLIENTE_COTACAO. Além da Quantidade, nos sobraram pouquíssimos atributos, sendo eles: Data da Cotação, Data de Envio da Cotação e o Valor Total da Cotação. Sendo assim, teremos nossa terceira entidade representada conforme a Figura 4.

Observe a Figura 2 e note que temos os atributos NUMERO_FONE_1 e NUMERO_FONE_2. Este é um típico problema encontrado em diversas estruturas de dados. Quando encontramos atributos com as mesmas características e nomes iguais, diferenciados apenas por números, como é nosso exemplo, isto caracteriza atributos multivalorados. Isto seria um grande problema para o negócio, pois a PESSOA só poderia ter dois telefones. Mas, e se houvesse a necessidade de incluir o terceiro telefone? Neste caso, apesar do atributo pertencer a entidade PESSOA, devemos torná-lo um atributo não repetitivo nesta entidade, e desta forma devemos isolar o atributo e criar uma nova entidade que tem por objetivo permitir a possibilidade de uma pessoa ter zero ou muitos telefones. Então aplicando a 1FN, teremos uma nova entidade e este novo objeto obviamente receberá o nome de PESSOA_TELEFONE, e será filha da entidade PESSOA. Para evoluir nosso exemplo e demonstrar o relacionamento entre as entidades, é importante atribuir uma Primary Key(PK) para a entidade PESSOA, que também deverá compor a PK da nova entidade PESSOA_TELEFONE. Observe na Figura 5 como devem ficar nossas duas entidades.

Figura 5. Entidades ainda desnormalizadas PESSOA e PESSOA_TELEFONE

Figura 4. Entidade desnormalizada ITEM_CLIENTE_COTACAO

Com o trabalho de alocação de atributos por assunto concluído, vamos iniciar a fase mais importante deste artigo, que é a aplicação das regras de normalização.

1FN Podemos afirmar que uma tabela só está na 1FN se obrigatoriamente respeitar a seguinte regra: 1. Não é permitida a existência de atributo multivalorado, ou seja, um atributo não pode receber mais de um valor. Mas como identificar esta situação e como tratá-la? É preciso sempre ficar atento a todas as características dos atributos, sempre observar as colunas que podem ter múltiplos valores para o mesmo registro. Vamos iniciar a aplicação da 1FN na entidade PESSOA e desco brir de uma forma clara como esta norma deve ser usada. 8 SQL Magazine Edição 101 •

Existem alguns casos em que o atributo não está repetido explicitamente, mas é fácil detectá-los, como é o caso do atributo DESCRICAO_ENDERECO. Este atributo tem as mesmas características do caso anterior e por este motivo, ele deve ser isolado em uma nova entidade, pois, apesar de pertencer a uma pessoa, é possível ter cadastrado nenhum ou muitos endereços para ela. Perceba que um verbo foi colocado no relacionamento entre as entidades PESSOA e PESSOA_TELEFONE. Ele indica a ação que uma entidade tem sobre a outra. Esta ação facilita o entendimento no momento da visualização e na leitura do modelo de dados lógico. Por exemplo, fica simples entender que uma pessoa tem telefones. Voltando ao nosso modelo, observe o exemplo da Figura 6 , onde a PK da entidade PESSOA é incorporada à PK da entidade PESSOA_ENDERECO, possibilitando a inclusão de muitos endereços para uma determinada pessoa. Com isso, as entidades PESSOA, PESSOA_TELEFONE e PESSOA_ENDERECO estão na 1FN. Agora vamos focar nossa validação na próxima entidade, que é a ITEM. Analisando os atributos da entidade ITEM, é fácil notar que nenhum dos três tem possibilidades de ser multivalorados, então passaremos para a validação da próxima entidade.

Apesar de não estar explícito o fato de redundância nesta norma, considero que minha interpretação seja mais abrangente e melhore um pouco o conceito e por este motivo, uniremos os dois atributos em um, que represente o nome do cliente e do vendedor e incluiremos um campo atrelado a uma Check Constraint (ler Nota DevMan 4) que represente qual o tipo da pessoa.

Nota do DevMan 4 Check Constraint: Uma Check Constraint(CK) é uma funcionalidade do banco de dados que

permite definir previamente condições para uma coluna, ou seja, a coluna só aceita os valores já determinados na CK

Figura 6. Entidades ainda desnormalizadas PESSOA , PESSOA_TELEFONE e PESSOA_ ENDERECO

Observe a Figura 4 e perceba que também existe a possibilidade de um Cliente solicitar a cotação de mais de um item e por este motivo, precisamos criar uma tabela de cotação, para que ela atenda esta demanda. A nova entidade deve receber um nome condizente com sua função, então nada mais lógico que seu nome seja COTACAO, e a sua tabela filha seja a COTACAO_ITEM e, desta forma, garantimos que a 1FN seja devidamente aplicada (ver Figura 7).

Se estamos modelando uma entidade PESSOA, nada mais justo que todas as pessoas do exemplo possam ser incluídas, portanto, o nome mais adequado para este novo atributo seja NOME_PESSOA. Você pode discordar da solução dada no exemplo e dizer que seria melhor controlar Vendedores e Clientes em entidades diferentes. Na verdade os dois estão certos, mas isto depende do objetivo do sistema. Neste caso, estamos modelando um exemplo simples, onde a ideia principal é passar os conceitos básicos de

Figura 7. Entidades PESSOA , COTACAO, COTACAO_ITEM_ITEM, NA 1FN

2FN Uma entidade só está na 2FN se respeitar obrigatoriamente as regras descritas abaixo: 1. Deve estar obrigatoriamente na 1FN; 2. Todas as colunas devem ser totalmente dependentes da PK. A 2FN nada mais é que um complemento da 1FN, pois você só pode avaliar sua consistência s e a norma anterior foi totalmente respeitada. Vamos iniciar a 2FN pela Figura 6 onde podemos encontrar dois campos que são redundantes, NOME_CLIENTE e NOME_VENDEDOR. Note que os dois campos pertencem à entidade PESSOA, porém possuem a mesma função e neste momento devemos retirar a redundânc ia e melhorar o conceito das entidades. Edição 101 SQL Magazine 9 •


normalização, e por este motivo, optei pela generalização, onde defino uma entidade para um assunto, embora fosse possível implementar a especialização, onde separaríamos as pessoas por grupos específicos. Entretanto, isto poderia causar algumas confusões ou perda de informações mais importantes passadas neste artigo. Agora observe a Figura 8 já considerando as novas modificações para adequar a 2FN.

Figura 9. Entidades ITEM e UNIDADE_MEDIDA, na 2FN

A entidade COTACAO já está na 2FN, pois todos os atributos são pertinentes a entidade. Sendo assim, encerramos a validação para todas as entidades no que diz respeito a esta regra. Analisando ainda a Figura 7 , podemos notar que a entidade COTACAO_ITEM é uma tabela associativa. As tabelas associativas são filhas de mais de uma entidade e tem obrigatoriamente as FKs herdadas compondo a PK, conforme o exemplo. Esta entidade também está na 2FN.

3FN Uma entidade só está na 3FN se respeitar obrigatoriamente as regras descritas abaixo: 1. Deve estar obrigatoriamente na 2FN; 2. Não pode haver atributos que tenham origem de cálculos derivados de outros atributos

Figura 8. Entidades PESSOA , PESSOA_TELEFONE e PESSOA_ ENDERECO na 2FN

A entidade ITEM é nosso próximo passo para a validação da 2FN. Observe que na Figura 3 nem todos os atributos pertencem a esta entidade e é simples identificar, basta seguir esta dica: Atributos com nomes Código, Identificador, Tipo, Sigla ou que tenham valores que precisam de algum mecanismo para identificá-los e não são PK, FK ou Check Constraint devem ser analisados, pois é necessário vinculá-los a uma Check Constraint ou serem transferidos para uma nova entidade e depois receber um relacionamento se tornando uma FK na entidade filha.

O atributo TIPO_UNIDADE_MEDIDA é um ótimo exemplo. Observando sua característica é possível dizer que ele deve ter sua representação em outra entidade, diferente do TIPO_PESSOA, que pode ser representada por uma Check Constraint. Cada um dos atributos mantidos no TIPO_UNIDADE_MEDIDA devem ter suas representações em uma nova entidade, e para isso foi necessário criar uma nova entidade UNIDADE_MEDIDA e transferí-la para lá, passando para a entidade ITEM apenas o atributo CODIGO_UNIDADE_MEDIDA como uma FK, conforme Figura 9. Agora temos que analisar a entidade COTACAO. Perceba que na Figura 7 existem dois relacionamentos da entidade PESSOA para a COTACAO. Estes relacionamentos são referentes ao cliente, que está solicitando uma cotação, e ao vendedor, que elabora a cotação. Apesar de terem a mesma origem, os atributos possuem nomes relativos à sua f unção na entidade COTACAO, fazendo com que sejam únicos nesta entidade. 10 SQL Magazine Edição 101 •

A 3FN é a norma mais simples de todas para se identificar, pois são atributos que armazenam resultados de cálculos efetuados entre outros atributos. Geralmente são atributos usados para fins de geração de relatórios ou telas de consultas e que n ão serão imputados pelo usuário. O armazenamento de cálculos além de ser uma desnormalização, pode ser também um grande problema. Suponha que a fórmula de um cálculo está errada e isso só é constatado meses ou anos depois. Imagine os problemas que a empresa pode ter por conta disso, pois as decisões e estratégias são tomadas a partir de informações extraídas dos dados armazenados. A única tabela que deve passar pela revisão da 3FN é a COTACAO, pois o campo VALOR_TOTAL_COTACAO pode ser o resultado dos atributos QUANTIDADE_ITEM e o VALOR_UNITARIO da entidade COTACAO_ITEM. Nosso modelo lógico está quase pronto, mas ainda podemos melhor um pouco mais. Temos uma entidade com a responsabilidade de armazenar os itens que serão comercializados e como os valores dos itens podem ser alterados no decorrer dos meses, nossas cotações nunca t erão o valor unitário real da época em que foram feitas. Para isso, devemos criar um campo do valor unitário dentro da entidade COTACAO_ITEM, com a intenção de gara ntir pesquisas e consultas das cotações anteriores. Esta inclusão não se caracteriza como uma redundância de dados, pois lembre-se que o valor unitário da entidade ITEM é mutável, pois sempre que houver reajuste de preços, este valor será alterado. Mas no caso da cotação, o valor unitário deve manter-se estático, sempre. Veja agora na Figura 10 como o nosso modelo lógico ficou após a aplicação da 1FN, 2FN e 3FN.

Preparando o modelo físico Mediante essas dicas básicas, observe como fica nosso modelo Agora precisamos usar a estrutura normalizada do nosso modelo de dados físico na Figura 11. lógico para criar o modelo físico e para isso é preciso entender algumas diferenças de nomenclaturas. Observe a Tabela 2. Exceções da normalização A partir deste momento, todas as entidades serão chamadas Sempre que um modelo de dados é validado, devemos nos de tabela e todos os atributos serão chamados de colunas, pois preocupar com a aplicação das regras da normalização, mas em estamos saindo da modelagem do negócio para a estrutura física alguns casos, somos obrigados a relevar esses conceitos. que será implementada no banco de dados. Existem alguns casos em que o dado armazenado na base de dados Você deve ter sempre em mente que uma padronização de nomes de uma empresa também é de propriedade de instituições externas, de objetos de banco de dados e seus tipos de dados são importantes principalmente entidades governamentais, então a exceção deve ser para o melhor aproveitamento possível no momento da utilização. aplicada aos documentos fiscais, aqueles que são feitos para atender Estruturas de dados sem padrão são difíceis de se trabalhar os governos, prefeituras, a Receita Federal e etc. nos momentos que é necessário efetuar Diferenças de nomenclatura entre os modelos manutenção ou até mesmo na hora de criar Lógico Físico instruções SQL. Tabela Tente sempre padronizar os nomes das Entidade Coluna tabelas e incluir colunas com uma natureza, Atributo a qual tem a finalidade de definir qual o Tabela 2. Diferença de nomenclatura ente os modelos de dados lógico e fisico seu data type. Outro ponto diferente entre os dois modelos são os nomes dos relacionamentos. No modelo de dados lógico nomeamos os relacionamentos com verbos que identificam a interação entre as entidades, sempre do pai para o filho, e no modelo físico, nomeamos o relacionamento com os nomes da tabela pai, seguida do nome da filha e iniciando com uma sigla que identifique qual é o tipo do índice. Neste caso, é comum usar a sigla “FK_”. Lembre-se também de sempre atribuir a opção “NOT NULL” para as informações obrigatórias. Desta forma, você garante que estes dados serão preenchidos assim que uma nova linha na tabela for inserida ou alterada. Figura 10. Modelo de dados lógico normalizado

Figura 11. Modelo de Dados Físico

Edição 101 SQL Magazine 11 •


Figura 12. Exemplo simplificado de Nota Fiscal

Um ótimo exemplo são as Notas Fiscais. Estes documentos devem ter exatamente seus campos e valores calculados e armazenados nas tabelas, mantendo os relacionamentos com as tabelas pais para rastreamento de informações. Para exemplificar a modelagem de uma Nota Fiscal, utilizaremos como ponto de partida o nosso modelo de cotação já desenvolvido. Como uma cotação de itens pode se tornar uma venda, criaremos nossas novas tabelas a partir da tabela COTACAO. Observe a Figura 12. As tabelas NOTA_FISCAL e NOTA_FISCAL_ITEM mantêm o relacionamento com as tabelas de origem das informações, porém todas as colunas são inseridas da mesma maneira que estão impressas na Nota Fiscal. O exemplo da Figura 12 não está completo, pois uma NF necessita de mais informações, trata-se apenas de um modelo didático. Este tipo de situação não pode ser considerado uma redundância ou quebra da normalização, pois os dados de um documento fiscal devem se manter íntegros. Se por acaso o nome ou o valor unitário de um produto for alterado, os dados dos documentos fiscais emitidos antes dessa alteração permanecerão estáticos, garantindo assim a informação gerada no documento. Resumindo, todas as colunas usadas para emitir uma Nota Fiscal devem estar na íntegra dentro da tabela Nota Fiscal.

12 SQL Magazine Edição 101 •

Conclusão Não existe um modelo de dados certo ou errado, o que existe são modelos bons ou ruins. Cada profissional tem uma forma de pensar e para um negócio, é possível encontrar várias maneiras de criar um modelo de dados. É muito importante saber os conceitos de normalização e identificar a função de cada atributo/coluna. Quanto mais o profissional modelar negócios, se aperfeiçoará e o resultado final será melhor que o anterior. O que se deve ter em mente é que a normalização é um conjunto de regras que vai guiar seu trabalho, agregando qualidade à estrutura de dados que será criada, facilitando assim a manutenção e manipulação dos dados. Usando as três formas normais descritas neste artigo, seus próximos modelos de dados irão ganhar mais qualidade e consequentemente o sistema também vai ficar melhor. O importante é utilizar as regras na sequência, para que nada seja deixado para trás, garantindo assim um total aproveitamento destas regras. Ponha em prática estes pequenos conceitos e tenha a certeza que o resultado será ótimo e com o tempo você usará essas regras naturalmente. Roberto de Angelantonio Jr.

[email protected]

Bacharel em Administração de Sistemas da Informação pela Universidade Ibero-Americana. Atua na área de sistemas a mais de vinte anos, sendo que, na maior parte da carreira trabalhou como Administrador de Dados. Experiência em implementar e reestruturar a área de Administração de Dados em diversas empresas. Dê seu feedback sobre esta edição!

A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista! Dê seu voto sobre este artigo, através do link: www.devmedia.com.br/sqlmagazine/feedback


m e g a l e d o M / e r a w t f o S e d o t e j o r P

NESTA SEÇÃO VOCÊ ENCONTRA ARTIGOS SOBRE PROJETO, ANÁLISE OU MODELAGEM DE DADOS

Introdução a projeto de banco de dados – Parte 1 Tipos de dados e chaves ESTE ARTIGO FAZ PARTE DE UM CURSO

Resumo DevMan De que se trata o artigo:

E

ncontrei mais um artigo muito interessante na internet. Na verdade, desta vez encontrei este artigo em um local realmente vasto de literaturas técnicas disponíveis para todos os profissionais. Estou falando do IBM developerWorks (ver seção Referências Bibliográficas). O artigo encontrado é do autor Philipp K. Janert (ver seção Referências Bibliográficas), que fala sobre alguns conceitos práticos no projeto de banco de dados. Este primeiro artigo trata dos tipos de dados simples e complexos e também uma visão sobre as chaves primária e estrangeira. Philipp é Consultor de Projetos de Software, programador de servidores e arquiteto. Seu interesse específico é a identificação, criação, transmissão e das melhores práticas de engenhar ia de software. Ele mantém o site www.BeyondCode.org e seus artigos foram publicados em IEEE Software, Linux Journal, e no site O’Reilly Network. Ele possui um Ph.D. em Física Teórica da Universidade de Washington em Seattle. Mas é claro que não poderia simplesmente fazer uma tradução. Me preocupei em agregar informações ao artigo e torná-lo mais agradável e rico pa ra você, leitor da SQL Magazine. Vamos ao que interessa. Nesta série, Phillip discute algu mas práticas gerais que encontrou durante sua trajetória profissional e percebeu que são particularmente úteis. Nenhuma dessas práticas é específica para um produto de qualquer fornecedor e tudo deve, portanto, ser aplicável, independentemente de qual implementação de banco de dados está sendo usada. 14 SQL Magazine Edição 101 •

O artigo apresenta as melhores práticas no desenvolvimento de um projeto de banco de dados relacional. Neste sentido, o artigo ajuda o arquiteto de dados a projetar os modelos de bancos de dados considerando boas práticas para garantir o êxito do projeto.

Em que situação o tema é útil: É especialmente útil desde o início do projeto de um sistema de banco de dados relacional.

Introdução a projeto de banco de dados – Parte 1: Quais são as melhores escolhas ao projetar o esquema para um banco de dados relacional? Qual é o fundamento para decidir em favor de um e contra alguma outra alternativa? Em face a grande variedade de recomendações e especificações dadas pelos desenvolvedores de Sistemas de Gerenciamento de Banco de Dados, como Oracle, Microsoft, IBM entre outros, é muito fácil ignorar os fundamentos básicos de banco de dados relacionais. Neste série de artigos serão apresentados os conceitos de tipos de dados simples e complexos, e também sobre as chaves primárias e chaves estrangeiras, que são extremamente importantes para manter o banco de dados íntegro e com bom desempenho. Também será vista uma introdução à normalização de banco de dados e as cinco formas normais. É discutido também outros usos possíveis para um banco de dados em um projeto, por exemplo, como um repositório de dados de configuração ou de registro.

As chaves primárias e assuntos afins Um banco de dados relacional (RDB) armazena dois tipos de informação - dados e relacionamentos. Podemos considerar dados como sendo os nomes de clientes, números de inventário, descrição de itens, e assim por diante, que o aplicativo usa. Já os relacionamentos se referem às chaves primárias e ch aves estrangeiras que o banco de dados precisa para encontrar registros de dados e relacioná-los uns aos outros.

Nota do DevMan 1 Surrogate key – chave substituta: A chave substituta (surrogate key) em um banco de dados é um

identificador único para qualquer entidade do mundo modelado ou um objeto no banco de dados. A chave substituta não é derivada dos dados da aplicação. Uma distinção importante entre uma chave substituta e uma chave primária depende se o banco de dados é um banco de dados atual (como os transacionais, ou on-line) ou um banco de dados temporal (conhecido como bando de dados históricos). Uma vez que um banco de dados atual armazena somente dados atuais válidos, há uma correspondência um-para-um entre uma chave substituta no mundo modelado e a chave primária do banco de dados. Neste caso, a chave substituta pode ser utilizada como uma chave primária. Numa base de dados temporal, no entanto, existe uma relação muitos-para-um entre as chaves primárias e as chaves substitutas. Uma vez que pode haver vários objetos na base de dados correspondente a uma chave substituta única, não podemos usar o substituto como uma chave primária; outro atributo é necessário para, além do substituto, identificar exclusivamente cada objecto. Alguns estudiosos argumentam que uma chave substituta deve ter as seguintes características: - O valor é único para todo o sistema, portanto, nunca reutilizado; - O valor é gerado pelo sistema; - O valor não é manipulável pelo usuário ou aplicação; - O valor não contém nenhum signifi cado semântico; - O valor não é visí vel para o usuário ou aplicação; - O valor não é composto de vários valores de domínios diferentes.

Conceitos básicos sobre relacionamentos Para efeitos de modelagem de dados, os relacionamentos devem ser praticamente transparentes. Na verdade, o conhecimento purista de banco de dados não faz distinção entre dados e relacionamentos. No entanto, você vai ver que os relacionamentos são mais eficientes para a administração e manutenção, bem como em termos de desempenho de execução, ter alguns campos adicionais que servem como chaves para o banco de dados. Cada tabela deve ter uma chave primária: um atributo ou com binação de atributos que são garantidos como sendo únicos e não nulos. Em geral, é útil introduzir uma c have substituta ( surrogate key – ver Nota DevMan 1) - um atributo de tabela que não tem nenhum significado para a regra de negócio, mas simplesmente serve como identificador único para cada registro na tab ela. Este é o relacionamento a que me refiro. Os requisitos para uma chave primária são muito rigorosos. Uma chave primária deve: Existir; Ser única; Não deve mudar ao longo do tempo. • • •

As chaves substitutas ajudam a mitigar o fato de que os dados reais da aplicação quase nunca cumprem estes requisitos. Nem toda pessoa tem um Número de Seguridade Social (os que estão fora os EUA), as pessoas mudam seus nomes, e outras informações importantes. Os dados da aplicação também podem simplesmente ser ruins falhas no sistema de Administração da Previdência Social podem levar a diferentes pessoas que têm o mesmo número de Seguro Social. Uma chave substituta ajuda a isolar o sistema a partir de tais problemas.

Em um banco de dados atual, a chave substituta pode ser a chave primária, gerada pelo sistema de gerenciamento de banco de dados e não derivada dos dados do aplicativo no banco de dados. O único motivo de se ter uma chave substituta é o de atuar como a chave primária. É também possível que a chave substituta exista, além do UUID gerado pelo banco de dados (por exemplo, um número de RH para cada empregado que não seja o UUID de cada empregado). A chave substituta é freqüentemente um número sequencial (por exemplo, em Sybase ou SQL Server uma coluna do tipo “identity column”, um número serial no PostgreSQL, uma sequência (sequence) no Oracle ou uma coluna definida com AUTO_INCREMENT no MySQL), mas não necessariamente precise ser, estes são apenas mecanismos que auxiliam nesta tarefa. Ter a chave independente de todas as outras colunas isola as relações de banco de dados a partir de mudanças nos valores de dados ou projeto do banco de dados (tornando a base de dados mais ágil) e garante a exclusividade. Numa base de dados temporal, é necessário distinguir entre a chave substituta e a chave primária. Normalmente, cada linha teria tanto uma chave primária quanto uma chave substituta. A chave primária identifica a linha única no banco de dados, a chave substituta identifica a entidade única no mundo modelado; estas duas chaves não são as mesmas. Por exemplo, a tabela "Funcionário" pode conter duas linhas para “John Smith”, uma linha quando ele foi contratado entre 1990 e 1999, outra linha quando ele foi contratado entre 2001 e 2006. A chave substituta é idêntica (não exclusivo) em ambas as linhas no entanto a chave primária será única. Alguns arquitetos de banco de dados usam chaves substitutas sistematicamente, independentemente da adequação das chaves candidatas, enquanto outros usarão uma chave já presente nos dados, se houver.

A segunda razão para que chaves substitutas sejam favoráveis tem a ver com eficiência e facilidade de manutenção, desde que você pode escolher o tipo de dados mais eficiente para a chave substituta. Além disso, a chave substituta é tipicamente um campo único (não é uma chave composta), o que simplifica o esquema (particularmente quando a chave é utilizada em outras tabelas como uma chave estrangeira – ver Nota DevMan 2). Toda tabela deve ter uma coluna dedicada a servir como chave primária desta. Esta coluna pode ser chamada de “id” ou “pk” (ou possivelmente _id ou _pk – isso vai depender da padronização de nomenclatura adotada). A maioria dos bancos de dados são ajustados para t irar proveito de consultas utilizando-se números inteiros, por isso faz sentido usar este tipo de dados como chave primária. Muitos bancos de dados, incluindo Oracle, Postgres também fornecem um tipo inteiro especial chamado “serial” ou “sequencia”, o que gera uma sequência de números inteiros únicos. Declarar uma coluna para usar esses tipos garante que uma única chave é gerada para cada linha inserida. As chaves estrangeiras são atributos da tabela, que são os valores das chaves primárias de outra tabela. Muitas vezes faz sentido rotular colunas de chave estrangeira explicitamente, por exemplo, através da adopção de uma convenção de nomenclatura, como _fk. A restrição de integridade referencial (referências) deve ser declarado como parte da instr ução CREATE ao criar a tabela. Vale a pena repetir que as chaves substitutas fazem parte apenas do relacionamento - a sua existência não elimi na a exigência de modelagem para ser capaz de formar uma chave primária a partir dos dados da aplicação. Nos dados da aplicação, uma Edição 101 SQL Magazine 15 •

Introdução a projeto de banco de dados – Parte 1

chave candidata é um subconjunto de todos os atributos, valores que nunca são nulos, e que c ada combinação de valores é único. Como uma verificação da correta modelagem de dados, as chavea candidatas devem existir e ser documentada para cada tabela.

Nota do DevMan 2 Foreign key – chave estrangeira: No contexto de bancos de dados relacionais, uma chave

estrangeira é uma restrição referencial entre duas tabelas. Uma chave estrangeira é um campo em uma tabela relacional que corresponde a uma chave candidata de outra tabela. A chave estrangeira pode ser usada para referência cruzada entre tabelas. Por exemplo, imagine que temos duas tabelas, uma tabela de clientes que inclui todos os dados do cliente, e uma tabela de pedidos que inclui todos os pedidos dos clientes.A intenção aqui é que todos os pedidos devem ser associados a um cliente que já está na tabela CLIENTE.Para fazer isso, vamos colocar uma chave estrangeira na tabela de pedidos e relacioná-la com a chave primária da tabela CLIENTE. A chave estrangeira identifica uma coluna ou conjunto de colunas em uma tabela (referência) que se refere a uma coluna ou conjunto de colunas em outra tabela (referenciada).As colunas da tabela de referência deve referenciar as colunas da chave primária ou superchave na tabela referenciada.Os valores em uma linha das colunas que fazem referência deve ocorrer em uma única linha na tabela referenciada.Assim,uma linha na tabela de referência não podem conter valores que não existem na tabela de referência (exceto potencialmente NULL).Desta forma,as referências podem ser feitas para relacionar informações,o que é uma parte essencial da normalização banco de dados.Várias linhas na tabela de referência podem se referir a mesma linha na tabela referenciada.Na maioria das vezes,reflete o relacionamento um (tabela pai ou tabela de referência) para muitos (tabela filho,ou referência a tabela) – 1:n. A tabela de referência e a referenciada podem ser a mesma, ou seja, a chave estrangeira remete para a a chave primária da mesma tabela. Este tipo de relacionamento é conhecido, no SQL:2003, como um auto-relacionamento ou chave estrangeira recursiva. Uma tabela pode ter várias chaves estrangeiras, e cada chave estrangeira pode referenciar uma tabela diferente. Cada chave estrangeira é aplicada de forma independente pelo sistema de banco de dados. Portanto, os relacionamentos entre tabelas em cascata podem ser estabelecidos usando chaves estrangeiras.

Nota do DevMan 3 Candidate key – chave candidata :No modelo de dados de bancos de dados relacionais, uma

chave candidata de uma relação é uma superchave mínima para que haja um relacionamento, isto é, um conjunto de atributos, tais que: - a relação não tem duas tuplas distintas (ou seja, linhas ou registros em linguagem de banco de dados) com os mesmos valores para estes atributos (o que significa que o conjunto de atributos é uma superchave); - não há nenhum subconjunto adequado destes atributos para o qual detém (o que significa que o conjunto é mínimo). Os atributos constituintes são chamados atributos primos. Por outro lado, um atributo que não aparece em qualquer chave candidata é chamado de um atributo não-primo. Uma vez que uma relação não contém tuplas duplicadas, o conjunto de todos os seus atributos é superchave se valores nulos não são utilizados. Daqui resulta que cada relação terá pelo menos uma chave candidata. As chaves candidatas de uma relação nos dizem todas as maneiras possíveis que podemos identificar as suas tuplas. Como tal, são um conceito importante para o projeto de um esquema de banco de dados. Por razões práticas, Sistemas de Gerenciamento de Banco de Dados geralmente exigem que, para cada relação uma das suas chaves candidatas seja declarada como chave primária, o que significa que ele é considerado como a forma preferida para identificar tuplas individuais. Chaves estrangeiras, por exemplo, geralmente são obrigadas a fazer referência a tal chave primária e não a qualquer uma das chaves candidatas.


Na prática, nem sempre você vai encontrar uma chave candidata entre os dados da aplicação. Imagine uma tabela para armazenar o primeiro e o último nome de cada usuário, mas não tenha nenhum atributo adicional. Agora vamos supor que há duas pessoas diferentes, ambos têm o primeiro nome “Joe” e sobrenome “Blow”. Em tal caso, não existe nenhuma combinação de atributos da tabela que podem formar uma chave candidata adequada. O problema é se você está falando sobre a singularidade de conjuntos de dados ou sobre a singularidade das entidades em questão - usuários, neste exemplo. Em geral, é mais intuitivo, em especial para os desenvolvedores acostumados a análise orientada a objetos, para modelar a singularidade das entidades em questão. As chaves substitutas, como discutido anteriormente, podem ajudar a alcançar este objectivo.

Chaves alternativas e identificadores visíveis Como parte do relacionamento, a chave substituta não tem necessidade de ser sempre visível fora do banco de dados. Em particular, nunca deve ser revelada para o usuário / aplicação. Isto permite ao administrador do banco de dados alterar a representação das chaves à vontade, conforme a necessidade. Se uma necessidade de negócio surge que proporcia à aplicação um identificador único para um conjunto de dados, este identificador deve ser considerado como dados reais da aplicação e mantido separado do relacionamento. Por exemplo, uma coluna adicional chamada “NumeroConta” ou algo semelhante pode ser introduzida sem que faça parte do relacionamento. Claro, este atributo deve ser não nulo e único para que forme uma chave candidata (ver Nota DevMan 3). Tendo uma coluna separada para identificadores visíveis também torna possível gerar e formatar os valores para este atributo de uma forma amigável de modo que seja fácil ler informações sobre o telefone de um cliente, por exemplo. Um caso limite é quando o identificador não é diretamente visível, mas ainda pode ser acessível para a aplicação. Os exemplos incluem os campos ocultos em páginas Web nas quais um identificador é devolvido para o cliente para ser usado como um parâmetro no pedido seguinte. Embora não haja nenhuma necessidade de o usuário lidar com o identificador, um malware pode tentar falsificar este identificador. Usando os valores numéricos de uma chave primária diretamente , em princípio, permite que qualquer ataque hacker percorra toda a tabela! Defesas contra este problema incluem tanto criptografar quanto descriptografar o valor da chave primária, ou proteger a chave anexando um Message Authentication Code (MAC). Uma alternativa é usar um atributo identificador visível de difícil falsificação para a tabela, como atribuir valores hash (ver Nota DevMan 4) para a chave primária ou um rótulo do tempo da criação (claro, a singularidade deste atributo deve ser assegurada.) Para que a chave seja visível para a aplicação (em oposição ao usuário final), vai depender diretamente das especificidades do projeto. Usar um tipo numérico diretamente faz com que a representação da chave do banco de dados esteja diretamente no código da aplicação e deve ser evitado para não cair no problema

de acoplamento. Em desenvolvimentos de pequena escala, uma representação do valor da chave do tipo string pode ser aceitável (todos os tipos de dados que podem ser armazenados em um banco de dados devem ser capazes de serem serializados). Mas a melhor solução é utilizar um simples empacotador, objeto conhecido como wrapper , que adiciona uma pequena complexidade, mas fornece uma forte dissociação da representação das chaves do banco de dados e suas interfaces. O perigo é tornar o objeto wrapper muito inteligente. A intenção das chaves substitutas é torná-las simples e eficiente para o banco de dados manusear. Configurações de um valor no banco de dados, possivelmente, a partir de um string , comparando com outro objeto chave, e, possivelmente, a serialização são todos os métodos que são necessários. Inteligência, tal como a capacidade de verificar o conteúdo com base em um cálculo de um valor hash, sugerem que esse objeto provavelmente pertence ao domínio de dados da aplicação (como os identificadores de registros visíveis, apresentado anteriormente).

Nota do DevMan 4 Função Hash: Uma função hash é um algoritmo ou sub-rotina que mapeia grandes conjuntos

de dados de comprimento variável, chamados chaves, para conjuntos de dados menores de um comprimento fixo. Por exemplo, o nome de uma pessoa, tendo um comprimento variável, pode ser convertido em um valor hash de um único número inteiro. Os valores retornados por uma função hash são chamados de valores hash, códigos hash, somas hash, checksum ou simplesmente hashes. As funções hash são usadas principalmente para acelerar as tarefas de pesquisa de comparação de tabelas ou de dados, tais como encontrar itens em um banco de dados, detecção de valores duplicados ou registros semelhantes em um arquivo grande, encontrando trechos similares em seqüências de DNA, e assim por diante. Uma função hash deve ser referencialmente transparente, ou seja, se chamada duas vezes em uma entrada que é “igual” (por exemplo, as strings que consistem na mesma sequência de caracteres), deve dar o mesmo resultado. Este é um conceito em muitas linguagens de programação que permitem ao usuário substituir igualdade e funções hash para um objeto: se dois objetos são iguais, seus códigos hash devem ser o mesmo. Funções hash estão relacionadas com (e muitas vezes confundida com) checksums, dígitos de verificação, impressões digitais, funções de randomização, códigos de correção de erros e funções hash criptográficas. Embora estes conceitos se sobrepõem, em certa medida, cada um tem as suas próprias utilizações e requisitos e são desenvolvidas e optimizadas de maneira diferente. O banco de dados HashKeeper mantido pelo American National Drug Intelligence Center, por exemplo, é mais adequadamente descrito como um catálogo de impressões digitais de arquivo do que de valores hash.

O problema de Universally Unique Identifier - UUID Uma última consideração diz respeito à possível necessidade de um identificador universalmente exclusivo (UUID – Universally Unique Identifier – ver Nota DevMan 5). A resposta é que, de fato, bancos de dados relacionais não necessitam de UUIDs. Na verdade, o conceito UUID é pouco relacionado com o gerenciamento de banco de dados relacionais. Chaves de bancos de dados relacionais - o relacionamento - precisam apenas ser únicas em cada tabela , que pode ser conseguido usando um tipo de dados auto-incremento, tais como o tipo de seriais mencionado anteriormente.

Nota do DevMan 5 Universally Unique Identifier - UUID: Um identificador exclusivo universal (UUID –

Universally Unique Identifier) é um padrão identificador utilizado na construção de software, padronizado pela Open Software Foundation (OSF) como parte do Distributed Computing Environment (DCE). A intenção de UUIDs é permitir que sistemas distribuídos identifiquem informações sem uma coordenação central significativa. Neste contexto, a palavra "único" deve ser entendida como “praticamente único” em vez de “única garantidamente”. Uma vez que os identif icadores têm um tamanho finito, é possível que dois itens diferentes compartilhem o mesmo identificador. O tamanho do identificador e o processo de geração devem ser definidos de modo a tornar esta situação suficientemente improvável na prática. Qualquer um pode criar um UUID e usá-lo para identificar algo com confiança razoável de que o mesmo identificador nunca será involuntariamente criado por qualquer pessoa para identificar outra coisa. A informação etiquetada com UUIDs pode, portanto, ser posteriormente combinada em um único banco de dados sem a necessidade de resolver conflitos de identificadores.

UUIDs podem ter algumas dificuldades técnicas. Para garantir a unicidade, todos os UUIDs devem ser gerados por um serviço centralizado - o que leva a problemas de escalabilidade e pode se tornar um ponto único de falha. A questão da escalabilidade pode ser solucionada por uma abordagem estratificada em que um servidor central é usado para dar sementes para vários escravos, que por sua vez geram os identificadores finais em lotes, e assim por diante. Para representar um UUID em um banco de dados, pode-se utilizar como atributo uma string ou uma chave composta que compreende várias colunas inteiras. Ambas abordagens são significativamente mais lentas do que as operações com base em chaves compostas de inteiros longos. Chaves compostas também aumentam a complexidade do esquema do banco de dados, quando utilizado como chaves estrangeiras. No final, se um registro em um banco de dados precisa ter um ID exclusivo global verdadeiramente é ditado pelas regras de negócio, e não pela arquitetura de banco de dados. Alguns reg istros já podem conter alguma forma de UUID (itens de mercadorias, por exemplo, normalmente possuem um Código de Produto Universal como código de barras). Alguns outros registros, possivelmente correspondentes a entidades empresariais principais, podem de alguma outra forma já conter um identificador único como parte de seus dados corporativos (como a combinação de timestamp e o nome da conta para uma entrada de livro-caixa, por exemplo). Se este não for o caso, um UUID pode ser gerado e armazenado juntamente com os dados úteis para os registos que o exijam. Em qualquer caso, UUIDs devem ser considerados como parte dos dados da aplicação - e não do relacionamento. Mesmo que (e este é um grande “se”) a abordagem de mapeamento objeto-relacional escolhida exija que cada objeto de negócios tenha um ID único e persistente, não há necessidade de basear os trabalhos internos do banco de dados relacional nesse fato. Em resumo, eu “bato na tecla” de que os dados de negócio devem ser mantidos fora do relacionamento interno do banco de dados. Construir um banco de dados relacional em torno de UUIDs que bra este princípio usando atributos, que, no fina l, são realmente parte dos dados de negócios, como infraestrutura interna. Edição 101 SQL Magazine 17 •


Tipos de dados O padrão SQL define uma série de tipos de dados padrão e a maioria dos fornecedores de Sistemas de Gerenciamento de Banco de Dados implementam alguns outros adicionais que são específicos para o seu próprio produto. Na ausência de razões verdadeiramente convincentes para o contrário, evite tais extensões por causa da portabilidade. Strings e Números Em geral, os tipos numéricos oferecem poucos problemas - basta selecionar um que é grande o suficiente para suportar o tamanho necessário de valores. A tentativa de encontrar tamanho ideal de uma coluna do tipo string é um esforço que geralmente não vale a pena. Você pode evitar um monte de confusão mais tarde, definindo todas os campos de texto com o tipo “varchar(n)” e limitando-se a alg uns comprimentos de string padrão e introduzindo apelidos (aliases) para eles, tais como: 32bytes (“Label”), 256bytes (“Nota”), e 4k (“Texto”). Mesmo se os requisitos de negócios restringirem o tamanho máximo de campos determinados para valores específ icos, o esquema de banco de dados definitivamente não é o melhor lugar para fazer cumprir essas regras. O momento em que os dados chegam ao banco de dados, é tarde demais para fazer qualquer coisa sobre ele (exceto rejeitá-los). Restrições individuais, decorrentes de regras de negócio e requisitos, devem ser executadas pela camada de lógica de negócio, que lida com a interação do usuário e val idação de entrada. Por outro lado, a manutenção do esquema do banco de dados é consideravelmente simplificado, se for limitada a um punhado de atributos de strings diferentes. Limite o uso de strings de tamanho fixo para os códigos de todos os tipos (em oposição a strings de tamanho variável para o texto real). Tenha em mente porém, que aparentemente, códigos de tamanho fixo se tornam mais amplos ao longo do tempo. O arquiteto de banco de dados prudente tenta evitar qualquer coisa semelhante ao problema Y2K (bug do milênio) para novos trabalhos de desenvolvimento. Tempo e dinheiro Um tipo para gravar timestamps (combinações de data / hora) é sempre necessário e é, felizmente, coberto pelo padrão SQL. Infelizmente, não há nenhuma implementação de tipo de dados que satisfaça plenamente a necessidade de gravar um valor monetário. Armazenar os valores monetários e tratá-los no código do programa como um valor de ponto flutuante sempre leva a erros de arredondamento. Gravando o valor como um inteiro exato da menor subdivisão monetária (como “cent” para dólares norteamericanos, bem como para Euros e outras moedas apropriadas) pode não ser suficiente também. Muitos valores usam mais dígitos além do ponto decimal que os dois que existem nas moedas (basta visitar o seu posto de gasolina e você verá). A escolha de decimal com 5 a 9 dígitos deve funcionar, no entanto. 18 SQL Magazine Edição 101 •

Sem contar que nenhum valor monetário deve ser armazenado sem também registrar a moeda - mesmo se você ache que a sua aplicação nunca vai lidar com qualquer coisa, diferente de dólares. Considere a criação de uma tabela de moeda e relacionando-a com os valores monetários usando chaves estrangeiras em vez de incorporar informações de moeda diretamente. Isto ajuda com a internacionalização (nomes diferentes de moeda e símbolos), bem como com problemas de formatação.

Booleanos e o tipos de tabelas de sistema O uso do tipo “bool” em qualquer lugar do projeto é uma dica para repensar este tópico especial novamente. Pouquíssimos atributos são verdadeiramente limitados a apenas dois valores - mesmo uma coluna “Gênero” tem uma tendência maliciosa de se transformar para (pelo menos) três estados - masculino, feminino, e desconhecido. Permitir nulos apenas mascara o problema real. Uma abordagem mais flexível para os códigos de tipo é necessária. Em muitos lugares no banco de dados, atributos determi nam o tipo de um registro de alguma forma. A coluna “Gênero” mencionada acima é um exemplo. Outros exemplos podem incluir “TipoItem” (como mercadoria, seguro, embalagem e frete), “TipoPagamento” (dinheiro, cheque, ordem de pagamento, cartão de crédito, troca), e coisas como “TipoLoja”, “TipoAfiliação”, “TipoDispositivo”, e muitos mais. Isto inclui também casos em que você deseja armazenar o tipo de um objeto em algum modelo de objeto aplicável. Para cada tipo, você precisa de alguma forma de documentação dizendo não só o nome do tipo, mas também as características associadas com ele, por exemplo, você pode querer saber o que as permissões de cada “TipoUsuario” tem. Que melhor lugar para manter esta informação do que no próprio banco de dados? Qualquer registro que tem alguma forma de informações de tipo associado a ele deve conter uma coluna de código de tipo, que em si é uma chave estrangeira referenciando uma tabela de códigos de tipo. A restrição de chave estrangeira garante que n ão há registros com tipos inválidos. A tabela de códigos de tipo pode ter os seguintes atributos: • typeCode_pk; • Label (mnemônico exclusivo, como varchar(32)); • Descrição (varchar(256) deve ser suficiente) • URI (apontando para recursos adicionais, se necessário)

(ver Nota DevMan 6); • codeGroup_fk.

Atributos adicionais são, naturalmente, concebíveis - tais como um código de três letras ou um código numérico visível. O atributo codeGroup_fk serve para organizar os códigos de tipo relacionados. Por exemplo, todos os “TipoAssinante” poderiam formar um grupo. O atributo codeGroup_fk é uma chave estrangeira em uma tabela separada. No entanto, ao perceber que um grupo de código nada mais é que um código de tipo, a relação pode ser feita de modo recursivo onde codeGroup_fk

referencia typeCode_pk. Isso não só faz da tabela código de tipo desnecessária, mas também torna possível organizar os grupos em uma estrutura hierárquic. É melhor manter o código de tipo de sistema relativamente simples e direto.

• • • • • •

Nota do DevMan 6

• •

URI (Uniform Resource Identifier): Em TI, um URI - Uniform Resource Identifier

(Identificador Uniforme de Recursos) é uma cadeia de caracteres compacta usada para identificar ou denominar um recurso na Internet. O principal propósito desta identificação é permitir a interação com representações do recurso através de uma rede, tipicamente a Rede Mundial, usando protocolos específicos. URIs são identificadas em grupos definindo uma sintaxe específica e protocolos associados. Um URI pode ser classificado como um localizador (URL) ou um nome (URN), ou ainda como ambos. Um URN - Uniform Resource Name (Nome de Recursos Uniforme) é como o nome de uma pessoa, enquanto que um URL - Uniform Resource Locator (Localização de Recursos Uniforme) é como o seu endereço. O URN define a identidade de um item, enquanto que o URL nos dá um método para o encontrar. Um URN típico é o sistema ISBN para identificar individual mente os livros. ISBN 0-486-27557-4 (urn:isbn:0-486-27557-4) cita sem equívocos uma edição específica da obra de Shakespeare “Romeu e Julieta”. Para aceder a esta objeto e ler o livro, é necessário obter a sua localização: um endereço URL. Um URL típico para este livro é um caminho de arquivos, como file:////home/pedro/Desktop/ RomeoAndJuliet.pdf, identificando o livro eletrônico salvo no disco de um PC local.

• • • • • • • • • • • • •

Então o propósito de URNs e URLs é o de serem complementares.

Tipos de dados complexos Finalmente, , existem alguns tipos de dados comuns, mas complexos, , tais como números de telefone, , endereços postais, in informainformaformações de contato, e cartões de crédito - que ocorrem em quase todos os esquemas de banco de dados. Normalmente, esses registros precisam ser acessados a partir de muitas tabelas no banco de dados. Em um sistema de eCommerce típico, por exemplo, pode ser necessário armazenar informações de contato para os usuários, fornecedores, armazéns, e administradores. Ao invés de incluir os atributos atributos do do respectivo respectivo usuário, usuário, , fornecefornecedor, ou outro registro. (e repetindo assim as colunas em todo o banco de dados), faz sentido a criação de uma tabela única para as informações de contato que é referenciada por chaves estrangeiras por todas as outras tabelas. Isto tem dois benefícios imediatos: É mais fácil alterar posteriormente a cardinalidade dos relarelacionamentos; Ele localiza as futuras alterações ao tipo de dados complexo. •

•

Antecipar os atributos que serão provavelmente necessários para cada um desses tipos complexos é uma arte. arte.. Minha Minha sugessugestão é tentar se esforçar ao máximo desde o início ao invés de ser forçado a mudar o esquema a cada vez que um campo adicional torna-se necessário. Uma amostragem de atributos possíveis para endereços postais inclui: Departamento; Empresa; • •

Correio; Endereço Linha 1; Endereço Linha 2; Endereço Linha 3; Cidade; Estado; Código Postal (“CEP”); País. Informações de contato podem incluir os seguintes atributos: Título; Nome; Nome do Meio (ou inicial); Sobrenome; Sufixo (como jr. ou sr.); Endereço residencial; Endereço comercial; Telefone residencial; Telefone comercial; Telefone celular; Fax; Pager; E-mail.

Finalmente, números de telefone não devem ser considerados simplesmente números. Na verdade, eles se dividem em os seguintes campos: Código do País; Código de área; Prefixo; Número da linha; Ramal. • • • • •

Em um número de telefone, tais como 987-1234, o prefixo é o 987 e o número da linha é 1234. O ramal é a única parte do número de telefone que é opcional. É, provavelmente, suficiente usar char(4) para todas as colunas, mas pode-se considerar char(6) por segurança. Note-se que os códigos de área nos EUA estão limitados a três dígitos, mas isso não é verdade para outros países. Os dados confidenciais devem ser mantidas de forma criptografada. Mesmo que o sistema de banco de dados esteja comprometido, os dados ainda são protegidos contra uso indevido. O mais famoso exemplo deste tipo de gerenciamento de dados é o sistema de senhas Unix, que apenas armazena hashes de senhas dos usuários, em vez das senhas propriamente ditas. Algu ns dados, como números de cartão de crédito, precisam ser codif icados em uma forma recuperável, no entanto, uma criptografia de mão única (como para o arquivo de senhas Unix) não vai funcionar. Isto leva ao problema da administração de chaves de criptografia - claramente, não devem ser armazenadas no banco de dados, juntamente com os segredos, mas que são fornecidos no momento necessário, por exemplo. Edição 101 SQL Magazine 19 •


Conclusão Neste artigo, discutimos algumas práticas gerais ao projetar bancos de dados relacionais , incluindo: Os benefícios da manutenção da tabela de atributos adicionais sem qualquer significado de negócios para servir como chaves substitutas; A recomendação de não basear os trabalhos internos do banco de dados em identificadores universalmente únicos; A utilização de uma instalação centralizada de códigos de tipo para expressar atributos com intervalos predeterminados de valores; Algumas considerações na concepção de tipos de dados complexos para serem usadas durante todo o esquema de banco de dados. •

•

•

Ricardo Rezende

e-mail: [email protected] / [email protected] Blog: http://www.devmedia.com.br/ricardorezende Blog: http://purl.org/ricarezende/blog Twitter: http://twitter.com/ricarezende

DBA Oracle certificado pela Oracle University (DBA 9i track e DBA OCP 10g). IBM Certified Database Associate – DB2 9. DBA Oracle na IBM do Brasil em projeto internacional administrando ambiente de produção de alta criticidade. Consultor independente de Bancos de Dados. Editor técnico da revista SQL Magazine. Mestrando em Ciência da Computação pelo Instituto de Computação da Universidade Estadual de Campinas - IC UNICAMP. Docente no curso de Administração de Banco de Dados na Dextra Systems.

•

No próximo artigo, falarei sobre normalização de banco de dados e alguns usos para um banco de dados dentro de um projeto, como o uso de tabelas de histórico e logs de eventos. Até a próxima.




m NESTA SEÇÃO VOCÊ ENCONTRA ARTIGOS SOBRE PROJETO, ANÁLISE OU MODELAGEM DE DADOS e g a l e d o M / e r a w t f o S e d o t e j o E r Resumo DevMan P

Os dez erros comuns em projetos de banco de dados – Parte 1 STE ARTIGO FAZ PARTE DE UM CURSO

De que se trata o artigo:

S

em a menor sombra de dúvidas a internet se tornou uma das principais fontes de consulta do mundo moderno. Nem me lembro mais qual foi a última vez que entrei em uma biblioteca para pesquisar algo. Sim, continuo comprando livros. Por sinal adoro perder algumas horas em uma boa livraria, principalmente as que possuem livros técnicos. Mas não podemos negar que a qua ntidade de material de boa qualidade e de fácil acesso que encontramos na internet é vasta e interessante. E justamente em uma dessas navegadas encontrei um artigo interessante escrito por Louis Davidson, um Arquiteto de Dados (DA – Data Architect , em inglês) com mais de 15 anos de experiência em Bancos de Dados (ver seção Referências Bibliográficas), que fala sobre os dez erros mais comuns no projeto de Bancos de Dados. Mas é claro que não poderia simplesmente fazer uma tradução. Me preocupei em agregar informações ao artigo e torná-lo mais agradável e rico para você, leitor da SQL Magazine. Se o projeto de Banco de Dados é feito de maneira correta, então o desenvolvimento, implementação e, consequentemente, o desempenho geral do banco de dados terá poucos problemas. Um banco de dados bem projetado “simplesmente funciona”. Há um pequeno número de erros no projeto de banco de dados que transformam a vida de desenvolvedores, gerentes e DBAs em um imenso inferno astral. Este artigo trata dos dez piores erros que são cometidos de uma maneira até que comum em grande parte dos projetos de bancos de dados. Nenhuma lista de erros jamais será definitiva. As pessoas, algumas vezes, fazem um monte de coisas

Este artigo apresenta os dez erros mais comuns no processo de modelagem de um banco de dados, tanto no tocante à modelagem em si, quanto à manutenção e também o desempenho geral do banco de dados. Neste contexto, o conteúdo deste artigo serve para que o arquiteto de dados reflita sobr e algumas más práticas de modelagem de dados para que possa evitá-las em seus projetos.

Em que situação o tema é útil: Durante o desenvolvimento de um modelo de dados, evitar certos vícios e práticas pouco adequadas é especialmente útil para que o projeto como um todo alcance o êxito desejado ou que, ao menos, o retrabalho seja o mínimo possível.

Os dez erros comuns em projetos de banco de dados – Parte 1: Durante o desenvolvimento de um projeto de banco de dados é comum nos depararmos com situações realmente assustadoras. Muitas dessas situações estão diretamente ligadas a erros que os arquitetos comentem mais frequentemente do que gostaríamos. É fato que se pretendêssemos escrever sobre todos os erros comuns durante o projeto/modelagem de banco de dados, teríamos assunto para um livro inteiro, e com direito a um segundo livro. Como a intenção não é a de escrever um livr o, separamos os dez err os mais comuns e que, acreditem, podem ser totalmente evitados. Neste primeiro artigo serão abordados os cinco primeiros, que passam por problemas de má concepção e planejamento do pro jeto, problemas típicos de falta de normalização, falta completa de padrões de nomenclatura, total falta de documentação e chegando ao problema de utilização de uma única tabela para armazenar todos os valores de domínio. Sem dúvida nenhuma este artigo não traz a solução para todos os problemas de projeto de banco de dados, mas leva o leitor a uma reflexão profunda do que ele pode evitar para obter um projeto pleno e o mais próximo possível do êxito.


Os dez erros comuns em projetos de banco de dados – Parte 1

estúpidas para garantir um prazo ou até mesmo por pura preguiça, inclusive eu. Conheço muita gente assim, acredite. Esta lista reflete simplesmente os erros de projeto de banco de dados que nos deparamos com uma certa frequência, infelizmente. É importante salientar que todos nós estamos sujeitos a comenter equívocos e mesmo implementar práticas, como dizer, “pouco ortodoxas” que podem não surtir o efeito desejado. É claro que sempre que escrevemos qualquer artigo, seja para a revista ou para o blog, procuramos apresentar sempre as melhores práticas e ensinar sempre o melhor possível. O mais importante é que, após ler este artigo, você tenha sempre uma pequena voz interior lhe alertando sempre que estiver prestes a cometer um destes equívocos. Vejamos então a tão falada lista: 01. Má concepção / planejamento; 02. Ignorar a normalização; 03. Falta de padrões de nomenclatura; 04. Falta de documentação; 05. Uma tabela para armazenar todos os valores de domínio; 06. Usar coluna auto-incremento como sua ún ica chave; 07. Não utilizar as características do SQL para proteger a integridade de dados; 08. Não usar stored procedures (ver Nota DevMan 1) para acessar dados; 09. Tentativa de construção de objetos genéricos; 10. Falta de testes. Neste primeiro artigo, veremos os cinco primeiros erros comuns descritos acima.

Má concepção / planejamento O autor já inicia com uma frase de efeito: “Se você não sabe onde está indo, qualquer caminho te leva até lá” – George Harrison. Palavras proféticas para qualquer momento da vida e uma descrição do tipo de questões que afligem muitos projetos nos dias de hoje. Deixe-me perguntar: você pode contratar um empreiteiro para construir uma casa e exigir que ele comece a construir a fundação no dia seguinte? Pior ainda, se você exigir que seja feito sem pro jetos ou planta da casa? Felizmente, você respondeu “não” (espero que tenha respondido isso)para ambos. Um projeto é necessário para a casa que você quer construída, e que a terra sob a qual está a construí-la não afunde em alguma caverna subterrânea. Agora, se você respondeu “sim”, eu não tenho certeza de que este artigo possa te ajudar! Exatamente como uma casa, um bom banco de dados é construído com premeditação e com o devido cuidado e atenção despendidos às necessidades dos dados que este banco de dados irá armazenar, não podem ser simplesmente jogados em uma espécie de “balaio”. Uma vez que o banco de dados é a base de praticamente todos os projetos de negócios, se você não utilizar corretamente o tempo para mapear as necessidades do projeto e como o banco de dados vai ao encontro deles, então as chances são de que todo o projeto irá desviar-se do curso e perder a direção. Além disso, se você não dispender o tempo necessário logo no início do projeto para garantir que o mesmo esteja correto, então você verá que todas as alterações substanciais nas estruturas de banco de dados que você precisa fazer mais para a frente poderão causar um impacto

Nota do DevMan 1 Stored Procedures: Uma stored procedure (procedimento armazenado) é uma sub-rotina disponível

para aplicativos que acessam um sistema de banco de dados relacional. Uma stored procedure (muitas vezes chamada de proc, sproc, stopro, StoredProc, sp ou SP) é, na verdade, um código armazenado no dicionário de dados do banco de dados. Os usos típicos para stored procedures incluem a validação de dados (integrado ao banco de dados) ou mecanismos de controle de acesso. Além disso, as stored procedures podem consolidar e centralizar a lógica que foi originalmente implementada na camada da aplicação. Processamentos extensos ou complexos que requerem a execução de várias instruções SQL são movidos para stored procedures e todos os aplicativos simplesmente “a chamam”. É possível ainda utilizar stored procedures aninhadas, ao executar uma stored procedures de dentro de outra. As stored procedures são semelhantes às funções definidas pelo usuário (UDF – User Defined Function, em inglês).A principal diferença é que UDFs podem ser usadas como qualquer outra expressão dentro de instruções SQL, enquanto stored procedures devem ser chamadas utilizando a instrução CALL ou EXECUTE. As stored procedures podem retornar conjuntos de resultados, ou seja, os resultados de uma instrução SELECT. Tais conjuntos de resultados podem ser processados usando cursores, através de outras stored procedures, associando um conjunto de resultados de localização, ou por aplicativos. As stored procedures podem conter também variáveis declaradas para processamento de dados e cursores que permitem percorrer várias linhas em uma tabela. Linguagens procedurais normalmente incluem declarações IF, WHILE, LOOP, REPEAT, CASE, e muito mais. As stored procedures podem receber variáveis ou modificar as variáveis e devolvê-las, dependendo de como e onde a variável é declarada. A implementação exata e correta de stored procedures varia de um sistema de banco de dados para outro. Fornecedores de banco de dados mais importantes os implementam de alguma forma. Dependendo


do sistema de banco de dados, as stored procedures podem ser implementados numa variedade de linguagens de programação, por exemplo SQL, Java, C ou C++. As stored procedures escritos em linguagens de programação não-SQL podem ou não executar instruções SQL. A crescente adoção de stored procedures levaram à introdução de elementos procedurais para a linguagem SQL nas normas SQL1999 e SQL2003. Isso fez com que o SQL se tornasse uma linguagem de programação imperativa. A maioria dos sistemas de banco de dados oferecem extensões proprietárias, que vão além do descrito nas normas SQL1999 e SQL2003. Abaixo uma listagem dos principais Sistemas de Gerenciamento de Banco de Dados e respectiva linguagem de implementação de stored procedures: • CUBRID: Java; • DB2: SQL PL ou Java; • Firebird: PSQL (Fyracle também suporta uma parcela de PL/SQL, da Oracle); • Informix: SPL • Microsoft SQL S erver: Transact-SQL e várias l inguagens do framework .NET; • MySQL: stored procedures próprias, adere à norma SQL2003; • Oracle: PL/SQL ou Java; • PostgreSQL: PL/pgSQL, pode também usar linguagens próprias como pl/perl ou pl/php; • Sybase ASE: Transact-SQL.

enorme em todo o projeto, e aumentar significativamente a proba bilidade de o cronograma do projeto “ir por água abaixo”. Muito frequentemente, uma fase de planejamento é simplesmente ignorada em favor de apenas “garantir o cronograma”. O projeto sempre avança em uma certa direção e quando os pro blemas inevitavelmente surgem - devido à falta de concepção e planejamento - não há “tempo hábil” para voltar atrás e corrigir os problemas corretamente, usando técnicas apropriadas. É qua ndo as “gambiwares” começam (somos profissionais de TI, não fazemos gambiarras, mas sim “gambiwares”), com a promessa velada de voltar e consertar as coisas mais tarde, algo que acontece muito raramente, quando acontecem. O fato é que é impossível prever todas as necessidades que o projeto terá que cumprir e cada problema que provávelmente surjirá, mas é importante que haja um planejamento cuidadoso para mitigar possíveis problemas, tanto quanto possível.

Ignorar a normalização Normalização (ver Nota DevMan 2) define um conjunto de métodos para quebrar tabelas em suas partes constituintes, até que cada tabela represente uma e somente uma “coisa”, e suas colunas servem para descrever completamente apenas essa “coisa” que a tabela representa.

Nota do DevMan 2

conjuntos de dados quantos você queira, para produzir o con junto f inal que você precise. Esta natureza “aditiva” é extremamente importante, não apenas pela facilidade de desenvolvimento, mas também em função de desempenho. Os índices são mais eficazes quando trabalham com todo o valor da chave. Sempre que precisar usar funções do tipo CHARINDEX, SUBSTRING, LIKE, etc. em sua consulta SQL para analisar um valor que é uma combinação com outros valores (por exemplo, para buscar apenas o último nome de uma pessoa em uma coluna onde está armazenado o nome completo) os paradigmas da linguagem SQL começam fazer com que os dados sejam cada vez menos “encontráveis”. Então, normalizar os seus dados é essencial para o bom desempenho e facilidade de desenvolvimento, mas a pergunta sempre vem à tona: “Quando a normalização está suficientemente normalizada?” Se você já leu algum livro sobre normaliz ação, então você já deve ter ouvido muitas vezes que a 3ª forma normal (3FN) é essencial, mas 4ª e 5ª formas normais (4FN e 5FN) são realmente úteis e, uma vez que você tem o domínio sobre elas, se torna muito fácil de acompanhar e chega-se à conclusão que valeu a pena o tempo necessário para implementá-las. Na realidade, porém, é bastante comum que nem mesmo a 1ª. forma normal (1FN) seja implementada corretamente. Sempre que vejo uma tabela com os nomes das colunas repetidos e simplesmente com números como sufixo, eu entro em pânico. E saiba que eu entro em pânico com bastante frequência. Considere a tabela “Cliente”, no exemplo da Figura 1.

Normalização: Aplicações em modelo de dados para um banco de dados relacional envolve a

necessidade de remoção de duplicidades e, para atingir este objetivo, usamos um processo chamado de "normalização". Este processo é composto por um conjunto de regras conhecidas como "formas normais" (ou simplesmente "FN"). A normalização é aplicada a um conjunto de dados ou tabelas em um banco de dados relacional, enquanto as tabelas são utili zadas para armazenar diretamente os dados associados a ela. As tabelas podem ser relacionadas ou ligadas entre si através de índices identificadores. Este índice identificador é usado para identificar a linha de dados da mesma forma que um índice tradicional em um livro.Veja que o índice é usado para encontrar uma determinada informação de interesse sem a necessidade de leitura de todo o livro. Basicamente, existem 5 níveis de normalização conhecidos como 1ª, 2ª, 3ª, 4ª e 5ª formas normais (ou 1FN, 2FN, 3FN, 4FN e 5FN) e cada uma das formas normais é um refinamento da forma normal anterior, ou seja, para dizermos que o modelo se encontra na 2ª forma normal, entende-se que este modelo também atende as regras da 1ª forma normal.

O conceito de normalização tem sido adotado a aproximadamente 30 anos e é a base sobre a qual a linguagem SQL e bancos de dados relacionais são implementados. Em outras palavras, a linguagem SQL foi criada para trabalhar com as estruturas de dados normalizadas. A normalização não é apenas alguma técnica usada por programadores de banco de dados para irritar os programadores de aplicativos (que é apenas um efeito colateral). A linguagem “aditiva” em sua natureza uma vez que, se você tem apenas pedaços de dados, é fácil construir um conjunto de valores ou resultados. Na cláusula FROM, você “pega” um conjunto de dados (uma tabela) e adiciona a outra tabela at ravés de uma junção (JOIN). Você pode adicionar (ou juntar) tantos

Figura 1. Tabela “Cliente” totalmente desnormalizada

Há sempre 12 pagamentos? A ordem dos pagamentos é realmente relevante? Será que um valor NULL para um pagamento significa DESCONHECIDO (não preenchido até o momento), ou um pagamento perdido (inadimplencia)? E quando foi feito o pagamento? Edição 101 SQL Magazine 23 •


Um pagamento não descreve um cliente e não deve ser arma zenado na tabela “Cliente”. Informações sobre os pagamentos devem ser armazenados em uma tabela de pagamento, em que você tam bém pode registrar informações adicionais sobre o pagamento, como quando o pagamento foi feito, e qual o motivo deste pagamento. Veja na Figura 2 como deve ser feita a normalização.

Figura 2. Tabela “Cliente” normalizada

No projeto da Figura 2 , cada coluna armazena uma única unidade de informação sobre uma “coisa” única (pagamento), e cada linha representa uma instância específica de um pagamento. O projeto da Figura 2 vai exigir um pouco mais de código no processo, mas é muito mais provável que você seja capaz de descobrir o que está acontecendo no sistema.

Falta de padrões de nomenclatura E mais uma frase de efeito: “Isso que nós chamamos de rosa, mesmo que chamado por qualquer outro nome teria um cheiro agradável”. Esta citação de “Romeu e Julieta”, de William Shakespeare, parece muito boa, e é verdade, por uma perspectiva. Se todos concordarem que, a partir de agora, uma rosa deve ser chamada de esterco, então nós poderíamos superar isso e o cheiro c ontinuaria sendo bastante agradável. O problema é que, se ao construi r um banco de dados para uma florista, o arquiteto de dados chamar de esterco e o cliente chama de rosa, então você terá algumas reuniões que soam muito mais como um episódio de Abbott e Costello (ver Nota DevMan 3) do que uma conversa séria sobre armazenar informações sobre os produtos hortícolas.

Nota do DevMan 3 Abbott e Costello: William “Bud” Abbott e Lou Costello (nascido Louis Francis Cristillo), eram uma

dupla de comédiantes norte-americanos cujo trabalho no palco, rádio, cinema e televisão fez deles a equipe de comédia mais popular durante os anos 1940 e 1950.

Nomes, enquanto uma escolha pessoal, são a primeira e mais importante linha de documentação para a sua aplicação. Eu não vou entrar em todos os detalhes de qual a melhor maneira de defin ir nome às coisas neste artigo, é um tema amplo e confuso. O que eu quero enfatizar neste artigo é a necessidade de coerência. Os nomes escolhidos não são apenas para que você possa identif icar a finalidade de um objeto, mas para permitir que todos os fut uros programadores, usuários, e assim por diante, de forma rápida e 24 SQL Magazine Edição 101 •

fácil, compreendam como um componente do banco de dados foi definido para ser utilizado, e quais os dados que ele armazena. Nenhum usuário futuro de seu projeto precisará percorrer um documento de 500 páginas para determinar o significado de algum nome maluco. Considere, por exemplo, uma coluna chamada X304_DSCR. Que diabos isso significa? Você pode decidir, depois de coçar bastante a cabeça, que significa “Descrição X304”. Provavelmente isso acontece, mas talvez DSCR signifique “Discriminador”, ou “Discretizator”? A menos que você tenha estabelecido DSCR como uma abreviação padrão corporativa para a descrição, então X304_DESCRICAO é um nome muito melhor, e não deixa margens à imaginação. Isso fará com que você só precise descobrir o que a parte X304 do nome significa. Na primeira “olhada”, para mim, soa muito mais que X304 deva ser uma informação a ser armazenada na coluna do que o nome da coluna propriamente dito. Se eu, posteriormente, descobrir que, na organização, houve também um X305 e X306, então eu iria sinalizar que houve um problema com o projeto do banco de dados. Para maior flexibilidade, os dados são arma zenados em colunas, e não em nomes de coluna. Nessa mesma linha, é muito importante resistir à tentação de incluir “metadados” em nome de um objeto. Um nome como “tblCliente” ou “colVarcharEndereco” pode parecer útil a partir de uma perspectiva de desenvolvimento, mas para o usuário final, é apenas conf uso. Como um desenvolvedor, você deve confiar em ser capaz de determinar que um nome de tabela é um nome de tabela pelo contexto no código ou ferramenta, e apresentar para os usuários definições de nomes claros e simples, como “Cliente” e “Endereço”. Uma prática que eu recomendo fortemente é a NÃO utilização de espaços e identificadores entre aspas em nomes de objetos. Você deve evitar os nomes das colunas como “Número da peça” ou, no estilo Microsoft, [Número da peça], exigindo, portanto que os seus usuários (programadores) tenham que incluir esses espaços e identificadores em seu código. É chato e simplesmente desnecessário. Alternativas aceitáveis seriam NumeroPeca, numero_peca ou numeroPeca. Mais uma vez, a consistência é a chave. Se você escolher NumeroPeca então tudo bem - desde que a coluna que contém números de fatura seja chamada de NumeroFatura, e não uma das outras variações possíveis.

Falta de documentação Sugeri na introdução que, em alguns casos, estou escrevendo para mim mesmo tanto quanto para você. Este é o tópico onde isso é mais verdadeiro. Tomando o devido cuidado para nomear seus objetos, colunas e assim por diante, você pode tornar claro para qualquer um o que é que seu banco de dados está modelando, ou seja, qual é a realidade modelada em seu banco de dados. No entanto, este é apenas um passo na batalha pela criação de uma documentação. A triste realidade é, porém, que “um passo” é muitas vezes o único passo.

Você não só terá um modelo de dados bem elaborado ao aderir a um padrão de nomeação sólido, mas também terá definições sobre suas tabelas, colunas, relacionamentos e restrições padrão ou de verificação (constraints – ver Nota DevMan 4), de modo que será claro para todos como estes objetos foram projetados para serem utilizados. Em muitos casos, você pode querer incluir valores de amostra, conforme uma necessidade que surgiu em um determinado objeto, ou qualquer outra coisa que você possa querer saber sobre aquele objeto em um ano ou dois, quando “você do futuro” tem que voltar e fazer alterações no código.

Nota do DevMan 4 Restrições - Constraints: As restrições de integridade (integrity constraints) são regras nomeadas

que restringem os valores para uma ou mais colunas em uma tabela. Essas regras evitam a entrada de dados inválidos nas tabelas. Além disso, as restrições podem impedir a exclusão de uma tabela quando certas dependências existam. Se uma restrição estiver habilitada, então o banco de dados verifica os dados que são inseridos ou atualizados. Os dados que não estejam em conformidade com a restrição são impedidos de serem inseridos. Se uma restrição estiver desabilitada, dados que não estejam em conformidade com as restrições podem ser autorizados a serem inseridos no banco de dados. Veja o exemplo abaixo: CREATE TABLE funcionario (id_funcionario NUMBER(6), nome VARCHAR2(20), sobrenome VARCHAR2(25) CONSTRAINT func_sobrenome_nn NOT NULL, email VARCHAR2(25) CONSTRAINT func_email_nn NOT NULL, telefone VARCHAR2(20), admissao DATE, CONSTRAINT func_admissao_nn NOT NULL, id_funcao VARCHAR2(10) CONSTRAINT func_funcao_nn NOT NULL, salario NUMBER(8,2), pct_comissao NUMBER(2,2), id_gerente NUMBER(6), id_departamento NUMBER(4), CONSTRAINT func_salario_min CHECK (salario > 0), CONSTRAINT func_email_uk UNIQUE (email)); No exemplo acima, a instrução CREATE TABLE especifica restrições NOT NULL para as colunas sobrenome, email, admissão e id_funcao. As cláusulas de restrição identificam as colunas e as condições de restrição. Estas restrições garantem que as colunas especificadas não contenham valores nulos. Por exemplo, uma tentativa de inserir um novo funcionário sem identificação da função gera um erro. Você pode criar uma restrição durante ou depois da criação uma tabela. As restrições podem ser temporariamente desabilitadas, se necessário. O banco de dados armazena as i nformações sobre as restrições no dicionário de dados.

Pode ser armazenado no próprio banco de dados, utilizando as propriedades estendidas. Alternativamente, podem ser mantidos nas ferramentas de modelagem de dados. Pode até mesmo ser armazenado em aplicativos separados, como o MS-Excel ou ainda em outro banco de dados relacional. Minha empresa mantém um banco de dados como repositório de metadados, que desenvolvemos a fim de apresentar esses dados para usuários finais em um formato “pesquisável”. O formato e a usabilidade são importantes, mas a batalha principal é ter a informação disponível e atualizada. Seu objetivo deve ser o de fornecer informações suficientes para que quando você efetivamente implemente o banco de dados, os programadores possam ser capazes de descobrir seus pequenos erros e corrigi-los (sim, todos nós cometemos erros em nosso código). Eu sei que há uma velha piada que o código mal documentado é um sinônimo de “segurança do emprego” (o famoso job preservation , ou em bom português,

preservação do emprego). Embora haja u ma pitada de verdade nisso, é também uma forma de ser odiado por seus colegas de trabalho e nunca ter um aumento. E nenhum bom programador que conheço quer voltar e refazer seus próprios códigos anos mais tarde. É melhor se os erros no código possam ser gerenciados por um programador júnior enquanto você se dedica à criação da próxima novidade. A segura nça do emprego, juntamente com aumentos é alcançada por ser a pessoa certa para novos desafios.

Uma tabela para armazenar todos valores de domínio E mais uma frase de efeito: “Um Anel para a todos governar e na escuridão aprisioná-los”. Isto é muito bom quando estamos em terreno de fantasias, mas não é tão bom quando aplicada ao projeto de banco de dados, na forma de uma tabela de domínio. Bancos de dados relacionais são baseados na ideia fundamental de que cada objeto representa uma e somente uma coisa. Nunca deve haver qualquer dúvida quanto ao que se refere determinada parte dos dados. Ao navegar através dos relacionamentos, de nome de coluna, para nome da tabela, e também a chave primária, deve ser simples determinar os relacionamentos e entender exatamente o que uma fração dos dados significa. O grande mito perpetrado por arquitetos que realmente não compreendem a arquitetura de banco de dados relacional (me incluo, no início da minha carreira) é que quanto mais tabelas existirem, mais complexo será o projeto. Assim, ao contrário, não deveríamos condensar várias tabelas em uma ú nica tabela do tipo “balaião” para simplificar o projeto? Por exemplo, considere o seguinte modelo onde eu precisava de valores de domínio para: Status de crédito do cliente; Tipo de cliente; Status da fatura; Status da encomenda do item da fatu ra; Envio do item da fatura via transportadora. • •

Algo a ser considerado é que onde esta documentação será armazenada é, em grande parte, uma questão de padrões corporativos e/ou conveniência para os desenvolvedores e usuários finais.

• • •



Diante disso, que seriam cinco tabelas de domínio... mas por que não usar uma tabela de domínio genérico, como a apresentada na Figura 3? O modelo apresentado na Figura 3 pode parecer uma forma muito limpa e natural para criar uma tabela para todos, mas o problema é que esta técnica não se mostra muito natural ao se trabalhar com

consultas SQL. Vamos imaginar que só queremos os valores de domínio para a tabela “Cliente”, veja a consulta SQL necessária para encontrar esta informação na Listagem 1. Como você pode ver, a solução apresentada na Listagem 1 está longe de ser uma junção natural. Tudo se resume ao problema de misturar maçãs com laranjas. À primeira

vista, as tabelas de domínio são apenas um conceito abstrato de um recipiente que contém texto. E do ponto de vista da implementação isto é bem verdade, mas não é a maneira correta de construir um banco de dados. Numa base de dados, o processo de normalização, no sentido de quebrar e isolar dados, assume cada tabela considerando que cada linha representa uma coisa. E cada domínio de valores é uma coisa distintamente diferente de todos os outros domínios (a menos que ele não seja e, este é o caso em que uma única tabela será suficiente). Então o que você faz, em essência, é normalizar os dados para cada projeto, dividindo o trabalho ao longo do tempo, ao invés de fazer toda a tarefa de uma única vez e ter que recomeçar mais tarde. Então, ao invés da tabela única para todos os domínios, você pode modelá-lo como apresentado na Figura 4. Parece mais difícil de fazer, certo? Bem, inicialmente realmente é. Francamente, gastei mais tempo para completar as tabelas de exemplo. Mas, no final das contas, há ganhos enormes ao se adotar esta técnica. Vamos a eles: • Para início de conversa, a consulta SQL

Figura 3. Modelo utilizando tabela de domínio

necessária para encontrar a mesma informação da Listagem 1 é muito mais fácil de ser escrita, conforme mostra a Listagem 2; • Os dados podem ser validados utilizan do restrições de chaves estrangeiras, muito naturalmente, algo inviável para a solução da Figura 3 , a não ser que você implemente intervalos de chaves para cada tabela uma confusão terrível de gerenciar; • Se concluir que você precisa armazenar

mais informações sobre um EnvioTransportadora além de apenas o código: “UPS”, e sua descrição: “United Parcel Service”, então é bastante simples apenas adicionar uma coluna ou duas. Você poderia ainda expandir a tabela para ser uma representação completa das empresas de transporte; • Todas as tabelas de domínio menores

Figura 4. Modelo da Figura 3 normalizado


irão caber em uma única página de disco (falando em armazenamento físico). Isso garante uma leitura única (e provavelmente uma única página em cache – ver Nota DevMan 5). No outro caso (tabela única de domínio), você pode ter sua tabela de domínio espalhada por muitas páginas de

disco, a menos que se use a técnica de clustering , que, por outro lado, poderia “custar” mais para usar um índice não- cluster se você tem muitos valores; • Você ainda pode ter um editor para todas as linhas, como a

Nota do DevMan 5 Cache: Em ciência da computação, um cache é um componente que armazena dados de forma

maioria das tabelas de domínio provavelmente terá o mesmo uso ou a mesma estrutura base. Mas, considerando que você perderia a capacidade de consultar todos os valores de domínio em uma consulta consulta SQL simples, por que você faria isso? (Uma consulta utilizando JOIN e/ou UNION pode ser facilmente criada conforme necessário, mas isso parece uma necessidade improvável).

transparente para que as futuras solicitações aos dados possam ser servidas mais rapidamente. Os dados que são armazenados em um cache podem ser valores que foram calculados anteriormente ou duplicatas de valores originais que são armazenados em outro lugar. Se os dados solicitados estão contidos no cache (cache hit), este pedido pode ser servido pela simples leitura do cache, que é relativamente mais rápido. Caso contrário (cache miss), os dados tem de ser recalculados ou obtidos a partir de seu local de armazenamento original, que é relativamente mais lento. Assim,quanto maior o número de solicitações que podem ser servidos a partir do cache, mais rápido se torna o desempenho geral do sistema.

Listagem 1. Consulta SQL para encontrar informações através de tabela de domínio.

Para ser eficiente e para permitir uma utilização eficiente dos dados, caches são relativamente pequenos. No entanto, caches se consolidaram em diversas áreas da informática, pois os padrões de acesso em típicas aplicações de computador têm sua localidade de referência. Referências exibem localidade temporal se os dados solicitados já foram recentemente solicitados. Referências exibem localidade espacial se os dados solicitados estiverem fisicamente armazenados próximo a dados que foram solicitados recentemente.

SELECT * FROM Cliente JOIN DominioGenerico as TipoCliente ON Cliente.idTipoCliente = TipoCliente.idDominioGenerico AND TipoCliente.Relativo_a_Tabela = ‘Cliente’ AND TipoCliente.Relativo_a_Coluna = ‘idTipoCliente’ JOIN DominioGenerico as StatusCredito ON Cliente.idStatusCredito = StatusCredito.idDominioGenerico AND StatusCredito.Relativo_a_Tabela = ‘Cliente’ AND StatusCredito.Relativo_a_Coluna = ‘idStatusCredito’; Listagem 2. Consulta SQL para encontrar informações no modelo normalizado.

SELECT * FROM Cliente JOIN TipoCliente ON Cliente.idTipoCliente = TipoCliente.idTipoCliente JOIN StatusCredito ON Cliente.idStatusCredito = StatusCredito.idStatusCredito;

O hardware implementa cache como um bloco de memória para armazenamento temporário de dados suscetíveis de serem utilizados novamente. CPUs e unidades de disco rígido frequêntemente usam um cache, como fazem navegadores e servidores web. Uma memória cache é constituída por um conjunto de entradas. Cada entrada tem um dado - uma cópia do mesmo dado em alguma unidade de armazenamento. Cada entrada tem também uma etiqueta, que especifica a identidade do ponto de referência na unidade de armazenamento das quais a entrada é uma cópia. Quando o cliente de cache (CPU, navegador web, sistema operacional) precisa acessar um dado que presumidamente exista na unidade de armazenamento, ele primeiro verifica o cache. Se uma entrada pode ser encontrada com uma etiqueta correspondente ao do dado desejado, o dado da entrada é usado. Esta situação é conhecida como cache hit. Assim, por exemplo, um navegador da Web pode verificar a sua cache local no disco para ver se ele tem uma cópia local dos conteúdos de uma página web em uma determinada URL. Neste exemplo, a URL é a etiqueta e o conteúdo da página web é o dado. A percentagem de acessos que resultam em cache hits é conhecida como a taxa de acerto ou taxa de acerto do cache.



Eu provavelmente deveria refutar o pensamento que deve estar em sua mente neste momento: “E se eu precisar adicionar uma nova coluna para todas as tabelas de domínio?” Por exemplo, você esqueceu que o cliente quer ser capaz de fazer a classificação personalizada em valores de domínio mas não colocou nada nas tabelas para permitir isso. Esta é uma pergunta justa, especialmente se você tem 1000 dessas tabelas em um banco de dados muito grande. Primeiro, isso raramente acontece , e quando isso acontecer vai ser uma grande mudança para o seu banco de dados de qualquer forma. Segundo, mesmo que isso se torne uma tarefa necessár ia, a linguagem SQL tem um conjunto completo de comandos que você pode usar para adicionar colunas a tabelas, e usar as tabelas do sistema é uma tarefa bastante simples para criar um script para adicionar a mesma coluna para centenas de tabelas de uma só vez. Isso não será uma mudança tão fácil, mas não vai ser muito difícil em relação aos grandes benefícios. O ponto chave desta dica é simplesmente que é melhor fazer o trabalho adiantado, formando estruturas sólidas e sustentáveis, ao invés de tentar fazer o mínimo de trabalho ao começar um projeto. Ao manter as tabelas o mais normalizado possível para representar uma “coisa”, significa que a maioria das eventuais mudanças só afetará uma tabela, e podemos concluir que haverá menos retrabalho para você no futuro.

Conclusão Vimos neste primeiro artigo os cinco erros mais comuns em projetos de banco de dados. Como citei acima, é apenas uma fração do que nos deparamos no dia-a-dia e também tenho certeza que continuarei encontrando.


A ideia aqui não é a de produzir um passo-a-passo do que se deve ou não fazer, mas apenas apontar alguns casos em que o ganho final é substancial. A chave para o sucesso, neste caso, é a profunda análise do que se vai fazer antes de efetivamente fazê-lo. E, principalmente, deve-se colocar na balança o “ganho instantâneo” X “ganho futuro” de uma determinada abordagem. Será que o ganho de tempo que terei ao não fazer determinada coisa (ou fazer) será um ganho de fato no f uturo no momento de uma manutenção de código ou de objeto? Esta é a reflexão que dese jamos com este artigo. Até a próxima. Ricardo Rezende

e-mail: [email protected] / [email protected] Blog: http://www.devmedia.com.br/ricardorezende Blog: http://purl.org/ricarezende/blog Twitter: http://twitter.com/ricarezende

DBA Oracle certificado pela Oracle University (DBA 9i track e DBA OCP 10g). IBM Certified Database Associate – DB2 9. DBA Oracle na IBM do Brasil em projeto internacional administrando ambiente de produção de alta criticidade. Consultor independente de Bancos de Dados. Editor técnico da revista SQL Magazine. Mestrando em Ciência da Computação pelo Instituto de Computação da Universidade Estadual de Campinas - IC UNICAMP. Docente no curso de Administração de Banco de Dados na Dextra Systems. Dê seu feedback sobre esta edição!



Validando documentos XML através do XML Schema Definition NESTA SEÇÃO VOCÊ ENCONTRA ARTIGOS BANCO DE DADOS, SQL OU PERSISTÊNCIA


Validando documentos XML através do XML Schema Definition Usando o XSD na prática

N

ão é de hoje que a parceria entre empresas de um mesmo segmento vem sendo usada como saída para a preservação de uma carteira de clientes. Digamos que sua empresa possui um sistema contábil de forte nome no mercado (sistema A) e um cliente que vocês pleiteiam já utiliza outro sistema que não é tão forte na área contábil (sistema B) mas que os atendem nas operações gerais; devido às grandes dificuldades que se apresentam na implantação de um novo ERP (nota DevMan 1) o cliente optou por não substituir o sistema em operação (sistema B), mas sim fazer solicitações de melhoria das suas funcionalidades. A software house que mantém o sistema B não tem interesse em realizar tais adequações, ou por falta de tempo, pessoal ou expertise. A solução mais plausível seria uma integração entre os softwares, onde o Sistema B enviaria as informações necessárias para que o sistema A pudesse funcionar a contento.

Resumo DevMan De que se trata o artigo: Apresentar para o leitor o XSD (XML Schema Definition), a linguagem de validação de documentos XML (eXtensible Markup Language) mais utilizada,demonstrandosuas principaisfuncionalidadeseaplicabilidades. Depois que o leitor estiver familiarizado com as principais terminologias do assunto iremos aplicá-las no Microsoft SQL Server.

Em que situação o tema é útil: Imagine que sua empresa acaba de fechar uma parceria com um exconcorrente e você foi incumbido de realizar a integração dos softwares. Naturalmente você optará por fazer a transferência de informações através de documentos XML, o problema é que o padrão XML é extremamente flexível, ocasionando problemas na integração. A fim de evitar retrabalhos você precisará definir uma estrutura “engessada” do documento para que possa haver uma correta integração entre os dados.

Validando documentos XML através do XML Schema Definition:

Nota do DevMan 1 Riscos na implantação de um ERP: Sistemas Integrados de Gestão Empresarial

(SIGE ou SIG), em inglês Enterprise Resource Planning (ERP), são sistemas de informação que integram todos os dados e processos de uma organização em um único sistema. A integração pode ser vista sob a perspectiva funcional (sistemas de: finanças, contabilidade, recursos humanos, fabricação, marketing, vendas, compras, etc) e sob a perspectiva sistêmica (sistema de processamento de transações, sistemas de informações gerenciais, sistemas de apoio a decisão, etc). Os ERPs, em termos gerais, são uma plataforma de software desenvolvida para integrar os diversos departamentos de uma empresa, possibilitando a automação e armazenamento de todas as informações de negócios. Existem diversos fatores que podem ocasionar uma falha na implantação de um ERP, podemos citar: - Erros de estimativa de custos de infraestrutura. - Desmotivação pessoal. - Falta de capacitação dos usuários finais.


A integração entre sistemas já é uma realidade na área de desenvolvimento de software, porém para que esta integração possa ocorrer sem maiores problemas faz-se necessário o uso de tecnologias de padronização para o meio de comunicação entre as partes. Como, atualmente, o principal meio de transmissão de dados se dá através de documentos XML, torna-se obrigatória a compressão das principais formas de validação destes documentos. Hoje em dia, a principal forma de realizar tal validação é utilizando os recursos do XML Schema. Estes recursos serão abordados no decorrer do artigo de uma forma prática e de fácil entendimento.

Após algumas reuniões foi definido que o transporte dos dados deverá ser feito através de documentos XML (ler Nota DevMan 2). Até aí nenhuma surpresa, o XML é atualmente, com certeza, o mais aceito e utilizado padrão de transporte de dados, devido a sua simplicidade, segurança e confiabilidade. Como se sabe, o padrão XML possui uma altíssima flexibilidade no que tange a

construção de suas tags, por isso se diz que ele é linguagem auto descritiva. Contudo, toda esta elasticidade culmina em problemas para validação dos documentos a serem transmitidos. Para que este problema fosse contornado criaram-se padrões para validação dos dados na camada de transporte.

(string, data, inteiro) ou complexo. Neste último caso, utilizamos a marcação complexType para especificar como é estruturado o elemento em questão.

Nota do DevMan 3 Nota do DevMan 2 XML: O padrão XML vem como um meio de garantir a interoperabilidade entre sistemas, através de

uma linguagem simples, fácil e confiável. Com ele os desenvolvedores podem focar na codificação de regras de negócio ou outras tarefas que demandem um nível de conhecimento específico, ao invés de criarem lógicas complexas para validação dos arquivos na camada de transporte dos dados.

Neste contexto, apresentaremos neste artigo o XSD (XML Schema Definition), a linguagem de validação de documentos XML mais utilizada, demonstrando suas principais funcionalidades e aplicabilidades. Depois que o leitor estiver familiarizado com a s principais terminologias do assunto iremos aplicá-las no Microsoft SQL Server Documentos XML podem ser classificados em bem formados ou válidos. Um documento é considerado bem formado quando segue as regras definidas pela sintaxe da XML. Para ser válido, além de seguir a sintaxe da XML, é necessário que o documento esteja de acordo com algum esquema previamente definido. O objetivo do XML Schema é, justamente, definir os blocos legais de construção de um documento XML. Neste esquema podem ser definidos: os elementos presentes no documento, os atributos que podem aparecer, quais elementos são filhos de outros elementos, a sequência em que os elementos filhos podem ser utilizados, a quantidade de elementos filhos, se um elemento pode ser vazio ou conter texto, os tipos de dados para os elementos e atributos e valores padrão para atributos dentre outros. O XML Schema foi originalmente proposto pela Microsoft e é, atualmente, uma recomendação do W3C. Alguns pontos que têm apoiado sua utilização são: • suporte a tipo de dados facilitando descrever o conteúdo do

documento; • a utilização da sintaxe da linguagem XML para definir o esquema.

Isto possibilita sua manipulação utilizando os mesmos princípios de um documento XML além de permitir a aplicação de regras de transformação definidas em XSLT (ler Nota DevMan 3). A Listagem 1 apresenta um exemplo de definição de esquema para o documento XML da Listagem 2. Os conceitos básicos que devem ser entendidos são os de elemento (element) e tipo complexo (complexType). A marcação element deve aparecer para cada elemento do documento XML. É nela que são definidas as possíveis propriedades de cada nó de um docu mento XML como: nome (name), tipo (type), atributos (attribute) e cardinalidade (minOccurs, maxOccurs e occurs). Tendo identificado os elementos, é preciso definir seu tipo. Este pode ser um tipo simples

XSLT: A XSLT (XML StyLesheeT) é uma linguagem para definição de regras de transformação em

documentos XML. Estas regras são definidas em folhas de estilo (do inglês stylesheet) seguindo a sintaxe da linguagem XML. O termo folha de estilo é utilizado pelo fato de que um dos principais papeis do XSLT é adicionar informações de apresentação a um documento XML através de sua transformação para uma linguagem orientada à apresentação como HTML, XHTML (eXtensible HTML) ou SVG (Scalable Vector Graphics). Estas transformações são executadas por um processador XSLT cuja responsabilidade é aplicar as regras definidas na folha de estilos aos nós correspondentes de um documento XML. Dentre as possíveis transformações, uma que vem ganhando importância e não está relacionada com formatação para apresentação são as de um documento XML para outro. O princípio de funcionamento do XSLT segue os seguintes passos: (1) um documento XML é enviado para o processador XSLT; (2) o processador XSLT utiliza um documento XSLT no qual estão presentes as informações sobre como deverá ser feita a conversão do documento; (3) a partir destas informações, um novo documento é gerado pelo processador.

Assim, para o documento da Listagem 1 temos o seguinte entendimento. Existe um elemento raiz (curriculo) do tipo complexo que deve ter como filho um elemento dadosPessoais. Este por sua vez, é também do tipo complexo e é composto por uma sequência de filhos cujos nomes das marcações são nome (do tipo string), dataNascimento (do tipo date), nacionalidade (do tipo string) e endereco (de tipo complexo). Este último pode ocorrer várias vezes (maxOccurs). Mais abaixo se encontra a definição do elemento endereco o qual possui o atributo tipo e uma sequência de elementos (rua, complemento, bairro, cidade, estado e país) do tipo string. Existem vários editores que facilitam a construção de documentos XML Schema Definition (XSD). Neste artigo fizemos uso do Microsoft Visual Studio. Fica a cargo do leitor escolher um editor que mais se adeque às suas necessidades.

Padrões de Validação Existem diferentes possibilidades para definirmos a estrutura de um documento XML. Para fins de conhecimento, descreveremos abaixo os padrões existentes, porém, não são mais usados: • DTD (Document Type Definition): Antecessor do XML Schema Definition, foi o primeiro padrão de validação de documentos GML (Nota DevMan 4) e XML. Por se tratar de um padrão limitado caiu em desuso. Podemos citar como exemplo de limitações a falta de suporte a namespaces, o tratamento de dados como texto, dentro outras; • XDR (Xml Data Reducted): Desenvolvido pela Microsoft com o objetivo de suprir as carências apresentadas pelo padrão DTD. Mesmo sendo lançado no mercado pouco tempo antes do XSD e apresentando diversas semelhanças com o mesmo, não teve grande aceitação do mercado. Inclusive, a própria Microsoft indica o uso do padrão aberto XSD; Edição 101 SQL Magazine 31 •

Validando documentos XML através do XML Schema Definition

• XSD (Xml Schema Definition): Padrão recomendado pela W3C (World Wide Web Consortium), orgão responsável por criar e manter padronizações na WEB. O XSD veio como forma de contornar as limitações do DTD e teve uma forte aceitação no mercado. Várias linguagens de programação desde então vêm incorporando funcionalidades que suportam o XSD em seus produtos. A estrutura básica de um documento XSD é apresentada na Listagem 3. Observe que é obrigatório o uso do elemento “schema” como forma de declarar que o documento em questão é um XSD. Listagem 1. Exemplo de XML Schema

Listagem 2. Documento XML

Nota do DevMan 4 GML: O GML, acrônimo de General Markup Language, foi uma solução para armazenamento de

informações desenvolvido pela IBM em meados dos anos 70.

Listagem 3. Estrutura básica XSD

Este artigo será totalmente baseado no padrão aberto XML Schema Definition.

Além da boa formação Um arquivo de validação baseado no padrão XML Schema Definition é em suma um arquivo XML. Portanto, a grande vantagem é que não será necessário que o desenvolvedor despenda tempo para aprender uma nova linguagem de programação, pois os documentos XSD serão desenvolvidos com a sintaxe XML. Como se sabe, os documentos XML devem obedecer algumas regras básicas para serem considerados bem formados (ver Tabela 1), porém quando um documento XSD é referenciado como agente validador da estrutura deste documento XML, ele terá de ser além de bem formado, um documento “tipado” (typed XML), ou seja, ele deverá obedecer a estrutura XSD que a ele foi referenciada. Quando um documento não segue tais estruturas é dito que este é um documento “não tipado” (untyped XML). • O documento XML deve começar com uma declaração XML:

“”. • Existe diferenciação entre caracteres maiúsculos e minúsculos (case-sensitive). • Todas as tags que foram abertas devem ser fechadas; • Elementos vazios podem ser especificados através de uma barra no final da

declaração da tag. Por exemplo: ; • A hierarquia deve ser respeitada. Os elementos que foram abertos devem ser

Fabio Nascimento 22/07/1973 Brasileira Candido Mendes Centro São Paulo SP Brasil Centro São Paulo SP Brasil


fechados na ordem adequada. • Atributos devem ser envoltos com aspas duplas. Atributo = “Texto”.

Tabela 1. Regras dos documentos bem formados

Namespaces Namespaces são containers para utilização de nomes. Como já se sabe, o XML é um padrão extremamente flexível, sendo comum encontrarmos tags com o mesmo nome, mas que signifiquem coisas diferentes e sejam formadas por tipos de dados diferentes. Para solucionar este problema de conflito nos nomes das tags usamos os namespaces. Por recomendação da W3C os namespaces devem ser definidos através de uma URL. Esta declaração serve apenas como demonstração de unicidade do namespace, e não necessariamente deve existir. Um exemplo de documento XSD com o uso de namespace é apresentando na Listagem 4.

Para definir o nome do namespace que será usado, declaramos o atributo “xmlns”. Como é possível declarar mais que namespace por documento XSD, é comum fazermos o uso de prefixos, que atuam como alias do namespace, podendo apenas um namespace não possuir esse alias declarado explicitamente, e a este damos o nome de namespace default. Em todos os exemplos nosso prefixo será o “xs”. No SQL Server fazemos uso de algumas views de sistema para visualizar quais os namespaces existentes e quais os tipos fazem parte de seu escopo, bem como outras informaçõs relativas ao XML Schema. A query necessária para obter estas informações é apresentanda na Listagem 5.

Declarando elementos Em um documento XML Schema a declaração de elementos é feita através da tag “element”. Esta tag possui alguns atributos que podem ser usados em conjunto com a tag. • name: nome do elemento; • type: tipo de dados que o elemento irá armazenar; • minOccurs/MaxOccurs: Número mínimo e máximo, respectiva-

mente, de vezes que será permitida a declaração deste elemento. Quando não houver um número máximo utilizaremos o parâmetro “unbounded”. • Um exemplo de declaração de elemento é apresentado na Listagem 6.

Listagem 4. Exemplo Namespace XSD

Listagem 5. Views de sistema para listar namespaces e tipos de dados

SELECT XSN.NAME AS “NAMESPACE”, XST.NAME AS DADP, XST.DERIVATION_DESC AS DEVIVACAO, XST.KIND_DESC AS TIPO FROM SYS.XML_SCHEMA_NAMESPACES XSN INNER JOIN SYS.XML_SCHEMA_TYPES XST ON XSN.XML_COLLECTION_ID = XST.XML_COLLECTION_ID AND XSN.XML_NAMESPACE_ID= XST.XML_NAMESPACE_ID WHERE XSN.NAME = ‘http://www.w3.org/2001/XMLSchema’ Listagem 6. Declaração de elementos e atributos

“attribute”. Ela também possui alguns atributos que podem ser declarados em conjunto com a tag: • name: nome do atributo; • type: tipo de dados que o atributo irá armazenar; • use: forma de utili zação do atributo. Ao declarar um atributo

ele poderá ter seu uso obrigatório (required), opcional (optional) ou proibido; Um exemplo de declaração de atributo é apresentado na Listagem 6.

Elementos complexos Comumente torna-se necessário criar elementos compostos, ou seja, elementos declarados de forma hierárquica. Para que esse tipo de declaração possa ser feita, faremos o uso da tag “complexType”, que determina que o elemento será composto por outros. Para que este tipo de elemento seja declarado, também será necessário o uso de um dos indicadores de ordem. Um exemplo da estrutura básica do documento usando o complexType (Listagem 7). Indicadores de Ordem Indicadores de ordem são usados para explicitar de que maneira os elementos deverão ser declarados no documento XML que será validado. Para isto, contamos com três conectores de ordem: • sequence: Todos os elementos envoltos por este conector deverão ser declarados exatamente na mesma ordem que foram criados no documento XSD; • choice: Somente um dos elementos envolto por este conector poderá ser escolhido; • all: Não importa a ordem que os elementos se encontrem no arquivo XML, contanto que todos os elementos envoltos por este conector sejam declarados. Um exemplo de cada uma destes conectores é apresentado na Listagem 8. Perceba que inicialmente definimos que deveremos escolher entre preencher CPF ou CNPJ. É exatamente isso que fazemos ao utilizar o choice neste exemplo. Em seguida, definimos que independente da ordem em que sejam definidos, os elementos rua e numero deverão estar presentes no arquivo através do conector all. Por fim, fazendo uso do conector sequence , definimos que as informações nome e sobrenome deverão ser preenchidas, e preenchidas nesta sequência.

Tipos de dados Como já sabemos, umas das grandes vantagens do xml schema sobre seu antecessor é a possibilidade de atribuir o tipo de dado que cada elemento/atributo deverá receber. Basicamente, existem dois tipos de dados que devemos conhecer: Primitivos (primitive datatypes): Os tipos de dados primitivos são aqueles que estão presentes na maior parte das linguagens de programação. Uma listagem dos principais tipos de dados primitivos é mostrada na Tabela 2. •

Declarando atributos Em um documento XML também existe a possibilidade de se trabalhar com atributos que são declarados através da tag



Tipo String Boolean Decimal Float Double Time Date dateTime

Descrição Usado para representar uma cadeia de caracteres. Usado para representar um valor booleano (Verdadeiro ou Falso) Usado para representar um valor decimal. Usado para representar um valor de ponto flutuante, usando 32 bits. Usado para representar um valor de ponto flutuante, usando 64 bits. Usado para representar um horário específico em um dia qualquer (HH:MM:SS). Poderá usar o fuso horário de forma opcional. Usado para representar uma data no calendário gregoriano. YYYY-MM-DD. Poderá usar o fuso horário de forma opcional. Usado para representar um instante específico no calendário gregoriano. YYY Y-MM-DDTHH:MM:SS, onde “T” atua como separador entre a data e o horário. Poderá usar o fuso horário de forma opcional.

Tabela 2. Dados Primitivos Faceta Length minLength maxLength Pattern Enumeration totalDigits fractionDigits whiteSpace minInclusive minExclusive maxInclusive maxExclusive

Descrição Comprimento de um dado. Número mínimo de caracteres aceito. Número máximo de caracteres aceito. Possibilita o uso de expressões regulares. Define uma lista de valores possíveis a serem atribuídos. Número máximo de dígitos aceitos, contando a parte decimal. Número máximo de casas decimais permitidas. Espaços em branco. Valor mínimo aceito, considerando o valor explicitado. Valor mínimo aceito, desconsiderando o valor explicitado. Valor máximo aceito, considerando o valor explicitado. Valor máximo aceito, desconsiderando o explicitado.

Tabela 3. Principais facetas de restrição Listagem 7. Declaração básica de um elemento complexo

Listagem 8. Uso dos indicadores de ordem


• Derivados: Uma das características do XSD é a possibilidade de

criar tipos de dados, ou seja, expandi-los para que atendam suas necessidades. A esta categoria chamamos dados derivados. Abaixo temos uma explicação dos dois principais tipos de derivação: Restrição e Extensão, esse que por sua vez se divide em extensão por lista e extensão por união. Vejamos a seguir: - Restrição: Uma série de restrições pode ser feitas nos tipos de dados primitivos, possibilitando assim, a criação de novos tipos que serão usados para fins específicos. As restrições são feitas através das chamadas facetas de restrição , que nada mais são que atributos pré-existentes que restringem de algum modo o valor que será considerado válido no atributo/elemento. Para fazer uso das facetas de restrição devemos especificar um dado do tipo primitivo que servirá como base para o novo dado. Para isso utilizaremos o elemento “restriction” e o atributo “base”. Uma lista das principais facetas de restrição é apresentada na Tabela 3. Na Listagem 9 é apresentado um exemplo do uso das principais facetas de restrição. Nesta listagem trabalhamos com enumeração, tamanho mínimo e máximo de caracteres aceitos, formato de número válido, restrição de casas decimais e definição de valor inteiro positivo. Perceba que inicialmente definimos que o tipo estado poderá assumir um dos seguintes valores: RJ, SP ou MG. Depois disto, indicamos os tamanhos mínimo (2) e máximo (10) que o tipo nome deverá possuir. Dando prosseguimento, restringimos o formato do número de telefone válido. Ainda nesta listagem, ainda definimos que o tipo valor aceitará casas decimais sendo que os dígitos fracionais não poderão exceder duas casas decimais. Por fim, definimos o tipo intPositivo que somente poderá receber valores de 0 para cima. - Extensão por lista: Semelhante à utilização de um vetor em linguagens de programação, permite criar uma lista de variáveis de um determinado tipo. Para isto, utilizaremos o elemento “list” e o atributo “itemType”. Um exemplo de seu uso é apresentado na Listagem 10. Nesta listagem definimos um vetor de dados do tipo data. - Extensão por união: Cria um novo tipo de dado, usando como fator restritivo os valores aceitos por seu tipo base. Para isto, utilizaremos o elemento “membersTypes” e o atributo “union”. Um exemplo é apresentando na Listagem 11. Nele a tag “produto” considera como válido dados do tipo inteiro, que seria um código do produto; ou do tipo string, que representando o nome do produto.

Listagem 9. Exemplo extensão por restrição

SQL Server - XML Schema Collection Com o tempo o XML deixou de ser visto apenas como uma camada de transporte de informações, mas também passou a ser utilizado como uma camada de persistência de dados, tanto que os maiores players de bancos de dados como Oracle, IBM e Microsoft logo passaram a fornecer suporte a este padrão em suas ferramentas. Para que possamos armazenar os dados no formato XML de forma consistente, devemos utilizar um mecanismo de validação que garanta tal solidez. O mecanismo em questão, tema deste artigo, já foi explicado e conceituado. Um XML Schema collection nada mais é que o nome dado a um documento XML Schema armazenado diretamente em banco e dados SQL Server, e disponível para ser utilizado quando se precisa persistir documentos/fragmentes XML em tabelas ou variáveis. Um exemplo de criação de um XML Schema Collection é demonstrado na Listagem 12. Listagem 12. Exemplo XML Schema Collection CREATE XML SCHEMA COLLECTION A AS ‘ ’

Tutorial

Listagem 10. Exemplo extensão por lista 1989-01-01 1990-12-10 2012-04-23 Listagem 11. Exemplo extensão por união ’ 10 Revista SQL Magazine

Aplicando a teoria Para fixarmos melhor todos esses novos termos, siglas e funções do universo XML Schema, vamos aplicá-los para solucionar um problema real. Cenário: Você acaba de ser contratado para trabalhar na área de integração de uma conceituada software house. Esta é uma área nova na empresa e você tem a oportu nidade de mostrar suas habilidades recém adquiridas. Como projeto piloto, a empresa realizará uma integração entre seu software de mala direta e um software de comércio eletrônico. A princípio, apenas as informações sobre os clientes serão integradas. As informações serão transmitidas através de documentos XML, e cabe a você elaborar o XML Schema para que essa troca de informações possa ser realizada da maneira correta. Vamos definir alguns padrões que o documentos com os dados dos clientes deverá seguir: 1. O nome e sobrenome poderão ter até 30 caracteres; 2. O telefone devera estar no formato XXXX-XXXX; 3. Somente poderá ser declarado um tipo de documento, ou CPF ou RG; Edição 101 SQL Magazine 35 •


4. Não serão aceitas pessoas com data de nascimento inferior a 01/01/1900; 5. Não é obrigatório fornecer e-mail; 6. Pode-se ter N e-mails; 7. Todas as tags devem obedecer à ordem de criação do XSD; 8. O valor gasto na última compra do cliente deve possuir duas casas decimais depois da vírgula e ser maior que zero. Para este exemplo utilizaremos o Microsoft SQL Server 2008 e suas funções nativas para criação, validação e armazenamento dos documentos XML Schema.

presente na Listagem 13. Execute todo o script para o ambiente possa ser criado. Perceba no script que inicialmente criamos um tipo de dados chamado “nomeRestrito” onde o número máximo de caracteres aceito é trinta. Depois, criamos um tipo de dados “telefone”, que faz uso de expressões regulares para estabelecer o formato adequado que o número de telefone deverá seguir, no caso XXXX-XXXX. Nosso próximo passo é criar u m tipo de dados “dataNasc” onde a data mínima aceita é 01/01/1990. Dando sequencia a nosso esquema, criamos um tipo de dados “valorGasto”. Nele são aceitos apenas valores maiores ou iguais a zero e com duas casas decimais. Feito isto, criamos um elemento raiz do tipo complexo “dadosCliente”, e todos os elementos dentro dele devem obedecer a sequencia na qual foram criados. Depois disto,

XSD de validaçao O XML Schema que valida todas as nossas regras de negócio está Listagem 13. Modelo de validação das regras de negócio

CREATE DATABASE XSD_SQL_MAGAZINE GO USE XSD_SQL_MAGAZINE GO CREATE XML SCHEMA COLLECTION SQL_Magazine AS ‘

’


usamos o tipo de dados nomeRestrito para restringir os possíveis valores atribuídos ao elemento “nome” e usamos o tipo de dados telefone para restringir o formato do valor atribuído ao elemento “telefone”. Ainda neste esquema, criamos um elemento complexo chamado “documento”, que somente aceitará um dos dois elementos envoltos pelas tags “xs:choice”. Neste caso, apenas será aceito que seja declarado o elemento CPF ou RG. Caso ambos sejam declarados um erro será gerado. Este trecho define também que é obrigatória a declaração do Elemento (minOccurs e MaxOccurs). Por fim, usamos o tipo de dados dataNasc para restringir o a data que será atribuída ao elemento dataNascimento. Além disso, definimos que será permitido que sejam declarados nenhum ou infinitos elementos do tipo email (maxOccurs = unbounded) e, usamos o tipo de dados “valorGasto” que não permite que valores negativos sejam atribuídos ao elemento e que o valor deva possuir duas casas decimais.

Referenciando o XSD no XML No SQL Server possuímos um tipo de dados nativo para trabalhar com docu mentos XML. Vamos fazer uso deste t ipo de dado para nos ajudar com a validação dos documentos XML. Na Listagem 14 é criada uma variável do tipo XML que irá receber o conteúdo de um documento e o validará. A validação se dará através da explicitação do nome do XML Schema Collection após a declaração do tipo de dados XML e entre parênteses. O documento XML que é inserido na variável segue os padrões do XSD, portanto, é válido. Listagem 14. Declaração do tipo de dados XML

declare @xml xml(SQL_Magazine)= ‘ Dhiego Piroto 9876-5432 12345678 1989-01-11 [email protected] [email protected] 1000.01 ’

Forçando erros Até aqui tudo bem, estamos pressupondo que o mundo é perfeito e que nunca o cliente irá enviar o XML no formato incorreto. Para nos habituarmos com possíveis mensagens de erro, a Listagem 15 declarara alguns elementos que violam as regras contidas no Xml Schema. A primeira variável declarada (“@xml_Negativo”) recebe um documento XML que atribui ao elemento “ultimaCompra” um valor negativo. Vale lembrar que a este elemento é atr ibuído um

dado do tipo “valorGasto” que dentre outras restrições aceita zero como sendo seu mínimo. Por existir violação das restrições do dado um erro é gerado. A segunda variável declarada (@xml_Telefone) não respeita o padrão imposto pela expressão regular atribuída ao t ipo de dados “telefone”, em que o número do telefone deve seguir o formato XXXX-XXXX. Portanto, um erro é gerado. Listagem 15. Forçando Erros declare @xml xml(SQL_Magazine )= ‘ Dhiego Piroto 9876-5432 12345678 1989-01-11 [email protected] [email protected] -1000.01 ’ declare @xml xml(SQL_Magazine )= ‘ Dhiego Piroto (11)9876-5432 12345678 1989-01-11 [email protected] [email protected] 1000.01 ’ declare @xml xml(SQL_Magazine )= ‘ Dhiego Piroto 9876-5432 12345678 987654567 1989-01-11 [email protected] [email protected] 1000.01 ’

Figura 1. Mensagens de erro de validação



A terceira variável (@xml_Documentos) viola o indicador de ordem “choice”, que exige que apenas um único tipo de documento seja declarado (CPF ou RG). Como ambos elementos são declarados um erro é gerado. Os erros gerados são apresentados na Figura 1.

Conclusão Neste artigo foram abordadas as principais funcionalidades do XML schema, suas aplicabilidades e vantagens. Ainda existem muitas outras características que não puderam ser abordadas, como por exemplo: herança, constraints (unique, primary key, foreign key) dentre outras que nos permitirão construir mecanismos de validação de documentos XML altamente complexos e poderosos. Por se tratar de uma tecnologia altamente utilizada no mercado, torna-se obrigatório, por parte do profissional de banco de dados, um conhecimento mínimo acerca dos recursos fornecidos pelo XML schema. Para quem se interessar em saber mais sobre este importantíssimo recurso de validação de arquivos, sugiro que faça uma leitura no conteúdo disponibilizado através dos links de referência.

Dhiego Piroto

e-mail: [email protected] twitter: @dhiegopiroto

Graduando em Sistemas de Informação pela Universidade Paulista - Tatuapé. Atua há quatro anos na área de TI, apoiado na tecnologia de banco de dados SQL Server 2005,2008 e 2008 R2. Possui o título de MCP e MCTS Microsoft SQL Server e é membro ativo da comunidade MSDN SQL Server. Rodrigo Oliveira Spínola

[email protected]

Editor Chefe – SQL Magazine | WebMobile | Engenharia de Software Magazine

W3C: Site oficial do World Wide Web Consortium

http://www.w3.org/standards/xml/ PlugMaster

http://www.plugmasters.com.br/sys/colunistas/145/Gustavo-Maia-Aguiar XML – Editora: Novatec – ISBN: 8585184868 XML Schema


http://www.w3.org/XML/Schema

A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista!

XML Schema

Dê seu voto sobre este artigo, através do link:

Riscos - Engenharia de Software: Um framework para gestão de riscos em projetos de software – ISBN: 9788573937855 – Marcelo Nogueira

www.devmedia.com.br/sqlmagazine/feedback


http://www.w3schools.com/schema/schema_intro.asp



NESTA SEÇÃO VOCÊ ENCONTRA ARTIGOS BANCO DE DADOS, SQL OU PERSISTÊNCIA

Obtendo melhor desempenho no servidor SQL Server Melhores práticas na configuração de servidores de bancos de dados usando SQL Server

U

ma das situações mais corriqueiras na vida de um DBA é a absorção de um novo servidor para seu ambiente de administração. Este momento pode ocorrer quando o profissional inicia um novo tra balho em uma nova empresa, onde ele precisa conhecer e absorver a responsabilidade sobre todo o ambiente da organização e entender quais as atividades realizadas dentro dele (atividades de negócio ou mesmo tarefas administrativas). Outro momento em que esta situação pode ocorrer é durante uma expansão do parque tec nológico de servidores da empresa, causada por aquisições ou implantações de novas máquinas. Os novos servidores podem ser entregues para que o próprio DBA os ajuste conforme suas necessidades ou já venham previamente configurados pelo fornecedor, deixando assim a cargo do DBA a tarefa de verificação das configurações já existentes, visando evitar qualquer ajuste indevido, que possa degradar o desempenho, a segurança ou a disponibilidade do seu ambiente. Em ambas as situações o profissional precisa manter seu enfoque na exploração do ambiente ou daquele servidor em específico em busca da identificação de pontos de melhoria e, principalmente, na descoberta de quais melhores práticas são passíveis de aplicação naquele cenário. O DBA ainda precisa se atentar a questões do sistema operacional, pois mesmo com o SQL Server possuindo um sistema de gerenciamento independente, como o SQLOS, ainda sofre inf luência dos ajustes realizados no Windows. Um ambiente de banco de dados é um sistema complexo, composto por quatro elementos que interagem 40 SQL Magazine Edição 101 •

Resumo DevMan De que se trata o artigo: O artigo aborda as melhores práticas aplicadas às configurações do sistema operacional Windows Server para que este forneça o melhor desempenho possível a uma plataforma de banco de dados implantada através do SQL Server.

Em que situação o tema é útil: Este artigo é útil aos prof issionais, sejam eles responsáveis pelos bancos de dados ou mesmo pelos sistemas operacionais, que lidam com a configuração e implantação de ambientes de banco de dados nas plataformas Windows Server e SQL Server.

Obtendo melhor desempenho no servidor SQL Server: O artigo apresentará algumas das melhores práticas a serem aplicadas nas configurações de processador, memória, redes e disco junto ao sistema operacional para que este possa fornecer à camada de banco de dados todos os recursos presentes no ambiente de forma que atinjam o mais alto desempenho. Estes ajustes são focados nas configurações do sistema operacional Windows Server, responsável por suportar a plataforma de banco de dados SQL Server, deixando-o apto a disponibilizar toda a condição para que o ambiente atenda às necessidades de negócio da organização.

entre si e influenciam no desempenho um do outro, fazendo com que cada elemento tenha sua própria importância. Este complexo sistema é composto pelo ambiente de banco de dados, pelos Jobs de manutenção das bases de dados, pela aplicação cliente que as

acessa e pelas próprias bases de dados. Cada um destes componentes possui suas características e melhores práticas relacionadas, sendo possível aplicar inúmeras técnicas para aperfeiçoar o desempenho do sistema. Podemos ilustrar a dependência entre estes elementos por meio da Figura 1.

MER). Uma modelagem adequada e bem constituída, respeitando as normas e melhores práticas, pode ser a chave principal para o bom funcionamento de uma aplicação de banco de dados, assim como uma modelagem inadequada pode prejudicar todo o ciclo de vida daquele sistema. O SQL Server traz consigo diversos recursos que podem influenciar tanto positivamente quanto negativamente no ambiente como um todo. Tecnologias como particionamento de tabelas, disponível a partir da versão 2005, e os índices filtrados, que chegaram com a versão 2008, podem ajudar no desempenho do banco de dados se forem bem empregados. Além dos recursos da própria plataforma, algumas técnicas como a divisão dos dados e índices em diferentes filegroups podem ser muito úteis caso sejam implantadas em paralelo com a separação dos discos e com suas respectivas melhores práticas. Neste artigo, analisaremos algumas das melhores práticas junto ao primeiro componente deste complexo sistema: o ambiente de banco de dados. Iremos abordar as melhores práticas relacionadas à configuração de um servidor de banco de dados, apresentando alguns conceitos do sistema operacional e do próprio SQL Server, explicando algumas configurações a serem realizadas visando melhor desempenho.

Figura 1. Os quatro elementos de um sistema de banco de dados

O ambiente de banco de dados refere-se ao sistema operacional, ao sistema gerenciador de banco de dados e aos recursos de hardware presentes no ambiente, tal como dispositivos de armazenamento, configuração dos discos, quantidade e tipo de processadores, quantidade e velocidade da memória, entre outras características que dão suporte ao funcionamento de toda a plataforma de banco de dados. Os Jobs de manutenção são processos executados ou agendados pelo DBA com o objetivo de manter o desempen ho do banco de dados em um nível compatível com as exigências de negócio da organização. Visam também garantir que as bases de dados estejam sempre em funcionamento adequado e operando em um estado íntegro, ou seja, que não estejam apresentando falhas nos níveis lógicos e físicos da plataforma de banco de dados. A influência das aplicações cliente ocorre por meio dos códigos utilizados nelas para conexão e manipulação dos dados provindos das bases de dados. A qualidade empregada no desenvolvimento e a aplicação das melhores técnicas e práticas dentro destes códigos pode fazer a diferença no desempenho final do processo realizado pelo usuário, quando o dado é exibido em formato de informação. Quando abordamos as bases de dados propriamente ditas, falamos sobre as estruturas das tabelas, índices e recu rsos adicionais da plataforma de banco de dados (em nosso caso, o SQL Server) para a criação e organização interna dos dados. Qualquer base de dados possui seu desempenho diretamente ligado ao seu design, ou seja, à sua modelagem entidade-relacionamento (a famosa

Tutorial Configurando o ambiente Assim que um ambiente é concebido ou absorvido, o DBA deve realizar um breve check-up de algumas configurações importantes que afetam o desempenho do SQL Server. Parte destas configurações, presentes no sistema operacional, alteram alguns comportamentos do ambiente como um todo e assim como qualquer configuração possível dentro de um sistema, possuem suas melhores práticas, as quais serão abordadas aqui. Antes de passarmos por estas configurações, devemos sempre nos atentar às considerações de disponibilidade do ambiente, pois alguns dos ajustes aqui apresentados exigem uma reinicialização do sistema operacional para que seja m efetivamente aplicados. Discos rígidos De todos os elementos de hardware envolvidos em um ambiente de banco de dados, não é segredo algum que os discos rígidos sejam, normalmente, os mais lentos e responsáveis pela maioria dos gargalos existentes dentro de um sistema computacional. Devido a isso, existem diversas estratégias de configuração e práticas recomendadas para que se possa obter o máximo de desempenho do subsistema de disco, evitando que haja qualquer prejuízo ao desempenho do ambiente como um todo. Edição 101 SQL Magazine 41 •

Obtendo melhor desempenho no servidor SQL Server

Tão grande a quantidade de equipamentos envolvidos junto ao subsistema de disco (HBA, Switches, Bandas de rede, Cache das controladoras e dos discos, etc.) e tal o tamanho da importância e responsabilidade sobre uma configuração otimizada em um ambiente de banco de dados, que para muitos, realizar esta atividade de configuração adequadamente pode ser considerada uma ciência, ou mesmo uma arte. Com a utilização de Redes de Área de Armazenamento , o famoso Storage (também conhecido como SAN, sigla para Storage Area Network), os discos ganharam maior flexibilidade de configuração e, consequentemente, mais possibilidades de aumento de desempenho. Uma das principais tecnologias para se obter aumento de segurança e desempenho nos discos rígidos é o RAID, o qual se baseia na configuração de discos para funcionarem em paralelo, dividindo assim a carga de escrita e leitura entre eles. Além das recomendações de RAID, as quais não fazem parte do escopo deste artigo, podemos citar duas configurações importantes que muitas vezes passam despercebidas nos ambientes de bancos de dados: cluster size e alinhamento das partições de disco. Uma configuração equivocada relacionada aos discos rígidos pode causar sensível perda de desempenho, e mesmo um administrador experiente pode dar menos importância do que deveria a estas opções. É frequentemente comentado que o ajuste delas é mais importante para servidores que executam o Microsoft Exchange, mas a realidade é que o fino ajuste dos disc os rígidos, indiferentemente da configuração a ser aplicada, é essencial a qualquer ambiente que exija alto desempenho.

Cluster Size (Partição de dados) O Cluster Size , ou Unidade de Alocação de Arquivo, é o tamanho do bloco de escrita e leitura lógica que uma determinada u nidade de disco irá gerenciar. Por padrão, quando criamos uma LUN (ver Nota DevMan 1), é definido que as partições de dados da unidade possuirão tamanho equivalente a 4KB, o que para uma LUN dedicada ao armazenamento de arquivos é uma boa opção, porém, para servidores de bancos de dados que utilizam o SQL Server como SGBD, pode ser um problema.

O SQL Server opera internamente em leituras e escr itas baseadas em blocos de tamanho igual a 64KB, o que significa que quando a unidade de disco utiliza o cluster size com o tamanho diferente deste, o SQL Server pode enviar uma solicitação de escrita de 64KB e o sistema operacional pode não encontrar a quantidade necessária de blocos sequenciais para fazer a operação, o que resultaria na divisão do bloco lógico enviado pelo SQL Server em diversos blocos físicos espalhados pela LUN, causando assim a fragmentação do disco. Para evitar este problema, é recomendado que a LUN seja formatada usando o cluster size igual a 64KB. Desta forma, o SQL Server e o sistema operacional t rabalharão com o mesmo tamanho de bloco lógico, evitando que um bloco seja gravado em diversas áreas do disco, o que causaria fragmentação naquela unidade, aumentando o esforço do subsistema de disco em recuperar dados quando estes forem requisitados pelo SQL Server. Se o ambiente já estiver em produção e possuir su as unidades lógicas já configuradas com o Cluster Size padrão de 4KB, a única forma de realizar a correção é com a formatação destas unidades, o que nem sempre é possível devido à mobilização e indisponibilidade que isso causa junto ao servidor. Existem diversas formas de se verificar o Cluster Size de uma unidade lógica. Para simplificar, podemos utilizar a maneira mais simples e prática delas: através de uma consulta às propriedades de um arquivo qualquer gravado no disco a ser avaliado. Para verificar o tamanho do cluster size utilizando este método, basta criar um arquivo qualquer (neste exemplo, usaremos um txt) no disco em questão, preenchê-lo com a quantidade mínima de informação (uma letra ou palavra qualquer) e salvá-lo novamente. Agora você pode verificar o tamanho consumido em disco por aquele arquivo consultando suas propriedades. A Figura 2 exemplifica uma unidade lógica que está configurada com o cluster size igual a 4KB.

Nota do DevMan 1 Logical Unit Number: Logical Unit Number, ou número lógico de unidade, é o acrônimo para um

grupo de n discos físicos configurados para serem apresentados como uma única unidade lógica ao sistema operacional.

Utilizar um Cluster Size de 4KB para os arquivos de dados de um banco de dados pode aumentar a probabilidade de seu disco sofrer com problemas de fragmentação, o que fará com que seu desempenho seja reduzido gradativamente ao longo de sua utilização. 42 SQL Magazine Edição 101 •

Figura 2. Verificando a configuração do Cluster Size de uma unidade lógica

Reparem na propriedade Tamanho em disco. O valor apresentado para o arquivo é de 4KB, significando que o cluster size deste disco está configurado com este valor. Agora observemos através da Figura 3 outra unidade lógica, já formatada adequadamente com o Cluster Size igual a 64KB. Note que o arquivo gerado possui 64KB de espaço em disco, o que significa que ele ocupa um bloco inteiro de informação dentro daquela unidade lógica. Esta informação indica que o cluster size está configurado adequadamente, ou seja, equivalente a 64KB, para abrigar os arquivos de dados de um banco de dados SQL Server.

Alinhamento das Partições de Disco Uma importante configuração no subsistema de disco é o alinhamento das partições. Internamente, cada disco rígido é dividido em alguns componentes, tais como: pratos, trilhas, clusters e setores. A Figura 5 exibe uma visão simplificada dessa estrutura. Adicionalmente aos quatro componentes de um disco rígido presentes neste escopo, possuímos as faixas de dados, que correspondem à quantidade de dados destinada a cada disco dentro uma configuração RAID. Cada disco rígido traz consigo uma reserva inicia l de 63 setores que não podem ser sobrescritos. Estes setores são usados para o armazenamento de dados referentes à inicialização do próprio equipamento e são lidos todas as vezes que o sistema operacional estiver reconhecendo o próprio disco rígido, o que pode ocorrer quando ligamos ou reiniciamos o servidor.

Figura 3. Verificando a configuração do Cluster Size de uma unidade lógica

Para fazer esta modificação, devemos primeiro assegurar que não há dados gravados nesta unidade de disco, pois se houver eles serão perdidos com a formatação da unidade. Podemos realizar esta alteração através da interface de gerenciamento de discos, que pode ser acessada clicando com o botão direito em Meu Computador, em seguida escolhendo a opção Gerenciar e finalmente abrir a aba Gerenciamento de Discos , assim como a Figura 4 indica.

Figura 4. Interface de gerenciamento de discos

Figura 5. Composição de um disco rígido

Devido a esta reserva, quando iniciamos o armazenamento de nossos dados dentro um disco novo, estamos localizando-os a partir do primeiro setor disponível e subsequente a reserva dos 63 setores. Ao armazenar uma determinada quantidade de dados, suficiente para completar uma faixa de dados, iniciamos a utilização da segunda faixa disponível dentro daquele disco, e assim sucessivamente até completarmos o disco. Quando inserimos uma quantidade de dados em uma faixa, é possível que estes não completem inteiramente a faixa de dados onde estão sendo inseridos, deixando espaços livres para outras operações de escrita. Ao permitir que isso aconteça, podemos criar situações em que os dados que completarão aquela faixa não sejam do tamanho exato para o espaço que ficou disponível, fazendo com que o sistema operacional inicie sua gravação naquela faixa e termine em outro espaço do disco. Com os dados divididos entre duas faixas, o sistema operacional e o subsistema de disco serão forçados a realizar duas operações de escrita ou leitura cada vez que aquela inforEdição 101 SQL Magazine 43 •


mação for solicitada, duplicando assim o esforço do ambiente para manipular tais dados. Para solucionar este problema, podemos alinhar as partições de disco ao iniciar o uso de um novo dispositivo. Fazendo este ajuste, configuramos o sistema operacional para reservar uma quantidade adicional, múltipla de dois, aos dados de inicialização do disco rígido. Desta forma, ao invés de reservar 63 setores do disco, podemos solicitar que reserve um bloco inteiro, contendo 64 setores, o que irá corrigir a gravação de um mesmo bloco de dados em duas faixas diferentes. A Figura 6 ilustra como o armazenamento de dados é feito em uma partição. A primeira linha (azul) ilustra a unidade de alocação, equivalente a 64KB, enquanto as outras duas linhas exibem uma comparação entre uma partição não alinhada e uma partição alinhada, respectivamente.

Figura 6. Diferença entre discos alinhados e não-alinhados

Repare que na segunda linha, os primeiros 63 setores, marcados em amarelo, são utilizados pelo próprio disco, e o primeiro setor a ser usado pelos dados da aplicação in icia-se no 64º setor, o que faz com que o sistema operacional divida os demai s clusters de uma forma que não fiquem ali nhados com os blocos de leitura do SQL Server, causando uma leitura duplicada, pois para um mesmo cluster (quadrado em verde) é necessário dois blocos de leitura, ou seja, duas operações de I/O. Já na terceira linha, indicamos ao gerenciador de disco que devemos iniciar o novo cluster no 65º cluster, deixando um inutilizado (quadrado em cinza), assim forçando-o a alinhar os clusters junto aos blocos de leitura do SQL Server, evitando a necessidade de operações adicionais de I/O para a manipulação do mesmo grupo de dados. Para realizar esta configuração podemos empregar a ferramenta diskpart.exe , que faz a manipulação e a config uração dos discos dentro de um ambiente na plataforma Windows. Este utilitário pode ser usado para desde a cr iação de uma nova partição, sua formatação, até a associação dela a uma determinada letra para ser exibida no sistema operacional. Através do comando create partition primary align = VALOR_ EM_KB , que é executado dentro do programa diskpart.exe , é possível ajustar corretamente o alinhamento das partições, deixando-as na medida para evitar operações duplicadas de escrita e leitura. Esta operação duplicada pode prejudicar todo o subsistema de disco do ambiente e, consequentemente, todo o ambiente computacional, visto que para cada leitura requisitada pelo SQL Server, o disco pode ter de manipular dois setores, mesmo que o SQL Server tenha pedido informações equivalentes a apenas um setor. 44 SQL Magazine Edição 101 •

Configurações de Rede Dentre as configurações do Windows, podemos encontrar diversos ajustes relacionados às interfaces de rede, porém, alguns deles se sobressaem quando consideramos um servidor destinado a armazenar bancos de dados, pois possuem maior impacto na transmissão de dados. Com esta seção, iremos apresentar as melhores práticas e os cuidados necessários para se manipular estas configurações no Windows, visando o máximo de desempenho em um ambiente de banco de dados. Configurações de velocidade e fluxo das interfaces de rede Deve-se, sempre que possível, configurar manualmente a velocidade das placas de rede do ambiente. Grande parte das interfaces de rede possui uma configuração padrão associada a elas, o que nem sempre é adequado a um ambiente de banco de dados. Para um melhor aproveitamento dos recursos, deve-se verifica r as configurações das interfaces de rede e fazer os ajustes necessários conforme a velocidade da rede em que elas estão conectadas, levando-se em consideração os demais dispositivos (switch, HBAs, Roteadores, etc.). Este ajuste é realizado visando a garantia de que o equipamento funcione exatamente na banda de dados que é suportada entre os dispositivos que fazem a comunicação entre a rede da empresa e o servidor de bancos de dados. Para realizar este ajuste é necessário conheci mento a respeito da capacidade de transmissão dos componentes da rede, para que assim seja possível ajustar a interface de rede com o valor máximo para aqueles componentes como um todo, e não apenas para a interface de rede como um único equipamento da estrutura. Em paralelo à configuração da velocidade de transmissão dos dados, é recomendável a configuração do fluxo de informações que passam por aquela interface, deixando-a pronta para o funcionamento em ambos os sentidos e ajustando-a para que use toda sua capacidade conforme a demanda. Desta forma, se houver maior fluxo de entrada de informação, a interface de rede pode usar livremente sua capacidade para este recebimento, e caso haja maior fluxo de saída, ela pode aproveitar de todos os seus recursos para dar vazão a este envio, assim utilizando de forma flexível o equipamento. A Figura 7 ilustra a configuração adequada. Repare que existem as opções Half Duplex e Full Duplex. Para que seja utilizada toda a capacidade da interface de rede em ambos os sentidos, é necessário que seja configurada a opção Full , evitando assim que a interface de rede limite o tráfego de informação entre os dados que são enviados pela placa e aqueles que são recebidos por ela. Referente à velocidade, observe que na imagem são ilustradas duas configurações possíveis: 10Mbps e 100Mbps. Para qualquer am biente produtivo, é preciso que sejam observadas as especificações técnicas dos demais equipamentos da rede, tais como switches, hubs e roteadores. Com base em seus requisitos, podemos identificar qual a capacidade máxima comum entre estes dispositivos e assim configurá-los para que usem uma banda de rede uniforme.

Figura 7. Configuração de velocidade e fluxo das placas de rede

Gerenciamento de energia das interfaces de rede Uma importante configuração relacionada a uma interface de rede presente em um servidor, não se limitando a máquinas dedicadas a hospedagem de bancos de dados, é o gerencia mento de energia do equipamento. É altamente indicado que seja verificada a configuração de economia de energia de todos os componentes de um servidor, especialmente as interfaces de rede, que possuem configurações padrões diferenciadas, podendo estar ajustadas de forma que o sistema operacional as desligue em momentos de inatividade. Caso seja permitido o servidor desligar a placa de rede quando não houver uso dela, a interface ficará indisponível para as primeiras requisições que forem feitas após um período de inutilização do servidor. Estas primeiras solicitações servirão para alertar o dispositivo que seu funcionamento está sendo requisitado. Ao detectar a necessidade de utilização da placa de rede, o Windows inicializa-o para que possa receber e enviar dados novamente. Este restabelecimento pode demorar alguns instantes, pois o sistema operacional precisa verificar se os drivers ainda estão carregados e se caso não estiverem, precisa providenciar isso em memória, visando deixar tudo em pleno funcionamento antes de liberar as conexões. A Figura 8 mostra a configuração adequada. Esta pode ser encontrada nas propriedades da interface de rede. Observem na figura que esta opção já se encontra desabilitada, ou seja, corretamente configurada para um servidor de banco de dados. É recomendado que sejam verificadas todas as placas de rede do servidor, visando evitar que não apenas o banco de dados enfrente algum problema, mas sim o ambiente como um todo. Memória Algumas configurações do Windows influenciam na forma do sistema operacional trabalhar com a memória física presente no

Figura 8. Gerenciamento de energia da interface de rede

servidor. Através destes ajustes, podemos regular a prioridade de concessão de memória entre os programas aplicativos e o sistema operacional, assim como é possível ajustar o espaço disponível para alocar aqueles dados que estavam em memória e não estão sendo mais acessados. Através das próximas sessões, iremos apresentar duas con figurações relacionadas à memória que podem ser ajustadas com o objetivo de maximizar o desempenho do ambiente de banco de dados.

Configuração de privilégio no uso de memória Esta configuração não está mais presente no Windows Server 2008, mas como ainda é grande a utilização da versão 2003 do Windows Server, ainda deve ser mencionada. Esta opção altera o privilégio na utilização da memória presente no sistema operacional, concedendo maior preferência entre os elementos da própria plataforma e os programas de usuário, sejam estes de execução em modo background ou f ront-end. O ajuste desta configuração é realizado através de uma opção no próprio Windows e exige um restart do servidor para que seja aplicada. Existem duas opções disponíveis para o ajuste: “Programas” e “Cache de Sistema”. A primeira delas, Programas , prioriza a utilização de memória por parte dos programas instalados (entre eles, o SQL Server) e dá ao SGBD maior direito sobre o consumo de memória física do servidor. A segunda configuração, “Cache de Sistema”, faz com que o próprio Windows tenha privilégio sobre a utilização de memória, podendo consumir maior quantidade e adquirir memória mais rapidamente do que as demais aplicações. Edição 101 SQL Magazine 45 •


Embora configurar esta opção como “Programas” possa parecer um risco, visto que irá aumentar a prioridade do SQL Server (incluso nos programas de usuário) e reduzir este poder por parte do sistema operacional, ela não oferta riscos à saúde do sistema do servidor, pois o Windows Server possui f uncionalidades para gerenciar de forma eficiente este ajuste, visando não prejudicar as alocações internas e essenciais ao seu funcionamento. A opção de prioridade de utilização de memória pode ser verificada dando um clique com o botão direito no Meu Computador e depois um clique com o botão esquerdo em Propriedades. Quando a janela aparecer, deve-se procurar a aba Avançado , onde terá um botão chamado Configurações , sob a área intitulada de Desempenho. Clicando neste botão, uma nova janela aparecerá, onde você poderá acessar a segunda aba, chamada Avançado. Ao acessar o conteúdo desta aba, observe a segunda área de opções, chamada de Uso da Memória. Nela haverá duas caixas de marcação que definirão o ajuste desta configuração. A Figura 9 ilustra como devemos proceder.

Figura 9. Configuração de prioridade do uso de memória

Arquivo de paginação O arquivo de paginação é uma ferramenta do Windows para prevenção de problemas relacionados à memória física e uma forma de aliviar a utilização dela quando necessário. É usado pelo sistema operacional para descarregar dados que estão em memória e colocá-los no disco rígido para que o próprio SO ou demais aplicativos possam reutilizar aquele espaço na memória física para alocação de outros dados. A utilização do arquivo de paginação pelo sistema operacional, mesmo que para alívio da memória RAM, não é um comportamento desejado quando considerado o desempenho e, sempre que 46 SQL Magazine Edição 101 •

possível, deve ser evitado. No entanto, mesmo com seu prejuízo referente à performance do ambiente, mesmo que o servidor possua quantidade de memória RAM suficiente para suportar a carga submetida a ele, o recomendado é que seja configurado o arquivo de paginação objetivando a prevenção de qualquer possível problema relacionado à memória do servidor, tal como o estouro da memória (também conhecido pelo erro de “out of memory”). Um arquivo de paginação pequeno pode não ser suf iciente para prevenir sobrecargas de utilização da memória, assim como um arquivo muito extenso pode consumir grande espaço em disco e mascarar gargalos de memória no servidor. Para melhor configuração, existem duas boas práticas relacionadas ao arquivo de paginação: a primeira é relacionada à localização do arquivo; e a segunda é relacionada ao seu tamanho. A localização do arquivo de paginação deve ser cuidadosamente analisada, pois este arquivo, por padrão, é criado na mesma unidade onde o sistema operacional está instalado (normalmente, na unidade C). Esta configuração padrão infringe as boas práticas relacionadas ao armazenamento dos binários do sistema operacional e demais aplicativos, pois aumenta a movimentação dos dados dentro do conjunto de discos que abriga o Windows, acarretando em maior risco de falha no equipamento físico, o que causaria a indisponi bilidade do sistema operacional. A recomendação em torno de sua localização aponta para que seu a rmazenamento seja feito em um disco, de preferência, dedicado exclusivamente ao arquivo de paginação. Além de possibilitar a redução de riscos no ambiente, colocar este arquivo em um disco exclusivo pode mitigar suas limitações de desempenho, pois toda aquela unidade de disco estará destinada apenas a gerenciar as operações de escrita e leitura do arquivo de paginação. Quanto ao tamanho do arquivo de paginação, que por padrão, vem ajustado para ser flexível conforme as necessidades do sistema operacional, deve ser configurado manualmente. Deixar que o tamanho deste arquivo seja manuseado unicamente pelo Windows pode causar problemas de espaço em disco. Para evitar este problema, deve-se configurar o arquivo de paginação com valores iniciais e monitorá-lo por um breve período de tempo para que seja avaliada uma possível necessidade de aumento de seu tamanho ou mesmo de uma redução. Como configuração inicial, existem duas recomendações. Am bas baseadas na quantidade de memória RAM que o servidor possui. Se a máquina possuir menos de 8GB de memória RAM, é recomendada a configuração do arquivo de paginação com o dobro da quantidade de memória presente no servidor. A Figura 10 ilustra a configuração do Page File para um servidor que possui 4GB de memória RAM. Caso a máquina possua mais de 8GB de memória RAM, o recomendado é que o arquivo de paginação seja ajustado com o tamanho máximo de apenas 8GB; valor suficiente para possíveis necessidades iniciais do ambiente.

ciclo do processador. O quantum de um processador é a quantidade de microciclos que ele entrega sequencialmente a uma determinada requisição antes de esta ser novamente colocada na fila de processamento, liberando assim espaço para outra thread ser processada. Seguindo o mesmo caminho feito na configuração da prioridade de utilização da memória, podemos encontrar o local onde alteramos a configuração de agendamento do processador. Assim, na mesma tela de configuração de memória, sob a área “Agendamento do Processador” , encontramos duas opções: Programas e Serviços em Segundo Plano. A primeira delas configura a unidade lógica de processamento para funcionar com um quantum de 4 microciclos, e a segunda opção ajusta os processadores para funcionar com base em vinte microciclos. A Figura 11 ilustra onde a configuração pode ser ajustada.

Figura 10. Configuração de Paging File.

Para um ambiente já existente, o recomendado é que seja monitorado o arquivo de paginação antes que seja feita qualquer alteração no tamanho e localização do mesmo. Se após o acompanhamento do ambiente for determinada a necessidade de uma mudança, não podemos nos esquecer de tomar todos os cuidados necessários, tais como procedimentos de backup, planos de retorno caso aconteçam imprevistos e, claro, a obtenção e utilização de uma janela de indisponibilidade adequada para a realização desta mudança.

Figura 11. Configuração de agendamento de processadores

Processamento Assim como as configurações de memória, o sistema operacional também possui alguns ajustes relacionados aos processadores, onde podemos aplicar mudanças que trazem impactos ao desempenho do servidor. Estes ajustes definem pequenas alterações na operação interna do sistema operacional, pois alteram algumas particularidades de como os núcleos de processamento serão utilizados pelas requisições enviadas a eles. Com esta seção, apresentaremos a principal opção relacionada a processamento presente no sistema operacional Windows Server. Esta configuração causa impacto no modo de funcionamento do ambiente como um todo, pois altera como serão alocados os processos de usuário e de sistema dentro dos núcleos das CPUs.

Ajustar os processadores com a primeira opção fará com que eles fiquem ocupados com uma mesma thread por 4 unidades de tempo. Esta opção é recomendada para máquinas de usuários por estas possuírem diversos programas em execução de forma simultânea. Com este ajuste o processador pode trabalhar de forma mais dinâmica, atendendo a pequenas requisições de forma mais eficiente. Configurando esta opção para Background Services , indicamos ao sistema operacional para trabalhar com um quantum de 20 microciclos para os processadores, o que fará com que um mesmo processo seja executado mais rapidamente, porém, as demais threads ficarão mais tempo aguardando. Esta configuração é recomendada para servidores de bancos de dados, pois permite que o SQL Server util ize o poder de processamento do servidor de uma forma mais eficiente. Esta configuração também reduz a quantidade de trocas de contexto feita pelos processadores. Uma troca de contexto é a ação executada por um processador quando ele necessita descarregar uma thread de seu núcleo e alocar outra para processá-la. Este processo exige o carregamento de dados e instruções dentro dos registradores do CPU e consomem o tempo de execução deste.

Configuração de Agendamento do Processador Similar à opção de prioridade de memória, também existe uma opção do sistema operacional que ajusta o tamanho do quantum do

Conclusão Ter a devida consideração com cada elemento de hardware presente no ambiente e sua respectiva configuração através do Edição 101 SQL Magazine 47 •


sistema operacional traz ao administrador, seja dos bancos de dados ou do próprio servidor, a responsabilidade de verificar todos os possíveis pontos de melhoria dentro daquele ambiente e ajustar cada um deles com a melhor configuração possível. Para isso, o profissional necessita do conhecimento teórico, técnico e prático para entender, avaliar, justificar e aplicar as mudanças necessárias a uma determinada situação e para cada configu ração na qual ele pretende interferir. Tendo como base as melhores práticas divulgadas pelos fornecedores de software e hardware, pode-se iniciar uma configuração personalizada junto ao ambiente, preparando-o para hospedar o funcionamento da plataforma de banco de dados, neste caso, o SQL Server. Com as orientações aqui apresentadas, torna-se possível o ajuste das opções do sistema operacional referentes a processadores, memória, disco e rede de uma forma condizente com as recomendações da Microsoft, possibilitando que o SQL Server e todos os demais elementos da plataforma de dados usem todos os recursos do ambiente Windows e de hardware da melhor forma possível.

Felipe de Assis

[email protected]

Felipe de Assis é consultor e instrutor SQL Server em uma empresa especializada em soluções Microsoft, atua exclusivamente com SQL Server há mais de três anos e possui a certificação MCITP na administração das versões 2005 e 2008, além de ser MCTS no desenvolvimento de soluções de banco de dados usando a versão 2008. Blog: http://felipedba.wordpress.com. Livro: SQL Server 2008: The Bible, Editora: Wiley; 1º edition. Professional Microsoft SQL Server 2008 Administration, Editora: Wrox; 1º edition Disk Partition Alignment Best Practices for SQL Server

http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx Dê seu feedback sobre esta edição!





Novidades para desenvolvedores no Oracle 11g – Parte 2 Novidades da PL/SQL voltadas para desenvolvedores de Software ESTE ARTIGO FAZ PARTE DE UM CURSO

Resumo DevMan De que se trata o artigo:

P

L/SQL é uma extensão da linguagem padrão SQL para o SGBD Oracle da Oracle Corporation. É uma linguagem procedural da Oracle que estende a linguagem SQL. A PL/SQL permite que a manipulação de dados seja incluída em unidades de programas. Blocos de PL/SQL são passados e processados por uma PL/SQL Engine que pode estar dentro de uma ferramenta Oracle ou do Server. A PL/SQL Engine filtra os comandos SQL e manda individualmente o comando SQL para o SQL Statement Executor no Oracle Server, que processa o PL/ SQL com os dados retornados do Server. A unidade básica em PL/SQL é um bloco. Todos os programas em PL/SQL são compostos por blocos, que podem estar localizados uns dentro dos outros. Geralmente, cada bloco efetua uma ação lógica no programa. Desde seu lançamento comercial em 1979, a cada nova versão do banco de dados Oracle, comunidades de DBAs e desenvolvedores do mundo todo ficam antenados para conhecer os novos recursos que facilitam as tarefas de planejar e manter aplicações em ambientes complexos com necessidades crescentes de performance, escala e segurança.

Este artigo é a segunda e última parte de nossa série que demonstra de forma resumida as principais novidades das linguagens SQL e PL/SQL que surgiram na versão 11g do banco de dados voltadas principalmente para desenvolvedores, administradores ou operadores de dados que necessitam utilizar de for ma eficiente os novos recursos do Oracle.

Em que situação o tema é útil: Conhecer as novidades introduzidas na versão 11g do principal banco de dados do mercado torna-se u m diferencial a profissionais que desejam destaque e oportunidades no mercado de trabalho, além de uma reciclagem na preparação para provas de certificação na nova plataforma.

Novidades para desenvolvedores no Oracle 11g – Parte 2: O artigo destaca as novidades da linguagem SQL do Oracle 11g. Nesta segunda parte focamos em mais algumas novidades da PL/ SQL. As novidades ficam p or conta dos novos tipos de dados, novas packages, triggers compostas, facilidades na linguagem, ordenar a execução de triggers e a evolução d o compilador.

Neste contexto, neste artigo iremos analisar as principais novidades da linguagem PL/SQL introduzidas nas versões 11g e 11gR2 do banco de dados Oracle, voltadas principalmente para desenvolvedores e operadores que necessitam dominar os novos recursos visando alcançar soluções eficientes, flexíveis e seguras. Edição 101 SQL Magazine 49 •

Novidades para desenvolvedores no Oracle 11g – Parte 2

DBMS_SQL A package dbms_sql ganhou novos recursos na versão 11g, aumentando as opções para se criar comandos SQL em tempo de execução. Dentre as melhorias, podemos destacar o suporte a todos os data types tratados na instrução execute immediate e a possibilidade de utilizar comandos SQL de até 32kb como argumento no procedimento dbms_sql.parse(). Além disso, as novas funções dbms_sql. to_refcursor() e dbms_sql.to_cursor() podem ser empregadas para conversão entre o data type ref_cursor e cursores da dbms_sql , como demonstrado na Listagem 1. No exemplo, um cursor definido na dbms_sql foi convertido para o data type sys_refcursor , possibilitando o uso do tradicional fetch em um loop PL/SQL. Listagem 1. Exemplo das funções to_refcursor() e to_cursor_number().

SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE 2 v_ref_cursor sys_refcursor; 3 v_cursor number; 4 v_SQL clob := ‘SELECT * FROM EMPREGADO ORDER BY 1’; 5 v_ret number; 6 v_reg_emp empregado%rowtype; 7 BEGIN 8 v_cursor := DBMS_SQL.open_cursor; 9 10 DBMS_SQL.parse(v_cursor,v_SQL, DBMS_SQL.native); 11 12 v_ret := DBMS_SQL.execute(v_cursor); 13 14 v_ref_cursor := DBMS_SQL.to_refcursor(v_cursor); 15 16 loop 17 fetch v_ref_cursor into v_reg_emp; 18 exit when v_ref_cursor%NOTFOUND; 19 dbms_output.put_line(v_reg_emp.codigo||’‘||v_reg_emp.nome||’ ‘||v_reg_emp.cargo); 20 end loop; 21 close v_ref_cursor; 22 END; 23 / 2 FORD ANALYST 11 JAMES CLERK 21 WARD SALESMAN 34 MILLER CLERK 39 KING PRESIDENT

Listagem 2. Armazenando resultado de funções em cache.

SQL> CREATE OR REPLACE FUNCTION f_qt_linhas 2 return number 3 RESULT_CACHE 4 is 5 v_Result number; 6 begin 7 select sum(count(*)) 8 into v_result 9 from TESTE_2M_TAB 10 group by col1; 11 12 return(v_Result); 13 end; 14 / Função criada. SQL> --PRIMEIRA EXECUÇÃO PROCESSA SQL E ARMAZENA RESULTADO EM CACHE SQL> SET AUTOTRACE ON STATISTICS SQL> SET TIMING ON SQL> SELECT f_qt_linhas FROM DUAL; F_QT_LINHAS --------------------------------------------------------- 2000000 Decorrido: 00:00:01.47 Estatísticas ---------------------------------------------------------19 recursive calls 0 db block gets 8890 consistent gets 80 physical reads 0 redo size 433 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> --SEGUNDA EXECUÇÃO IRÁ UTILIZAR O RESULTADO JÁ EM CACHE SQL> SELECT f_qt_linhas FROM DUAL; F_QT_LINHAS --------------------------------------------------------- 2000000 Decorrido: 00:00:00.01

Procedimento PL/SQL concluído com sucesso.

PL/SQL Function Result Cache A versão 11g do Oracle permite agora uma cláusula na criação de funções que instrui o banco de dados a armazenar em cache o valor de retorno da função, evitando assim sua execução nas chamadas subsequentes. Essa nova funcionalidade promove grande ganho no desempenho de aplicações PL/SQL , como demonstrado na Listagem 2. Observe que fazemos uso do result cache e que por conta disso, o resultado da segunda execução do comando SQL acaba sendo muito mais rápido. Observe que o tempo utilizada nas duas execuções foi de 00:00:01.47 na primeira execução e 00:00:00.01 na segunda. 50 SQL Magazine Edição 101 •

Compound DML Triggers Compound DML Triggers é uma novidade que permite a criação de triggers compostas. Uma trigger composta pode conter código executável para todos os eventos possíveis de DML em um único programa, permitindo também o uso de variáveis globais, visíveis por todo o corpo da trigger. A Listagem 3 demonstra uma trigger composta, onde reutilizamos um procedimento local para todos os eventos de dml na tabela cliente. Execution Order of Triggers Antes da versão 11g não havia recurso possibilitando controlar a ordem de execução de triggers pertencentes a um mesmo evento de

disparo. Agora podemos definir a ordem de execução de dml trig gers através da cláusula FOLLOWS , como ilustrado na Listagem 4. Este código demonstra o controle na ordem de execução entre duas triggers before update de uma tabela. Perceba que ao definirmos a trigger TG_BU_CLIENTE_2 colocamos explicitamente que ela será executada depois da execução da trigger TG_BU_CLIENTE_1 conforme podemos observar na linha 4 da segunda trigger. Listagem 3. Exemplo de trigger composta.

SQL> SET SERVEROUTPUT ON SQL> SQL> CREATE OR REPLACE TRIGGER TG_COMPOSTA_CLIENTE 2 FOR UPDATE ON CLIENTE 3 COMPOUND TRIGGER 4 5 v_global_msg varchar2(100); 6 7 procedure ShowMsg 8 is 9 begin 10 DBMS_OUTPUT.PUT_LINE(v_Global_Msg); 11 end ShowMsg; 12 --------------------------------------------------------13 BEFORE STATEMENT 14 IS 15 BEGIN 16 v_Global_Msg := ‘BEFORE STATEMENT...’; 17 ShowMsg(); 18 END BEFORE STATEMENT; 19 --------------------------------------------------------20 BEFORE EACH ROW 21 IS 22 BEGIN 23 v_Global_Msg := ‘BEFORE EACH ROW...’; 24 ShowMsg(); 25 END BEFORE EACH ROW; 26 --------------------------------------------------------27 AFTER EACH ROW 28 IS 29 BEGIN 30 v_Global_Msg := ‘AFTER EACH ROW...’; 31 ShowMsg(); 32 END AFTER EACH ROW; 33 --------------------------------------------------------34 AFTER STATEMENT 35 IS 36 BEGIN 37 v_Global_Msg := ‘AFTER STATEMENT...’; 38 ShowMsg(); 39 END AFTER STATEMENT; 40 41 END TG_COMPOSTA_CLIENTE; 42 / Gatilho criado.

Creating Disabled Triggers Este é mais um diferencial na sintaxe de criação de triggers no Oracle 11g, possibilitando a criação de triggers que permanecerão desativadas até a sua alteração com o comando alter trigger enable. Esta técnica pode ser utilizada para minimizar o impacto nas aplicações em funcionamento, visto que a trigger não será executada até sua ativação. A Listagem 5 demonstra a criação de triggers desativadas. Observe que inicialmente definimos a trigger TG_BU_CLIENTE como sendo disable (linha 4). Na sequência, executamos alguns comandos SQL e percebemos que ela de fato não foi considerada. Feito isto, ativamos a trigger utilizando o comando ALTER TRIGGER TG_BU_CLIENTE ENABLE. Perceba que ao executarmos mais alguns comandos SQL, perceberemos também que a trigger será executada. Automatic Subprogram Inlining O compilador do Oracle 11g pode automaticamente realizar otimização em seu código PL/SQL utilizando a técnica de subprogram inlining , que consiste na substituição da chamada a subprogramas pela cópia de seu próprio código. Apesar de melhorar o desempenho da rotina, substituir chamadas de subprogramas por seu código vai contra a técnica de programação modular, que torna o código mais limpo e compreensível. Assim, através de uma instrução para o compilador, o Oracle 11g faz isso de forma transparente, alterando apenas o código compilado, sem que haja necessidade de alterar o fonte de seus procedimentos. Na Listagem 6 invocamos a otimização por subprogram inlining de duas formas, visando ganho de desempenho no programa. Listagem 4. Controlando a ordem de execução de triggers na versão 11g.

SQL> CREATE OR REPLACE TRIGGER TG_BU_CLIENTE_1 2 BEFORE UPDATE ON CLIENTE 3 FOR EACH ROW 4 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE(‘PRIMEIRO BEFORE UPDATE’); 7 END; 8/ Gatilho criado. SQL> CREATE OR REPLACE TRIGGER TG_BU_CLIENTE_2 2 BEFORE UPDATE ON CLIENTE 3 FOR EACH ROW 4 FOLLOWS TG_BU_CLIENTE_1 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE(‘SEGUNDO BEFORE UPDATE’); 7 END; 8 9/ Gatilho criado.

SQL> UPDATE cliente SET nome=’Arlindo Vieira’ WHERE codigo = 10; BEFORE STATEMENT... BEFORE EACH ROW... AFTER EACH ROW... AFTER STATEMENT...

SQL> SET SERVEROUTPUT ON SQL> SQL> UPDATE cliente SET nome = ‘Carolline Medeiros’ WHERE codigo=10; PRIMEIRO BEFORE UPDATE SEGUNDO BEFORE UPDATE

1 linha atualizada.

1 linha atualizada.


Novidades para desenvolvedores no Oracle 11g – Parte 2

Listagem 5. Uso da cláusula disable na criação de triggers

SQL> CREATE OR REPLACE TRIGGER TG_BU_CLIENTE 2 BEFORE UPDATE ON CLIENTE 3 FOR EACH ROW 4 DISABLE 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE(‘ESTOU ATIVO!’); 7 END; 8/

SQL> rollback; Rollback concluído. SQL> ALTER TRIGGER TG_BU_CLIENTE ENABLE; Gatilho alterado. SQL> UPDATE cliente SET estado =’MT’ WHERE codigo=10; ESTOU ATIVO!

Gatilho criado. SQL> SET SERVEROUTPUT ON SQL> UPDATE cliente SET estado =’MT’ WHERE codigo=10;

1 linha atualizada. SQL> commit;

1 linha atualizada. Commit concluído.

Listagem 6. Exemplo de subprogram inlining.

SQL> SET TIMING ON SQL> DECLARE 2 v_num PLS_INTEGER; 3 4 function soma(p1 number, p2 number) return number 5 is 6 begin 7 return(p1+p2); 8 end; 9 10 BEGIN 11 for i in 1..10000000 loop 12 v_num := soma(v_num, i); --não otimizado 13 end loop; 14 END; 15 / Procedimento PL/SQL concluído com sucesso. Decorrido: 00:00:02.55 SQL> DECLARE 2 v_num PLS_INTEGER; 3 4 function soma(p1 number, p2 number) return number 5 is 6 begin 7 return(p1+p2); 8 end; 9 10 BEGIN 11 for i in 1..10000000 loop

PL/Scope Esta é mais uma ferramenta do compilador PL/SQL que organiza dados sobre os identificadores utilizados nos programas PL/SQL no ato da compilação, disponibilizando acesso para consulta através da view USER_IDENTIFIERS, conforme o exemplo na Listagem 7. Este recurso permite a desenvolvedores PL/SQL maior produtividade ao minimizar o tempo de reconhecimento do código fonte em programas armazenados no banco de dados. 52 SQL Magazine Edição 101 •

12 PRAGMA INLINE(soma,’YES’); --invocar otimização de forma explícita 13 v_num := soma(v_num, i); 14 end loop; 15 END; 16 / Procedimento PL/SQL concluído com sucesso. Decorrido: 00:00:01.13 SQL> --ALTERAR O COMPORTAMENTO PADRÃO DO COMPILADOR: SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3; Sessão alterada. SQL> DECLARE 2 v_num PLS_INTEGER; 3 4 function soma(p1 number, p2 number) return number 5 is 6 begin 7 return(p1+p2); 8 end; 9 10 BEGIN 11 for i in 1..10000000 loop 12 v_num := soma(v_num, i); 13 end loop; 14 END; 15 / Procedimento PL/SQL concluído com sucesso. Decorrido: 00:00:01.13

PL/SQL Compile Warnings Na versão 11g, uma nova advertência para o compilador PL/SQL foi introduzida visando alertar os desenvolvedores quanto ao uso das cláusulas when others , que não propagam o erro após o tratamento da exceção e podem ocultar problemas ou bugs no programa. Vale lembrar que as advertências de compilação não são erros que impedem a execução do procedimento e podem ser ativadas através do parâmetro plsql_warnings , exemplificado na Listagem 8.

Listagem 7. Listar identificadores de programas PL/SQL.

SQL> ALTER SESSION SET PLSCOPE_SETTINGS=’IDENTIFIERS:ALL’; Sessão alterada. SQL> CREATE OR REPLACE PROCEDURE PROC1(param1 varchar2, param2 number) 2 IS 3 v_minha_var date; 4 BEGIN 5 IF param1 =’TESTE’ AND param2 > 0 THEN 6 v_minha_var := sysdate + param2; 7 END IF; 8 END; 9/ SQL> SELECT LPAD(‘ ‘, level*2, ‘ ‘) || name AS name, type, usage_id, line 2 FROM user_identifiers 3 START WITH usage_context_id = 0 4 CONNECT BY PRIOR usage_id = usage_context_id; NAME TYPE USAGE_ID -------------------- ------------------------------ --------------PROC1 PROCEDURE 1 PROC1 PROCEDURE 2 PARAM1 FORMAL IN 3 VARCHAR2 CHARAC TER DATATYPE 4 PARAM2 FORMAL IN 5 NUMBER NUMBER DATATYPE 6 V_MINHA_VAR VARIABLE 7 …

LINE ---------1 1 1 1 1 1 3

Listagem 8. Exemplo do parâmetro plsql_warnings.

SQL> ALTER SESSION SET PLSQL_WARNINGS = ‘enable:all’; Sessão alterada. SQL> CREATE OR REPLACE PROCEDURE PROC_TATA_ERRO 2 IS 3 BEGIN 4 RAISE_APPLICATION_ERROR(-20001,’TESTE EXCEÇÃO’); 5 EXCEPTION 6 WHEN OTHERS THEN 7 NULL; 8 END PROC_TATA_ERRO; 9/ SP2-0804: Procedimento criado com advertências de compilação SQL> SHOW ERRORS Erros para PROCEDURE PROC_TATA_ERRO: LINE/COL ERROR -------- ----------------------------------------------------------------6/9 PLW-06009: o handler OTHERS do procedimento “PROC_TATA_ERRO” não termina com RAISE ou RAISE_APPLICATION_ERROR

Conclusão Com isso finalizamos nossa série de dois artigos que apresentou algumas importantes melhorias trazidas pelo Oracle na versão 11gr2. Os recursos aqui apresentados são úteis para tirarmos melhor proveito da nova versão do banco de dados que é considerado um dos mais robustos do mercado. Thyago Bernuci

[email protected] @thyagobernuci | br.linkedin.com/in/thyagobernuci

Atua no ramo de desenvolvimento de sistemas há 18 anos, e em Administração de banco de dados Oracle há 13 anos. É Bacharel em Ciência da Computação com Especialização em Análise de Sistemas e MBA em Governança de TI. Possui os certificados Oracle OCP 8i, 9i, 10g, 11g, Oracle RAC 11gR2 and Grid Infrastructure Administration Expert, Oracle on Linux Expert, Oracle SQL Expert, Oracle 11g Implementation Specialist, PMI CAPM, RUP, COBIT e ITIL FOUNDATION. What’s New in the SQL Language Reference

http://docs.oracle.com/cd/E11882_01/server.112/e26088/wnsql.htm#sthref3 What’s New in PL/SQL Language Reference

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/whatsnew. htm#CJAEGHHH PL/SQL New Features and Enhancements in Oracle Database 11g

http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements _11gR1.php Oracle Technology Network Developer License Terms

http://www.oracle.com/technetwork/testcontent/standard-license-088383.html Oracle 11g Product Editions & Features

http://www.oracle.com/us/products/database/product-editions-066501.html Dê seu feedback sobre esta edição!





Questões de banco banco de dados do concurso concurso da PRODAM PROD AM (2012) Veja neste artigo a correção das questões sobre banco de dados do concurso público para a PRODAM realizado em 2012

E

m dezembro de 2011 a Empresa de Tecnologia da Informação e Comunicação do município de São Paulo (PRODAM/SP) abriu um concurso público para preencher vagas de reserva para diversos cargos. Esta prova selecionou candidatos para os cargos de Analista de TIC II (Tecnologia da Informação e Comunicação) para suprir a necessidade de suporte a banco banc o de dados e outros cargos envolvendo tecnologias como Java, .NET, sistemas de Mainframe e administração de dados. O salário inicial para o cargo de Analista de TIC II com especialidade especia lidade em suporte de banco de dados é de R$ 5.330,90 e, como um dos pré-requisitos para o ca rgo, os candidatos devem ter diploma de graduação na área de Tecnologia da Informação, devidamente reconhecido, ou diploma de graduação em outro curso superior, acompanhado do diploma/certificado do curso de pós-graduação ou extensão na área de Tecnologia da Informação. Também é necessário três anos como tempo mínimo mín imo de formação e experiência comprovada de seis meses em suporte a banco de dados. De acordo com o edital, a primeira fase da prova foi dividida entre questões com quatro alternativas com conteúdo de português, lógica e duas séries de conhecimentos específicos. O exame foi composto de 15 questões de português e lógica e 25 questões téc nicas incluindo banco de dados, sistemas operacionais e redes. Além disso, a segunda fase da prova apresentou cinco questões dissertativas que abordaram um estudo de caso e cenários que envolviam banco 54 SQL Magazine Edição 101 •

Resumo DevMan De que se trata o artigo: Apresentar a correção das questões com conteúdo de banco de dados do concurso público da Prodam/SP para a carreira de Analista de TIC II com especialidade em suporte a banco de dados.

Em que situação o tema é útil: A resolução das questões apresentadas neste artigo é útil para o candidato que almeja seguir a carreira de servidor público em um dos diversos cargos relacionados à área de tecnologia da informação, mais especificamente para aqueles que precisam estudar o conteúdo de banco de dados.

Questões de banco de dados do concurso da PRODAM (2012) : Este artigo apresentou a correção das questões de banco de dados cobradas na prova que selecionou candidatos para o cargo de Analista de TIC II (Tecnologia da Informação e Comunicação) com foco no suporte a banco de dados para a empresa PRODAM. O artigo contém a correção das questões de múltipla escolha e também os comentários sobre as questões dissertativas, orientando o candidato que está estudando para concursos públicos para vagas na área de tecnologia da informação.

de dados. O tempo total de duração foi de quatro horas para as duas fases. O edital diz di z também que o conteúdo da prova envolve envolve os principais conceitos de bancos de dados e também como instalar,

administrar, configurar e resolver problemas relacionados aos bancos de dados MySQL e SQL Server, sem o detal detalhamenhamento de uma versão específica destes Sistemas Gerenciadores de Bancos de Dados Relacionais. Neste artigo será apresentada a correção das questões relacionadas ao conteúdo de banco de dados para o cargo de Analista de TIC II com especialidade em suporte de banco de dados. As questões serão apresentadas, corrigidas e comentadas junto com o gabarito oficial.

Questões objetivas de conhecimento específico 16.. Considerando 16 Considerando uma relação R, uma ___ ______ ______ ______ ______ ______ ______ ___,, indicada por X?Y, entre dois conjuntos de atributos X e Y que são subconjuntos de R, especifica uma restrição sobre possíveis tuplas que podem formar for mar um est ado de relação r de R. A restrição é que, para quaisquer duas tuplas t1 e t2 em r que tenham tenha m t1[X] t1[X] = t2[X], elas ta mbém devem ter t1[ t1[Y] Y] = t2[Y]. A) relação de chave secundária. B) dependência funcional. C) relação de chave estrangeira. D) vinculação transitiva. Gabarito: Alternativa B. Comentário: A primeira questão de banco de dados da prova indica que é necessário conhecer aspectos teóricos de banco de dados. De acordo com o enunciado, é preciso indicar o nome do conceito a partir da explicação de uma restrição entre tuplas. Para responder esta questão o candidato deve se concentrar na últi ma frase da mesma, onde é dito que para quaisquer quaisq uer tuplas t1 e t2 t 2 que tenham tenh am t1[X] t1[X] = t2[X], então estas tuplas também t ambém devem ter t1[Y] t1[Y] = t2[Y t 2[Y]. ]. Esta definição quer dizer que se o conjunto de atributos de X em t1 for o mesmo em t2, então o conjunto de atributos de Y em t1 também deve ser o mesmo em t2. Isso fica claro quando pensamos em uma um a relação de chave primária e c have estrangeira, pois tal relação exige que os atributos da chave primária correspondam com os atributos da chave estrangeira. Se o candidato conseguir fazer esta associação entre o enunciado e a restrição imposta por chaves primá rias e chaves estrangeiras, fica fácil identificar que estamos falando do conceito de dependência dependência funcional fu ncional apresentado na definição da segunda forma normal. Sendo assim, o candidato deve marcar a opção B para a questão 16. 17. A terceira forma normal 17. norma l é baseada no conceito conc eito de: A) relação de chave secundária. B) relação de chave estrangeira. C) dependência transitiva. D) atributo não-atômico.

Gabarito: Alternativa C. Comentário: Esta é uma questão que cobra do candidato o

conhecimento a respeito da normalização. Apenas para lem brar, o conceit c onceitoo da terce t erceira ira forma f orma norma normall é o segu s eguinte: inte: Uma estrutura estrutu ra de dados dados encontra-se na terceira forma normal (3FN) se (3FN) se j á estiver na segunda forma normal ( 2F N) e se nã o existir a a nomalia da dependência transitiva ou i ndireta entre um atributo e a chave prim á ria. Portanto, uma estrutura de dados estar á na terceira formal normal ( 3FN ) se todos os seus atributos dependerem funcionalmente apenas da chave primá ria e de nenhum outro atributo. Como se pode notar, o enunciado da terceira forma normal utiliza utili za o conceito de depend ência transitiva. tran sitiva. Para compreender melhor o enunciado da quest ã o, vamos considerar o seguinte exemplo: em um cadastro de clientes temos um identi ficador num érico (chave prim á ria), o nome do cliente e a sua cor preferida. Suponha que a cor preferida sej a relacionada ao nome do cliente e n ã o ao seu identificador que é a chave prim á ria. Este exemplo simples mostra como a estrut ura n ão estaria na forma normal, pois a cor preferida depende funcionalmente (dependência tran sitiva sitiva)) do nome do cliente que n ão é a chave primá ria da tabela. Sendo assim, o candidato deve marcar a alternativa C como correta para a quest ã o 17. 18. Considere as seguintes afirmações: I - Um esquema de relação R está na terceira forma normal se cada atributo não principal A em R não for parcialmente dependente de qualquer chave K de R. A chave K de R deve ser obrigatoriamente uma chave candidata multivalorada (não-atômica) da relação R.

II - A primeira forma normal afirma que o domínio de um atributo deve incluir apenas valores atômicos (simples, indivisíveis) e que o valor de qualquer atributo em uma tupla deve ser um único valor do domínio desse atributo. Pode-se afirmar que: A) apenas a afirmação II está correta. B) apenas a afirmação I está correta. C) todas as afirmações estão corretas. D) todas as afirmações estão incorretas. Gabarito: Alternativa A. Comentário: Esta questão foca no conhecimento e entendimento das três formas normais através da avaliação de duas afirmações. A afirmação I se concentra na terceira forma normal, cuja definição é a seguinte: uma estrutura de dados encontra-se na terceira forma normal (3FN) se já estiver na segunda forma normal (2FN) e se não existir a anomalia da dependência transitiva ou indireta entre um atributo e a chave primária. Portanto, uma estrutura de dados estará na terceira formal normal (3FN) se todos os seus atributos dependerem funcionalmente apenas da chave primária e de nenhum outro atributo. A primeira frase da afirmação I está correta, pois os atributos não principais, ou seja, que não fazem parte da chave primária, Edição 101 SQL Magazine 55 •

Questões de banco de dados do concurso da PRODAM (2012)

não devem ser parcialmente depe ndentes de qualquer chave K de R. Já a segunda frase da afirmação I diz que a chave K de R deve ser obrigatoriamente uma ch ave candidata multivalorada

Pode-se af irmar que: Pode-se A) apenas a afirmação III está incorreta. B) apenas a afirmação II está incorreta. C) apenas a afirmação I está incorreta. D) todas as afirmações estão corretas.

tabela, então não há colunas da tabela q ue dependem apenas de A ou de B. O candidato deve lembrar que na definição da segunda forma normal o importante é que a tabela já esteja na primeira forma normal e que não exista a anomalia da dependência transitiva ou indireta entre um atributo e a chave primária. Estes dois conceitos estão presentes nesta afirmação I, tornando-a correta. A segunda afirmação afir mação pode ser traduzida traduzida da seguinte maneira: A primeira forma normal (1FN) significa qu e a tabela não possui atr ibutos multivalorados ou compostos. (Um atributo composto contém outros atributos e pode ser dividido em partes menores). Esta segunda a firmação apresenta o principal requisito da primeira forma normal: a ausência de colunas multivaloradas. Já a segunda seg unda f rase desta de sta afi a firmaç rmação ão foca na defin def inição ição de uma u ma tabela relacional, dizendo que toda tabela relacional já está na primeira forma normal, argumentando que o valor de qualquer linha precisa ser atômico. Esta afirmação é importante, pois trata do conceito de “tabela relacional” que não é citado diretamente pelas formas normais. Aqui o candidato deve lembrar que os termos “tabela” e “entidade” são citados na definição das formas normais, e “tabela relacional” é algo específico de um banco de dados relacional como, por exemplo, o SQL Server ou o Oracle. Do ponto de vista teórico, se uma tabela está em um banco de dados relacional, ela já está de acordo com a primeira forma normal. Já do ponto de vista prático, é possível criar uma tabela com atributos multivalorados e sem nenhuma chave em um banco de dados relacional. Como o foco desta questão é voltado para as definições teórica s, o mais adequado é considerar que o termo “tabela relacional” se refere a uma tabela que já está na primeira forma normal, tornando a afirmação II correta. A tradução da afirmação III é a seguinte: uma tabela está na terceira forma normal (3FN) se está na 2FN e não há dependências funcionais entre as colunas não chave. Esta definição está correta e é equivalente à definição da terceira forma normal, que cita o conceito de dependência funcional. Portanto, a afirmação III está correta. Como as afirmações I, II e III estão corretas, o candidato deve marcar a alternativa D para a questão 19.

Gabarito: Alternativa D. quest ão cobra o conhecimento Comentário: Mais uma vez, esta questão a respeito das definições das três formas normais. As afirmações apresentadas no enunciado estão em inglês e podem confundir o candidato que não está acostumado a compreender definições nesta língua. Uma possível tradução para a afirmação I é a seguinte: uma tabela está na segunda forma normal (2FN) se ela já estiver na 1FN e não há colunas que dependam apenas de parte da chave primária primár ia desta tabela. Isto signif ica que se (A,B) (A,B) for uma combinação de duas colunas que compõem a chave primária da

23. Considere as afirmações abaixo. I - No modelo de dados relacional, a restr ição de integridade referencial é especificada entre duas relações e usada para manter a consistência entre t uplas nas duas relações. Informalmente, a restrição de integridade referencial afirma que uma tupla em uma relação que referencia outra relação prec isa se referir a uma tupla existente nessa relação. II - Replicação é o processo de criar uma cópia exata dos dados. Gerar uma ou mais réplicas dos dados de produção é uma das formas de se fornecer BC (Business Continuity, continuidade de negócios). Essas réplicas podem ser usadas

(não-atômica) da relação R. Uma chave multivalorada é aquela

que é composta por mais de um atributo como, por exemplo, uma entidade cuja chave é comporta de um identificador do pedido e um identif icador do produto. produto. Contudo, a definição da terceira forma normal não faz referência em nenhum momento a chaves multivaloradas e, por isso, a afirmação I é incorreta. A afirmação II remete à definição da primeira forma normal, que é a seg uinte: uma estrutura de dados encontra-se na primeira forma normal (1F N) se todas as suas colunas não apresentarem atributos multivalorados, ou seja, atributos que podem conter mais de um valor para uma mesma instâ ncia da entidade como, por exemplo, o telefone (comercial, residencial e celular). Em suma, a definição apresentada na afirmação II é equivalente à definição da primeira forma normal, tornando-a verdadeira. verdadeir a. Sendo assim, o candidato ca ndidato deve marcar a alternativa A para esta questão, uma vez que apenas a afirmação II está correta. 19. Considere as afirmações abaixo. I - A table is in second normal form (2NF) if it is in 1NF and there is no nonkey column dependent on a partial pri mary key of that table. This means if (A,B) is a combination of two table columns building the key, then there is no column of the table depending either on only A or only B. II - First normal form (1NF) means that a table has no multivalued attributes or composite attr ibutes. (A composite attribute contains other attributes and can therefore be divided into smaller parts.) All relational tables are by definition in 1NF, because becau se the value of any column in a row must be atomic—t hat is, single valued. III - A table is in third th ird normal form (3NF) (3NF) if it is in 2NF and there are no f unctional dependencies between nonkey columns.


para operações de recuperação e reinicialização em caso de perda de dados. Pode-se af irmar que: A) todas as afirmações estão incorretas. B) apenas a afirmação II está correta. C) todas as afirmações estão corretas. D) apenas a afirmação I está correta. Gabarito: Alternativa C. Comentário: Esta questão cobra do candidato dois conceitos distintos de banco de dados: a restrição imposta pela integridade referencial e o propósito de replicação . A primeira afirmação está correta e apresenta o conceito de integridade referencial entre duas relações focando na consistência que este recurso proporciona. Também está correta a afirmação sobre a integridade referencial no que diz respeito à presença de tuplas correspondentes em relações distintas. Nos SGBDRs a integridade referencial é representada como um relacionamento entre as chaves primárias e as chaves estrangeiras. A segunda af irmação defi ne o que é o processo de replicação e também está correta. Contudo, esta afir mação cita o conceito de BC (Business Continuity), cuja definição formal é a seguinte: “Business Continuity é a atividade executada por uma organização para garantir que funções críticas do negócio serão disponíveis para clientes, fornecedores, regulamentadores e outras entidades que precisam ter acesso a estas funções”. Em geral, o termo Business Continuity não é usual quando se fala em banco de dados e deve-se dar preferência à expressão alta disponibilidade (ou HA de High Availability) para indicar que o serviço de banco de dados deve estar sempre dispon ível. De qualquer maneira, a replicação pode ser utilizada para implementar alta disponibilidade de banco de dados por meio de réplicas envolvidas em situações de recuperação e reinicialização quando há perda de dados. Como as afirmações I e II são corretas, o candidato deve marcar a alternativa C para responder corretamente à questão 23. 27. Backup ______________ copia os dados que foram alterados desde o último backup completo. A) incremental B) sintético C) cumulativo D) estático Gabarito: Alternativa C. Comentário: Esta questão cobra do candidato o conhecimento dos tipos de backup. No enunciado não é feita nenhuma referência ao tipo de backup de um Sistema Gerenciador de Banco de Dados Relacional (SGBDR) específico. No entanto, como as demais questões e o edital da prova fazem referência aos SGBDRs MySQL e SQL Server, é razoável assumir que o

examinador deseja cobrar conhecimentos a respeito de uma destas duas opções. Nota-se também que o enunciado não diz se o backup de dados é focado no tipo de backup fornecido por um sistema operacional ou por um banco de dados. De acordo com a documentação oficial do SQL Server, os tipos backups nativos possíveis (que podem ser realizados pelo próprio SQL Server e não por ferramentas externas) são os backups Full, differential e transact ion log backup. A tradução usual utilizada pela comunidade de usuários e adotada pelo próprio fabricante (Microsoft) é backup completo, diferencial e backup de transaction log. Já o MySQL utiliza o conceito de backups físicos (raw) e lógicos em sua documentação. Os backups físicos (raw) são cópias dos arquivos feitas utiliza ndo as ferramentas do sistema operacional, e os backups lógicos são gerados por ferramentas do MySQL que armazenam a estrutura lógica do banco de dados através de instruções SQL (CREATE, INSERT e outras). A ferramenta mysqldump é utilizada para a geração de backups lógicos no MySQL. Todas as alternat ivas apresentam nomes de backups q ue não são utilizados nem pelo MySQL e nem pelo SQL Server. O gabarito indica que a alternativa correta é a C, supondo que o candidato deve associar a parte do enunciado que fala “(...) copia os dados que foram alterados desde o último backup completo” com a definição da palavra c umulativo. Contudo, como nenhuma das alternativas apresenta um tipo de backup válido para os bancos de dados citados no edital. Sendo assim, é de minha opinião que a questão deve ser anulada por apresentar um enunciado incompleto e alternativas inadequadas. 33. O MySQL pode ler as opções de inicialização no arquivo: A) mysql.init B) my.cnf C) config.csf D) start.cfg Gabarito: Alternativa B. Comentário: O MySQL, assim como outros bancos de dados, utiliza diversos parâmetros de inicialização que são lidos quando o servidor de banco de dados é iniciado. Estes parâmetros controlam configurações de replicação, valores numéricos referentes a limites, caminhos de arquivos do servidor de banco de dados e outras configurações. O armazenamento destes parâmet ros deve ser feito de forma externa ao banco de dados, uma vez que estes valores devem ser acessados no exato momento em que o banco de dados está sendo inicializado e as tabelas e objetos ainda não são acessíveis. Para tanto, os bancos de dados recorrem a a rquivos texto do sistema operacional que armazenam os valores e os nomes dos parâmetros e configurações. Há uma tendência em colocar estes valores em arquivos XML, mas este não é o caso do MySQL, que armazena seus parâmetros de configurações de inicialização no a rquivo my.cnf , indicado na alternativa B. Edição 101 SQL Magazine 57 •


Os demais arquivos presentes nas outras alternativas não são utilizados para o armazenamento de configurações de inicialização do MySQL. 34. O sistema de privilégios do MySQL garante que todos usuários possam fazer exatamente as operações que lhes são permitidas. O controle de acesso do MySQL é composto por dois estágios. O servidor utiliza as tabelas _________________________ no banco de dados mysql em ambos estágios do controle de acesso. A) privilege, access e root B) passwd, secure e data C) control, pwd e oper D) user, db e host Gabarito: Alternativa D. Comentário: Esta questão cobra do candidato o conhecimento sobre os metadados do MySQL, ou seja, o nome das tabelas de sistema utilizadas para checagem de permissões e controle de acesso. Estas tabelas de sistema são armazenadas no banco de dados de sistema chamado mysql, que é criado durante a instalação. A questão faz uma rápida menção aos estágios utilizados no controle de acesso. Apesar de não haver nenhum detalhamento destes estágios, podemos assumir que são os dois principais estágios envolvidos em qualquer processo de controle de acesso: a autenticação, onde o usuário garante que ele é quem diz ser; e a autorização, onde são conferidas as permissões de acesso aos objetos do banco de dados para o usuário. No MySQL estas informações são armazenadas nas tabelas de metadados, também conhecidas como catá logo do sistema ou tabelas de dicionário do MySQL. Estas tabelas são a u ser, responsável pelo armazenamento dos usuários cadastrados; a db, responsável pelos bancos de dados utilizados no My SQL; e a host, que faz a associação de clientes (por endereço IP ou por outra forma de identificação) que podem ou não acessar o servidor MySQL. Assim, a alternativa D apresenta corretamente o nome das tabelas de sistema associadas com o processo de controle de acesso do MySQL. As demais tabelas indicadas nas outras alternativas não existem no MySQL. 35. A deleção de um usuário no MySQL pode ser feita pelo comando: A) DLT -u nome_usuario B) REMOVE -u nome_usuario C) DROP USER nome_usuario D) CANCEL -a nome_usuario Gabarito: Alternativa C. Comentário: Esta questão solicita que o candidato marque a alternativa que contém o comando DDL ( Data Definition Language) utilizado para remover um usuário no MySQL. 58 SQL Magazine Edição 101 •

Para responder rapidamente à questão basta recorda r que os comandos DDL são iniciados por CREATE, ALTER ou DROP, para criar, alterar e remover objetos do banco de dados, inclusive usuários. Desta maneira, a alternativa C apresenta o comando correto para a remoção de um usuário no MySQL. As demais alternativas não são comandos padronizados pelo conjunto de instruções DDL da linguagem SQL e não removem um usuário do MySQL. 36. No MySQL, o backup em um banco de dados pode ser feito por intermédio do programa: A) mysqldump. B) mybackup. C) sqlcopy. D) sqlback. Gabarito: Alternativa A. Comentário: Esta questão segue a linha de cobrança do conhecimento de tarefas administrativas no MySQL, sendo que o backup é uma das principais atividades realizadas por quem trabalha com banco de dados. O MySQL possui apenas uma ferramenta nativa de backup que transforma todo o conteúdo de um banco de dados em instruções SQL para posterior recriação do banco de dados. Esta ferramenta de linha de comando se chama mysqldump, e assume a forma de um arquivo binário que recebe alguns parâmetros e retorna um arquivo texto contendo todas as instruções SQL necessárias para criar o banco de dados, seus objetos e seus dados. Sendo assim, o candidato deve marcar a alternativa A para esta questão. As demais alternativas não apresentam nomes corretos de programas para a realização de backups no MySQL. 37. No MySQL, utiliza-se o comando _____ ________ quando se deseja limpar algum dos caches internos que o MySQL usa. Para executar este comando, deve-se ter o privilégio _______________. A) CLEAN/CONTROL B) REORG/FILE C) HOUSEK/DIRECT D) FLUSH/RELOAD Gabarito: Alternativa D. Comentário: O objetivo do examinador ao criar esta questão é cobrar do candidato o conhecimento a respeito da importante tarefa de limpeza de caches internos do MySQL, geralmente empregada quando é necessário realizar testes de desempenho que devem eliminar o fator de cache. Além de perguntar sobre o comando que limpa o cache interno do MySQL na primeira lacuna do enunciado, o candidato deve indicar o privilégio utilizado para que um usuário do MySQL possa enviar o comando. Vale a pena lembrar aqui que todos os comandos do MySQL requerem permissões que podem

ser atribuídas individualmente ou através de grupos. E estas permissões têm nomes específicos. Para limparmos o cache interno do MySQL utilizamos o comando FLUSH. Um detalhe: utilizar apenas o comando FLUSH limpa todos os logs internos do MySQL, porém é p ossível limpar logs individuais com os comandos FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, FLUSH TABLES WITH READ LOCK, dentre outros. Já a permissão para a realização deste coma ndo pode ser atribuída individualmente a um usuário por meio do comando GRANT, indicando a permissão RELOAD, ou pela inserção do usuário dentro de um grupo que tenha o privilégio RELOAD. Desta maneira, o candidato deve marcar a alternativa D como correta e notar que nenhuma das outras opções apresentam comandos válidos no MySQL. 38. O mecanismo de ______ ____ __ é um importante mecanismo de autorização discricionário por si só. Por exemplo, se o proprietário A de uma relação R quiser que outra conta B seja capaz de recuperar apenas alguns campos de R, então A pode criar uma visão V de R que inclua apenas os atr ibutos e depois conceda SELECT em V para B. A) trigger B) view C) stored procedure D) control Gabarito: Alternativa B. Comentário: Esta questão apresenta o enunciado um tanto quanto confuso e pode atrapalhar ca ndidatos que não estejam adequadamente preparados. O objetivo é verificar se o candidato sabe qual é o objeto de banco de dados que corresponde ao que é descrito no enunciado. Alguns candidatos podem se confundir com a palavra discricionário utilizada no enunciado para descrever uma das características do mecanismo que deve ser preenchido na lacuna. De acordo com o dicionário, discricionário significa deixado à discrição, livre de condições, não limitado. Esta palavra geralmente é utilizada por juristas e profissionais da área de Direito quando empregam a expressão poder discricionário. Se o candidato analisar o exemplo que é fornecido na segunda sentença do enunciado, ele pode rapidamente notar que a maneira utilizada nos bancos de dados para acessar os dados da relação R com as características de acesso dos usuários A e B é representada por uma view. Apesar de o enunciado classificar a view como um mecanismo, algo incomum, pois é cons enso que uma view é um objeto de banco de dados, é possível pensar em views como objetos para facilitar o acesso de usuários a certas colunas de uma relação. Sendo assim, o ca ndidato deve marcar como correta a alternativa B para esta questão. 39. In MS SQL Server 2008, during t he installation of Database Engine, the following system databases are generated:

A) master, model, tempdb and msdb. B) control, testdb, mstemp and relat ive. C) user, proc, exec and persist. D) primary, hash, relate and login. Gabarito: Alternativa A. Comentário: Esta questão cobra do candidato o conhecime nto dos bancos de dados de sistema que são criados no momento da instalação do SQL Server 2008. A tradução para português do enunciado é a seguinte: “No MS SQL Server 2008, durante a instalação do Database Engine, os seguintes bancos de dados de sistema são gerados:”. O SQL Server 2008 possui quat ro bancos de dados de sistema em uma instância recém-instalada: Master, Model, TempDB e MSDB. O Master é o principal banco de dados de sistema e contém metadados sobre todos os demais bancos, além de tabelas, views, stored procedures e funções de sistema. Os logins do servidor e as permissões destes logins também são armazenadas no Master, junto com as configurações gerais do servidor. O Model é um banco de dados utilizado como modelo. Toda vez que um usuário cria um novo banco de dados, uma cópia do Model é realizada. Internamente este banco de dados contém tabelas, usuários e a lguns objetos de sistema indispensáveis para qualquer banco de dados SQL Server 2008. O tempdb é um banco de dados que armazena objetos temporários, como tabelas, stored procedures e outros objeto s que são utilizados por todos os bancos de dados. Toda vez que o SQL Server 2008 é reiniciado o tempdb é automaticamente reconstruído. Por este motivo não se recomenda utilizar este banco de dados para a criação de objetos e o armazenamento de dados. O msdb armazena toda a estrutura necessária para a execução de tarefas agendadas, como jobs , schedules , operadores e alertas, e o histórico de execução dos jobs e notificações. Além dos bancos de dados de sistema Master, Model, tempdb e msdb, uma instância do SQL Server 2008 recém-criada que utiliza replicação faz uso do banco de dados de sistema distribution no servidor que assume o papel de Distribuidor (Distributor). O banco de dados distribution contém tabelas e stored procedures de sistema necessárias para a replicação. Desta maneira, o candidato deve marcar a alternativa A como correta para esta questão. 40. In MS SQL Server 2008, utilities are components that provide different features such as data reliability, data definition, and statistics maintenance functions. The program ______ ___ is a useful utility that copies database data to/from a data file. A) copydb B) utilmove C) bcp D) cdbtf Edição 101 SQL Magazine 59 •


Gabarito: Alternativa C. Comentário: Esta questão cobra do candidato o conhecime nto a respeito das ferramentas administrativas. A tradução para português do enunciado fica assim: No MS SQL Server 2008, os utilitários são componentes que proporcionam diferentes funcionalidades tais como confiança nos dados, definição de dados e funções para manutenção das estatíst icas. O programa ____________ é um utilitário prático que copia os dados de um banco de dados de/para um arquivo de dados. A questão cobra do candidato o conhecimento do nome do utilitário que melhor preenche a lacuna. Dentre as opções, a única que se enquadra na descrição é a ferramenta de linha de comando chamada bcp.exe , que é apresentada na alternat iva C. Questões dissertativas e estudo de caso 1. Descreva, de modo sucinto, quais procedimentos você adotaria para monitorar a performance e a segurança dos bancos de dados MySQL e MS SQL Server. Resposta: Para a monitoria de performance e segurança do MySQL podemos utilizar: Captura das consultas mais utilizadas pela utilização de general query log habilitado na inicial ização do daemon mysql ou mysqld; Análise do plano de execução das instruções SQL com o comando EXPLAIN; Análise das tabelas de metadados para identificação dos índices nas tabelas; Visualização das tabelas de sistema user, db e host do banco MySQL para identificação do controle de acesso; Criação de um plug-in de auditoria. Para o leitor que deseja mais informações sobre a criação de plug-ins de auditoria no MySQL, recomendo o seguinte link para a documentação do MySQL: http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html; Verificar as regras de c riação de senhas visando garantir que elas possuam atributos de segurança tais como tamanho, data de expiração e uso de caracteres especiais. •

•

•

•

•

•

Para a monitoria de performance e segurança do MS SQL Server podemos utilizar: Uso da ferramenta SQL Profiler para captura das instruções SQL enviadas para o servidor; Análise do plano de execução, estatísticas de acesso, I/O e outras características envolvidas na execução de instruções SQL; Análise das tabelas e views de metadados (DMV – Data Mana gement Views) para identificação das características e detalhes de uso dos índices nas tabelas; Habilitar a funcionalidade CDC ( Change Data Capture) para monitorar o acesso a certos objetos do banco de dados; Habilitar a auditoria de logins do SQL Server; Monitorar a quantidade de acessos realizados por meio de um performa nce counter no performance monitor do Windows, exclusivo para o SQL Server; •

•

•

•

• •


Verificar as políticas de segurança das sen has para logins do SQL Server. •

2. Os bancos de dados relacionais utilizam a normalização de dados para evitar redundâncias e possibilitar um maior desempenho nas consultas ao banco de dados. Explique quando uma relação está na primeira, segunda ou terceira forma normal. Exemplifique. Resposta: A normalização é uma técnica utilizada para organizar as entidades de dados e evitar anomalias, redundâncias e gerar ganho de desempenho. Uma estrutura de dados encontra-se na primeira forma normal (1FN) se todas as suas colunas não apresentarem atri butos multivalorados, ou seja, atributos podem conter diversos valores para uma mesma linha da tabela como, por exemplo, o telefone (comercial, residencial e celular). Uma estrutura de dados encontra-se na segunda forma nor mal (2FN) se j á estiver na primeira forma normal ( 1FN) e se todas as suas colunas que n ã o são chave prim á ria n ão apresentarem a anomalia da depend ência funcional parcial em rela çã o à chave primá ria. A anomalia da depend ência funcional parcial ocorre quando a chave prim á ria é composta por dois ou mais atributos (colunas), e outros atributos n ã o chave dependem de parte dessa chave prim á ria. Como o enunciado da quest ã o solicita um exemplo, vamos supor que temos um modelo que represente uma nota fiscal. Este modelo cont ém uma entidade necess ária para relacionar os detalhes da nota fiscal (data de emiss ão, cliente, transportadora, etc.) com os produtos realmente comprados. Esta entidade deve possuir uma chave prim á ria composta pelo n ú mero da nota fiscal e pelo identificador dos produtos. Se colocarmos uma coluna preço do produto nesta entidade, devemos garantir que o preço est á se referindo ao pre ço do produto apenas nesta nota fiscal, e n ão ao pre ço fixo do produto para todas as demai s notas fiscais, pois se o pre ço estivesse relacionado apenas a uma parte da chave (no caso, apenas ao identificador do produto), ter íamos uma dependência parcial em rela çã o à chave prim ária e violar íamos a segunda forma normal. Uma estrutura de dados encontra-se na terceira forma normal (3FN) se já estiver na segunda forma normal ( 2FN) e se n ão existir a anomalia da depend ência transitiva ou indireta entre um atributo e a chave prim á ria. Portanto, uma estrutura de dados estará na terceira formal normal ( 3FN) se todos os seus atributos dependerem funcionalmente apenas da chave prim á ria e de nenhum outro atributo. Exemplo: em um cadastro de clientes temos um identificador num érico (chave prim á ria), o nome do cliente e a sua cor preferida. Suponha que a cor preferida seja relacionada ao nome do cliente e n ão ao seu identificador que é a chave prim á ria. Este exemplo simples mostra como a estrutura n ão estaria na terceira forma normal, pois a cor preferida depende funcionalmente (depend ência transitiva) do nome do cliente que n ã o é a chave prim á ria da tabela.

3. Uma empresa usa fitas como sua mídia principal de arma zenamento de backup em toda a organização. Backups completos são executados de segunda a sábado. O ambiente contém muitos servidores de backup, fazendo backup de diferentes grupos de servidores. Os aplicativos de e-mail e banco de dados têm de ser fechados durante o processo de backup. Devido ao ambiente de backup descentralizado, a capacidade de recuperação muitas vezes é comprometida. Há fitas demais que precisam ser montadas para executar uma recuperação completa no caso de falha total. O tempo necessário para recuperação é longo demais. A empresa quer implantar um ambiente de backup fácil de ser gerenciado. Ela precisa reduzir o tempo em que os aplicativos de e-mail e banco de dados ficam indisponíveis e diminuir o número de fitas necessárias para recupera r completamente um servidor em caso de falha. Proponha uma solução de backup e recuperação considerando as necessidades da empresa. Justifique explicando como sua solução assegura que os requisitos serão atendidos. Resposta: Neste cenário há a necessidade de atender os seguintes requisitos: Backups diários (segunda a sábado); Backups de diferentes grupos de servidores com aplicativos de e-mail e banco de dados; Tempo de recuperação curto; Backup fácil de ser gerenciado; Diminuir o número de fitas para recuperação em caso de falha. • •

•

dos demais tipos e facilitar a ad ministração. E cada jogo de três fitas pode ser separado por grupo de servidores, evitando-se confundir os backups de diferentes aplicações. 4. O departamento de TI de um banco promete acesso dos clientes à tabela de taxas de conversão monetária entre 10h00 e 16h00 de segunda a sexta-feira. A atualização da tabela é feita todos os dias às 09h00. O processo de atualização leva 35 minutos para terminar. Na quarta-feira, devido a um banco de dados corrompido, a tabela de taxas não pôde ser atualizada. Às 10h05 foi constatado que a tabela continha erros. A verificação foi executada por 15 minutos e a tabela de taxas foi disponibilizada para as agências. Qual foi a disponibilidade da tabela de taxas na semana em que este incidente ocorreu, supondo-se que não houve outros problemas? Resposta: Considerando que a disponibilidade total do serviço em um dia é de 6 horas, ou 360 minutos (6 x 60), temos que a disponibilidade total em uma semana é de 1.800 minutos (5 x 360). Na fatídica quarta-feira um problema foi detectado às 10h05 e, supondo que o processo de verificação de 15 minutos foi iniciado nesta hora, temos que o serviço foi iniciado às 10h20. Neste dia a dispon ibilidade foi de 340 minutos (360 - 20), ou de 94,444%. Já a disponibilidade nesta semana foi de 1.780 minutos (1.800-20), ou de 98,888%.

• •

A primeira sugestão é a util ização de um servidor de backup centralizado. Desta maneira fica mais fácil gerenciar o processo. Para atender os demais requisitos é possível mesclar diferentes tipos de backup tais como o completo, diferencial e do log do banco de dados. Para montar uma estratégia de backup detalhada e atender plenamente os requisitos é preciso conhecer detalhes internos do banco de dados e também seu volume, frequência de atualização, estilo de aplicação (OLAP x OLTP) e outras características. Contudo, para o cenário em questão, recomenda-se a utilização de um backup completo por semana, intercalado por um backup diferencial por dia e um backup de log a cada hora. Desta maneira o tempo de indisponibil idade é reduzido, pois os backups de log e diferencial não precisam fechar as aplicações para serem realizados. Esta abordagem também diminui o tempo de restauração, pois é preciso apenas restaurar o ú ltimo backup completo, mais o último backup diferencial e o último backup de log, dependendo do ponto no qual se deseja restaurar o banco de dados. Como os backups diferenciais e de log ocupam menos espaço que o backup completo, menos fitas serão necessárias e o tempo de restauração também será diminuído. A propósito, recomendase utilizar três jogos de fitas para sepa rar os backups completos

5. Uma organização está planejando uma migração no data center. Ela só pode tolerar um máximo de duas horas fora do ar para completar a migração. Explique como a tecnologia de replicação pode ser usada para satisfazer os requisitos de tempo fora do ar. Resposta: A tecnologia de replicação pode ser utilizada neste cenário para que os dados sejam disponibilizados em mais de um local. No exemplo é possível montar uma replicação entre o local onde os dados estão atual mente (um servidor localizado fisicamente na organização) e o servidor localizado no data center. Esta replicação pode ser configurada de modo que haja uma transferência inicial dos objetos e estruturas do banco de dados (snapshot) atual origem para o banco de dados no serv idor do data center, sem que o banco de dados fique fora do ar. Em seguida os dados vão sendo copiados pelo processo de replicação unidirecional enquanto todas as aplicações cliente (web sites, sistemas ERP, etc.) acessam apenas o servidor de origem. Uma vez que ambos os ambiente estejam completamente sincronizados, é preciso habilitar o banco de dados do ambiente de destino (datacenter) para que ele aceite as conexões das aplicações que, por sua vez, devem ser configu radas para acessar o novo servidor no datacenter. Deste modo o tempo de duas hora s fora do ar será apenas para mudar as configurações do banco de dados de destino e para redirecionar todas as aplicações para se conectarem nesta nova fonte dados. Edição 101 SQL Magazine 61 •


Conclusão As questões da prova cobraram diversos assuntos fundamentais para quem vai trabalhar com banco de dados, incluindo conceitos teóricos de banco de dados e aspectos relacionados à modelagem. A dificuldade das questões desta prova é condizente com um profissional de nível júnior ou pleno que tenham tido uma sólida formação em banco de dados e em desenvolvimento de sistemas em geral. A prova apresentou alguns pontos peculiares que não são muito comuns em concursos públicos. Um destes pontos é a falta de imagens para serem analisadas nas questões de modelagem, pois é comum encontrar pequenos modelos com imagens ou mesmo telas das aplicaçõ es neste tipo de prova. De forma semelhante, não houve nenhuma questão que cobrasse conhecimentos da linguagem SQL, a principal ferramenta de trabalho para quem utiliza banco de dados. Uma crítica em relação à prova vai para a questão que cobrou conhecimentos do tipo de backup. Como citado na correção, o enunciado desta questão é ambíguo e dá margem para a anulação da mesma. Além disso, destacam-se alguns conceitos que raramente são utilizados na prática e termos não usuais. Um ponto positivo a ser destacado é a abordagem utilizada para as questões dissertativas, pois elas cobram do candidato a habilidade para explicar conceitos, analisar cená rios, sugerir alternativas e realizar cálculos de alta disponibilidade. Todas estas habilidades são muito úteis no dia a dia e devem ser conhecidas por quem deseja trabalhar com banco de dados.


Mauro Pichiliani (@pichiliani)

[email protected]

É bacharel em Ciência da Computação, mestre e doutorando pelo ITA (Instituto Tecnológico de Aeronáutica) e MCP, MCDBA e MCTS. Trabalha há mais de 10 anos utilizando diversos bancos de dados, como o SQL Server, Oracle e MySQL. É colunista de SQL Server do web site iMasters (http://www.imasters.com.br) e mantenedor do DatabaseCast (@databasecast), o podcast brasileiro sobre banco de dados. Download do edital, gabarito e resultados

http://www.caipimes.com.br/prodam012011/editais.asp





Trabalhando com listas dinâmicas Compreendendo a utilidade de organizar dados dinamicamente

N

aturalmente, os bancos de dados nos oferecem acesso a dados e tal acesso pode acontecer concorrentemente por muitos usuários. Nestes casos, o acesso deve ser feito de forma prudente. O acesso simultâneo exige uma análise da quantidade de usuários, velocidade da rede, configuração do servidor e muitas outras variáveis. No geral, uma boa prática é realizar o acesso ao banco de forma prudente, minimizando o fluxo de dados entre servidor e cliente. A realização de muitos acessos simultâneos pode demandar um fluxo de dados alto, resultando em lentidão no sistema, excesso de processamento no servidor e processamento desnecessário de consultas. Uma vez feita uma consulta que será utilizada diversas vezes, é prudente alocar o resultado da mesma em memória do computador cliente. Para alocação de memória em diferentes linguagens de programação, temos diversas possibilidades. Neste ponto, se torna importante conhecer conceitos discutidos fortemente em disciplinas de linguagens de programação e estruturas de dados: listas, pilhas, filas e arvores. Conheceremos um pouco mais sobre estas estruturas mais adiante neste artigo. Outra situação em que a organização de dados em memória é importante é quando o usuário realiza uma sequência de operações que geram dados em muitas tabelas. Para evitar o envio de dados para o banco muitas vezes, é mais aconselhado organizar os dados em memória e enviar para o banco de dados de tempos em tempos ou quando a operação for finalizada. Esta organização é mais trivial do que qua ndo estamos organizando dados no caminho inverso, isto é, obtendo dados do servidor para uma possível manipulação e posterior envio. Tal operação exige manter a integridade dos dados, tais como relacionamentos anteriores, chaves primárias etc.

Resumo DevMan De que se trata o artigo: Este artigo apresenta os conceitos de alocação dinâmica e estática de memória. A alocação da memória ocorre em tempo de execução, p odendo ser estática, quando os elementos estiverem ordenados (como em variáveis globais e/ou arrays), ou dinâmica, quando os dados estiverem desordenados. Serão descritas as vantagens e desvantagens de cada tipo de alocação.

Em que situação o tema é útil: O presente artigo é útil na implementação de rotinas para organização de informações originadas ou destinadas a bancos de dados. Em alguns casos, a alocação estática de memória é mais eficiente, ao passo que, em outros casos, a organização deve ser feita de forma dinâmica.

Trabalhando com listas dinâmicas: Neste artigo serão apresentados os benefícios da organização de dados em estruturas dinâmicas e estáticas, indicando as vantagens de cada uma delas. Frequentemente os resultados de consultas feitas em bancos de dados são utilizadas para realização de operações onde é necessário identificar cada elemento isoladamente. Neste tipo de ocasião é necessário criar elementos de memória para armazenamento dos dados de forma dinâmica. Para tais operações deve ser implementada a alocação adequada.

Nos casos mais genéricos, os bancos de dados são acessados por computadores com hardware que oferecem memória em quantidades superiores a 1GB. Nestes tipos de ocasião, o programador pode alocar memória dinamicamente no computador cliente, de forma a não utilizar recursos do servidor, tendo em vista que o mesmo será acessado por muitos usuários. Mesmo que o limite de 1GB seja ultrapassado, ainda é mais prudente alocar memória virtual do que aumentar o fluxo de dados entre servidor e cliente. Edição 101 SQL Magazine 63 •

Trabalhando com listas dinâmicas

Listas dinâmicas Uma lista dinâmica (ver Nota DevMan 1) se assemelha a um “array”, que já é largamente utilizado nas linguagens de programação. No entanto, existe uma grande diferença entre uma lista dinâmica e um “array”. O “array” é definido com um tamanho fixo durante o desenvolvimento do programa e não muda. Já a lista dinâmica apresenta tamanho indeterminado, ou seja, seu tamanho vai sendo modificado conforme o programa é utilizado pelo usuário. Neste caso, o sistema irá alocar apenas a quantidade de memória necessária. É importante destacar que o programador pode determinar limites a fim de não deixar o sistema lento. Isso quer dizer, alocar memória dinamicamente mas com um limite pré-determinado.

As listas dinâmicas, independente da linguagem adotada, oferecem a possibilidade de organizar os dados na memória, ocupando apenas o espaço necessário. Ao criar uma lista, temos um dado ou conjunto de dados ocupando o espaço na memória e além dos dados em si, temos o endereço do próximo dado ou conjunto de dados. Em seguida, serão apresentadas algumas figuras com a representação e operações (remoção e inserção) em listas simplesmente encadeadas. Veja na Figura 1 um exemplo ilustrativo de uma lista dinâmica simplesmente encadeada ou lista ligada. Neste tipo de lista cada elemento é constituído de um dado ou conjunto de dados seguido do endereço de memória do próximo dado. O último elemento tem

Figura 1. Modelo de lista simplesmente encadeada.

Figura 2. Operação de remoção em uma lista simplesmente encadeada

como “endereço para próximo” o valor zero (Null ou GND). Diante disso, sabemos que tal elemento é o ultimo elemento. Continuando nossa análise, a Figura 2 apresenta uma ilustração de uma operação de remoção em uma lista simplesmente encadeada. Perceba que uma remoção apenas implica na modificação do “endereço para próximo” no conjunto precedente ao conjunto a ser removido. Já a Figura 3 apresenta uma operação de inserção em uma lista simplesmente encadeada. Perceba que uma inserção implica na modificação de “endereço para próximo” no conjunto precedente ao conjunto a ser inserido, sendo que o conjunto a ser inserido deve conter o endereço de seu conjunto sucessor. Além da lista dinâmica simplesmente encadeada, temos também as listas circulares e listas duplamente encadeadas. Estas por sua vez implicam em mais controle. A Figura 4 apresenta um exemplo de uma lista circular. A vantagem da lista circular é que esta possibilita a busca a partir de qualquer ponto dela, pois o último elemento tem o endereço do primeiro, ou seja, não é necessário se posicionar no início dela para percorrê-la completamente. Ao identificar que o “endereço para próximo” chegou ao endereço inicial, a lista foi completamente percorrida. Já a Figura 5 ilustra uma lista duplamente encadeada. Conforme visto, uma lista encadeada consiste em um conjunto de dados/ endereço. Tal conjunto é definido por uma estrutura que armazena um dado, ou con junto de dados e, principalmente, o endereço

Nota do DevMan 1 Listas Dinâmicas

Para uma exemplificação de listas dinâmicas, podemos imaginar um conjunto de dados que desejamos armazenar informações relativas a clientes de uma loja. Tal conjunto pode ser muito variável, neste caso fica impossível criar um conjunto de variáveis, pois não sabemos ao certo qual a quantidade de clientes. Outro exemplo para uma lista dinâmica: podemos indicar um cupom fiscal de uma loja qualquer, podemos imaginar a criação de 100 espaços, mas se alguém comprar 101? Neste caso, é melhor criar 1000 espaços, mas isso seria desperdício, levando-se em conta que a quantidade de itens nem sempre será tão alta. Figura 3. Operação de inserção em uma lista simplesmente encadeada.


para o próximo (ou endereço para anterior e próximo no caso de listas duplamente encadeadas). O último conjunto (dado/endereço) tem o endereço nulo (zero). A inserção de valores neste tipo de estrutura pode ser feita no início, no fim ou em uma posição central, no entanto, uma posição central demanda mais modificações. Visto o conceito sobre listas, percebemos que uma desvantagem surge: manter e controlar o “endereço para o próximo”, ou no caso de listas duplamente encadeadas, manter e controlar o “endereço para o próximo” e “endereço para o anterior”. Realmente esta é uma desvantagem deste tipo de organização. Quando se trata de uma quantidade pequena de dados, uma alocação estática pode ser a melhor solução.

Figura 4. Ilustração de uma lista circular

Figura 5. Ilustração de uma lista duplamente encadeada

Pilhas e Filas Além de listas temos também as pilhas e filas, que são estruturas semelhantes a listas, porém com algumas limitações. Pilhas podem apenas receber conjuntos novos no topo e a retirada de conjuntos pode ser feita apenas no topo. Este tipo de estrutura também é chamado de FILO (first in, last out – primeiro a entrar, último a sair). Já as filas obedecem à organização de permitir a saída apenas na ordem que os conjuntos foram inseridos. Estas estruturas também são chamadas de FIFO (first in, first out – primeiro a entrar, primeiro a sair). Alocação dinâmica e a linguagem C ++ Em C++ temos o new que cria objetos (array pode ser criado com new) e delete que deleta objetos criados por new. Apesar do C++ aceitar malloc e new , não devemos misturar os dois estilos. O espaço alocado por malloc não pode ser liberado por delete; os objetos criados por new não podem ser deletados por free. A linguagem C++ prevê que muitos programadores utilizem programas desenvolvidos em C, por isso malloc e free podem ser utilizados, no entanto, duas outras funções são oferecidas para o uso de memória: calloc e realloc. Neste contexto, a classe Vector do C++ pode colaborar muito, pois permite alocação dinâmica de posições de memória para

Listagem 1. Criando um vetor com tamanho indefinido

vector idades; //cria um vetor de inteiros de tamanho indefinido int valor; char op; do{ cout<<”digite uma idade”; cin>>valor; idades.push_back(valor); //insere o valor no fim do vetor fflush(stdin); cout<<”deseja digitar uma idade? (s/n)”; cin>>op; }while (op==’s’); cout<<”Foram digitadas “ <
int valores[]= new int[12];

armazenamento de valores na memória. Na Listagem 1 temos um exemplo de alocação dinâmica de memória em C++. Perceba que inicialmente criamos um vetor de inteiros chamado idades. Na sequência, inserimos um conjunto de valores nele e, por fim, imprimimos na tela as idades que foram cadastradas no vetor. Na linguagem C++ existem os três tipos de alocação de memória que foram citados anteriormente: listas, pilhas, filas e, também, árvores. Todos eles exigem que o programador libere a memória após o uso. Além disso, temos também recursos de manipulação de dados, que possibilitam tarefas comuns, tais como: criar coleções de

dados, adicionar elementos a essas coleções, remover, alterar, procurar por elementos, ordenar uma sequência qualquer, etc.

Alocação dinâmica e a linguagem Java Em Java temos recursos para manipulação de dados, tais como Vector e Hastable. A partir do Java 1.2 o framework Collections oferece ArrayList e HashMap. No Java 1.4 temos o List , LinkedList , LinkedHashMap , LinkedHashSet. No Java 5 vieram as classes EnumSet , EnumMap , Queue e blockingQueue.

Analisando o “array” no Java, vemos que basta indicar o tipo e tamanho do mesmo, conforme mostra a Listagem 2. Edição 101 SQL Magazine 65 •

Trabalhando com listas dinâmicas

O “array” valores proposto na Listagem 2 permite o armazenamento de 12 inteiros. Desta forma, temos um conjunto de dados onde cada um deles tem um endereço identificado por um número, de forma bem simples. Com relação à quantidade de memória ocupada pelo “array”, temos que um inteiro em Java ocupa 4 bytes (em C e C++ isso ocorre da mesma forma, 4 bytes para inteiros, indicando que cada inteiro ocupa 4 x 8(bits) = de 32 bits, o que significa valores de -2147483648 até 2147483647), portanto o array “valores” ocupa 12 x 4 bytes = 48 bytes. Caso desejássemos acessar o primeiro valor do conjunto valores, deveríamos nos referenciar assim: valores[0], já o último elemento seria valores[11]. Vemos que este tipo de estrutura não deve ser utilizada para armazenar dados cuja quantidade de elementos é um valor desconhecido, pois é obrigatório indicar o tamanho da estrutura na sua criação e não pode ser modif icado no decorrer do programa, nem mesmo durante sua utilização. Para armazenamento de valores de tabelas é possível utilizar “arrays” bidimensionais, mas isso também não é uma boa prática. A partir do Java J2SE 5.0 podemos contar com listas de argumentos de comprimento variado. Este recurso permite criar um método que receba uma quantidade de valores indefinida e armazene-os na ordem desejada. Tal recurso pode auxiliar no armazenamento temporário de resultados de consultas. Outro recurso que pode ser utilizado para armazenar dados é o List. List é uma coleção (da classe Collection) que oferece recursos de acessar e manipular elementos, no nosso caso resultados do banco ou dados que serão levados para o banco. List consiste de ArrayList , Linkedlist e Vector. O uso de ArrayList ou Vector , atende à necessidade de cuidado com memória, pois tais elementos consistem de conjuntos redimensionáveis. ArrayList é mais rápido do que Vector pois não sincroniza seus estados entre as várias threads. Entretanto, o mais adequado mesmo é utilizar a estrutura linkedList. Já na utilização de Vector é importante definir corretamente seus parâmetros, pois por padrão este aumenta de tamanho pelo seu dobro quando precisar de mais espaço de armazenamento. O valor em dobro pode significar um aumento muito grande quando se trata de uma estrutura com muitos dados.

Vantagens e desvantagens Deixar a cargo do usuário a responsabilidade de liberar memória quando ele terminar de utilizar pode ocasionar erros como, por exemplo, liberar um local de memória antes que tenha realmente finalizado a utilização do mesmo. Acessar um local de memória que foi deletado ou liberado irá causar um erro de execução ou violação de memória ou, ainda será acessado um local que está sendo usado para um propósito completamente distinto. O problema também ocorre se um espaço não for liberado por esquecimento. Caso isso ocorra, o usuário do programa será prejudicado, pois alguns benefícios da alocação dinâmica serão perdidos. Em algumas circunstâncias, por exemplo, em casos de acesso a memória virtual, isso pode ocasionar lentidão no sistema. 66 SQL Magazine Edição 101 •

Por fim, é importante estar atento ao fato de que a alocação de memória ocorre reservando área de memória que não é contínua (um ao lado do outro), isto é, cada elemento do conjunto não fica sequencialmente na memória, o que implica em um processamento diferente caso estivessem dispostos de forma contígua na memória. Como citato anteriormente, a alocação dinâmica de memória é feita nas partes livres da memória, que nem sempre estão dispostas sequencialmente, o que pode gerar o fragmentação de memória.

Conclusões A organização dos resultados de consultas feitas em bancos de dados, ou mesmo em arquivos texto, xml, pdf etc muitas vezes exige alocação de memória do sistema em quantidades desconhecidas. Nestes casos, devemos alocar memória dinamicamente. Esta técnica permite utili zar recursos de hardware de forma organizada e prudente, evitando utilização desnecessária. Porém, em casos onde se saiba previamente que a qu antidade de memória não vai variar muito nem precisa de valores a ltos, deve ser adota a alocação estática mesmo, pois está oferece maior velocidade de acesso e ordenação, já que cada elemento é acessado diretamente e os mesmos estão em áreas sequenciais na memória. Como vimos, a vantagem de alocação dinâmica consiste em poder aumentar e reduzir o tamanho ocupado de memória na media em que o programa for utilizado. Isto está diretamente associado a sua principal vantagem que é impedir o desperdício de memória alocada desnecessariamente. A desvantagem, por outro lado, está no fato de que os dados nas organizações dinâmicas não se encontram armazenados sequencialmente na memória, ao contrário dos dados armazenados em “array” de tamanho fixo (estático).

Marcelo Josué Telles

[email protected]

Graduado em Licenciatura da Computação pela Universidade Feevale, no ano de 2007 e é Especialista em informática na Educação pela UFRGS. Cursou disciplinas do Programa de Pós Graduação em Computação na UFRGS (PPGC-UFRGS), como aluno especial. Atua como professor desde 2008. Atualmente leciona na Escola de Educação Profissional Olímpio, Escola de Educação Básica Feevale Escola de Aplicação e no Colégio Sinodal. Leciona as disciplinas de lógica de programação, linguagens de programação, eletrônica para computação, sistemas operacionais, bancos de dados e robótica. Trabalha com desenvolvimento de software nas linguagens PHP, Java e Visual Basic.




SQL Magazine 101 - Modelagem de Dados

Recommend Documents