INTEGRIDADE REFERENCIAL Para que possa ser estabelecida a integridade referencial é necessário que se verifique o seguinte conjunto de condições: - O campo da tabela de origem da relação deve ser um campo de chave primária ou um campo possuindo um índice único - Os campos relacionados devem ser do mesmo tipo de dados - Ambas as tabelas devem ser do formato das tabelas do Acess Quando é estabelecida a integridade referencial num relacionamento, as consequências são as seguintes: - Não é possível introduzir um valor numa chave externa de um registo de uma tabela, desde que esse valor não exista como chave primária na tabela de origem; Todavia, se a chave externa não fizer parte da chave primária da tabela de destino, o Acess permite introduzir um nulo como “valor” de uma chave externa. - Não pode ser eliminado um registo de uma tabela se existirem registos relacionados noutras tabelas - Não pode ser alterado o valor de uma chave primária num registo de uma tabela, se existirem registos relacionados noutra tabela e ligados por essa chave. JUNÇÃO (JOIN) Uma operação de junção de várias tabelas é decomponível decomponível numa projecção de uma restrição de um produto. Se observarmos o conjunto de comandos SQL apresentados nesta query (junção) podemos verificar o seguinte: A cláusula FROM define um produto: o produto das tabelas ALUNOS, DISCIPLINAS e EXAMES. O comando SELECT executa uma projecção, definindo quais são os campos que devem figurar no resultado da query. A cláusula WHERE estabelece e stabelece a restrição, definindo as condições a que uma linha da tabela produto deve obedecer para que possa figurar no resultado da query. SELECT ALUNOS.[Nome De Aluno], DISCIPLINAS.[Designação De Disciplina], EXAMES.Nota FROM ALUNOS, DISCIPLINAS, EXAMES WHERE ALUNOS.IdAluno=EXAMES.IdAluno AND ALUNOS.[Morada De Aluno] <> EXAMES.Local; PRODUTO O produto de duas tabelas A e B é uma nova tabela com: - Um conjunto de atributos igual ao conjunto dos atributos de A acrescido do conjunto de atributos de B - Um conjunto de linhas formado por todas as combinações de cada linha de A com cada linha de B Para que a operação seja possível é necessário que não existam atributos comuns nas tabelas A e B. Se assim fosse, a tabela resultante apresentaria atributos repetidos, violando um dos princípios das tabelas relacionais. Apesar disso, é possível executar o produto p roduto de duas tabelas com atributos a tributos comuns, desde que, previamente, os atributos comuns sejam renomeados. O número de atributos da tabela produto será igual à soma do número de atributos da tabela A e da tabela B. O número de linhas será igual ao produto do número de linhas de A pelo número de linhas de B. A operação produto, só por si, tem pouco interesse prático. No entanto, ela é importante como passo intermédio na obtenção dos resultados da operação junção (join). DISTINCT A palavra chave DISTINCT faz com que só apareça uma linha dentre muitas possivelmente iguais numa junção Ex: SELECT SELECT DISTINCT DISTINCT ALUNOS.[Nome ALUNOS.[Nome De Aluno], Aluno], ALUNOS.[Mor ALUNOS.[Morada ada De Aluno] Aluno] AS [Morada [Morada De Aluno], EXAMES.Local AS [Local De Exame] FROM ALUNOS, EXAMES WHERE ALUNOS.[Morada De Aluno] <> EXAMES.Local ORDER BY ALUNOS.[Morada De Aluno]
Aqui, por exemplo, se não usássemos a palavra DISTINCT, apareceriam muitas linhas repetidas, resultado do produto FROM ALUNOS, EXAMES. Assim ele distingue os alunos da tabela alunos presentes no produto e é só esses que apresenta, isto é, os exames feitos por esse aluno e não por outros. Outr Outra a mane maneir ira a é usar usar o WHE WHERE para para faz fazer igua iguall numa numa e nout noutrra tabel abela a IdAl IdAlun uno. o. Experimentar............................................... AS A palavra chave AS renomeia um campo a aparecer no query, diferentemen te do que aparece na tabela ORDER BY A palavra chave ORDER BY, ordena por... (campo), por ordem crescente. COUNT(*) A palavra chave COUNT(*) conta o número de itens de uma tabela MAX(tabela.campo) e MIN(tabela.campo) Calculam o valor máximo e mínimo, respectivamente dos campos das tabelas indicados entre parentesis. IN (subquery) A palavra chave In utiliza-se quando se quer fazer uma query a uma query feita dentro dos parentesis. A tabela a inserir/ADD é só aquela que é feita na query; as da subquery não se inserem/ADD WHERE NOT EXISTS (SELECT * Selecciona só os que fizeram os exames a todas as disciplinas DIVISÃO A operação divisão pode ser executada entre duas tabelas que possuam pelo menos um atributo em comum (atributo esse definido no mesmo domínio). Considere-se a tabela A com atributos x e y e a tabela B constituída apenas pelo atributo y (definido no mesmo domínio do atributo y da tabela A) A tabela que resulta da divisão de A por B é: Uma tabela constituída apenas pelo atributo x e contendo as linhas que na tabela A têm como valor no atributo y todos os valores de y constantes na tabela B. UNION Permite a união de 2 queries numa só. SELECT... UNION SELECT... Atenção: As tabelas resultantes das 2 queries têm de ser compatíveis para pa ra a operação União, isto é, têm de ter o mesmo número de colunas com o mesmo nome e definidas no mesmo domínio. AVG Executa uma média. SELECT AVG(EXAMES.Nota) AS Média.... SUM Executa uma soma. SELECT SUM(DISCIPLINAS.Créditos) AS Soma...
NORMALIZAÇÃO DE TABELAS FORMAS NORMAIS A normalização é um processo que consiste em estruturar as tabelas e os atributos na forma mais adequada, do ponto de vista das operações a executar sobre a informação registada na base de dados, tendo em vista eliminar redundâncias desnecessárias e evitar problemas com a inserção, eliminação e actualização de dados. O modelo original previa a existência de três formas normais, designadas respectivamente por primeira forma normal (1FN), segunda forma normal (2FN) e terceira forma normal (3FN). Existe uma relação entre essas formas normais, que pode ser expressa do seguinte modo:
Uma tabela pode estar na primeira forma normal, mas não obedecer aos requisitos necessários para ser considerada na segunda forma normal. - Do mesmo modo... Na prática, os procedimentos de normalização consideram-se geralmente satisfatórios se as tabelas atingirem a 3FN. Os desenvolvimentos posteriores do modelo levaram à conclusão de que existem situações em que a 3FN não constitui o estado final “ideal”. Nesse sentido, foram criadas outras especificações que ficaram conhecidas pelas seguintes designações: - Forma normal Boyce/Codd - Quarta forma normal (4FN) - Quinta forma normal (5FN) -
PRIMEIRA FORMA NORMAL (1FN) Condição: Uma tabela encontra-se na 1FN se todos os seus atributos estiverem definidos em domínios que contenham valores atómicos. Dito por outras palavras: os domínios devem ser formados por valores elementares e não por conjuntos de conjuntos. Esta é aliás uma condição imposta para que a tabela seja considerada como uma tabela do modelo relacional: um atributo só pode admitir valores elementares e não conjuntos de valores. EX: de não 1FN: ALUNOS(IdAluno, Nome, Morada, Disciplinas), onde Disciplinas seria do tipo Matemática, Economia, Direito a representar as disciplinas em que o aluno estava matriculado. Esta tabela não obedece à 1FN, uma vez que o atributo Disciplinas admite conjuntos de valores. Pode ultrapassar-se este problema repetindo valores nas linhas da tabela, por forma a que, em cada linha, o atributo Disciplinas contenha um único valor. Esta nova tabela encontra-se na 1FN, pois todos os atributos contêm valores elementares. Apresentam no entanto evidente redundância de informação, que se reflecte na repetição de identificadores, dos nomes e das moradas dos alunos (para além de outros eventuais atributos que uma tabela real de alunos deveria conter). Para além desse inconveniente, podem apontar-se ainda os seguintes: PROBLEMAS CE ACTUALIZAÇÃO Se a morada de um aluno for alterada, essa alteração tem de ser feita em várias linhas da tabela, sob pena de se gerar um estado de inconsistência da base de dados. PROBLEMAS DE INSERÇÃO Com esta estrutura não é possível registar informação relativa a um aluno que, por qualquer razão, não esteja matriculado a nenhuma disciplina (externo). PROBLEMAS DE ELIMINAÇÃO No caso de um aluno matriculado a uma só disciplina, se essa matrícula for anulada, perde-se, não apenas apenas a informa informação ção refere referente nte à matrícu matrícula, la, mas também também a informaç informação ão relati relativa va aos restantes atributos do aluno. Os problemas referidos podem eliminar-se criando: - Uma tabela para registar os dados dos alunos - Uma tabela para os dados relativos às disciplinas - Uma tabela para representar as matrículas O esquema seria: ALUNOS(IdAluno, Nome, Morada, ...) DISCIPLINAS(IdDisciplina, Designação,...) MATRÍCULAS(IdAluno, IdDisciplina, Data, ...) Esta Esta deco decomp mposi osição ção da tabel tabela a permi permite te elim elimin inar ar os probl problem emas as anter anterio iorm rment ente e refer referid idos, os, relacionados com a actualização, a inserção e eliminação de dados nas tabelas. As novas tabelas apresentam-se aliás já na terceira forma normal e não apresentam nenhum dos problemas referidos. SEGUNDA FORMA NORMAL (2FN) Antes DEPENDÊNCIA FUNCIONAL Ex: Considere-se uma tabela A com os atributos x, y, z e w. A(x, y, z, w)
O atributo z é funcionalmente dependente do atributo x se, para um dado valor de x, o valor de z é sempre (não apenas para um conjunto concreto de valores que num particular momento estejam armazenados na tabela) o mesmo. x->z Pode também ser composição de atributos (x,y)->w Existe uma dependência funcional entre o atributo x e oatributo z. O atributo x designa-se por determinante; o atributo z designa-se por dependente. Em regra, regra, entre entre os atributo atributoss de uma tabela tabela podem podem estabel estabelece ecer-s r-se e várias várias depend dependênci ências as funcionais triviais. Tem-se uma dependência funcional trivial quando o dependente é um subconjunto do determinante. DEPENDÊNCIA FUNCIONAL ELEMENTAR Como se observou anteriormente, o atributo w é funcinalmente dependente dos atributos {x,y}, tomados em conjunto, mas não é funcinalmente dependente de nenhum dos atributos x ou y, tomados isoladamente. Diz-se, neste caso, que existe uma dependência funcional elementar entre o conjunto de atributos {x,y} e o atributo w, ou que o atributo w é irredutivelmente dependente de {x,y}. Por outras palavras: o atributo w depende do conjunto {x,y} como um todo, e não depende de nenhum dos seus subconjuntos, tomados isoladamente. CONDIÇÕES PARA QUE UMA TABELA SE ENCONTRE NA 2FN Considere-se uma tabela constituída por uma única chave candidata que funciona como chave primária. Essa tabela encontra-se na 2FN se: - Estiver na 1FN - Todos os atributos que não pertencem à chave, dependem da chave através de uma dependência funcional elementar (isto é, dependem do conjunto e não dependem de nenh nenhum um dos dos seus seus elem elemen entos tos ou subc subconj onjunt untos os isol isolada adame ment nte). e). Esta Esta condi condiçã ção o evidentemente só se aplica no caso de a chave ser composta por mais que um atributo. Caso a chave seja constituída por um único atributo a condição imposta é a de que os restantes atributos dependam funcionalmente da chave. Ex: ALUNOS(IdAluno, Nome, Morada, IdDisciplina, Designação) não está na 2FN Se dividir em 3 tabelas, como acima explicitado, já está na 2FN. ALUNOS e DISCIPLINAS vêse logo. MATRÍCULAS: O único atributo que não faz parte da chave (Data), depende funcinalmente do conjunto da chave mas não é possível estabelecer uma dependência funcional com qualquer dos componentes da chave, tomados isoladamente. Nem o atributo IdAluno determina a data (pois o mesmo aluno pode matricular-se em datas diferentes, a diferentes disciplinas), nem a data é determinada pelo atributo IdDisciplina, porque diferentes alunos podem efectuar a matrícula numa disciplina, em diferentes datas. O atributo data é irredutivelmente dependente da chave. TERCEIRA FORMA NORMAL CONDIÇÕES PARA QUE UMA TABELA SE ENCONTRE NA 3FN - Estiver na segunda forma normal - Nenhum dos atributos que não fazem parte da chave for funcionalmente dependente de qualquer combinação dos restantes, ou seja, cada atributo deve depender apenas da chave e não de qualquer outro atributo ou conjunto de atributos. Isto visa eliminar as chamadas dependências transitivas. Ex: A(x,y,z) . x é a chave primária. Não existem mais chaves candidatas. Os atributos y e z depend dependem em funcio funcional nalment mente e de x. Todavia Todavia,, verifi verificaca-se se uma transi transitiv tivida idade: de: o atributo atributo z também depende funcionalmente de y. x->y y->z x->z => não 3FN Para que uma tabela esteja na 3FN é necessa´rio que todos os atributos que não pertenem à chav chave e seja sejam m mutua mutuamen mente te inde indepe pende ndent ntes es,, isto isto é se puder puderem em ser ser actua actualiliza zados dos independentemente uns dos outros. Ex: JOGO(IdJogo, Estádio, Nome-árbitro, Categoria-árbitro) - Todos os atributos são funcionalmente dependentes da chave - Verifi Verificaca-se se uma situaç situação ão de transi transitiv tividad idade: e: o atribut atributo o Categor Categoria-á ia-árbi rbitro tro depende depende funcinalmente do atributo Nome-árbitro; não são mutuamente independentes. Logo, a tabela não se encontra na 3FN, o que implica: PROBLEMAS DE ACTUALIZAÇÃO A informação relativa aos árbitros e respectiva categoria pode aparecer repetidamente na tabela. Se o árbitro mudar de categoria, essa alteração deve ser efectuada em todas as ocorrências desse valor. Se existisse uma tabela própria para registar os dados referentes aos árbitros, a alteração da categoria seria feita num único registo.
PROBLEMAS DE INSERÇÃO Se um árbitro ainda não tiver arbitrado nenhum jogo, mas já estiver inscrito na respectiva associação com determinada categoria, ele não pode ser registado nesta tabela PROBLEMAS DE ELIMINAÇÃO Se um árbitro apitou um só jogo... Ex: OK, já na 3FN JOGO(IdJogo, Estádio) ÁRBITRO(IdÁrbitro, Nome-árbitro, Categoria-árbitro) JOGO-ÁRBITRO(IdJogo, IdÁrbitro) FORMA NORMAL DE BOYCE/CODD (FNBC) Constitui um “reforço” ou “aperfeiçoamento” das especificações da 3FN, destinadas a lidar com situações em que se verifique: - A existência de mais do que uma chave candidata e - Que duas chaves candidatas tenham elementos comuns Uma tabela está na forma normal Boyce/Codd quando os únicos determinantes são chaves candidatas. Ex: Numa universidade os alunos frequentam várias disciplinas. Cada professor lecciona apenas uma disciplina, mas uma disciplina pode ser leccionada por vários professores. Cada aluno, em cada disciplina, tem apenas um professor. A tabela A(IdAluno, IdDisciplina, Professor) está na forma 3FN porque: - O atributo Professor é o único atributo que não faz parte da chave; - Esse atributo depende irredutivelmente da chave. No entanto esta situação é diferente da que foi considerada quando se definiu a 3FN, porque: - Existe Existem m duas duas chaves chaves candid candidata atass compos compostas tas:: {IdAlu {IdAluno, no, IdDisc IdDiscipl iplina ina}} e {IdAlu {IdAluno, no, Professor} - Estas chaves têm um atributo comum: IdAluno - O atributo Professor não é chave candidata, mas é um determinate (como cada professor só lecciona uma disciplina, o nome do professor determina a disciplina) Esta Esta última última situaç situação ão viola viola a especi especific ficação ação da forma forma normal normal Boyce/C Boyce/Codd odd (só as chaves chaves candidatas podem ser determinantes). Assim pode eliminar-se informação a mais: se eliminarmos uma linha (aluno A1 frequenta a disciplina D2), podemos eliminar igualmente informação que o professor P2 lecciona essa disciplina, se esse professor não aparecer em mais nenhuma linha. Então devemos decompor em: B(IdAluno, IdProfessor) C(IdProfessor, IdDisciplina) Esta decomposição não origina perda de informação uma vez que a tabela original pode ser obtida através da junção destas duas tabelas, com base no atributo comum IdProfessor. Procedimento para efectuar a normalização FNBC: Decompor as tabelas em projecções por forma a eliminar todas as dependências funcionais em que o determinante não seja chave. QUARTA FORMA NORMAL (4FN) Recorre ao conceito: MULTIDEPENDÊNCIA OU DEPENDÊNCIA DE VALORES MÚLTIPLOS EX: Considere-se Considere-se uma tabela A com atributos atributos x, y, z, w. Diz-se que x multidetermina multidetermina y (ou que y é multidependente de x) se a um valor de x está associado um conjunto de valores de y e esse conjunto de valores de y é independente dos restantes atributos da tabela, z e w. x-->->y O conceito estende-se a conjuntos de atributos. X-->->Y A multidependência diz-se elementar se não existem subconjuntos de X e de Y entre os quais se verifique igualmente multidependência. CONDIÇÕES PARA QUE UMA TABELA SE ENCONTRE NA 4FN Uma tabela encontra-se na 4FN quando as únicas multidependências elementares são aquelas em que uma chave determina um atributo. Visa eliminar a possível existência de grupos repetidos independentes.
Ex: A(Aluno, Disciplina, Actividade) Todos os atributos pertencem à chave. Obedece a FNBC. Todavia, podemos imaginar uma tabela com alguns dados hipotéticos para evidenciar as redundâncias Aluno Disciplina Actividade João Matemática Futebol João Matemática Cinema João Economia Futebol João Economia Cinema Ana Direito Basquetebol Ana Direito Teatro Ana Informática Basquetebol Ana Informática Teatro Não se pode obstar que haja duplicação pois se João deixasse de praticar futebol, termos de manter manter a inform informação ação que contin continua ua matricu matriculad lado o em 2 discip disciplin linas. as. Haveria Haveria proble problemas mas de actualização. - O atributo Disciplina é claramente um atributo que gera grupos repetidos, porque um aluno pode frequentar várias disciplinas. - O atributo Actividade tem as mesmas característic características. as. É um atributo que pode assumir um conjunto de valores; - Além disso, é razoável admitir que o atributo Disciplina é independente do atributo Actividade. Temos assim uma multidependência ou uma dependência de valores múltiplos. A cada aluno corresponde: - Um conjunto de disciplinas - Um conjunto de actividades Aluno -->-> Disciplina Aluno -->-> Actividade Então há que decompor: B(Aluno, Disciplina) e C(Aluno, Actividade). Fazendo a junção das duas tabelas obtemos a inicial, pelo que não há perda de informação. Outro exemplo: Imagine-se o caso de uma empresa que efectua viagens de transporte de mercadorias. Em cada viagem o transporte pode passar por várias cidades. A tripulação utilizada em cada viagem é constituida por várias pessoas cuja função é a carga e descarga de mercadorias. Admitamos: A(Viagem, Cidade, Tripulação) Não Não se enco encont ntra ra na 4FN, 4FN, pois pois os atri atribut butos os Cida Cidade de e Trip Tripul ulaç ação ão são são grupo gruposs repet repetid idos os independentes, porque: - Uma viagem pode passar por várias localidades; - A tripulação pode ser constituída por várias pessoas; - As localidades são independentes da tripulação. Verifi Verificaca-se se uma multide multidepend pendênc ência ia entre entre Viagem Viagem e Cidade Cidade,, assim assim como como entre entre Viagem Viagem e Tripulação. Então=> B(Viagem, Cidade) e C(Viagem, Tripulação) QUINTA FORMA NORMAL (5FN) ex: (Artista, Filme, Realizador) Artista Filme Realizador A1 F1 R1 A1 F1 R2 A1 F2 R2 A1 F3 R1 A2 F1 R1 A2 F1 R2 A2 F2 R2 A3 F3 R3 A3 F4 R4 Esta tabela visa responder a questões do tipo: Quais os filmes em que participou o artista X e que tenham sido dirigidos pelo realizador Y?
Aparentemente, nada existe de “errado” com esta tabela. A resposta à pergunta anterior pode po de facilmente obter-se através de uma operação de restrição sobre a tabela, definindo critérios de selecção selecção das linhas para os artista artista e para o realizador. realizador. Pode ainda verificar-se o seguinte: Se for efectuada uma decomposição da tabela nas seguintes projecções: (Artista, Filme) e (Realizador, Filme) o resultado do join dessas duas tabelas não reproduz a original. Verificar-se-ão 2 resultados anómalos: A1 – F3 – R3 e A3 – F3 – R1. Pode ainda verificar-se que não é possível essa decomposição, quaisquer que sejam as projecções de duas colunas que se formem a partir da tabela original. O facto relevante é: A circunstância de não ser possível a decomposição em duas projecções, não significa que não seja possível efectuar a decomposição em mais de duas projecções. Assim: (Artista, Filme) (Realizador, Filme) (Artista, Realizador) => a join das 3 dá a original. DEPENDÊNCIA DE JUNÇÃO Considere-se uma tabela A com atributos (a1, a2, ...,an) e os seguintes subconjuntos x1, x2, ..., xk, formados a partir do conjunto de atributos da tabela. Imagine-se que, a apartir da tabela A é efectuada uma decomposição com as projecções x1, x2, ..., xk. Se a tabela A puder ser obtida a partir da junção das projecções x1, x2, ...,xk diz-se que, nessa tabela existe uma dependência de junção dessas projecções. *{x1, x2, ..., xk} CONDIÇÕES PARA QUE UMA TABELA SE ENCONTRE NA 5FN Uma tabela encontra-se na 5FN se qualquer dependência de junção é implicada pelas chaves candidatas. Se for possível encontrar dependências de junção que não sejam implicadas pelas chaves, então então não é possív possível el proced proceder er a uma decompo decomposiç sição ão da tabela, tabela, recuperáv recuperável el atravé atravéss da operação de junção dessas projecções. Esta tarefa nem sempre será fácil. São igualmente discutíveis as vantagens práticas de levar o processo de decomposição até este ponto. Tomese, por exemplo, o caso anteriormente estudado da tabela (Artista, Filme, Realizador). As condições definidas pela 5FN excluem do processo de normalização os acsos em que a decomposição implicaria sempre as chaves, uma vez que essa decomposição não apresenta à partida qualquer interesse.
