Chapter 5: Databases Flashcards
(32 cards)
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.
Separation of data, and applications
The DBMS acts as a go-between, connecting applications to the underlying data.
It is important to seperate the applications from the data so that:
- Programmers don’t have to worry that their applications might damage existing data structures.
- New applications can be written without reconstructing data.
- Data can be more easily shared between applications.
- Data remains consitent because there is one copy for all applications.
Transactions
When a change takes place in a database it is called a transaction. Transactions must not damage the integrity of a database.
e.g. One account is debited, but the one is is going to is not credited. This would mean the overall state of the database (the total amount of money in the system) would be different after the transaction, The database would be inconsitent, it would no longer reflect reality, and therefore the data would lose its integrity.
Multi-user database
Most commercial databases are mutli-user. Many people need to access them at the same time. This can cause conflicts.
If two users try to modify data at the same time, one of the transactions will fail.
The avoid this most DBMSs use record locking. This means that if one user has opened a record for writing (editing), other users can only view it until the transaction is completed. Then it is unlocked for other users.
Common tools provided by a DBMS
Most dataase management systems have a set of standard tools. You can see them in such products as Microsoft Access, or Libre Office Base.
Tables
Tables are the structures where data is stored. The DBMS provides tools for creating, and modifying tables.
- Each table contains data about just one entity.
- A row is equivalent to a record.
- Each row in a table is made up in the same way - it has the same fields, and all of the rows are the same size.
- Each table has a primary key (uniquely identifies a record, can be one field e.g. clientID, or a combination of fields e.g. hotel room, and date together)
- Each field has to be a particular data type.
Linking tables
Relational databases are linked together.
The primary key of one table can be linked to the foreign key of another table. This allows data such as client details to only be stored once.
Every time a new booking is made it can be connected to oen copy of the client details. This avoids data redundancy, and reduces the risk of errors.
Primary key
A way of uniquely identifying records in a table.
Foreign key
Primary keys from another table that are used to link tables together.
Composite key
Primary key which is made up of more than one field to make it unique.
