Multi-lesson final project

Once students have acquired some SQL knowledge, they can develop an original escape-room game as a final project. At this point they already understand the big picture and can approach development systematically. I ran this project before introducing ER models, though ER modelling can readily be incorporated.

In the first phase, students choose a topic that interests them and begin outlining a story. In a “Prison Break” scenario, for example, the protagonist might need to gather information in order to escape. Modelling is central to this phase: which data matters, and how can suitable tables structure it? If ER models have already been covered, students can create one here.

In the second phase, students implement the database and match the story with SQL queries. They can build the database in a tool such as DB Browser for SQLite. Experimentation is expected: ideas may change and relational schemas may need revision. This repeated work with queries develops an understanding of SQL and relational databases along the way. Students then implement the game in the SQL Game Editor and export the result as an XML file.

The third phase begins once the groups have finished: during the final lesson, students use the SQL Game Console to play one another’s games. This tends to be highly motivating. The class has created original, playable products, reflects on its own and others’ work, and takes pride in the results.

Design and create a game that can be used to learn the database query language SQL.

• Game world

  Does the protagonist live in space, under water or somewhere else? Who interacts with whom?

  Design a relational model—a table structure—for the game world. Every piece of data required by the game must have a place in these tables.

• Storyline

  This is the heart of the game. Find a thoughtful way to introduce SQL, beginning with simple queries so that players do not need expert knowledge.

  Your story must be representable as a sequence of scenes. Each scene has one of four types:

  1. Text scene. A dialogue or narration. The player clicks “Continue” to advance.

  2. Image scene. Like a text scene, but displaying an image. The player clicks “Continue” to advance.

  3. Select scene. After reading some text, the player enters an SQL query. Early scenes may demonstrate part or all of the query—for example, “Now enter SELECT * FROM resident.” Later scenes can leave the formulation entirely to the player. Progress is possible only when the query produces the intended result.

  4. Manipulation scene. The player enters an INSERT, UPDATE or DELETE statement. The game also needs a SELECT query that checks whether the manipulation was correct. If an item is meant to be deleted, for example, a query such as SELECT * FROM item can verify which records remain.

  Your story should contain 10 to 15 tasks in which the player independently formulates an SQL query. You may choose which parts of SQL to introduce, but the game must include both joins and aggregation in some form.

Existing SQL games can provide inspiration. Your finished game can later be listed in the browser.

• SQL Island: the original.
• Games made by other students
• Further resources

The following tools are recommended.

• DB Browser for SQLite for creating the database, defining tables, inserting data and testing queries. The finished database can be exported as an *.sql file.

• The SQL Game Editor for creating the game file, which can then be opened in the SQL Game Console.

• ChatGPT or another large language model for help with SQL, generating data and drafting the story, subject to your school’s policies.

• Optional extension: create scene graphics with tools such as DALL·E, Midjourney or Paint.

Submit one game file by the deadline shown in the schedule. This *.xml file contains all game information.

The following weighting can serve as a rough guide:

• 10% Name and teaser
• 30% Relational model: Are the tables well chosen? Are primary- and foreign-key relationships present and correct? Is redundancy avoided? Are suitable records included?
• 50% Story: Do the text and tasks fit together? Are meaningful data queried? Are joins and aggregation included? Do tasks span several levels of difficulty?
• 10% Game file: Does the file run, and does its database fit the tasks?