Databases and Transactions

• To ensure data integrity, transaction processing must conform to a set of rules, ACID

  A - Atomicity - A change to the database is either completely performed or not at all

  C - Consistency - Any change in the database must retain the overall state of the database

  I - Isolation - A transaction must not be interrupted by another transaction, they must be in isolation so other users cannot access the dat concerned

  D - Durability - Once a change has been made, it must not be lost to a system failure
• Flat file database - Made of only one table

  • Created very easily
  • Often saved as CSV file
  • Simple and easy to maintain
  • Very inefficient beyond simple applications

  Relational databases - Made of 2 tables which are linked by a relationship.

  • Tables need a common field for relationship to work
  • Many types of relationship (1:1, 1 : many, many : many)
• Primary key - key which is guaranteed to be unique to each record in a table

  Foreign key - Field which links two tables together
• 4 ways to handle data:

  • Capturing data (how do we get the data into the database?)
  • Selecting data (how do we query and retrieve the data?)
  • Managing data (how do we manage, manipulate, add etc)
  • Exchanging (how do we exchange data with other people/systems)
• Capturing data:

  • Vast amount is done using paper-based data capture forms
  • We can speed up and automate data input by using optical character recognition, it automatically reads text by interpreting shape of letters
  • OMK - used in multiple choice tests
  • QR codes, barcodes, sensors, etc

  Number of tactics can be implemented to prevent someone inputting wrong data
• Selecting:

  • Query languages like SQL, QBE
  • QBE uses visual tables where the user would enter commands and conditions
• Managing:

  • Some languages like SQL also function as a database manipulation language (UPDATE, DELETE, INSERT)
  • Can also use Database Management Systems (DBMS) which provide a lot of abstraction for the user and programmer
• Exchanging:

  • Can be done using XML or JSON files, which are human-readable, open formats for structuring data which can be transported across systems
  • CSV (comma separated values) can also be used
  • Once format is agreed, methods of transporting data can be decided (flash drives, optical media, hard disk, email etc)
  • Better method is having the databases interact with each other so they can read and write from each other, one example is Electronic Data Interchange (EDI)
• The concept of splitting tables in a database and arranging the data to move it to 3NF is called normalisation
• To get to 1NF:

  • Field names unique
  • Values in fields should be from the same domain
  • Values in fields should be atomic
  • No two records can be identical
  • Each table needs a primary key
• To get to 2NF:

  • Data is in 1NF
  • Any partial-key dependencies have been removed
• Partial-key dependency - one or more fields only depend on part of the primary key

  To fix this, create a relational database
• To fix a many to many relationship, create a linking table, and assign the two primary keys of the initial two tables as the composite key for the new linking table
• Value Domain - Type of value (e.g CSA and Craig Sargent aren‘t the same domain)
• Atomic - for every field and record interaction, there should only be one value
• To get to 3NF:

  • Data is in 2NF
  • Any non-key dependencies have been removed

  This means we should not be able to determine the value of one field using the value of a non-key field