Departamento: Departamento de Engenharias e Ciência da Computação Curso: Ciência da Computação Semestre: 4o Ano: 2006 Disciplina: Banco de Dados II Créditos: 04 Professora: Cristina Paludo Santos Restrições de Integridade - Resumo 1. Integridade de Dados: Idéia básica: São regras impostas pelo projetista do banco de dados no momento de sua criação e que devem ser cumpridas pelo SGBD. Objetivo principal: Resguardar a consistência do banco de dados expressada por condições sobre o estado das informações. 1.1 Tipos de Restrições Existem diferentes categorias de restrições que podem ser definidas em uma base de dados: I) Restrição de Chave ou Restrição de Integridade da Entidade: Esta regra se refere a declaração de chaves nas tabelas. Uma tabela pode conter , no mínimo uma, ou várias chaves candidatas. Uma chave candidata é um ou mais atributos que identifica de forma única uma linha. Uma tabela pode ter mais de uma chave candidata, sendo que a chave primária é uma das chaves candidatas da tabela. As chaves primárias deverão ser identificadas unicamente e não podem ser nulas. Assim como a definição de chave primária não permite que valores se repitam nas diferentes linhas, as chaves candidatas também impõem esta restrição, com a diferença de que podem assumir valores nulos. As chaves candidatas que não são PRIMARY KEY, são UNIQUE. Os atributos que não fazem parte das chaves candidatas são conhecidos como “não-chave”. Um comando SQL pode conter apenas uma declaração PRIMARY KEY, mas várias declarações UNIQUE. Abaixo encontra-se um exemplo de declaração desse tipo de restrição: CREATE TABLE db.Aluno ( Matricula int(5) PRIMARY KEY, Nome char(50) , RG int(10) UNIQUE, CPF int(9) UNIQUE); Chave primária Chaves candidatas Chaves alternativas OBS.: As chaves alternativas também servem para diferenciar uma linha de outra, mas é a chave primária que é utilizada para representar relacionamentos entre tabelas. II) Restrição de Integridade Referencial: Esta regra se refere a declaração de chaves estrangeiras de uma relação. As referências entre as tabelas devem existir ou serem nulas (desde que não faça parte da chave primária). A chave estrangeira implementa um “apontador” lógico para outro objeto. Restrições de Integridade – Banco de Dados II 1 Por exemplo: não cadastrar um empregado em Departamento que não existe. Da mesma forma, não excluir um departamento, enquanto houver algum empregado vinculado a ele. Quando a operação for impedida, atribuir um novo valor para a coluna que implementa o relacionamento ou realizar a deleção em cascata. Todos os registros ligados àquele campo serão excluídos. pedido codigo cliente data_pedido 1 1 Dec 9 1992 10:50AM 2 1 Dec 9 1997 10:51AM 3 1 Dec 9 1996 10:51AM 4 2 Dec 9 1995 10:51AM 5 5 Dec 9 1994 10:51AM 6 5 Dec 9 1997 10:51AM 7 5 Dec 9 1994 10:51AM 8 5 Dec 9 1997 10:51AM 9 12 Dec 9 1994 10:51AM FK total 7,113.90 882.57 1,365.79 3,890.82 1,085.81 4,068.93 3,932.02 3,026.01 1,140.65 itens_pedido pedido produto quant 1 1 2.000 1 2 1.000 1 13 12.000 1 16 12.000 1 17 5.000 1 19 78.000 2 3 10.000 2 13 4.000 situacao vendedor T 2 T 2 T 2 produto T 2 codigo nome T 2 1 LAMINA PARA MAQUINA HIDRAULICA A 2 2 LAMINA DE SERRA MANUAL A 6 3 DISCO DE FREIO T 6 4 CABO DE ACO DO VELOCIMETRO T 7 5 BROCA DE 8 MM FK 6 FERRO FUNDIDO RECICLADO 7 MANIVELA DE TORNO HIDRAULICO total situacao 1,217.70 A 9 TAMBOR DE FREIO AUTOMOVEL 78.30 A 10 TAMBOR DE FREIO MOTOCICLETA 939.60 A 95.40 A 36.80 A 4,738.50 A 7.60 A 313.20 A tipo 1 1 3 3 1 5 1 3 4 unidade UND UND UND UND UND UND UND UND UND Na linguagem SQL a declaração de chave estrangeira se dá da seguinte forma: CREATE TABLE db.Aluno ( Matricula int(5) PRIMARY KEY, Nome char(50) , RG int(10) UNIQUE, CPF int(9) UNIQUE, CodCurso int (3), FOREIGN KEY (CodCurso) REFERENCES CURSO(CodCurso)); III) Restrição de Nulidade (NULL/NOT NULL): Um NULL (nulo) indica a ausência de valor para um atributo – não existe ou não informado. Exemplo: - Tipo de sangue desconhecido - Nota do aluno não informada (é ≠ de zero) - Não tem telefone O conceito de NULL é fundamental para a integridade do banco de dados. Exemplo: NOT NULL é uma restrição automática para chave candidata (primary key) Chave estrangeira pode ser NULA Implementa algumas regras do âmbito da aplicação Todo funcionário deve ter um nome Todo cliente deve ter um endereço Todo produto tem um código Restrições de Integridade – Banco de Dados II 2 CREATE TABLE db.Aluno ( Matricula int(5) PRIMARY KEY, Nome char(50) NOT NULL, Endereço char(40) NOT NULL, Telefone int(10), RG int(10) UNIQUE, CPF int(9) UNIQUE); Colunas obrigatórias Coluna opcional IV) Restrição de Domínio e Regras: Um domínio define o universo de valores permitidos para um determinado item de dado. Um domínio compreende um tipo de dado pré-definido, mais um conjunto de restrições de integridade que limitam os valores permitidos para este tipo de dado. Por exemplo: Se a coluna de uma tabela for Data_de_Nascimento, não aceitar valores inválidos como 31/02/1998. Da mesma forma, não devem ser aceitas datas além do dia de hoje. Certos sistemas poderão, ainda, validar as datas, não aceitando nascimentos antes de 1920. Assim os valores ficam coerentes com a realidade em questão. Um domínio pode ser utilizado em mais de um campo. Se for definido um domínio UF, ele poderá validar UF_Nascimento, UF_Residência, UF_Trabalho, etc... Podem ser usadas Rules (regras) para que valores: (a) estejam dentro de uma faixa, (b) valores adotem certo padrão, (c) aceite valores que estão em um conjunto (lista). Para isso usa-se a clausula CHECK da linguagem SQL, associada ao comando CREATE TABLE. Veja o exemplo abaixo: create table Professores ( matricula int(3) primary key, nome varchar(40) not null, RG numeric(10) unique, sexo char(1) CHECK (sexo in ('M','F')), idade tinyint CHECK (idade between 21 and 80), titulação varchar(15) CHECK (titulacao in ('graduado','especialista', 'mestre', 'doutor')), categoria varchar(15) CHECK (categoria in ('auxiliar','assistente', 'adjunto', 'titular')), nroTurmas tinyint CHECK (nroTurmas >= 0)); Além de associar restrições aos atributos de uma tabela, podem também ser associados regras às tabelas propriamente ditas. Para isso podem ser usados gatilhos (ou triggers). V) Triggers (ou gatilhos): Uma forma de se assegurar a integridade de um banco de dados é armazenar certos 'gatilhos' que são disparados por eventos pré-determinados. Esses gatilhos, também chamados de Triggers funcionam no sistema Condição Ação e são programas que são executados quando uma condição pré-definida for alcançada. As operações que podem disparar um trigger são: (a) inserção de linha(s), (b) atualização de algum atributo de linha(s) existente(s), (c) remoção de linha(s). Você pode desenvolver um trigger e associa-la a uma tabela do banco de dados para executar um bloco de instruções implícitas, sempre que um comando de manipulação de dados for emitido pela tabela. Por exemplo: Um trigger de banco que mantenha os valores do salário como válidos, de acordo com a categoria profissional do empregado. Restrições de Integridade – Banco de Dados II 3 Abaixo exemplo de criação de trigger. CREATE TRIGGER exigeFornecedor BEFORE UPDATE OF CodFornecedor ON Embarque REFERENCING NEW AS N FOR EACH ROW WHEN (N.CodFornecedor IS NULL) SIGNAL SQLSTATE ‘70005’ (‘Código do Fornecedor deve ser Informado’); OBS: A forma de sinalizar erro varia de SGBD para SGBD Um trigger contém 3 partes: Evento Condição Ação. Quando o evento ocorre o sistema testa a condição e executa a ação. Instrução possíveis que podem disparar o trigger: INSERT ON table, DELETE ON table, UPDATE [OF coluna] ON table. A declaração FOR EACH ROW indica que a ação é executada para cada linha modificada. A ação de modificação pode ser executada após (AFTER) ou antes (BEFORE) do evento ocorrer. Variáveis usadas em triggers: OLD – A linha modificada antes da ocorrência do evento NEW – A linha modificada após a ocorrência do evento VI) Stored Procedures: Pode-se, ainda, armazenar no banco de dados algumas procedures. Elas são um conjunto de instruções que poderão ser executadas repetidas vezes. Por exemplo: executar a atualização na tabela de logs toda a vez que a tabela de salário for modificada, inserindo a identificação do usuário responsável e a data da alteração. As stored procedures são invocadas explicitamente, portanto comandos de COMMIT, ROLLBACK e SAVEPOINT são permitidos no corpo das instruções. Obs.: Pode-se dizer que a principal diferença entre trigger e stored procedure é que, a primeira é executado automaticamente, enquanto o segundo precisa ser explicitamente “chamado” por um programa ou, até mesmo, por um trigger. Restrições de Integridade – Banco de Dados II 4 CONSIDERAÇÃO FINAL: Convencional Aplicação REGRAS Aplicação REGRAS Servidor de Arquivos ou SGBD incompleto Inteligência no aplicativo Aplicação REGRAS Dados SGBD Bem Implementado Visual Fox Pro, Access, Delphi SQL Engine ODBC, Excel Visual Basic, C++, Java Regras Dados Restrições de Integridade – Banco de Dados II Inteligência no database 5