HIT191 > Widgets & Gadgets: Tables
Tables

Having covered an overview of relational databases in the last topic, this topic explores tables in greater depth. As the repository of the data, tables are a core component and implementing them correctly is a critical task. This topic looks at individual tables before the next topic examines linking tables together.


Develop your skills - Tables

Stable 2007 textbook
Stable 2003 textbook

SET TEXT
Chapter 3
pp. 367-397
or
Chapter 3
pp. 365-395

The following reading discusses some techniques used when setting up tables. When you have finished the reading you should understand:
  • the unique property of a primary key
  • the field properties including
    • data types,
    • field description,
    • field sizes,
    • format,
    • input masks,
    • default values,
    • required and
    • allow zero length

http://www.pearsoned.com.au/stable
Stable website
Note: In this and subsequent chapters, you do not need to enter data. Instead, download the tables from the Stable website.

TASK 1: Complete the Develop Your Skills 5, p. 395. If you are still not feeling very confident, complete Develop Your
Skills 4, p. 393.


Compare ideas
Compare ideas
TASK 2: Consider the address database from the last topic with one table - Contact. Each contact or person would be one record. Fields would be such things as first name, surname, address (could be broken up into street, suburb, postcode, city), work number, home number, email, mobile, etc.
  • What would be a possible primary key this table?
  • Can you see any problems with your choice?


Tips & tricks icon Tips & Tricks

Wildcards (p.392)

The three wildcards you have learnt, namely *, ? and # are used by more programs than Access. Most databses (e.g. library databases) use them as well as quite a few other programs (e.g. Linux/Unix operating systems).

Lookup Tables

Use these where ever possible - as discussed below these are useful in helping to ensure data accuracy.


Putting it to work: Data accuracy

It is important to realise that a database is only as good as that information (data) being stored in it.

If the information stored in a database is wrong, then regardless of how well designed and implemented the database is, it will be of very limited use.

Or as the expression GIGO describes it: Garbage In, Garbage Out.

Every attempt should be made to ensure the accuracy of the information being stored in your database. Setting of field types and properties is a good way to do this.

Compare ideas
Compare ideas
Field types
  1. What field type (eg. text, numeric, date, etc) would you set for the following student information?
    • a date of birth
    • a name
    • a student number (a six digit number such as 817362)
    • a phone number
  2. How can field types help to limit errors?

Compare ideas
Compare ideas
Field properties

How could field properties such as:

  • field sizes
  • input masks
  • validation rules
  • default values
  • required and allow zero length

be used to improve the accuracy of the data stored in a database?


Extension - optional
Extensions
InputMask
Property

Input masks are beyond the scope of this course but if you would like to know more, read the link provided.

 

What have I learned?

After completing these activities you should:
  • understand the unique property of the primary key;
  • recognise the importance of ensuring data accuracy;
  • be able to make informed decisions in using field properties to help ensure data accuracy and reliability.


Next section --> Relationships