Technique – Database TRACS
Summary of Technique
Pupils use a paper-based TRACS model to trace the processing of a database query. Pupils annotate the model to indicate the sequence of the operations and the effect of each of the operations on the results of the query. The activity is based on SQL query notional execution.
- The modification of the original TRACS concept to provide DB TRACS was produced during the PLAN C writing week at Piperdam in August 2015 by Matthew Mackie, Brendan McCart and David Proudfoot.
Exercise(s) for Teachers
Ways of Using the Technique in the Classroom
The activity should be introduced as a teacher-led demonstration. The class should already have completed practical work using databases so that the concepts are familiar and the end results can be anticipated. This activity is about the methodology rather than result.
It would be helpful to demonstrate that graphical tools for building a query (Query Designer/Wizard or Query Builder) create (SQL) code that the DBMS executes. Try to show that editing the query in the GUI causes a corresponding change in the code, perhaps even edit the code directly and ask pupils to predict the result. The aim here is to help the pupils appreciate that code is executed rather than to develop SQL coding skill.
It would also be helpful if the pupils have used the paper database machine.
Introduce the TRACS DB system to the class and demonstrate the process to the pupils prompting them to help complete the options. Emphasise the execution sequence.
Follow up by organising the pupils to either work individually and then peer-review, or simply to work in pairs promoting dialogue using technical vocabulary.
Encourage pupils to use the model in the sequence described in the presentation. In the early stages perhaps stop and check that each stage has been completed before moving on to the next.
Prepare the handouts as a double-sided A4 worksheet with two templates per side. Highlighters or coloured pens can help make the annotation more effective.
The sample material uses a holiday flights database, you may wish to amend dates to the current year to avoid the materials appearing dated. (Same may go for resorts!)
Benefits for Learners
Pupils gain an experience in the processes that take place during a database query. They have a concrete view of how the table (or particular rows and columns) are used to produce the answer table. This can help when trouble-shooting a query that appears not to have worked. This experience can provide the basis for peer discussion or pupil-teacher discussion in clarifying understanding or resolving errors. The experience also helps develop the concepts needed to describe a query without a query GUI, gently introducing the notion of a query language such as SQL.
Tips for Creating New Exercises
- Create a table with a good mix of text, number and perhaps data values.
- Keep the number of rows to about a dozen, not too many but enough to be interesting.
- Ensure that some columns have duplicate values to provide opportunities for complex sort.
- Try to use a scenario that is meaningful, purposeful and one that pupils can relate to.
- Create a set of queries that cover simple and comples searches and sorts and also simple row calculations.