Chapter 5

Chapter 5

1. Describing terms.

   • Tables
     • Contains data about "things" for example orders, events, purchases, customs + suppliers.
     • The table must have unique name, each field/column must have a unique name, each record/row must be unique, each data item within the field must...
       • ...Contain a single data item.
   • Record
     • Single row within a table, collection of data about a single item or a single event. For example a record might be about an individual, such as a cust
     • Made up fields, can contain different data types.
   • Field
     • Individual data item within a record, each field within a record should have a unique name, field should only contain a single data item. Fields...
     • ...Have individual data types + can have their own validation.
   • Key
     • Primary key: Field, in the table, that allows each record to be uniquely identified, every value of the primary key must be unique.
     • Simple primary key - One which is made up of a single field only. Compound primary key- One which combines more than one field to make a unique value.
     • Secondary key: Field that is identified as being suitable for indexing the data, used to sort the data in a different order to the primary key.
       • Foreign key: Used to link tables together, is a field in one table that is linked to a primary key in another table.

2. Relationships

   • Entities: Single objects or processes, Information about things, each entity becomes a table, entities linked by relationships.
   • One-to-many: One record in a table is linked to many records in the 2nd table.
   • One-to-one: Where one record in a table is linked to only one record in another table.
   • Many-to-many: The many link goes both ways between 2 tables.

3. Referential Integrity: Ensuring that for every record in a table with a foreign key, there is a record in the corresponding table with the primary key

4. Normalisation: Process applied to data structures, decreases redundancy, Increases integrity.

   • 1NF: Every data value is atomic, No repeating data, Primary key.
   • 2NF: Must be in 1NF, Non-key attributes are dependent on the entire primary key.
   • 3NF: Must be in 2NF, No functional dependency between non-key terms.
   • Advantages of normalisation: Removes redundancy, increases consistency, increases integrity, easier maintenance, flexibility for future expansion.
     • Disadvantages...
       • Reduced database performance. Problems with historical calculations.

5. Database Dictionary.

   • Called a database about databases, contains metadata (Data about data),different database packages will contain slightly different information.
   • Contains design details about the database, Includes table name, field name, data type, length, validation relationships + security.

6. Queries + Parameters...

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