We humans find it easier to navigate through the vast array of data there is out there if the data is structured and easy to manage. Databases provide this functionality - structure firstly and then a special type of language to 'query' the data to draw out useful things.
We are learning ...
So that we can ...
NEEDS AN INTRODUCTION ABOUT STRUCTURED AND UNSTRUCTURED DATA RECORD STRUCTURES It's all about 'attributes' isn't it!
Click to engage
The relationship between a table and a CSV file - click to engage
Task 1.1 Storing a table as a CSV file Where we learn how to store records in a CSV file and access them with a Python script.
Set up your data collection methods First we'll collect some raw data about your peers. Grab yourself a piece of paper, rotate it to landscape format and set up a table like this ... Look carefully at the sample data I've included, particularly the format and the encoding ...
Collect data on at least 20 of your peers! Make sure that you have encoded the Eye Colour and Hair Colour and you've NOT written the units in the Nose Length! Whereas in normal conversation, we might refer to the parts of this table as rows and columns, when we are discussing databases, we call them records and fields ...
Now label your table like the example to identify the records and fields. Now convert your manual data into a CSV file called peer.csv and make sure that you ...
Print out your CSV file for your notes. For instance, my results might look like this (I only collected 5 'cause I'm lazy) ... Programming challenges Attempt the following challenges to demonstrate your understanding.
Irma wanted to know whether it was possible to replace her manual system of recording users data for the FilmBuffs website with a computerised version using CSV files and Python scripts. Write an instruction manual for how she might be able to achieve this. Make sure you use all the keywords from this task in your response.
Could you extend your Python script so that it performs the following functions on the data?
Click to engage
For this section, we are going to be using a piece of software called DB Browser for SQLite which is free and allows you to create single file databases which can be accessed using Python! Nice!
Task 2.1 Download and open an existing database file Where we learn how to manage a simple database file
Download the database file
Download the 'films.db' database file and save this in a suitable place in your documents. DO NOT DOUBLE CLICK THE FILE - the operating system will probably not know which application to open it with and you might get an error!
Open the database file using DB Browser for SQLite
Now, open DB Browser for SQLite and click on the 'Open Database' button at the top of the screen. Find the 'db' file you saved and open it. You should see a lot of entries on the 'Database Structure' tab, including an entry under 'Tables' called '
film'.Click on the 'Browse Data' tab. The '
film' table should already be selected and you should see 12 records. You can resize the columns if you like to make the data easier to read. Do you recognise this data from a previous section?The contents of the film table - click to engage
The fields in this table are ...
True, there is not much to learn in this task, but nevertheless. In a word processed document with a suitable header and footer, put a screenshot of the table view from DB Browser. Label the field names and the records in a similar way to the last part of Task 1.1.
Where we learn how to create a new database table
You've got a simple, single table database now that you can experiment with in the next Activity. If you are up for a challenge, we will now try to create a new table in the database by constructing the table first and then importing data into it from a CSV file. Follow the instructions carefully!
Choose 'Edit > Create Table ...' and you should get a popup window asking you for information about the table.
Name the table '
user'. Notice that the table is called 'user' rather than 'users'. This is important as it holds instances of each user of our film rating system.Using the 'Add field' button, enter the following field definitions ...
Field definitions - click to engage
When creating the table, we specify the username as the primary key which is used to identify each user.
As you were creating the table, you should have noticed that the software created some instructions in the panel underneath. This is a special type of language called 'Structured Query Language' or 'SQL' for short. We will learn about simple SQL later on in this section.
Defining the table in SQL - click to engage
If you are happy that your utterances match mine, click the 'OK' button.
Write changes to the database
Click the 'Write Changes' button to make sure that the changes you have made to the database are saved.
Did you click the 'OK' button? Shame - you'll need to get the table definition dialogue back again before you can evidence your learning. Click on the 'Database Structure' tab and select the '
user' table. Now select 'Edit > Modify Table' from the menu bar.Take a screenshot of the 'Edit table definition' window and put this in a word processed document with a suitable header and footer. Write an explanation to Sampson describing what you have done.
Where we learn how to import data from a CSV file into a database table
Follow the instructions carefully!
Choose 'File > Import > Table from CSV file ...'. Find the 'user.csv' file in your userspace, select it and click 'Open'. Because you have already created a table called '
user' and the CSV file is called 'user.csv', DB Browser for SQLite should automatically decide to import the data into the correct table.Make your your 'Import CSV file' dialogue looks like mine ... The 'Import CSV file' dialogue box - click to engage
When you are happy, click the 'OK' button. You should see a dialogue box like this. If you don't, you have probably named either your table or the CSV file incorrectly. Go back and fix it now! Are you sure - click to engage
When you are happy, click the 'Yes' button. You should see an 'Import completed' confirmation box. Click 'OK'. Now click on the 'Browse Data' tab and make sure that the '
user' table is selected. You should see all the data from your CSV file in the table. Again, you can adjust the column widths to make the contents easier to read.Browsing the 'user' table - click to engage
Click the 'Write Changes' button to make sure that you changes have been saved.
Provide a screenshot of the data in the users table on a word processed document with a suitable header and footer. Underneath the screenshot, write an explanation to Sampson explaining what you have done. You should be able to compare this to the work you evidenced in Task 1.1.
Yes, you totally could have done that! I would always suggest creating the table first however as it gives you more control of the field definitions. If you really want to import the table structure and the data together, read on!
Where we learn how to import a complete table from an SQL file
The last option for creating a table is to import a text file which creates the table and then imports the data directly into it. The text file has a special 'sql' file extension so that DB Browser knows it's a valid file.
Download the rating.sql file and save this in the same folder as your database file.
Open the SQL file in Notepad++ - do NOT double click it! Again, the Operating System might complain that it doesn't have a file association or it might open the SQL file in another application instead. Look carefully at the structure of the file - there are four sections.
The first line signifies a 'database transaction' is occurring ...
The first part of the SQL file - click to engage
The second section defines the table structure, using the '
filmID' and the username as a composite key ...Defining the new table in SQL - click to engage
The majority of the file INSERTs data into the table ...
Lots of inserting going on - click to engage
The last part COMMITs changes to the database
I'm ready to commit - click to engage
Print out the SQL file for your folders (your teacher may give you a copy).
Choose 'File > Import > Database from SQL File ...' and locate the 'rating.sql' file you downloaded in Step 1. You should get a dialogue box asking whether you want to create a new database. Make sure you click 'No' ...
Ready to import the SQL file - click to engage
You should be greeted with a dialogue box informing you that the import was successful.
Click the 'Browse Data' tab and make sure that the '
rating' table is selected. You should see a table of data with three columns ...
This table records each film (from the '
film' table) that each user (from the 'user' table) has watched and what rating they awarded it from 1 through 5. The table is structured so that each user can only rate (or re-rate) each film once. through the use of a composite key of 'filmID' and 'username' (which must be unique).Browse the 'rating' table - click to engage
We could use this table to investigate which users have watched each film or which films a particular user has watched, but more of this later ...
Click the 'Write Changes' button to make sure that the changes you have made to the database have been saved.
In a suitable word processed document, write an step-by-step instruction guide to explain to Sampson how to import data into a database from an SQL file. Also, email a copy of the 'film.db' file to your teacher so that they can check that everything is OK and that you have completed the work satisfactorily.
Click to engage
You will need a copy of the completed 'film.db' database file from the previous activity. If you haven't finished or you have lost it, you can download a completed copy!
If you had, you might not have done the work!
We will be using Structured Query Language (SQL) to extract data from our database. The SQL instructions we write are called queries because they ask the database questions.
Where we learn how to select data from a database
If you have not done so already, open up the completed film database using DB Browser for SQLite.
We have mainly been working in the 'Browse Data' tab. Switch to the 'Execute SQL' tab now will allow us to ask our database questions to help us to search for data using SQL.
Structure of the 'Execute SQL' tab - click to engage
In SQL, the asterisk (*) is a wildcard character which stands for 'everything' and we use this when we want to retrieve all fields from tables in our database.
SELECT * FROM filmTry out this query yourself in the 'Execute SQL' tab of DB Browser for SQLite.
Selecting all data from a single table - click to engage
Sometimes, we don't need all the fields in the results so we can specify which field we want.
SELECT title FROM filmTry out this query yourself in the 'Execute SQL' tab of DB Browser for SQLite.
Selecting a single field from a table
Finally, we can specify multiple fields as a comma separated list.
SELECT title, genre FROM filmTry out this query yourself in the 'Execute SQL' tab of DB Browser for SQLite.
Selecting multiple fields from a table
Roy wants some data from the database. You challenge is to construct suitable SQL queries to satisfy the following requirements. Provide evidence (screenshot or written explanation) of your SQL queries and explain to Roy how they work. Remember to test them out to make sure they work!
Where we learn how to sort data selected from a database
Once we have selected our data, we can sort it into order. The order can be ASCENDING (starting small and getting bigger) or DESCENDING (starting big and getting smaller). Data can be sorted numerically or alphabetically (by ASCII code).
Ascending or descending - click to engage
Make sure you have the '
film' database open and that you are working in the 'Execute SQL' tab.In SQL, we use the
ORDER BY clause to tell the database how to sort the data. For instance, to sort the films into ascending order of length, we would use the following SQL command ...SELECT * FROM filmORDER BY film.length ASC... where
ASC stands for ASCending. I've split the command onto multiple lines to make it easier to read. Try out this query yourself in DB Browser. I've been lazy and selected all (*) the fields.Sorting in ASCending order
To sort in descending order, simple change the end of the query from
ASC to DESC (for DESCending).SELECT * FROM filmORDER BY film.length DESCTry the query out yourself in DB Browser to make sure you are comfortable with it's use.
Sorting in DESCending order
Roy is demanding, isn't he? He wants some more data from the database and has asked you to construct suitable SQL queries to satisfy the following requirements. Write your SQL queries in your notebook / on paper and explain to Roy how they work. Remember to test them out to make sure they work!
Where we learn how to filter data from a database
This is where the real power of databases comes into play. To filter results from our database, we use the
WHERE clause followed by a conditional statement.Make sure you have the '
film' database open and that you are working in the 'Execute SQL' tab.Quite simply, we use the
WHERE clause to specify an equality. For instance if we only want the films from the 'Action' genre, we could use an SQL command like this ...SELECT * FROM filmTry out the query in DB Browser.
Filtering based on an exact value
We can also use other standard comparison operators in the
WHERE clause. For instance, if we want all films which are 2.0 or more hours long from our table, we could use this SQL query ...SELECT * FROM filmTry out the query in DB Browser.
Filtering based on a range
Finally, we can search for data my matching part of the contents of a field using the
LIKE clause. We also normally have to use the '%' wildcard character before and after the search term to force the SQL statement to match the search term anywhere in the field.SELECT * FROM filmTry out the query in DB Browser.
Filtering based on a match
You can use logical AND and OR operations to specify more than one search criteria at a time. For instance, if we wanted all the details for the films in the "Comedy" genre that were longer than 1.5 hours in length, we could write a query like this ...
SELECT * FROM filmWHERE film.genre = 'Comedy'AND film.length > 1.5Try out the query in DB Browser
Filtering using AND and multiple criteria
If we wanted all the details of films in the "Science fiction" or the "Historical" genre, we could use the OR operator.
SELECT * FROM filmWHERE film.genre = 'Science fiction'OR film.genre = 'Historical'Try out the query in DB Browser
Filtering using OR and multiple criteria
What, more questions! Roy challenges you to construct suitable SQL queries to satisfy the following requirements. Write your SQL queries in your notebook / on paper and explain to Roy how they operate. Remember to test them out to make sure they work!
In Step 4 of the last task, we used the '
%' wildcard character to enable us to search for something in one of the fields in our database by putting it before and after the search term. Investigate what happens if you omit the '%' sign at the start, at the end or both. What does the '%' sign actually do? Under what circumstances would each search string find the word 'the'? Tell your teacher you thoughts.Click to engage
You could, but why make life easy for yourself?
Task 4.1 Inserting records Where we learn how to insert records into a database table
Make sure you have the '
film' database open and that you are working in the 'Execute SQL' tab.Let's use SQL to
INSERT (that's a clue) a new record into the table. Inserting records always puts them at the end of the table. In fact, we've seen the INSERT command before, can you remember where? (HINT : Look up ...)INSERT INTO filmVALUES ('013','A Slight Disturbance on Oak Avenue','Horror',2.7) Demonstrate your learning
Emma has asked you to add a new film (title, genre and length of your choice) into the database. Remember to use a different, unique ID for the film (currently '
014') or you will get a 'UNIQUE constrain failed' error - the 'filmID' must be unique to allow each film in the 'film' table to be identified. Evidence your learning using screenshots and written explanation in a word processed document.Task 4.2 Deleting records Where we learn how to delete records from a database table
Make sure you have the 'film' database open and that you are working in the 'Execute SQL' tab.
To delete a record from a table you have to use a
WHERE clause to specify which record to delete. For instance, to delete the new record we just inserted ...DELETE FROM filmDemonstrate your learning
Emma made a mistake and has asked you to delete the record you added in the previous task. Be careful however; if you delete one of the other films, you might inadvertently break an activity which you might engage in later on. Again, evidence your learning using a combination of screenshots and written explanations of what you did, why you did it and what effect it had.
Task 4.3 Updating / editing records Where we learn how to update and edit records from a database table
Make sure you have the 'film' database open and that you are working in the 'Execute SQL' tab.
Again, the update / edit a record, we have to use a
WHERE clause to specify which record we want to update. For instance, one of the film titles in the original 'film' table was wrong, so let's change it.UPDATE film SET title = 'The Prisoner of the Boyfriend' WHERE film.filmID = '011' Demonstrate your learning
Lastly, Emma has noticed that the title of film 'The First Flames' is wrong - it should be called 'The Last Flames'. Use a suitable SQL query to change the '
title' of the film. Evidence your learning using a combination of screenshots and written explanations in a word processed document with a suitable header and footer.Click to engage
The database you have created holds details of ...
The database can tell me that? We can find the answer to this question using database joins. Let me explain ...
Click to engage and print yourself a copy
Task 5.1 Complex, man Where we learn how to select data from multiple tables
If your teacher hasn't already given you a copy, print yourself a copy of the explanation from the popup and put it in your folders.
Rubber duck the example to your shoulder partner. Quack, quack!
Open up your copy of the 'film.db' database file. If you haven't got a copy, you can download one from the top of Activity 3. Switch to the 'Execute SQL' tab, type in and execute the query from the example. As you are typing it, READ IT OUT LOUD - this will help you to focus on it's operation.
Executing a complex query using joins
Now construct a multi-table query which satisfies Georgia's original request (you might need to look back at the start of the activity to find out what it was. Evidence your learning using a combination of screenshots and written explanations - if you achieve this, you are cookin!
Click to engage
Click to download the Cheatsheet
Task 6.1 Using Python to retrieve data from a database Where we use Python to retrieve data from a database
Create a new Python script and save it with a suitable filename in the same folder as your film.db database file.
Use the 'Cheatsheet' to help you to write Python scripts to retrieve and display the following. The results are always returned as a read only list (called a tuple) but you can 'handle' this like you would any normal Python list.
Look back at the scripts you have written and choose the 'best' one (you decide what makes it best). Print out this script in colour using Notepad++ and annotate it to help to explain to Zackary how the script works. You can also give this to your teacher to get it checked if you can't find Zackary.
Task 6.2 Using Python to change data in a database Where we use Python to alter data in a database
Create a new Python script and save it with a suitable filename in the same folder as your film.db database file.
Use the 'Cheatsheet' to help you to write Python scripts to achieve the following. Remember to commit the changes to the database after you have executed the SQL statement.
Look back at the scripts you have written and choose the 'best' one (you decide what makes it best). Print out this script in colour using Notepad++ and annotate it to help to explain to Zackary how the script works. You can also give this to your teacher to get it checked if you can't find Zackary.
Task 6.3 Using parameters in your queries Where we learn how to put parameters in our queries
Create a new Python script, save it in the same folder as the 'film.db' file and execute it.
import sqlite3# Connect to databasedb = sqlite3.connect('film.db')cursor = db.cursor()# Get parametertitle = input('Tell me the film title : ')# Execute SQL using parametercursor.execute('''SELECT * FROM film WHERE title = ?''',[title])# Handle resultsfor line in cursor: print(line)# Close database connectiondb.close()Programming challenges Now, write scripts using parameters which satisfy the following requirements. Remember that if you are retrieving data from the database you need to handle it appropriately and if you are changing data you need to commit the changes to the database before you close the connection.
Look back at the scripts you have written and choose the 'best' one (you decide what makes it best). Print out this script in colour using Notepad++ and annotate it to help to explain to Zackary how the script works. You can also give this to your teacher to get it checked if you can't find Zackary.
There are plenty of further resources you can use to extend your learning if you want to. Investigate these two website as a starting point.
Make sure you tell your teacher what you have learnt!
Click to load key word list to help you make your own flash cards
The film database that you've been using in this topic contains a list of random usernames and passwords. The passwords are generated by a tool located called XKPasswd, a really useful service inspired by this XKCD Comic ...
 |
 |
|
|
|
Teachers Resources
Online Safety Links
This website and its contents are licensed under a ...
Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License
Chinese Panda Character from GraphicMama Team on Pixabay