Databases

Databases

1. A persistent, organised store of data on a computer system.

2. Importance

   • Need to be: accurate, up to date, available for those who need to use them, protected fro those who shouldn't have access
   • Admins go to great lengths in order to protect data against: errors, loss, insufficient data, inconsistencies, unauthorised access
   • Errors can result in embarrassment, financial loss, life or death situations
   • Databases help organisations access, search, sort, group, copy and protect data

3. Data security and integrity

   • Data security refers to keeping it safe.
     • Data can be lost by: a catastrophe, an accident (deleting data), a malicious action
     • Data is protected by: making regular backups (in safe places e.g. the cloud), having a mirror database, restricting access, keeping audit trails of who has accessed data
   • Data integrity means that the data reflects reality (correct and fit for purpose)
     • Can be maximised by suitable validation and software that prevents inconsistent states
       • Validation is checking data when it is input. It is carried out by software.
         • It conforms to rules like length, data type, range, presence, lookup, format checks, and a check digit checks data is as previously entered
       • Verificaton is checking that the data is correct; can be a simple visual check against the source data or an algorithm that checks two copies entered independently and flags upinconsistensies

4. Operations

   • CRUD (create, read, update, delete)
     • Making suitable views (subset of data) for each user increases efficiency and reduces risk
   • Data matching compares different databases to look for particular relationships (e.g can uncover fraud)
   • Data mining is a process that looks in many different unrelated databases to show up unexpected relationships that may have not been noticed before

5. Models

   • Flat file
     • Simplest - just rows (records) and columns (fields). Easy using just a spreadsheet
     • Limited, data might be repeated unnecessarily (data redundancy), inconsistent spellings (problems with searching database), some data items might be updated and others not
   • Hierarchical
     • Useful when making an inventory (compiles items with one another)
     • Logical grouping of data in subgroups (they make it easier to locate data)
   • Relational
     • Most useful and common
     • Store data in separate tables which are linked together so related data can easily be extracted
     • Each table contains data about an entity (something in real life about which we store data) so data is stored once only so it's always up to date version
       • Entities should link in one-to-many relationships (a record links with many members of a different table)
         • e.g. clients, bookings and rooms (client and room can have many bookings, but a booking only one of each)
     • Primary key is a unique field (usually a reference) that links to a foreign key so avoids redundancy and errors

6. DBMS

   • Database Management System is a software that looks after a database
     • Allows admins to create database applications, protect data, run queries to extract data and keep data consistent and accurate
     • It acts as a go-between, connecting applications to the underlying data
       • It is important to separate them so that: programmers don't have to worry that their applications might damage existing data structures, new applications can be written without restructuring the data, data can be easily shared between applications, data remains consistent because there is only one copy
         • In multi-user databases, record locking means that a record is only open for editing for a single user, and the other has to wait
   • Common tools
     • Provides tools for creating and modifying tables
     • Chooses data type of a field so software can validate and process data easily
     • Makes forms with objects such as buttons to make data input and ouput easy to use
     • Makes reports which summarise, group and select data
     • Graphing features means programmers don't have to write their own display routines
     • Modules are units of software which take care of some particular functionality of a database
     • Groups and priviliges can be assigned
     • Automatic backup and data recovery tools
     • Queries extract a subset of data
       • Queries by example uses a GUI to assemble fields and conditions required
         • SQL means programs can be written to extract data (provides operators to check conditions AND and OR)