Baixe Banco de Dados Relacional e Linguagem SQL e outras Notas de estudo em PDF para Computação avançada e segurança de rede, somente na Docsity!
Banco de Dados Relacional
O Modelo de Dados relacional representa os dados contidos em um Banco de Dados através de relações. Estas relações contém informações sobre as entidades representadas e seus relacionamentos. O Modelo Relacional, é claramente baseado no conceito de matrizes, onde as chamadas linhas (das matrizes) seriam os registros e as colunas (das matrizes) seriam os campos. Os nomes das tabelas e dos campos são de fundamental importância para nossa compreensão entre o que estamos armazenando, onde estamos armazenando e qual a relação existente entre os dados armazenados.
Cada linha de nossa relação será chamada de TUPLA e cada coluna de nossa relação será chamada de ATRIBUTO. O conjunto de valores passíveis de serem assumidos por um atribruto, será intitulado de DOMÍNIO.
Estes tópicos serão estudados cuidadosamente na disciplina Análise de Sistemas, que se incumbirá de apresentar cuidadosamente regras e normas para elaboração destes modelos.
Em nosso curso, voltado à construção prática dos Bancos de Dados, e não de sua construção teóricas, apenas citaremos os aspectos básicos da construção teórica, de forma a facilitar ao estudante o relacionamento que existe entre Análise de Sistemas e Banco de Dados (uma das sub-disciplinas de Tópicos Avançados).
O domínio consiste de um grupo de valores atômicos a partir dos quais um ou mais atributos retiram seus valores reais. Assim sendo Rio de Janeiro, Paraná e Pará são estados válidos para o Brasil, enquanto que Corrientes não é um estado válido (pertence a Argentina e não ao Brasil).
O esquema de uma relação, nada mais são que os campos (colunas) existentes em uma tabela. Já a instância da relação consiste no conjunto de valores que cada atributo assume em um determinado instante. Portanto, os dados armazenados no Banco de Dados, são formados pelas instâncias das relações.
As relações não podem ser duplicadas (não podem existir dois estados do Pará, no conjunto de estados brasileiros, por exemplo), a ordem de entrada de dados no Banco de Dados não deverá ter qualquer importância para as relações, no que concerne ao seu tratamento. Os atributos deverão ser atômicos, isto é, não são íveis de novas divisões.
Chamaremos de Chave Primária ao Atributo que definir um resgistro, dentre uma coleção de registros. Chave Secundária (Terceária, etc), serão chaves que possibilitarão pesquisas ou ordenações alternativas, ou seja, diferentes da ordem criada a partir da chave primária ou da ordenação natural (física) da tabela. Chamaremos de Chave Composta, aquela chave que contém mais de um atributo (Por exemplo um cadastro ordenado alfabéticamente por Estado, Cidade e Nome do Cliente, necessitaria de uma chave composta que contivesse estes três atributos). Chamaremos de Chave Estrangeira, aquela chave que permitir a ligação lógica entre uma tabela (onde ela se encontra) com outra na qual ele é chave primária.
Exemplo:
Cidade Estado
- CidCodi * EstCodi CidNome EstNome EstCodi (E)
CidCodi e EstCodi, são chaves primárias respectivamente das tabelas Cidade e Estado, enquanto EstCodi é chave estrangeira na tabela de cidades. É precisamente por este campo (atributo, ou coluna), que será estabelecida a relação entre as tabelas Cidade-->Estado.
Forma Normal
A disciplina Análise de Sistemas abordará detalhadamente esta importante metodologia para definição das tabelas que irão compor a base de dados, que aqui apenas citaremos.
Primeira Forma Normal: Uma relação se encontra na primeira forma normal se todos os domínios de atributos possuem apenas valores atômicos (simples e indivisíveis), e que os valores de cada atributo na tupla seja um valor simples. Assim sendo todos os atributos compostos devem ser divididos em atributos atômicos.
Segunda Forma Normal: Uma relação se encontra na segunda forma normal quando estiver na primeira forma normal e todos os atributos que não participam da chave primária são dependentes desta. Assim devemos verificar se todos os atributos são dependentes da chave primária e retirar-se da relação todos os atributos de um grupo não dependente que dará origem a uma nova relação, que conterá esse atributo como não chave. Desta maneira, na segunda forma normal evita inconsistências devido a duplicidades.
Terceira Forma Normal: Uma relação estará na terceira forma normal, quando estiver na primeira forma norma e todos os atributos que não participam da chave primária são dependentes desta porém não transitivos. Assim devemos verificar se existe um atributo que não depende diretamente da chave, retirá-lo criando uma nova relação que conterá esse grupo de atributos, e defina com a chave, os atributos dos quais esse grupo depende diretamente.
O processo de normalização deve ser aplicado em uma relação por vez, pois durante o processo de normalização vamos obtendo quebras, e por conseguinte, novas relações. No momento em que o sistema estiver satisfatório, do ponto de vista do analista, este processo iterativo é interrompido. De fato existem literaturas indicando quarta, quinta formas normais, que não nos parece tão importante, nem mesmo academicamente.
A normalização para formas apoiadas em dependências funcionais evita inconsistências, usando para isso a própria construção da Base. Se a mesma consistência for passível de ser garantida pelo aplicativo, a normalização pode ser evitada com ganhos reais no desempenho das pesquisas. No caso da consistência não ser importante, também podemos não normalizar totalmente uma Base de Dados.
Exemplo: Normalizar os seguintes atributos:
Nº do Pedido, Nome do Cliente, Nome dos Produtos, Quantidades
Nº do Pedido, Código do Cliente, Nome dos Produtos, Quantidades Código do Cliente, Nome do Cliente
Nº do Pedido, Código do Cliente, Código dos Produtos, Quantidades Código do Cliente, Nome do Cliente Código do Produto, Nome do Produto
Nº do Pedido, Código do Cliente Código do Cliente, Nome do Cliente Código do Produto, Nome do Produto Nº do Pedido, Código do Produto, Quantidade
SQL - Structured Query Language
Introdução
Quando os Bancos de Dados Relacionais estavam sendo desenvolvidos, foram criadas linguagens destinadas à sua manipulação. O Departamento de Pesquisas da IBM, desenvolveu a SQL como forma de interface para o sistema de BD relacional denominado SYSTEM R, início dos anos 70. Em 1986 o American National Standard Institute ( ANSI ), publicou um padrão SQL.
A SQL estabeleceu-se como linguagem padrão de Banco de Dados Relacional.
SQL apresenta uma série de comandos que permitem a definição dos dados, chamada de DDL (Data Definition Language), composta entre outros pelos comandos Create, que é destinado a criação do Banco de Dados, das Tabelas que o compõe, além das relações existentes entre as tabelas. Como exemplo de comandos da classe DDL temos os comandos Create, Alter e Drop.
Os comandos da série DML (Data Manipulation Language), destinados a consultas, inserções, exclusões e alterações em um ou mais registros de uma ou mais tabelas de maneira simultânea. Como exemplo de comandos da classe DML temos os comandos Select, Insert, Update e Delete.
Uma subclasse de comandos DML, a DCL (Data Control Language), dispõe de comandos de controle como Grant e Revoke.
A Linguagem SQL tem como grandes virtudes sua capacidade de gerenciar índices, sem a necessidade de controle individualizado de índice corrente, algo muito comum nas linguagens de manipulação de dados do tipo registro a registro. Outra característica muito importante disponível em SQL é sua capacidade de construção de visões, que são formas de visualizarmos os dados na forma de listagens independente das tabelas e organização lógica dos dados.
Outra característica interessante na linguagem SQL é a capacidade que dispomos de cancelar uma série de atualizações ou de as gravarmos, depois de iniciarmos uma seqüência de atualizações. Os comandos Commit e Rollback são responsáveis por estas facilidades.
Devemos notar que a linguagem SQL consegue implementar estas soluções, somente pelo fato de estar baseada em Banco de Dados, que garantem por si mesmo a integridade das relações existentes entre as tabelas e seus índices.
O Ambiente SQL
Dispomos na Ibero de dois softwares destinados a linguagem SQL o ISQL e o WinSQL.
O ISQL faz parte do pacote Ideo e permite construirmos Banco de Dados e tabelas diretamente pelo interpretador SQL, bem como acessarmos as Bases de Dados construídas no Ideo. O ISQL pode gerar Banco de Dados em seu ambiente proprietário (Watcom, hoje pertencente a Symantech) ou ainda nos consagrados Banco de Dados Oracle, SyBase, Ingres (Computer Associates), DB/2 (IBM) e Informix. Devido as origens do Ideo, o Banco de Dados SQL Server da Microsoft não é suportado, pois este Banco de Dados originou-se na microinformática e somente recentemente a Sapiens migrou seu software dos Ambientes Mainframe e Unix.
Já o WinSQL é um ambiente inteiramente gráfico (ao contrário do ISQL que guarda fortes características do ambiente em Mainframe onde se originou), destinado ao apredinzado, portanto somente pode criar Banco de Dados em seu formato proprietário.
Os comandos do WinSQL por serem visuais, não necessitam de maior esclarecimento além daqueles já contidos no Help. Já o ISQL apesar de possuir um Help bastante completo necessita, em nosso entender, de alguns esclarecimentos iniciais.
Uma série de comandos do interpretador, que funciona de forma análoga àquela existente no dBase modo interativo, podem ser utilizados pelo usuário. Não obstante alguns comandos tenham nome idêntico a alguns comandos do DOS, devemos notar que muitas vezes sua sintaxe é bastante diversa daquele sistema operacional. Vamos destacar os seguintes comandos:
\EDIT - Carrega o editor de bloco de notas do windows, o qual serve para a criação de arquivos para serem executados no Ideo. Ex: \edit teste.sql
\CD - Mostra o diretório onde serão gravados os arquivos *.sql, *.dic *.dat. Permite alterar para determinado diretório (\CD DADO, fará com que o diretório corrente passe a ser C:\DADO, caso o diretório corrente fosse a raiz. Permite retornar ao diretório de nível inferior (\CD ..). Atenção este comando não é análogo ao Change Dir do DOS, na medida em que não permite a mudança direta de um subnível do diretório X para um diretório Y por exemplo.
\DEFAULT - permite alterarmos o drive corrente. Ex: \DEFAULT F:
\INCLUDE - Executa arquivos *.sql. O arquivo .sql deverá conter uma série de instruções SQL. Ex: \include teste.sql
@< file > ; - Também executa arquivos *.sql. Ex: @teste.sql;
EXIT; - Finaliza a sessão do ISQL. ou ( \QUIT )
COMMIT; - Confirma a transação.
ROLLBACK; - Desfaz a transação.
SHOW ; - Mostra os nomes das tabelas existentes em determinado banco de dados. Ex: SHOW tables;
SHOW FIELDS FOR ; - Mostra os campos de determinada tabela. Ex: SHOW FIELDS FOR ATOR;
SHOW INDEXES FOR ; - Lista de indices da tabela.
SHOW RELATIONSHIPS FOR ; - Lista de relacionamentos da tabela.
LIST ; - Lista conteúdo da tabela.
PARTE I - Comandos de Modificações do Esquema e Criação de Banco de Dados
Comando Create Este comando permite a criação de tabelas no banco de dados ou mesmo de sua criação.
Sintaxe: CREATE DATABASE < nome_db >;
onde: nome_db - indica o nome do Banco de Dados a ser criado.
Sintaxe: CREATE TABLE < nome_tabela > ( nome_atributo1 < tipo > [ NOT NULL ], nome_atributo2 < tipo > [ NOT NULL ], ...... nome_atributoN < tipo > [ NOT NULL ] ) ;
onde: nome_table - indica o nome da tabela a ser criada. nome_atributo - indica o nome do campo a ser criado na tabela. tipo - indica a definição do tipo de atributo ( integer(n), char(n), real(n,m), date... ). n- número de dígitos ou de caracteres m- número de casas decimais
Agora vamos criar uma tabela. Use o editor para salvar em um arquivo ou digite na linha de comando do ISQL.
CREATE DATABASE TRABALHO;
O comando acima criou um Banco de Dados, porém este na verdade não passa de uma abertura no diretório, pois não conta com nenhuma tabela.
Agora criaremos as tabelas que estarão contidas no Banco de Dados TRABALHO.
A primeira Tabela será a de Departamentos (DEPT). Esta tabela conterá além dos campos também sua chave primária, suas chaves estrangeiras e também seus índices. A segunda tabela será a de Empregados (EMP), que também será criada.
Não devemos esquecer de primeiramente abrirmos o Banco de Dados. Diferentemente do que ocorre em alguns aplicativos, em SQL o fato de criarmos um Banco de Dados, não significa que o banco recém criado já está preparado para utilização. A instrução a seguir, providencia a abertura do Banco de Dados criado.
OPEN DATABASE TRABALHO;
Agora estamos prontos para criarmos as tabelas necessárias. Lembramos aos Estudantes, que o Arquivo TABS.SQL, contém todas as instruções necessárias para criação do Banco de Dados Trabalho e de suas tabelas. Já o Arquivo DADOS.SQL irá popular estas tabelas. Para efeitos didáticos, criamos as tabelas de forma que sua população, em outras palavras os dados, sejam facilmente referenciáveis pelos estudantes. Assim sendo, na tabela de departamentos, contamos com 5 departamentos, cada um deles tendo seu gerente. Todos os “gerentes” tem nomes de cantoras brasileiras (Gal Costa, Marina Lima, etc), todos os “operários” tem nomes de jogadores de futebol, todas as vendedoras tem nomes de jogadoras de volei, todas as balconistas tem nome de jogadoras de basquete e o presidente da empresa exemplo, tem o mesmo nome do presidente do Brasil. Desta forma os testes devem resultar em grupos bastante definidos. Assim se você estiver listando Gerentes e aparecer um homônimo da Ana Paula (jogadora de volei), verifique sua query atentamente, pois muito provavelmente a mesma estará errada.
A seguir código necessário a criação da tabela Departamento e seu índice:
create table Dept (DepNume integer(4) not null, DepNome char(20) not null, DepLoca char(20) not null, DepOrca integer(12,2),
primary key (DepNume) );
create unique index DepNum on Dept (DepNume asc);
Note-se que a chave primária já está definida juntamente com o registro da tabela. A criação do índice, que por razões óbvias deve ser criado após a tabela, naturalmente é um comando totalmente independente do primeiro create, que serviu para criar a tabela e suas característica básicas.
Vamos analisar o código necessário para a criação da tabela de empregados, apresentado a seguir:
create table Emp (EmpNume integer(5) not null, EmpNome char(30) not null, EmpGere integer(5) , EmpServ char(20) , DepNume integer(4) not null, EmpAdmi date not null, EmpSala integer(10,2), EmpComi integer(10,2), primary key (EmpNume), foreign key has (DepNume) references Dept on delete restrict on update cascade );
create unique index EmpNum on Emp (EmpNume asc); create index EmpDep on Emp (DepNume asc);
A Tabela de Empregados não poderia ter sido criada antes da Tabela de Departamento, pois contém uma referência direta àquela tabela. Quando declaramos que DepNume é chave estrangeira, promovemos de fato a ligação do cadastro de empregados como o cadastro de departamentos. Ao restringirmos as exclusões, permitimos a existência de funcionários não alocados a nenhum departamento. Apesar desta prática ser contrária a tese de que devemos possuir apenas tuplas perfeitamente relacionáveis em nossas tabelas, podemos deixar esta pequena abertura, pois um usuário que excluisse inadivertidamente determinado departamento, acabaria por excluir também uma grande quantidade de funcionários, que estivessem ligados a este departamento.
Já a atualização em cascata dos códigos de departamento é uma boa providência, na medida em que teremos, uma vez alterado algum código de departamento, a atualização imediata de todos os funcionários pertencentes ao departamento cujo código foi modificado.
Observações:
1- Observar que os índices são parte intrínseca das tabelas. 2- A integridade relacional é garantida pelo Banco de Dados e não pelo aplicativo. 3- Exclusões ou Alterações em Chaves Primárias, podem acarretar exclusões, anulações ou até mesmo perda de integridade nas tabelas onde esta chave primária existir como chave estrangeira. Portanto é imprescindível muito cuidado quando da elaboração do Banco de Dados. Uma tentação muito comum ao estudante é começar criando as tabelas do Banco de Dados sem prévia Normalização. Este talvez seja o melhor caminho para perder-se tempo em vão, pois quando você terminar de projetar suas telas de entrada de dados, notará "que nada funciona!". Esta será a senha para usar o velho comando DEL do DOS e depois começar tudo de novo ...
Comando Drop
Este comando elimina a definição da tabela, seus dados e referências.
Sintaxe: DROP TABLE < nome_tabela > ;
Ex: DROP TABLE EMP;
Parte II - Comandos de Consulta ao Esquema
Devemos ressaltar que a linguagem SQL é utilizada tanto pelos profissionais responsáveis pelos dados, onde é ressaltada a figura do Administrador do Banco de Dados e dos Analistas de Dados, como também pelos desenvolvedores de Aplicações. Enquanto àqueles estão preocupados com o desempenho, integridade do Banco de Dados e utilizam toda gama de recusos disponíveis no SQL, estes estão preocupados apenas em "transformar dados em informações", portanto para os desenvolvedores costuma-se dizer que conhecer o "select" já basta. Em nosso curso enfatizaremos a importância de TODOS os comandos do SQL, mas sabemos de antemão que os professores responsáveis pelas linguagens IDEO, VB e Delphi, ressaltarão a preponderância da instrução "select", que será apresentada a seguir e não no final do curso de SQL como geralmente acontece, pelo fato de que diversas disciplinas necessitam especificamente deste comando, que passaremos a apresentar:
- Seleção de todas os campos (ou colunas) da tabela de Departamentos.
Resp: SELECT * FROM DEPT;
O exemplo utiliza o coringa "*" para selecionar as colunas na ordem em que foram criadas. A instrução Select, como pudemos observar seleciona um grupo de registros de uma (ou mais) tabela(s). No caso a instrução From nos indica a necessidade de pesquisarmos tais dados apenas na tabela Dept.
Where como base das Restrição de tuplas.
A cláusula "where" corresponde ao operador restrição da álgebra relacional. Contém a condição que as tuplas devem obedecer a fim de serem listadas. Ela pode comparar valores em colunas, literais, expressões aritméticas ou funções.
A seguir apresentamos operadores lógicos e complementares a serem utilizados nas expressões apresentadas em where.
Operadores lógicos
operador significado = igual a
maior que = maior que ou igual a < menor que <= menor que ou igual a
Exemplos: SELECT EMPNOME, EMPSERV FROM EMP WHERE DEPNUME > 10;
SELECT EMPNOME, EMPSERV FROM EMP WHERE EMPSERV = 'GERENTE';
O conjunto de caracteres ou datas devem estar entre apóstrofes (‘) na cláusula "where".
- Selecione todos os departamentos cujo orçamento mensal seja maior que 100000. Apresente o Nome de tal departamento e seu orçamento anual, que será obtido multiplicando-se o orçamento mensal por 12.
Resp: Neste problema precisamos de uma expressão que é a combinação de um ou mais valores, operadores ou funções que resultarão em um valor. Esta expressão poderá conter nomes de colunas, valores numéricos, constantes e operadores aritméticos.
SELECT DEPNOME, DEPORCA * 12 FROM DEPT WHERE DEPORCA > 100000;
- Apresente a instrução anterior porém ao invés dos "feios" DepNome e DepOrca, os Títulos Departamento e Orçamento.
Resp: Neste exemplo deveremos denominar colunas por apelidos. Os nomes das colunas mostradas por uma consulta, são geralmente os nomes existentes no Dicionário de Dado, porém geralmente estão armazenados na forma do mais puro "informatiquês", onde "todo mundo" sabe que CliCodi significa Código do Cliente. É possível (e provável) que o usuário desconheça estes símbolos, portanto devemos os apresentar dando apelidos às colunas "contaminadas" pelo informatiquês, que apesar de fundamental para os analistas, somente são vistos como enigmas para os usuários.
SELECT DEPNOME "DEPARTAMENTO", DEPORCA * 12 "ORCAMENTO ANUAL" FROM DEPT WHERE DEPORCA > 100000;
- Apresente todos os salários existentes na empresa, porém omita eventuais duplicidades.
Resp: A cláusula Distinct elimina duplicidades, significando que somente relações distintas serão apresentadas como resultado de uma pesquisa.
SELECT DISTINCT EMPSERV FROM EMP;
- Apresente todos os dados dos empregados, considerando sua existência física diferente de sua existência lógica (ou seja devidamente inicializado).
Resp: Desejamos um tratamento diferenciado para valores nulos. Qualquer coluna de uma tupla que não contenha informações é denominada de nula, portanto informação não existente. Isto não é o mesmo que "zero", pois zero é um número como outro qualquer, enquanto que um valor nulo utiliza um "byte" de armazenagem interna e são tratados de forma diferenciada pelo SQL.
SELECT EMPNOME, EMPSALA + EMPCOMI
FROM EMP;
SELECT EMPNOME, NVL(EMPSALA,0) + NVL(EMPCOMI,0)
FROM EMP;
Obs: a função "NVL" é utilizada para converter valores nulos em zeros.
