Database Tools and Techniques: Tables

Database Tools and Techniques: Tables

1. Table Characteristics

   • Rows = Records
     • Add
       • Manuallly
       • External Source
         • Spreadsheet
     • Edit
     • Delete
   • Rows and columns
   • Columns = Fields
     • Types
       • Alphabetic
       • Numeric
       • Boolean/logical
       • Web
         • Hyperlink
       • Lookup Wizards

2. Validation

   • Correct data type
   • Correct size
   • Ranged things
   • To check for errors
   • Format
     • Postcode
       • Input mask

3. Validation Examples

   • Check Digit
     • last one or two digits in a code are used to check the other digits are correct
     • Bar code readers in supermarkets use check digits
   • Format Check
     • A National Insurance number is in the form LL 99 99 99 L where L is any letter and 9 is any number
     • Checks the data is in the right format
   • Spell Check
     • Looks up words in a dictionary
     • When word processing
   • Presence check
     • In most databases a key field cannot be left blank
     • Checks that data has been entered into a field
   • Lookup table
     • There are only seven possible days of the week
     • Looks up acceptable values in a table
   • Length check
     • Checks the data isn't too short or too long
     • A password which needs to be six letters long
   • Range Check
     • Checks that a value falls within the specified range
     • Number of hours worked must be less than 50 and more than 0

4. Relationships

   • One to many
   • One to one
   • many to many

5. Indexing

   • Primary Key
   • Foreign Key