Chapter 5

Chapter 5

1. Terminology

   • Table
     • About entities and is made up of records. Made up of rows, columns and has a unique name.
   • Record
     • Single row within a table containing data about a single event.
   • Field
     • An individual data item within a record, it contains a unique name
   • Primary Key
     • Field that allows a record to be uniquely identified.
   • Foreign Key
     • Links tables together, field in one table that is linked to the primary key in another
   • Entity
     • Anything you can store data about (people, events, things etc)
   • Attribute
     • Characteristics of an entity
   • Duplicate Data
     • Exists in two tables and is removed by normalisation
   • Referential integrity
     • Makes sure it is impossible to enter a reference in the database to a link which does not exist

2. Relationships

   • One to one
     • One record in a table that can only be linked to one record in another table
   • One to many
     • One record in one table that can be linked to many records in another table
   • Many to many
     • Many records in one table that can be linked to many records in another table

3. Normalisation

   • ONF
     • When data is first collected and put into the table; no formatting
   • 1NF
     • Must have a primary key, unique field names, no repeated fields. Data is atomic.
   • 2NF
     • 1NF, non-key fields found using the primary key
   • 3NF
     • 2NF, non key fields dependant on the primary key
   • Advantages;
     • Increases integrity
     • Removes redundant and duplicate data
     • Easier maintainence
   • Disadvantages
     • Reduced database performance
     • Not appropriate for some data types

4. Data dictionary

   • Table name
   • Field name
   • Field length
   • Field data type
   • Field validation
   • Keys
   • Field default value
   • Indexes
   • Relationships
   • Table security

5. Parameter Queries

   • Simple
     • Only one parameter value
   • Complex
     • More than one parameter value
   • Static
     • Cannot be changed "hardcoded"
   • Dynamic
     • Asks the user what to search for