bases de dados i

BASES DE DADOS I
LTSI/2
Universidade da Beira Interior, Departamento de Informática
Hugo Pedro Proença, 2010/2011
A Linguagem SQL
 
 
 
As raízes da linguagem SQL remontam a 1974, altura
em que a IBM desenvolvia um protótipo de um sistema
relacional, designado “System R”.
Simultaneamente foi desenvolvida uma linguagem,
designada SEQUEL, destinada a ser utilizada nesse
sistema e que mais tarde veio a dar origem à
linguagem SQL – Structured Query Language.
Em 1984 foi proposta a sua estandarização, tendo o
ANSI e a ISO criado grupos de trabalho para o efeito.
A Linguagem SQL
 
 
 
 
A primeira versão-standard foi proposta em 1986, com a designação SQL-86.
Surgiram regularmente novas revisões SQL-89, SQL-93 e está em preparação
o lançamento de uma nova, que incorporará características tipicamente
associadas à programação orientada por objectos.
É uma linguagem de alto nível e disponibiliza formas de interacção com
multiplos sistemas gestores de bases de dados.
A linguagem pode ser decomposta em três grupos principais:
 
 
 
DMLData Manipulation Language
DDL Data Definition Language
DAL Data Administration Language
A Linguagem SQL
 
DMLData Manipulation Language
  Conjunto
de instruções que possibilitam a manipulação
da informação existente numa base de dados.
  Inserir
informação
  Alterar informação
  Eliminar informação.
  Consultar informação
A Linguagem SQL
 
DDLData Definition Language
  Conjunto
de instruções que possibilitam a definição da
estrutura de uma base de dados.
  Criar
relações
  Alterar relações
  Eliminar relações
A Linguagem SQL
 
DALData Administration Language
  Conjunto
de instruções que possibilitam a definição da
politica de previlégios sobre a informação existente
numa base de dados.
  Gerir
utilizadores
  Gerir permissões sobre objectos da base de dados
Relações
  Vistas
  Funções e Procedimentos
 
SQL-DDL
 
Como já foi referido anteriormente, o SQL possui simultaneamente
instruções de definição de dados (Data Definition Language - DDL). Estas
servem para definir (criar e/ou alterar) a estrutura da base de dados e
fazer a passagem do modelo conceptual entretanto finalizado para o
modelo físico.
 
Alteração da estrutura da base de dados
 
Criação, Alteração ou Eliminação de relações (CREATE TABLE, ALTER
TABLE e DROP TABLE)
SQL-DDL
 
Tipos de Dados:
 
 
 
A especificação do domínio de cada atributo (conjunto de valores passíveis
de atribuir a cada atributo de cada tuplo de uma relação), pode variar
consoante o SGBD onde é implementada a Base de Dados.
Diferentes SGBD´s incorporam pequenas variantes nos tipos de dados
passíveis de definição e nos tipos de dados disponíveis.
A SQL-92 possui alguns tipos estandardizados, designados tipos primitivos:
 
 
 
 
INTEGER, SMALLINT, DECIMAL, DOUBLE PRECISION, FLOAT: para valores
numéricos
CHAR, VARCHAR: para valores alfanuméricos
DATE, TIME, TIMESTAMP: para valores de Data e Tempo
BIT: para valores booleanos.
SQL-DDL
 
Tipos de Dados:
 
Adicionalmente é também possível definir novos tipos de dados, apesar
destes terem que se basear em tipos de dados primitivos.
 
Este mecanismo é essencialmente um mecanismo de definição de
“ALIAS”.
 
CREATE DOMAIN morada VARCHAR(30)
 
Especifica a criação de um novo tipo de dados, designado “morada”,
e que é formado por um conjunto de caracteres que pode ter no
máximo comprimento igual a 30.
SQL-DDL - Criação
 
Criação de Tabelas
 
CREATE TABLE <nome_tabela>(
<Coluna1> <Tipo1>
[<Opcoes1>],
<Coluna2> <Tipo2> [<Opcoes2>],
...
<Colunan> <Tipon> [<Opcoesn>],
[restrições de integridade]);
 
 
 
 
<Colunai> Nome da i-nesima coluna
<Tipoi>Tipos de dados da i-nesima coluna
<Opcoesi> Opções relativas à i-nésima coluna
Restrições de integridadeEventuais restrições a acrescentar à definição
da tabela (chaves, restrições de referência e restrições de intervalo)
SQL-DDL - Criação
 
Criação de Tabelas
CREATE TABLE Aluno(
Numero INTEGER PRIMARY KEY,
Nome VARCHAR(80) NOT NULL,
MORADA VARCHAR(80)
);
 
Específica a criação de uma nova relação com 3 atributos.
“Numero” é do tipo inteiro e constitui a chave primária da
relação. Nome e Morada são do tipo alfanumérico, sendo
que “Nome” não pode conter valores nulos.
SQL-DDL - Criação
 
Criação de Tabelas
CREATE TABLE Nota(
CodAluno INTEGER,
CodDisciplina INTEGER,
Ano INTEGER,
Nota FLOAT,
CONSTRAINT C1 PRIMARY KEY (CodAluno,
CodDisciplina, Ano),
CONSTRAINT C2 CHECK Nota > 0,
CONSTRAINT C3 CHECK Ano IN (1,2,3,4,5)
);
 
Específica a criação de uma nova relação com 4 atributos. A chave
primária é uma chave composta e existem duas relações de intervalo para
dois dos atributos (“Ano” e “Nota”)
SQL-DDL - Criação
 
Criação de Tabelas
CREATE TABLE Utilizacao_Peca(
CodUtilizacao INTEGER PRIMARY KEY,
CodPeca INTEGER,
Quantidade INTEGER,
CONSTRAINT C1 FOREIGN KEY (codPeca) REFERENCES Peca (CodPeca),
CONSTRAINT C2 CHECK Quantidade > 0
);
 
Específica a criação de uma nova relação com 3 atributos. A chave
primário será o código da utilização de peça e existe uma restrição de
referência. Todos os valores que forem registados no atributo “CodPeca”
devem ter correspondência no atributo “CodPeca” da relação “Peca”.
Existe ainda uma restrição de intervalo.
SQL-DDL - Criação
 
Criação de Tabelas
CREATE TABLE Utilizacao_Peca(
CodUtilizacao INTEGER PRIMMARY KEY,
CodPeca INTEGER,
CONSTRAINT FOREIGN KEY (codPeca) REFERENCES Peca
(CodPeca)
ON UPDATE CASCADE
ON DELETE SET NULL,
);
 
Específica a criação de uma nova relação com 2 atributos. As duas ultimas
linhas servem para especificar que ao actualizar ou eliminar informação
da relação para onde a restrição se refere (Peca) se devem
respectivamente eliminar os respectivos tuplos ou colocá-los com valor nulo.
SQL-DDL - Criação
 
ON DELETE | INSERT | UPDATE <ACCAO>
 
Estas clausulas servem para garantir a consistência da informação na
base de dados após a alteração de informação. Imagine-se o seguinte
esquema de relacionamento:
 
Duas relações (“Obra” e “Responsável”) para registar as obras em curso por parte de uma
empresa de construção, podendo cada obra possuir entre 0 e “n” responsáveis.
Obra
Descrição
Local
Obra
Responsável
123
Prédio
Lisboa
123
José
671
Estrada
Covilhã
123
Rita
129
Saneamento
Fundão
671
Paulo
SQL-DDL - Criação
 
ON DELETE | INSERT | UPDATE <ACCAO>
 
O que aconteceria à informação da relação que regista os
responsáveis de cada obra, caso fossem eliminada tuplos da relação
que regista as obras em andamento ?
 
X
Informação inconsistente ?
Obra
Descrição
Local
123
Prédio
Lisboa
671
Estrada
Covilhã
129
Saneamento
Fundão
?
Obra
Responsável
123
José
123
Rita
671
Paulo
SQL-DDL - Criação
 
ON DELETE | INSERT | UPDATE <ACCAO>
 
Para resolver estas situações especifica-se na criação da tabela que contém
restrições de referência (isto é, chaves externas), o que se pretende fazer
em caso de alteração da informação na relação referida.
 
CREATE TABLE Encarregado(
CodObra INTEGER,
Encarregado VARCHAR(30),
CONSTRAINT C1 PRIMARY KEY (CodObra, Encarregado)
CONSTRAINT C2 FOREIGN KEY (CodObra) REFERENCES Obra(CodObra)
ON UPDATE CASCADE
ON DELETE CASCADE);
SQL-DDL - Criação
 
ON DELETE | INSERT | UPDATE <ACCAO>
 
Com base na definição anterior, ao alterar informação na relação
“Obra” (eliminar tuplos, ou alterar o código de obra, por exemplo) vaise efectuar a mesma operação na relação “Responsável”. A este
processo designa-se operação em cascata.
 
 
 
Após a eliminação de um tuplo da relação “Obra”, todos os tuplos da
relação “Responsável” que contivessem valor para o atributo chave externa
igual ao eliminado, vão também ser eliminados.
Caso apenas seja alterada a relação “Obra”, então o código
correspondente à chave externa vai também ser actualizado em todos os
tuplos necessários.
O analista poderia considerar, por outro lado, que seria mais útil colocar os
respectivos valores para o campo chave-externa a NULL, em vez de eliminar
ou alterar informação.
SQL-DDL - Restrições
 
Definição de Restrições
 
Os exemplos atrás ilustrados mostram formas usuais de incluir restrições na
definição de relações.
 
Existe uma clausula geral para esse fim: “CONSTRAINT”
 
Serve para definir todos os tipos de restrições e atribuir a cada uma
delas um identificador
 
CONSTRAINT <NOME> <ESPECIFICAÇÃO>
  <NOME> Nome da restrição.
  <ESPECIFICAÇÃO> Especificação da restrição. É aqui que se define
o seu tipo e conteúdo.
SQL-DDL - Restrições
 
 
Restrições – Exemplo
CREATE TABLE Inscricao_Turma(
CodAluno INTEGER NOT NULL,
CodDisciplina INTEGER NOT NULL,
CodTurma INTEGER NOT NULL,
Ano INTEGER,
Data DATE DEFAULT NOW(),
CONSTRAINT chave_primaria PRIMARY KEY (CodALuno, CodDisciplina),
CONSTRAINT chave_ext_disciplina
FOREIGN KEY (CodDisciplina) REFERENCES Disciplina(CodDisciplina)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT chave_ext_akunos
FOREIGN KEY (CodAluno) REFERENCES Aluno(CodAluno)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT valor_ano CHECK Ano > 0);
SQL-DDL
 
Outras clausulas aplicáveis à criação de relações:
 
NOT NULL  Indica que o atributo não poderá conter o valor nulo.
 
UNIQUE  Indica que o valor a colocar no atributo terá que ser diferente
para todos os tuplos presentes na relação.
 
BETWEEN lim_inferior AND lim_superior  Indica que o valor a assumir por
cada tuplo para determinado atributo terá obrigatoriamente que estar
entre “lim_inf” e lim_sup”.
 
IN (valor1, valor2, ..., valorn) Indica que o valor atribuído ao atributo em
cada tuplo existente na relação terá que assumir um dos “n” valores
descritos na lista.
SQL-DDL - Eliminação
 
Eliminação de Tabelas
 
DROP TABLE <nome_tabela>;
 
<nome_tabela>  Nome da tabela a eliminar da base de dados.
 
Para eliminar relações de uma base de dados basta utilizar a instrução
DROP TABLE seguida do nome da tabela.
DROP TABLE Aluno;
SQL-DDL - Alteração
 
Alteração de Tabelas
 
A alteração de tabelas de uma base de dados pode ser feita
exclusivamente de duas formas:
  Adição de colunas (atributos)
  Eliminação de colunas (atributos).
 
ALTER TABLE <nome_tabela>
{[ADD COLUMN <nome_coluna> <tipo> [<Restrições>]]||
[DROP COLUMN <nome_coluna>]}
 
 
 
 
 
<nome_coluna>  Nome da coluna a adicionar ou remover.
<tipo>  Tipo do atributo que se deseja adicionar à relação.
<Restrições>  Eventuais restrições a aplicar à coluna a adicionar. (Sintaxe
identica à da instrução CREATE TABLE).
SQL-DDL - Alteração
 
Alteração de Tabelas
 
Exemplos:
ALTER TABLE Aluno
DROP COLUMN Nome;
ALTER TABLE Aluno
ADD COLUMN Idade INTEGER
CHECK IDADE > 0;
SQL-DDL - Exercício
 
Criação de Bases de Dados
 
Criação de uma base de dados correspondente ao esquema:
Aluno (NumAluno, BI, Nome, Morada, CodPostal)
Disciplina (CodDisciplina, Nome, Programa, AnoLectivo)
Nota (NumAluno, Nota, CodInscricao, Época)
Inscricao (CodInscricao,NumAluno, Ano, CodDisciplina)
CodPostal (Codigo, Cidade)
SQL-DDL - Exercício
 
Criação de Bases de Dados
CREATE TABLE CodPostal(
Codigo INTEGER,
Cidade VARCHAR(100),
CONSTRAINT C1 Codigo PRIMARY KEY,
CONSTRAINT C2 Cidade NOT NULL);
CREATE TABLE Aluno(
BI INTEGER,
Nome VARCHAR(100),
Morada VARCHAR(100),
CodPostal INTEGER,
CONSTRAINT C1 BI PRIMARY KEY,
CONSTRAINT C2 Nome NOT NULL,
CONSTRAINT C3 CodPostal FOREIGN KEY
REFERENCES CodPostal(Codigo));