Privilégio de INSERT | Oracle

Privilégio de INSERT | Oracle

Será que quando se dá o privilégio de INSERT numa tabela a um role isso implicitamente atribui a esse role a possibilidade de "ler" (SELECT) linhas da tabela?

Para começar considere-se a tabela "Data" mostrada na Figura 1 (pertencente ao esquema IMDB) à qual se vai atribuir a autorização de inserção de linhas ao role sousa:

SQL> GRANT INSERT ON "Data" TO sousa;

Figura 1: Tabela Data.

Agora falta testar a hipótese. Para tal entramos como sousa, e:

SQL> SELECT * FROM "Data";

O resultado, como seria de esperar, é uma mensagem de erro:

                              ORA-01031: insufficient privileges
                              01031. 00000 -  "insufficient privileges"

Apesar de em termos organizacionais não fazer grande sentido que alguém que pode registar dados não os consiga ler, de facto os privilégios nos sistemas de bases de dados relacionais cumprem com o objectivo com que foram pensados. O que pode ser facilmente demonstrado digitando o seguinte comando:

SQL> INSERT INTO "Data" ("Chave da Data", "Descrição Completa da Data",
          "Descrição Abreviada da Data", "Século", "Data-SQL")
          VALUES ('5', '5 de Janeiro de 2017', '05/01/2017', 'XXI',
          TO_DATE('2017-01-05', 'YYYY-MM-DD'));

Read More

O talento em bases de dados

Há uma grande procura de profissionais na área das bases de dados, nomeadamente para o ambiente de administração de bases de dados (os denominados DBAs), como se pode rapidamente concluir através de breves pesquisas, utilizando a expressão "base dados", em sites especializados em ofertas de emprego em áreas mais tecnológicas (Figura 1).

Figura 1: Pesquisas para "base dados" nestes três sites em 22/4/2017.

Uma das maiores dificuldades que as empresas, nacionais e internacionais, têm no recrutamento de talentos com formação e/ou experiência nesta área é precisamente a sua escassez. Isso tem provocado uma (pequena) inflação nos salários oferecidos aos profissionais ainda disponíveis.

Como tal, pode ser interessante o investimento pessoal em formação na área das bases de dados, quer seja numa óptica de desenho e construção da base de dados, como na vertente mais complexa relacionada com a administração dos sistemas de bases de dados.

Read More

Lista (enorme) com nomes | Oracle | PostgreSQL | MySQL

Neste link está uma folha de cálculo em Excel com 1 milhão de nomes em português que pode ser usada em múltiplas aplicações, entre elas para preencher tabelas de teste em sistemas de bases de dados relacionais. Na Figura 1 está um pequeno output desse ficheiro.

Figura 1: Exemplo da lista de nomes.

Read More

Geração de Dados | Oracle

Geração de Dados | Oracle

A geração de valores aleatórios em Oracle para valores numéricos e datas, por exemplo, e recorrendo ao utilitário DBMS_RANDOM é fácil e traduz-se em resultados que fazem sentido e que podem posteriormente ser utilizados em testes.

Já a geração de cadeias alfanuméricas recorrendo aquele utilitário resulta em valores sem utilização prática. Acresce ainda que muitas vezes os testes de performance a bases de dados têm que ser feitos utilizando tabelas interligadas entre si por chaves estrangeiras. Ora isso não fazível recorrendo a strings do tipo ‘wqT45dfreDSkutRR’.

Então a melhor forma de ultrapassar esta deficiência é utilizar tabelas de lookup em que inserimos listas com possíveis valores, por exemplo, para nomes de pessoas, ou denominação de localidades ou países.

Figura 1: A tabela Colaborador.

A Query 1 que nos vai servir de caso de estudo devolve três campos: “Número de Funcionário”, “Nome” e “Data de Entrada”, em que para o primeiro e último campo pode-se aplicar o pacote DBMS_RANDOM mas em que o “Nome” será gerado a partir da coluna “Nome” da tabela “Colaborador” (Figura 1).

Query 1: Geração de valores aleatórios

SELECT "Número de Funcionário", "Nome", "Data de Entrada"

  FROM (SELECT TRUNC(DBMS_RANDOM.VALUE (100, 1000)) "Número de Funcionário",

          SYSDATE + DBMS_RANDOM.VALUE (-365* 15, -1) "Data de Entrada"

        FROM lamy

      CONNECT BY LEVEL <= 100) tabela1

    LEFT OUTER JOIN (SELECT DISTINCT "Nome" FROM "Colaborador"

        ORDER BY DBMS_RANDOM.VALUE) tabela2 ON 1 = 1

ORDER BY DBMS_RANDOM.VALUE;

Conjunto resultado:

Figura 2: Resultado da geração de valores aleatórios para a coluna Nome.

Read More

Regras para o desenho de tabelas | Parte 3

Depois de vermos a importância das chaves primárias, na terceira parte das regras para o desenho de tabelas vamos ver como elas se relacionam entre si. É essa associação entre os dados armazenados em duas ou mais tabelas que dá forma a uma base de dados relacional. Um grupo de tabelas isoladas, como que ilhas - sem vista entre si - num vasto oceano, não passa de uma tulha de dados com pouco ou nenhum interesse para os processos de tomada de decisão.

Como se faz então essa associação entre tabelas?

Essa conexão faz-se através do que se designa como chave estrangeira, que consiste numa ou mais colunas de uma tabela que são provenientes de outra tabela, conhecida como tabela-pai ou tabela de origem. São essas colunas em comum que permitem relacionar os dados entre si. Uma chave estrangeira diz-se simples quando consiste numa única coluna, e composta quando é formada por duas ou mais colunas.

No esquema Cinema retratado na Figura 1 a tabela Actores do Filme tem duas chaves estrangeiras:

• a coluna Filme proveniente da tabela Filme e da coluna Título do Filme, em notação técnica seria Filme.Título do Filme, i.e., nome_da_tabela.nome_da_coluna;
• e a coluna Actor com origem na coluna Nome do Actor da tabela Actor.

Figura 1: Esquema Cinema.

Então a tabela Actores do Filme tem duas chaves estrangeiras (simples). Por sua vez o conjunto destas duas colunas constitui a chave primária da tabela, ou seja, são os valores simultâneos nas duas colunas os únicos que asseguram a regra da integridade da tabela. Caso se escolhesse, e.g., a coluna Filme como chave primária então haveria apenas a hipótese de cada actor durante toda a sua vida artística participar numa única película. Por outro lado, e considerando o último exemplo, sendo as chaves primárias das tabelas Actores do Filme e Filme as mesmas então isso implicaria que seriam uma e a mesma tabela.

Read More

Regras para o desenho de tabelas | Parte 2

É muito comum que, no momento de declarar a chave primária de uma tabela, não haja a mínima preocupação com a compreensão do seu contexto no âmbito do processo de negócio, e seja atribuída uma chave primária com valor abstracto e com o formato numérico. Veja-se o exemplo da tabela Género da Figura 1 em que se utiliza o campo GéneroID como chave primária e.g., o género "Comédia" tem o código "11". 

Figura 1: Tabela Género com uma chave primária abstracta "GéneroID".

Mas na realidade no sistema real não existe nenhuma classificação numérica para os géneros dos filmes. No mundo do cinema as comédias não têm um código "11". Em todas as situações em que num sistema não existam códigos identificadores verdadeiros/reais então o mesmo tem que acontecer nas bases de dados que os modelam. Assim, a tabela Género correctamente desenhada, terá apenas uma coluna a qual será obviamente igualmente chave primária (Figura 2).

Figura 2:  Tabela Género num formato normalizado.

Os sistemas não são todos iguais e há alguns em que existem conjuntos de entidades em que os seus elementos se diferenciam por códigos, ou seja, por valores numéricos ou alfa numéricos, que são reconhecidos por todos os participantes do sistema, Por exemplo, os alunos têm números, aos contribuintes são-lhes atribuídos números de identificação fiscal, ou cada viatura automóvel tem a sua própria matrícula.

Read More

Regras para o desenho de tabelas | Parte 1

Para que cada linha de uma tabela tenha utilidade ela tem que ter uma identidade própria, quer dizer, deve ser diferente de todas as outras linhas. A distinção entre as linhas faz-se utilizando o conjunto mínimo de colunas que contenham os valores necessários e suficientes para tornar única cada linha. São os valores contidos nesse conjunto mínimo que distinguem as linhas entre si. No caso da tabela Filme da Figura 1 a coluna Título do Filme não é suficiente para distinguir os filmes entre si pois basta uma análise rápida para se encontrarem películas com a mesma denominação, e.g., "King Kong" aparece duas vezes e, caso fosse essa coluna identificadora então a tabela já teria linhas duplicadas.

Figura 1: Tabela Filme.

Ora a duplicação de linhas é aquilo que nunca se quer numa base de dados relacional. Assim, a coluna Título do Filme não satisfaz a condição do conjunto mínimo, há que que juntar pelo menos mais uma para que se consiga atingir a unicidade das linhas. Isso depende sempre do contexto do processo de negócio que se está a analisar. No caso do esquema Cinema poderia aplicar-se optar-se entre duas colunas:

1. Realizador;
2. Ano de Produção.

A opção 1) não é completamente satisfatória pois nada obsta a que um realizador trabalhe em duas películas distintas mas com a mesma denominação ao longo da sua vida. Já a opção 2) parece satisfazer a regra da unicidade pois é quase impossível que sejam produzidos dois filmes no mesmo ano e com o mesmo título. A regra da unicidade que se designa tecnicamente como chave primária (CP) será assegurada neste caso em simultâneo pelas colunas Título do Filme e Ano de Produção (Figura 2).

Figura 2: Tabela Filme com chave primária (CP).

A unicidade é tecnicamente designada como a Integridade da tabela e, implica que em Filme possam existir muitos filmes denominados "King Kong" mas apenas um por cada ano de calendário. São possíveis muitos filmes em cada ano desde que tenham títulos diferentes. Já não é possível registar duas vezes a película "Apocalyse Now" para o ano de "1979". Dai designar-se esta medida de segurança dos dados como uma Regra de Integridade.

Read More

Representação de dados numa base de dados

Uma tabela típica num base de dados relacional (BDr) contém dados sobre um único tema, ou objecto-informativo, e tem uma denominação unívoca no esquema em que está incluída. Já um esquema é uma representação de um processo de negócio. Por exemplo, o esquema da Figura 1 denomina-se Cinema e é composto pelas tabelas Filme, Actor e Actores do Filme; neste esquema só pode haver uma tabela Actor.

Figura 1: Esquema Cinema.

Uma tabela tem que ser constituída por pelo menos uma coluna, e uma coluna é um conjunto de valores de um certo tipo de dados: nomes, números de telefone, datas, entre muitas outras possibilidades. Numa tabela não podem coexistir colunas com o mesmo nome. Por outro lado, tanto quanto possível essa denominação deve explicitamente indicar qual o tipo de dados tratados na coluna.

Figura 2: Tabela Filme.

A tabela Filme (Figura 2) tem quatro colunas: uma para o nome do filme (Título do Filme), outra para a duração em minutos (Duração), uma outra para registar o nome do realizador (Realizador) e, finalmente, uma para o ano de produção do filme (Ano de Produção).

Na Figura 2, tabela Filme, o título do filme "Gone with the Wind" é uma peça de informação atómica, ou simplesmente, uma peça de informação. Por sua vez cada linha é um objecto distinto dentro da tabela, ou dito de outro modo, cada linha é irrepetível. Ou seja, a linha da tabela Filme em que o título é "King Kong" e o ano de produção "1933" é um conjunto único de dados.

Regra geral a intercepção de uma coluna com uma linha origina sempre um valor, mas pode acontecer que ele não exista, como no caso da coluna Duração no filme "Apocalypse Now", e nesse caso a coluna tem o valor de NULL. O valor NULL não é zero, nem um espaço em branco, é pura e simplesmente a ausência de um valor. Numa BDr sempre que não se conhece o valor para uma coluna não se deve usar nenhum dos truques clássicos da informática como, por exemplo, preencher com "-", ou "_". É sempre preferível deixar o campo a NULL.

Figura 3: Tabela Filme com valor nulo na coluna Duração.

Read More

A organização e gestão dos dados

A informação numa base de dados relacional está guardada em tabelas que são constituídas por linhas e colunas. As tabelas organizam-se numa base de dados de acordo com um determinado esquema. Cada tabela é um objecto bem definido, com um propósito concreto, e com uma denominação única no esquema.

Uma tabela é normalmente constituída por muitas colunas e linhas mas há casos particulares em que pode existir apenas uma única coluna; ou o inverso também pode ser verdadeiro: uma tabela com várias colunas mas constituída unicamente por uma linha, a qual por sua vez pode estar vazia ou conter dados.

Os dados nas tabelas estão contidos nas linhas e são as colunas que dão contexto a esses dados. Para um determinado conjunto de colunas os valores (nessas colunas) são únicos em cada uma das linhas. É este facto que permite distinguir as linhas entre si. Esse conjunto identificador denomina-se chave primária (CP). O conjunto identificador pode ser singular ou plural, no primeiro caso é formado por uma só coluna - pelo que se diz chave primária simples - no segundo por múltiplas colunas em simultâneo - e então denomina-se chave primária composta. No entanto, quer seja simples ou composta uma tabela tem sempre e unicamente uma chave primária.

Uma base de dados relacional precisa de uma interface especial com o utilizador. essa interface que possibilita quer a simples interacção com os dados, ou as mais complexas tarefas de gestão da informação e da estrutura da base de dados é o Sistema de Gestão de Base de Dados Relacional (SGBDR). Quando se fala do Oracle, MySQL ou SQL Server, por exemplo, está-se a referir a SGBDRs. Todos os modernos e mais utilizados SGBDRs utilizam o SQL (Structured Query Language) como o "caminho" de acesso à informação.

Read More

Componentes de uma base de dados

Uma base de dados, no sentido estrito de "qualquer coisa que informa", e não de um sistema de gestão de base de dados, é composta por tabelas, que por sua vez estão estruturadas em linhas e colunas, e cada linha tem uma identidade própria na tabela.

Cada linha é interceptada por pelo menos uma coluna, e cada um desses pontos de intersecção é uma peça de informação.

Uma base de dados é do tipo relacional quando tem origem no Modelo de Dados Relacional. Ou seja, como um prédio tem plantas e é construído com suporte nesse planeamento, também uma base de dados tem que ser pensada e conceptualizada. E é para isso que servem os modelos de dados: são as plantas das bases de dados. Na prática o qualificativo relacional significa que é possível associar os dados localizados em tabelas distintas através de colunas que lhes sejam comuns.

É esta capacidade de relacionar os dados contidos em tabelas diferentes que é o ponto forte das bases de dados relacionais. Aliás, é essa característica que permite transformar dados (aquilo que está em cada uma das tabelas) em informação. A informação nasce precisamente dessa faculdade de criar informação ao ligar as tabelas entre si.

Como se pode ver na Figura 1 é possível associar os actores que participaram num determinado filme através da tabela Actores do Filme que tem colunas em comum com as tabelas Filme e Actor

Figura 1: Associação entre actores e filmes em que participaram.

Read More

Sistema de Gestão de Base de Dados Relacional | SGBDR

O MySQL tal como o Oracle ou o SQL Server, é um Sistema de Gestão de Base de Dados Relacional, i.e., aquilo que aparentemente é unicamente uma base de dados é muito mais do que isso.

Um SGBDR além de ser composto por dados (aquilo que é mais evidente para os utilizadores sem conhecimentos técnicos) engloba uma série de componentes que permitem e facilitam a entrada e manipulação dos dados, ou dito de outro modo, um sistema de gestão de base de dados é um conjunto de software que gere e controla o acesso à informação propriamente dita.

Read More

MySQL | Dicionário de dados

Uma instalação de MySQL tem por defeito três bases de dados (de sistema): information_schema, mysql e performance_schema. O repositório mysql contém o dicionário de dados do sistema MySQL. Uma cópia, apenas com a função de leitura, é guardada na base de dados information_schema. O dicionário de dados é totalmente constituído por tabelas.

Nunca, em nenhum caso - nem com nenhuma justificação -, se deve alterar directamente as tabelas do dicionário de dados pois isso leva SEMPRE a consequências muito nefastas quer na disponibilidade e/ou quer no mero funcionamento do sistema de base de dados.

Read More

MySQL | Utilizador | Login

A segurança do MySQL avalia os utilizadores consoante as suas coordenadas de origem. A chave de segurança é composta por três parâmetros: nome do utilizador (user name), palavra-chave (password) e computador (host). Na gestão de utilizadores convém ter muita atenção com os valores para host pois frequentemente a entrada de um utilizador é recusada apenas por ter a máquina de origem mal atribuída.

O parâmetro host pode ter três valores:

• localhost. O utilizador apenas pode ligar-se a partir da mesma máquina onde funciona o servidor de MySQL.
• IP/domínio. A ligação somente será efectuada a partir de um determinado IP ou de um domínio.
• %. A ligação pode ser feita a partir de qualquer máquina e/ou domínio. Funciona como um "trunfo", i.e., representa qualquer computador, quer esteja na rede local ou na Internet.

Read More

MySQL | Listener

O listener ("aquele que ouve") é um serviço lançado quando o daemon⁽¹⁾ do MySQL é iniciado. O listener é um subprocesso dependente do próprio servidor da base de dados, e corre ma porta que estiver especificada no ficheiro de configuração mysql.ini. Por defeito, quer seja em Windows ou Linux, por exemplo, está assignado à porta 3306.

Num sistema Windows pode verificar-se se o processo está a correr acedendo ao gestor GUI de serviços, ou utilizando a janela de comandos com a seguinte instrução:

netstat -a | findstr /C:LISTENING | findstr /C:3306

¹ Programa de computador que corre em backgound, i.e., sem controlo directo pelo utilizador.

Read More

MySQL | Motores de base de dados

São os motores (engines) de base de dados que orientam os processos que asseguram a integridade dos dados, o modo como a estrutura física dos dados se organiza e, ainda, os mecanismos que asseguram a associação entre as estruturas lógicas e físicas do sistema.

No MySQL 5.6 existem os seguintes tipos principais de motores:

• InnoDB. Motor que implementa o mecanismo ACID.
• MyISAM. Anteriormente à versão 5.6 era o motor por defeito do MySQL; motor não-transaccional.
• Memory. Um motor que funciona unicamente na memória (física ou virtual).
• Archive. Destina-se ao arquivamento de dados raramente utilizados.

Para ver todos os motores disponíveis pode digitar-se, numa janela de Command Line do MySQL, a seguinte instrução: "SHOW ENGINES;".

Read More

MySQL | Motor InnoDB

O motor InnoDB mantém um conjunto de logs em tudo semelhante ao que faz o Oracle. Esses logs (ou registos) destinam-se a assegurar a integridade das transacções ao mesmo tempo qye optimizam o custo transaccional. Este tipo de motor faz com o MySQL seja uma base de dados tipicamente transaccional. O InnoDB implementa as transacções ACID, o lock a nível da linha, além de assegurar a integridade referencial.

Read More

MySQL 5.6 | Arquitectura | Parte I

O superutilizador/DBA principal em MySQL denomina-se root (em Oracle é o utilizador SYS). Em MySQL uma base de dados é gerida de uma forma lógica e é o SGBDR que trata do armazenamento físico. O trabalho em SQL é controlado pela componente "MySQL Monitor" (em Oracle quem trata disso é o PL/SQL). A partir da versão 5.6 o MySQL já inclui a capacidade de processar XML.

Em MySQL é possível trabalhar com vários motores de base de dados, mas o único que assegura as propriedades transaccionais ACID é o InnoDB. Para além desse aspecto o motor InnoDB é o único que garante a regra de integridade referencial, e do protocolo X/Open XA para o processamento distribuído de transacções. O InnoDB é o motor por defeito em MySQL.

Read More

Duas relações com a mesma chave primária?

Por vezes surgem propostas de modelos de dados em que co-existem duas, por vezes mais, relações com a mesma chave primária (CP). Ora, isso teoricamente é um erro dado que duas relações com uma CP idêntica são uma única relação.

Regra:

Os objectos que tenham uma relação de 1:1 entre si devem manter-se juntos: um aluno tem um número de aluno (não tem dois); um automóvel tem uma matrícula (não tem duas), e por ai fora.

Exemplo:

 

Figura 1: Excerto de modelo de dados

No caso da Figura 1 ninguém poderá afirmar que o desenho esteja correcto. Porquê subdividir algumas características dos alunos por relacções diferentes quando na verdade os atributos têm uma relação de 1:1 com cada aluno.

• Por acaso um aluno têm dois nomes? Não, não tem.
• Será que um aluno têm dois números de bilhete de identidade? Também parece que não...

Então isso significa que entre a relação Aluno e os atributos nome e número de bilhete de identidade há uma associação de 1:1, i.e., um aluno tem um só nome e um único número de bilhete de identidade. Pelo que esses atributos têm que pertencer à mesma relação, têm que estar todos no mesmo conjunto.

A intersecção do conjunto (a relação) Aluno com o conjunto Aluno Identificação não é o conjunto vazio pelo que não são disjuntos, pelo contrário são uma e a mesma coisa. A intersecção dos dois conjuntos é a soma de todos os atributos sem que isso implique qualquer perca de integridade dos factos. Aliás, a integridade e o valor da informação aumentam quando os atributos 1:1 ficam todos no mesmo conjunto.

Conclusão: duas relações com uma CP idêntica são uma única relação.

Read More

Associação 1:1 entre objectos

A associação, ou relação, entre peças de informação é fundamental para no desenho do modelo de dados relacional. Tudo o que tenha o grau 1:1 deve manter-se na mesma relação.

A não observação desta regra leva a que no mesmo modelo de dados possam existir várias relações com a mesma chave primária. O que em si mesmo é uma impossibilidade lógica, pois duas relações com a mesma chave primária são uma e a mesma coisa.

Read More

Media library - Itens

Por vezes surgem em modelos de dados construções particulares que são confusas pois fogem aos estereótipos. No caso do modelo proposta por Williams (2011) há uma secção sobre a tipificação de atributos de suportes de informação como se pode ver na Figura 1.

Figura 1: Tipificação de atributos.

Como se trata de um centro de documentação onde são possíveis tipos muito distintos de elementos como, por exemplo, livros, revistas, DVD, VHS, CD, então é quase impossível ter uma tabela na base de dados onde existam em simultâneo TODOS as colunas necessárias à caracterização de casa item. Uma tabela construída dessa forma além de ter numerosas colunas teria sempre muitas que não seriam preenchidas por não se encaixarem no item a registar.

Assim, Williams (2011) propõe uma construção invulgar que começa pela definição do tipo de dados (Ref_Data_Types), que se liga a Data_Items onde se definem TODAS as características que interessem aos diferentes tipos de media. Esta última por sua vez converge em Library_Data_Items onde se atribuem as caraterísticas a cada um dos Library_Items.

A tabela Data_Item_Values proposta não faz qualquer sentido pois há uma relação de 1:1 entre a característica e o respectivo valor. Ou seja a coluna "Data_Item_Value" deve transitar para a tabela Library_data_Items, e a tabela Data_Item_Values deve ser descartada.

Read More