Bases de Dados

Marta Torneiro (ci08021)
Mónica Silva (ci08050)
Patrícia Latourrette(ci05031)
Bases de Dados
Relatório do Trabalho Prático
Docente: Professor Gabriel de Sousa Torcato David
Licenciatura em Ciência da Informação
Maio 2011
1 Sumário Sumário ......................................................................................................................................... 2 Introdução .................................................................................................................................... 3 Descrição do problema ................................................................................................................. 4 Modelo de Classes ........................................................................................................................ 5 Esquema Relacional ...................................................................................................................... 7 Instância Exemplo e Perguntas à Base de Dados .......................................................................... 8 Apresentação de tabelas e formulários exemplificativos da Base de Dados .............................. 21 Conclusão .................................................................................................................................... 26 2 Introdução O presente relatório foi elaborado no âmbito do trabalho prático da
disciplina de Bases de Dados, leccionada no 2º semestre do 3º ano da
Licenciatura em Ciência da Informação.
Este trabalho tem como objectivo constituir uma oportunidade de
experimentação das matérias expostas nas aulas da disciplina, mais
concretamente o modelo relacional, a linguagem SQL e as técnicas de
apresentação de dados.
Neste relatório, são explicitadas todas as fases de desenvolvimento do
trabalho prático: a descrição sucinta do problema e o tema escolhido; o modelo
de classes, com indicação das classes, atributos e domínios, assim como as
suas associações; o esquema relacional; a invenção de uma instância exemplo
e a sua implementação numa base de dados relacional; a elaboração de um
mínimo de 5 perguntas à base de dados em álgebra relacional e em SQL e
respectivos resultados.
Por fim, preparou-se a interface propriamente dita, de forma a ser
perceptível a construção e utilização da mesma.
3 Descrição do problema O trabalho por nós apresentado consiste na construção de uma possível
base de dados para um Salão de Beleza, tendo em conta os clientes, cuja ficha
já existe na nossa Base de Dados, os funcionários, a agenda e as marcações.
O Salão de Beleza por nós criado encontra-se aberto das 9h da manhã
até às 20h da noite, encerrando aos Domingos e cuja organização está
dependente de uma Agenda, onde são registadas todas as Marcações. As
marcações dependem directamente da disponibilidade dos funcionários
(disponibilidade esta apresentada na Agenda do Salão), em termos de hora e
dia e, consequentemente, dos serviços disponíveis no espaço.
Os serviços disponíveis no Salão de Beleza enquadram-se nas funções
dos Funcionários do presente Salão. Assim sendo, os serviços que podem ser
requisitados pelos nossos clientes, fazem parte das funções, pelos nossos
funcionários desempenhadas, e incluem: Cabeleireiro, Massagem, Estética e
Tatuagem.
4 Modelo de Classes O modelo de classes pelo nosso grupo elaborado, teve por base uma
discussão acerca de todos os requisitos e intervenientes essenciais para o
funcionamento do Salão de Beleza.
Assim sendo, optamos por construir 4 tabelas, que passamos a
apresentar em seguida:
•
AGENDA
o Nagenda
o Dia
o Hora_inicio
o No_clientes
o Codf
•
MARCAÇÃO
o Nagenda
o Codc
o Servico
o Hora
•
FUNCIONÁRIO
o Codf
o Nome
o NIF
o Morada
o Cod_postal
o Contacto
o E-mail
o Data_nasce
o Funcao
o Sexo
5 •
CLIENTE
o Codc
o Nome
o Contacto
o Morada
o Email
o Data_nasce
o Cod_postal
o Profissao
o NIF
o Sexo
Apresentamos, seguidamente, o esquema do Modelo de Classes com as
respectivas Chaves assinaladas e Relações estabelecidas:
6 Esquema Relacional Neste ponto, apresentamos o esquema relacional do trabalho
por nós desenvolvido, tendo por base o Modelo de Classes
previamente apresentado.
Assim sendo, o Esquema relacional obtido foi o seguinte:
Funcionário (codf, nome, NIF, morada, cod_postal, contacto, data_nasce,
funcao, sexo)
Cliente(codc, nome, NIF, morada, cod_postal, contacto, data_nasce, profissao,
sexo)
Agenda(nagenda, dia, hora_inicio, no_clientes, codf → Funcionario)
Marcacao(nagenda → Agenda, hora, preco_base, servico, codc → Cliente)
7 Instância Exemplo e Perguntas à Base de Dados Na elaboração do nosso trabalho, tivemos por base o programa
Microsoft Access 2007, com o objectivo de criar uma base de dados bem
estruturada.
Na base de dados do nosso Salão de Beleza, são visualizadas 4
tabelas e as respectivas relações, as questões à Base de Dados por nós
criadas quer em linguagem SQL, quer em Álgebra Relacional.
Assim, serão devidamente apresentados os resultados obtidos, bem
como os formulários respectivos a cada uma das questões, previamente
seleccionadas.
8 1.
Qual o número de marcações e dinheiro total gasto, por cada
cliente do Salão de Beleza? (ordenados de forma ascendente pelo número de
marcações)
Linguagem SQL:
SELECT cliente.codc, nome, COUNT(marcacao.codc) AS nummarcacoes, SUM(marcacao.preco_base) AS total FROM cliente LEFT JOIN marcacao ON marcacao.codc=cliente.codc GROUP BY cliente.codc, nome ORDER BY 3; Álgebra Relacional:
τ 3 (πcodc, nome, nummarcacoes=CNT(*), total=SUM(preco_base) (C ⋈ M)) Resultado obtido, esquematicamente:
Formulário obtido:
9 2. Quais os dados de todos os clientes do sexo Feminino?
Linguagem SQL:
SELECT * FROM cliente WHERE sexo='F'; Álgebra Relacional:
σ Sexo = ‘F’ (C) Resultado obtido, esquematicamente:
Formulário obtido:
10 3. Quais os dados de todos os clientes do sexo Masculino?
Linguagem SQL:
SELECT * FROM cliente WHERE sexo='M'; Álgebra Relacional:
σ Sexo = ‘M’ (C) Resultado obtido, esquematicamente:
Formulário obtido:
11 4. Indicar o dia, a hora, o código do Funcionário e o serviço em que a hora
da marcação coincide com o início do período de marcações da agenda.
Linguagem SQL:
SELECT Agenda.dia, Marcacao.hora, Agenda.codf, Marcacao.servico FROM Marcacao, Agenda WHERE Marcacao.nagenda=Agenda.nagenda AND Marcacao.hora=Agenda.hora_inicio; Álgebra Relacional:
πdia, hora, codf, servico(M ⋈ M.nagenda=A.nagenda ⋀ M.hora=A.hora_inicio A) Resultado obtido, esquematicamente:
Formulário obtido:
12 5. Quais as moradas (rua e código-postal) existentes na Base de Dados?
Linguagem SQL:
SELECT morada, cod_postal FROM Cliente UNION SELECT morada, cod_postal FROM Funcionario; Álgebra Relacional:
πmorada, cod_postal (C) ⋈ πmorada, cod_postal (F) Resultado obtido, esquematicamente:
Formulário obtido:
13 6. Quais os nomes e respectivas funções de todos os funcionários do
Salão de Beleza? (Ordenados Alfabeticamente por nome)
Linguagem SQL:
SELECT nome, funcao FROM Funcionario ORDER BY nome; Álgebra Relacional:
τ nome (π nome, funcao (F))
Resultado obtido, esquematicamente:
Formulário obtido:
14 7. Quais os nomes dos Funcionários do Salão de Beleza, cujos nomes se
encontram entre “David Guetta” e “Katy Perry”? (Ordenados
alfabeticamente por nome)
Linguagem SQL:
SELECT * FROM funcionario WHERE nome Between 'David Guetta' And 'Katy Perry'; Álgebra Relacional:
σ ‘David Guetta’ < nome > ‘Katy Perry’ Resultado obtido, esquematicamente:
Formulário obtido:
15 8. Qual o número de marcações para cada Funcionário e o total de
dinheiro recebido? (Ordenado de forma descendente por número de
marcações)
Linguagem SQL:
SELECT Funcionario.codf, nome, COUNT(Marcacao.codc) AS nummarcacoes, SUM(Marcacao.preco_base) AS total FROM (Funcionario INNER JOIN Agenda ON Agenda.codf=Funcionario.codf) INNER JOIN Marcacao ON Marcacao.nagenda=Agenda.nagenda GROUP BY Funcionario.codf, nome UNION SELECT Funcionario.codf, nome, 0 AS nummarcacoes, 0 AS total FROM Funcionario WHERE Funcionario.codf NOT IN ( SELECT Agenda.codf FROM Agenda ) ORDER BY nummarcacoes DESC; Álgebra Relacional:
τ nummarcacoes desc (π codf, nome, nummarcacoes=CNT(*), total=SUM(preco_base) (F ⋈ A ⋈ M)) Resultado obtido, esquematicamente:
16 Formulário obtido:
17 9. Quais os serviços e preços correspondentes prestados pelos
funcionários do Salão de Beleza? (Ordenados de forma ascendente)
Linguagem SQL:
SELECT DISTINCT servico, preco_base AS preco FROM funcionario, agenda, marcacao WHERE funcionario.codf=agenda.codf AND agenda.nagenda=marcacao.nagenda ORDER BY preco_base; Álgebra Relacional:
δ (π servico, preco (F F.codf=A.codf ⋀ A.nagenda=M.nagenda ⋈A ⋈ M)) Resultado obtido, esquematicamente:
Formulário obtido:
18 10. Qual o serviço mais caro e o preço respectivo?
Linguagem SQL:
SELECT servico, AVG(preco_base) AS preco FROM funcionario, agenda, marcacao WHERE funcionario.codf=agenda.codf AND agenda.nagenda=marcacao.nagenda GROUP BY servico HAVING AVG(preco_base)=(SELECT MAX(preco) FROM Preços_ordem_ascendente); Álgebra Relacional:
P = π servico, preco=AVG(preco_base) (F ⋈ A ⋈ M) PMAX = π max_preco=MAX(preco)(P) π servico, max_preco (P ⋈ preco=max_preco PMAX) Resultado obtido, esquematicamente:
Formulário obtido:
19 11. Quais os nomes e contactos existentes na Base de Dados? (Ordenados
alfabeticamente por nome)
Linguagem SQL:
SELECT nome, contacto FROM cliente UNION SELECT nome, contacto FROM funcionario ORDER BY nome; Álgebra Relacional:
τ nome (π nome, contacto (C)) U (π nome, contacto (F)) Resultado obtido, esquematicamente:
Formulário obtido:
20 Apresentação de tabelas e formulários exemplificativos da Base de Dados Neste ponto, temos o intuito de apresentar as 4 tabelas, bem como exemplos de formulários relativos a cada componente da Base de Dados do nosso Salão de Beleza. •
Tabela “Agenda” •
Tabela “Marcação” 21 •
Tabela “Funcionários” •
Tabela “Clientes” 22 •
Exemplo de Formulário “Agenda” •
Exemplo de Formulário “Marcação” 23 •
Exemplo de Formulário “Clientes” •
Exemplo e Formulário “Funcionários” 24 •
Exemplo de Formulário “Menú” •
Exemplo de Formulário “Queries” 25 Conclusão Após a realização deste trabalho pudemos aprofundar os
componentes teóricos e práticos da unidade curricular de Base de Dados. As
principais dificuldades centraram-se na elaboração das queries.
No que diz respeito à componente visual (aspecto) da Base de Dados,
tentamos desenvolver algo simples e com um ar “fresco” e, principalmente,
funcional, sem descuidar o fim para o qual esta se destina.
Desenvolvemos alguns botões dinâmicos, simples e que permitem um
fácil acesso, aquando da navegação na nossa Base de Dados.
Assim sendo ficamos mais familiarizados com o uso de SGBD
comerciais, mais concretamente o MS Access e aplicamos os nossos
conhecimentos no que refere à linguagem SQL e Álgebra Relacional.
26