What email address or phone number would you like to use to sign in to Docs.com?
If you already have an account that you use with Office or other Microsoft services, enter it here.
Or sign in with:
Signing in allows you to download and like content, and it provides the authors analytical data about your interactions with their content.
Embed code for: DB TRACSnotional machine examples - single table
Select a size
A N4/N5 Database version of TRACS
Notional Machine concept
Most PLAN C-ers have acknowledged that
developing the students' understanding of what is happening underneath the language has been a big step forward
most/all of the class now getting it, instead of the more usual half!
This is TRACS and also code explaining
Reminder on what TRACS has…
Analysing the program
Pick out the expressions (as they're "different" to other parts of code)
Write on the static control flow lines through the program
Demonstrate the behaviour of the program at run-time
Use step numbers on the control flow arrows so the run-time trace through the program is captured
Keep a trace table of the variables and how the associated values changes
Expression evaluation area (used by some, not all!)
Input and output areas
Used in two ways
For the teacher to present a single consistent model to the students
e.g. via powerpoint presentation
For the students to demonstrate that they have the right model
e.g. on paper, as homework, or in class to form basis for discussion with other students
So… why not a TRACS for Databases?
Are there equivalents between PL and DB?
you don't read a query top-to-bottom, left-to-right
so there is an execution order to learn
Processing of stored data
just like the trace table, there is a collection of data to be manipulated
Let's explore a simple example
Again, as with TRACS
this could be on paper
this is the next slide
or delivered via animation
this is the following slide – showing how to use the format
Starting point: A query and the database it's going to work on
Airport, Resort, Departs, Price
Price > 300
The green-boxed numbers in left pane give execution order, so pick our table
first – by ticking it in the database
Second part is to identify which rows/records to keep
Finally, output the results
Third part is to select which parts of the records to output
The rest of the presentation
A series of query examples
starting point – could be printed for students to complete, and
an animation, to show the expected execution
These are of steadily increasing complexity
At least two at each level
so could demonstrate, then ask pupils to do one
print some out
demonstrate one, try one in the group, discuss
(Airport = Stansted AND
Price < 300)
Airport = Stansted AND Price < 300
Airport, Departs, Resort
(Airport = Luton AND
Price < 280)
Airport = Luton AND Price < 280
(Airport = Luton OR
Airport = Stansted)
Airport = Luton OR Airport = Standsted
(Airport = Gatwick AND
Price > 250 AND
Price < 300 ) OR
Resort = Zante
( Airport = Gatwick AND
Price > 250 AND
Price < 300 )
Airport = Gatwick
Airport = Luton
Resort, Departs, Price
Resort = Corfu
Price ASC, Departs ASC
Resort, Price, Sale as Price-50
350 - 50
Price - 50
300 - 50
280 - 50
250 - 50
Resort, Price, Sale as Price*0.5
Price * 0.5
300 * 0.5
280 * 0.5
Will it be helpful?
When would you use?
Any suggestions for improvement?
Developing understanding of notional machine proven useful in programming
This is an equivalent for databases
can be used by teacher to introduce a good notional machine model for the learners
or by the learners to show that they have the right model
Welcome to this presentation on the database version of TRACS!
TRACS may seem an obvious technique to use with languages such as VB, Python, Java and so on.
In contrast students may see a database package such as MS Access simply as an application and so processing a query is simply using a feature of that application.
The GUI for packages such as Access, although not trivial to use, do help create queries but perhaps they tend NOT to help the student to develop an understanding of what processes take place to produce the answer table.
This presentation includes animated annotation of the DB TRACS model and this could be used by the teacher to demonstrate the concepts.
However there are additional materials with further queries and blank DB TRACS models for printing on paper so that students can try the DB TRACS activity for themselves.
Developers familiar with SQL will know that queries are expressed by a code statements, in fact the query builder GUI in applications, including Access, generate SQL code which is then executed to produce the answer table. Again it is often possible to use the applications in code mode or view the generated code.
SQL execution follows a particular sequence (at least notionally) and an appreciation of this execution sequence can provide learners with insights into the query process that will help them.
TRACS for databases then leverages the SQL execution model to create practical activities for learners.
Lets look at the elements in the DB TRACS model. This example is about holiday flights.
The data table is pre-populated with values. The table is shown as are column titles. There are check boxes which will be used to annotate the execution of a query.
The index grid on the left and the include check boxes on the right will also be used to annotate query execution.
Below the data table is a blank screen display. This is for showing the answer table once the query processes are complete. The distinction between the original data table and and the results is important. The original data never changes.
Top left is a reminder of the notional execution sequence of an SQL query. Note that an SQL query is not written in this order. The execution sequence is important in understanding the results of a query.
Middle left is for the student to write down the query, usually the student will be supplied with a description of the query and the SQL code. The DB TRACS activity is NOT about the pupils working out SQL for a query, it is about appreciating the execution process. In this example the SQL query has already been filled in, normally this would only have the SELECT, FROM, WHERE keywords to begin with. Later examples will have ORDER BY.
Bottom left is an expression evaluation box for logic or arithmetic operations.
Later an amendment to deal with row calculations or derived data will be included. It is not shown here simply to keep this introduction simpler.
Let’s take this first example and apply the TRACS technique.
The FROM clause executes first, mark the sequence number beside the SQL code.
The effect of FROM is simply annotated by ticking the check box for the table.
WHERE is executed second so mark the sequence number in the SQL code.
This clause includes a logical expression which needs to be evaluated against each row in the base table.
Copy the expression to the expression box to make it clear what is to be evaluated.
Use the Price data from the first row in the expression, it evaluates to TRUE. This is annotated by ticking the check box beside the first row showing that the first row will be included in the results table.
Repeat with the value from the second row. It also produces a TRUE result so tick the second row.
When evaluating the expression with data from the third row the result evaluates to FALSE. This row will not be included in the results table, this is annotated by placing an X in the include checkbox.
This operation is repeated for every row.
The SELECT clause is now executed even though it appears first!
Mark sequence number 3 for SELECT in the SQL box.
Reading the clause we can see which of the columns should appear in the final result table. This is annotated by checking the column headings. In this case all the columns are checked.
The query execution is now complete, we are ready to present the finished result or answer table.
The annotations guide the content of the answer table, simply copy the checked headings and corresponding checked rows.
Looking back the key points are:
The sequence in which the SQL clauses are executed and therefore the sequence of annotations expressing the stages of work done to process the query.
How to annotate the model and then use the annotations to show the result.
The Index grid is not used since there is no ORDER BY clause in the query, no sort order is specified.
You might want to speak to a colleague or note down a few comments at this point.
Perhaps the points will be addressed later in the presentation of elsewhere on the website.
The queries are presented here in SQL format, hopefully the examples are straightforward enough for teachers to read and for pupils to begin reading.
The pupil DB TRACS materials have an English description of the query along with the SQL code.
Again the intention of the activity is to appreciate the query PROCESS.
Whether the query is created using a GUI or entered as SQL code directly, the same sequence of operations take place.
Quite possibly pupils may begin to predict the results simply by looking at the SQL code. This is a good thing! However, remind the pupils that the learning intention is to develop the ability to verbalise and explain the sequence of operations in handling rows and columns that leads to the final version of the result table.
There are ten more examples.
The key features covered are:
Simple search and complex search.
Simple sort and complex sort.
Combining search and sort.
Basic row calculations to produce derived data.
A simple query with no WHERE clause so all rows will be returned.
Note that only two columns are selected.
Since there is no WHERE clause, all the rows are checked to be included in the answer table.
Columns not included in the SELECT clause are annotated with a cross in the checkbox. They are not present in the answer table.
In this example the WHERE clause has a complex condition to be evaluated for each row.
The query begins execution as before, step 1 is marked and the table checkbox is ticked.
There are two conditions along with the logical operator AND to be evaluated in the WHERE expression
When using data from the first row the first and second conditions produce FALSE and so are annotated with a cross beside the values.
Since the logical operator is AND, the entire expression evaluates to FALSE. This is annotated with a cross in the include checkbox.
The second row evaluation is similar to the first.
The third row has a first FALSE condition and then a second TRUE condition, however the AND operator of course produces an overall FALSE and the row is not included.
The fourth row is similarly evaluated to false.
The fifth row conditions are both TRUE and so the entire row evaluates to TRUE, a tick in the checkbox shows the row will be included.
The remaining rows are treated the same and at the end we have two rows to include.
Stage 3 is completed as before.
The final answer table is now displayed in the same way as before.
Another example with a complex search.
The select statement places the columns in a different sequence to that in the table.
The execution / annotation process is carried out as before and results in a single row being shown.
Another complex search example.
The complex condition uses data from the same column so only one check is used for each data item.
The rest of the execution follows a similar path.
This example has a complex logical expression in the WHERE clause.
The annotation should help students to carefully evaluate each row.
This query now introduces an ORDER BY to sort the rows of the answer table.
The query is executed in a manner as before up to the completion of the third clause.
Now ORDER BY is to be processed.
Only the rows which have already been included will take part in the sort.
This is an interesting and important point.
If this were a 5 million row database and only 4 rows were selected, it would be inefficient to sort rows that were not going to appear. Real-life systems must avoid such inefficiency.
To annotate the sort the index grid is used to record the correct position for the row. Note again that the rows do not move. If this were a 200 column table with 5,000 rows to sort then physically moving the rows of data would cause processor and memory inefficiency. It is much better to assign the index position.
Now we are ready again to produce the answer table using the annotated model. This time the rows are copied in index order so that the answer table provides the correct result.
Looking back at this example hopefully students will begin to appreciate the importance of the SEQUENCE of operations in processing the query.
Notice that the Airport column is used in the WHERE clause but is not SELECTed for display.
A complex sort example.
Pupils should already have explored how a “double sort” is handled, perhaps using the PLAN C paper database machine.
DB TRACS does not try to explore the added computation needed to deal with multiple sort keys.
Carrying out the complex sort is no different to the simple sort.
The index key is based on both columns.
Pupils should already know how to identify the correct order for multiple sort keys.
The remaining examples include a calculated column or derived data.
A calculation is performed for each row.
An additional grid on the right is now shown to record the result of the calculation. This can then be used to display the result table with an additional column.
The first two stages begin as usual: the table is checked and the WHERE clause conditions are evaluated for each row.
At step 3 when we read the SELECT clause we see it includes “Sale as Price – 50” where we would expect a column name.
This is the SQL way of including a new column to display the results or row calculations.
The column will be headed as Sale and the values for each row are calculated as Price – 50.
Notice that the sale values are only calculated for the rows which are to be included.
The calculated values do not become part of the HOLIDAY table, they will be used to display the answer table.
Once all values for the included rows are calculated the result table is ready to be displayed as usual.
In this last example the derived data is used to set the order for the rows in the answer table. It becomes clear why SQL ORDER BY is executed after the SELECT clause.
The execution takes place in the usual manner, now including the row calculations for the derived data, in this case the sale price if half price.
At stage 4 when the ORDER BY is executed the index position is based on the newly calculated derived data.
Again it is worth noting that the execution order supports the efficiency in only calculating included rows and handling the ORDER BY AFTER the SELECT permits sorting by the derived data.
That’s the end of the examples.
You want to take some time to reflect on the DB TRACS technique.
Thanks for viewing this presentation.
Look out for the pupil materials, there are blank DB TRACS models and a list of query exercises.
Download documents you need and organise paper copies.
Think about the practical organisation of demonstrating DB TRACS and the logistics of managing.
There is some further advice in the website.as before and results in a single row being shown.
The complex condition uses data fro