Chapter 5: Databases Flashcards
Database
A store of data that is persistent, related, and organised.
- Persistent: held on a permenant medium e.g. disk, flash memory
- Related: isn’t just a haphazard collection of facts
- Organised: data is stored in a structured, and predictable way so it can be easily processed
Databases can be viewed at three levels:
1) External view: what the user sees, designed to be useful for a particular job.
2) Conceptual view: how the data is organised, design of the tables, and how they are linked.
3) Physical view: how the data is stored on the storage medium. Designers, and users are not concerned with this detail, it is looked after by the database software.
Importance of databases
Most organisations depend on their database in order to operate so databases need to be:
- Accurate
- Up to date
- Available to those who need them
- Protected from those who should not have access
Database administrators protect their data from:
- Errors
- Loss
- Insufficient data
- Inconsistencies
- Unauthorised access
Errors in a database can result in:
- Embarressment e.g. sending bills to dead customers
- Financial loss e.g. bank account is wrongly changed
- Life, or death situations e.g. incorrect navigational data in an aircraft.
Data security, and data integrity
Data security = Keeping data safe so it isn’t lost.
Data can be lost because of:
- A catastrophe e.g. fire, flood
- An accident e.g. employee deleting data
- Malicious action by an intruder
Data is protected against loss by:
- Regular backups
- A mirror database software (data saved in two places)
- Storing backups in a safe place e.g. cloud, off site
- Restricting access
- Keeping audit trails of who accessed data
Data integrity = Data reflects reality (is correct, and fit for purpose). This can be maximised by:
- Suitable validation
- Software that prevents inconsistent states
Validation
The process of checking data when it is input. Carried out be software. It checks that data conforms to certain rules.
- Length check: must have a certain number of character, or be above, or below some limit e.g. password
- Type check: must be a certain data type e.g. no numerals in surname
- Range check: must fall between certain limits e.g. DOB
- Presence check: must be filled in e.g. Surname
- Lookup check: must match what is held on file e.g. Password
- Format check: must conform to a certain pattern e.g. Car registration must be LLNNLLL
- Check digit: must be exactly the same as data previously entered. An algorithm calculates an extra digit which is appended to the data, the same algorithm checks data when input e.g. ISBN (international book number)
Verification
Checking that the data entered is correct.
It can be a simple visual check aginst the source data, or an algorithm checks two copies, entered independantly, and flags up inconsistencies.
Examples of Databases
Situation Example of use
Telephone company Customers, phone calls, payments
School Students, exams, results, staff
Bank Accounts, transactions, customers
Shops Inventories, customers, prices
Doctors Patients, drugs, hospitals
Government Tax records, drivers, vehicles
Internet providers Router addresses, customers
Airline booking Flights, customers, bookings, airport
Database Operations
Every situation that uses a database needs special software to make the database useful, and achieve its function within the business.
Databases process information. They make data easy to access, group, search, copy, sort, and protect.
Standard operations on databases are sometimes listed under the term CRUD. These are the basic things that most users need to do to a database: C: Create R: Read U: Update D: Delete
Things are usually more complex than this though; it is possible to apply all sorts of algorithms to data in order to gain additional benefits from databases.
Databases: view
A subset of data in a database is called a view.
Making suitable views for each staff member increases the efficiency of using the database, and reduces risks to it.
In organisations, databases, and their application software give every member of staff the right information they need to do jobs, and no more. Giving a user too much access increases the risks of damagng the data by accident, or deliberatly.
Data matching
Compares different databases to look for particular relationships e.g. compare housing benefit claims with credit agency data to uncover benefit fraud.
Data mining
A process that looks in many different unrelaterd databases. It may show up unexpected relationships that went unnoticed before e.g. using supermarket loyalty card data to look for connections between purchases, and various lifestyle indicatiors such as postcodes.
Data models
Databases are organised according to a model.
A model is a data structure that attemps to represent reality in such a way that it is useful to the owner of the database e.g. a hotel booking agency needs data organised around hotels, rooms, dates, and customers.
Flat file database
Rows, and columns (suitable for an address book). Can be set up using a spreadsheet.
Each row is called a record, and each column is called a field.
Flat file databases often suffer from data redundancy (data is repeated unneccesarily), this leads to inconsistencies as things are inputted incorrectly.
Hierarchical database
Useful for making an inventory.
Groups items together that may always belong with each other.
Relational database
Most useful, and most common.
Store data in separate tables, and link the tables together so that related data can easily be extracted.
Each table contains data about an entity (something in real life about which we store data) e.g a customer, a restaurant booking,
This means that data is only stored once. You are always looking at the single up to date version.
Most relational databases seperate data so that entities are linked in one to many relationships e.g. one cat has many fleas, but each flea has only one cat. This is shown using a pronged link.
The DBMS
Database management systems are software that looks after a database. It is a general purpose tool that allows database administrators to:
- Create database applications
- Protect data
- Run queries to extract data
- Keep data consistent
- Keep data accurate
Some DBMSs are small systems for personal computers, and others are huge systems that are designed for large organisations.
