COMM 1A Application Building

1

• COMM 1A Application Building
• Unit 2

2
Introduction

• Objectives
• To be able to select appropriate Data Types
• To be able to select and apply appropriate data
  Validation
• To be aware of the distinction between Indexes
  and Sorts and how they may be applied in Access
• To understand the need for Relationships and how
  they are applied through Foreign Keys

3
Data Types

• Choice of Data Type
• Think about how this is going to be processed
  into Information
• Formatting Data
• This is done to ensure consistency and efficiency
• Data Design vs. Data Entry
• It is necessary to specify the fields, their data
  type and formatting before you start creating
  records

4
Data Type Example (1)

• Data Type Format Size
• Customer
• Family Name Text gt 20
• Given Name Text gt 20
• Address Text gt 50
• Purchase Order
• Order number Number Integer N/A
• Date Date Short date N/A

5
Data Type Example (2)

• Data Type Format Size
• Purchase Order Line
• Line number Number Integer N/A
• Number of items Number Integer N/A
• Stock
• Item Text None 20
• Cost of item Currency Auto N/A

6
Validation

• To ensure that data is only entered in a specific
  format
• Validation Rules
• Specifies the format of the data that needs to be
  entered
• Validation Text
• Provides guidance to the user in the event that
  they beak the Validation Rule

7
Validation example

• Validation rule Validation text
• ltgt 0 Entry must be a non-zero
• value.
• gt 1000 Or Is Null Entry must be blank or
• greater than 1000.
• Like "A????" Entry must be 5 characters
• and begin with the letter "A".
• gt 1/1/96 And lt1/1/97 Entry must be a date in
  1996

8
Data Organisation

• Indexes
• An index is a separate file that remembers where
  records are located that match given criteria
• Initial low overhead, but always present
• Many indexes can be created on the same data
• Sorts
• A Sort re-arranges the data on one or more fields
• Initial high overhead but Rapid and Efficient
  access
• New records require the Sort be performed again
• New criteria requires the re-sorting of data
• Stable data

9
Normalisation

• Un-Normalised Form
• First Normal Form
• No attribute should have more than one value per
  entity occurrence. A repeating group is an entity
  in its own right and is the many end of a 1M
  relationship with the original entity
• Second Normal Form
• Where an entity has a compound identifier, then
  any set of one or more attributes which are only
  dependent on part of the identifier should be a
  separate entity
• Third Normal Form
• Where an entity has a set of one or more
  non-identifier attributes which are dependent on
  another non-identifier attribute, then that set
  of attributes should become a separate entity
• Meldrum, M. Lejk M, Guy, P (1993) SSADM
  Techniques and Introduction to Version 4,
  Chartwell Bratt
• Lejk M, Deeks D (2002) An Introduction to Systems
  Analysis Techniques, Addison Wesley

10
Normalisation Example
11
Relationships (1)

• 11
• Often serve little purpose and can be merged
• Be prepared to justify your design decisions
• 1N
• In general, table relationships are resolved as a
  series of one to many links
• NM
• For any one instance of a record in one table you
  must be able to isolate all related records in
  the other
• NM should always be resolved into 1N

12
Relationships (2)

• Referential Integrity
• Ensures that it is not possible to have an
  instance of a child record without a master
  record
• Ensures that it is not possible to delete a
  master record whilst there are related child
  records
• Foreign keys
• Key fields from the master table are inserted
  into the child table, so that for any one
  instance of the master record there can be 0, 1
  or many child records
• The foreign key fields are Indexed fields that
  are required but there can be duplicates

13
Relationships example (1)

• Customer Customer
• Family Name Jones,Ann,1 Hastings Way
• Given Name Smith,John,3 Davis Road
• Address Smith,Peter,53 Front Street
• Purchase Order Purchase Order
• Order number 10021,1/5/98,Smith,John
• Date 10022,1/5/98,Jones,Ann
• Family Name 10023,2/5/98,Jones,Ann
• Given Name

14
Relationships example (2)

• Purchase Order Line Purchase Order Line
• Line number 1,10021,6,001
• Order number 2,10021,3,002
• Number of items 3,10021,1,003
• Item code 1,10022,2,001
• 1,10023,5,004
• Stock Stock
• Item code 001,apple,0.25
• Item 002,orange,0.30
• Cost of item 003,pineapple,1.23
• 004,banana,0.12

15
Summary

• Breaking physical data into related tables
  improves efficiency
• Each of the records needs to be uniquely
  identifiable so that it can be re-combined
• Keys and relationships provide the mechanism to
  achieve this