Categoria: Escola de Base de Dados & SQL

Privilégio de INSERT | Oracle

Leave a comment

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’));

Geração de Dados | Oracle

Leave a comment

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.

Regras para o desenho de tabelas | Parte 3

Leave a comment
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.

Regras para o desenho de tabelas | Parte 2

Leave a comment
É 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.

Regras para o desenho de tabelas | Parte 1

Leave a comment
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.

Representação de dados numa base de dados

Leave a comment

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.

A organização e gestão dos dados

Leave a comment
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.