Bases de Dados (usando Microsoft Access)

1. Bases de Dados(usando Microsoft Access) Alberto Manuel Simões (albie@alfarrabio.di.uminho.pt) Alberto Simões - Dept Informática - Univ. Minho

2. Motivação • Qualquer colecção deve estar catalogada; • Qualquer catálogo deve permitir pesquisas; • Um catálogo não é mais que uma colecção de registos; • Podemos ver uma base de dados como sendo um catálogo informatizado e com métodos optimizados de pesquisa e armazenamento. Alberto Simões - Dept Informática - Univ. Minho

3. Noção de Base de Dados • Qualquer conjunto de registos pode ser visto como uma base de dados; • Um conjunto de registos com a mesma estrutura é denominado por tabela: Alberto Simões - Dept Informática - Univ. Minho

4. Noção de Base de Dados (2) • A uma linha da tabela chama-se registo; • A cada elemento do registo chama-se campo ou atributo; • Chama-se chave a um campo (ou conjunto de campos) que nunca se repete em toda a tabela; Da tabela anterior, que chave poderíamos escolher? Alberto Simões - Dept Informática - Univ. Minho

5. Tipos de dados • Cada campo de uma tabela tem um tipo de dados definido: • Número; • Texto; • Booleano; • Data; • Moeda Que tipos de dados associar a cada um do campos da tabela anterior? Alberto Simões - Dept Informática - Univ. Minho

6. Divisão de Tabelas • Vamos construir uma base de dados para livros: • Título; • ISBN; • Ano da edição; • Editora; • Colecção; • Autores Quantos autores é que um livro tem? Teoricamente, não existe limite para o número de autores. Alberto Simões - Dept Informática - Univ. Minho

7. Divisão de Tabelas (2) • Soluções: • criar um campo texto, onde se introduziria todos os nomes dos autores; • criar um número de campos elevado para armazenar os autores: • Ao definir um número de autores elevado para cada livro, a tabela iria ficar com muitos campos em branco; • Qual o número de campos a definir? Ou se define um número excessivamente exagerado, ou pode sempre ocorrer que venha a aparecer um livro com mais autores que campos disponíveis. Alberto Simões - Dept Informática - Univ. Minho

8. Divisão de Tabelas (3) • A solução para este tipo de casos consiste em dividir a informação em duas tabelas: • informação de cada livro; • autor por livro; Alberto Simões - Dept Informática - Univ. Minho

9. Chaves Estrangeiras • O campo ISBN estabelece a ligação entre as tabelas Livros e Autores. Este campo, na tabela Autores, diz-se que é uma chave estrangeira. • Note-se que não existe obrigatoriedade de utilizar o mesmo nome para ambos os campos ao definir uma ligação entre tabelas. Na tabela de autores, que chave poderíamos escolher? Alberto Simões - Dept Informática - Univ. Minho

10. Divisão de Tabelas (4) • Considerando que se pretende armazenar informação sobre os autores: Mas, John Gardner escreveu uma vasta lista de obras. Vamos repetir toda esta informação para cada um dos livros? Alberto Simões - Dept Informática - Univ. Minho

11. Dependências Funcionais • A solução convencional para este problema consiste em identificar as dependências entre campos: • o campo Nacionalidade depende do campo Nome Autor • o campo Ano Nascimento depende do campo Nome Autor • Ou seja, se soubermos o valor do campo Nome Autor podemos saber o valor dos campos Ano Nascimento e Nacionalidade. Alberto Simões - Dept Informática - Univ. Minho

12. Divisão de Tabelas (5) • Sempre que se verificar a existência de campos numa tabela que não dependam da chave primária, a tabela deve ser dividida; Alberto Simões - Dept Informática - Univ. Minho

13. Resultado Normalizado Em que ano nasceram os autores do Manuel de Access? Alberto Simões - Dept Informática - Univ. Minho

14. Exercício 1 Criar as tabelas necessárias para acomodar os dados de uma factura; E todos os dados da empresa? Alberto Simões - Dept Informática - Univ. Minho

15. Resolução 1.1 • Cada factura contém: • Um número da factura; • Uma data em que foi emitida; • Um cliente que efectuou a compra; • Várias linhas de produtos • Código do produto, descrição, quantidade, preço unitário e valor total; • Um valor total das compras Alberto Simões - Dept Informática - Univ. Minho

16. Resolução 1.2 • Uma vez que não podemos saber quantos produtos vão ser comprados, consideremos apenas os dados que aparecem uma só vez. Alberto Simões - Dept Informática - Univ. Minho

17. Resolução 1.3 • Criemos uma nova tabela para os produtos: • Mas, quem é que efectuou estas compras? Alberto Simões - Dept Informática - Univ. Minho

18. Resolução 1.4 • Para relacionar as compras com o cliente, temos de adicionar uma chave estrangeira a esta tabela: Alberto Simões - Dept Informática - Univ. Minho

19. Resolução 1.5 • O preço de cada produto, e respectiva descrição são dependências funcionais do código do produto; • Devemos dividir a tabela em: • Detalhe de cada linha da factura; • Informação sobre cada produto; Alberto Simões - Dept Informática - Univ. Minho

20. Resolução 1.6 Alberto Simões - Dept Informática - Univ. Minho

21. Resolução 1.7 • Para melhorar o sistema de facturação poderíamos ainda: • Atribuir um código a cada cliente; • Criar uma tabela de clientes, com a sua informação, como a morada, telefone, número de contribuinte; • Cada factura relacionava-se com o cliente apenas pelo seu código Alberto Simões - Dept Informática - Univ. Minho

22. Exercício 2 • Usando o exemplo da factura, vamos criar as seguintes tabelas em Access: Alberto Simões - Dept Informática - Univ. Minho

23. Relacionamentos • Os relacionamentos entre tabelas podem ser de tipos diferentes: • 1 – n • 1 – 1 • m – n • Estes valores correspondem ao número de elementos de uma tabela a que podem corresponder a elementos de outra tabela; Alberto Simões - Dept Informática - Univ. Minho

24. Relacionamentos (1-n) • Uma turma tem n alunos • Neste caso, atabela de relacionamento associa a cada aluno apenas uma turma; • Ou seja, um aluno não pode pertencer a duas turmas; • Neste caso, o relacionamento é feito na tabela alunos, em que se adiciona a chave estrangeira relativa à turma (chave da tabela de turmas). Alberto Simões - Dept Informática - Univ. Minho

25. Relacionamentos (1-1) • Supondo que temos uma tabela de professores e uma outra de departamentos; • Um professor director de um departamento, não o pode ser de um outro; • Cada departamento tem apenas um director. • Nestes casos, pouco frequentes, o relacionamento pode ser feito em qualquer uma das tabelas; • Neste caso em particular, seria mais sensato colocar na tabela de departamentos. Alberto Simões - Dept Informática - Univ. Minho

26. Relacionamentos (m-n) • São os mais frequentes; • Um produto pode aparecer em mais do que uma factura; • Uma factura pode conter mais do que um produto; • Neste caso, constrói-se uma tabela auxiliar que efectua o relacionamento. Alberto Simões - Dept Informática - Univ. Minho

27. Integridade referencial • Chama-se integridade referencial ao processo de garantir que todos os dados de campos relacionados existem em ambas as tabelas; • Por exemplo, existir uma factura com um código de produto que não existe na tabela de produtos; • Uma factura passada a um cliente que não existe na tabela de clientes; Alberto Simões - Dept Informática - Univ. Minho

28. Exercício 3 • Utilizando as tabelas construídas no exercício 2, efectuar os devidos relacionamentos no Microsoft Access. • Verificar o funcionamento do Microsoft Access em relação à integridade referencial; Alberto Simões - Dept Informática - Univ. Minho

29. SQLQuery Language Alberto Manuel Simões (albie@alfarrabio.di.uminho.pt) Alberto Simões - Dept Informática - Univ. Minho

30. Motivação • As bases de dados servem para se poder aceder de forma eficiente aos seus dados; • O uso de uma interface gráfica para a pesquisa nem sempre é eficiente; • Então, desenvolveu-se uma linguagem específica para este tipo de consultas; Alberto Simões - Dept Informática - Univ. Minho

31. Noções • Chamamos querie a uma pergunta à base de dados; • Todas as respostas a queries são tabelas; • A estas tabelas também chamamos de views, porque consistem em vistas sobre porções da informação da base de dados; • As views são tabelas virtuais, ou seja, não são guardadas na base de dados. Alberto Simões - Dept Informática - Univ. Minho

32. Consideremos as seguintes tabelas: • Facturas • Produtos • Facturas / Produtos • Os resultados dos próximos exemplos são retirados desta base de dados. Alberto Simões - Dept Informática - Univ. Minho

33. Operações de Selecção(apenas com uma tabela) • O comando SELECT permite seleccionar determinadas colunas de uma tabela: SELECT <campos> FROM <tabela> ; • Por exemplo, podemos seleccionar os nomes dos produtos com: SELECT Designação FROM Produtos; Alberto Simões - Dept Informática - Univ. Minho

34. Operações de Selecção(apenas com uma tabela - 2) • Podemos pedir mais do que um campo; • Por exemplo, o código e a designação do produto: SELECT Produto, Designação FROM Produtos; Alberto Simões - Dept Informática - Univ. Minho

35. Operações de Selecção(apenas com uma tabela - 3) • Para seleccionar todos os campo, podemos usar um atalho (wildcard): SELECT * FROM Produtos; Alberto Simões - Dept Informática - Univ. Minho

36. Operações de Selecção(explicitando condições na selecção) • É possível especificar condições para refinamento da selecção; • Uma linha só será incluída no resultado se a condição for verificada para essa linha; • As condições são testadas nas linhas da tabela original, i.e. podemos definir condições sobre colunas que fazem parte da tabela original mesmo que essas colunas não apareçam no resultado. Alberto Simões - Dept Informática - Univ. Minho

37. Operações de Selecção(explicitando condições na selecção 2) • Neste caso, usa-se a sintaxe: SELECT <campos> FROM <tabela> WHERE <condição>; • Seleccionar produtos que custem menos de € 2.00: SELECT Descrição FROM Produtos WHERE PreçoUn<2; Alberto Simões - Dept Informática - Univ. Minho

38. Operações de Selecção(explicitando condições na selecção 3) • Podemos escrever condições mais complicadas… SELECT Disciplinas.nome FROM Disciplinas WHERE ano = 1 AND semestre = 0; Alberto Simões - Dept Informática - Univ. Minho