Chapter 5

Chapter 5

1. Database Terms

   • Table
     • About "things" and made up of records. Rows, columns and a unique name
   • Record
     • Single row within a table, data about a single event, fields and data types
   • Field
     • Individual data item within a record, unique name
   • Primary Key
     • Field that allows a record to be uniquely identfied
   • 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, singularly and written in capitals
   • Attribute
     • Characteristic of an entity
   • Duplicate Data
     • Exists in two tables, 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 one 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. Parameter Queries

   • Simple
     • Only one parameter value
   • Complex
     • More than one parameter value
     • AND, OR, NOT, >, <
   • Dynamic
     • Asks the user what to search for
   • Static
     • Cannot be changed, "hardcoded"

4. Normalisation

   • 0NF
     • When data is first collected and put into the table
   • 2NF
     • 1NF, non-key fields found using primary key
   • 3NF
     • 2NF, non-key items dependent on primary key
   • 1NF
     • Must have a primary key, unique field names, no repeated fields, data is atomic
   • Advantages/ Disadvantages
     • increases integrity, data consistency, removes redundant and duplicate data, easier maintenance
     • Reduced database performance, not appropriate for some data types

5. Data Dictionary

   • Table Security
     • Who has access to the table
   • Keys
     • Primary and foreign
   • Table Name
     • Unique name for each table
   • Field Data Type
     • Data type allocated to each field
   • Field Length
     • Number of characters allocated to field
   • Field Default Value
     • Value that automatically appears
   • Field Validation
     • Any validation applied
   • Indexes
     • Any field indexed
   • Field Name
     • Each field is indentified
   • Relationships
     • Between tables