Trabalho 1
Bancos de Dados
Prof. Sérgio Lisfchitz
Bernard Lupiac
Gabriel Barsi
Ilan Gryzspan
Jordana Mecler
Luiz Helt
Maria Carolina Marinho
Texto para projeto
Necessidades da equipe RioBotz
Necessita-se fazer um banco de dados para a equipe de robótica da PUC-Rio, a
RioBotz. A equipe é composta de alunos de diversos cursos, professores e robôs. A
equipe vai a diversas competições, onde se deseja fazer um registro geral, tanto para
controle, quanto para previsão.
Os membros são sempre alunos, obrigatoriamente, podendo ser de graduação,
mestrado, pós-graduação e doutorado. Existem três áreas principais de atuação, são elas:
mecânica, eletrônica, marketing/comunicação visual. Essas podem possuir diversos
alunos, e um aluno sempre participa de uma área. Toda área possui um aluno como
coordenador, que coordena somente aquela área. Sobre os alunos, devem-se armazenar
nome, matrícula, data de ingresso na equipe, nota na prova do processo seletivo, área,
curso, CPF, RG, data de nascimento, número do passaporte, data de vencimento do
passaporte e vencimento do VISA americano (podendo ser uma data ou vencido). Esses
dados são importantes para organização e compra de passagens. A equipe possui um
professor orientador. Qualquer membro pode participar de diversas competições.
Os robôs devem ser identificados pelo nome, e possuem categoria de peso, peso
na configuração mais pesada, peso na configuração mais leve, competições que
participou, piloto oficial (aluno), um link para armazenamento remoto dos arquivos
CAD, número de medalhas, divididas em ouro, prata e bronze, e se está aprovado ou
não para inspeção dinâmica e estática. Dois robôs na mesma categoria não podem
possuir o mesmo piloto, isso impossibilita sua inscrição.
Os professores podem ser de dois tipos, ou orientador ou contribuinte. Sobre o
professor deve-se saber seu nome, CPF, RG, data de nascimento, número do passaporte,
data de vencimento do passaporte e vencimento do VISA americano (podendo ser uma
data ou vencido).
As competições necessitam de diversos dados para inscrição, e precisa-se ter um
controle sobre as ocorrências. Múltiplas competições ocorrem num mesmo ano, e a cada
ano as mesmas competições ocorrem portanto é de praxe da equipe identificar cada
competição por seu nome e respectivo ano, por exemplo "RoboGames 2015".
Para a competição, deve-se ter uma lista de membros (pelo menos um) que irão
comparecer, professores (pelo menos um orientador) para aquela competição, robôs que
irão competir , taxa de inscrição por robô e por membro, custo do transporte, companhia
de transporte, data de saída da PUC, data de chegada na PUC, data de início da
competição, data de término da competição, custo por pessoa do hotel, nome, número e
endereço do hotel, status de pagamento por integrante da hospedagem, endereço do
local do evento, medalhas adquiridas na competição, itens perdidos durante a
competição devido a danos ou outras fatalidades por robô e número de malas.
Toda competição tem um robô participando, porém um robô não
necessariamente participa de todas as competições. Um robô participante pode obter
uma medalha na competição, e essa só é obtida por ele.
E-R
Lista de Atributos
Área
Nome (Nome da área de atuação, palavra com até 20 caracteres)
Aluno
Matrícula (Matrícula do aluno, número com 7 dígitos)
Nome (Nome do aluno, palavra com até 40 caracteres)
Tipo (Tipo de formação a qual o aluno pertence, palavra de até 20 caracteres)
DataIngres (Data de ingresso do aluno na RioBotz, no formato dd/mm/aaaa)
Nota (Nota do aluno no processo seletivo, número de 0 a 10)
Curso (Curso do aluno, palavra com até 20 caracteres)
CPF (CPF do aluno, número com 11 dígitos)
RG (RG do aluno, número com 9 dígitos)
DataNasc (Data de nascimento do aluno, no formato dd/mm/aaaa)
NumPass (Número do passaporte do aluno, número com 8 dígitos)
VencPass (Data de vencimento do passaporte do aluno, no formato dd/mm/aaaa)
VencVISA (Data de vencimento do visto do aluno, no formato dd/mm/aaaa)
Professor
CPF (CPF do professor, número com 11 dígitos)
Nome (Nome do professor, palavra com até 40 caracteres)
RG (RG do professor, número com 9 dígitos)
DataNasc (Dara de nascimento do professor, no formato dd/mm/aaaa)
NumPass (Número do passaporte do professor, número com 8 dígitos)
VencPass (Data de vencimento do passaporte do professor, no formato dd/mm/aaaa)
VencVISA (Data de vencimento do visto do professor, no formato dd/mm/aaaa)
Robô
Nome (Nome do robô, palavra com até 20 caracteres)
CatPeso (Categoria de peso do robô, palavra com até 20 caracteres)
PesoConfPesado (Peso do robô na configuração mais pesada, número de 0 a 200)
PesoConfLeve (Peso do robô na configuração mais leve, número de 0 a 200)
LinkCAD (Link referente ao robô, palavra com até 80 caracteres)
Aprovação (Aprovação do robô na inspeção, palavra com até 10 caracteres)
Competição
Nome (Nome da competição, palavra com até 20 caracteres)
Ano (Ano em que a competição foi realizada, número com 4 dígitos)
EndEvento (Endereço da competição, palavra com até 40 caracteres)
DataIniComp (Data de início da competição, no formato dd/mm/aaaa)
DataFimComp (Data de encerramento da competição, no formato dd/mm/aaaa)
Medalha
Tipo (Tipo da medalha, palavra sendo ouro, prata ou bronze)
Compete
DataSaidaPUC (Data de partida da PUC para participar da competição, no formato
dd/mm/aaaa)
DataChegPUC (Data de chegada da PUC para participar da competição, no formato
dd/mm/aaaa)
NumMalas (Número de malas levadas, número de 0 a 10)
CustoPessHotel (Custo de hospedagem no hotel por pessoa participante da competição,
número de 0 a 1000)
NomeHotel (Nome do hotel que se hospedarem durante a competição, palavra com até
20 caracteres)
EndereçoHotel (Endereço do hotel que se hospedarem durante a competição, palavra
com até 100 caracteres)
StatusDePag (Status do pagamento do hotel, palavra com até 10 caracteres)
TaxaInscriçãoMembro (preço cobrado pela participação do membro na competição,
número de 0 a 500)
PreçoTransporte (custo de ônibus ou avião para ir e voltar da competição, número de 0 a
5000)
CompanhiaTransporte (nome da companhia de ônibus ou avião, palavra com até 20
caracteres)
Participa
ItensPerdidos (Itens perdidos na competição, palavra com até 50 caracteres)
TaxaInscriçãoRobo (preço cobrado pela participação do robô na competição, número de
0 a 500)
Vai
DataSaidaPUC (Data de partida da PUC para participar da competição, no formato
dd/mm/aaaa)
DataChegPUC (Data de chegada da PUC para participar da competição, no formato
dd/mm/aaaa)
NumMalas (Número de malas levadas, número de 0 a 10)
CustoPessHotel (Custo de hospedagem no hotel por pessoa participante da competição,
número de 0 a 1000)
NomeHotel (Nome do hotel que se hospedarem durante a competição, palavra com até
20 caracteres)
EndereçoHotel (Endereço do hotel que se hospedarem durante a competição, palavra
com até 100 caracteres)
StatusDePag (Status do pagamento do hotel, palavra com até 10 caracteres)
PreçoTransporte (custo de ônibus ou avião para ir e voltar da competição, número de 0 a
5000)
CompanhiaTransporte (nome da companhia de ônibus ou avião, palavra com até 20
caracteres)
Modelo Relacional
PK
FK
Aluno( Nome, Matrícula, Tipo, DataIngresso, Nota, NomeÁrea, Curso, CPF, RG,
DataNasc, NumPassaporte, VencPassaporte, VencVISA )
PK
Professor( Nome, CPF, RG, DataNasc, NumPassaporte, VencPassaporte, VencVisa )
PK
FK
Área( Nome, MatriculaCoordenador)
PK
FK
Robô( Nome, CatPeso, PesoPesado, PesoLeve, LinkCAD, Aprovação, MatrículaPiloto)
FK
FK
FK
Medalha( TipoMedalha, NomeRobô, NomeCompeticao, AnoCompetição )
|__________________________________________________|
PK
PK
PK
Competição( Nome, Ano, EndEvento, DataIniComp, DataFimComp )
Compete( DataSaidaPUC, DataChegPUC, NumMalas, CustoPessHotel, NomeHotel,
EndereçoHotel, StatusDePag, TaxaInscriçãoMembro, PrecoTransporte,
CompanhiaTransporte, Matrícula, NomeCompeticao, AnoCompetição, )
FK
FK
FK
|___________________________________|
PK
Vai( DataSaidaPUC, DataChegPUC, NumMalas, CustoPessHotel, NomeHotel,
EndereçoHotel, StatusDePag, PrecoTransporte, CompanhiaTransporte,
CPFProf, NomeCompeticao, AnoCompeticao)
FK
FK
FK
|___________________________________|
PK
Participa( ItensPerdidos, TaxaInscricaoRobo,
NomeRobô, NomeCompeticao, AnoCompeticao)
FK
FK
FK
|____________________________________|
PK
Regras semânticas:
“Em uma categoria, dois ou mais robôs não podem ser pilotados pelo mesmo piloto”
“Deve haver no mínimo um professor orientador em uma competição”
Todas as tabelas foram criadas de acordo com as melhores alternativas
propostas, com exceção do relacionamento aluno - coordena - área, pois como no
relacionamento aluno - participa - área já haviam sido adicionadas colunas a tabela pela
regra, optamos pela segunda melhor alternativa (adicionar colunas a tabela).
Consultas de Álgebra Relacional
1) Listar os nomes dos membros que participaram da competição “Winter Challenge” em 2010.
OBS: |X| é um join
(𝐶𝑜𝑚𝑝𝑒𝑡𝑖çã𝑜)
𝑅1 ← 𝜎𝑛𝑜𝑚𝑒="Winter challenge" ∧
ano="2010"
𝑅2 ← 𝑅1 |𝑋| 𝐶𝑜𝑚𝑝𝑒𝑡𝑒 (𝑁𝑜𝑚𝑒=𝑁𝑜𝑚𝑒𝐶𝑜𝑚𝑝𝑒𝑡𝑖çã𝑜)
(𝑅2)
𝑎𝑢𝑥 ← 𝛿𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎 →𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎𝐴𝑙𝑢
∧ 𝑁𝑜𝑚𝑒 →𝑁𝑜𝑚𝑒𝑎𝑙𝑢
𝑅3 ← 𝑎𝑢𝑥 |𝑋| 𝐴𝑙𝑢𝑛𝑜 (𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎=𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎𝐴𝑙𝑢)
(𝑅3)
𝑅𝑒𝑠𝑝 ← Π𝑁𝑜𝑚𝑒_𝑎𝑙𝑢
2) Listar o nome dos alunos que nunca participaram de nenhuma competição.
𝑅1 ← 𝐴𝑙𝑢𝑛𝑜 |𝑋| 𝐶𝑜𝑚𝑝𝑒𝑡𝑒
(𝐴𝑙𝑢𝑛𝑜)
𝑅2 ← Π𝑁𝑜𝑚𝑒
(𝑅1)
− Π𝑁𝑜𝑚𝑒
3) Qual foi a taxa de inscrição mais cara paga por um aluno?
(𝐶𝑜𝑚𝑝𝑒𝑡𝑒)
𝐶𝑜𝑚𝑝𝑀𝑎𝑖𝑠𝐶𝑎𝑟𝑎 ← 𝑔 max(𝑡𝑎𝑥𝑎𝐼𝑛𝑠𝑐𝑟𝑖çã𝑜𝑀𝑒𝑚𝑏𝑟𝑜)
4) Listar a data de vencimento do VISA de alunos e de professores.
(𝑃𝑟𝑜𝑓𝑒𝑠𝑠𝑜𝑟)
𝐷𝑎𝑡𝑎𝑉𝑒𝑛𝑐𝑃𝑟𝑜𝑓𝑒𝑠𝑠𝑜𝑟 ← Π𝑉𝑒𝑛𝑐𝑉𝑖𝑠𝑎
(𝐴𝑙𝑢𝑛𝑜)
𝐷𝑎𝑡𝑎𝑉𝑒𝑛𝑐𝐴𝑙𝑢𝑛𝑜 ← Π𝑉𝑒𝑛𝑐𝑉𝑖𝑠𝑎
𝑅𝑒𝑠𝑝 ← 𝐷𝑎𝑡𝑎𝑉𝑒𝑛𝑐𝑃𝑟𝑜𝑓𝑒𝑠𝑠𝑜𝑟 ∪ 𝐷𝑎𝑡𝑎𝑉𝑒𝑛𝑐𝐴𝑙𝑢𝑛𝑜
5) Listar todos os robôs que obtiveram medalha de “bronze” nas competições “Winter Challenge”
ou “Robo Games”, em 2014 ou em 2015.
(𝐶𝑜𝑚𝑝𝑒𝑡𝑖𝑐𝑎𝑜)
𝑎𝑢𝑥𝑁𝑜𝑚𝑒 ← 𝜎(𝑁𝑜𝑚𝑒 ="Winter Challenge" V Nome = "Robo Games")
(𝑎𝑢𝑥𝑁𝑜𝑚𝑒)
𝑎𝑢𝑥𝑁𝑜𝑚𝑒𝐴𝑛𝑜 ← 𝜎(𝐴𝑛𝑜 = 2014 V 𝐴𝑛𝑜=2015)
(𝑎𝑢𝑥𝑁𝑜𝑚𝑒𝐴𝑛𝑜)
𝑎𝑢𝑥𝑃𝑟𝑜𝑗 ← 𝜋𝑁𝑜𝑚𝑒,𝐴𝑛𝑜
𝑎𝑢𝑥𝑀𝑒𝑑𝑎𝑙𝑆𝑒𝑙𝑒𝑐𝑡 ← 𝑀𝑒𝑑𝑎𝑙ℎ𝑎 ÷ 𝑎𝑢𝑥𝑃𝑟𝑜𝑗
(𝑎𝑢𝑥𝑀𝑒𝑑𝑎𝑙𝑆𝑒𝑙𝑒𝑐𝑡)
𝑎𝑢𝑥𝐵𝑟𝑜𝑛𝑧𝑒𝑆𝑒𝑙𝑒𝑐𝑡 ← 𝜎𝑇𝑖𝑝𝑜𝑀𝑒𝑑𝑎𝑙ℎ𝑎="𝑏𝑟𝑜𝑛𝑧𝑒"
6) Obter a maior nota inferior a 7.0 no processo seletivo.
(𝐴𝑙𝑢𝑛𝑜)
𝑎𝑢𝑥𝑃𝑟𝑜𝑗 ← 𝜋𝑁𝑜𝑡𝑎 < 7.0
𝑎𝑢𝑥𝑃𝑟𝑜𝑗
𝑅𝑒𝑠𝑝 ← 𝑔𝑚𝑎𝑥 (𝑁𝑜𝑡𝑎)
7) Listar os nomes e números de passaporte de todos os alunos que foram à competição “Robo
Games” em 2015.
(𝐶𝑜𝑚𝑝𝑒𝑡𝑒)
𝑎𝑢𝑥𝐶𝑜𝑚𝑝 ← 𝜎𝑁𝑜𝑚𝑒𝐶𝑜𝑚𝑝𝑒𝑡𝑖çã𝑜="Robo Games" ∧ AnoCompetição=2015
(𝐴𝑙𝑢𝑛𝑜)
𝑎𝑢𝑥𝑀𝑎𝑡𝑟 ← 𝜋𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎
(𝑎𝑢𝑥𝐶𝑜𝑚𝑝)
𝑎𝑢𝑥𝐼𝑛𝑡𝑒𝑟𝑠𝑒𝑐𝑡 ← 𝑎𝑢𝑥𝑀𝑎𝑡𝑟 ∩ 𝜋𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎
𝑅1 ← 𝑎𝑢𝑥𝐼𝑛𝑡𝑒𝑟𝑠𝑒𝑐𝑡 |𝑋| 𝐴𝑙𝑢𝑛𝑜
(𝑅1)
𝑅𝑒𝑠𝑝 ← 𝜋𝑁𝑜𝑚𝑒,𝑁𝑢𝑚𝑃𝑎𝑠𝑠𝑎𝑝𝑜𝑟𝑡𝑒
8) Liste o nome de todos os alunos e os nomes dos robôs que estes pilotam, se aplicável.
OBS: =|X| é um outer join aberto a esquerda
(𝑅𝑜𝑏𝑜)
𝑅1 ← 𝐴𝑙𝑢𝑛𝑜 = |𝑋| 𝛿𝑁𝑜𝑚𝑒 →𝑁𝑜𝑚𝑒𝑅𝑜𝑏𝑜 (𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎=𝑀𝑎𝑡𝑟𝑖𝑐𝑢𝑙𝑎𝑃𝑖𝑙𝑜𝑡𝑜)
(𝑅1)
𝑅𝑒𝑠𝑝 ← Π𝑁𝑜𝑚𝑒
Consultas em SQL
1) Listar o nome de cada aluno.
SELECT nome
FROM aluno
2) Listar todas as informações sobre alunos cujo nome contém "ilão".
SELECT *
FROM aluno
WHERE nome like '%ilão%'
3) Mostrar o nome de alunos que fazem parte da área 'eletronica' e que
participaram da competição 'winter challenge' do ano 2015.
SELECT nome
FROM aluno
WHERE area = 'eletronica'
INTERSECT
SELECT aluno.nome
FROM aluno INNER NATURAL JOIN compete
WHERE compete.nomeCompeticao = 'winter challenge'
AND compete.ano = '2015'
4) Mostrar o nome de todos os alunos cujo CPF está ausente da base de
dados.
SELECT nome
FROM aluno
WHERE cpf IS NULL
5) Mostre o nome de todos os alunos que são coordenadores de
departamento e pilotam um robô.
SELECT nome
FROM aluno
WHERE matricula IN
(SELECT matriculaAluno
FROM robo)
AND matricula IN
(SELECT matriculaCoordenador
FROM area)
6) Listar os nomes de todos os robôs com configuração de peso mais leve
inferior à do robô chamado 'Touro Light'.
SELECT DISTINCT T.nomeRobo
FROM robo as T, robo as S
WHERE T.pesoLeve < S.pesoLeve
AND S.nomeRobo = 'Touro Light'
7) Listar os nomes de todos os robôs cuja configuração mais pesada é
superior à de todos os robôs que participaram na competição “ENECA" de
2010.
SELECT DISTINCT nomeRobo
FROM robo
WHERE pesoPesado > all
(SELECT pesoPesado
FROM robo INNER JOIN participa ON
robo.nome = participa.nomeRobo
WHERE participa.nomeCompeticao = 'ENECA'
AND participa.AnoCompeticao = 2010)
8) Listar o nome de todos os robôs que ainda não ganharam nenhuma
medalha
SELECT nome
FROM robos
WHERE not exists
(SELECT *
FROM medalha
WHERE robos.nome = medalha.nomeRobo)
9) Listar o nome dos robôs e seus pilotos.
SELECT robo.nomeRobo, aluno.nome
FROM aluno INNER JOIN robo ON
robo.matriculaAluno = aluno.matricula
10) Listar todos os nomes de robôs e tipos de medalhas ganhas, se aplicável.
SELECT robo.nomeRobo, medalha.tipoMedalha
FROM robo LEFT OUTER JOIN medalha ON
robo.nome = medalha.nomeRobo
11) Listar o nome de todos os alunos que tem o mesmo nome que o robô que
eles pilotam.
SELECT aluno.nome
FROM aluno
WHERE aluno.nome IN
(SELECT nome
FROM robo
WHERE robo.matriculaPiloto = aluno.matricula
AND robo.nome = aluno.nome)
12) Listar o nome e endereço de todas as competições que aconteceram de
2008 até 2012.
SELECT nome, endEvento
FROM competição
WHERE ano IN (2008, 2009, 2010, 2011, 2012)
13) Mostrar o nome competições ordenados pelo ano em que aconteceram.
SELECT nome
FROM competicao
ORDER BY ano
14) Mostar o peso médio de todos os robôs da categoria de peso 'feather' em
sua configuração mais pesada.
SELECT AVG(PesoPesado)
FROM robo