ICT A2 - Topic 6: Database Systems

Flat files – They only contain one table of data

• The applications software would access the set of data held in the table

• For example a payroll system would access the file which contains all of the employees and their pay details

Problems with flat files

• Because there is no sharing of data with flat-files there is no record, details still need to be re-entered which wastes time and inconsistencies in data can arise when a customer changes their address

Flat files and Relational databases

• Features of flat files -> Created using spreadsheet or database software, all of the data is held in one table, data redundancy can occur, it is only useful with simple applications and it is simple to set up

• Features of relational databases -> Data is held in several tables and there are links between them, data can be taken out from any of the tables however more knowledge is required to set them up

Relational Databases

• Relational databases have many tables which are linked together

E.g. in a tool hire company, each tool can be given a unique number and other details, another table could have all of the customers with unique customer numbers and other info -> Another table could be called rentals which would hold information about what customer has what tool -> The Tool ID which appears in the tools and rentals would hold the link

Relational Database Organisation

• Relational database organisation deals with data consistency, redundancy, integrity and independence

Data Consistency

• Same data can be found in various files which is wasteful because it may need to be entered in twice and data must be updated more than once if updated in one file

Data Redundancy

• A central pool of data must be created so the data can be shared between several applications

• This is good because data doesn’t need to be duplicated

• Marketing department may have a database which contains customer and account department information -> Customer tells the marketing department that they have moved house and changed address, the department will need to contact the accounts department as well as entering the new information in themselves

• In a bigger company the system would break down as people wouldn’t know who has information about customers

• There are some advantages of having a single table -> don’t have to type and keep the same data unnecessarily and don’t have to make lots of changes

• Data which is repeated unnecessarily is redundant data

Data Integrity

• Correctness of data

• Users can lose their trust in a database once they start to find inaccurate data

There are some procedures which can be undertaken to ensure that this doesn’t happen:

• Ensuring that errors don’t appear during transcription -> Careful checks and good training can reduce the amount of transcription errors

• Verification methods -> Ensures that what is being entered onto the computer is completely identical to the data which is on the source document

• Validation methods -> Checks correctness of data by use of range checks, list checks etc.

• Ensuring that there are methods in place for regular updating -> Maintenance will be where there is continual updating of information

• Ensuring that there are no errors in operating procedures -> Wrong file could be used in updating a master file

• Data independence -> New applications can be developed to access data and it can still be used if completely new systems are used

Use of Primary Keys, Foreign Keys and Links:

Primary Keys

• Field which is used to uniquely identify a specific record or line/row in a table

• Primary keys are mostly always numeric fields

• Databases will make a primary key for you if you do not define one

Links

• Data can be combined together even if the data is in different tables

• Links are called relationships

Foreign keys

• Field of one table which is also a primary key of another

Database Normalisation:

It minimises the duplication of data, eradicates redundancy of data, makes sure that data integrity is ok and it enables flexible extraction of information

Un-normalised Form to First Normal Form Remove repeating groups and put them in their own table with their own name

First Normal Form to Second Normal Form

Look at tables with two keys and if a field only depends on one then it should be taken out with its key and put into another table

Second Normal Form to Third Normal Form

Look at fields and find any which are equally dependent and if they are then they need to be moved to a different table

Good to leave one of these fields in the original table so that you can use it as a key

Advantages - Administrators will require a detailed view of the data

Managers can have an overall view which isn’t hard to see by a load of detail

Data can be shown graphically to make trends over time easier to see

Database Security

• Flat-file databases only allow a user to either access all or none of the data whereas a relational database only allows users to access certain parts of the database

Improved Security in a Relational Database

• Data is stored in a different place to the programs

• Hierarchy of passwords preventing people from accessing data which they don’t need

• You can prohibit access to parts of programs

Data Warehousing:

• A data warehouse is utilised to hold all of an organisation’s historical data and it is used to take information which will enable managers to make decisions

• Can be used to find out the day of the week when a certain store sold the biggest number of a product type in a certain year

Data Mining:

• Data needs to be ‘mined’ after being held in a data warehouse to find patterns, associations and trends

• Data mining can create information e.g. comparisons with competitors, future sale predictions and customer buying patterns

Applications of Data Mining

• Helping in the fight against terrorism since 9/11

• Trying to fight against shoplifting in the clothes retailer Jaeger

• Identification of customer needs

Database Management System (DBMS)

- Application packages based around need to hol collection of centralised and structured data

- Allow user to set up their own databases

- Allow database to be defined

- Allow users to query database

- Allow data to be added, deleted and edited

- Allow user to change structure

- Good security and you can import and export data

Advantages - Stores data in a logical and structured way, data independence, avoids data redundancy, data can be used by various departments, data integrity is maintained, better security and data definitions are standardised

Disadvantages - Complex to learn, high development costs and more vulnerable (centralised)

Queries:

- Request for specific info and allow users to design specific queries

Query languages -> SQL (Structured Query Language) and QBE (Query by Example = Converts to SQL)

SQL -> You can combine data, select which fields to use, save results on query

Data Dictionary:

- Central store of information, allows staff to work consistently and to understand how the data has been established

- Usually contain details of tables, field names, field types, field length and validation

- Don't have to be manually created

General Information:

- Data is distributed across several servers located in different sites

- Data can be stored locally

Advantages -> Less risk of all data stored being inaccessible, improved performance

Disadvantages -> More complex and expensive, more of a security risk due to the fact that data is transferred across networks and inconsistencies in data (lots of people accessing it)