ICT chapter 8: Databases

ICT chapter 8: Databases

1. Database: is an organised collection of related data. It is possible to add, edit, manage and retrieve data from a database. The structure of a database allows data to be found and used quickly.

2. Data types

   • Number (integer)
     • A whole number (positive or negative) but no fractions.
   • Number (real)
     • Any number, including whole numbers and fractions.
   • Text/string
     • Letters, numbers or other characters such as punctuation marks.
   • Data/Time
     • Any time or date; the way the time or date is displayed depends on how it is formatted.
   • Currency
     • Money £ (including foreign currency $ or €)
   • Boolean
     • Two opposing states e.g. true or false, male or female, yes or no.
   • Lists
     • A list of values, sometimes called a “drop-down” list; only data from the list can be selected.
   • Picture
     • Image data, from a digital camera, mobile phone, the internet, a clip-art library or scanned in image.
   • Sound
     • A beep from a barcode reader or popular song.
   • Video
     • A video introduction to computer game or a downloaded film/TV programme.

3. Files, records and fields

   • A field is a single item of data within a record.
   • A record consists of a collection of related fields.
   • A file (Known as a table in Microsoft Access) is a collection of related fields

4. Key Field: A Key fields uniquely identities each record. No two records have the same data in their key field. Also known as Primary Key.

   • The purpose of the key field is to be able to refer to an individual record using a single field. This often takes the form of an identity number, membership number or bar code.

5. Validation: Data entered in to a database must be sensible or we get GIGO. Validation methods can only ensure reasonable and sensible data is input in to a system, but there is no certainty that the data is correct.

   • Validation rules are needed for some fields. As data is entered it is checked to see that it obeys the rules; any data that does not obey the rules is not accepted. You can also set validation text – the text that appears if the data is not acceptable.
   • Lookup list: If the data entered consists of a limited number of possible entries then the computer can compare the entry with a list of allowed answers.
   • Presence check: Some data has to be entered.
   • Length check: The length of the input is checked for the number of characters used.
   • Format/Picture check: If the data entered has a particular pattern e.g. a postcode or car reg then an input mask can be used to force the data to conform to the rule.
   • Type check: This ensures that the data is of the correct type.
   • Range check: The data entered must be in a sensible range.

6. Reports: A report is a printed document that contains data from the database, organised and analysed in a certain way. A report from a student database might list the students in each class in alphabetical order.

7. Forms: Forms are used to make data entry more attractive and secure rather than entering data into raw tables.

8. Calculated fields: Not all data needed in a report may be present in a database. For example a person’s age is never included because it changes day by day. If you want to use age in a report it must be calculated. This result is known as a calculated field.

   • The solution is for the database to use the recorded date of birth to calculate the age by subtracting the date of birth from the date of the report.

9. Uses of a database:

   • Add, change or delete records
   • Search for a particular record
   • Sort the records into a given order
   • Print reports about some aspect of the database

10. Updating records: Records do not stay the same forever. They will need to be updated at some point. For example, people get married and change their surname, people move and change their address or prices of goods change.

    • Add a new record when a new pupil arrives to a new school
    • Edit existing records when someone move house
    • Delete an old record when an employee leaves a job

11. Searching for data: One main use a database is to find information quickly. To search for information you use a query. You are “querying the database”. A query specifies what records you are searching for and what fields from the records you would like to display.

    • Simple query search (keyword search) – looks for data in one field.
    • Complex query search – looks at data with multiple criteria.

12. Query operators

    • =
      • Equals
    • <
      • Less than
    • >
      • Greater than
    • <>
      • Not equal to
    • AND
      • Data is only listed where both conditions are true
    • OR
      • Data is only listed where either or both conditions are true
    • LIKE
      • Where a perfect match is not needed

13. Wildcard characters: You use wildcard searches in a query for data that you only know part of. For example find all the surnames beginning with ‘S’ you might write: surname LIKE “*S”

    • *
      • Any number of characters
    • [ ]
      • Any character in the brackets
    • ?
      • Any character
    • !
      • Any character not in the brackets
    • [ - ]
      • Any character within a range of characters

14. Sorting data: Results of queries can be presented in a sorted order e.g. alphabetical or in numerical order. Sorts can also be in ascending (A-Z) or descending (Z-A) order.

15. Security issues with databases

    • If data is secure then:
      • It cannot be destroyed
      • It cannot be accidently or maliciously altered
      • It cannot fall in to the hands of unauthorised people
    • Consequences for a business where data is not secure include:
      • Loss of customers – unhappy customers go elsewhere
      • Payments for gods not being received
      • Bad publicity – all businesses rely on a good reputation
      • Management unable to make decisions due to lack of information

16. Software methods for keeping data secure:

    • Passwords:
      • Authorised users are given a username. Each user also has a password that proves the user is really the one whose username is given.
      • Using passwords is a method of authentication.
    • Access rights (file attributes) can be places on individual files to give:
      • No right to access the file
      • Read access (user can view file but not alter it)
      • Write access (allowing the user to alter the contents of the file)
    • Back-ups:
      • A back-up is a second copy of the data. If a problem arises with the data file, then the back-up copy is used.

17. Benefits of using a database

    • Fast access: using a database to find data is much faster compared to paper in a filing cabinet.
    • Easy to edit: a database allows for data to be easily updated and changed.
    • Validation: you can set checks on the data being entered to ensure no inappropriate data gets into the system.
    • Reports: results of searches can be used to print reports that may help management in their decision making.
    • Sharing: database can be shared with any number of users on a network. Any user can edit the data.
    • Security: passwords may be set so that no unauthorised user can access the database, which may contain personal and sensitive data.