(BD) TP2: Criação de Tabelas e Consultas

Faculdade de Computação
Estruturas e Bancos de Dados
2a. Aula Prática: Banco de Dados
Base de dados simples usando o BrOBase
_______________________________________________________________
Seja o diagrama abaixo, representando o modelo conceitual de dados para uma
empresa de comércio de produtos. O modelo permite identificar e estruturar as
informações relativas às compras dos clientes. Por exemplo, cada compra possui um
número (que a identifica unicamente), um status, e deve estar associada à um cliente
e à um conjunto de itens (no mínimo 1 item).
Para que possamos destacar as restrições que devem ser impostas aos dados (chaves
primárias e estrangeiras), podemos definir o modelo lógico abaixo. Nele é possível
visualizar todos os atributos e seus inter-relacionamentos. As chaves primárias
aparecem em vermelho, e as chaves estrangeiras em verde:
Utilizando o modelo acima, resolva as atividades abaixo, utilizando o BrOBase
salvando suas respostas num arquivo (labBDnome.doc) que deverá ser enviado ao
professor para correção.
1) Crie as tabelas Cliente, Compra e Produto usando os scripts abaixo. Em seguida,
defina e execute o comando SQL para a criação da tabela Item.
create table cliente (
nroCliente integer primary key,
nome varchar(64),
email varchar(64),
endereco varchar(256),
cartao varchar(16)
);
create table compra (
nroCompra integer primary key,
refCliente integer,
status varchar(16),
foreign key (refCliente) references cliente (nroCliente)
);
create table produto (
codProduto integer primary key,
nome varchar(64),
preco numeric(10,2),
estoque integer
);
2) Povoar as tabelas criadas no item 1 utilizando os comandos SQL abaixo. Antes de
executar os comandos, verifique como são feitas as referências aos nomes das
tabelas.
INSERT INTO Cliente VALUES (900,'João Silva', '[email protected]', 'Rua XV Novembro
120','1522.1324');
INSERT INTO Cliente VALUES (901,'Marina Garcia', '[email protected]', 'Av 9 de Julho
92','6453.9943');
INSERT INTO Cliente VALUES (902,'Maria Paula Gama', '[email protected]', 'Rua Amelia
Dias 430','2975.1151');
INSERT INTO Cliente VALUES (903,'Carlos José Melo', '[email protected]', 'Pç Pedro
Sanchez 14','7657.3714');
INSERT INTO Cliente VALUES (904,'Luiz Lima', '[email protected]', 'Rua Vicente Risola
87','2412.3423');
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
Produto
Produto
Produto
Produto
Produto
Produto
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(25,'Camera Digital A220',499.00, 15);
(27,'Filmadora Digital XV40',1399.00, 25);
(32,'Camera Digital Aquatica BV400',49.00, 200);
(56,'Cartao Memoria 4GB',159.00, 70);
(57,'Cartao Memoria 8GB',199.00, 70);
(58,'Cartao Memoria 10GB',269.00, 70);
INSERT INTO Compra VALUES (340, 901,'Pag. Cartao');
INSERT INTO Compra VALUES (344, 903,'Pag. Cartao 2x');
INSERT INTO Compra VALUES (348, 900,'Pag. Boleto');
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
Item
Item
Item
Item
VALUES
VALUES
VALUES
VALUES
(11,
(12,
(13,
(14,
340,
340,
340,
340,
25,
27,
56,
57,
1);
2);
1);
3);
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Item
Item
Item
Item
Item
Item
Item
Item
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(15,
(16,
(17,
(18,
(19,
(20,
(21,
(22,
344,
344,
344,
348,
348,
348,
348,
348,
58,
25,
32,
25,
32,
27,
57,
56,
1);
1);
4);
1);
1);
1);
2);
2);
a) Crie mais alguns clientes:
– o primeiro de nro: 905, nome: Paulo Henrique Naves, email: [email protected],
endereço: Rua Carajás 333, cartão: 2341.4432
– o segundo de nro: 906, nome: Victor Peres (apenas o nome deverá ser
informado, as outras informações pessoais como email, endereço e cartão
devem ficar em branco)
– o terceiro de nro: 903, nome: Marcelo Julio, email [email protected] (e demais
informações em branco)
b) Inclua mais uma compra:
– nro 354, do cliente Luiz Lima, com pagamento em cheque.
– os itens da compra são 2 filmadoras, e 3 cartões de memória de 8GB
c) Execute algumas alterações, como por exemplo, modificar o nome do cliente de
número 903 ou remover um item de uma compra:
UPDATE cliente set nome='Marcelo Rossi' where nroCliente = 903;
DELETE FROM Item where numero = 21;
c1) Altere na tabela produto o código da Camera Digital A220 para 35.
c2) Remova da tabela o cliente 904
c3) Altere a quantidade do produto 32 para 5 na compra 348.
3) Nos exercícios 1 e 2, conforme observado até aqui, a política para controle da
integridade referencial definida na criação das tabelas foi do tipo RESTRITA, tanto
para modificação de dados referenciados quanto para a remoção dos mesmos.
a) Remova as tabelas
Para remover uma tabela usa-se o comando:
drop table item;
As tabelas com dados referenciados (por chaves estrangeiras) devem ser
removidas antes daquelas que armazenam a informação referenciada.
b) Crie novamente as tabelas usando a política de alteração em cascata (ON UPDATE
CASCADE) e remoção com nulos (ON DELETE SET NULL).
Para definir a política de atualização de dados na criação de uma tabela:
create table compra (
nroCompra integer primary key,
refCliente integer,
status varchar(16),
foreign key (refCliente) references cliente (nroCliente) ON UPDATE CASCADE
ON DELETE SET NULL
);
c) Refaça as alterações e remoções sugeridas no exercício 2 e verifique as respostas.
d) Proponha 5 novas alterações e remoções que envolvam dados referenciados e
explique os resultados obtidos.
4) Usando a instrução SELECT para consulta aos dados, codifique a recuperação das
informações e explique os resultados obtidos:
a) O nome e o endereço do cliente 904
select NOME,ENDERECO from CLIENTE where
NROCLIENTE = 904
b) Qual o resultado da consulta:
select * from CLIENTE, COMPRA
where CLIENTE.NROCLIENTE = COMPRA.REFCLIENTE
c) A lista dos produtos que não foram comprados por nenhum cliente.
d) O nome dos clientes que fizeram compras e o status da compra
e) Proponha 3 novas consultas aos dados.
5) Crie um modelo conceitual (DER – Diagrama de Entidade-Relacionamento) e em
seguida o modelo físico para o sistema descrito abaixo:
Seja um sistema de controle de biblioteca em que devem ser
cadastrados: Alunos, Livros, Editora, Autor, Idioma e Movimento. Para
cada um dos itens de cadastro, devem ser especificados seus atributos
(Por exemplo, Alunos devem ter nome, rg, email, endereço, telefone, ...).
O movimento é definido por empréstimos e devolução de livros (didáticos
ou literatura). O aluno pode estar bloqueado ou em situação normal. Os
empréstimos devem conter data inicial, data final, situação e multa (se
houver).
a) Defina os comandos em SQL para a criação das tabelas do sistema de biblioteca de
acordo com os diagramas.
b) Determine a política a ser utilizada quando da alteração ou remoção de dados
referenciados.
c) Defina comandos em SQL para a inserção de dados nas tabelas.
d) Mostre exemplos de remoções e modificações de dados referenciados.
e) Defina consultas para retornar informações como: o nome do livro mais
emprestado, os nomes dos usuários bloqueados, etc.