Transcript Organisation Of Data

Organisation Of Data (1)
Database Theory
www.ICT-Teacher.com
11.3 Organisation of Data for
Effective Retrieval
• Describe the nature and purpose of a relational database
and how it works.
• Understand that data needs to be organised in a
relational database to allow for effective updating and
retrieval.
• Understand how data can be retrieved to produce
meaningful information.
• Recall the relevant advantages of relational databases
over flat file information storage and retrieval systems.
Select and justify appropriate file and relational database
structures for particular applications.
What Is A Database?
• An organised collection of data / information.
• Not necessarily stored on a computer.
• A computerised database gives:
– flexibility in organising, displaying, and printing
– much faster than a manual system.
Data
• Field: data held on a person can be split into a
number of fields; these could be:
– ID; first name; second name; address; town;
post code; date-of-birth; etc.
• Record: a collection of all the fields above on one
person.
• File: a collection of records; a payroll file will
contain a record for each person on the payroll.
• Database: may contain many different files,
structured in such a way that information can be
obtained from one or all of them at once.
Flat File And Relational
• A computerised database is one of two
different types:
– Flat-File which is only suitable for a few
simple applications.
– Relational which is much more
comprehensive and flexible and suitable for
most applications.
Advantages Of Computerised
Databases
• Only have to enter the data once, all other
applications can use this data.
• Files are linked so an update in one will affect
the data in another.
• The structure can be changed.
• Access is very rapid.
• Complex searches can be saved and used over
and over again or amended.
• Data remains consistent as the same data is
accessed by everyone.
• Data can be validated upon entry protecting
integrity.
Disadvantages
• Dependant on the technology, a break in service
means no applications can be run.
• Centrally held data needs extra security than
dispersed data.
• Users need to be trained which is costly.
Flat File
• Similar to a computerised card box of files.
• Only one file of records can be stored.
• Ok for i.e. a list of names and addresses such
as what you would find in an address book.
• No good for businesses where data is more
complex and flexibility is required.
Employee
Number:
1
Employee
First name:
Fred
Employee
Surname:
Bassett
Sex:
M
Age:
21
Position:
Sales Assistant
Salary:
£15,000
Cafe
Address
Bridge Street
NewCross
London
Telephone:
02073333456
e-mail
fred1@SurferCafe.
com
Relational Database
• These types are better than the flat-file as
everything is not stored in one file, it is stored in
several files with relationships between them.
• The relationship ensures the data contained in
one file can be combined and used with data
from any or all of the other files.
• Files in this type of database are known as
tables, when designing we create tables
containing the various Fields of data.
Advantages
• Data is not duplicated, less data stored.
• Time is saved entering data.
• Processing and searching time reduced.
• Consider the flat file database of employee
details.
• Much of the data appears in every record, the
cafe details.
• Instead of repeating these in each record, a file
would be created for the cafe details.
• One field will need to stay as the common link
between files, i.e. the employee number.
• With more data than shown it may be possible to
create more than two files.
Employees
Employee
Cafe
Employee Title
Title
Cafe Name
Employee First Name
First Name
Road
Employee Surname
Surname
Town
Employee Number
Number
Post Code
Address Road
Address Town
=
Road
+
Telephone
Town
e-mail
Post Code
Post Code
Employee Number
Employee Telephone
Telephone
Employee Salary
Salary
Employee Sex
Sex
Employee Age
Age
Employee Date of Birth
DoB
Employee Position
Position
Cafe Name
Cafe Address Road
Cafe Address Town
Cafe Address Post
Code
Cafe Telephone
Cafe e-mail
• Instead of entering the cafe details every time
data is entered for an employee, each cafe need
only to be entered once.
• The cafe details are kept in the Cafe table and
the Employee details are kept in the Employees
table.
• One linking field needs to be present, in this
example the employee number.
• Generally more data would be held in a
database and consequently more tables may be
required.
Searching
• The concept of storing data in a database is to
retrieve bits of it when required.
• This is done by searching for data that satisfies
a particular criteria, i.e. who are the employees
who live in Greenwich.
• Searches in a database may be done by
programming, called Structured Query
Language (SQL), or in Access can be done by
selecting criteria, called Query By Example
(QBE). Access converts QBE into SQL.
Structured Query Language
• Query By Example (QBE) and Structured
Query Language are both industry standard
methods for extracting information from a
database.
• The advantage of QBE is that it is graphical
and simple to use, the advantage of SQL is
that it is almost universally used in a
relational database.
Query By Example
A Query
• A query may:
• combine the information from one or many
related tables,
• select the fields to be shown,
• specify which criteria for each field to search
for,
• save the query for repeated use,
• save the results of a query, or enter them on
a report for printing off.
Using SQL
• SELECT TblEmployee.Title, TblEmployee.[First Name],
TblEmployee.Surname, TblEmployee.Position
• FROM TblEmployee
• WHERE (((TblEmployee.Position)="manager"))
• ORDER BY TblEmployee.Surname;
Database Structure
• The building blocks are the tables and the
relationships between the tables.
• A table is a collection of data arranged in rows
and columns.
• Fields are the titles to the columns.
• Records are the rows, which contain the set of
data for a single item.
Table (Data)
Rows
Columns
Primary Key
Fields (title)
Table (Design)
Data Types
Field Names
Primary Key
• A field where the data entered is not duplicated
in any other record for that field, (unique data).
• It is usually a numeric field, or a mix of text and
numbers, i.e. National Insurance number.
• In some cases a surname could be used as a
Primary key, depending upon the amount of data
held, but a second person with the same
surname cannot be entered. This could be
overcome by having two fields entered as
Primary key: First name and Surname.
• If no Primary key is entered then the database
may create one by default: (an Auto-number).
Foreign Key
• This is a Field in a table, and this Field also
exists in another table as the Primary key.
• This is the basis of a direct link between the two
tables. It is the only Field where the data needs
to be repeated.
• What would a suitable Primary key be for:
– an employee in the payroll,
– a hospital out-patient,
– a catalogue of DVD films?
Field Data Types
• Data entered into a database may
be of several types.
• This helps with the validation of
data entered into the database
and restricts the entering of
erroneous data.
• Calculations can be performed on
numeric data only.
• The length of text fields need to
be regulated to free up allocated
space and need to be similar for
linking through relationships.
Indexing
• Important for sorting and
searching.
• Data can be shown in i.e.
alphabetical surname
order, employee number
order, or a combination of
two or more Fields if
indexed in the design.
• If there is a Primary key
present the database will
be sorted by that Field
automatically.
• An index field is generally
known as a Secondary
key.
Surname Indexed
• Only text, numeric,
currency or date can
be indexed.
Validation
• Is done to ensure (as far as possible) that every
data entry is accurate.
• There are several types of validation check:
• Does it have to be there;
• Is it within a specified range;
• Is it one of a list of valid values;
• Is it in a specified format of numbers and letters?