1.3.2 Databases

• What is a database?

  • An organised collection of data
  • Allows for easy adding, modification, deletion and searching
• Benefits of using electronic databases:

  • Easier to retrieve, add. delete, update and modify data
  • Easier to back up and make copies of the data
  • Can be accessed by multiple people at the same time + from different locations
• What is a flat file?

  • Consists only a single table, can be created easily using either database/spreadsheet software
• Advantages of a flat file

  • Very simple + quick to set up
  • Require little expertise to maintain
  • Suitable for storing small amounts of data
• Typical uses of flat files

  • Storing contact details
  • Small product database
  • Maintaining a game/music collection
• Disadvantage of flat file

  • Can be inefficient as they include a lot of repetitive data which takes up space
• If only flat file databases were used, they would quickly...

  • Take up unnecessary space
  • Be slow to query
  • Become difficult to maintain
• What is a relational database?


  A database which recognises the difference between entities + uses different tables for each entity
• What is an entity?

  An item of interest about which information is stored
• What is a primary key?


  A unique identifier (field) for each record in a table
• What is a foreign key?

  Attribute which relationally links two tables together
• What is a secondary key?

  An index other than a primary key used to search and sort through the database with more convenience and speed
• What does capturing data mean?

  The process of getting the information that will be stored in the database
• Different methods of capturing data

  • Paper based forms
  • Optical character recognition
  • Optical mark recognition
  • Automated ways e.g. QR codes and barcodes
• What methods do banks use to capture data from cheques?

  • Magnetic Ink Character Recognition
  • Used for all the details apart from the amount which must be entered manually
• What are paper based forms?


  Data input is manual + involves human reading the form + typing the info into a computer based system
• How are errors reduced when inputting the data from a paper based form?

  • Form clearly labelled
  • Complete in black pen and capital letters
  • Use of tick boxes
  • Squares for entering each letter separately
• What is Optical Character Recognition?

  Automatically reads text by interpreting the shape of the letter and works best with printed text than hand written text
• Optical Character Recognition examples

  • Post Office uses OCR software to read postcodes and route mail
  • Road cameras use automatic number plate recognition software to handle congestion charging + identify drivers who are speeding
• OMR (Optical Mark Recognition)

  Suitable for multiple choice tests and lottery tickets

  • Fast and efficient way of collecting data while reducing human error
• Other methods of capturing data include:

  Magnetic strips

  • Chip and pin
  • Barcodes
  • QR codes
• Methods of selecting data examples

  • SQL
  • Query by example
• Structured Query Language


  Allows for fast and efficient retrieval, deletion and manipulation of data using a simple set of commands
• Query by Example


  1st graphical query language, making use of visual tables where the user enters the command and conditions

  • Once the query is built, it is converted into statements that can be executed against the database
  • User doesn't have to remember the finer details of SQL syntax
• Both SQL and Query by example...

  Allow to specify tables, fields, criteria, output sorting and use BOOLEAN expression
• What can be used to manage databases?

  Database Management System
• Database Management System


  Provides a layer of abstraction for the user and programmer as it hides the underlying structure of data and ensures it remains integral by:

  • Preventing the recreation of a duplicate primary key
  • Providing encryption
  • Managing multiple users
• When exchanging data, the following should be considered

  • Common formats used for exchanging data
  • Manual methods of data exchange
  • Automatic methods of data exchange
• Examples of formats used when exchanging data

  XML and JSON
• What is XML and JSON used for

  Readable open format for structuring data and is designed for storing and transporting data
• What are Comma Separated Values files?

  • Format used when exchanging data
  • Each record is on a different line and each field is separated by a comma
  • The structure is fixed so import routines ca be written to extract data from the CSV file
• Examples of manual methods of data exchange

  Memory sticks

  • Optical media
  • Removable hard disk
  • Email
• Automatic methods of data exchange

  Electronic Data Interchange - protocol between two systems to facilitate exchange of data, increasing speed of data transmission and efficiency of processes for users

  • e.g. automatic order placement system for when shop stocks are low
• What is used when no existing field has no appropriate key?

  A composite primary key from two or more fields is used

  • Another field could also be added
• Criteria for First Normal Form

  • All field names must be unique (avoids confusion about which data should be retrieved or updated)
  • Values in the field should be of the same domain
  • Values in the field should be atomic
  • No two records can be identical (unnecessary space + causes inconsistencies if updates aren't performed on all of them)
  • Each table needs a primary key
• Criteria for Second Normal Form

  • Data is already in 1NF
  • Partial dependencies have been removed
• What is a partial dependency?


  When more fields depend on only part of the primary key
• How do you know when to split up a field into a separate table (2NF)?

  If the field or group of fields can be inferred from a field that isn't the primary key, they are suitable candidates for splitting into a separate table
• How do you fix many-to-many relationships?

  • Create linking table
  • Assign primary keys from the two initial tables as the composite key for the new linking table
  • Flipping M:M crows-feet relationship to become two separate 1:M relationships joined by the new table
• Criteria for Third Normal Form

  • Data is already in 2NF
  • Any transitive dependencies have been removed