Chapter 5

Chapter 5

1. Database components

   • Table - data structured into rows and columns requires a primary key
   • Records - a single row within a table made up of individual fields
   • Field - An individual data item within a record
   • Key - a field used for identification
     • Primary - allows a record to be uniquely identified. Can be combined to make a composite key
     • Secondary - a field which can be used to index data
     • Foreign key - a field which is linked to the primary key in another table or entity
   • Entity - A Thing which can be identified, a logical or physical storage unit

2. Relationships

   • One-to-one
     • One instance of X to one instance of Y
     • Uncommon
   • One-to-many
     • Common
     • One instance of X relates to many instances of Y
   • Many-to-many
     • Foreign key on many side and primary on the one
     • Many instances of X relate to many instances of Y

3. Normalisation

   • 1NF
     • Primary key
     • Unique field names
     • no repeating fields
     • Atomic fields
   • 3NF
     • All 2NF conditions met
     • All non-key fields are dependent on the primary key
   • 2NF
     • All 1NF conditions met
     • All of the non-key attributes can be found using the primary key

4. Data dictionary

   • Defines aspects of a database
   • e.g. Table name Keys Relationships

5. Queries

   • Static - Predefined parameters
   • Dynamic - Parameter input on each run
   • Simple - Only one parameter
   • Complex - multiple parameters
     • AND
     • OR
     • NOT
     • *xyx*
     • x* (starts)
     • *x (ends)
     • Standard mathematical operators