Acetatos aulas compilados e em formato redzido

UNIVERSIDADE DE TRÁS-OS-MONTES E ALTO DOURO
BASE DE DADOS
ACETATOS AULAS TEÓRICAS
Por Paulo Martins
AFG v1
1
3.1 Introdução aos Sistemas de Bases de Dados
Base de Dados


Uma vez que o constituinte central de qualquer sistema de informação é a sua memória (conjunto
armazenado de dados), a “solução informática”, para qualquer organização, deve assentar num
depósito integrado de dados – a base de dados.
Uma base de dados é, por definição, um conjunto organizado de dados, disponível a todos os
utilizadores ou processamentos da organização que deles tenham necessidade.
Dados vs. Informação



Dados são apenas elementos ou valores discretos que, isoladamente, não têm qualquer valor, só se
transformam em informação quando relacionados ou interpretados de alguma forma.
Para que possa ser utilizada como apoio eficaz à tomada de decisão nas organizações, a informação só
tem valor se se verificarem, simultaneamente, algumas condições: actualidade, correcção, relevância,
disponibilidade e legibilidade.
De entre as tecnologias da informação, a tecnologia das bases de dados tem, neste contexto, um
interesse particular.
Sistemas de Gestão de Ficheiros












Os antecessores, em termos de evolução tecnológica, dos modernos sistemas de bases de dados foram
os sistemas de gestão de ficheiros.
Utilizando sistemas de gestão de ficheiros, as organizações começaram por automatizar algumas das
tarefas até aí realizadas manualmente.
Os processos continuavam a ser executados, no essencial, da mesma forma, só que mais rapidamente.
É vulgar encontrar situações de replicação de um mesmo documento, cada um deles com um destino e
um processamento diferentes.
Os sistemas surgiam no panorama informático das organizações sem qualquer relação com os sistemas
já existentes, constituindo “ilhas isoladas” entre si.
Os dados como são actualizados, independentemente, pelas respectivas aplicações, existem grandes
probabilidades de ocorrerem incoerências – redundância não controlada.
Apesar de ser possível com a tecnologia de sistemas de gestão de ficheiros desenvolver um outro tipo
de solução, segundo a qual, todos os dados necessários às várias aplicações são organizados num único
conjunto de ficheiros, acessível a todas elas, verifica-se que a estrutura física dos dados e a sua
organização nos ficheiros é parte integrante da lógica dos programas.
Consequência grave: basta que ocorra uma alteração na estrutura dum ficheiro para que essa alteração
se propague, necessariamente, para todas as aplicações que o utilizam – a partilha de dados apresenta
problemas ao nível da manutenção dos próprios sistemas.
Outra consequência grave: o acesso concorrente aos dados partilhados tem de ser feito ao nível das
próprias aplicações, utilizando funções de baixo-nível disponibilizadas pelo sistema operativo – o que
vem pôr em causa a fiabilidade dos próprios sistemas.
Os sistemas de bases de dados surgem como tentativa de resolver estes problemas.
A abordagem pelos sistemas de bases de dados tem uma característica fundamental – os dados são
organizados num único conjunto. Isto é, em vez de estarem separados por várias unidades
independentes, os dados encontram-se integrados numa só unidade (lógica) de armazenamento.
Todos os acessos aos dados passam sempre por uma entidade designada Sistema de Gestão de Bases
de Dados (SGBD), que centraliza em si o acesso físico à base de dados.
2

A interface lógica entre o nível aplicacional e a base de dados é conseguida à custa do armazenamento
na base de dados, não só dos dados propriamente ditos, mas também das suas descrições
(metadados), numa entidade conhecida por dicionário de dados (também chamado catálogo).
1.1. Sistema de Gestão de Bases de Dados
Por definição, o Sistema de Gestão de Bases de Dados (SGBD), em inglês Database Management System
(DBMS), é um conjunto de software, destinado a gerir todo o armazenamento e manipulação dos dados do
sistema, fazendo a interface entre o nível aplicacional e a base de dados propriamente dita.

O SGBD:
o “Esconde” os detalhes de armazenamento físico dos dados;
o Proporciona ao nível aplicacional um grau de abstracção elevado.
Conceito de Transacção



É um dos conceitos mais importantes num sistema de base de dados. Praticamente todas as tarefas de
gestão de base de dados vivem à custa deste conceito.
Exemplo típico: transferência de valores entre duas contas bancárias.
o Debitar de A e creditar em B.
o Só a sua execução conjunta produz um resultado válido.
Por definição, uma transacção é um conjunto de operações sobre a base de dados, perfeitamente
delimitado, que exibe algumas características importantes:
o Atomicidade (Atomicity) – o conjunto de operações que constituem uma transacção formam um
grupo indivisível (atómico), no sentido em que ou todas elas são executadas com sucesso ou
nenhuma é executada.
o Consistência (Consistency) – uma transacção, se envolver actualização de dados, deve transportar a
base de dados de um estado de integridade para outro estado também de integridade.
o Isolamento (Isolation) – apesar de as transacções executarem concorrentemente, o sistema deve
dar a cada transacção a ilusão de que é a única a executar no sistema – executa isoladamente das
outras.
o Durabilidade (Durability) – o sistema deve assegurar que todos os efeitos provocados por uma
transacção bem sucedida se tornam persistentes na base de dados e visíveis para as outras
transacções.

Estas 4 características são conhecidas na literatura inglesa por ACID (Atomicity, Consistency, Isolation
and Durability).
3
Requisitos Fundamentais de um SGBD




Segurança – O objectivo das medidas de segurança dos sistemas de bases de dados é proteger os
dados armazenados de acessos não autorizados, garantindo que apenas os utilizadores autorizados
acedem ao sistema, de acordo com os seus privilégios – através de perfis ou contas.
Integridade – Por definição, uma base de dados está num estado de integridade se contém apenas
dados válidos, isto é, que não contradizem a realidade que estão a representar, antes a reflectem
correctamente.– “A definição das restrições de integridade pode representar 90% das especificações de
uma base de dados típica.”
Controlo da concorrência – Um dos pressupostos fundamentais dos sistemas de bases de dados é a
partilha dos dados armazenados pelo nível aplicacional. O controlo da concorrência relaciona-se com a
coordenação dessa partilha por várias aplicações e utilizadores. Trata-se, portanto, de um problema
específico dos sistemas de bases de dados multiutilizador e a ideia principal é garantir que cada
utilizador ou aplicação interage com a base de dados como se fosse o único a utilizar os seus serviços. A
unidade base do controlo da concorrência é a transacção.
Recuperação/tolerância a falhas – É a actividade que tem por objectivo o restaurar da base de dados,
após a ocorrência de uma qualquer falha, para um estado de integridade garantido. Mecanismos de
recuperação: backups e transaction logs.
Arquitectura ANSI/SPARC
•
Numa tentativa de estabelecer um padrão para toda a indústria de desenvolvimento de tecnologia de
bases de dados, foi proposto pela American National Standards Institute (ANSI), nomeadamente pelo
seu Standards Planning and Requirements Committee (SPARC), uma arquitectura de três níveis
independentes (arquitectura ANSI/SPARC), cada uma delas descrevendo a base de dados a um nível
diferente de abstracção.
•
Nível interno. Armazenamento físico dos dados e definição das estruturas físicas que permitem obter
um bom nível de desempenho.
Nível conceptual. Representação do modelo conceptual de dados, independentemente de qualquer
utilizador ou aplicação em particular, constituindo o chamado esquema conceptual ou estrutura da
base de dados. Esta camada esconde os detalhes de implementação física dos ficheiros que armazenam
os dados.
Nível externo. Definição de “vistas” sobre o esquema conceptual da base de dados. Permite trabalhar
apenas com uma parte dos dados que tenha interesse para um determinado utilizador ou aplicação em
particular.
•
•
•
Este tipo de arquitectura permite:
o Independência física. Alterações no nível interno não se repercutem no nível conceptual.
o Independência lógica. Alterações no nível conceptual não interferem, de forma obrigatória, com as
“vistas” estabelecidas no nível externo.
4
Utilizadores de Sistemas de Bases de Dados


Administrador da base de dados – É o responsável máximo pelo bom funcionamento de todo o
sistema. Tem como principais responsabilidades a especificação do esquema conceptual da base de
dados e a sua manutenção.
Utilizadores – Podem-se distinguir 2 tipos de utilizadores:
o Utilizadores finais – São a razão de existir da base de dados. Acedem ao sistema para
consultar, adicionar, alterar ou remover dados (debaixo das respectivas condições de acesso),
não poderão nunca é alterar a estrutura da base de dados (esquema conceptual).
o Programadores – Utilizando linguagens de alto nível e tendo por base “vistas” sobre o esquema
conceptual da base de dados, desenvolvem as aplicações que os utilizadores finais irão depois
utilizar.
1.2. Modelos de Bases de Dados



A modelação de dados é a actividade que, debruçando-se sobre a totalidade dos requisitos de
informação de um sistema de informação, tenta encontrar um modelo que traduza a estrutura lógica
dos dados que satisfaz esses requisitos. A esse modelo designa-se modelo conceptual de dados. Ex:
diagrama E-R (Entidade-Relacionamento), diagrama UML (Unified Modeling Language), etc.
O modelo conceptual de dados é depois traduzido num modelo de base de dados. Ex: hierárquico,
rede, relacional, orientado a objectos, etc.
Os modelos de bases de dados podem ser classificados em três gerações distintas:
o 1ª geração (modelos hierárquico e rede) - Corresponde aos primeiros passos da tecnologia de
bases de dados com o desenvolvimento dos modelos hierárquico e rede.
o 2ª geração (modelo relacional) - É caracterizada pelo desenvolvimento e divulgação do modelo
relacional.
o 3ª geração (novos modelos) - Corresponde ao estado actual de desenvolvimento da tecnologia
de bases de dados. Caracteriza-se pelo desenvolvimento, em paralelo, de alguns modelos ainda
não estabilizados.
• Dada a importância e o marco histórico que foi o modelo relacional, também é vulgar apresentar estas
três gerações como pré-relacional, relacional e pós-relacional.
1.2.1. 1ª Geração (Modelos Hierárquico e Rede)
•
•
Resultam de um processo evolutivo sobre as técnicas de processamento utilizadas nos sistemas de
gestão de ficheiros.
Apesar de existirem ainda muitos sistemas da 1ª geração em exploração e de haver grande quantidade
de dados armazenados segundo os seus modelos, trata-se de tecnologias em claro declínio.
o Modelo hierárquico
 Os dados encontram-se estruturados em hierarquias ou árvores. O acesso aos dados
faz-se percorrendo essas hierarquias, desde a raiz até ao nodo que contém o registo
pretendido.
o Modelo rede
 Basicamente, corresponde a uma extensão do modelo hierárquico. Eliminando o
conceito de hierarquia, permite que um mesmo tipo de registo esteja envolvido em
várias associações.
 Enquanto que no modelo hierárquico os registos estão organizados em conjuntos de
árvores, no modelo rede os registos estão organizados em grafos.
5
1.2.2. 2ª Geração (Modelo Relacional)
•
•
•
Contrariamente aos modelos hierárquicos e rede, o modelo relacional não evoluiu a partir das técnicas
de processamento sobre ficheiros.
Foi fruto de um desenvolvimento teórico, tendo a sua base num ramo da matemática que é,
simultaneamente, simples e poderosa – a teoria dos conjuntos.
Em 1970, Edgar Frank Codd, na altura investigador da IBM Corp., publicou um artigo com os
fundamentos teóricos do modelo relacional. (O modelo relacional vai ser apresentado
detalhadamente à frente)
1.2.3. 3ª Geração (Novos Modelos)
•
•
Áreas como o GIS (Geographic Information Systems), o CAD (Computer-Aided Design), o CIM (Computer
Integrated Manufacturing), as ferramentas CASE (Computer-Aided Software Engineering), os sistemas
multimédia, etc., com características muito diferentes das aplicações tradicionais de processamento de
dados de gestão, necessitam de facilidades que os sistemas convencionais de bases de dados não
suportam de forma adequada.
Actualmente, as três principais linhas de investigação e desenvolvimento das tecnologias de base de
dados desenrolam-se nas seguintes áreas:
o Extensões ao modelo relacional. Dado que o modelo relacional é um modelo estável, seguro e
com provas dadas, tentam aproveitar-se as suas virtudes (simplicidade de conceitos, linguagens
de manipulação de alto nível, bom suporte teórico, etc.), adicionando-lhe novas características.
Ou seja, a ideia é adicionar ao modelo relacional novas funcionalidades no sentido de
ultrapassar algumas das suas fraquezas.
o Modelo orientado a objectos. Esta linha de desenvolvimento surge na sequência dos
progressos ocorridos com as linguagens de programação orientadas a objectos. Trata-se de
uma abordagem nova, revolucionária e com grande potencial. Dispondo de grandes
capacidades de modelação, adequa-se aos requisitos das áreas de aplicação mais complexas.
o Modelo objecto-relacional. São bases de dados híbridas, tentando incluir numa mesma infraestrutura o melhor dos dois modelos (relacional e orientado a objectos). Trata-se,
normalmente, de bases de dados relacionais cujas funcionalidades são estendidas de maneira a
suportar armazenamento e processamento de objectos, que passam a ser tratados como se
fossem um tipo de dados da própria base de dados.
3.2 Concepção e Desenvolvimento de Bases de Dados
Etapas de Modelação de uma Base de Dados
6
2.1. Diagrama E-R
•
•
•
Foi proposto por Peter Chen em 1976.
Devido à sua notação simples e poderosa, depressa se tornou numa abordagem quase universal para a
modelação de dados. Curiosamente, ainda hoje não existe uma notação E-R normalizada,
predominando uma grande variedade de notações para representar os conceitos fundamentais de
modelação.
Como proposto originalmente, os elementos básicos de um diagrama E-R são as entidades, os
relacionamentos e os atributos. Posteriormente, outros conceitos foram sendo agregados à
abordagem, resultando no aparecimento de vários “dialetos” de diagramas E-R.
Entidades
•
Uma entidade representa um conjunto de objectos (concreto ou abstracto) do mundo real que
possuem características comuns.
Relacionamentos
•
Um relacionamento representa uma associação entre entidades.
Os relacionamentos também possuem ocorrências, que são associações entre ocorrências das entidades.
Cardinalidade de um Relacionamento
•
Cardinalidade máxima (1 ou N) – representa a quantidade máxima de ocorrências de uma entidade
que pode estar associada a uma mesma ocorrência de outra entidade. Em notações onde se assinale
um par de cardinalidades (x,y), a cardinalidade máxima é anotada no segundo elemento.
7
Quanto à cardinalidade máxima, os relacionamentos podem ser do tipo:
1:1 (um para um) 1:N (um para muitos) N:N (muitos para muitos)
Em notações onde se assinale a cardinalidade apenas com um elemento único, esse corresponde à
cardinalidade máxima.
•
Cardinalidade mínima (0 ou 1) – indica se a participação de todas as ocorrências de uma entidade em
um determinado relacionamento é obrigatória (1) ou opcional (0).
Em notações onde se assinale um par de cardinalidades (x,y), a cardinalidade mínima é anotada no
primeiro elemento. Nota: só se assinala a cardinalidade mínima em notações com par de cardinalidades.
Leitura genérica para determinar a cardinalidade
Grau de um Relacionamento
•
Embora a maioria dos relacionamentos ocorra entre duas entidades (relacionamento binário), podem
ser definidos relacionamentos entre qualquer número de entidades – relacionamento n-ário.
8
o
Relacionamento unário (ou auto-relacionamento)
o
Relacionamento ternário
Se, por exemplo, os alunos frequentam uma determinada disciplina em salas diferentes, vai ser necessário
um relacionamento ternário para guardar em que salas, é que os alunos frequentam essa disciplina.
Atributos
• A cada entidade (ou relacionamento) podem estar associados um ou mais atributos que representam
as suas propriedades elementares.
Todas as entidades devem possuir atributos. A ocorrência de relacionamentos com atributos é pequena,
aparecendo principalmente em relacionamentos N:N.
•
•
Um identificador é um atributo ou (menor) grupo de atributos que determina nivocamente uma
entidade.
Toda a entidade deve possuir um e apenas um identificador, podendo este ser simples ou composto.
9
Atributos multivalor não são permitidos em algumas notações, pois não existe representação
correspondente em SQL. Nestes casos, os atributos multivalor são representados pela criação de uma nova
entidade e de um novo relacionamento.
2.2. Modelo Relacional
Modelo Relacional Intro
•
•
•
•
Contrariamente aos modelos hierárquicos e rede, o modelo relacional não evoluiu a partir das técnicas
de processamento sobre ficheiros.
Foi fruto de um desenvolvimento teórico, tendo a sua base num ramo da matemática que é,
simultaneamente, simples e poderosa – a teoria dos conjuntos.
Em 1970, Edgar Frank Codd, na altura investigador da IBM Corp., publicou um artigo com os
fundamentos teóricos do modelo relacional.
Alguns SGBDs relacionais mais representativos: Oracle da Oracle Corp., MS SQL Server da Microsoft
Corp., DB2 da IBM Corp., Informix da IBM Corp., Adaptive Server Enterprise da Sybase Inc., MySQL
(Open Source) da MySQL AB Corp., MS Access da Microsoft Corp., etc.
Relação ou Tabela
•
•
•
A relação (ou tabela) é a estrutura fundamental do modelo relacional. Uma relação é uma estrutura
bidimensional com um determinado esquema e zero ou mais instâncias.
O esquema de uma relação é constituído por um ou mais atributos (também designados por colunas ou
campos). Cada atributo terá associado um tipo de dados de acordo com a informação que irá
armazenar.
A cada instância do esquema de uma relação designa-se tuplo (ou linha).
10
Tipos de Relações
•
No que concerne às relações ou tabelas, definem-se dois tipos:
o – Relações base que constituem o esquema da base de dados, onde estão realmente
armazenados os dados.
o – Relações virtuais (ou views) que não têm existência própria, pois derivam e estão associadas
às relações base com o objectivo de proporcionar “vistas” parciais sobre o esquema da base de
dados.
Tipos de Chaves
•
•
•
•
Superchave – associação de um ou mais atributos que, em conjunto, identificam univocamente cada
um dos tuplos. Nota: no limite, a associação de todos os atributos de uma relação constitui uma
superchave.
Chave candidata – subconjunto dos atributos de uma superchave que, sendo ainda superchave, não
pode ser reduzido sem perder essa qualidade.
Chave primária (ou chave principal) – chave seleccionada entre as várias chaves candidatas para,
efectivamente, identificar cada tuplo.
Chave estrangeira (ou chave importada) – atributo ou conjunto de atributos de uma relação, que é
chave primária noutra relação.
A existência de uma chave estrangeira numa relação prende-se com a necessidade de manter a
interligação entre essa relação e a relação onde esse conjunto de atributos é chave primária.
Restrições de Integridade Implícitas ao Modelo Relacional
•
•
•
Integridade de domínio. O valor de um atributo deve obedecer ao tipo de dados e às restrições de
valores que lhe estão admitidos.
Integridade da entidade. Cada tuplo de uma relação deve poder ser identificado univocamente. Isto
quer dizer que cada relação deverá possuir na sua definição uma chave primária. A existência de uma
chave primária garante que o atributo ou os atributos que a constituem não podem ser nulos, nem
podem ser repetidos.
Integridade referencial. O valor dos atributos que constituem a chave estrangeira de uma relação deve
estar também presente na chave primária da relação referenciada ou, quando muito, pode ser nulo.
11
2.3. Normalização
•
•
•
A normalização é um processo sistemático, definido por um conjunto de regras bem definidas, que visa
eliminar fontes de redundância nos dados.
Problemas associados à redundância de dados:
o Problemas de manutenção;
o Custos de espaço de armazenamento;
o Problemas de desempenho.
O processo de normalização ocorre através de um conjunto de fases que conduzem a base de dados a
estados onde a redundância se torna cada vez menor. A cada um destes estados dá-se o nome de
forma normal (FN).
•
Dependências funcionais – existe uma dependência funcional X ®Y entre dois conjuntos de atributos X
e Y, se uma instância de valores de X determina ou identifica univocamente uma instância de valores
dos atributos de Y.
•
Uma relação diz-se que está na Primeira Forma Normal (1FN) quando:
o
o
•
Uma relação diz-se que está na Segunda Forma Normal (2FN) quando:
o
o
•
Não contém atributos multivalor;
Não contém grupos repetitivos.
Está na Primeira Forma Normal (1FN);
Todos os atributos não chave dependem funcionalmente da totalidade da chave.
Uma relação diz-se que está na Terceira Forma Normal (3FN) quando:
o
o
Está na Segunda Forma Normal (2FN);
Todos os atributos não chave não dependem funcionalmente uns dos outros.
3. Linguagem SQL
3.1
•
•
Linguagens de Bases de Dados
A interacção do nível aplicacional, incluindo os utilizadores, com o SGBD faz-se à custa de linguagens
específicas.
Basicamente, podem-se identificar dois tipos de linguagens de bases de dados:
o – Linguagem de Definição de Dados (LDD) ou, em inglês, Data Definition Language (DDL) –
Esta não é uma linguagem no sentido vulgar dado às linguagens de programação, pois não
possui qualquer instrução específica de processamento. É antes uma notação utilizada para
descrever a estrutura dos dados a armazenar, ou seja, é a linguagem de definição dos
metadados.
o Linguagem de Manipulação de Dados (LMD) ou, em inglês, Data Manipulation Language
(DML) – Esta é a linguagem disponibilizada ao nível aplicacional para a manipulação dos dados
propriamente ditos. As operações possíveis são as vulgares: consulta, inserção, alteração e
remoção de dados. Em alguns sistemas, as instruções LMD podem ser executadas
interactivamente pelos utilizadores finais a partir do terminal (as chamadas questões ad hoc)
12
ou, então, embutidas numa linguagem de programação de alto-nível para o desenvolvimento
de aplicações.
3.2
Linguagem SQL
•
É uma linguagem normalizada pelo ANSI (American National Standards Institute) e pela ISO
(International Organization for Standardization).
o – SQL-86 (SQL-87) – Publicado pelo ANSI em 1986 e rectificado pela ISO em 1987.
o SQL-89 – Menor revisão.
o SQL-92 (SQL2) – Maior revisão.
o SQL:1999 (SQL3) – Adição de queries recursivas e triggers. Adição de tipos não-escalados e
algumas características de orientação a objectos (estas duas características são controversas e
ainda não são amplamente implementadas).
o SQL:2003 – Introduz características relacionadas com a XML e colunas com valores autogerados (incluindo colunas-identidade).
o SQL:2006 – Define mecanismos que permite usar a SQL em conjunto com a XML de uma forma
perfeitamente integrada.
•
A maior parte dos SGBD relacionais usam a linguagem SQL, mas possuem as suas próprias extensões à
SQL.
No entanto, tipicamente a linguagem pode ser migrada de plataforma para plataforma sem mudanças
•
estruturais principais.
•
A linguagem SQL é constituída por três sublinguagens:
o
o
o
3.3
Linguagem de Definição de Dados (LDD) [Data Definition Language (DDL) ]
Linguagem de Manipulação de Dados (LMD) [Data Manipulation Language (DML) ]
Linguagem de Controlo de Dados (LCD) [Data Controle Language (DCL) ]
SQL como Linguagem de Definição de Dados
Comandos CREATE DATABASE e DROP DATABASE
•
EXEMPLO
•
A SQL, enquanto Linguagem de Definição de Dados, disponibiliza um conjunto de comandos para a
criação (CREATE), alteração (ALTER) e remoção (DROP) de tabelas e outras estruturas.
Comando CREATE DATABASE – permite criar uma base de dados. Nota: não disponível no MS Access.
Comando DROP DATABASE – permite remover uma determinada base de dados, apagando todas as
tabelas e estruturas associadas e, consequentemente, todos os dados nelas existentes. Nota: não
disponível no MS Access.
EXEMPLO
•
CREATE DATABASE nome_da_base_de_dados
DROP DATABASE nome_da_base_de_dados
13
Comando CREATE TABLE
SINTAXE
Comando CREATE TABLE – permite criar uma tabela.
CREATE TABLE nome_tabela(
coluna1 tipo,
coluna2 tipo,
... ...,
colunan tipo).
EXEMPLO
•
CREATE TABLE Professor(
Id INTEGER,
Nome CHAR(50),
Data_Nasc DATE,
Salario FLOAT)
Tipos de Dados Básicos
Colunas – Valores por Defeito
• Na criação de uma tabela:
o Por defeito as colunas podem admitir valores nulos – NULL.
o É possível especificar se uma coluna pode admitir nulos ou não através das cláusulas NULL ou
NOT NULL.
o É possível associar um outro valor por defeito através da cláusula DEFAULT. Nota: não
disponível no MS Access.
14
EXEMPLO
CREATE TABLE Professor(
Id INTEGER NOT NULL,
Nome CHAR(50),
Data_Nasc DATE NULL,
Salario FLOAT DEFAULT 1000)
Restrições (em Inglês, Constraints)
•
•
•
•
•
Restrições (ou constraints) são regras a que os valores de uma ou mais colunas devem obedecer.
A utilização de restrições é a única garantia que temos de que os dados existentes nas colunas estão de
acordo com as regras especificadas no desenho da base de dados.
Restrição NOT NULL – é a restrição de uso mais generalizado, pois impede a introdução de valores
nulos na coluna.
Restrição CHECK() – permite realizar a validação dos dados introduzidos na coluna, através da
especificação de uma condição. São admitidos apenas os dados cujo resultado da avaliação da condição
seja verdadeiro. Nota: não disponível no MS Access.
o É possível a especificação da restrição CHECK() relativa a duas ou mais colunas.
Operadores a usar na restrição CHECK():
o Operadores relacionais: =, >, <, >=, <=, <> ou !=.
o Operadores lógicos: AND, OR, NOT.
o Outros operadores: BETWEEN, IN, IS, LIKE.
EXEMPLO
(Todos estes operadores serão vistos detalhadamente à frente no comando SELECT)
Restrição UNIQUE – permite indicar que os valores dessa coluna não se podem repetir.
EXEMPLO
•
CREATE TABLE Professor(
Id INTEGER NOT NULL,
Nome CHAR(50) NOT NULL CHECK(Nome NOT LIKE '%Rui%'),
Idade INTEGER NOT NULL CHECK(Idade BETWEEN 0 AND 150),
Sexo CHAR NOT NULL CHECK(Sexo IN ('M', 'F')),
Salario FLOAT NOT NULL CHECK(Salario >= 0),
Data_Nasc DATE NOT NULL,
Data_Admi DATE NOT NULL,
CHECK(Data_Admi > Data_Nasc))
CREATE TABLE Professor(
Id INTEGER,
Nome CHAR(50) UNIQUE,
Telefone CHAR(15),
BI INTEGER UNIQUE)
15
EXEMPLO
•
EXEMPLO
CREATE TABLE Professor(
Id INTEGER PRIMARY KEY,
Nome CHAR(50) UNIQUE,
Telefone CHAR(15),
BI INTEGER UNIQUE)
EXEMPLO
Restrição PRIMARY KEY – é utilizada para indicar qual a chave primária (se houver) da tabela.
CREATE TABLE Matriz(
Linha INTEGER,
Coluna INTEGER,
Valor FLOAT,
PRIMARY KEY(Linha,Coluna))
Restrição REFERENCES – é utilizada para indicar as chaves estrangeiras.
EXEMPLO
•
CREATE TABLE Professor(
Id INTEGER,
Nome CHAR(40),
Apelido CHAR(20),
Telefone CHAR(15),
BI INTEGER,
UNIQUE(Nome,Apelido),
UNIQUE(BI))
CREATE TABLE Professor(
Id INTEGER,
Nome CHAR(40) NOT NULL,
Apelido CHAR(20) NOT NULL,
BI NUMERIC NOT NULL UNIQUE,
Morada CHAR(80),
Cod_Postal NUMERIC(4) REFERENCES Postal(Codigo),
Salario NUMERIC(10,2) NOT NULL CHECK(Salario >= 0),
PRIMARY KEY(Id),
FOREIGN KEY(BI) REFERENCES Seg_Social(BId),
FOREIGN KEY(Nome,Apelido) REFERENCES Pessoa(Nome,Apelido))
16
Comandos ALTER TABLE e DROP TABLE
Comando ALTER TABLE – permite alterar a estrutura de uma tabela.
SINTAXE
•
ALTER TABLE nome_tabela ADD nome_coluna tipo_coluna
ALTER TABLE nome_tabela MODIFY nome_coluna tipo_coluna
ALTER TABLE nome_tabela DROP nome_coluna
EXEMPLO
Comando DROP TABLE – permite remover uma tabela. É removida toda a estrutura e o conjunto de valores
lá existentes.
3.4
DROP TABLE nome_tabela
SQL como Linguagem de Manipulação de Dados
Comando INSERT
•
A SQL, enquanto Linguagem de Manipulação de Dados, disponibiliza um conjunto de comandos que se
podem classificar em dois grupos:
o Actualização da base de dados (comandos INSERT, UPDATE e DELETE);
o Interrogação da base de dados (comando SELECT).
•
Comando INSERT – permite inserir novos registos numa tabela da base de dados.
SINTAXE
INSERT INTO nome_tabela(coluna1,...,colunan)
VALUES(valor1,...,valorn)
SINTAXE
INSERT INTO nome_tabela
VALUES(valor1,...,valorn)
EXEMPLO
A sintaxe pode tomar uma das seguintes formas:
INSERT INTO Professor(Id, Nome)
VALUES (1111, 'José Fernandes')
OU
Pode-se trocar o ordem das colunas, desde que se coloquem os valores associados na posição respectiva.
17
EXEMPLO
INSERT INTO Professor(Nome, Id)
VALUES ('José Fernandes', 1111)
Se pretendermos inserir valores em todas as colunas da tabela, poderemos utilizar o formato abreviado do
comando INSERT. A ordem a utilizar na disposição dos valores deve ser aquela pela qual as tabelas foram
criadas.
Nota: as colunas que não forem colocadas no comando INSERT (formato longo) são preenchidas com o
valor NULL.
EXEMPLO
MS ACCESS
EXEMPLO
SQL SERVER
INSERT INTO Professor(Id, Nome, Data_Nasc)
VALUES (2222, 'Silva', '13 Feb 1970')
INSERT INTO Professor(Id, Nome, Data_Nasc)
VALUES (2222, 'Silva', TO_DATE('13/02/1970', 'dd/mm/yyyy'))
INSERT INTO Professor(Id, Nome, Data_Nasc)
VALUES (2222, 'Silva', '1970-02-13')
EXEMPLO
EXEMPLO
INSERT INTO Professor(Id, Nome, Data_Nasc)
VALUES (2222, 'Silva', #02/13/1970#)
ORACLE
Manipulação de datas – o formato para a representação das datas é diferente de sistema para sistema.
mySQL
•
Comando UPDATE
Comando UPDATE – permite alterar os valores já existentes nos campos de uma única tabela.
SINTAXE
•
UPDATE nome_tabela
SET coluna1 = {expressão1,query1},
coluna2 = {expressão2,query2},
...,
colunan = {expressãon,queryn}
[WHERE condição]
18
EXEMPLO
UPDATE Professor
SET Salario = Salario * 1.1
EXEMPLO
A cláusula WHERE no comando UPDATE permite restringir o conjunto dos registos que irão ser processados
pelo comando. Se não for colocada a cláusula WHERE, as alterações serão realizadas a todos os registos da
tabela.
UPDATE Professor
SET Salario = Salario * 1.2,
Telefone = NULL
WHERE Categoria = 'Professor Catedrático'
Nota: com o comando UPDATE também se podem afectar os valores das colunas com o valor do resultado
de uma query (resultante de um SELECT).
Comando DELETE
Comando DELETE – permite apagar conjuntos de linhas existentes numa única tabela.
SINTAXE
•
DELETE FROM nome_tabela
[WHERE condição]
EXEMPLO
EXEMPLO
A cláusula WHERE no comando DELETE permite restringir o conjunto dos registos que irão ser apagados.
(apaga todas as linhas da tabela)
DELETE FROM Professor
(apaga apenas uma linha, uma vez que o Id é único – chave primária)
DELETE FROM Professor
WHERE Id = 1111
Nota: se na base de dados estiver definida integridade referencial entre as tabelas A e B, então só poderá
apagar uma linha na tabela A quando na tabela B não existirem referências a essa linha.
Comando SELECT
Comando SELECT – permite consultar uma base de dados relacional.
SINTAXE
•
SELECT coluna1,coluna2,...,colunan,*
FROM tabela1,...,tabelak
[WHERE condição]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]
19
Comando SELECT (Selecções Simples)
Comando SELECT nas suas utilizações mais simples:
SINTAXE
•
Todos os exemplos do comando SELECT apresentados daqui para a frente vão ser baseados nas
seguintes tabelas:
BASE
•
SINTAXE
Seleccionando todos as linhas de uma tabela com todas as colunas:
SELECT Codigo, Designacao
FROM Curso
SELECT *
FROM Curso
Seleccionando apenas algumas colunas de uma tabela (operação denominada por Projecção):
SINTAXE
•
•
Aluno (Numero, Nome, Idade, Propina, Telefone, Cod_Curso)
Curso (Codigo, Designacao)
Disciplina (Cod_Disciplina, Nome_Disciplina)
Avaliacao (Numero, Cod_Disciplina, Nota)
SINTAXE
•
SELECT ...
FROM ...
[WHERE condição]
SELECT Numero, Nome, Cod_Curso
FROM Aluno
A cláusula WHERE permite restringir o conjunto de linhas a apresentar (operação denominada por
Selecção).
• Operadores a usar na cláusula WHERE:
o Operadores relacionais: =, >, <, >=, <=, <> ou !=.
o Operadores lógicos: AND, OR, NOT.
o Outros operadores: BETWEEN, IN, IS, LIKE.
20
EXEMPLO
SELECT *
FROM Aluno
WHERE Idade = 21
EXEMPLO
• Operadores relacionais: =, >, <, >=, <=, <> ou !=.
SELECT Numero, Nome, Propina
FROM Aluno
WHERE Idade >= 23
EXEMPLO
SELECT Numero, Nome, Idade
FROM Aluno
WHERE Idade >= 20 AND Idade <= 30
EXEMPLO
• Operadores lógicos: AND, OR, NOT.
SELECT Numero, Nome, Idade
FROM Aluno
WHERE NOT (Idade < 20 OR Idade > 30)
SINTAXE
SELECT ...
FROM ...
WHERE coluna [NOT] BETWEEN valor1 AND valor2
EXEMPLO
• Operador BETWEEN.
SELECT Numero, Nome, Idade
FROM Aluno
WHERE Idade BETWEEN 20 AND 30 (inclusive!!!)
21
SINTAXE
SELECT ...
FROM ...
WHERE coluna [NOT] IN (valor1,valor2,...,valorn)
EXEMPLO
SELECT Numero, Nome, Telefone
FROM Aluno
WHERE Propina IN (200, 300, 400)
EXEMPLO
• Operador IN.
SELECT Codigo, Designacao
FROM Curso
WHERE Designacao NOT IN ('Informática', 'Gestão')
• Operador IS (tratamento de valores nulos).
SINTAXE
As comparações de valores com NULL terão de ser realizadas utilizando o operador IS.
SELECT ...
FROM ...
WHERE coluna IS [NOT] NULL
Ex: SELECT Nome
FROM Aluno
WHERE Telefone IS NULL
• Operador LIKE (comparação de strings).
A comparação de strings com os operadores relacionais utiliza sempre a totalidade da string. Alternativa:
operador LIKE.
22
Comando SELECT (Ordenação)
A ordenação é baseada no valor do código ASCII de cada carácter.
A ordenação pode ser realizada através da cláusula ORDER BY no comando SELECT. Esta cláusula, se
existir, aparece sempre posicionada no final do comando SELECT.
SINTAXE
•
•
SELECT coluna1,coluna2,...,colunan,*
FROM tabela1,...,tabelak
[WHERE condição]
[GROUP BY ...]
[HAVING ...]
[ORDER BY coluna [ASC|DESC], coluna [ASC|DESC],
...]
23
Onde: coluna representa o nome de uma coluna, uma expressão ou a posição pela qual se pretende
ordenar o resultado do SELECT; ASC indica que a ordenação é ascendente; e DESC indica que a ordenação é
descendente.
•
Ordenação por uma coluna
Por defeito, a ordenação é ascendente.
A forma como o NULL é colocado no resultado ordenado de um SELECT depende de sistema para sistema.
•
Ordenação por várias colunas
EXEMPLO
SELECT *
FROM Aluno
ORDER BY Idade DESC, Propina
Ordenação por expressão
EXEMPLO
•
SELECT *
FROM Aluno
ORDER BY Idade, Propina
EXEMPLO
Caso se indique que se pretende ordenar o resultado de um SELECT por mais do que uma coluna, a
ordenação é feita pela primeira coluna, e entre valores iguais é ordenada pela segunda coluna, etc.
SELECT Idade, Propina AS Total, Propina*0.3 AS
Entrada,
Propina-Propina*0.3 AS Restante
FROM Aluno
WHERE Numero <= 1000
ORDER BY Idade, Propina*0.3
24
EXEMPLO
• Ordenação por posição
SELECT Idade, Propina AS Total, Propina*0.3 AS
Entrada,
Propina-Propina*0.3 AS Restante
FROM Aluno
WHERE Numero <= 1000
ORDER BY Idade, 3
A ordem das colunas é 1, 2, 3,… a contar da esquerda para a direita levada em conta a ordem pela qual as
colunas ou expressões foram colocadas na cláusula SELECT, nada tendo a ver com a ordem pela qual foram
criadas.
• Eliminação de repetições (SELECT DISTINCT e SELECT ALL)
EXEMPLO
A cláusula DISTINCT permite eliminar repetições de linhas no resultado de um SELECT. Os dados são
apresentados ordenadamente, uma vez que o motor da base de dados ao executar a pesquisa vai ordenar
as colunas seleccionadas, sendo depois mais fácil detectar e eliminar as repetições, pois, se existirem,
aparecem seguidas.
SELECT DISTINCT Nome_Disciplina
FROM Disciplina
EXEMPLO
A cláusula ALL está, por defeito, associada ao comando SELECT.
SELECT [ALL] Nome_Disciplina
FROM Disciplina
25