Database Applications

• ΣΑ


  Database Applications
• Query


  Searches and filters data in a database that satisfies the criteria
• Table


  • Consists of a number of records
  • A record is represented as a row in a table
  • A field is part of a record that stores a single data item, represented by a column
  • A key field uniquely identifies one record
• Form


  Used to collect data or create a new record, modify an existing record or view records already stored in a table
• Report


  • Used to present results from queries in an effective manner
  • Layout of reports can be customised, such as grouping similar data or presenting data in ascending or descending order
• Data types


  • Text
  • Number
  • Date/time
  • Currency
  • Autonumber
  • Hyperlink
  • Yes/No
  • OLE Object
  • Attachment
  • Calculated
  • Lookup
• Database Relationships


  • Link two or more tables in a database using a relationship type
  • Require a common field stored in each table, with the key field from one table stored in another table as a non-key field (or foreign key)
  • Relationship types: one-to-one, one-to-many, many-to-many
• Big Data


  • Refers to large amounts of data that have the potential to be mined for information
  • Described by the 3Vs: Volume (amount of data stored), Variety (different types and formats of data), Velocity (speed at which data can be processed)
• Data Analytics


  • Involves analysing large volumes of data to produce useful information
  • Beneficial for organisations to make decisions and plan for future growth
  • Specialised software such as data mining and statistical analysis are used to process vast quantities of data
  • Preparing and processing data is more challenging when data is in different formats (structured/unstructured)
• Spreadsheet


  A spreadsheet contains one or more worksheets
• Worksheet


  • Presented as a grid in the form of rows and columns
• Spreadsheets


  • Designed to allow applications to perform calculations and recalculations automatically
• Cells


  Can store data in a variety of forms, e.g. text, number, date, formula etc.
• Spreadsheet data formatting


  • General
  • Number
  • Currency
  • Date
  • Time
  • Percentage
• Formatting


  • Can control the appearance of a cell, e.g. aligning text, changing the font or adding a border or background colour
• Conditional formatting


  Allows cell shading or font colour to be applied to a cell or group of cells if a specified condition is met
• Data validation


  Involves the automatic checking of data at the input stage to ensure it is reasonable, sensible and within acceptable limits
• Data validation checks


  • Presence check
  • Length check
  • Type check
  • Format check
  • Range check
• Lookup function


  Uses the value in a selected cell to lookup a match in a column of a lookup table (vertical lookup) and returns a value from a specified column from the same row of the lookup table
• Absolute cell reference


  When a formula is copied to other cells, part of the cell reference does not change as the formula is modified
• Macros


  A small program written to perform a repetitive task automatically
• Simple functions


  • SUM
  • Average
  • Max
  • Min
• Formulas and functions allow a spreadsheet to perform calculations and automatic re-calculations
• When the formula or function is entered into a cell it can be replicated quickly down a column or across a row using the 'Fill' function