Calculated fields - saving precious storage space
Scenario
A fast food joint keeps a database of menu items and calories.
Calculated fields
A very smart database engineer realised that this design was inefficient.
One of the fields Other calories doesn't need to be stored in the database.
It could just be calculated when required ie when a query is run.
The calculation is:
'Calories' - 'Calories from fat'
This approach saves memory because data in the Other calories field does not have to saved in the table.
We can delete the Other calories field from the table.
Here is an attempt at creating a calculated field in an SQL query.
SELECT ('Calories' - 'Calories from fat') AS 'Other calories'
FROM menudata;
Task 1 - Import
Download the csv file about peripheral devices. Save it in a project folder.
Import the data into a XAMPP database table.
Remember to ask yourself a couple of questions:
- Does the first row contain field names?
- Is there a field which makes every record unique? If so, set it to be the Primary Key field. If not, add a new field called id which auto-increments.
Designing Queries
Design the following queries. Paste your SQL into the worksheet where indicated.
Query 1
This query creates a new field called Threshold which displays 80% of the Calories value.
The output only shows the Item and the Threshold fields, where the Category is Breakfast
Number of records:
Query 2
This query creates the same calculated field, Threshold, as Query 1.
It displays the Category, Item and Threshold fields
but only selects records related to Chicken items.
Number of records: 40
Query 3
This query calculates the average Threshold of all records containing Chicken items and groups the items by Category
It display the Category, Category Count and Average Threshold fields
And ordered in ascending order of Average Threshold.
Number of records: 4
The output should look like this:
Query 4
Adapt Query 3 so that the output is shown in descending order of Average Threshold
Food For Thought
Database engineers are constantly thinking of new ways to use memory efficiently. Computer memory is not free and efficiency reduces costs. Calculated fields allow only necessary data to be stored permanently and when a user interacts with the database, any fields relying on calculations can be displayed.
Homework
essential vocabulary
database sigma querycriteriaimport evidence table integer screenshot record text ascending field double descending data type Yes/No