Exercícios progressivos sobre o schema real do Alumnus
Pegue as migrations em:
github.com/gustavopinto/alumnus/tree/main/backend/migrations
Rode uma após a outra, em ordem numérica (000_schema.sql, depois 001_…, 002_… e assim por diante). Cada migration evolui o schema a partir do estado anterior — pular ou trocar a ordem quebra o banco.
Atenção: nome, email e ativo vivem em users — não em researchers/professors. Quase toda consulta passa por users.
id, name, domain
id
(só id — nome vem de users)
id, name,
institution_id
id, status, matricula, curso, enrollment_date,
group_id, orientador_id
id, nome, email, role, ativo,
researcher_id, professor_id
CHECK: só um dos dois FKs
id, relation_type,
source_researcher_id,
target_researcher_id
id, text, created_at,
user_id, created_by_id
id, text, due_date, done,
created_by_id, institution_id
id, label, date,
abstract_date,
institution_id, created_by_id
id,
deadline_id, user_id
UNIQUE (deadline_id, user_id)
id, type, title, date,
user_id
id, title, url, status,
user_id
Selecione PostgreSQL no menu superior.
Cole o conteúdo de 000_schema.sql no painel Schema SQL e rode.
Escreva seus próprios INSERTs — pelo menos 3 instituições, 3 grupos, 6 pesquisadores, 2 professores e algumas notas, lembretes e deadlines.
A qualidade dos dados que você inserir define se as consultas vão fazer sentido. Capriche.
1. Liste nome e email de todos os pesquisadores com status = 'doutorado', ordenados por nome. (dica: o nome está em users, ligado por users.researcher_id)
2. Todos os reminders ainda pendentes (done = FALSE), ordenados pela due_date crescente.
3. As 5 deadlines mais próximas que ainda não venceram (use CURRENT_DATE).
4. Para cada pesquisador ativo (users.ativo = TRUE): nome em caixa alta, o ano da enrollment_date e o domínio do e-mail.
5. Quantos pesquisadores existem por status?
6. Quais group_id têm mais de 1 pesquisador? (use HAVING)
7. Nome do pesquisador + nome do grupo de pesquisa a que pertence. (duplo JOIN: researchers → users e researchers → research_groups)
8. Todos os pesquisadores + nome do orientador — incluindo os sem orientador. (o orientador também vem de users, mas via users.professor_id = researchers.orientador_id)
9. Para cada deadline: label e quantos usuários marcaram interesse.
10. Top 3 professores com mais pesquisadores ativos sob sua orientação.
Você escreveu as consultas. Agora olhe para o schema e questione.
user é professor ou pesquisador?A tabela users tem duas FKs: professor_id e researcher_id — só uma delas é preenchida por linha. Que problemas isso pode causar nas consultas e nos INSERTs? Como você redesenharia para evitar o NULL em uma das colunas? Vale criar duas tabelas separadas, ou herança, ou aceitar o trade-off?
usersnotes tem user_id (sobre quem é a nota) e created_by_id (quem escreveu). Em quais das 10 consultas isso atrapalhou? Como você renomearia essas colunas para deixar o papel de cada uma explícito? Esse padrão se repete em reminders e deadlines — você unificaria?
Para responder "quais pesquisadores nunca tiveram nota?" é preciso navegar researchers → users → notes. Se você pudesse mexer no schema, como encurtaria esse caminho? Vale colocar researcher_id direto em notes? O que se ganha em consulta e o que se perde em consistência?