Restrições de Integridade - Exercício

Prévia do material em texto

Exercício Prático
Restrições de Integridade
Nível 1
Crie as tabelas abaixo:
create table Jogador (
cpf int,
nome varchar(40),
posicao varchar(30) ,
sexo char(1),
 idade int,
habilidade varchar(30),
velocidade_chute number(3),
nro_camisa int,
 salario int(5),
primary key (cpf)
);
create table Time (
codigo int,
nome varchar(60),
cidade varchar(70),
ano_fundacao int,
primary key (codigo)
);
create table partida (
id_partida int,
nome_campeonato varchar(60),
duração int,
 primary key (id_partida)
);
Insira alguns dados nas tabelas Jogador, Time e Partida conforme solicitado abaixo:
- Insira dois jogadores com números de camisas idênticos
	INSERT INTO jogador(
cfp, nome, posicao, sexo, idade, habilidade, velocidade_chute, nro_camisa, salario)
VALUES
(09871213,'Romário','Volante', 'm', 28, 120, 12, 1000)
;
	INSERT INTO jogador
VALUES(4033,'Romário','atacante', 'm', 32, 'ataque', 100, 08, 1400);
- Insira um jogador com idade superior aos 50 anos e com velocidade do chute negativo
	INSERT INTO jogador
VALUES(2058,'Zagalo','zagueiro', 'm', 60, 'chute', -1, 10, 1900);
- Insira um time com ano de fundação inferior a 1800
	INSERT INTO time
VALUES(01,'Flamengo','Rio de Janeiro', 1500);
- Insira uma partida com duração de 300 minutos
	INSERT INTO partida
VALUES(20,'Estadual', 300);
Os dados inseridos acima preservam a integridade deste banco de dados? Justifique.
R= Não! Os dados acima foram inseridos sem nenhum erro de restrição, devido a ausência de regras para assegurar a consistência do BD.
Nível 2	
Tente inserir alguns outros dados na tabela jogador conforme solicitado abaixo:
Um jogador com chave primária nula
R= Não é possível declarar um jogador sem a chave primária nula devido quebra de integridade de Entidade e integridade referencial, anulando a questão abaixo. 
Dois jogadores com a mesma chave primária
Aponte o resultado encontrado (Imagem ou descrição do resultado de acordo com o console de resultados do Oracle). R=
	ERROR: null value in column "cpf" violates not-null constraint SQL state: 23502 Detail: Failing row contains (null, Vampeta, zagueiro, m, 28, chute, 10, 18, 1900).
	ERROR: duplicate key value violates unique constraint "jogador_pkey" SQL state: 23505 Detail: Key (cpf)=(2058) already exists.
Que restrições de integridade (da abordagem relacional) foram preservadas em cada uma das tentativas da questão anterior? Justifique sua resposta.
R= Sim! Devido o controle de integridade de entidade integridade referencial implementada no código.
Nível3	
Crie as tabelas abaixo: 
create table Jogador_Validacao (
 cpf int, 
 nome varchar(40) not null,
 posicao varchar(30),
 sexo char(1) check (sexo in ('m','f')),
 idade int check (idade between 21 and 40),
habilidade varchar(30) check (habilidade in ('craque','bom_jogador', 'esforcado','perna_de_pau')),
 velocidade_chute number(15) check(velocidade_chute >10),
nro_camisa int unique check (nro_camisa between 1 and 22),
salario decimal check (salario > 0), 
 constraint pkJogadorChave primary key (cpf));
create table Time_Validacao ( 
codigo serial,
nome varchar(60) not null,
cidade varchar(70) not null,
ano_fundacao int check(ano_fundacao between 1900 and 1950),
constraint pkTimeChave primary key (codigo)
);
create table Partidada_Validacao (
id_partida serial,
nome_campeonato varchar(60) not null,
duracao int check(duracao between 90 and 120),
constraint pkPartidaChave primary key (id_partida)
);
Insira alguns dados nas tabelas Jogador_Validacao, Time_Validacao e Partida_Validacao conforme solicitado abaixo:
Insira dois jogadores com número de camisa idênticas
	INSERT INTO Jogador_Validacao 
VALUES(986543, 'Chupeta','Goleiro', 'm', 23, 'craque', 15, 7, 2000);
INSERT INTO Jogador_Validacao 
VALUES(283543, 'Neimala','Atacante', 'm', 23, 'esforcado', 17, 7, 2000);
Insira um jogador com idade superior aos 40 anos e com uma velocidade de chute menor que 10 km
	INSERT INTO Jogador_Validacao 
VALUES(183143, 'Romaldinho','Atacante', 'm', 45, 'esforcado', 9, 7, 2000);
Insira um Time com ano de fundação inferior a 1900
	INSERT INTO Time_Validacao 
VALUES(01,'Flamengo','Rio de Janeiro', 1500);
Insira uma Partida com duração de 150 minutos
	INSERT INTO Partidada_Validacao 
VALUES(20,'Estadual', 150);
Insira um Jogador com a seguinte habilidade ‘razoável’
	INSERT INTO Jogador_Validacao 
VALUES(183143, 'Romaldinho','Atacante', 'm', 45, 'razoável', 9, 7, 2000);
Que tipo de restrição de integridade foi respeitada através da criação das tabelas da questão seis, levando em conta as inserções da questão anterior? Justifique sua resposta.
R.I Alcance, porque verificou o volume de dados investigados pela R.I
R.I Estado, porque analisou os valores novos e antigos dos dados.
Nível 4 
Crie as tabelas abaixo:
create table fornecedores (
fornecedores_id serial,
fornecedores_nome varchar(50) 	not null,
PRIMARY KEY (fornecedores_id)
);
create table produtos (
produtos_id serial,
fornecedores_id_ref int not null,
PRIMARY KEY (produtos_id)
FOREIGN KEY (fornecedores_id_ref) REFERENCES fornecedores (fornecedores_id)
);
Insira alguns dados nas tabelas fornecedores e produtos conforme solicitado abaixo:
Insira dois fornecedores distintos
Insira três produtos distintos, e os associe a um mesmo fornecedor
Consulte os dados de todos os produtos
Consulte os dados de todos os fornecedores
Delete (ou tente deletar) o fornecedor que possui produtos associados
Consulte novamente todos os dados da tabela produtos
A deleção do fornecedor foi possível? Explique o porquê apontando que restrição de integridade entrou em ação e a relação desta restrição com a forma como a tabela produtos foi criada?
R= 
Nível 5
Crie a tabela abaixo na mesma instância:
create table fornecedores2 (
fornecedores_id serial,
fornecedores_nome varchar(50) 	not null,
CONSTRAINT pkfornecedor_2 PRIMARY KEY (fornecedores_id)
);
create table produtos2 (
produtos_id 	serial,
fornecedores_id_ref int not null,
CONSTRAINT pkprodutos_2 PRIMARY KEY (produtos_id, )
CONSTRAINT fk_fornecedores2 FOREIGN KEY (fornecedores_id_ref)
REFERENCES fornecedores2 (fornecedores_id)
ON DELETE CASCADE
);
Insira alguns dados na tabela produtos2conforme solicitado abaixo:
Insira dois fornecedores distintos
Insira três produtos distintos, e os associe a um mesmo fornecedor
Consulte os dados de todos os produtos
Consulte os dados de todos os fornecedores
Delete (ou tente deletar) o fornecedor que possui produtos associados
Consulte novamente todos os dados da tabela produtos
A deleção do fornecedor foi possível? Explique o porquê apontando que restrição de integridade entrou em ação e a relação desta restrição com a forma como a tabela produtos foi criada?
R= 
Controle de Segurança
Conceda privilégios de visualização (SELECT) dos dados da tabela Jogador_Validação para outro usuário sem propagação. (criar outros usuários)
Teste a aplicabilidade da questão 15. Isto, é, mostre que o usuário que recebeu o privilégio, efetivamente pode usufruí-lo.
Conceda privilégios de atualização (UPDATE) dos dados da tabela Time_Validacao para outro usuário sem propagação. (USUÁRIO DISTINDO DDA QUESTÃO ANTERIOR) 
Teste a aplicabilidade da questão 17. Isto, é, mostre que o usuário que recebeu o privilégio, efetivamente pode usufruí-lo.
Conceda privilégios de inserção(INSERT) e deleção(DELETE)dos dados da tabela Fornecedores2 para outros usuários sem propagação. 
Teste a aplicabilidade da questão 19. Isto, é, mostre que o usuário que recebeu o privilégio, efetivamente pode usufruí-lo.
Conceda privilégios de visualização e atualização (SELECT, UPDATE) de dados da tabela Jogador_Validacao para outro usuário com propagação de privilégios. 
Teste a aplicabilidade da questão 21.
Solicite ao usuário que recebeu privilégio da questão 21,que o propague a um terceiro usuário com propagação de privilégios e posteriormente para que o terceiro propagará a um quarto usuário com propagação de privilégios.
Teste a aplicabilidade da questão 23.