Site under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finishedSite under construction — some sections are still being finished
Back to Projects

Databases

Pousada da Juventude

We refactored the pricing logic and improved it. The formula inside it was inverted, and the refactor carried it through untouched.

Role
Data modelling, T-SQL implementation, report
Team
Project type
Academic project
Context
Databases — BSc Multimedia, IPB EsACT, 2022/2023
Year
2023
Tags
  • SQL Server
  • T-SQL
  • Data Modelling
  • Normalisation
Tools
  • SQL Server 2019 Express
  • SSMS
  • T-SQL

A relational database for a youth hostel: nine tables covering guests, staff, rooms, bookings, payments, invoices, events and attendance, deployed on SQL Server Express. Two working files survive — the hand-written script from partway through, and the script SSMS generated back out of the finished database.

As documented — trigger

Fires on the table it updates. Reads one arbitrary row from `inserted`.

CREATE TRIGGER atualizar_preco_total_reserva
ON reservas
AFTER INSERT, UPDATE
AS
BEGIN
  SELECT @id_reserva = id_reserva FROM inserted;
 
  SELECT @dias_reserva = DATEDIFF(day, data_checkin, data_checkout)
  FROM reservas WHERE id_reserva = @id_reserva;
 
  SET @preco_hospede = @preco_diaria / @numero_hospedes;
 
  UPDATE reservas
  SET preco_total = @dias_reserva * @preco_hospede
  WHERE id_reserva = @id_reserva;
END

As deployed — stored procedure

No recursion, no multi-row assumption. The caller asks for the value.

CREATE PROCEDURE [dbo].[calcular_preco_total_reserva]
  @id_reserva INT,
  @preco_total MONEY OUTPUT
AS
BEGIN
  SELECT @dias_reserva = DATEDIFF(day, data_checkin, data_checkout)
  FROM reservas WHERE id_reserva = @id_reserva;
 
  SELECT @preco_diaria = preco_diaria, @numero_hospedes = r.numero_hospedes
  FROM reservas r
  INNER JOIN quartos q ON r.id_quarto = q.id_quarto
  WHERE r.id_reserva = @id_reserva;
 
  SET @preco_hospede = @preco_diaria / @numero_hospedes;
 
  SET @preco_total = @dias_reserva * @preco_hospede;
END

Quarto 1 — solteiro

50 € per night

As implemented nights × (rate ÷ guests)

Expected nights × rate

The test case from the report: six nights, two guests. The database returns 150 € for a room that costs 50 € a night.

The slider stops at one guest. The code does not: numero_hospedes = 0 divides by zero.

Between the two versions the pricing logic was genuinely improved. The first attempt was an AFTER INSERT, UPDATE trigger that ran an UPDATE on the very table it was triggered by, and pulled its target row with SELECT @id_reserva = id_reserva FROM inserted — which silently picks one arbitrary row the moment anyone inserts two bookings at once. The deployed version is a stored procedure with an OUTPUT parameter instead. No recursion, no multi-row assumption, and the caller decides when the number is wanted. That is a real architectural improvement, and it appears in none of the documentation.

The arithmetic inside it never changed. Both versions divide the nightly rate by the number of guests before multiplying by the number of nights, so a room gets cheaper the more people sleep in it, and a booking with zero guests divides by zero. The mechanism was rewritten twice; the number it produces was never once checked.

The same blind spot, in the data

The seed data shows it too. reservas stores id_hospede and a copy of the guest’s name in nome_hospede. In four rows of invented test data, the copy already disagrees with its own foreign key: guest 2 is Maria de Oliveira in hospedes and Maria Rodrigues in reservas. Nobody noticed, because nothing ever read the two together. pagamentos repeats the pattern — a foreign key to the employee who processed the payment, but the guest and the room stored as loose text with no key at all.

Two things named repeatedly in the requirements are absent from the finished database. There is no table for guest feedback, though it appears in the domain description, the ER diagram, the class diagram and five separate requirement bullets. And the two roles, gerente and recepcionista, exist with no permissions attached — SSMS exports grants when there are grants, and there are none. Role-based access was the single most repeated requirement in the report and it shipped as two empty shells.

The report has its own version of the same gap: it documents the schema in MySQL syntax, with AUTO_INCREMENT and INT(11), while the database that was actually delivered is SQL Server and both surviving scripts are T-SQL. The write-up describes a version that had already been replaced.

What the structure got right

Relational diagram of nine tables — hostel, rooms, guests, staff, bookings, payments, invoices, events and attendance — joined by primary and foreign keys
Nine tables, keys in place, referential integrity enforced. This half of the work was done, and kept getting better.

Everything structural was done, and got better over time: tables, keys, referential integrity, the refactor above. Everything that produces a value was left unverified. Structure is what a diagram shows you. Value only appears when someone runs the thing and checks the answer against a number they worked out by hand first.

Its companion piece is the Banking Management System: both are about which half of a system gets checked — nouns against verbs there, structure against value here.