Information and
Communication Technology
Using a database to enter and organise information
People have been collecting and organising data for a very long
time.
Thousands of clay tablets still exist from Mesopotamia, dating
back over 5000 years and recording things such as trade transactions and
storage of goods.
The problems with clay tablets, and all the subsequent systems
based on papyrus sheets, paper, parchment, etc. is that they are bulky and that
it takes a relatively long time to find a record once it has been stored.
That all changed when computers arrived. Storage became digital,
search times dropped to seconds rather than minutes or hours, and the
development of user-friendly software meant that using a
database could become an everyday task for anybody with a PC.
This page looks at some of the commoner aspects of using a database.
Entering data
Using a database requires you to collect and enter data
Using a database is regarded by examiners as requiring a higher
level of skill than using many other software packages. So, for the IGCSE
practical examination, and probably for other practical examinations as well,
it is likely that you will be given a database to work with. It will have
already been partially completed and the examination questions will ask you to
perform a number of tasks with it.
If you are creating your own database however, you will need to be
able to collect and enter data.
The commonest way is to type the data in using a keyboard, but nearly all modern
databases allow the import of data from external sources as well. The details
differ between databases but they will all accept data from:
·
a table from another
database
·
a comma separated variable
(CSV) file
If you are typing in the data you will usually do it on a form or
a table.
A table looks like a spreadsheet table and you can enter data into
any cell.
A form is often a customised input screen and may vary in
appearance. Usually, forms are created for specific purposes and will only
display selected fields.
Modern databases can store and work with a lot of different types
of data.
For the IGCSE, you are required to know about: logical / Boolean,
alphanumeric / text, numeric (real and integer), and date types.
Logical / Boolean. This
data type may be represented by a single binary digit (a bit), 1 or 0. However,
it rarely means the numbers 1 or 0 and should not be used for that purpose
unless the 1 and 0 are alternative choices. More commonly it will mean Yes or
No, True or False, or some other pair of values that must be one or the other.
Alphanumeric / text. Sometimes
called 'string'. This data type holds any character, including letters, numbers
and symbols. The amount of characters that can be held may be limited to 255,
as this allows the size of the data to be held in a single byte. Some databases
allow up to 65,535 charcters, with the size being held in two bytes.
Numeric. Numbers may be integers (whole
numbers) or real (numbers with decimal places). There are a
lot of variations on number, including fixed decimal, floating point, and small
or large integers. Fortunately, for the IGCSE practical examination, you will
only have to deal with reals and integers. i.e. those with decimal places and
those that are whole numbers.
Date. This also includes
time and date-time. Although dates and times look as though they
are alphanumeric, they can be converted into numeric values that will allow
calculations to be performed. e.g. to find the number of days between two
dates, or to add a month to a date.
There are a lot of date and time formats but these are just for
presentation. 1st December 2011 will be held as the same number as 01/12/2011
or 01 Dec 11. The database simply presents that number in different formats
when you view the date.
If you are using a database outside of the IGCSE specification,
you may find other data types useful. Most databases can use data types such as
currency, hyperlinks and OLE objects. OLE(object linking and embedding) can
include things such as images, sound files, documents, spreadsheet charts and
bits of other databases.
Database structure
There are a lot of different types of database, but for IGCSE ICT
you only need to know about two of them, flat file and relational. Both of
these, and some other types, are based on tables of data which look like
spreadsheet tables. In fact, databases are able to use spreadsheet tables as
data tables and spreadsheets can be used to build databases. It's really just a
matter of how the data is organised and what can be done with it.
Flat file databases are
held in a single table of data, while relational databases can have a number of
tables linked together.
Most modern databases are relational. e.g. MS Access, LibreOffice Base, but there are several common
applications such as address books or diaries which are based on a flat file
system.
A data table is organised into
rows and columns, like a spreadsheet. Each row holds a single record and each
column holds a field.
A record holds a set of
related data. e.g. a person's name, address, date of birth, etc. A field holds
data for one of those items. e.g. date of birth. When answering examination
questions It is important to distinguish between the field name and the field
content. e.g. The field named 'date of birth' will hold a date, not the words
date of birth. Common mistakes are giving content when asked for a name or a
name when asked for the content.
In order to be searchable, and therefore more useful than a
spreadsheet table, at least one of the fields must be a key field.
The key field holds the primary
key for each record. The primary key must be a unique identifier for
that record and many databases create a default autonumber field to ensure that
a primary key exists.
It is allowable to have more than one key field for each record,
in which case, only one of them needs to hold a unique primary key.
Relational databases often have more than one table. Links
(relationships) are formed between tables by sharing fields. e.g. in a database
about cars, the vehicle registration field would be the key field in a table
holding details of individual cars. The same vehicle registration field could
be included in a table holding details of car owners. The field would form the
link that allows an owner to be connected to the car that they own.
There is a well known saying in computing, Garbage in, garbage
out. (GIGO). If you want to avoid the garbage out part, you need to ensure that
you don't make mistakes when entering data into your database.
One way of helping with this is to use validation.
Validation is the use of software to check that data meets pre-set
criteria when it is entered into a database. Note that although it will reject
data that does not fit the criteria, it does not ensure that the data is
actually correct.
Making sure that data is correct would need the process of
verification, where the data input is independently checked against the
original data source.
For the IGCSE, you need to know about four validation methods;
range check, presence check, type check, length check.
Range check. This ensures that an
entry falls between two boundaries. These may be numerical, e.g. between 2 and
8, or alphanumerical. e.g. between abr and hgy. The boundaries may be made
inclusive or exclusive. e.g. between 2 and 8 OR between and including 2 and 8.
If only one end of the range is specified, it becomes a limit
check instead.
Presence check. This check simply
ensures that something is entered in the field before a record can be stored.
i.e. an empty field is not allowed if a presence check is enforced.
Type check. This checks what sort
of data is allowed. It is usually set up when a field is first created, since
most databases require that the field be given a data type to start with. You
need to be able to set up fields with logical / Boolean, alphanumeric / text,
numeric (real and integer), and date types.
Length check. This
checks how many characters a piece of data consists of. As with type checks,
this is often set up when a field is first created, but most databases have
default field sizes so the length check could be imposed afterwards.
Sorting and searching
Once you have a database with some records in it, you will need to
be able to sort and search the data.
Sorting simply means putting
the data in order. This might be numerical, date, or alphabetical, depending on
the field that you use for the sort process. Data can also be sorted to be
ascending or descending in value. i.e. 0 - 9 or 9 - 0.
Data is often sorted automatically using the key field, but you
are allowed to use any field or combination of fields that you want.
Sorting using a single field is faster than having multiple
fields, but for the size of database that you are likley to use at IGCSE level
the difference will not be noticable. If you choose to sort using more than one
field you must choose the order in which the sort will occur. e.g. for an
address book, you might sort on family name, followed by first name. This would
first group people by family and then put them in alphabetical order of first
name.
Searching involves finding
specific records within the database.
With a simple flat file database such as an address book, this is
a simple process and could be done by sorting on the field you are interested
in, e.g. name, and then looking down the list to find the correct record.
For relational databases the process is more complex. The data is
usually spread over several tables, so just sorting one of them and looking
down the list would not find all the information for that record. In addition,
for any type of database, as the number of records increase, the task of
finding what you want gets more difficult and time consuming.
For the practical examination you need to know how to; search on a
single criterion, search with multiple criteria, and search within results.
The database that you are working with at IGCSE level will
probably use Structured Query Language (SQL) to run searches.
SQL queries take the general form of SELECT field(s) FROM table
WHERE condition.
Fortunately any database that you use for an IGCSE practical
examination is likely to have a search / query wizard built in to it. You just
need to follow the wizard and the SQL will be written and run by the software.
You will of course need to get plenty of practice in using the wizard before
taking the examination.
A single criterion search just
looks for a single condition in the WHERE part of the SQL. e.g. searching for
records where the name field holds the word Smith.
A multiple criteria search involves
looking for more than one condition in the WHERE part of the SQL. e.g.
searching for records where the name field holds the word Smith and where the
age field holds a number greater than 16.
Searching within results is
a process where a search produces a set of records which are then narrowed down
further by a second search which only applies to that set of records rather
than the whole database. In most cases the desired result can be achieved by
one multiple criteria search. Where several criteria must be looked at, it is
often better to take things one stage at a time as this will help avoid mistakes
when setting up the search.
The condition part of a query
statement is often just the value or word that you are looking for. e.g. age
> 16, name = Smith, Date of Birth < 12/12/ 2000
The <. >, and = symbols are
known as relational operators. Other ones are /= (not equal
to)<=(less than or equal to) >= (greater than or
equal to).
Search conditions may also include logical operators, OR,
AND, NOR (neither OR, meaning neither of the two choices), NAND (not
AND, meaning not both of the two choices) and several others, You are unlikely
to need anything other than AND, OR in a practical examination
but no list is given in the specification, so you may come across NOR,
NAND.
Although searching may sound a bit complex, you have probably
already done something similar when using advanced search features on a search engine.
Database output
Once you have completed your sorting and searching, you will need
to output the results. For a practical examination this will usually mean a report or a mail merge.
A report is a document produced by the database, usually laid out
in rows and columns. The report can show all
of the search results or just a selection from them. The report format can be
customised with different fonts, colours, etc. in a
similar way to a word processed document.
Reports are usually created by a wizard, you just have to select
the layout and what is to be included and then do some customisation if
necessary.
In an examination, customisation may be specifically asked for, or
it may be something that you need to think about for yourself under the heading
of making the report fit for purpose. The
default layout is unlikely to get full marks.
When creating a report in an examination, pay careful attention to
the question. Common mistakes are:
·
not including all the
required fields / records
·
putting in extra fields /
records
·
putting things in the wrong
order / incorrect sorting
·
using default layout
headings / titles etc. when you've been asked to change things / produce a
report for a specific purpose that the defaults don't fit.
A mailmerge or form letter may be
produced by the database or by a combination of a database and a word processor.
The process is similar for both cases. Which one you use will
depend on the facilities offered by the database you are using and the task you
are trying to perform.
Mailmerges are usually created by a wizard, you just have to
select the layout and what is to be included and then do some customisation if
necessary.
Mailmerges are a little more complex than reports as you need to
think about the content and writing style of the text that goes into the form
letter as well as what fields to include and whereabouts in the letter they
need to go.
When creating a mailmerge in an examination, pay careful attention
to the question. Common mistakes are:
- · not including all the required fields
- · putting in extra fields
- · making errors in the text part of the document. Don't forget to spellcheck.
- writing the text part in the wrong style. e.g. if it's a business letter write it in a formal style
Examples of query criteria
Criteria for Text
| TO INCLUDE RECORDS THAT... | USE THIS CRITERION | QUERY RESULT |
|---|---|---|
| Exactly match a value, such as China | "China" | Returns records where the CountryRegion field is set to China. |
| Do not match a value, such as Mexico | Not "Mexico" | Returns records where the CountryRegion field is set to a country/region other than Mexico. |
| Begin with the specified string, such as U | Like U* | Returns records for all countries/regions whose names start with "U", such as UK, USA, and so on.
NOTE When used in an expression, the asterisk (*) represents any string of characters — it is also called a wildcard character. For a list of such characters, see the article Access wildcard character reference.
|
| Do not begin with the specified string, such as U | Not Like U* | Returns records for all countries/regions whose names start with a character other than "U". |
| Contain the specified string, such as Korea | Like "*Korea*" | Returns records for all countries/regions that contain the string "Korea". |
| Do not contain the specified string, such as Korea | Not Like "*Korea*" | Returns records for all countries/regions that do not contain the string "Korea". |
| End with the specified string, such as "ina" | Like "*ina" | Returns records for all countries/regions whose names end in "ina", such as China and Argentina. |
| Do not end with the specified string, such as "ina" | Not Like "*ina" | Returns records for all countries/regions that do not end in "ina", such as China and Argentina. |
| Contain null (or missing) values | Is Null | Returns records where there is no value in the field. |
| Do not contain null values | Is Not Null | Returns records where the value is not missing in the field. |
| Contain zero-length strings | "" (a pair of quotes) | Returns records where the field is set to a blank (but not null) value. For example, records of sales made to another department might contain a blank value in the CountryRegion field. |
| Do not contain zero-length strings | Not "" | Returns records where the CountryRegion field has a nonblank value. |
| Follow a value, such as Mexico, when sorted in alphabetical order | >= "Mexico" | Returns records of all countries/regions, beginning with Mexico and continuing through the end of the alphabet. |
| Fall within a specific range, such as A through D | Like "[A-D]*" | Returns records for countries/regions whose names start with the letters "A" through "D". |
| Match one of two values, such as USA or UK | "USA" Or "UK" | Returns records for USA and UK. |
| Contain one of the values in a list of values | In("France", "China", "Germany", "Japan") | Returns records for all countries/regions specified in the list. |
Criteria for Number, Currency, and AutoNumber fields
| TO INCLUDE RECORDS THAT... | USE THIS CRITERION | QUERY RESULT |
|---|---|---|
| Exactly match a value, such as 100 | 100 | Returns records where the unit price of the product is $100. |
| Do not match a value, such as 1000 | Not 1000 | Returns records where the unit price of the product is not $1000. |
| Contain a value smaller than a value, such as 100 | < 100 <= 100 | Returns records where the unit price is less than $100 (<100). The second expression (<=100) displays records where the unit price is less than or equal to $100. |
| Contain a value larger than a value, such as 99.99 | >99.99 >=99.99 | Returns records where the unit price is greater than $99.99 (>99.99). The second expression displays records where the unit price is greater than or equal to $99.99. |
| Contain one of the two values, such as 20 or 25 | 20 or 25 | Returns records where the unit price is either $20 or $25. |
| Contain a value that falls with a range of values | >49.99 and <99.99 -or- Between 50 and 100 | Returns records where the unit price is between (but not including) $49.99 and $99.99. |
| Contain a value that falls outside a range | <50 or >100 | Returns records where the unit price is not between $50 and $100. |
| Contain one of many specific values | In(20, 25, 30) | Returns records where the unit price is either $20, $25, or $30. |
| Contain a value that ends with the specified digits | Like "*4.99" | Returns records where the unit price ends with "4.99", such as $4.99, $14.99, $24.99, and so on. |
Criteria for Date/Time fields
| TO INCLUDE RECORDS THAT ... | USE THIS CRITERION | QUERY RESULT |
|---|---|---|
| Exactly match a value, such as 2/2/2006 | #2/2/2006# | Returns records of transactions that took place on Feb 2, 2006. Remember to surround date values with the # character so that Access can distinguish between date values and text strings. |
| Do not match a value, such as 2/2/2006 | Not #2/2/2006# | Returns records of transactions that took place on a day other than Feb 2, 2006. |
| Contain values that fall before a certain date, such as 2/2/2006 | < #2/2/2006# | Returns records of transactions that took place before Feb 2, 2006.
To view transactions that took place on or before this date, use the <= operator instead of the < operator.
|
| Contain values that fall after a certain date, such as 2/2/2006 | > #2/2/2006# | Returns records of transactions that took place after Feb 2, 2006.
To view transactions that took place on or after this date, use the >= operator instead of the > operator.
|
| Contain values that fall within a date range | >#2/2/2006# and <#2/4/2006# | Returns records where the transactions took place between Feb 2, 2006 and Feb 4, 2006.
You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2006# and #2/4/2006# is the same as >=#2/2/2006# and <=#2/4/2006# .
|
| Contain values that fall outside a range | <#2/2/2006# or >#2/4/2006# | Returns records where the transactions took place before Feb 2, 2006 or after Feb 4, 2006. |
| Contain one of two values, such as 2/2/2006 or 2/3/2006 | #2/2/2006# or #2/3/2006# | Returns records of transactions that took place on either Feb 2, 2006 or Feb 3, 2006. |
| Contain one of many values | In (#2/1/2006#, #3/1/2006#, #4/1/2006#) | Returns records where the transactions took place on Feb 1, 2006, March 1, 2006, or April 1, 2006. |
| Contain today's date | Date() | Returns records of transactions that took place on the current day. If today's date is 2/2/2006, you see records where the OrderDate field is set to Feb 2, 2006. |
| Contain yesterday's date | Date()-1 | Returns records of transactions that took place the day before the current day. If today's date is 2/2/2006, you see records for Feb 1, 2006. |
| Contain tomorrow's date | Date() + 1 | Returns records of transactions that took place the day after the current day. If today's date is 2/2/2006, you see records for Feb 3, 2006. |
| Contain a date that occurred in the past | < Date() | Returns records of transactions that took place before today. |
| Contain a date that occurrs in the future | > Date() | Returns records of transactions that will take place after today. |
Access wildcard character reference
Use this set of wildcard characters when you use the Find and Replace dialog box to find and optionally replace data in an Access database.
| CHARACTER | DESCRIPTION | EXAMPLE |
|---|---|---|
| * | Matches any number of characters. You can use the asterisk (*) anywhere in a character string. | wh* finds what, white, and why, but not awhile or watch. |
| ? | Matches any single alphabetic character. | B?ll finds ball, bell, and bill. |
| [ ] | Matches any single character within the brackets. | B[ae]ll finds ball and bell, but not bill. |
| ! | Matches any character not in the brackets. | b[!ae]ll finds bill and bull, but not ball or bell. |
| - | Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). | b[a-c]d finds bad, bbd, and bcd. |
| # | Matches any single numeric character. | 1#3 finds 103, 113, and 123. |
Download Database ( Thanks Muneeb Vohra)
Practical Task From Hodder Education IGCSE ICT Book
Download Data Files from the following link
Task
Database Management
You work for a small garage called
‘Dodgy Dave’s Motors’. This garage sells used cars. Using a suitable database
package, import the file CARS.csv. Assign the following data types to the
field.
Who manufactured the car? Text
Model Text
Color Text
Price that we bought the car for Numeric/Currency/
2 decimal Places
Price that we will sell the car for Numeric/Currency/
2 decimal Places
Year Numeric/Integer
Extras Text
Does the car need cleaning? Boolean/Logical
Some field names are inappropriate.
Create appropriate and meaningful fieldnames for those fields. You may add
another field as a primary key field if your software requires this.
Save your database.
Add the new car to the database.
Make
|
Model
|
Color
|
PPrice
|
SPrice
|
Year
|
Extras
|
Valet
|
Ford
|
Escort
|
Silver
|
4350
|
5285
|
2002
|
Alarm
Central Locking
Alloy Wheels
|
Y
|
Add a new field to the database
called PDate. Add the purchase date of 30 September 2009 for the new record.
A customer would like a car made by
ford. Find the customer a list of all the cars in the garage made by Ford.
The manager would like to see all
the details of all the Fords need valeting.
The manager would like to all the
details of all the cars made by Ford or Vauxhall.
The manager would like to see all
the details of all the cars in stock not made by Ford.
The manager would like to see all
the details of all the cars that have alloy wheels.
The manager would like to see all
the details of all the cars in stock for sale for less than or equal to £4125.
The manager would like to see all
the details of all the cars where the type contains the word file.
Produce a report which:
o Shows all
the cars made by Ford
o Display
only the Make, Model, Color, SPrice, Extras and Valet fields within the width
of a landscape page
o Has your
name on the left in the header of the each page
o Has a title
‘All Ford cars in stock’ centre aligned at the top of the first page
o Has a
subtitle ‘request for Mr David Watson’ right aligned at the top of first page
o Take a
print here
Produce a report which:
o Displays
all the data for all the cars with alloy wheels within the width of a portrait
page
o Has your
name in the report header followed by ‘Cars with alloy wheels’.
o Take a
print here
Export the report into rich text format so that it can be
included in a word processed document.
Export all the details of all the cars with alloy wheels into
a format that can be used to produce a graph.
Hide all the labels and data for the Valet and PDate fields
from the report. Hide the pprice data (but not the title) by setting a black
background.
Find all the cars with a sale price of less than £4000 and for these cars produce
labels which:
o Have a page
orientation of portrait
o Fit two
side by side on the page
o Have a 16
point centre aligned heading ‘Special Offer’ at the top of each label
o Show only
the fields Make, Model, Color, SPrice, Year and Extras, sorted into make and
model order
o Have your
name at the bottom right of each label.
Till page 169
DownloadDownload
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.