session 8: databases teaching computing to gcse level with python
TRANSCRIPT
![Page 1: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/1.jpg)
Session 8: DatabasesTeaching Computing to GCSE Level with Python
![Page 2: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/2.jpg)
Topics todayDatabases theory
Databases
Using SQL
Programming in Python with sqlite
![Page 3: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/3.jpg)
Programming warm up (optional)TaskWrite a function to convert from pounds to euros that takes two parameters, the number of pounds and the exchange rate. The function will convert pounds to euros and return the number of euros.
ExtensionWrite a main() function that will ask the user to enter the number of pounds and the exchange rate and then will call the function you wrote above and then output the answer.
![Page 4: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/4.jpg)
From the specificationOCR
Candidates should be able to:
(a)describe a database as a persistent organised store of data
(b)explain the use of data handling software to create, maintain and interrogate a database.
(c)separation of data from applications and why this is desirable
(d)describe the principal features of a DBMS and how they can be used to create customised data handling applications.
(e)understand the relationship between entities and tables
(f)understand the components of a relational database, such as tables, forms, queries, reports and modules
AQA
know how to read and write data from an external database in a way that is appropriate for the programming language(s) used and the problem being solved
understand the basic concepts of a relational database as a data store
be able to explain the terms record, field, table, query, primary key, relationship, index and search criteria
be able to create simple SQL statements to extract, add and edit data stored in databases
have experience of using these SQL statements from within their own coded systems
be able to use databases from within their own web based applications
![Page 5: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/5.jpg)
What is a database?
A database is a collection of related data, organised such that efficient data processing may be carried out on the data contained in it.
The software used to control access to the data is known as a Data Base Management System (DBMS)
![Page 6: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/6.jpg)
Relational Database
A collection of tables similar to the one above
CarReg Make Model Colour Year
AB02ERT Peugeot 206 Silver 2002
AG51DRT Ford Focus Green 2001
![Page 7: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/7.jpg)
Terminology Quiz – these are the answers!
Field
Table
Report
SQL
Index
Entity
Query
Module
Relationship
Primary Key
Record
Form
![Page 8: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/8.jpg)
Definitions (or clues) to follow
There are 10 questions – write down the terms
![Page 9: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/9.jpg)
A unique field of a record which is used to identify that record.
![Page 10: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/10.jpg)
An object, person, event or thing of interest of an organisation about which data is to be held
![Page 11: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/11.jpg)
Setting criteria to extract particular data from a database
![Page 12: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/12.jpg)
A feature of database software used to view and enter data
![Page 13: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/13.jpg)
A collection of fields describing one instance in a table
![Page 14: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/14.jpg)
The name of a column of data stored in a table
![Page 15: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/15.jpg)
A feature of database software used to produce output from a database
![Page 16: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/16.jpg)
The implementation of an entity in a database
![Page 17: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/17.jpg)
A link or association between two entities.
![Page 18: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/18.jpg)
Storage of an ordered list of key values
![Page 19: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/19.jpg)
A standard language used to describe and query data in a database.
![Page 20: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/20.jpg)
Answers!
![Page 21: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/21.jpg)
An Example Data DictionaryforContact(Surname, FirstName, Title, Street, Town, PostCode, TelNo)
Attribute Data Type Field Size Required? FormatSurname Text 25 YesFirst Name Text 15 NoTitle Text 6 Yes Mr, Ms,
Mrs, Miss,Dr, Rev
Street Text 20 Yes
Town Text 20 YesPost Code Text 8 No LLNN NNLL
Tel. No. Text 15 No (STDCode)-number
![Page 22: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/22.jpg)
Relationship
A relationship is a link or association between two entities
![Page 23: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/23.jpg)
Types of Relationship
One-to-oneHusband and WifePerson and Guide Dog
One-to-manyMother and ChildrenBorrower and Library book
Many-to-manyStudent and CourseFilm and Film Star
![Page 24: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/24.jpg)
Entity - Relationship Diagrams
one - to - one
one - to - many
many - to - many
![Page 25: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/25.jpg)
Enitity-Relationship Diagrams
Specify both degree and name of relationship
Blind Person Guide Dog
![Page 26: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/26.jpg)
Enitity-Relationship Diagrams
The name of a relationship
helps to establish its degree
Blind Person Guide Dogownshas
![Page 27: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/27.jpg)
Enitity-Relationship Diagrams
Ward Patient
The name of a relationship
helps to establish its degree
![Page 28: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/28.jpg)
Enitity-Relationship Diagrams
Ward Patientholds/has
is
The name of a relationship
helps to establish its degree
![Page 29: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/29.jpg)
Enitity-Relationship Diagrams
Ward Patient
The name of a relationship
helps to establish its degree
![Page 30: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/30.jpg)
Enitity-Relationship Diagrams
Patient
![Page 31: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/31.jpg)
Enitity-Relationship Diagrams
Patientattacks
suffers fromhas
![Page 32: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/32.jpg)
Enitity-Relationship Diagrams
attacks
suffers fromhas
PatientIllness
Many-to-many relationship
![Page 33: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/33.jpg)
Many to Many relationships The tables that make up a relational
database cannot represent many-to-many relationships
A many-to-many relationship can be removed by creating a link entity
The link entity’s identifier (primary key) will often be made up of the original entities’ identifiers
![Page 34: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/34.jpg)
Removing many-to-many
E.g. Student and course
STUDENT COURSE
Create a link tableCreate a link table
STUDENT COURSEENROL
![Page 35: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/35.jpg)
Removing many-to-many Using the table notation
Student(StudentID, Surname, DOB)
Course(CourseID, CourseName, Level)
With the link tableStudent(StudentID, Surname, DOB)
Course(CourseID, CourseName, Level)
Enrol(StudentID, CourseID)
![Page 36: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/36.jpg)
QBE Example:
The query is applied to the student table:
The result of the query is
Student Name Student Number D.o.B. Tutor Group Gender
Heathcote, R 12345 20/08/73 AB M
Head, J 22433 13/02/73 CD F
Hargrave, R 66688 13/09/54 AB M
Daley, C 87656 24/12/72 AB F
Student Name Tutor GroupDaley, C ABHead, J CD
![Page 37: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/37.jpg)
Structured Query Structured Query LanguageLanguageA standard language used to extract data from
a database. More flexible and powerful than QBE and database independent.
Key commands for selecting data
SELECT FROM WHERE ORDER BY
37
![Page 38: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/38.jpg)
Structured Query LanguageTo create a table
CREATE TABLE
To insert data
INSERT INTO <TABLENAME> <FIELDS>
VALUES <CORRESPONDING VALUES>
![Page 39: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/39.jpg)
Create Table
create table customer(
customerID integer,
firstName text,
lastName text,
dateOfBirth text,
primary key (customerID))
![Page 40: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/40.jpg)
TASK: sqLite BrowsersqLiteBrowser is open source software that works with sqlite
It can be downloaded from here
We use it prior to using Python and sqLite3 to give some familiarity with SQL first
It can be a bit ropey so write your SQL in a text file first! – and then paste in.
Use Sqlite to test the examples on the next three slides then experiment with different types of SELECT statement
![Page 41: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/41.jpg)
Create Table
create table car(
carReg text,
make text,
model text,
yearOfReg integer,
primary key (carReg))
![Page 42: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/42.jpg)
INSERT INTOINSERT INTO car
(carReg, make, model, yearOfReg)
Values
("MF59 YXS","Hyundai","i20",2009)
![Page 43: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/43.jpg)
SELECT.. FROM.. WHERE
SELECT CarReg, Make
FROM Car
WHERE YearOfReg=2009
![Page 44: Session 8: Databases Teaching Computing to GCSE Level with Python](https://reader033.vdocuments.us/reader033/viewer/2022051401/56649ee55503460f94bf5748/html5/thumbnails/44.jpg)
BREAK
Python and sqlite3