Chapter 5

Chapter 5

1. Database Terminology

   • Table - a data structure made up of rows and columns that contains data about the items.
   • Record - a single row within a table.
   • Field - an individual data item within a record.
   • Entity - a table.
   • Attribute - fields within an entity.

2. Keys

   • Primary key - a field, in the table, that allows each record to be uniquely identified.
     • Simple - made up of a single field only.
     • Composite - combines more than one field to make a unique value.
   • Secondary key - a field that is identified as being suitable for indexing the data.
   • Foreign key - a field in one table that is linked to a primary key in another table.

3. Relationships

   • One to one - any instance of the entity X can be associated with only one instance of entity Y.
   • Many to many - single instance of an entity can be associated with many instances of another entity.

4. Referential Integrity

   • Referential integrity – for every foreign key there must be a related primary key value.
   • Makes sure that it is impossible to enter a reference to a link which does not exist.

5. Data Dictionary

   • Contains metadata (data about data).
   • Table name - name of table, must be unique.
   • Field name - each field uniquely identified.
   • Field data type - data type allocated to each field.
   • Field length - no. of characters allocated for field contents.
   • Field default value - value appears on creation of new record.
   • Field validation - any validation applied to field.
   • Table security - who has access to edit table.
   • Keys - primary and foreign keys.
   • Indexes - any field which is indexed.
   • Relationships - relationships between tables identified.

6. Queries

   • Simple - a query where there is only one parameter.
   • Complex - a query with more than one parameter. Makes use of AND, OR and NOT to join parameters.
   • Static - parameter is hard coded into the query and cannot be changed by the end user.
   • Dynamic - request for the parameter given to the end user at run time, usually by a dialogue box.

7. Normalisation

   • UNF
   • 1NF
     • All data is atomic.
     • Record has a primary key.
     • Each field name is unique.
     • No repeating fields.
   • 2NF
     • Table is in 1NF.
     • All non-key fields be found out by using the entire primary key.
   • 3NF
     • Table is in 2NF.
     • All non-key items are fully dependent on the primary key.
   • Evaluation
     • Advantages
       • No redundant data.
       • Easier to maintain.
       • Efiicient structure.
       • Flexible, can be altered without major redesign.
     • Disadvanatges
       • Reduced performance.
       • Can't store historical data.
       • More storage space required.