Databases

• Database


  A collection of data items and links between them, structured in a way that allows it to be accessed by a number of different application programs
• Key Database Definitions


  • Table
  • Records
  • Field
  • Primary key
• Table


  A structure where data is organised in rows and columns, used to store data about related items
• Records


  A collection of data items, which may be of different data types, all relating to an individual or object
• Field


  A part of a record designed to hold a single data item of a specified data type
• Primary key


  A field that uniquely identifies an individual record in a table
• Foreign key


  The primary key in one table which is included in another table to form a relationship (a link) between the two tables
• Data Types


  • Autonumber
  • Text
  • Date and Time
  • Number
  • Currency
  • Yes/No
• Autonumber


  A number field, where the database chooses the next available number automatically
• Text


  Used to store a string of characters (letters & numbers) up to a certain field size
• Date and Time


  Can store a specific date and time, can be formatted to display in different ways
• Number


  Stores a numeric value, could be an integer or decimal number
• Currency


  Stores a value of a specific amount of money, with set currency symbol such as $ or €
• Yes/No


  Stores only one of two options, Yes or No, can be displayed as a checkbox in the database
• Validation Checks


  • Presence Check
  • Length Check
  • Type Check
  • Format Check
  • Range Check
• Presence Check


  Ensures that data has been entered into a field
• Length Check


  Ensures that data entered has the correct number of characters or doesn't exceed the amount
• Type Check


  Ensures that data entered is of the correct data type
• Format Check


  Ensures that data entered is of an exact format (possibly matching an input mask)
• Range Check


  Ensures that data entered is within an acceptable range (possibly between a lower and upper boundary)
• Database Relationship


  A link between two tables in a database using a field that exists in each of the tables
• To make a database relationship, the primary key of one table will be stored as a foreign key in another table
• Types of Database Relationships

  • One to One
  • One to Many
  • Many to Many
• Data Capture Form


  A database object that can be used to enter, edit or display data from a table or a query
• Good Data Capture Form


  • Has a user friendly interface with good colours and layout
  • Hides the complexity from the user and gives help for completing the form
  • Provides options for the user to choose from to speed up data entry
• Query


  A question used to search selected information from a database
• Query Conditions


  • Equal to-=
  • Between 2 values
  • AND-Both conditions
  • OR-either condition
  • Greater than->
  • Greater than or equal->=
  • Less than-<
  • Less than or equal-<=
• Report


  Allows tables and results from queries to be presented in a user friendly way
• Grouping a Report


  • Records can be grouped by similar data
• Sorting a Report


  • Records can be sorted in ascending or descending order
• Data validation aims to make sure data is sensibly , reasonable , complete and within acceptable boundaries