Transcript Document

GCSE Computing
The Database concept
CANDIDATES SHOULD BE ABLE TO:
DESCRIBE A DATABASE AS A PERSISTENT ORGANISED
STORE OF DATA
EXPLAIN THE USE OF DATA HANDLING SOFTWARE TO
CREATE, MAINTAIN AND INTERROGATE A DATABASE.
Slide 1
What is a database?

A database is a persistent, organised store of related
data.

A database is persistent because the data and structures are
maintained even when the applications that use the data are
no longer running.

A database is organised because the data is stored in a very
structured way, using tables, records and fields so that users
and data handling applications can easily add, delete, edit,
search and manipulate the data.

A database is made up of related data because the individual
items of data have a connection of some sort.

For example, a database of students would contain related items such as
name, date of birth, address, classes etc.
Slide 2
Database examples
- Hospital databases

Hospital databases
maintain details of
patients, doctors and
treatments.

The databases manage
and co-ordinate
admissions,
consultations,
treatments, staffing and
stock control.
Slide 3
Database examples –
Businesses

Businesses use databases
to keep track of sales,
stock and staff etc. and
to analyse their own
performance.

Databases also help
businesses to monitor
trends in customers’
purchases. This helps
businesses identify
market opportunities.
Slide 4
Database examples - Internet
Search engines


Internet Search engines,
such as Google, Bing,
Yahoo, etc. all have
powerful databases
behind the scenes to
collect the details of
websites that are used in
searches.
The search engine
indexes web pages it
finds and uses this index
to answer user queries.
Slide 5
Computerised databases

Computerised databases have several advantages
over paper-based manual databases. These include:

the ability for the data to be accessed by more than one
person at the same time

the ability to interrogate or query the data in many different
ways and view the resulting answers

The ability to rapidly sort the data by multiple criteria

the ability for changes to the data to be made quickly
available to all end users

the reduction of errors in repetitive tasks due to the processing
accuracy of data handling software

the output of data in a range of different formats to suit user
needs (e.g. graphs, reports, forms, etc.), either for viewing on
screen or as print-outs
Slide 6
Databases as Files

Serial Files

In Python – Create the following file.
#program to write 10 values to a disk file.
f = open(‘workfile.txt’,’w’)
for x in range (1,11)
f.write(str(x))
f.write(‘\n’)
f.close()
Databases as Files

Serial Files

This creates a serial file. It can be read also as follows
#program to read 10 values from a disk file
f = open(‘workfile.txt’,’r’)
for x in range (1,11):
f.read(x)
print(x)
Databases as Files

Serial Files

To search for an item in a serial file is easy as they are stored one after each other.
Using the following algorithm – can you create a python program to search for a
value in a serial file.
Input search item
Go to first item in the file
Repeat
if item = search item then output item
endif
move to next item
Until item found or not more data
If item not found
output item not found
endif
Databases as Files

Sequential Files.

Sequential files are files where the data is in some sort of order.

Ordered files are much quicker to search. You can use good search
algorithms such as the Binary search.

Sequential files can be searched using an index.
Databases as Files

Sequential Files.
Lookup
2005
INDEX
DATA
0
2000
1000
2001
2000
2002
3000
2003
2004
2005
2006
The position of
the item is
looked up in the
index then a
serial search is
perform from
that location.
Flat File database

A database that has only one entity such as Addresses can be
stored as a flat file. Which means all the data about that one
thing is stored in a single table/file.
First name Last
name
Telephone
Street
City
Postcode
DOB
Claire
01242 234234
1434
Aenean
Road
Cheltenham
GL52 XXX
28/06/1999
Pate
Data Redundancy

However, if we wanted an address book that now stored orders –
in a flat file we would have to duplicate data each time someone
ordered something.
First
name
Last
Name
Tel
Street
City
Post DOB
Cod
e
Order
Item
Date
Callie
Hodge
01242
234234
1 The
Place
Cheltenham
GLX
XXX
05/07/19
78
Printer
28/05/20
14
Callie
Hodge
01242
234234
1 The
Place
Cheltenham
GLX
XXX
05/07/19
78
Toner
28/05/20
14

What is Callie changed her name or got married – or moved
house – what are the implications for this file then?
Data integrity, validation
and Verification

Check Digit


A calculation performed on a number that generates another digit
which is appended to the number – bar codes, isbn etc. If when the
number is entered and the check digit is not calculated correct –
then this is in error.
Format Check

Data needs to conform to a particular pattern – INPUT Masks TASK.

Length check

Lookup Check

Presence check

Range Check

Type Check
Data integrity, validation
and Verification

Verification

Can be checked by humans

Data Entry Twice

Automated

Bar codes

Magnetic Strips

OCR

OMR

RFID

Smart Cards
Data Modelling

Hierarchical Databases
Data Modelling

Entity Relational Databases

Entity – An object, person, event or thing of interest to an
organisation and about which data is recorded.

Relationship – an association or link between two entities
Data Modelling

Types of relationship
One to one (1:1)
One to Many (1:n)
Many to One (n:1)
Many to Many (m:n)
Data Modelling

Consider this scenario: A level College Enrollment

A college that enrols students for AS and A2 courses. Here are the
data requirements:

A student Can enrol on a number of courses

A course can contain many students

A course is taught by one lecturer

A lecturer can teach one or more course.

A student belongs to one set

A set can have many students.
Create an entity relationship diagram for above.
Database creation

Database creation involves using software to define
and build the structures to hold the data. In a database
file the data is structured in a particular way.

A single item of data is stored in a named FIELD

A complete set of fields makes up a RECORD, the KEY FIELD is a
special field that contains data unique to that record

All the records
on one ENTITY
are stored in a
TABLE

One or more
tables then
make up the
database FILE
Slide 20
Database creation involves the
following steps:

Each field would be created, selecting a data type to match the
data to be stored.

An existing field is set as the key field or a field is created for this
purpose.

Once the complete set of fields have been created and any
validation rules added, they are saved as a table.

Data is then entered into the database fields, each complete set of
fields forming a single record with a unique entry in the KEY FIELD.

For example, in a database of students;

A TABLE would store all the data on all the students

An individual RECORD would store the data on a single student

Several FIELDS would store the data (attributes) of the student such as Student ID,
Forename, Surname etc.

A KEY FIELD such as 'StudentID' can store a unique number to identify that student.

This database FILE would contain just one table and is known as a flat-file database.
Data Modelling

Task

Create a database for the A Level College Enrolment Scenario

What Validations can you put onto the system
GCSE Computing
Slide
23
The DBMS
Candidates should be able to:

describe how a DBMS allows the separation of
data from applications and why this is desirable

describe the principal features of a DBMS and
how they can be used to create customised
data handling applications.
What is meant by a
DBMS?
A DBMS (Database Management System)
is used to manage a database.
 It is an integrated suite of data handling
software that controls and manages the
organisation, storage and access of data
in a database.
 A DBMS allows the data to be separated
from the actual applications that use the
data.

Slide
24
What does a DBMS allow the user to do?
A DBMS will allow the user to:
 create tables and fields
 create relationships between tables
 add, edit and delete data
 maintain the integrity of the data in the
database
 set the access rights of the database users
 allow the database to be searched using
queries
 create forms to help data input and viewing
the information in the database
 create reports to output information from the
database
Slide
25
How does a DBMS allow separation of data from
applications?
Slide
26
A DBMS can present users (and other
application programs) with views of
the data that are particular to the
needs of the application.
 The views are largely independent of
the way that the data is physically
stored in the database files.
 Application programs that need to
use the data do not have to include
code to directly access the database.
 Instead, the programs send requests
and instructions to the database via
the DBMS.

Why is it desirable to separate data from
applications?
Slide
27

A prime advantage of modern databases is that the
database and applications that use the data can be
altered independently of each other.

Without a DBMS, applications were data dependent.
This means that:
 The way in which the data was organised and the
way in which it was accessed were both decided
by the requirements of the application that
accessed it.
 An understanding of how the data was stored and
how it needed to be accessed had to be coded
into the application.
What are the advantages of applications being
data independent?

Avoids data duplication
Different applications need different views of the same data. If
applications are data-dependent, they need their own slightly
different copy of the data.
 This data duplication leads to wasted storage space and time wasted
entering the same information in different places.


Avoids data inconsistency


When data is duplicated for different applications, this can also lead
to data inconsistency. For example: if an employee’s address is
updated in the personnel database file, but not on the payroll
database file, this will lead to inconsistencies in the employee’s data.
The database AND the applications are easier to modify
Ideally the person responsible for looking after the database, the
database administrator (DBA), must have the freedom to change the
physical storage of the database structures in response to changing
business requirements without having to modify existing applications.
 When applications are data-dependent, changes made to the
database can require major modifications the applications that
access it.

Slide 28
What are the principal features of a DBMS?

A good DBMS should provide the following features:
 Elimination of data redundancy
 Managing data integrity
 Handling the security of the database
 Supporting querying
 Applying data validation checks
 Operating transaction controls (managing concurrent
access).
 Facilitating centralised
and privileges to users)
control (managing access rights
 Database
back-ups
 Customised reports and forms
Slide 29
How can a DBMS be used to create customised
data handling applications?
A DBMS is able to present different areas of an
organisation with views of the data that are particular
to their needs.
 This can customised forms for data input and editing
and reports for data output.
 Applications can be therefore be created for different
areas of an organisation.

 For
example, the forms and reports developed for
users in the Sales department of a business can differ
from those developed for the Marketing
department, even though the underlying database
is the same.
Slide 30
Using a database


Transactions

A change to database is called a transaction

Need to be Controlled to prevent conflict
ACID

Atomicity


Consistency


Must not break referential integrity. Must still be valid after transaction
Isolation


Transaction needs to be carried out completely or not at all. DB will refer to
original state if transaction fails
No transaction will interfere with another. Tables are locked when in use.
Durability

Transactions are persistent.
The Language of a Database
SQL – Structured Query Language

The Structure of an SQL Statement
The Language of a Database
SQL – Structured Query Language
Data Type Qualifiers
When supplying values to an SQL statement, for example as query
criteria, their data type must be correctly defined by a "qualifier". This is
done by enclosing the value between a pair of appropriate characters.
Text must be enclosed in either single quotes (') or double quotes ("), for
example:
WHERE tblStaff.Department = "Marketing”
or
WHERE tblStaff.Department = 'Marketing’

A Date should be enclosed in hash marks (#) also called pound or
number signs, for example:
WHERE tblStaff.BirthDate = #09/27/1950#
A number, of any sort, needs no qualifier and can be entered as it is, for
example:
WHERE tblInvoices.InvoiceNumber > 1500
The Language of a Database
DDL – Database Definition Language

DDL is the language Databases use to create tables

You can create a table using just SQL as well, by using the CREATE command as follows.
CREATE TABLE tablename(field_name data_type required, next field…..);

Try entering in the following code into a module in a blank database:
Sub createtbl()
Dim strsql As String
strsql = "CREATE TABLE tblTest([StaffID] COUNTER CONSTRAINT ndxStaffID PRIMARY KEY," _
& "[FirstName] Text(25),[LastName] TEXT(30), [BirthDate] DATETIME);"
DoCmd.RunSQL strsql
End Sub
DDL
When your table is created you can then use SQL to enter in records. Try the
following in the same module and run it. Check to see that the table you
have just created now has the new values.
Sub inserttbl()
Dim strsql As String
strsql = "INSERT INTO tblTest ([FirstName], [LastName], [BirthDate])" _
& " VALUES ('Martin', 'Green', #09/27/1950#);"
DoCmd.RunSQL strsql
End Sub
DDL
If you wanted to add a new field to your table dynamically – SQL
can also do this as well. Add the following code to the module and
run the subroutine and see what happed you your table.
Sub addtbl()
Dim strsql As String
strsql = "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;"
DoCmd.RunSQL strsql
End Sub
DDL
If you then wanted to modify the data because you have entered a
new field for instance – SQL will allow you to do that as well.
Sub modifytbl()
Dim strsql As String
strsql = "UPDATE tblTest SET [Age]=52 WHERE
[FirstName]='Martin' AND [LastName]='Green';"
DoCmd.RunSQL strsql
End Sub
The value “52” has been hardcoded here – but you could have a
function work out the age from other fields, store that answer in a
variable and have that assigned here.
DDL
Now if you wanted the delete a row the SQL statement for that is as
follows:
Sub deletetbl()
Dim strsql As String
strsql = "DELETE FROM tblTest WHERE [FirstName]='Martin';"
DoCmd.RunSQL strsql
End Sub
DDL
And finally if you wanted to removed the table altogether then the
DROP command will allow you to do this:
Sub droptbl()
Dim strsql As String
strsql = "DROP TABLE tblTest"
DoCmd.RunSQL strsql
End Sub
Features offered by a DBMS
Queries
Forms
Reports
Query Design

The example of a simple query below uses an Microsoft Access
database table and a query to find the names of all students who
are in form 10B. This involves the following steps:

Adding the 'Students' table to the query;

Selecting the fields to display;

Adding the search criteria against the correct field, in this case 'Form‘;

Running the query.
Slide 41
Query Design.

1)
2)
3)
4)
5)
6)
7)
8)
TASK

Using the Basement Database – Perform the following queries.

Use the query help sheet as a guide.
All the Davies that live in a close
All the Doctors
All the Men who are not doctors
All the Customers that have one
letter for the First Name
All the customers with a surname
alphabetically after Morris
All the Records that have a country
specified.
All the CDs that are between £5 and
£10
All the Best of albums
Queries.
Some Criteria
•
•
•
•
•
•
•
•
•
>10
<=100
Between 10 and 20
“Bob”
Like “B*”
Like
[Form]![frmCustomer]![CustomerI
D] & “*”
<10/12/13
<Date()
“Yes”
Forms

Task

With the teacher – create a user form for searching customers in the
Basement Database system
Reports

Task

Create some customised reports for the queries you created in the
Query Design Task.