António Rocha Nuno Melo e Castro

António Rocha

Existem alguns pontos que já foram

abordados nas aulas teóricas e que serão

aqui, apenas, brevemente descritos:

Insert, Update e Delete

!

"

Nas duas últimas aulas vimos o DML na

perspectiva de acesso a dados – Select

Agora vamos terminar o DML abordado a

alteração da informação na BD:

Insert

Update

Delete

#

INSERT – inserção de tuplos numa tabela

insert into <tabela ou vista> values <Conjunto de tuplos>

Adicionar um novo tuplo à tabela account

insert into account

values (‘A-9732’, ‘Perryridge’,1200)

ou

insert into account (branch-name, balance, account-number) values (‘Perryridge’, 1200, ‘A-9732’)

Adicionar um novo tuplo a account em que balance é null

insert into account

No nosso exemplo

Pretende-se dar uma conta de poupança de $200, como bónus a todos os mutuários da agência de Perryride.

O loan_number servirá de número de conta da poupança

insert into account

select loan-number, branch-name, 200 from loan

where branch-name = ‘Perryridge’ insert into depositor

select customer-name, loan-number from borrower, loan

where borrower.loan_number = loan.loan.number and

branch-name = ‘Perryridge’

UPDATE –actualização de tuplos numa tabela

update <tabela ou vista>

set <Atributo> = <Expressão>, <Atributo> = <Expressão>, ... where <Condição>

Pagar juros de 1% a todas as contas da agência Perryride.

update account

set balance = balance ∗ 1.01

No nosso exemplo

Pretende-se lançar juros de 6% a todas as contas com saldos superiores a

$10,000, e juros de 5% às restantes contas.

update account

set balance = balance ∗ 1.06

where balance > 10000

update account

set balance = balance ∗ 1.05

where balance ≤ 10000

$

DELETE – remover tuplos de uma tabela ou view

delete from <tabela ou vista> where <Condição>

Apagar todas as contas da agência de Perryridge

delete from account

%

No nosso exemplo

Remover todas as contas de todas as agências na cidade de Brooklyn.

delete from depositor

where account-number in

(

select account-number

from branch natural inner join account

where branch-city = ‘Needham’)

delete from account

where branch-name in (select branch-name

from branch

"

SQL - DDL :: Data Definition Language

Permite criar e destruir bases de dados e objectos das bases de

dados

Comandos fundamentais

CREATE

DROP

ALTER

Qualquer comando tem uma sintaxe própria. Para poder ser

executado temos de especificar o comando na totalidade

Exemplos

CREATE DATABASE …

DROP DATABASE …

ALTER DATABASE …

CREATE TABLE …

DROP TABLE …

ALTER TABLE …

"

Datatypes

A especificação SQL2003 reconhece os 5 tipos genéricos:

Numeric String Boolean Datetime Interval

Cada tipo pode ter vários subtipos:

Numeric

Exact Numeric (Integer, Smallint, Bigint, Numeric, Decimal) Integer, Smallint, Bigint são inteiros, variando a precisão

Numeric, Decimal são números reais com uma determinada precisão e escala

[numeric(p, s)]. A diferença é que a precisão pode ser excedida no caso do Decimal.

Approximate Numeric (Real, Double, Float). Usados quando a exactidão não é imperativa (Floating Point Numbers).

São números em vírgula flutuante. Real e Double têm uma precisão dependente do hardware.

Em máquinas de 64 bits o Real tem uma precisão superior do que em máquinas 32 bits. O Double tem precisão dupla do Real, e também é dependente do hardware. Float difere dos anteriores porque permite definir a precisão.

Um número em vírgula flutuante (a) pode ser representado por dois números (m) e (e), tal que: a = m*b^e, sendo b uma base.

"

[ NULL | NOT NULL ]

[ DEFAULT constant_expression ]

[ <column_constraint> [ ...n ] ] <column_constraint> ::=

[ CONSTRAINT constraint_name]

{

{ PRIMARY KEY | UNIQUE } |

[ FOREIGN KEY ] REFERENCES [ schema_name. ] referenced_table_name [ ( ref_column )] |

CHECK ( logical_expression )

}

< table_constraint > ::= {

{ PRIMARY KEY | UNIQUE } (column [ ASC | DESC ] [ ,...n ] ) |

FOREIGN KEY (column [ ,...n ] ) REFERENCES [ schema_name . ] referenced_table_name [ (

ref_column )] |

CHECK ( logical_expression )

!

"

Cada coluna de uma tabela tem um domínio associado.

O domínio define-se especificando o datatype e constraints. CREATE TABLE Aluno(

id_aluno integer,

nome varchar(50))

Neste exemplo, a tabela Aluno tem duas colunas de nome id_aluno e nome. Ambas as colunas admitem valores NULL.

O valor NULL faz sempre parte do domínio de qualquer coluna, excepto quando se define uma restrição que o impeça.

O domínio da coluna id_aluno é o conjunto dos números inteiros. Como é óbvio, o id_aluno pode tomar valores positivos ou negativos. Se quisermos, por exemplo, que a coluna id_aluno só tenha valores positivos, como o podemos garantir?

Definindo uma constraint

#

"

Garantindo que o id_aluno será sempre positivo...

Tipo de constraint a usar: CHECK

Hipótese 1 (usando uma column_constraint)

<column_constraint> ::=

[ CONSTRAINT constraint_name ]

{

{ PRIMARY KEY | UNIQUE } |

[ FOREIGN KEY ] REFERENCES [ schema_name . ]

referenced_table_name [ ( ref_column ) ] |

CHECK ( logical_expression )

}

CREATE TABLE Aluno(

id_aluno integer CHECK (id_aluno > 0),

nome varchar(50))

Este comando originou a criação de dois objectos na base de dados:

Uma tabela com o nome Aluno

"

Hipótese 2 (usando uma column_constraint)

<column_constraint> ::=

[ CONSTRAINT constraint_name ]

{

{ PRIMARY KEY | UNIQUE } |

[ FOREIGN KEY ] REFERENCES [ schema_name . ]

referenced_table_name [ ( ref_column ) ] |

CHECK ( logical_expression )

}

CREATE TABLE Aluno(

id_aluno integer CONSTRAINT CK_Aluno_id_aluno CHECK (id_aluno > 0),

nome varchar(50))

Este comando originou a criação de dois objectos na base de dados:

Uma tabela com o nome Aluno

"

Se quiser garantir que os valores de id_aluno serão sempre diferentes

Temos de criar uma constraint que garanta a unicidade

Por exemplo:

CREATE TABLE Aluno(

id_aluno integer UNIQUE NOT NULL,

nome varchar(50),

CONSTRAINT CK_Aluno_id_aluno CHECK (id_aluno > 0))

Definição da chave primária

A chave primária é uma constraint

Ao definir uma chave primária estamos implicitamente a:

A definir um UNIQUE INDEX A garantir a unicidade da chave

"

O nosso modelo de dados:

Aluno (id_aluno, nome)

Disciplina (id_disciplina, nome) Nota (id_aluno, id_disciplina, nota)

As relações entre as tabelas:

Aluno.id_aluno é relacionado com Nota.id_aluno

Disciplina.id_disciplina é relacionado com Nota.id_disciplina

Uma relação entre duas tabelas define-se através de uma chave estrangeira Uma chave estrangeira existe, quando uma coluna (ou conjunto de colunas) numa tabela se relaciona(m) com outra coluna (ou conjunto de colunas) noutra tabela, mas nessa outra tabela a(s) coluna(s) é(são) chave primária.

Neste caso específico, existem 2 chaves estrangeiras

Nota.id_aluno é chave estrangeira de Aluno.id_aluno

$

"

Criação das chaves estrangeiras Hipótese 1

CREATE TABLE Nota(

id_aluno integer

CONSTRAINT FK_Nota_id_aluno_Aluno REFERENCES Aluno(id_aluno),

id_disciplina integer

CONSTRAINT FK_Nota_id_disciplina_Disciplina REFERENCES Disciplina(id_disciplina),

nota numeric(3, 1) NOT NULL,

CONSTRAINT PK_Nota PRIMARY KEY (id_aluno, id_disciplina), CONSTRAINT CK_Nota_nota CHECK (nota between 0 and 20))

Hipótese 2

CREATE TABLE Nota(

id_aluno integer,

id_disciplina integer,

nota numeric(3, 1) NOT NULL,

CONSTRAINT PK_Nota PRIMARY KEY (id_aluno, id_disciplina), CONSTRAINT CK_Nota_nota CHECK (nota between 0 and 20),

CONSTRAINT FK_Nota_id_aluno_Aluno FOREIGN KEY (id_aluno) REFERENCES Aluno(id_aluno),

CONSTRAINT FK_Nota_id_disciplina_Disciplina FOREIGN KEY (id_disciplina) REFERENCES Disciplina(id_disciplina))

%

"

É possível alterar tabelas

Acrescentar novas colunas, alterar o tipo de dados, eliminar colunas

Acrescentar ou retirar constraints

Acrescentar uma coluna

ALTER TABLE Aluno ADD (email varchar(100))

Eliminar uma coluna

ALTER TABLE Aluno DROP email

Alterar o tipo de dados de uma coluna

ALTER TABLE Aluno MODIFY (email varchar(75))

Eliminar uma constraint

ALTER TABLE Aluno DROP CONSTRAINT FK_Nota_id_aluno_Aluno

Acrescentar uma constraint

ALTER TABLE Aluno ADD CONSTRAINT FK_Nota_id_aluno_Aluno FOREIGN KEY (id_aluno) REFERENCES Aluno(id_aluno)

Considerações:

A sintaxe dos comandos pode variar de SGBD para SGBD

Nem todos os SGBD permitem todas as versões do comando ALTER TABLE

Quando se adiciona uma constraint, terá sempre de ser no formato de uma

table_constraint

& '(

Views

Uma view (vista) é uma tabela virtual cuja

estrutura e conteúdo é definido através de uma

query. A view é criada dinamicamente a partir do

momento que é invocada. Uma view actua como

um filtro sobre os dados da BD, e podem ser

resultados de uma ou mais tabelas.

As views são normalmente utilizadas para

simplificar a manipulação dos dados, e definir os

dados na perspectiva do utilizador.

& '

Podem também ser utilizadas como mecanismos

de segurança de dados, pois é possível definir

seguranças ao nível de uma view.

É também possível alterar informação através de

views

As views podem ser usadas em pesquisas tal

qual uma tabela normal

)* +,

create table cliente(

cod_cliente int constraint pk_cliente primary key, nome varchar(20) not null,

morada varchar(50), telefone varchar(15))

create table agencia(

cod_agencia int constraint pk_agencia primary key, nome varchar(20))

create table conta(

num_conta char(10) constraint pk_conta primary key,

cod_agencia int not null constraint fk_conta_agencia references agencia(cod_agencia),

cod_cliente int not null constraint fk_conta_cliente references cliente(cod_cliente),

!

)* +,

insert into cliente (cod_cliente, nome) values(1, 'A1') insert into cliente (cod_cliente, nome) values(2, 'B1') insert into cliente (cod_cliente, nome) values(3, 'C1')

insert into agencia (cod_agencia, nome) values(1, 'AgA1') insert into agencia (cod_agencia, nome) values(2, 'AgB1') insert into agencia (cod_agencia, nome) values(3, 'AgC1')

insert into conta (num_conta, cod_cliente, cod_agencia, saldo) values (1, 1, 1, 12000) insert into conta (num_conta, cod_cliente, cod_agencia, saldo) values (2, 1, 2, 8000) insert into conta (num_conta, cod_cliente, cod_agencia, saldo) values (3, 2, 1, 1000) insert into conta (num_conta, cod_cliente, cod_agencia, saldo) values (4, 3, 3, 16000) insert into conta (num_conta, cod_cliente, cod_agencia, saldo) values (5, 3, 2, 10000) insert into conta (num_conta, cod_cliente, cod_agencia, saldo) values (6, 3, 1, 500)

#

)* +,

Suponhamos o seguinte conceito existente

no nosso sistema:

Os "Super clientes" são aqueles cujo saldo

individual de pelo

menos uma das suas contas é superior ao saldo

médio das contas do banco

Há interesse em ter uma tabela que indique quais

são os "Super clientes".

A informação necessária é o código, nome e

saldo total (todas as suas contas)

)* +,

Como é óbvio, esta informação não é

estática.

Num instante uma determinada pessoa é "Super

cliente" e noutro pode deixar de o ser. Basta

movimentar as contas.

Para resolver este problema, a solução

passa pela criação de uma view.

)* +,

A informação pretendida é dada pelo seguinte Select:

select a.cod_cliente, a.nome, sum(b.saldo)

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and a.cod_cliente in (select a.cod_cliente

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and b.saldo > (select avg(saldo) from conta))

group by a.cod_cliente, a.nome

)* +,

Não é um comando SQL trivial. E se é necessário usar

frequentemente esta informação relativa aos clientes, ou...

Há utilizadores do sistema que não sabem SQL...

O administrador do sistema escreve uma view que resolve vários

problemas:

Esconde dos utilizadores a complexidade do Sql

Ficamos com uma "tabela" que contém a informação sobre os

"Super clientes"

Esconde dos utilizadores alguns dos campos das tabelas

"Cliente" e "Conta".

É importante perceber que muitas vezes é importante esconder

parte da informação que está contida nas tabelas.

$

)* +,

Criação da view

create view "Super clientes" as

select a.cod_cliente, a.nome, sum(b.saldo) saldo_total

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and a.cod_cliente in (select a.cod_cliente

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and b.saldo > (select avg(saldo) from conta))

group by a.cod_cliente, a.nome

Para a usar:

%

)* +,

O nosso modelo de dados passou a ser:

cliente(cod_cliente, nome, morada, telefone)

agencia(cod_agencia, nome)

conta(num_conta, cod_agencia, cod_cliente,

saldo)

!

)

, -.

È possível o utilizador aceder a definição da view. Para o evitar

podemos usar uma opção de encriptação:

alter view "Super clientes" with encryption

as

select a.cod_cliente, a.nome, sum(b.saldo) saldo_total

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and a.cod_cliente in (select a.cod_cliente

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and b.saldo > (select avg(saldo) from conta))

group by a.cod_cliente, a.nome

!

-.

/ + -.

create view v_cliente as

select cod_cliente, nome, morada from cliente

Podemos inserir dados nas views

insert into v_cliente values(4, 'D1', 'Rua X')

Também podemos eliminar ou alterar registos...

update v_cliente set morada = 'Rua Y'

where cod_cliente = 4

delete from v_cliente

!

-.

/ + -.

Mas não podemos alterar informação através

da view “Super Clientes”

create view "Super clientes" as

select a.cod_cliente, a.nome, sum(b.saldo) saldo_total

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and a.cod_cliente in (select a.cod_cliente

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

and b.saldo > (select avg(saldo) from conta))

group by a.cod_cliente, a.nome

!!

-.

/ + -.

E se tiver várias tabelas e não tiver informação

agregada?

create view v_clienteconta as

select a.cod_cliente, a.nome, a.morada, a.telefone, b.num_conta, b.cod_agencia, b.saldo

from cliente a, conta b

where a.cod_cliente = b.cod_cliente

Se tentarmos inserir um registo através da view...

o registo vai para a tabela cliente, conta, ou ambas?

e o nome, morada e telefone do cliente?

se por exemplo, para o cliente 1 não especificar o nome

'A1' vou gerar inconsistência?

!#

-.

/ + -.

insert into v_clienteconta

(cod_cliente, nome, morada, telefone, num_conta, cod_agencia,

saldo)

values (1, 'A2', 'Morada X', '22-1234567', 7, 1, 0)

Msg 4405, Level 16, State 1, Line 1 View or function 'v_clienteconta'

is not updatable because the modification affects multiple base

tables.

Não houve problema porque não é possível inserir registos

No entanto é possível alterar parte da informação.

update v_clienteconta

set morada = 'x'

!

-.

/ + -.

É possível alterar a tabela conta

update v_clienteconta

set saldo = saldo + 10

where cod_cliente = 1 and num_conta = 1

Mas não é possível alterar simultaneamente as

tabelas cliente e conta.

update v_clienteconta

set morada = 'xx', saldo = saldo + 10

where cod_cliente = 1 and num_conta = 1

Msg 4405, Level 16, State 1, Line 1

View or function 'v_clienteconta' is not updatable because the

modification affects multiple base tables.

!

-.

/ + -.

Não é possível apagar registos

delete from v_clienteconta

where cod_cliente = 1 and num_conta = 1

Msg 4405, Level 16, State 1, Line 1 View or

function 'v_clienteconta' is not updatable

because the modification affects multiple

base tables.

!

0

Recapitulando:

É possível efectuar qualquer acção (INSERT,

UPDATE, DELETE) sobre uma view que tem

apenas uma tabela.

Quando a view tem várias tabelas, é possível alterar

registos se a alteração apenas se repercutir numa

única tabela

Quando a view tem várias tabelas, não é possível

inserir ou eliminar registos.

Todas estas restrições que existem quando a view

tem várias tabelas desaparecem, quando usarmos

INSTEAD OF TRIGGERS

!$

01 2

CREATE view v_agencia as

select * from agencia

where nome like 'Ag%‘

Perante o exemplo anterior podemos

efectuar o seguinte insert?

insert into v_agencia(cod_agencia, nome)

values (5, 'Nome inválido...')

!%

01 2

Para a view validar a informação é

necessário colocar a opção CHECK

CREATE view v_agencia as

select * from agencia

where nome like 'Ag%'

with check option

#

3

+