Topic 8 - Databases

Topic 8 - Databases

1. Types of data

   • Integer
     • A whole number (positive or negative) but no fractions.
   • Real
     • Any number, including decimal numbers and fractions.
   • String
     • Letters, numbers and characters - phone numbers go here
   • Data/time
     • Any time or date
   • Currency
     • Money (including foreign $ or Euros)
   • Boolean
     • On/off, true/false, yes/no
   • Lists
     • A list of drop down values
   • Picture
     • Images
   • Sound
     • Beep from a barcode reader or popular song
   • Video
     • Introduction to program or downloaded film.

2. Database validation

   • Range check
     • Data within a defined range.
   • Type check
     • Data is the correct type of data.
   • Length check
     • Data is a defined set of numbers.
   • Look up list
     • Data is limited to a number of possible data entries.
   • Picture or format check
     • Data entered conforms to a particular pattern.
   • Input mask
     • Data conforms to a rule (pattern).
   • Presence check
     • If selected, ensures data is selected.

3. Wildcard

   • *
     • Any number of characters
     • "Like S*" finds Smith, Smithers, Scott
   • ?
     • Any character (one ? stands for one character)
     • "Like Ca?" fins Cat, Cab, BUT NOT Cart
   • [ ]
     • Any character in the brackets.
     • "Like 10[TS]" finds 10T or 10S
   • [ - ]
     • Any character within a range of characters.
     • "Like 10[B-M]" finds 10D, 10G, BUT NOT 10T
   • !
     • Any character NOT in the brackets.
     • "Like 10[!TW]" finds 10A and 10Z but not 10T or 10W

4. Key definition

   • Database is a collection of related data or information that is held together in an organised and logical way.
   • A record consists of related fields (eg a row).
   • A field is a single item of data in a record.
   • A table (file) is a collection of related records.
   • A field heading is what identifies what is in a field - not a record.
   • A primary key uniquely identifies each record. No two records have the same data in their PK field. The purpose of the key field is to be able to refer to an individual record using a single field.
   • Validation rules check to see that data entered is sensible and reasonable.
   • A simple query looks for data in one field.
   • A complex query looks at data with multiple criteria.