Session 8: Databases

scacchicgardenSoftware and s/w Development

Dec 13, 2013 (4 years and 19 days ago)

87 views

Session 8: Databases

Teaching Computing to GCSE Level with Python


Topics today

Databases theory


Databases


Using SQL


Programming in Python with
sqlite



Programming warm up
(optional)

Task

Write 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.



Extension

Write 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.

From the specification

OCR

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


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)

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

Terminology Quiz


these are the
answers!


Field


Table


Report


SQL


Index


Entity



Query


Module


Relationship


Primary Key


Record


Form



Definitions (or clues) to follow

There are 10 questions


write down the terms


A unique field of a record which
is used to identify that record.




An object, person, event or
thing of interest of an
organisation about which data is
to be held



Setting criteria to extract
particular data from a database



A feature of database software
used to view and enter data



A collection of fields describing
one instance in a table



The name of a column of data
stored in a table



A feature of database software
used to produce output from a
database



The implementation of an entity
in a database



A link or association between
two entities.



Storage of an ordered list of key
values



A standard language used to
describe and query data in a
database.



Answers!



An Example Data Dictionary

for

Contact(
Surname
,
FirstName
, Title, Street, Town,
PostCode
,
TelNo
)

Attribute
Data Type
Field Size
Required?
Format
Surname
Text
25
Yes
First Name
Text
15
No
Title
Text
6
Yes
Mr, Ms,
Mrs, Miss,
Dr, Rev
Street
Text
20
Yes
Town
Text
20
Yes
Post Code
Text
8
No
LLNN NNLL
Tel. No.
Text
15
No
(STD
Code)-
number
Relationship


A relationship is a link or
association between two
entities


Types of Relationship


One
-
to
-
one


Husband and Wife


Person and Guide Dog



One
-
to
-
many


Mother and Children


Borrower and Library book


Many
-
to
-
many


Student and Course


Film and Film Star

Entity
-

Relationship
Diagrams


one
-

to
-

one

one
-

to
-

many

many
-

to
-

many

Enitity
-
Relationship Diagrams



Specify both degree and name of
relationship

Blind Person

Guide Dog

Enitity
-
Relationship Diagrams



The name of a relationship

helps to establish its degree

Blind Person

Guide Dog

owns

has

Enitity
-
Relationship Diagrams



Ward

Patient

The name of a relationship

helps to establish its degree

Enitity
-
Relationship Diagrams



Ward

Patient

holds/has

is

The name of a relationship

helps to establish its degree

Enitity
-
Relationship Diagrams



Ward

Patient

The name of a relationship

helps to establish its degree

Enitity
-
Relationship Diagrams



Patient

Enitity
-
Relationship Diagrams



Patient

attacks

suffers from

has

Enitity
-
Relationship Diagrams



attacks

suffers from

has

Patient

Illness

Many
-
to
-
many relationship

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



Removing many
-
to
-
many


E.g. Student and course


STUDENT

COURSE


Create a link table


STUDENT

COURSE

ENROL

Removing many
-
to
-
many


Using the table notation

Student(
StudentID
, Surname, DOB)


Course(
CourseID
, CourseName, Level)



With the link table

Student(
StudentID
, Surname, DOB)


Course(
CourseID
, CourseName, Level)


Enrol(
StudentID
,
CourseID)


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 Group
Daley, C
AB
Head, J
CD
Structured Query
Language


A 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

3
7

Structured Query Language

To create a table

CREATE TABLE

To insert data


INSERT INTO <TABLENAME> <FIELDS>

VALUES <CORRESPONDING VALUES>

Create Table


create table customer(


customerID integer,


firstName text,


lastName text,


dateOfBirth text,


primary key (customerID))


TASK: sqLite Browser

sqLiteBrowser 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

Create Table


create table car(


carReg text,


make text,


model text,


yearOfReg integer,


primary key (carReg))


INSERT INTO

INSERT INTO car

(carReg, make, model, yearOfReg)

Values

("MF59 YXS","Hyundai","i20",2009)



SELECT.. FROM.. WHERE

SELECT CarReg, Make

FROM Car

WHERE YearOfReg=2009

BREAK



Python and sqlite3