The Smartest Guy in Westeros
The Citadel Library
There is the largest library in Westeros where Maesters can borrow issues of their favorite scientific magazines to read. Archmaester Ebrose is highly interested in the following questions.
- Who doesn’t return magazine issues back?
- What magazine is the most popular?
He wants us to define a database schema with all the relations satisfying 3NF and answer the questions.
Winter is Coming
The first thing to begin with is to determine entities out of the problem description and their relations between each other. Moreover, we need to know attributes of each entity to operate upon.
We have the Citadel full of Maesters, who may want to use the library, i.e. to become a user. We certainly are to track the readers and to know some basic things about them, e.g. their names. No surprise, the corresponding relation may look like that.
Lets fill the table with some values.
Sometimes Maesters can walk into the library and take one or more scientific magazines to read.
Lets add a couple of popular magazines to the library.
Magazines have concrete issues. For example, the library may have 50 copies of the magazine’s April issue. Issues may be distinguished by one or more specific attributes, i.g. isbn number.
Lets fill it with values.
Maesters can borrow issues to read. Sad but true, some readers may not bring borrowed issues back. We want to track borrowed and returned issues in order to understand what we currently have in the library.
Time goes by, and we have people coming to the library to take something to read.
So far, we have the following schema of library database. All the relations satisfy 3NF.
When You Play a Game of Thrones You Win or You Die
Now using defined schema lets try to answer Archmaester Ebrose questions.
Who doesn’t return magazine issues back?
The first thought coming to mind is to find the latest operation upon the issue made by user and check the type of that operation.
Using that query we can find that Bran, Sansa and Sam haven’t returned borrowed issues yet.
Another approach is based on using two joins instead of subquery.
The result is the same.
And what magazine is the most popular?
We can find the issues which were booked sometime in the history. Then match the issues with the magazines and sort the result by number of bookings.
But this looks a little bit ugly and performs not so well. The thoughts may lead us to another pattern with no subqueries but with joins.
If I Look Back I am Lost
Sam didn’t fall into a pattern of servitude at the Citadel, under the tutorial of Archmaester Ebrose, but healed greyscale of Jorah Mormont and discovered dragonglass on Dragonstone island. He did it, because he is the most talented reader in the Citadel library!
Samwell Tarly, the honest Maesters ever, was feeling guilty and told Daenerys and Jorah Mormont that he had “borrowed” some books from the Citadel.
The problem is that now the librarians know it too.