Restrições de Integridade em Banco de Dados

Prévia do material em texto

1- 
LIVRO_AUTOR 
(FK Cod_Livro): Ao deletar (ON DELETE) deve acontecer um CASCADE. Se um livro for 
removido do catálogo, as relações com o autor devem ser removidas, já que não fazem 
mais sentido. Ao atualizar (ON UPDATE) também usar CASCADE porque caso o código do 
livro seja alterado, a alteração tem que ser mudada em outros lugares também para manter 
consistência. 
 
LIVRO 
(FK Nome_editora): Usar RESTRICT quando dar DELETE na Editora. Isso será feito para 
que não tenha livros sem uma editora. 
E para dar UPDATE no nome da editora, usar CASCADE, pra todos livros serem mudados. 
 
LIVRO_COPIAS: 
(FK Cod_livro): ao dar DELETE no livro, usar CASCADE, porque se ele é removido do 
catálogo, as suas cópias nas bibliotecas devem ser removidas do sistema. E ao usar 
UPDATE no Cod_livro, usar CASCADE, para que a alteração mude todas as outras cópias. 
 
Para (FK Cod_unidade): ao deletar uma unidade da biblioteca, usar CASCADE já que todas 
as cópias de livros naquela unidade devem ser removidas do sistema. Também dá pra usar 
RESTRICT para impedir que uma unidade com cópias seja deletada. UPDATE no 
Cod_unidade, usar CASCADE, para que a alteração mude todas as outras cópias. 
 
LIVRO_EMPRESTIMO 
(FK Cod_livro): ao dar DELETE num Livro, usar RESTRICT, para não permitir remoção caso 
tenha registro de empréstimo para ele. ao usar UPDATE no Cod_livro, usar CASCADE, 
para que a alteração mude os registros de empréstimo também. 
 
(FK Cod_unidade): ao usar DELETE para uma unidade, usar RESTRICT, para não excluir 
uma unidade caso tenha empréstimos associados à ela. UPDATE no Cod_unidade, usar 
CASCADE, para que a alteração mude todas as outras cópias. 
 
(FK Nr_cartao): DELETE para o usuario, usar RESTRICT, para não permitir exclusão de um 
usuário se ele possui histórico de empréstimos. Ao dar UPDATE no Num_cartao do usuario, 
usar CASCADE para os registros do empréstimo refletirem essa mudança. 
 
 
 
2 - 
 
use Biblioteca; 
 
create table Editora 
( 
Nome varchar (50), 
Endereco varchar (50), 
Telefone varchar (12), 
primary key (Nome) 
) 
 
 
create table Livro 
( 
Cod_livro varchar (10), 
Titulo varchar (50), 
Nome_editora varchar (50), 
primary key (Cod_livro), 
foreign key (Nome_editora) references Editora (Nome) on delete restrict on update cascade 
) 
 
create table Livro_autor 
( 
Cod_livro varchar (10), 
Nome_autor varchar (50), 
primary key (Cod_livro, Nome_autor), 
foreign key (Cod_livro) references Livro (Cod_livro) on delete cascade on update cascade 
) 
 
create table Unidade_biblioteca 
( 
Cod_unidade varchar (10), 
Nome_unidade varchar (50), 
Endereco varchar (50), 
primary key (Cod_unidade) 
) 
 
create table Usuario 
( 
Num_cartao varchar (10), 
Nome varchar (50), 
Endereco varchar (50), 
Telefone varchar (12), 
primary key (Num_cartao) 
) 
 
create table Livro_copias 
( 
Cod_livro varchar (10), 
Cod_unidade varchar (10), 
Qt_copia integer, 
primary key (Cod_livro, Cod_Unidade), 
foreign key (Cod_livro) references Livro (Cod_livro) on delete cascade on update cascade, 
foreign key (Cod_unidade) references Unidade_biblioteca (Cod_unidade) on delete cascade 
on update cascade 
) 
 
create table Livro_Emprestimo 
( 
Cod_livro varchar (10), 
Cod_unidade varchar (10), 
Num_cartao varchar (10), 
Data_emprestimo date, 
Data_devolucao date, 
primary key (Cod_livro, Cod_Unidade, Num_cartao), 
foreign key (Cod_livro) references Livro (Cod_livro) on delete restrict on update cascade, 
foreign key (Cod_unidade) references Unidade_biblioteca (Cod_unidade) on delete restrict 
on update cascade, 
foreign key (Num_cartao) references Usuario (Num_cartao) on delete restrict on update 
cascade 
) 
 
 
3.a. - 
 
select Pnome, Minicial, Unome from funcionario f 
join trabalha_em t on f.cpf = t.fcpf 
join projeto p on t.pnr = p.projnumero 
where (f.dnr = 4) and (p.projnome = 'ProdutoY') and (t.horas > 20); 
 
Aqui a pesquisa foi construída da seguinte forma: 
 
Select = Comando para selecionar e buscar 
Pnome, Minicial, Unome = Variáveis desejadas, nesse caso, o primeiro nome, inicial do 
nome do meio e o último nome 
From = Comando para dizer de onde deseja selecionar 
funcionario= Tabela de onde deseja pegar a informação, nesse caso a tabela funcionário 
f = Contração/Representação de funcionário para podermos nos referir a essa tabela ao 
falar das colunas 
 
Em seguida foram feitos dois joins que funcionam de forma semelhante, apenas alterando 
as “variáveis”: 
 
join = Comando para “juntar” duas tabelas 
trabalha_em / projeto = O nome das tabelas para a junção com as anteriores 
t / p = Mesmo caso do f, a contração / representação dessas tabelas 
on = Para denominar que depois dele vem a condição de igualdade entre as tabelas para 
filtrar apenas as combinações desejadas. 
f.cpf = t.fcpf = Condição para que, para que sejam mostradas as linhas e combinações, o 
cpf na tabela funcionário e o fcpf na tabela trabalha_em deveriam bater 
 t.pnr = p.projnumero = Condição para que, para que sejam mostradas as linhas e 
combinações, o pnr na tabela trabalha_em e o projnumero na tabela projeto deveriam bater 
 
 
Por fim, foram colocadas três cláusulas Where para a filtragem dos resultados obtidos 
usando de base a filtragem pedida na questão: 
 
Where = Cláusula para filtrar linhas da tabela com base em uma condição específica. 
And = Para juntar mais de uma cláusula e fazer com que o resultado apenas seja mostrado 
se atender a todas as cláusulas 
(f.dnr = 4) = Cláusula para garantir que apenas funcionários do departamento 4 sejam 
mostrados 
(p.projnome = 'ProdutoY') = Cláusula para garantir que apenas linhas da junção que sejam 
relativas aos ‘ProdutoY’ no projeto sejam mostradas 
(t.horas > 20) Cláusula para garantir que apenas linhas da junção que sejam relativas a 
mais de 20 horas de trabalho sejam mostradas 
 
Por fim, o resultado mostrado foi vazio, pois não havia nenhum funcionário que atendesse a 
essa filtragem de forma completa: 
 
 
 
 
 
3.b. - 
 
select distinct Pnome, Minicial, Unome from funcionario f 
join dependente d on f.Cpf = d.Fcpf 
where (d.Datanasc'33344555587'); 
 
SELECT Pnome, Minicial, Unome from funcionario where (Cpf_supervisor = (select Cpf from 
funcionario where (Pnome = 'Fernando' and Unome = 'Wong'))); 
 
 
 
Aqui foram feitas duas formas para a pesquisa: 
 
A primeira considera que quem busca os funcionários conhece o CPF do Fernando Wong, 
então busca diretamente por ele. A sintaxe então foi construída da seguinte forma: 
 
Select = Comando para selecionar e buscar 
Pnome, Minicial, Unome = Variáveis desejadas, nesse caso, o primeiro nome, inicial do 
nome do meio e o último nome 
From = Comando para dizer de onde deseja selecionar 
funcionario = Tabela de onde deseja pegar a informação, nesse caso a tabela funcionário 
Where = Cláusula para filtrar linhas da tabela com base em uma condição específica. 
(Cpf_supervisor = '33344555587'): Condição de filtragem, nesse caso, que Cpf_supervisor 
seja igual a 33344555587, o cpf do Fernando Wong. 
 
 
Já na primeira foi usado um tipo de subconsulta para o caso de quem estiver buscando a 
informação não saber o cpf do Fernando Wong, podendo pesquisar apenas com o nome e 
sobrenome dele. A sintaxe inicial (“SELECT Pnome, Minicial, Unome from funcionario where 
(Cpf_supervisor = “) é a mesma, o que muda é que, ao invés de comparar o Cpf do 
supervisor diretamente com um cpf, se faz outra busca para filtrar apenas o Fernando Wong 
(Através das cláusulas where de comparação e o and para juntar ambas) e pegar o cpf dele 
para então comparar com cpf do supervisor e filtrar para apenas aqueles que ele 
supervisiona. 
 
Ao final, em ambos os casos, o resultado da busca foi: 
 
 
 
 
 
4- 
 
4.a: 
 
Sistema de Blog 
TABELAS: 
 
- Usuários (id_usuario PK INT, nome VARCHAR, email VARCHAR, senha VARCHAR) 
- Posts (id_post PK INT, titulo VARCHAR, conteudo TEXT, data_publicacao 
TIMESTAMP, id_usuario FK INT) 
- Comentarios (id_comentario PK INT, texto TEXT, data_criacao TIMESTAMP, id_post 
FK INT, id_usuario FK INT) 
- Categorias (id_categoria PK INT, nome VARCHAR) 
RELACIONAMENTOS 
- Usuarios (1,1) – (0,N) Posts 
- Posts (1,1) – (0,N) Comentarios 
- Usuarios (1,1) – (0,N) Comentarios 
- Posts (0,N) – (1,N) Categorias (usar tabela intermediária de relacionamento) 
 
 
CRIAÇÃO DAS TABELAS E RELAÇÕES COM DDL DO SQL: 
 
CREATE TABLE Usuarios ( 
 id_usuario INT PRIMARY KEY, 
 nome VARCHAR(100) NOT NULL, 
 email VARCHAR(100) NOT NULL, 
 senha VARCHAR(200) NOT NULL); 
CREATE TABLE Categorias ( 
 id_categoria INT PRIMARY KEY, 
 nome VARCHAR(50) NOT NULL); 
CREATE TABLE Posts ( 
 id_post INT PRIMARY KEY, 
 titulo VARCHAR(50) NOT NULL, 
 conteudo TEXT NOT NULL, 
 data_publicacao TIMESTAMP, 
 
 id_usuario INT, 
 CONSTRAINT fk_post_autor 
 FOREIGN KEY (id_usuario) 
 REFERENCES Usuarios(id_usuario) 
 ON DELETE SET NULL); 
 
CREATE TABLE Comentarios ( 
id_comentario INT PRIMARY KEY AUTO_INCREMENT, 
texto TEXT NOT NULL, 
data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
id_post INT NOT NULL, 
 
id_usuario INT, 
 CONSTRAINT fk_comentario_post 
 FOREIGN KEY (id_post) 
 REFERENCES Posts(id_post) 
 ON DELETE CASCADE, 
 
 CONSTRAINT fk_comentario_autor 
 FOREIGN KEY (id_usuario) 
 REFERENCES Usuarios(id_usuario) 
 ON DELETE SET NULL); 
 
CREATE TABLE Post_Categorias ( 
 id_post INT, 
 id_categoria INT, 
 PRIMARY KEY (id_post, id_categoria), 
 
 CONSTRAINT fk_post_id 
 FOREIGN KEY (id_post) 
 REFERENCES Posts(id_post) 
 ON DELETE CASCADE, 
 
 CONSTRAINT fk_categoria_id 
 FOREIGN KEY (id_categoria) 
 REFERENCES Categorias(id_categoria) 
 ON DELETE CASCADE); 
 
4.b - Consultas 
 
a. 
SELECT p.titulo, p.data_publicacao, u.nome AS autor 
FROM Posts p 
JOIN Usuarios u ON p.id_usuario = u.id_usuario 
ORDER BY p.data_publicacao DESC; 
 
b. 
SELECT c.texto, u.nome AS autor_comentario 
FROM Comentarios C 
JOIN Usuarios u ON c.id_usuario = u.id_usuario 
WHERE c.id_post = 1; 
 
c. 
SELECT u.nome, COUNT(p.id_post) AS total_de_posts 
FROM Usuarios u 
LEFT JOIN Posts p ON u.id_usuario = p.id_usuario 
GROUP BY u.id_usuario, u.nome 
ORDER BY total_de_posts DESC; 
 
4.c - Resultados das consultas depois de ter inseridos os dados no BD do Blog 
 
 
 
 
 
 
Eduarda Gabriela Nerbas 
Giuliano Conti Porcher