Esta seção apresenta exemplos de anomalias de sincronicação e a notação a ser usada. O modelo de processamento de transações adotados.
Anomailias de Sincronização: Todo método de controle de concorrência deve evitar certos problemas, chamados de anomalias de sincronização, que podem resultar do acesso concorrente irrestrito aos dados.
As principais anomalias são:
Perdada consistência do banco; Acesso a dados inconsistentes; perda de atualizações.
Execuções concorrentes serão representados por sequencias de rótulos correpondendo aos comandos que acessam o banco de dados. comandos que não acessam o banco de dados não infuenciam a discussão, sendo, portanto ignorados.
Os valores dos parâmetros das transações são indicados fora da própria sequência de rótulos. Caso haja mais de uma execução da mesma transação, Cada uma das execuções e os rótulos correspondentes serão distinguidos por subcristos.
Modelagem do Sistema: A nível lógico, o banco de dados é descrito por um esquema conceitual global consistendo de um conjunto de objetos lógico. A nível físico, o banco é descrito por uma série de esquemas internos, um para cada nó onde está armazenado; cada esquema interno consiste de um conjunto de objetos fisicos.
O mapeamento do esquema conceitual global para os esquemas internos definem a forma de distribuição do banco e a correspondência entre objetos físicos armazenem cópias dos mesmo dados. Os comandos lógicos são manipulados através de comandos da LMD e os objetos físicos através de ações elemetares. Toda a operação de nivel lógico são traduzidos em sequencias de operações a nivel físico, uma execuçãode um grupo de transações gera, em cada nó onde o banco está armazenado, uma sequencia de ações elementares. A execução de um comando da LMD (operção de nivel lógico) poderá gerar varias ações de lietura para recuperar objetos fisicos que ainda não estão na área de trabalho da transação. Um comando da LMD nunca gerará ações elemenares a alteração do banco de dados não é alterado de imediato. Apenas quando o protocolo bifásico atingir a segunda fase, ações elementares do tipo ação de atualização serão geradas para efetivar a as alterações nos bancos de dados local.
CRITÉRIOS DE CORREÇÃO: Serão considerados critérios pertencentes a três classes distintas: critérios para transações, critérios genéricos para o sistema e critérios especificos para os métodos de controle de concorrência.
METODOS BASEADOS EM BLOQUEIOS: O uso de bloqueios para controle de concorrências em um ambiente centralizado. Incialmente os problemas de gerência de bloqueios e tratamento de bloqueios mútuos são abordados.
Em seguida, um método de uso de bloqueios para atingir apenas execuções serializáveis, chamado de bloqueio em duas fases. Algoritimos para deteção e bloqueios mútuos em um ambiente de banco de dados distribuídos. As implentações diferirão essencialmente no posicionamento da tabela de bloqueios ao longo da rede. O argumento de correção destas implementações segue diretamente da prova de correção do protocolo de bloqueio em duas fease para caso centralizado. PRÉ-ORDENAÇÃO: cada transação ao ser iniciada recebe uma senha senha ou número de protocolo. Há um mecanismo de controle de concorrência local que garante que as ações conflitantes são geradas e processadas em ordem de senha.
O processamento de transação
O processamento de transação é uma referência ao processamento das transações realizada pelo sistema conhecido como monitor de transação ou servidor de transação. Esta operação é realizada em dois passos (pelo menos).
Podem ser considerados como sistemas de nível operacionail. Constituem os Sis mais antigos usados pelas organizações e, embora seu papel se restrinja ao processamento de transaçoes , sao indispensaveis para o sucesso de qualquer empresa , pois da o suporte as operações mais basicas, tais como contas a pagar ea receber, folha de pagamento de pessoal e entrada e saida de mercadorias em estoque, entre outras. Para Turban, "O Sistema de processamento de translação é a espinha dorsal do sistema de informação de uma empresa. Ele monitora, coleta, armazena, processa e dissemina a informaçao para todas as transaçoes rotineiras da empresa".
Podem ser considerados exemplos de transações um debito ou um credito de recurso financeiro na conta de uma empresa e a entrada ou saida deum produto no estoque.
A Transação é qualquer troca de valor ou movimento de mercadoria que afete a lucratividade de uma organização ou seu ganho global, inclusive a realização de metas organizacionais. O pagamento de empregados, o envio de faturas a clientes e o preenchimento de formulários para o governo federal são exemplos de transações para uma organização com fins lucrativos; o recebimento de doações e o financiamento de pesquisas médicas são exemplos de transações feitas por uma organização sem fins lucrativos. O coração da maior parte das organizações empresariais é o processamento de transações, que dá apoio à monitoração e à realização das negociações de uma organização e gera e armazena dados sobre estas negociações. A maioria consiste de banco de dados, são dispositivo de software e hardware usados para processar as transações. Este processamento inclui a coleta de dados de entrada, a realização de cálculos, o armazenamento de informação no banco de dados, e/ou a produção de vários documentos e relatórios empresariais. Os sistemas de processamento de transação permitem que as operações sem agrupadas em uma única transação, garantindo que problemas de consistência de dados não ocorram. Eles garantem que as transações sejam atômicas e que as propriedades ACID sejam alcançadas. Begin, Commit e RollBack. Uma transação pode ser definida como um conjunto de operações (alteração, inclusão, exclusão, atualização, etc.) realizadas nos dados de uma aplicação em um dado momento. Uma transação tem o seu início no início de uma sessão e o seu término no encerramento da sessão (fechamento do programa ou operação).
O inicio de uma nova transação é marcada pela diretiva Begin, o encerramento pela diretiva Commit e o cancelamento da transação pela diretiva RollBack.
O processamento da transação é realizado pelo objeto Workspace definido em uma sessão.
Begin - Inicia uma nova Transação,
Commit - Encerra a transação atual e salva as alterações realizadas durante o processamento.
RollBack - Encerra a transação atual e restaura os dados a sua posição quando do inicio da transação. O processamento de transações permite proteger a integridade dos dados garantindo que múltiplas alterações em uma ou mais tabelas de dados ocorram de forma integrada, permitindo que todas as alterações sejam realizadas ou caso contrário nenhuma alteração ocorra.
Podem ser considerados como sistemas de nível operacionail. Constituem os Sis mais antigos usados pelas organizações e, embora seu papel se restrinja ao processamento de transaçoes , sao indispensaveis para o sucesso de qualquer empresa , pois da o suporte as operações mais basicas, tais como contas a pagar ea receber, folha de pagamento de pessoal e entrada e saida de mercadorias em estoque, entre outras. Para Turban, "O Sistema de processamento de translação é a espinha dorsal do sistema de informação de uma empresa. Ele monitora, coleta, armazena, processa e dissemina a informaçao para todas as transaçoes rotineiras da empresa".
Podem ser considerados exemplos de transações um debito ou um credito de recurso financeiro na conta de uma empresa e a entrada ou saida deum produto no estoque.
A Transação é qualquer troca de valor ou movimento de mercadoria que afete a lucratividade de uma organização ou seu ganho global, inclusive a realização de metas organizacionais. O pagamento de empregados, o envio de faturas a clientes e o preenchimento de formulários para o governo federal são exemplos de transações para uma organização com fins lucrativos; o recebimento de doações e o financiamento de pesquisas médicas são exemplos de transações feitas por uma organização sem fins lucrativos. O coração da maior parte das organizações empresariais é o processamento de transações, que dá apoio à monitoração e à realização das negociações de uma organização e gera e armazena dados sobre estas negociações. A maioria consiste de banco de dados, são dispositivo de software e hardware usados para processar as transações. Este processamento inclui a coleta de dados de entrada, a realização de cálculos, o armazenamento de informação no banco de dados, e/ou a produção de vários documentos e relatórios empresariais. Os sistemas de processamento de transação permitem que as operações sem agrupadas em uma única transação, garantindo que problemas de consistência de dados não ocorram. Eles garantem que as transações sejam atômicas e que as propriedades ACID sejam alcançadas. Begin, Commit e RollBack. Uma transação pode ser definida como um conjunto de operações (alteração, inclusão, exclusão, atualização, etc.) realizadas nos dados de uma aplicação em um dado momento. Uma transação tem o seu início no início de uma sessão e o seu término no encerramento da sessão (fechamento do programa ou operação).
O inicio de uma nova transação é marcada pela diretiva Begin, o encerramento pela diretiva Commit e o cancelamento da transação pela diretiva RollBack.
O processamento da transação é realizado pelo objeto Workspace definido em uma sessão.
Begin - Inicia uma nova Transação,
Commit - Encerra a transação atual e salva as alterações realizadas durante o processamento.
RollBack - Encerra a transação atual e restaura os dados a sua posição quando do inicio da transação. O processamento de transações permite proteger a integridade dos dados garantindo que múltiplas alterações em uma ou mais tabelas de dados ocorram de forma integrada, permitindo que todas as alterações sejam realizadas ou caso contrário nenhuma alteração ocorra.
PADRÃO SQL
O banco de dados relacional usa uma liguagem padrão denomida Structured Query Language – SQL. Desenvolvido pela IBM Corporation, está linguagem foi adaptada nos meados da década de 80. Uma linguagem abilitada e de facilmente manuseio por todos os tipos de usuários finais. O objeito do SQL e fornecer uma interface para um banco de dados relacional, usando todas as sua declarações de trabalho com dados de nivel lógico, definindo o conjuto de linha de uma tabela, definir uma condição que serão recuperadas e poderá ser repassado para o usuário final; O Banco de dados é consistente, controlando todo o ambiente do Banco de Dados relacional, usado para selecionar dados na tabela, manipular dados (inserções, atualiações e exclusões) das tabelas; criar, guardar e arrastar objetos; proteger dados e estruturas (backup e recuperação) e compartilhar dados com outros ambientes do banco de dados relacional. Aplicando seu funcionamento e suas propiedades funcional “Atomicidade, não podendo ser divisível; Consistência, dados estão consistentes e as regras de negócio execultadas e cumpridas; Integridade, isolamento de transação, as regras devem ser cumpridas; Durabilidade, inseridos os dados deve obrigatóriamente ser durável”. Os componetes básicos de um banco de dados relacional: A estrutura de dados relacionais (tabela e índices), as regras que são a organização das estrutuas de dados (restrições) e as operações de criação e manipulação – que podem ser apliada a estrutura de dados (inserções, atualizações e exclusões).
COMANDO CREATE TABLE, é usado para especificar uma nova relação, dando um nome especifico aos seus atributos que são definidos primeiro e suas retrições chaves, as intregridades de entidades e as integridades referenciais.
COMANDO PARA ALTERÇÃO DE ESQUEMAS, nesta seção são as evoluções de um esquemas, que podemos alterar um esquema ou por intermedio da adição ou da eliminação de tabelas, atributos, retrições.
COMANDO DROP (eliminar) usado para eleminar os elementos de esquemas nomeados, como tabelas.
COMANDO ALTER (alterar) a definição de uma coluna e adicionar ou eliminar restrições de tabela.
COMANDO SELECT, recupera o nome e o endereço de todos os dados dentro da tabela ou a tabela.
COMANDOS INSERT (INSERÇÃO), DELETE (EXCLUSÃO) E UPDATE (ATUALIZAÇÃO), COMANDO INSERT usado para adcionar uma tupla em uma relação, os valores devem ser inseridos na mesma ordem em que forma especificado os atributos.
COMANDO DELETE remove as tuplas de uma relação.
COMANDO UPDATE é usado para modificar os valores dos atributos e uma ou mais tuplas.
COMANDO COMMIT OU ROLLBACK, estes comando são para confirmação da transação, neste as propiedades do banco de dados atomicidade, consistência, integridade e durabilidade são execultada e confirmada; o comando ‘commit’ – confirma a transação, o comando ‘roolback’ – desfaz a transação, cancelado toda a transação deixando o banco de dados com as informações anteriores.
COMANDO CREATE TABLE, é usado para especificar uma nova relação, dando um nome especifico aos seus atributos que são definidos primeiro e suas retrições chaves, as intregridades de entidades e as integridades referenciais.
COMANDO PARA ALTERÇÃO DE ESQUEMAS, nesta seção são as evoluções de um esquemas, que podemos alterar um esquema ou por intermedio da adição ou da eliminação de tabelas, atributos, retrições.
COMANDO DROP (eliminar) usado para eleminar os elementos de esquemas nomeados, como tabelas.
COMANDO ALTER (alterar) a definição de uma coluna e adicionar ou eliminar restrições de tabela.
COMANDO SELECT, recupera o nome e o endereço de todos os dados dentro da tabela ou a tabela.
COMANDOS INSERT (INSERÇÃO), DELETE (EXCLUSÃO) E UPDATE (ATUALIZAÇÃO), COMANDO INSERT usado para adcionar uma tupla em uma relação, os valores devem ser inseridos na mesma ordem em que forma especificado os atributos.
COMANDO DELETE remove as tuplas de uma relação.
COMANDO UPDATE é usado para modificar os valores dos atributos e uma ou mais tuplas.
COMANDO COMMIT OU ROLLBACK, estes comando são para confirmação da transação, neste as propiedades do banco de dados atomicidade, consistência, integridade e durabilidade são execultada e confirmada; o comando ‘commit’ – confirma a transação, o comando ‘roolback’ – desfaz a transação, cancelado toda a transação deixando o banco de dados com as informações anteriores.
MODELAGEM RELACIONAL NORMALIZADO – MRN
Foi o primeiro modelo de banco de dados formal. Os bancos de dados hierárquicos e em rede, passaram a ser também descritos em linguagem formal.
O modelo relacional para gerência de bancos de dados é um modelo baseado em lógica e na teoria de conjuntos. a análise dos dados é feita em uma lógica de predicados de dois valores (ou seja, sem o valor nulo); isto significa que existem dois possíveis valores para uma proposição: verdadeira ou falsa. Os dados são tratados pelo cálculo relacional ou álgerbra relacional. O modelo relacional permite ao projetista criar um modelo lógico consistente da informação a ser armazenada. Pode ser refinado através de um processo de normalização. O princípio básico do modelo relacional é o princípio da informação: toda informação é representada por valores em relações (relvars). Assim, as relvars não são relacionadas umas às outras no momento do projeto. Os projetistas utilizam o mesmo dominio em vários relvars, e se um atributo é dependente de outro, esta dependência é garantida através da integridade referencial. A normalização de banco de dados é normalmente realizada quando projeta-se um banco de dados relacional, para melhorar a consistência lógica do projeto do banco de dados e o desempenho transacional. A dependência funcional (DF) é um dos conceitos fundamentais no desenho dos modelos de dados relacionais. A dependência funcional é uma associação que se estabelece entre dois ou mais atributos duma relação e define-se do seguinte modo: Se A e B são atributos, ou conjuntos de atributos, da relação R, diz-se que B é funcionalmente dependente de A se cada um dos valores de A em R tem associado a si um e um só valor de B em R; a DF tem a notação: A – B.
DF Transitiva ou Indireta Se um atributo não-chave Ax possui DF total da CP de uma tabela T e também possui DF total de um ou mais atributos não-chave de T, então diz-se que Ax possui DF transitiva ou indireta da CP de T.
Normalização é o processo de organizar dados numa base de dados. Este processo envolve a criação de tabelas e o estabelecimentos de relações entre essas tabelas, de acordo com regras concebidas para proteger os dados e para tornar a base de dados mais flexível, através da eliminação da redundância e da dependência inconsistente. Os dados redundantes desperdiçam espaço em disco e criam problemas de manutenção. Se é necessário alterar dados que existem em mais do que um local, esses dados têm de ser alterados exactamente do mesmo modo em todos os locais. Uma alteração de morada de um cliente é muito mais fácil de implementar se esses dados estiverem apenas armazenados na tabela Clientes e em mais nenhum local da base de dados. Existem algumas regras para a normalização de bases de dados. Cada regra é chamada "formula normal". Se a primeira regra é respeitada, diz-se que a base de dados está na "primeira formula normal". Se as três primeiras regras são observadas, considera-se que a base de dados está na "terceira formula normal”. Considera-se que a terceira formula normal corresponde ao nível mais alto necessário para a maior parte das aplicações. De um modo geral, a normalização requer mais tabelas e alguns clientes acham este procedimento confuso. Se decidir violar uma das três primeiras regras da normalização, certifique-se de que a sua aplicação antecipa quaisquer problemas que possam ocorrer, tais como a existência de dados redundantes e dependências inconsistentes.
Como definir o banco de dados com SQL
1 Introdução a SQL
O objetivo deste curso é introduzir o aluno na tarefa de manipular bancos de dados através da linguagem
SQL. SQL significa Structured Query Language (linguagem estruturada de consulta). Apesar do nome, fazer
consultas a bancos de dados não é a única função de SQL. Ela é utilizada para criar tabelas, inserir, excluir e
alterar dados do banco de dados, além de outras utilizações. A seguir é mostrado um breve histórico da
linguagem seguido de uma rápida visão de bancos de dados relacionais.
1.1. Um Breve Histórico
A linguagem SQL foi desenvolvida pela IBM em meados dos anos 70 como uma linguagem de manipulação de dados (DML - Data Manipulation Language) para suas primeiras tentativas de desenvolvimento de bancos de dados relacionais. A grande vantagem do SQL sobre modelos de dados anteriores é que as operações realizadas sobre os dados são especificadas numa linguagem não procedural e conjuntos de dados são manipulados com um único comando. Isto faz com que os programadores não tenham de navegar por uma estrutura complexa de banco de dados, reduzindo a quantidade de código necessário para acessar os dados.
A SQL tornou-se de fato o padrão depois de 1986, quando o American National Standards Institute
(ANSI), a organização responsável pelos padrões industriais nos Estados Unidos, endossou o SQL como
linguagem padrão para os bancos de dados relacionais. Desde então, o SQL já sofreu duas atualizações oficiais, uma em 1989, e outra em 1992. A revisão de 1992, SQL-92 (ANSI X3.135-1992) é o padrão usado atualmente, mas desde 1993 há um trabalho sendo desenvolvido para atualizar o padrão de modo que este atenda às características das últimas versões de bancos de dados relacionais lançadas no mercado. O novo padrão SQL chama-se SQL3; o número 3 vem de 1993, o ano em que os trabalhos começaram.
Em 1986, o ANSI publicou o primeiro padrão SQL, X3.135-1986. A International Organization for
Standardization (ISO) pubicou um padrão tecnicamente idêntico, ISO 9075-1987, alguns meses depois em
1987. O padrão representava um denominador comum das implementações SQL existentes e consequentemente deixou de padronizar muitas características populares e necessárias da linguagem. Este padrão continha características de SQL de quatro linguagens de programação: COBOL, FORTRAN, Pascal e PL/I.
Em 1989, tanto ANSI quanto ISO publicaram padrões substitutos (ANSI X3.135-1989 e ISO/IEC
9075:1989) que aumentaram a linguagem e acrescentaram uma capacidade opcional de integridade referencial, permitindo que projetistas de bancos de dados pudessem criar relacionamentos entre dados em diferentes partes do banco de dados. No mesmo ano, o ANSI adicionou ao padrão suporte para duas outras linguagens de programação, Ada e C.
Em 1992, ISO e ANSI publicaram a terceira revisão do padrão SQL, o SQL92 (X3.135-1992 e ISO/IEC
9075:1992). Na mais nova versão, SQL3, a nova característica mais importante é a adição de características
de orientação a objetos na linguagem.
Devido ao sucesso da forma de consultar e manipular dados da SQL, dentro de um ambiente de banco
de dados, a utilização da SQL foi se tornando ainda maior. Uma grande quantidade de SGBDs foi tendo como linguagem básica a SQL, a saber, SQL/DS e DB2 da IBM, ZIM, Oracle, RDB, Sybase, dentre outros. Cabe ressaltar que cada implementação de SQL possui uma adaptação da sintaxe para resolver determinados problemas, portanto, qualquer comando mostrado neste curso pode ser usado de forma diferente em um determinado SGBD. Recomenda-se a leitura do manual do fabricante para maiores informações sobre o uso da linguagem SQL em SGBDs comerciais.
1.2. Banco de Dados Relacional
Para que haja um melhor entendimento da linguagem SQL é necessário abordar a evolução dos bancos
de dados e de sua teoria. Sistemas de bancos de dados relacionais são aqueles baseados no modelo lógico relacional, o qual foi proposto por E. F. Codd em 1970.
A abordagem relacional está baseada no princípio de que as informações em uma base de dados podem
ser consideradas como relações matemáticas e que estão representadas de maneira uniforme, através do uso de tabelas bidimensionais. Todos os dados de um BD relacional estão armazenados em relações (ou tabelas).
Cada tabela possui atributos (ou campos), os quais são armazenados em colunas da tabela. As linhas
guardam informações sobre uma entidade (registro). Alguns atributos da tabela são chave primária da mesma.
Isto significa que não existem duas linhas iguais numa tabela. Cada linha pode ser identificada univocamente
pela sua chave primária (por exemplo matrícula de aluno). A seguir é mostrada a tabela de alunos que tem como
atributos Matrícula, Nome, Endereço e Cod_Curso. A chave primária da tabela é o atributo matrícula.
Alunos
Matrícula Nome Endereço Cod_Curso
9100807 José xxxx 88
9200789 Maria yyy 77
A seguir é mostrada a tabela de cursos com os atributos Cod_Curso e Nome. Cod_Curso é a chave
primária.
Cod_Curso Nome
77 Computação
88 Eng. Civil
Note que cada aluno da tabela de alunos possui um valor de Cod_Curso que existe na tabela de cursos.
Isso significa que se quisermos saber o nome do curso de um aluno, basta sabermos o código do curso do aluno
e procurar pelo mesmo código na tabela de cursos. Quando existe essa ligação entre tabelas, o atributo que faz
a ligação (no caso, Cod_Curso) é considerado chave estrangeira ou chave externa. Isto implica em várias regras
de verificação num SGBD, pois um aluno não pode ter um código de curso que não existe na tabela de cursos,
para citar um exemplo.
Existem várias regras definidas por Codd para caracterizar um SGBD relacional. Em resumo, pode-se
dizer que todos os dados do SGBD devem ser armazenados em tabelas e que a manipulação dessas tabelas
através de inclusão, exclusão, alteração e consultas deve gerar novas tabelas
2 Linguagem SQL
Atualmente, a linguagem SQL assume um papel muito importante nos sistemas de gerenciamento de
banco de dados (SGBDs), podendo ter muitos enfoques. Através de comandos SQL, os usuários podem montar
consultas poderosas sem a necessidade de criação de um programa, ou ainda utilizar comandos SQL embutidos
em programas de aplicação que acessam os dados armazenados. O DBA, pessoa responsável pela administração
de banco de dados pode realizar suas tarefas através de SQL. Da mesma forma, a linguagem pode ser usada
como linguagem cliente/servidor, linguagem para banco de dados distribuídos e caminho de acesso a outros
bancos de dados ou outras plataformas.
Devido ao fato de possuir várias aplicações, a linguagem SQL provê suporte a várias funções de um
SGBD. Ela consiste de:
.
DDL (linguagem de definição de dados), onde os dados a serem armazenados são definidos e
estruturados;
DML (linguagem de manipulação de dados), que permite a inclusão, remoção, seleção ou atualização de
dados armazenados no banco de dados;
.
Controle de acesso, permitindo proteção dos dados de manipulações não autorizadas;
.
Restrições de Integridade, que auxiliam no processo de definição da integridade dos dados, protegendo
contra corrupções, inconsistências e falhas do sistema de computação.
Além dessas características principais, ainda podemos citar o suporte a visões, onde são especificadas
as consultas disponíveis através de tabelas virtuais e especificação de transações, para garantia do
compartilhamento dos dados.
.
2.1. Como definir o banco de dados com SQL
Para definir o banco de dados utilizamos os comandos de definição de dados de SQL, que a transformam
em DDL.
2.1.1 Create Database
O comando create database não está presente em todos as versões de SQL, mas serve para definir um
nome para o banco de dados a ser criado. Por exemplo:
create database controle_bancário
2.1.2 Create Table
O comando create table permite a definição de uma relação ou tabela com restrições de integridade de
entidade e referencial dentro do banco de dados definido. Para isso ele contém as cláusulas:
primary key: indica os atributos que formam a chave primária;
unique key:
indica os atributos que formam a chave candidata;
foreign key:
indica os atributos que formam a chave estrangeira e o nome da relação referida pela
chave estrangeira
Formato do comando create table:
CREATE TABLE nome_tabela
(nome_atributo tipo_de_dado [NOT NULL],...,
primary key (nome_atributo1, nome_atributo2,...)
foreign key (nome_atributo) references nome_relação)
Ex: BD bancário:
create table cliente (
cliente_nome char(20) not null,
rua char(30),
cidade char(30,
primary key (cliente_nome))
create table agência (
agencia_nome char(15) not null,
ativos integer,
cidade char(30),
primary key (agencia_nome))
create table depósito (
agencia_nome char(15) not null,
conta_numero char(10),
cliente_nome char(20) not null,
saldo integer,
primary key (conta_numero, cliente_nome)
foreign key (agencia_nome) references agencia,
foreign key (cliente_nome) references cliente)
Tipos de dados suportados pelo Personal Oracle 7:
Tipo Comentários
CHAR dados alfanuméricos de até 255 caracteres. Acrescenta espaços
depois do valor para complementar o tamanho da coluna
DATE Inclui século, ano, mês, dia, hora, minuto e segundo
LONG strings alfanuméricas de tamanho variado de até 2 gigabytes
(memo)
LONG RAW Dados binários até 2 gigabytes (BLOB) - gráficos, sons, vídeo,
etc.
NUMBER Números positivos ou negativos com ou sem ponto decimal
RAW Dados binários até 255 bytes
RAWID String hexadecimal representando o endereço único de uma linha
em uma tabela
VARCHAR2 Dados alfanuméricos de tamanho variado. Até 2000 caracteres.
Exercício 1: Criar as tabelas sql para os esquemas abaixo:
a) BD hospitalar
médicos(codm, nomem, idade, especialidade)
pacientes(codp, nomep, idade, problema)
consultas (codm, codp, data, hora)
b) Zoológico:
animais (codA, tipo, nome, peso, idade)
jaulas (nro, area)
alocação (nro_jaula, codA, data_entrada)
zeladores (codZ, nomez, tempo_serviço)
manutenção (codZ, nro, horas)
2.1.3 Alter Table
O comando Alter Table permite modificar a estrutura de uma tabela já definida. Pode-se adicionar uma
coluna à tabela ou modificar uma coluna existente. A sintaxe do comando é mostrada a seguir:
alter table nome_tabela
[add nome_coluna tipo_dados; |
modify nome_coluna tipo_dados;]
O comando a seguir altera a estrutura da tabela agência para aumentar o tamanho do campo cidade:
alter table agência
modify cidade char(40);
A seguir é acrescentado o atributo país na tabela agência:
alter table agência
add país char(20)
2.1.4 Drop Table
Para remoção de uma tabela, usa-se o comando drop table, o qual remove todas as informações sobre
a relação (inclusive do catálogo)
Ex: drop table agência
2.2. Manipulação com tabelas SQL
Para manipular com tabelas do banco de dados, podem ser usados comandos de inserção, exclusão,
alteração e consulta de tabelas. Os comandos de atualização de banco de dados com SQL são mostrados a
seguir:
2.2.1 Inserção de Tuplas
insert into nome_tabela values
Ex: Inserção do cliente João:
insert into cliente
values (“João”, “A”, “Porto Alegre”)
Obs: valores nulos podem estar presentes em alguns atributos
insert into cliente values (“José”, null, “Santa Cruz do Sul”)
2.2.2 Alteração de Tuplas
update nome_tabela
set alteração
[where condição]
Exemplos:
Alterar a tabela de depósitos para acrescentar 5% de juros em todos os saldos:
update deposito
set saldo = saldo * 1.05
Alterar a cidade do cliente João para “Belém”
update cliente
set cidade = “Belém”
where cliente_nome = “João”
Acrescentar 6% de juros às contas com saldo maior que 10000:
update deposito
set saldo = saldo * 1.06
where saldo > 10000
2.2.3 Remoção de Tuplas
delete from nome_tabela
[where condição]
Ex: Remover todos os depóstos da conta de Smith:
delete from deposito
where cliente_nome = “Smith”
Remover todas as tuplas da tabela cliente:
delete cliente
Exercício 2: Remover as agências da cidade de Belém
2.3. Consultando Tabelas SQL
Toda consulta SQL é baseada no modelo relacional, ou seja, retorna uma tabela como resposta. Para
definir uma consulta, basta informarmos o que queremos e não como fazê-lo, pois SQL é uma linguagem nãoprocedural.
2.3.1 Comando Select
Estrutura básica de uma consulta SQL:
select
from
[where]
Select seleciona as colunas que deverão compor a tabela resultante. Os nomes dos atributos devem ser
os mesmos definidos no banco de dados. É uma cláusula obrigatória em qualquer consulta.
From indica as tabelas do banco de dados que devem ser consultadas. Também é obrigatória em qualquer
consulta.
Where indica uma condição pela qual os dados serão filtrados. A condição especificada deve retornar
verdadeiro ou falso. Para cada linha da tabela, o interpretador SQL verifica se atende a condição especificada
nesta cláusula e adiciona a linha na resposta caso seja verdadeira a avaliação. É uma cláusula opcional de
consulta.
- Observações:
Caso a cláusula where seja omitida, o predicado P (condição) é verdadeiro;
.
A lista de atributos A , A , ..., A pode ser substituida por um asterisco (*) para selecionar todos os
.
12 n
atributos de todas as relações da cláusula from;
SQL forma o produto cartesiano (relaciona cada linha da primeira tabela com todas as linhas da outra
tabela) das relações chamadas na cláusula from, verifica a condição da cláusula where, e então, projeta
.
o resultado para os atributos da cláusula select;
O resultado de uma consulta SQL é uma tabela.
.
Operadores lógicos: and, or e not
.
Operadores de comparação: >, >=, <, <=, = , != (ou <>)
.
Exemplo: Dada a seguinte tabela de clientes:
cliente_nome rua cidade
José Pe. Eutíquio Belém
Maria Pres. Vargas Fortaleza
Joana Alm Barroso Belém
Rogério Praia de Belas Porto Alegre
Para consultar os clientes (todos os atributos) que moram em Belém:
select *
from clientes
where cidade = “Belém”
O resultado seria:
cliente_nome rua cidade
José Pe. Eutíquio Belém
Joana Alm Barroso Belém
Exercício 3: Consultar no BD hospitalar:
a) O nome dos médicos com idade > 22 ou com especialidade diferente de traumatologia;
b) o nome e o problema dos pacientes com menos de 24 anos;
c) as consultas para o dia 26/09/96 após as 15hs.
2.3.1.1 Comando SELECT - Cláusula DISTINCT
A cláusula DISTINCT é usada para remover as duplicações de linhas no resultado da consulta.
select distinct cliente_nome
from depósito
Ex: Se a tabela depósito tiver os dados: O resultado da consulta será:
Agência_
nome
conta_
número
cliente_n
ome
saldo
Ag1 001 João 100
Ag2 002 José 200
Ag3 003 Smith 150
Ag3 003 José 350
cliente_nome
João
José
Smith
Exercício 4: No BD hospitalar:
a) Buscar todas as especialidades dos médicos;
b) Buscar todas as datas de consultas com horário após as 15hs
c) Buscar todas as idades dos médicos
2.3.1.2 Comando SELECT - Retorno de valores calculados
O comando Select permite retornar expressões aritméticas como resultado da consulta. Cada atributo
especificado na cláusula Select também é uma expressão, e como tal pode ser manipulada. Essa é uma forma
de mostrar resultados que na verdade não estão armazenados no banco de dados. Para atributos numéricos,
podemos utilizar +, -, *, /. E para atributos do tipo string pode-se utilizar concatenação ||. Além desses
operadores, ainda existem outras funções que serão mostradas na seção a seguir.
Exemplo: Buscar o código do médico e os dias consecutivos de suas consultas:
select codm, data + 1
from consultas
Exercício 5: buscar a idade em meses de todos os pacientes e seus nomes
Obs: Pode-se dar nomes novos as colunas resultantes de tabelas em algumas implementações de sql. Por
exemplo:
select codm, data + 1 dia_consecutivo
from consultas
Esta consulta resultaria em uma tabela com os atributos codm e dia_consecutivo.
2.3.1.3 Comando SELECT - Funções de Agregação
As funções de agregação operam sobre um conjunto de linhas e algumas são mostradas a seguir:
a) Count: contador de ocorrências
Ex: 1) total de linhas (tuplas) da tabela de pacientes:
select count (*)
from pacientes
2) Quantas consultas para o dia 26/05/96 existem?
select count (data) {não conta nulos}
from consultas
where data = “26/05/96"
b) Sum: somador de valores de atributos numéricos
Ex: Soma dos saldos do dos clientes da agência Ag1:
select sum (saldo)
from depósito
where agência_nome = “Ag1"
c) Avg: Média de valores de atributos numéricos
Ex: Obter média de idade dos médicos ortopedistas:
select avg (idade)
from médicos
where especialidade = “ortopedia”
d) Max / Min: Maior/Menor valor de um atributo
Ex: Maior e menor saldo de contas em depósito:
select max (saldo), min (saldo)
from depósito
2.3.2 Cláusula WHERE
Na cláusula WHERE são especificadas as condições de busca da tabela. Podem ser usados vários
operadores relacionais, como =, >, <, <=, >=, <> e ainda operadores de comparação de padrões em valores,
como LIKE e comparação de nulos.
2.3.2.1 Comparação [NOT] LIKE
Permite a definição de padrões de busca
*
Padrões possíveis:
a) Like “c*” - o valor do atributo inicia com o caracter “c”
b) Like “*c” - o valor do atributo termina com “c”
c) Like “*c*” - o valor do atributo possui o caracter “c” no meio da cadeia
Obs: algumas implementações de SQL usam % no lugar de *
Ex:Buscar o nome de todos os pacientes que começam com a letra C:
select nomep
from pacientes
where nomep like “C*”
Buscar o código do médico, paciente e horário para todas as consultas marcadas para o ano de 1996:
select codm, codp, hora {ou *}
from consultas
where data like “*96"
2.3.2.2 Comparação de nulos IS [NOT] NULL
* Permite o teste sobre valores nulos de atributos
Ex: buscar dados dos médicos que não têm especialidade:
select *
from medicos
where especialidade is null
2.3.3 Cláusula UNION
* Permite a união de duas tabelas compatíveis (equivalente ao operador c da álgebra relacional;
Ex: buscar o nome de todas as pessoas cadastradas no hospital:
select nomem
from medicos
union
select nomep
from pacientes
Buscar todos os clientes com empréstimo ou depósito na agência Ag1:
(select cliente_nome
from depósito
where agencia_nome = “Ag1")
union
(select cliente_nome
from empréstimo
where agencia_nome = “Ag1")
obs: a cláusula Union retira as repetições. Em algumas versões de SQL, pode-se utilizar UNION ALL, que
retorna todas as tuplas das tabelas unidas, mesmo havendo repetição.
2.3.4.Cláusula INTERSECT
Permite a interseção de duas tabelas compatíveis (operador 1 da álgebra relacional), retornando apenas
as linhas que pertencem às duas tabelas
Ex: buscar todos os clientes da Ag1 que possuem uma conta e empréstimo:
(select distinct cliente_nome
from depósito
where agencia_nome = “Ag1")
intersect
(select distinct cliente_nome
from empréstimo
where agencia_nome = “Ag1")
2.3.5 Claúsula MINUS
* Permite a subtração de duas tabelas compatíveis (operador - da álgebra relacional), retornando apenas
as linhas que pertencem à primeira tabela, com exceção das que aparecem na segunda tabela.
Ex: Buscar todos os clientes da Ag1 que possuem conta mas não possuem empréstimo:
(select distinct cliente_nome
from depósito
where agencia_nome = “Ag1")
minus
(select distinct cliente_nome
from empréstimo
where agencia_nome = “Ag1")
2.4 Consultas envolvendo mais de uma tabela do BD
Quando a resposta de uma consulta precisa ser buscada em várias tabelas do banco de dados, então é
necessário especificar quais tabelas serão usadas na consulta SQL. Para isso é utilizada a cláusula FROM.
Como SQL é baseada no modelo relacional, a forma de juntar tabelas é através de produto cartesiano das
mesmas. Por exemplo, se quisermos usar duas tabelas, a de médicos e a de consultas, mostradas abaixo, o
resultado é uma tabela que contém relacionamentos entre todos os médicos e todas as consultas. Obviamente
que várias tuplas do resultado não são úteis, portanto temos que especificar quais as tuplas úteis através da
cláusula WHERE.
médicos(codm, nomem, idade, especialidade)
codm nomem idade especialidade
M1 João 33 traumatologia
M2 José 23 clínica geral
M3 Maria 30 obstetrícia
consultas (codm, codp, data, hora)
codm codp data hora
M1 P1 12/12/1997 15
M1 P2 13/12/1997 14
M2 P1 05/10/1997 8
Se a consulta fosse:
select *
from médicos, consultas
O resultado seria:
codm nomem idade especialidade codm codp data hora
M1 João 33 traumatologia M1 P1 12/12/1997 15
M1 João 33 traumatologia M1 P2 13/12/1997 14
M1 João 33 traumatologia M2 P1 05/10/1997 8
M2 José 23 clínica geral M1 P1 12/12/1997 15
M2 José 23 clínica geral M1 P2 13/12/1997 14
M2 José 23 clínica geral M2 P1 05/10/1997 8
M3 Maria 30 obstetrícia M1 P1 12/12/1997 15
M3 Maria 30 obstetrícia M1 P2 13/12/1997 14
M3 Maria 30 obstetrícia M2 P1 05/10/1997 8
O resultado expressa valores que nem sempre são verdadeiros. Por exemplo, existe uma linha com
médico M1 e ao lado, uma consulta do médico M2. Se quisermos saber os dias de consulta do médico João,
temos que escolher somente as linhas onde o codm em médicos é igual a codm em consultase o nome do médico
seja João. Pode-se perceber que para saber datas de consultas basta olharmos na tabela de consultas, mas esta
somente contém o código dos médicos. Portanto se quisermos avaliar o nome do médico, temos que buscar na
tabela de médicos. Daí a necessidade de juntar as tabelas pelo código do médico. A esta operação damos o
nomes de JOIN, ou seja, junção de valores relacionados. A consulta SQL que obtém tal resposta é:
select data
from médicos, consultas
where médicos.codm = consultas.codm and médicos.nomem = “João"
O resultado desta consulta é
data
12/12/1997
13/12/1997
Na cláusula FROM, podemos definir quantas tabelas forem necessárias. A única restrição é de que as
chaves estrangeiras devem ser igualadas para que os dados retornados sejam verdadeiros.
Ex: Buscar o nome dos médicos com consulta marcada para o dia 22/05/96:
select medicos.nomem
from medicos, consultas
where consultas.data = “22/05/96" and
medicos.codm = consultas.codm
* Pode-se ter variáveis de tupla associadas a cada tabela declarada na cláusula from
Ex:
select M.nomem, P. nomep
from consultas C, medicos M, pacientes P
where
C.codm = M.codm and
C.codp = P.codp and
C. hora = 15
Exercício 6: Buscar nome e idade dos médicos com consulta marcada com a paciente Ana.
2.5. Subconsultas
Na linguagem SQL ANSI podem ser definidas subconsultas através das cláusulas IN, ANY, ALL,
EXISTS e CONTAINS. Entretanto, somente podem ser definidas subconsultas na cláusula WHERE..
2.5.1 Cláusula [NOT] IN
Esta cláusula estabelece a relação de pertinência entre conjuntos (tabelas). Sua avaliação retorna um valor
booleano.
*sintaxe: where [not] in ()
Ex: Buscar nome de todos os pacientes sem consulta marcada:
select nomep
from pacientes
where codp not in
(select codp
from consultas)
A subconsulta (select codp from consultas) é avaliada primeiro, retornando uma tabela com todos os
códigos de pacientes que possuem consulta marcada. Em seguida a consulta principal é processada selecionando
os nomes dos pacientes cujo código não se encontra no resultado da subconsulta. Pode-se observar que essa
consulta também poderia ser descrita através da cláusula minus, subtraindo os pacientes com consulta do
conjunto de todos os pacientes cadastrados (mostre como!).
Uma outra observação importante é que um item (codp) é comparado a um conjunto (o resultado da
subconsulta). A cláusula IN comporta-se como o operador pertence da matemática, comparando um elemento
a um conjunto de elementos do mesmo tipo. A subconsulta não poderia, por exemplo, retornar código de médico
ao invés de pacientes, ou seja, o elemento comparado tem que ser do mesmo tipo dos elementos do conjunto.
Ex: Buscar nome e problema dos pacientes com idade > 21 e que também são médicos:
select nomep, problema
from pacientes
where idade > 21 and nomep in
(select nomem
from medicos)
Exercício 7:
Buscar todos os clientes da Ag1 que possuem conta e empréstimo (com cláusula in):
Buscar todos os clientes da Ag2 que possuem conta mas não possuem empréstimo.
2.5.2 Cláusula ANY
Esta cláusula permite outras formas de comparação elemento-conjunto. Ao invés de somente verificar
se um elemento pertence a um conjunto, podemos verificar se um elemento é maior, menor, diferente ou até
igual a qualquer elemento do conjunto resultante da subconsulta.
= any () {mesmo que in}
> any () {verdadeiro se o atributo comparado for maior do que algum valor de
atributo das tuplas resultantes da subconsulta}
< any ()
<> any ()
Ex: Buscar o nome de todos os pacientes, exceto o mais idoso:
select nomep
from pacientes
where idade < any
(select idade
from pacientes)
Exercício 8: Buscar o nome e a idade do médico mais jovem.
2.5.3 Cláusula ALL
Esta cláusula é utilizada quando uma condição deve ser satisfeita para todos os elementos de um
conjunto.
= all () {igual a todos}
> all () {maior do que todos}
< all () {menor que todos}
<> all () {diferente de todos (= not in)}
Ex:
1) Buscar o nome dos clientes que tem saldo menor que todos os clientes com depósto na agência
Ag3:
select cliente_nome
from deposito
where saldo < all
(select saldo
from deposito
where agencia_nome = “Ag3")
2)Buscar o nome dos médicos com consultas marcadas para horários mais tarde que todas as
consultas da médica Maria
select nomem
from medicos m, consultas c
where m.codm = c.codm and
c.hora > all
(select hora
from consultas c1, medicos m1
where c1.codm = m1.codm and
m1.nomem = “Maria”)
Exercício 9: Buscar os dados dos pacientes com consultas marcadas para horários anteriores a todos os
horários marcados para o dia 22/06/96
2.5.4 Cláusula [NOT] EXISTS
Esta cláusula testa se uma subconsulta tem alguma tupla em seus resultados. Se for usado o NOT
antes de EXISTS, o resultado é o contrário.
Ex: Podemos reescrever a consulta: Encontre os clientes que possuem conta e empréstimo na Ag1.
select cliente_nome
from cliente
where exists (select *
from deposito
where deposito.cliente_nome = cliente.cliente_nome and
agencia_nome = “Ag1")
and exists (select *
from emprestimo
where emprestimo.cliente_nome = cliente.cliente_nome and
agencia_nome = “Ag1")
Ex2: Considere novamente a consulta: “Encontre todos os clientes que têm uma conta em todas as
agências localizadas em “StaCruz”. (usando not exists e minus)
select distinct S.cliente_nome
from deposito S
where not exists ((select agencia_nome
from agencia
where agencia_cidade = “StaCruz”)
minus
(select T.agencia_nome
from deposito T
where S.cliente_nome = T.cliente_nome))
Exercício 10 (BD hospitalar):
a)Buscar o nome de todos os médicos com consulta marcada.
b)Buscar o nome do médico mais jovem.
2.5.5 Cláusula CONTAINS
A cláusula CONTAINS foi introduzida originalmente no sistema R, e não aparece no padrão ANSI pois
o processamento dessa cláusula é muito custoso.
As construções in, any e all permitem-nos testar um valor simples contra membros de um conjunto
inteiro. Uma vez que o select gera um conjunto de tuplas, podemos querer comparar conjuntos para determinar
se um conjunto contém todos os membros de algum outro conjunto. Tais comparações são feitas em SQL
usando contains e not contains. Em outras palavras, esta cláusula é equivalente ao operador contém .
da
matemática
Ex: Considere a mesma consulta do Ex2 acima. Para cada cliente, precisamos ver se o conjunto de todas
as agências na qual um cliente possui uma conta contém o conjunto de todas as agências de “StaCruz”.
A consulta pode ser escrita assim:
select distinct S.cliente_nome
from deposito S
where (select T.agencia_nome
from deposito T
where S.cliente_nome = T.cliente_nome)
contains
(select agencia_nome
from agencia
where agencia_cidade = “StaCruz”)
Exercício 11: identifique outras possíveis consultas que possam ser reescritas com a cláusula contains.
2.6 Ordenando a exibição de tuplas - Cláusula ORDER BY
Esta cláusula permite a ordenação do resultado da consulta. É utilizada após a cláusula Where.
*
Sintaxe:
ORDER BY [desc] [asc]
{desc - ordem decrescente, asc - ordem crescente}
Ex: Buscar os nomes dos clientes em ordem alfabética com empréstimo na agência “Ag3":
select distinct cliente_nome
from emprestimo
where agencia_nome = “Ag3"
order by cliente_nome
Ex2: Listar todos os empréstimos na ordem decrescente de quantia e os empréstimos com a mesma quantia
devem ser ordenados pelo número do empréstimo.
select *
from emprestimo
order by quantia desc, emprestimo_numero asc
Exercício 12: Buscar os dados de todas as consultas do paciente Carlos, ordenadas de forma decrescente
pela hora da consulta.
2.7 Agrupando tuplas - Cláusulas GROUP BY e HAVING
*
Group by: agrupa partes do resultado de uma consulta, a partir do qual é possível utilizar funções
de agregação (avg, min, max, count, sum);
*
Having: Especifica condições para a formação de um grupo. Só existe associado à cláusula group
by.
*
As condições só podem envolver os atributos a serem buscados ou alguma função de agregação.
Exemplos:
1) Encontre o saldo médio de conta em cada agência:
select agencia_nome, avg(saldo)
from deposito
group by agencia_nome
obs: as duplicatas são mantidas.
2) Encontre o número de depositantes de cada agência:
select agencia_nome, count(distinct cliente_nome)
from deposito
group by agencia_nome
obs: evita-se as duplicações com a cláusula distinct.
3) Buscar as agências nas quais a média dos saldos é maior do que 1200:
select agencia_nome, avg(saldo)
from deposito
group by agencia_nome
having avg(saldo) > 1200
4) Obter as agências com maior média de saldos:
select agencia_nome
from deposito
group by agencia_nome
having avg(saldo) >= all (select avg(saldo)
from deposito
group by agencia_nome)
obs: não é possível usar max(avg(...)).
5)
Encontre a média dos saldos de todos os depositantes que vivem em “StaCruz” e têm pelo menos 3
contas. (uso de where e having juntos)
select avg(saldo)
from deposito, cliente
where deposito.cliente_nome= cliente.cliente_nome and
cliente.cidade= “StaCruz”
group by deposito.cliente_nome
having count(distinct conta_numero) >= 3
obs: O predicado na cláusula where é aplicado antes do predicado de having.
Exercício 13:
1) buscar todas as datas de consultas e o total de consultas para esta data.
2) buscar somente as datas e o total de consultas para horários após as 14 horas
3) buscar somente as datas e o total de consultas para as datas onde haja mais de uma consulta marcada.
4) buscar, para a tabela de médicos, todas as idades e o total de médicos com a mesma idade.
5) buscar o nome dos médicos com mais de uma consulta marcada.
2.8 Comandos de atualização + comandos de consulta
Expressões de consulta podem estar associadas aos comandos de atualização de dados, para melhor
restringir o universo de tuplas a serem atualizadas. Os comandos usados são os já apresentados delete e update.
Exemplos:
1) Remover todas as contas em agências localizadas em Canoas
delete deposito
where agencia_nome in
(select agencia_nome
from agencia
where agencia_cidade = “Canoas”)
2) Remover todas as consultas do médico “João”:
delete consultas
where codm in ( select codm
from medicos
where nomem = “João”)
3) Acrescentar 5% de juros sobre contas cujos saldos sejam maiores do que a média:
update deposito
set saldo = saldo * 1.05
where saldo > (select avg (saldo)
from deposito)
4) Passar para as 19hs todas as consultas da paciente Ana
update consultas
set hora = “19:00"
where codp in (select codp
from pacientes
where nomep = “Ana”)
3. Visões em SQL
Um banco de dados é composto de várias tabelas relacionadas. Se quisermos obter qualquer informação,
devemos citar essas tabelas nas consultas. Algumas consultas são bastante utilizadas no dia-a-dia e são
complexas de escrever, devido ao uso de subconsultas. Muitas vezes os usuários não têm conhecimento
adequado para formular tais consultas. Nesse caso, o DBA poderia colocar algumas consultas prontas à
disposição, as quais seriam acessadas com consultas simples.
As visões são tabelas virtuais derivadas das tabelas do banco de dados e são úteis para garantir segurança
de acesso ao BD, disponibilização de tabelas que melhor se adequam às necessidades de uma aplicação e
facilitade de acesso aos dados sem ter que formular consultas complexas.
Exemplos:
a) Um funcionário do hospital não deve ter acesso a todos os dados pessoais de um paciente, somente ao
seu código e nome;
b) Pode ser interessante vincular os dados de um médico aos dados de suas consultas
A criação de visões em SQL obedece a seguinte sintaxe:
create view as
obs: a visão criada não é executada, e sim, armazenada no catálogo. Porém, para o usuário é como se
tivesse uma visão armazenada no BD.
Para remover uma visão, usa-se o comando
drop view
obs: a visão especificada é eliminada (a definição é removida) e todas as visões definidas em termos
desta visão também são automaticamente anuladas. Porém os dados continuam existindo, já que a visão
é uma tabela virtual.
Exemplos:
a) A seguir é criada uma visão chamada DadosPac, que, do ponto de vista do usuário comum,
corresponde a uma tabela como outra qualquer. Esta visão retorna somente o código e o nome de todos os
pacientes do hospital. Se um funcionário desse hospital tiver acesso somente a tabela (visão) DadosPac, então
ele não verá o problema do paciente, o qual é um atributo da tabela pacientes.
create view DadosPac as
select codp, nomep
from pacientes
b) A visão a seguir, chamada MedCons contém os nomes dos médicos e suas datas e horas de
consulta. Observe que para obter essa informação, precisamos das tabelas de médicos e consultas e da junção
entre os atributos codm das duas tabelas. A visão facilita a consulta já que, após a sua criação, basta pedirmos
para ver todos os dados da tabela MedCons que automaticamente a consulta da visão é processada, sem termos
que defini-la novamente.
create view MedCons as
select nomem, data, hora
from medicos, consultas
where medicos.codm = consultas.codm
Obs:
Uma vez definida uma visão, qualquer operação de consulta ou atualização pode ser aplicada sobre
ela.
Operações realizadas sobre uma visão se refletem diretamente sobre as tabelas físicas das quais ela
deriva.
3.1 Consultas sobre Visões
Nas operações de recuperação (select) o processo de conversão das operações em visão para operações
em tabelas básicas é bem direto e funciona bem.
Exemplos:
a)
o funcionáro do hospital deseja buscar o nome de todos os pacientes cadastrados que começam com
a letra R:
select nomep
from DadosPac
where nomep like “R*”
O sistema (tradutor) converte esta consulta em:
select nomep
from pacientes
where nomep like “R*”
b) Buscar o nome dos médicos com consulta marcada para horários após as 18hs no mês de outubro:
select nomem
from MedCons
where hora > 18 and data like “*/10/94"
O sistema (tradutor) converte esta consulta em:
select nomem
from medicos, consultas
where medicos.codm=consultas.codm and
hora > 18 and
data like “*/10/94"
3.2 Atualizações
*Operações de inserção, atualização e remoção de tuplas (insert, update e delete) em uma visão muitas
vezes geram problemas. Nem todas as visões são atualizáveis
Situação 1: Inserção de tuplas na visão MedCons
Problemas:
- violação da regra de integridade de entidade; (a chave primária não está na visão)
- perde-se o relacionamento entre médicos e consultas
Situação 2: Visão que relaciona total de consultas de um determinado paciente.
create view ConsPac as
select codp, count (*)
from consultas
group by codp
Problemas:
-inserção nesta visão viola a regra de integridade de entidade e não se pode inserir no campo count(*);
-Não se pode atualizar um atributo calculado.
3.3 Vantagens das visões
-Elas fazem com que o mesmo dado seja visto por diferentes usuários de diferentes formas (ao mesmo
tempo)
-A percepção do usuário é simplificada;
-É óbvio que o mecanismo da visão possibilita aos usuários centrarem-se unicamente nos dados que lhes
são importantes e ignorarem o resto. O que talvez não seja tão óbvio é que, pelo menos na recuperação
(select), tal mecanismo também possa simplificar consideravelmente as operações de manipulação de
dados feitas pelo usuário.
-
Segurança automática para os dados ocultos
Dados ocultos são aqueles não visíveis através de determinada visão. Ficam claramente protegidos
por meio desta visão específica. Assim, obrigar os usuários a acessar o banco de dados através de
visões é um mecanismo simples, porém eficaz de controle de autorização.
3.4 Algumas soluções para problemas com visões
-
Impedir operações de atualização sobre visões
-As tuplas de uma visão devem corresponder a tuplas que tenham condições de serem identificadas nas
tabelas físicas das quais ela deriva;
-Cada atributo de uma visão deve corresponder a um atributo distinto e identificável de alguma das tabelas
físicas das quais ela deriva.
Exercício 14:
Dada o BD de peças e fornecedores:
fornecedores (codf, nomef, cidade)
peças (codp, nomep, peso, cor)
fornecem (codf, codp, qtde)
e dada a definição de visão:
create view peças_pesadas
as select codp, peso, cor from peças where peso > 20
Demonstrar a operação executada (isto é, a forma convertida) do que segue:
a)
select *
from peças_pesadas
where cor = “preto”
b) update peças_pesadas
set cor = “branco”
where peso = 30
c) insert into peças_pesadas
values (“P4", 52, “vermelha”)
4. Autorizações de Acesso
Muitos SGBDs relacionais podem ser acessados por diversos usuários. Cada usuário tem uma
determinada necessidade em relação aos dados armazenados. De acordo com o projeto do banco de dados,
alguns usuários só podem consultar alguns dados, outros podem atualizar, outros podem inserir, etc. Para que
o dado fique protegido do uso indevido de qualquer usuário, a linguagem SQL permite a definição dos
privilégios que cada um pode ter em relação às tabelas criadas no banco de dados.
Os privilégios garantem a segurança e integridade dos dados, bem como a responsabilidade de cada
usuário sobre seus dados específicos.
4.1. Comando Grant
Este comando garante privilégios para os usuários sobre tabelas/visões.
GRANT ON TO
podem ser:
Select: pode executar uma consulta sobre a tabela
Insert: pode executar uma inserção sobre a tabela
Delete: pode apagar registros da tabela
Update: pode modificar registros da tabela
All Privileges/all: pode executar qualquer operação sobre a tabela
:
nome do usuário que vai receber os privilégios. Deve ser um nome cadastrado dentro do
ambiente.
PUBLIC:
concede os privilégios especificados a todos os usuários do ambiente
Exemplos:
- grant select on medicos to Paulo
permite somente consultas do usuário Paulo na tabela de médicos
-grant select, insert, update on consultas to Mary
concede ao usuário Mary os privilégios de seleção, inserção e atualização sobre a tabela consultas
-grant all privileges on DadosPac to public
permite todos os privilégios a todos os usuários sobre a tabela (neste caso, visão) DadosPac.
4.2. Comando Revoke
Este comando revoga os privilégios de acesso aos usuários.
REVOKE ON FROM
Exemplos:
-revoke select on medicos from Paulo
-revoke all on DadosPac to public - revoga todos os direitos sobre a visão DadosPac
5. Mais Exercícios
1) BD formado pelas seguintes tabelas:
medicos (codm, nomem, idade, especialidade)
pacientes (codp, nomep,idade, problema)
consultas (codm, codp, data, hora)
a) Mostrar todos os dados da tabela de consultas.
SELECT *
FROM CONSULTAS
b) Mostrar os dados dos médicos classificados por ordem de código
SELECT *
FROM MEDICOS
ORDER BY codm ASC
c) Obter os nomes e códigos de todos os médicos cirurgiões
SELECT nomem, codm
FROM MEDICOS
WHERE especialidade= “cirurgia”
d) fornecer os nomes e códigos de todos os médicos e seus respectivos dias de consulta
SELECT nomem, data
FROM medicos, consultas
WHERE medicos.codm = consultas.codm
e) fornecer os nomes dos médicos que possuem alguma consulta marcada com o paciente P4
Várias soluções possíveis:
e.1) SELECT nomem
FROM medicos
WHERE codm in (SELECT codm
FROM consultas
WHERE codp= “P4")
e.2) SELECT nomem
FROM medicos
WHERE “P4" in ( SELECT codp
FROM consultas
WHERE codm = medicos.codm)
e.3) SELECT nomem
FROM medicos
WHERE EXISTS (SELECT *
FROM consultas
WHERE codm = medicos.codm and
CODP = “P4")
f) mostrar os nomes dos médicos que não têm consulta marcada com a paciente P4
SELECT nomem
FROM medicos
WHERE NOT EXISTS (SELECT *
FROM consultas
WHERE codp = “P4" and codm = medicos.codm)
g) Mostrar os nomes dos médicos que não tem consulta marcada com a paciente Maria
SELECT nomem
FROM medicos
WHERE codm IN
(SELECT codm
FROM consultas
WHERE codp NOT IN ( SELECT codp
FROM pacientes
WHERE nome = “Maria”)
h) Mostrar o código dos pacientes que tem consulta marcada para o dia 23/09/96 ou com médicos pediatras.
(SELECT codp
FROM consultas
WHERE data = “23/09/96")
UNION
(SELECT codp
FROM consultas
WHERE codm IN (SELECT codm
FROM medicos
WHERE especialidade = “pediatra”))
obs: poderia ter sido utilizado o OR ao invés de UNION na primeira subconsulta?
i) Mostrar os nomes dos médicos que não fornecem consultas para os pacientes de obstetras.
SELECT nomem
FROM medicos
WHERE codm NOT IN
(SELECT codm
FROM consultas
WHERE codp IN
(SELECT C.codp
FROM medicos M, consultas C, pacientes P
WHERE M.especialidade = “obstetrícia” and
M.codm = C.codm and
C.codp = P.codp))
2) BD formado pelas seguintes tabelas:
empregado (codemp, salário, função, codd)
departamento (codd, cidade, nome)
joga (codemp, codtime, posição)
time(codtime, nome, endereço)
a) Mostrar os códigos dos empregados que ganham acima da média dos salários
SELECT codemp
FROM empregado
WHERE salario > (SELECT avg(salario) from empregado)
b) mostrar os departamentos que não tem empregados
SELECT codd
FROM departamento
WHERE codd NOT IN
(SELECT distinct codd
FROM empregado)
c) mostrar os departamentos com média salarial inferior a 500 (obs: não cai na prova)
SELECT codd
FROM empregado
GROUP BY codd
HAVING avg(salario) < 500
d) mostrar os departamentos que possuem mais de 10 programadores (obs: não cai na prova)
select codd
from empregado
where funcao = “programador”
group by codd
having count(*) > 10
e) mostrar o nome do time do empregado de maior salário
SELECT nome
FROM time
WHERE codtime IN
(SELECT codtime
FROM joga
WHERE codemp IN
(SELECT codemp
FROM empregado
WHERE salario IN
(SELECT Max(Salario)
FROM empregado)))
f) mostrar o maior salário dos empregados que jogam na ponta direita
SELECT max(salario)
FROM empregado, joga
WHERE posicao = “ponta direita” and (joga.codemp = empregado.codemp)
g) mostrar a posição e o código dos empregados de maior salário em cada departamento (obs: não cai na prova)
select codemp, posicao
from joga
where codemp in
(select codemp
from empregado
where salario in
(select max(salario)
from empregado
group by codd))
6 O Futuro do Padrão SQL
Recentemente temos assistido ao aumento da popularidade dos bancos de dados orientados a objetos
(OODBMS -Object Oriented Database Management System). Um banco de dados orientado a objetos é aquele
que incorpora objetos complexos, tipos abstratos de dados (Abstract Data Types - ADTs), encapsulamento e
herança [MULL,1994]. Mantelman define um objeto complexo como aquele que armazena procedimentos além
de dados, e estende a definição de um banco de dados orientado a objetos dizendo que não há restrições a dados
baseados em caracteres e que podem ser acomodados objetos multimídia [MANT,1996].
Os bancos de dados orientados a objetos tratam os dados de modo diferente dos bancos de dados
relacionais. Estes implementam acesso aos dados através de linhas e colunas, enquanto os orientados a objetos
têm uma estrutura mais complexa, que não é suportada pelo padrão SQL92.
Como alguns fornecedores de SGBDs começaram a acrescentar a seus produtos características além do
escopo do padrão SQL92, houve a necessidade de se estudar um novo padrão, de modo a garantir a
portabilidade entre os vários SGBDs. Daí a necessidade do SQL3. Algumas das características implementadas
incluem tipos de dados novos e estendidos, incluindo tipos de dados abstratos, múltiplos tipos de estados nulos,
suporte para objetos e identidade de objetos, encapsulamento, herança e triggers.
O SQL92 tem um conjunto de tipos de dados atômicos. No SQL3, estes tipos de dados são mantidos
como tipos de dados pré-definidos, embora alguns tenham sido modificados ou estendidos. Por exemplo, os
tipos de dados character e bit foram estendidos para incluir o conceito de um "objeto maior"(large object). Estes
"large objects" facilitam o uso de objetos multimídia dentro do banco de dados.
Dois tipos adicionais de dados, antes não suportados, foram incluídos: boolean e enumerated. O tipo
boolean somente pode ter os valores "true", "false" e "unknown". O tipo enumerated permite que se defina
domínios cujos valores são restritos a um pequeno conjunto de valores. Como exemplo, teríamos o comando
CREATE DOMAIN cores (azul, vermelho, amarelo) que define um domínio chamado cores e restringe os
valores inseridos na coluna de tipo enumerated a um dos três valores especificados para o domínio
[MELT,1995].
Ao contrário do SQL92, o SQL3 suporta múltiplos estados nulos. Isto significa que diferentes classes
de null são suportadas, permitindo às aplicações definir os significados de determinado estado nulo. Possíveis
significados incluem "unknown" (como vimos acima, no caso do tipo enumerated), "missing", "not applicable"
ou "pending". Cada definição de nulo tem uma representação diferente que o torna identificável durante uma
consulta ou atualização.
O segundo grupo de objeto de dados suportado pela nova versão, e não suportado pela anterior, é o tipo
abstrato de dados (ADT). O ADT é utilizado pelo SQL3 para possibilitar o uso de objetos na estrutura SQL.
O ADT permite aos usuários criar novos tipos de dados definindo tipos abstratos de dados a partir de tipos de
dados pré-definidos ou a partir de outros ADTs já existentes. Os ADTs suportam os conceitos de
encapsulamento e subtipos da orientação a objetos.
Estendendo o que vimos acima, objetos são itens de dados que combinam complexas estruturas de dados
com processos ou métodos para manipular estes dados. A noção de combinar dados e processos é chamada de
encapsulamento. Enquanto os tipos de dados que conhecemos no SQL92 são identificados através de seus
valores, no SQL3, um dado definido como object será identificado por um identificador único de objeto, gerado
quando o objeto é criado. Os conceitos de subtipo e supertipo são baseados no conceito de herança da orientação
a objetos, que é a capacidade de se criar novos objetos a partir de outros já existentes, herdando suas
características. Atributos e métodos podem ser herdados, o que diminui a redundância de informações. No
SQL3, um ADT pode ser um subtipo de outro ADT. Por exemplo, podemos definir um ADT como um objeto
PESSOA. Um subtipo de PESSOA poderia ser ESTUDANTE, que herdaria as propriedades e comportamento
de PESSOA. Um subtipo do subtipo ESTUDANTE poderia ser GRADUANDO, que herdaria as característica
de PESSOA e ESTUDANTE.
Outra importante característica do SQL3 é o suporte a triggers. No padrão SQL92, esta característica
foi deixada de lado porque seu uso foi subestimado. Existe hoje, entretanto, uma grande demanda desta
característica por parte dos usuários, e mais e mais fornecedores estão incluindo suporte a triggers em seus
produtos, como, por exemplo, a Oracle no seu SGBD Oracle7. Um trigger é um objeto do esquema do banco
de dados ou um bloco de código que diz ao banco de dados que outras ações devem ser tomadas depois de
executados certos comandos SQL. Por exemplo, pode-se querer adicionar um registro a uma tabela de log toda
vez que um registro for excluído de uma outra tabela para se manter uma auditoria de quem está realizando as
mudanças. Um trigger seria usado neste caso para iniciar uma sequência de ações (atualizar a tabela de log)
depois que uma função de deleção fosse executada.
O trabalho de revisão de um padrão para a inclusão de novas características é intenso e dinâmico. Os
fornecedores de SGBDs aumentam o poder de seus produtos adicionando mais e mais características, algumas
delas não cobertas pelo padrão atual, o que cria a necessidade de revisão. No caso do SQL3, a inclusão de
características de orientação a objetos é a mais importante.
7 Bibliografia
CAMPOS, Maria Luiza. “Laboratório de Banco de Dados”. Departamento de Ciência da Computação UFRJ.
Disponível no endereço “http://www.dcc.ufrj.br/~labbd/texto/sql.htm”. 1997.
DATE, C. J. “Introdução a Sistemas de Bancos de Dados”. Ed. Campus. 1991.
LIMA, CARLA A. G. “Linguagem SQL”. Banco de Dados I - Notas de Aula. Depto. Informática - UFPA.
1997.
MACHADO, F.N; ABREU, M. “Projeto de Banco de Dados: Uma visão prática”. Érica. 1995.
STEPHENS, R.; PLEW, R.; MORGAN, B.; PERKINS, J. “Teach yourself SQL in 21 days”. 2nd Edition.
SAMS Publishing, USA, 1997.
O objetivo deste curso é introduzir o aluno na tarefa de manipular bancos de dados através da linguagem
SQL. SQL significa Structured Query Language (linguagem estruturada de consulta). Apesar do nome, fazer
consultas a bancos de dados não é a única função de SQL. Ela é utilizada para criar tabelas, inserir, excluir e
alterar dados do banco de dados, além de outras utilizações. A seguir é mostrado um breve histórico da
linguagem seguido de uma rápida visão de bancos de dados relacionais.
1.1. Um Breve Histórico
A linguagem SQL foi desenvolvida pela IBM em meados dos anos 70 como uma linguagem de manipulação de dados (DML - Data Manipulation Language) para suas primeiras tentativas de desenvolvimento de bancos de dados relacionais. A grande vantagem do SQL sobre modelos de dados anteriores é que as operações realizadas sobre os dados são especificadas numa linguagem não procedural e conjuntos de dados são manipulados com um único comando. Isto faz com que os programadores não tenham de navegar por uma estrutura complexa de banco de dados, reduzindo a quantidade de código necessário para acessar os dados.
A SQL tornou-se de fato o padrão depois de 1986, quando o American National Standards Institute
(ANSI), a organização responsável pelos padrões industriais nos Estados Unidos, endossou o SQL como
linguagem padrão para os bancos de dados relacionais. Desde então, o SQL já sofreu duas atualizações oficiais, uma em 1989, e outra em 1992. A revisão de 1992, SQL-92 (ANSI X3.135-1992) é o padrão usado atualmente, mas desde 1993 há um trabalho sendo desenvolvido para atualizar o padrão de modo que este atenda às características das últimas versões de bancos de dados relacionais lançadas no mercado. O novo padrão SQL chama-se SQL3; o número 3 vem de 1993, o ano em que os trabalhos começaram.
Em 1986, o ANSI publicou o primeiro padrão SQL, X3.135-1986. A International Organization for
Standardization (ISO) pubicou um padrão tecnicamente idêntico, ISO 9075-1987, alguns meses depois em
1987. O padrão representava um denominador comum das implementações SQL existentes e consequentemente deixou de padronizar muitas características populares e necessárias da linguagem. Este padrão continha características de SQL de quatro linguagens de programação: COBOL, FORTRAN, Pascal e PL/I.
Em 1989, tanto ANSI quanto ISO publicaram padrões substitutos (ANSI X3.135-1989 e ISO/IEC
9075:1989) que aumentaram a linguagem e acrescentaram uma capacidade opcional de integridade referencial, permitindo que projetistas de bancos de dados pudessem criar relacionamentos entre dados em diferentes partes do banco de dados. No mesmo ano, o ANSI adicionou ao padrão suporte para duas outras linguagens de programação, Ada e C.
Em 1992, ISO e ANSI publicaram a terceira revisão do padrão SQL, o SQL92 (X3.135-1992 e ISO/IEC
9075:1992). Na mais nova versão, SQL3, a nova característica mais importante é a adição de características
de orientação a objetos na linguagem.
Devido ao sucesso da forma de consultar e manipular dados da SQL, dentro de um ambiente de banco
de dados, a utilização da SQL foi se tornando ainda maior. Uma grande quantidade de SGBDs foi tendo como linguagem básica a SQL, a saber, SQL/DS e DB2 da IBM, ZIM, Oracle, RDB, Sybase, dentre outros. Cabe ressaltar que cada implementação de SQL possui uma adaptação da sintaxe para resolver determinados problemas, portanto, qualquer comando mostrado neste curso pode ser usado de forma diferente em um determinado SGBD. Recomenda-se a leitura do manual do fabricante para maiores informações sobre o uso da linguagem SQL em SGBDs comerciais.
1.2. Banco de Dados Relacional
Para que haja um melhor entendimento da linguagem SQL é necessário abordar a evolução dos bancos
de dados e de sua teoria. Sistemas de bancos de dados relacionais são aqueles baseados no modelo lógico relacional, o qual foi proposto por E. F. Codd em 1970.
A abordagem relacional está baseada no princípio de que as informações em uma base de dados podem
ser consideradas como relações matemáticas e que estão representadas de maneira uniforme, através do uso de tabelas bidimensionais. Todos os dados de um BD relacional estão armazenados em relações (ou tabelas).
Cada tabela possui atributos (ou campos), os quais são armazenados em colunas da tabela. As linhas
guardam informações sobre uma entidade (registro). Alguns atributos da tabela são chave primária da mesma.
Isto significa que não existem duas linhas iguais numa tabela. Cada linha pode ser identificada univocamente
pela sua chave primária (por exemplo matrícula de aluno). A seguir é mostrada a tabela de alunos que tem como
atributos Matrícula, Nome, Endereço e Cod_Curso. A chave primária da tabela é o atributo matrícula.
Alunos
Matrícula Nome Endereço Cod_Curso
9100807 José xxxx 88
9200789 Maria yyy 77
A seguir é mostrada a tabela de cursos com os atributos Cod_Curso e Nome. Cod_Curso é a chave
primária.
Cod_Curso Nome
77 Computação
88 Eng. Civil
Note que cada aluno da tabela de alunos possui um valor de Cod_Curso que existe na tabela de cursos.
Isso significa que se quisermos saber o nome do curso de um aluno, basta sabermos o código do curso do aluno
e procurar pelo mesmo código na tabela de cursos. Quando existe essa ligação entre tabelas, o atributo que faz
a ligação (no caso, Cod_Curso) é considerado chave estrangeira ou chave externa. Isto implica em várias regras
de verificação num SGBD, pois um aluno não pode ter um código de curso que não existe na tabela de cursos,
para citar um exemplo.
Existem várias regras definidas por Codd para caracterizar um SGBD relacional. Em resumo, pode-se
dizer que todos os dados do SGBD devem ser armazenados em tabelas e que a manipulação dessas tabelas
através de inclusão, exclusão, alteração e consultas deve gerar novas tabelas
2 Linguagem SQL
Atualmente, a linguagem SQL assume um papel muito importante nos sistemas de gerenciamento de
banco de dados (SGBDs), podendo ter muitos enfoques. Através de comandos SQL, os usuários podem montar
consultas poderosas sem a necessidade de criação de um programa, ou ainda utilizar comandos SQL embutidos
em programas de aplicação que acessam os dados armazenados. O DBA, pessoa responsável pela administração
de banco de dados pode realizar suas tarefas através de SQL. Da mesma forma, a linguagem pode ser usada
como linguagem cliente/servidor, linguagem para banco de dados distribuídos e caminho de acesso a outros
bancos de dados ou outras plataformas.
Devido ao fato de possuir várias aplicações, a linguagem SQL provê suporte a várias funções de um
SGBD. Ela consiste de:
.
DDL (linguagem de definição de dados), onde os dados a serem armazenados são definidos e
estruturados;
DML (linguagem de manipulação de dados), que permite a inclusão, remoção, seleção ou atualização de
dados armazenados no banco de dados;
.
Controle de acesso, permitindo proteção dos dados de manipulações não autorizadas;
.
Restrições de Integridade, que auxiliam no processo de definição da integridade dos dados, protegendo
contra corrupções, inconsistências e falhas do sistema de computação.
Além dessas características principais, ainda podemos citar o suporte a visões, onde são especificadas
as consultas disponíveis através de tabelas virtuais e especificação de transações, para garantia do
compartilhamento dos dados.
.
2.1. Como definir o banco de dados com SQL
Para definir o banco de dados utilizamos os comandos de definição de dados de SQL, que a transformam
em DDL.
2.1.1 Create Database
O comando create database não está presente em todos as versões de SQL, mas serve para definir um
nome para o banco de dados a ser criado. Por exemplo:
create database controle_bancário
2.1.2 Create Table
O comando create table permite a definição de uma relação ou tabela com restrições de integridade de
entidade e referencial dentro do banco de dados definido. Para isso ele contém as cláusulas:
primary key: indica os atributos que formam a chave primária;
unique key:
indica os atributos que formam a chave candidata;
foreign key:
indica os atributos que formam a chave estrangeira e o nome da relação referida pela
chave estrangeira
Formato do comando create table:
CREATE TABLE nome_tabela
(nome_atributo tipo_de_dado [NOT NULL],...,
primary key (nome_atributo1, nome_atributo2,...)
foreign key (nome_atributo) references nome_relação)
Ex: BD bancário:
create table cliente (
cliente_nome char(20) not null,
rua char(30),
cidade char(30,
primary key (cliente_nome))
create table agência (
agencia_nome char(15) not null,
ativos integer,
cidade char(30),
primary key (agencia_nome))
create table depósito (
agencia_nome char(15) not null,
conta_numero char(10),
cliente_nome char(20) not null,
saldo integer,
primary key (conta_numero, cliente_nome)
foreign key (agencia_nome) references agencia,
foreign key (cliente_nome) references cliente)
Tipos de dados suportados pelo Personal Oracle 7:
Tipo Comentários
CHAR dados alfanuméricos de até 255 caracteres. Acrescenta espaços
depois do valor para complementar o tamanho da coluna
DATE Inclui século, ano, mês, dia, hora, minuto e segundo
LONG strings alfanuméricas de tamanho variado de até 2 gigabytes
(memo)
LONG RAW Dados binários até 2 gigabytes (BLOB) - gráficos, sons, vídeo,
etc.
NUMBER Números positivos ou negativos com ou sem ponto decimal
RAW Dados binários até 255 bytes
RAWID String hexadecimal representando o endereço único de uma linha
em uma tabela
VARCHAR2 Dados alfanuméricos de tamanho variado. Até 2000 caracteres.
Exercício 1: Criar as tabelas sql para os esquemas abaixo:
a) BD hospitalar
médicos(codm, nomem, idade, especialidade)
pacientes(codp, nomep, idade, problema)
consultas (codm, codp, data, hora)
b) Zoológico:
animais (codA, tipo, nome, peso, idade)
jaulas (nro, area)
alocação (nro_jaula, codA, data_entrada)
zeladores (codZ, nomez, tempo_serviço)
manutenção (codZ, nro, horas)
2.1.3 Alter Table
O comando Alter Table permite modificar a estrutura de uma tabela já definida. Pode-se adicionar uma
coluna à tabela ou modificar uma coluna existente. A sintaxe do comando é mostrada a seguir:
alter table nome_tabela
[add nome_coluna tipo_dados; |
modify nome_coluna tipo_dados;]
O comando a seguir altera a estrutura da tabela agência para aumentar o tamanho do campo cidade:
alter table agência
modify cidade char(40);
A seguir é acrescentado o atributo país na tabela agência:
alter table agência
add país char(20)
2.1.4 Drop Table
Para remoção de uma tabela, usa-se o comando drop table, o qual remove todas as informações sobre
a relação (inclusive do catálogo)
Ex: drop table agência
2.2. Manipulação com tabelas SQL
Para manipular com tabelas do banco de dados, podem ser usados comandos de inserção, exclusão,
alteração e consulta de tabelas. Os comandos de atualização de banco de dados com SQL são mostrados a
seguir:
2.2.1 Inserção de Tuplas
insert into nome_tabela values
Ex: Inserção do cliente João:
insert into cliente
values (“João”, “A”, “Porto Alegre”)
Obs: valores nulos podem estar presentes em alguns atributos
insert into cliente values (“José”, null, “Santa Cruz do Sul”)
2.2.2 Alteração de Tuplas
update nome_tabela
set alteração
[where condição]
Exemplos:
Alterar a tabela de depósitos para acrescentar 5% de juros em todos os saldos:
update deposito
set saldo = saldo * 1.05
Alterar a cidade do cliente João para “Belém”
update cliente
set cidade = “Belém”
where cliente_nome = “João”
Acrescentar 6% de juros às contas com saldo maior que 10000:
update deposito
set saldo = saldo * 1.06
where saldo > 10000
2.2.3 Remoção de Tuplas
delete from nome_tabela
[where condição]
Ex: Remover todos os depóstos da conta de Smith:
delete from deposito
where cliente_nome = “Smith”
Remover todas as tuplas da tabela cliente:
delete cliente
Exercício 2: Remover as agências da cidade de Belém
2.3. Consultando Tabelas SQL
Toda consulta SQL é baseada no modelo relacional, ou seja, retorna uma tabela como resposta. Para
definir uma consulta, basta informarmos o que queremos e não como fazê-lo, pois SQL é uma linguagem nãoprocedural.
2.3.1 Comando Select
Estrutura básica de uma consulta SQL:
select
from
[where
Select seleciona as colunas que deverão compor a tabela resultante. Os nomes dos atributos devem ser
os mesmos definidos no banco de dados. É uma cláusula obrigatória em qualquer consulta.
From indica as tabelas do banco de dados que devem ser consultadas. Também é obrigatória em qualquer
consulta.
Where indica uma condição pela qual os dados serão filtrados. A condição especificada deve retornar
verdadeiro ou falso. Para cada linha da tabela, o interpretador SQL verifica se atende a condição especificada
nesta cláusula e adiciona a linha na resposta caso seja verdadeira a avaliação. É uma cláusula opcional de
consulta.
- Observações:
Caso a cláusula where seja omitida, o predicado P (condição) é verdadeiro;
.
A lista de atributos A , A , ..., A pode ser substituida por um asterisco (*) para selecionar todos os
.
12 n
atributos de todas as relações da cláusula from;
SQL forma o produto cartesiano (relaciona cada linha da primeira tabela com todas as linhas da outra
tabela) das relações chamadas na cláusula from, verifica a condição da cláusula where, e então, projeta
.
o resultado para os atributos da cláusula select;
O resultado de uma consulta SQL é uma tabela.
.
Operadores lógicos: and, or e not
.
Operadores de comparação: >, >=, <, <=, = , != (ou <>)
.
Exemplo: Dada a seguinte tabela de clientes:
cliente_nome rua cidade
José Pe. Eutíquio Belém
Maria Pres. Vargas Fortaleza
Joana Alm Barroso Belém
Rogério Praia de Belas Porto Alegre
Para consultar os clientes (todos os atributos) que moram em Belém:
select *
from clientes
where cidade = “Belém”
O resultado seria:
cliente_nome rua cidade
José Pe. Eutíquio Belém
Joana Alm Barroso Belém
Exercício 3: Consultar no BD hospitalar:
a) O nome dos médicos com idade > 22 ou com especialidade diferente de traumatologia;
b) o nome e o problema dos pacientes com menos de 24 anos;
c) as consultas para o dia 26/09/96 após as 15hs.
2.3.1.1 Comando SELECT - Cláusula DISTINCT
A cláusula DISTINCT é usada para remover as duplicações de linhas no resultado da consulta.
select distinct cliente_nome
from depósito
Ex: Se a tabela depósito tiver os dados: O resultado da consulta será:
Agência_
nome
conta_
número
cliente_n
ome
saldo
Ag1 001 João 100
Ag2 002 José 200
Ag3 003 Smith 150
Ag3 003 José 350
cliente_nome
João
José
Smith
Exercício 4: No BD hospitalar:
a) Buscar todas as especialidades dos médicos;
b) Buscar todas as datas de consultas com horário após as 15hs
c) Buscar todas as idades dos médicos
2.3.1.2 Comando SELECT - Retorno de valores calculados
O comando Select permite retornar expressões aritméticas como resultado da consulta. Cada atributo
especificado na cláusula Select também é uma expressão, e como tal pode ser manipulada. Essa é uma forma
de mostrar resultados que na verdade não estão armazenados no banco de dados. Para atributos numéricos,
podemos utilizar +, -, *, /. E para atributos do tipo string pode-se utilizar concatenação ||. Além desses
operadores, ainda existem outras funções que serão mostradas na seção a seguir.
Exemplo: Buscar o código do médico e os dias consecutivos de suas consultas:
select codm, data + 1
from consultas
Exercício 5: buscar a idade em meses de todos os pacientes e seus nomes
Obs: Pode-se dar nomes novos as colunas resultantes de tabelas em algumas implementações de sql. Por
exemplo:
select codm, data + 1 dia_consecutivo
from consultas
Esta consulta resultaria em uma tabela com os atributos codm e dia_consecutivo.
2.3.1.3 Comando SELECT - Funções de Agregação
As funções de agregação operam sobre um conjunto de linhas e algumas são mostradas a seguir:
a) Count: contador de ocorrências
Ex: 1) total de linhas (tuplas) da tabela de pacientes:
select count (*)
from pacientes
2) Quantas consultas para o dia 26/05/96 existem?
select count (data) {não conta nulos}
from consultas
where data = “26/05/96"
b) Sum: somador de valores de atributos numéricos
Ex: Soma dos saldos do dos clientes da agência Ag1:
select sum (saldo)
from depósito
where agência_nome = “Ag1"
c) Avg: Média de valores de atributos numéricos
Ex: Obter média de idade dos médicos ortopedistas:
select avg (idade)
from médicos
where especialidade = “ortopedia”
d) Max / Min: Maior/Menor valor de um atributo
Ex: Maior e menor saldo de contas em depósito:
select max (saldo), min (saldo)
from depósito
2.3.2 Cláusula WHERE
Na cláusula WHERE são especificadas as condições de busca da tabela. Podem ser usados vários
operadores relacionais, como =, >, <, <=, >=, <> e ainda operadores de comparação de padrões em valores,
como LIKE e comparação de nulos.
2.3.2.1 Comparação [NOT] LIKE
Permite a definição de padrões de busca
*
Padrões possíveis:
a) Like “c*” - o valor do atributo inicia com o caracter “c”
b) Like “*c” - o valor do atributo termina com “c”
c) Like “*c*” - o valor do atributo possui o caracter “c” no meio da cadeia
Obs: algumas implementações de SQL usam % no lugar de *
Ex:Buscar o nome de todos os pacientes que começam com a letra C:
select nomep
from pacientes
where nomep like “C*”
Buscar o código do médico, paciente e horário para todas as consultas marcadas para o ano de 1996:
select codm, codp, hora {ou *}
from consultas
where data like “*96"
2.3.2.2 Comparação de nulos IS [NOT] NULL
* Permite o teste sobre valores nulos de atributos
Ex: buscar dados dos médicos que não têm especialidade:
select *
from medicos
where especialidade is null
2.3.3 Cláusula UNION
* Permite a união de duas tabelas compatíveis (equivalente ao operador c da álgebra relacional;
Ex: buscar o nome de todas as pessoas cadastradas no hospital:
select nomem
from medicos
union
select nomep
from pacientes
Buscar todos os clientes com empréstimo ou depósito na agência Ag1:
(select cliente_nome
from depósito
where agencia_nome = “Ag1")
union
(select cliente_nome
from empréstimo
where agencia_nome = “Ag1")
obs: a cláusula Union retira as repetições. Em algumas versões de SQL, pode-se utilizar UNION ALL, que
retorna todas as tuplas das tabelas unidas, mesmo havendo repetição.
2.3.4.Cláusula INTERSECT
Permite a interseção de duas tabelas compatíveis (operador 1 da álgebra relacional), retornando apenas
as linhas que pertencem às duas tabelas
Ex: buscar todos os clientes da Ag1 que possuem uma conta e empréstimo:
(select distinct cliente_nome
from depósito
where agencia_nome = “Ag1")
intersect
(select distinct cliente_nome
from empréstimo
where agencia_nome = “Ag1")
2.3.5 Claúsula MINUS
* Permite a subtração de duas tabelas compatíveis (operador - da álgebra relacional), retornando apenas
as linhas que pertencem à primeira tabela, com exceção das que aparecem na segunda tabela.
Ex: Buscar todos os clientes da Ag1 que possuem conta mas não possuem empréstimo:
(select distinct cliente_nome
from depósito
where agencia_nome = “Ag1")
minus
(select distinct cliente_nome
from empréstimo
where agencia_nome = “Ag1")
2.4 Consultas envolvendo mais de uma tabela do BD
Quando a resposta de uma consulta precisa ser buscada em várias tabelas do banco de dados, então é
necessário especificar quais tabelas serão usadas na consulta SQL. Para isso é utilizada a cláusula FROM.
Como SQL é baseada no modelo relacional, a forma de juntar tabelas é através de produto cartesiano das
mesmas. Por exemplo, se quisermos usar duas tabelas, a de médicos e a de consultas, mostradas abaixo, o
resultado é uma tabela que contém relacionamentos entre todos os médicos e todas as consultas. Obviamente
que várias tuplas do resultado não são úteis, portanto temos que especificar quais as tuplas úteis através da
cláusula WHERE.
médicos(codm, nomem, idade, especialidade)
codm nomem idade especialidade
M1 João 33 traumatologia
M2 José 23 clínica geral
M3 Maria 30 obstetrícia
consultas (codm, codp, data, hora)
codm codp data hora
M1 P1 12/12/1997 15
M1 P2 13/12/1997 14
M2 P1 05/10/1997 8
Se a consulta fosse:
select *
from médicos, consultas
O resultado seria:
codm nomem idade especialidade codm codp data hora
M1 João 33 traumatologia M1 P1 12/12/1997 15
M1 João 33 traumatologia M1 P2 13/12/1997 14
M1 João 33 traumatologia M2 P1 05/10/1997 8
M2 José 23 clínica geral M1 P1 12/12/1997 15
M2 José 23 clínica geral M1 P2 13/12/1997 14
M2 José 23 clínica geral M2 P1 05/10/1997 8
M3 Maria 30 obstetrícia M1 P1 12/12/1997 15
M3 Maria 30 obstetrícia M1 P2 13/12/1997 14
M3 Maria 30 obstetrícia M2 P1 05/10/1997 8
O resultado expressa valores que nem sempre são verdadeiros. Por exemplo, existe uma linha com
médico M1 e ao lado, uma consulta do médico M2. Se quisermos saber os dias de consulta do médico João,
temos que escolher somente as linhas onde o codm em médicos é igual a codm em consultase o nome do médico
seja João. Pode-se perceber que para saber datas de consultas basta olharmos na tabela de consultas, mas esta
somente contém o código dos médicos. Portanto se quisermos avaliar o nome do médico, temos que buscar na
tabela de médicos. Daí a necessidade de juntar as tabelas pelo código do médico. A esta operação damos o
nomes de JOIN, ou seja, junção de valores relacionados. A consulta SQL que obtém tal resposta é:
select data
from médicos, consultas
where médicos.codm = consultas.codm and médicos.nomem = “João"
O resultado desta consulta é
data
12/12/1997
13/12/1997
Na cláusula FROM, podemos definir quantas tabelas forem necessárias. A única restrição é de que as
chaves estrangeiras devem ser igualadas para que os dados retornados sejam verdadeiros.
Ex: Buscar o nome dos médicos com consulta marcada para o dia 22/05/96:
select medicos.nomem
from medicos, consultas
where consultas.data = “22/05/96" and
medicos.codm = consultas.codm
* Pode-se ter variáveis de tupla associadas a cada tabela declarada na cláusula from
Ex:
select M.nomem, P. nomep
from consultas C, medicos M, pacientes P
where
C.codm = M.codm and
C.codp = P.codp and
C. hora = 15
Exercício 6: Buscar nome e idade dos médicos com consulta marcada com a paciente Ana.
2.5. Subconsultas
Na linguagem SQL ANSI podem ser definidas subconsultas através das cláusulas IN, ANY, ALL,
EXISTS e CONTAINS. Entretanto, somente podem ser definidas subconsultas na cláusula WHERE..
2.5.1 Cláusula [NOT] IN
Esta cláusula estabelece a relação de pertinência entre conjuntos (tabelas). Sua avaliação retorna um valor
booleano.
*sintaxe: where
Ex: Buscar nome de todos os pacientes sem consulta marcada:
select nomep
from pacientes
where codp not in
(select codp
from consultas)
A subconsulta (select codp from consultas) é avaliada primeiro, retornando uma tabela com todos os
códigos de pacientes que possuem consulta marcada. Em seguida a consulta principal é processada selecionando
os nomes dos pacientes cujo código não se encontra no resultado da subconsulta. Pode-se observar que essa
consulta também poderia ser descrita através da cláusula minus, subtraindo os pacientes com consulta do
conjunto de todos os pacientes cadastrados (mostre como!).
Uma outra observação importante é que um item (codp) é comparado a um conjunto (o resultado da
subconsulta). A cláusula IN comporta-se como o operador pertence da matemática, comparando um elemento
a um conjunto de elementos do mesmo tipo. A subconsulta não poderia, por exemplo, retornar código de médico
ao invés de pacientes, ou seja, o elemento comparado tem que ser do mesmo tipo dos elementos do conjunto.
Ex: Buscar nome e problema dos pacientes com idade > 21 e que também são médicos:
select nomep, problema
from pacientes
where idade > 21 and nomep in
(select nomem
from medicos)
Exercício 7:
Buscar todos os clientes da Ag1 que possuem conta e empréstimo (com cláusula in):
Buscar todos os clientes da Ag2 que possuem conta mas não possuem empréstimo.
2.5.2 Cláusula ANY
Esta cláusula permite outras formas de comparação elemento-conjunto. Ao invés de somente verificar
se um elemento pertence a um conjunto, podemos verificar se um elemento é maior, menor, diferente ou até
igual a qualquer elemento do conjunto resultante da subconsulta.
= any (
> any (
atributo das tuplas resultantes da subconsulta}
< any (
<> any (
Ex: Buscar o nome de todos os pacientes, exceto o mais idoso:
select nomep
from pacientes
where idade < any
(select idade
from pacientes)
Exercício 8: Buscar o nome e a idade do médico mais jovem.
2.5.3 Cláusula ALL
Esta cláusula é utilizada quando uma condição deve ser satisfeita para todos os elementos de um
conjunto.
= all (
> all (
< all (
<> all (
Ex:
1) Buscar o nome dos clientes que tem saldo menor que todos os clientes com depósto na agência
Ag3:
select cliente_nome
from deposito
where saldo < all
(select saldo
from deposito
where agencia_nome = “Ag3")
2)Buscar o nome dos médicos com consultas marcadas para horários mais tarde que todas as
consultas da médica Maria
select nomem
from medicos m, consultas c
where m.codm = c.codm and
c.hora > all
(select hora
from consultas c1, medicos m1
where c1.codm = m1.codm and
m1.nomem = “Maria”)
Exercício 9: Buscar os dados dos pacientes com consultas marcadas para horários anteriores a todos os
horários marcados para o dia 22/06/96
2.5.4 Cláusula [NOT] EXISTS
Esta cláusula testa se uma subconsulta tem alguma tupla em seus resultados. Se for usado o NOT
antes de EXISTS, o resultado é o contrário.
Ex: Podemos reescrever a consulta: Encontre os clientes que possuem conta e empréstimo na Ag1.
select cliente_nome
from cliente
where exists (select *
from deposito
where deposito.cliente_nome = cliente.cliente_nome and
agencia_nome = “Ag1")
and exists (select *
from emprestimo
where emprestimo.cliente_nome = cliente.cliente_nome and
agencia_nome = “Ag1")
Ex2: Considere novamente a consulta: “Encontre todos os clientes que têm uma conta em todas as
agências localizadas em “StaCruz”. (usando not exists e minus)
select distinct S.cliente_nome
from deposito S
where not exists ((select agencia_nome
from agencia
where agencia_cidade = “StaCruz”)
minus
(select T.agencia_nome
from deposito T
where S.cliente_nome = T.cliente_nome))
Exercício 10 (BD hospitalar):
a)Buscar o nome de todos os médicos com consulta marcada.
b)Buscar o nome do médico mais jovem.
2.5.5 Cláusula CONTAINS
A cláusula CONTAINS foi introduzida originalmente no sistema R, e não aparece no padrão ANSI pois
o processamento dessa cláusula é muito custoso.
As construções in, any e all permitem-nos testar um valor simples contra membros de um conjunto
inteiro. Uma vez que o select gera um conjunto de tuplas, podemos querer comparar conjuntos para determinar
se um conjunto contém todos os membros de algum outro conjunto. Tais comparações são feitas em SQL
usando contains e not contains. Em outras palavras, esta cláusula é equivalente ao operador contém .
da
matemática
Ex: Considere a mesma consulta do Ex2 acima. Para cada cliente, precisamos ver se o conjunto de todas
as agências na qual um cliente possui uma conta contém o conjunto de todas as agências de “StaCruz”.
A consulta pode ser escrita assim:
select distinct S.cliente_nome
from deposito S
where (select T.agencia_nome
from deposito T
where S.cliente_nome = T.cliente_nome)
contains
(select agencia_nome
from agencia
where agencia_cidade = “StaCruz”)
Exercício 11: identifique outras possíveis consultas que possam ser reescritas com a cláusula contains.
2.6 Ordenando a exibição de tuplas - Cláusula ORDER BY
Esta cláusula permite a ordenação do resultado da consulta. É utilizada após a cláusula Where.
*
Sintaxe:
ORDER BY
{desc - ordem decrescente, asc - ordem crescente}
Ex: Buscar os nomes dos clientes em ordem alfabética com empréstimo na agência “Ag3":
select distinct cliente_nome
from emprestimo
where agencia_nome = “Ag3"
order by cliente_nome
Ex2: Listar todos os empréstimos na ordem decrescente de quantia e os empréstimos com a mesma quantia
devem ser ordenados pelo número do empréstimo.
select *
from emprestimo
order by quantia desc, emprestimo_numero asc
Exercício 12: Buscar os dados de todas as consultas do paciente Carlos, ordenadas de forma decrescente
pela hora da consulta.
2.7 Agrupando tuplas - Cláusulas GROUP BY e HAVING
*
Group by: agrupa partes do resultado de uma consulta, a partir do qual é possível utilizar funções
de agregação (avg, min, max, count, sum);
*
Having: Especifica condições para a formação de um grupo. Só existe associado à cláusula group
by.
*
As condições só podem envolver os atributos a serem buscados ou alguma função de agregação.
Exemplos:
1) Encontre o saldo médio de conta em cada agência:
select agencia_nome, avg(saldo)
from deposito
group by agencia_nome
obs: as duplicatas são mantidas.
2) Encontre o número de depositantes de cada agência:
select agencia_nome, count(distinct cliente_nome)
from deposito
group by agencia_nome
obs: evita-se as duplicações com a cláusula distinct.
3) Buscar as agências nas quais a média dos saldos é maior do que 1200:
select agencia_nome, avg(saldo)
from deposito
group by agencia_nome
having avg(saldo) > 1200
4) Obter as agências com maior média de saldos:
select agencia_nome
from deposito
group by agencia_nome
having avg(saldo) >= all (select avg(saldo)
from deposito
group by agencia_nome)
obs: não é possível usar max(avg(...)).
5)
Encontre a média dos saldos de todos os depositantes que vivem em “StaCruz” e têm pelo menos 3
contas. (uso de where e having juntos)
select avg(saldo)
from deposito, cliente
where deposito.cliente_nome= cliente.cliente_nome and
cliente.cidade= “StaCruz”
group by deposito.cliente_nome
having count(distinct conta_numero) >= 3
obs: O predicado na cláusula where é aplicado antes do predicado de having.
Exercício 13:
1) buscar todas as datas de consultas e o total de consultas para esta data.
2) buscar somente as datas e o total de consultas para horários após as 14 horas
3) buscar somente as datas e o total de consultas para as datas onde haja mais de uma consulta marcada.
4) buscar, para a tabela de médicos, todas as idades e o total de médicos com a mesma idade.
5) buscar o nome dos médicos com mais de uma consulta marcada.
2.8 Comandos de atualização + comandos de consulta
Expressões de consulta podem estar associadas aos comandos de atualização de dados, para melhor
restringir o universo de tuplas a serem atualizadas. Os comandos usados são os já apresentados delete e update.
Exemplos:
1) Remover todas as contas em agências localizadas em Canoas
delete deposito
where agencia_nome in
(select agencia_nome
from agencia
where agencia_cidade = “Canoas”)
2) Remover todas as consultas do médico “João”:
delete consultas
where codm in ( select codm
from medicos
where nomem = “João”)
3) Acrescentar 5% de juros sobre contas cujos saldos sejam maiores do que a média:
update deposito
set saldo = saldo * 1.05
where saldo > (select avg (saldo)
from deposito)
4) Passar para as 19hs todas as consultas da paciente Ana
update consultas
set hora = “19:00"
where codp in (select codp
from pacientes
where nomep = “Ana”)
3. Visões em SQL
Um banco de dados é composto de várias tabelas relacionadas. Se quisermos obter qualquer informação,
devemos citar essas tabelas nas consultas. Algumas consultas são bastante utilizadas no dia-a-dia e são
complexas de escrever, devido ao uso de subconsultas. Muitas vezes os usuários não têm conhecimento
adequado para formular tais consultas. Nesse caso, o DBA poderia colocar algumas consultas prontas à
disposição, as quais seriam acessadas com consultas simples.
As visões são tabelas virtuais derivadas das tabelas do banco de dados e são úteis para garantir segurança
de acesso ao BD, disponibilização de tabelas que melhor se adequam às necessidades de uma aplicação e
facilitade de acesso aos dados sem ter que formular consultas complexas.
Exemplos:
a) Um funcionário do hospital não deve ter acesso a todos os dados pessoais de um paciente, somente ao
seu código e nome;
b) Pode ser interessante vincular os dados de um médico aos dados de suas consultas
A criação de visões em SQL obedece a seguinte sintaxe:
create view
obs: a visão criada não é executada, e sim, armazenada no catálogo. Porém, para o usuário é como se
tivesse uma visão
Para remover uma visão, usa-se o comando
drop view
obs: a visão especificada é eliminada (a definição é removida) e todas as visões definidas em termos
desta visão também são automaticamente anuladas. Porém os dados continuam existindo, já que a visão
é uma tabela virtual.
Exemplos:
a) A seguir é criada uma visão chamada DadosPac, que, do ponto de vista do usuário comum,
corresponde a uma tabela como outra qualquer. Esta visão retorna somente o código e o nome de todos os
pacientes do hospital. Se um funcionário desse hospital tiver acesso somente a tabela (visão) DadosPac, então
ele não verá o problema do paciente, o qual é um atributo da tabela pacientes.
create view DadosPac as
select codp, nomep
from pacientes
b) A visão a seguir, chamada MedCons contém os nomes dos médicos e suas datas e horas de
consulta. Observe que para obter essa informação, precisamos das tabelas de médicos e consultas e da junção
entre os atributos codm das duas tabelas. A visão facilita a consulta já que, após a sua criação, basta pedirmos
para ver todos os dados da tabela MedCons que automaticamente a consulta da visão é processada, sem termos
que defini-la novamente.
create view MedCons as
select nomem, data, hora
from medicos, consultas
where medicos.codm = consultas.codm
Obs:
Uma vez definida uma visão, qualquer operação de consulta ou atualização pode ser aplicada sobre
ela.
Operações realizadas sobre uma visão se refletem diretamente sobre as tabelas físicas das quais ela
deriva.
3.1 Consultas sobre Visões
Nas operações de recuperação (select) o processo de conversão das operações em visão para operações
em tabelas básicas é bem direto e funciona bem.
Exemplos:
a)
o funcionáro do hospital deseja buscar o nome de todos os pacientes cadastrados que começam com
a letra R:
select nomep
from DadosPac
where nomep like “R*”
O sistema (tradutor) converte esta consulta em:
select nomep
from pacientes
where nomep like “R*”
b) Buscar o nome dos médicos com consulta marcada para horários após as 18hs no mês de outubro:
select nomem
from MedCons
where hora > 18 and data like “*/10/94"
O sistema (tradutor) converte esta consulta em:
select nomem
from medicos, consultas
where medicos.codm=consultas.codm and
hora > 18 and
data like “*/10/94"
3.2 Atualizações
*Operações de inserção, atualização e remoção de tuplas (insert, update e delete) em uma visão muitas
vezes geram problemas. Nem todas as visões são atualizáveis
Situação 1: Inserção de tuplas na visão MedCons
Problemas:
- violação da regra de integridade de entidade; (a chave primária não está na visão)
- perde-se o relacionamento entre médicos e consultas
Situação 2: Visão que relaciona total de consultas de um determinado paciente.
create view ConsPac as
select codp, count (*)
from consultas
group by codp
Problemas:
-inserção nesta visão viola a regra de integridade de entidade e não se pode inserir no campo count(*);
-Não se pode atualizar um atributo calculado.
3.3 Vantagens das visões
-Elas fazem com que o mesmo dado seja visto por diferentes usuários de diferentes formas (ao mesmo
tempo)
-A percepção do usuário é simplificada;
-É óbvio que o mecanismo da visão possibilita aos usuários centrarem-se unicamente nos dados que lhes
são importantes e ignorarem o resto. O que talvez não seja tão óbvio é que, pelo menos na recuperação
(select), tal mecanismo também possa simplificar consideravelmente as operações de manipulação de
dados feitas pelo usuário.
-
Segurança automática para os dados ocultos
Dados ocultos são aqueles não visíveis através de determinada visão. Ficam claramente protegidos
por meio desta visão específica. Assim, obrigar os usuários a acessar o banco de dados através de
visões é um mecanismo simples, porém eficaz de controle de autorização.
3.4 Algumas soluções para problemas com visões
-
Impedir operações de atualização sobre visões
-As tuplas de uma visão devem corresponder a tuplas que tenham condições de serem identificadas nas
tabelas físicas das quais ela deriva;
-Cada atributo de uma visão deve corresponder a um atributo distinto e identificável de alguma das tabelas
físicas das quais ela deriva.
Exercício 14:
Dada o BD de peças e fornecedores:
fornecedores (codf, nomef, cidade)
peças (codp, nomep, peso, cor)
fornecem (codf, codp, qtde)
e dada a definição de visão:
create view peças_pesadas
as select codp, peso, cor from peças where peso > 20
Demonstrar a operação executada (isto é, a forma convertida) do que segue:
a)
select *
from peças_pesadas
where cor = “preto”
b) update peças_pesadas
set cor = “branco”
where peso = 30
c) insert into peças_pesadas
values (“P4", 52, “vermelha”)
4. Autorizações de Acesso
Muitos SGBDs relacionais podem ser acessados por diversos usuários. Cada usuário tem uma
determinada necessidade em relação aos dados armazenados. De acordo com o projeto do banco de dados,
alguns usuários só podem consultar alguns dados, outros podem atualizar, outros podem inserir, etc. Para que
o dado fique protegido do uso indevido de qualquer usuário, a linguagem SQL permite a definição dos
privilégios que cada um pode ter em relação às tabelas criadas no banco de dados.
Os privilégios garantem a segurança e integridade dos dados, bem como a responsabilidade de cada
usuário sobre seus dados específicos.
4.1. Comando Grant
Este comando garante privilégios para os usuários sobre tabelas/visões.
GRANT
Select: pode executar uma consulta sobre a tabela
Insert: pode executar uma inserção sobre a tabela
Delete: pode apagar registros da tabela
Update: pode modificar registros da tabela
All Privileges/all: pode executar qualquer operação sobre a tabela
nome do usuário que vai receber os privilégios. Deve ser um nome cadastrado dentro do
ambiente.
PUBLIC:
concede os privilégios especificados a todos os usuários do ambiente
Exemplos:
- grant select on medicos to Paulo
permite somente consultas do usuário Paulo na tabela de médicos
-grant select, insert, update on consultas to Mary
concede ao usuário Mary os privilégios de seleção, inserção e atualização sobre a tabela consultas
-grant all privileges on DadosPac to public
permite todos os privilégios a todos os usuários sobre a tabela (neste caso, visão) DadosPac.
4.2. Comando Revoke
Este comando revoga os privilégios de acesso aos usuários.
REVOKE
Exemplos:
-revoke select on medicos from Paulo
-revoke all on DadosPac to public - revoga todos os direitos sobre a visão DadosPac
5. Mais Exercícios
1) BD formado pelas seguintes tabelas:
medicos (codm, nomem, idade, especialidade)
pacientes (codp, nomep,idade, problema)
consultas (codm, codp, data, hora)
a) Mostrar todos os dados da tabela de consultas.
SELECT *
FROM CONSULTAS
b) Mostrar os dados dos médicos classificados por ordem de código
SELECT *
FROM MEDICOS
ORDER BY codm ASC
c) Obter os nomes e códigos de todos os médicos cirurgiões
SELECT nomem, codm
FROM MEDICOS
WHERE especialidade= “cirurgia”
d) fornecer os nomes e códigos de todos os médicos e seus respectivos dias de consulta
SELECT nomem, data
FROM medicos, consultas
WHERE medicos.codm = consultas.codm
e) fornecer os nomes dos médicos que possuem alguma consulta marcada com o paciente P4
Várias soluções possíveis:
e.1) SELECT nomem
FROM medicos
WHERE codm in (SELECT codm
FROM consultas
WHERE codp= “P4")
e.2) SELECT nomem
FROM medicos
WHERE “P4" in ( SELECT codp
FROM consultas
WHERE codm = medicos.codm)
e.3) SELECT nomem
FROM medicos
WHERE EXISTS (SELECT *
FROM consultas
WHERE codm = medicos.codm and
CODP = “P4")
f) mostrar os nomes dos médicos que não têm consulta marcada com a paciente P4
SELECT nomem
FROM medicos
WHERE NOT EXISTS (SELECT *
FROM consultas
WHERE codp = “P4" and codm = medicos.codm)
g) Mostrar os nomes dos médicos que não tem consulta marcada com a paciente Maria
SELECT nomem
FROM medicos
WHERE codm IN
(SELECT codm
FROM consultas
WHERE codp NOT IN ( SELECT codp
FROM pacientes
WHERE nome = “Maria”)
h) Mostrar o código dos pacientes que tem consulta marcada para o dia 23/09/96 ou com médicos pediatras.
(SELECT codp
FROM consultas
WHERE data = “23/09/96")
UNION
(SELECT codp
FROM consultas
WHERE codm IN (SELECT codm
FROM medicos
WHERE especialidade = “pediatra”))
obs: poderia ter sido utilizado o OR ao invés de UNION na primeira subconsulta?
i) Mostrar os nomes dos médicos que não fornecem consultas para os pacientes de obstetras.
SELECT nomem
FROM medicos
WHERE codm NOT IN
(SELECT codm
FROM consultas
WHERE codp IN
(SELECT C.codp
FROM medicos M, consultas C, pacientes P
WHERE M.especialidade = “obstetrícia” and
M.codm = C.codm and
C.codp = P.codp))
2) BD formado pelas seguintes tabelas:
empregado (codemp, salário, função, codd)
departamento (codd, cidade, nome)
joga (codemp, codtime, posição)
time(codtime, nome, endereço)
a) Mostrar os códigos dos empregados que ganham acima da média dos salários
SELECT codemp
FROM empregado
WHERE salario > (SELECT avg(salario) from empregado)
b) mostrar os departamentos que não tem empregados
SELECT codd
FROM departamento
WHERE codd NOT IN
(SELECT distinct codd
FROM empregado)
c) mostrar os departamentos com média salarial inferior a 500 (obs: não cai na prova)
SELECT codd
FROM empregado
GROUP BY codd
HAVING avg(salario) < 500
d) mostrar os departamentos que possuem mais de 10 programadores (obs: não cai na prova)
select codd
from empregado
where funcao = “programador”
group by codd
having count(*) > 10
e) mostrar o nome do time do empregado de maior salário
SELECT nome
FROM time
WHERE codtime IN
(SELECT codtime
FROM joga
WHERE codemp IN
(SELECT codemp
FROM empregado
WHERE salario IN
(SELECT Max(Salario)
FROM empregado)))
f) mostrar o maior salário dos empregados que jogam na ponta direita
SELECT max(salario)
FROM empregado, joga
WHERE posicao = “ponta direita” and (joga.codemp = empregado.codemp)
g) mostrar a posição e o código dos empregados de maior salário em cada departamento (obs: não cai na prova)
select codemp, posicao
from joga
where codemp in
(select codemp
from empregado
where salario in
(select max(salario)
from empregado
group by codd))
6 O Futuro do Padrão SQL
Recentemente temos assistido ao aumento da popularidade dos bancos de dados orientados a objetos
(OODBMS -Object Oriented Database Management System). Um banco de dados orientado a objetos é aquele
que incorpora objetos complexos, tipos abstratos de dados (Abstract Data Types - ADTs), encapsulamento e
herança [MULL,1994]. Mantelman define um objeto complexo como aquele que armazena procedimentos além
de dados, e estende a definição de um banco de dados orientado a objetos dizendo que não há restrições a dados
baseados em caracteres e que podem ser acomodados objetos multimídia [MANT,1996].
Os bancos de dados orientados a objetos tratam os dados de modo diferente dos bancos de dados
relacionais. Estes implementam acesso aos dados através de linhas e colunas, enquanto os orientados a objetos
têm uma estrutura mais complexa, que não é suportada pelo padrão SQL92.
Como alguns fornecedores de SGBDs começaram a acrescentar a seus produtos características além do
escopo do padrão SQL92, houve a necessidade de se estudar um novo padrão, de modo a garantir a
portabilidade entre os vários SGBDs. Daí a necessidade do SQL3. Algumas das características implementadas
incluem tipos de dados novos e estendidos, incluindo tipos de dados abstratos, múltiplos tipos de estados nulos,
suporte para objetos e identidade de objetos, encapsulamento, herança e triggers.
O SQL92 tem um conjunto de tipos de dados atômicos. No SQL3, estes tipos de dados são mantidos
como tipos de dados pré-definidos, embora alguns tenham sido modificados ou estendidos. Por exemplo, os
tipos de dados character e bit foram estendidos para incluir o conceito de um "objeto maior"(large object). Estes
"large objects" facilitam o uso de objetos multimídia dentro do banco de dados.
Dois tipos adicionais de dados, antes não suportados, foram incluídos: boolean e enumerated. O tipo
boolean somente pode ter os valores "true", "false" e "unknown". O tipo enumerated permite que se defina
domínios cujos valores são restritos a um pequeno conjunto de valores. Como exemplo, teríamos o comando
CREATE DOMAIN cores (azul, vermelho, amarelo) que define um domínio chamado cores e restringe os
valores inseridos na coluna de tipo enumerated a um dos três valores especificados para o domínio
[MELT,1995].
Ao contrário do SQL92, o SQL3 suporta múltiplos estados nulos. Isto significa que diferentes classes
de null são suportadas, permitindo às aplicações definir os significados de determinado estado nulo. Possíveis
significados incluem "unknown" (como vimos acima, no caso do tipo enumerated), "missing", "not applicable"
ou "pending". Cada definição de nulo tem uma representação diferente que o torna identificável durante uma
consulta ou atualização.
O segundo grupo de objeto de dados suportado pela nova versão, e não suportado pela anterior, é o tipo
abstrato de dados (ADT). O ADT é utilizado pelo SQL3 para possibilitar o uso de objetos na estrutura SQL.
O ADT permite aos usuários criar novos tipos de dados definindo tipos abstratos de dados a partir de tipos de
dados pré-definidos ou a partir de outros ADTs já existentes. Os ADTs suportam os conceitos de
encapsulamento e subtipos da orientação a objetos.
Estendendo o que vimos acima, objetos são itens de dados que combinam complexas estruturas de dados
com processos ou métodos para manipular estes dados. A noção de combinar dados e processos é chamada de
encapsulamento. Enquanto os tipos de dados que conhecemos no SQL92 são identificados através de seus
valores, no SQL3, um dado definido como object será identificado por um identificador único de objeto, gerado
quando o objeto é criado. Os conceitos de subtipo e supertipo são baseados no conceito de herança da orientação
a objetos, que é a capacidade de se criar novos objetos a partir de outros já existentes, herdando suas
características. Atributos e métodos podem ser herdados, o que diminui a redundância de informações. No
SQL3, um ADT pode ser um subtipo de outro ADT. Por exemplo, podemos definir um ADT como um objeto
PESSOA. Um subtipo de PESSOA poderia ser ESTUDANTE, que herdaria as propriedades e comportamento
de PESSOA. Um subtipo do subtipo ESTUDANTE poderia ser GRADUANDO, que herdaria as característica
de PESSOA e ESTUDANTE.
Outra importante característica do SQL3 é o suporte a triggers. No padrão SQL92, esta característica
foi deixada de lado porque seu uso foi subestimado. Existe hoje, entretanto, uma grande demanda desta
característica por parte dos usuários, e mais e mais fornecedores estão incluindo suporte a triggers em seus
produtos, como, por exemplo, a Oracle no seu SGBD Oracle7. Um trigger é um objeto do esquema do banco
de dados ou um bloco de código que diz ao banco de dados que outras ações devem ser tomadas depois de
executados certos comandos SQL. Por exemplo, pode-se querer adicionar um registro a uma tabela de log toda
vez que um registro for excluído de uma outra tabela para se manter uma auditoria de quem está realizando as
mudanças. Um trigger seria usado neste caso para iniciar uma sequência de ações (atualizar a tabela de log)
depois que uma função de deleção fosse executada.
O trabalho de revisão de um padrão para a inclusão de novas características é intenso e dinâmico. Os
fornecedores de SGBDs aumentam o poder de seus produtos adicionando mais e mais características, algumas
delas não cobertas pelo padrão atual, o que cria a necessidade de revisão. No caso do SQL3, a inclusão de
características de orientação a objetos é a mais importante.
7 Bibliografia
CAMPOS, Maria Luiza. “Laboratório de Banco de Dados”. Departamento de Ciência da Computação UFRJ.
Disponível no endereço “http://www.dcc.ufrj.br/~labbd/texto/sql.htm”. 1997.
DATE, C. J. “Introdução a Sistemas de Bancos de Dados”. Ed. Campus. 1991.
LIMA, CARLA A. G. “Linguagem SQL”. Banco de Dados I - Notas de Aula. Depto. Informática - UFPA.
1997.
MACHADO, F.N; ABREU, M. “Projeto de Banco de Dados: Uma visão prática”. Érica. 1995.
STEPHENS, R.; PLEW, R.; MORGAN, B.; PERKINS, J. “Teach yourself SQL in 21 days”. 2nd Edition.
SAMS Publishing, USA, 1997.
SQL – STRUCTURED QUERY LANGUAGE
As aplicações em rede são cada dia mais numerosas e versáteis. Em muitos casos, o esquema básico de operação é uma série de scripts que dirigem o comportamento de uma base de dados.
Devido à diversidade de linguagens e de base de dados existentes, a maneira de comunicar entre umas e outras seria realmente complicado de providenciar, a não ser pela existência de padrões que nos permite realizar as operações básicas de una forma universal.
É justamente disso que se trata o Structured Query Language que não é mais do que uma linguagem padrão de comunicação com base de dados. Falamos portanto, de uma linguagem normalizada que nos permite trabalhar com qualquer tipo de linguagem (ASP ou PHP) em combinação com qualquer tipo de base de dados (MS Access, SQL Server, MySQL...).
O fato de ser padrão não quer dizer que seja idêntico para cada base de dados. Na prática, determinadas bases de dados implementam funções específicas que não têm necessariamente que funcionar em outras.
À parte desta universalidade, o SQL possui outras duas características muito apreciadas. Por uma parte, apresenta potência e versatilidade notáveis que contrasta, por outra, com sua acessibilidade de aprendizagem.
Estudo mais aprofundado - ENTRAR
Devido à diversidade de linguagens e de base de dados existentes, a maneira de comunicar entre umas e outras seria realmente complicado de providenciar, a não ser pela existência de padrões que nos permite realizar as operações básicas de una forma universal.
É justamente disso que se trata o Structured Query Language que não é mais do que uma linguagem padrão de comunicação com base de dados. Falamos portanto, de uma linguagem normalizada que nos permite trabalhar com qualquer tipo de linguagem (ASP ou PHP) em combinação com qualquer tipo de base de dados (MS Access, SQL Server, MySQL...).
O fato de ser padrão não quer dizer que seja idêntico para cada base de dados. Na prática, determinadas bases de dados implementam funções específicas que não têm necessariamente que funcionar em outras.
À parte desta universalidade, o SQL possui outras duas características muito apreciadas. Por uma parte, apresenta potência e versatilidade notáveis que contrasta, por outra, com sua acessibilidade de aprendizagem.
Estudo mais aprofundado - ENTRAR
Processamento de Transações
Quando você comprar um livro de uma livraria online, você troca o dinheiro (na forma de crédito) para um livro.Se o crédito é bom, uma série de operações relacionadas garantirá que você obtenha o livro e a livraria obtém seu dinheiro.Contudo, se uma simples operação da série falhar durante o processamento, todo o processamento irá falhar.Você não obtém o livro e a livraria não obtém o seu dinheiro.
A tecnologia responsável por fazer a troca equilibrada e previsíveis é chamada o processamento de transações.Transactions ensure that Resources Data-Oriented not are Updated permanently unless Tudo Operations within the completo Unit Transactional successfully.Combinando um conjunto de operações relacionadas em uma unidade que completamente é bem-sucedida ou falhar totalmente, você pode simplificar a Recuperação de erro e tornar seu aplicativo mais confiável.
Sistemas de processamento de transações consistem em hardware do computador e de software que hospeda um aplicativo orientado a transação que executa as transações de rotina necessárias para conduzir os negócios.Examples include Systems that Gerenciar entrada pedidos de vendas, reservations airline, Payroll, Records funcionário, Manufacturing, and Shipping.
Esta seção fornece informações gerais sobre processamento de transações tanto e informações específicas sobre como Gravar Aplicativos transacionais e gerenciadores recurso usando o Microsoft .NET Framework.
A tecnologia responsável por fazer a troca equilibrada e previsíveis é chamada o processamento de transações.Transactions ensure that Resources Data-Oriented not are Updated permanently unless Tudo Operations within the completo Unit Transactional successfully.Combinando um conjunto de operações relacionadas em uma unidade que completamente é bem-sucedida ou falhar totalmente, você pode simplificar a Recuperação de erro e tornar seu aplicativo mais confiável.
Sistemas de processamento de transações consistem em hardware do computador e de software que hospeda um aplicativo orientado a transação que executa as transações de rotina necessárias para conduzir os negócios.Examples include Systems that Gerenciar entrada pedidos de vendas, reservations airline, Payroll, Records funcionário, Manufacturing, and Shipping.
Esta seção fornece informações gerais sobre processamento de transações tanto e informações específicas sobre como Gravar Aplicativos transacionais e gerenciadores recurso usando o Microsoft .NET Framework.
Nesta seção Controle de Concorrência
|
Assinar:
Postagens (Atom)
