A lending database for a library: nine tables covering the collection, its borrowers, its staff and the loans between them, on SQL Server. It is the more careful of the two databases I built that year, and the interesting part is where the care ran out.
Three things here were done properly. Loans store identifiers only — id_utente, id_objeto, id_Funcionario — with no copy of anyone’s name sitting beside the key, so the record cannot drift from the row it points at. Access control is real rather than declared: Funcionário is granted read, write and DDL rights, Utente read only, through actual logins mapped to database users. And both halves of the domain are modelled as table-per-subtype hierarchies — Utilizadores splits into Utente and Funcionario, Objeto into Livro, Jornal and Cassete_DVD — which is a real modelling decision rather than a flat table with a type column.
Then the loan table joins back to the wrong level:
FOREIGN KEY (id_utente) REFERENCES Utilizadores(id_utilizador)
FOREIGN KEY (id_Funcionario) REFERENCES Funcionario(id_Funcionario)The second line is correct — it points at the subtype. The first names its column id_utente and then points at the supertype, so the Utente table that was just built sits between the two and constrains nothing. The two keys are three lines apart in the same CREATE TABLE, written by the same hands, in the same sitting. One got it right. Nobody read the other.
The seed data plays it out without any help. Users 1 and 2 are borrowers; users 3 and 4 are staff. The first two loans go to users 1 and 2 and look fine. The third comes in through the stored procedure:
EXEC Requisitar 3, 3, 3, 1, '2023-02-06 14:00:00', '2023-02-07 14:00:00', 2;Borrower 3 is Pedro Martins, who is an employee — and the loan was processed by employee 2, Ana Ribeiro. The database accepted a member of staff borrowing as a patron, on its own test data, because the key was pointed one level too high. Written down, the model forbids it. Executed, it does not.
Availability went missing in the same way. Managing what is on the shelf is the first requirement in the report, and no table has a field for it. What exists is a BIT called estado on the loan, whose meaning is never defined, and a procedure named LivroIndisponivel that returns rows of Requisição rather than of Livro. An object nobody has ever borrowed produces no row at all, so the procedure cannot answer the question it is named after. Availability is a derived fact, and deriving it was left for later.
And Biblioteca itself — the library — is a single row with no foreign key in either direction. That makes three projects in a row: the floating Menu in one class diagram, pousada_juventude connected to nothing, now this. Each time, the entity standing for the organisation gets modelled and never gets attached. It is the easiest thing to draw and the only one nothing depends on.