ORGANISATION OF DATA FOR EFFECTIVE RETRIEVAL

1. ORGANISATION OF DATA FOR EFFECTIVE RETRIEVAL ICT 2.3

2. Objectives • Describe the nature and purpose of a database and how they work • Understand that data needs to be organised in a database to allow for effective updating and retrieval. Understand how data can be retrieved to produce meaningful information • Select and justify appropriate file and database structures for particular applications M A Fletcher

3. What is a database? • An organised collection of data • Not necessarily on computer • But most large collections of data are now computerised • Faster • More flexibility – eg selecting, sorting, printing M A Fletcher

4. Invoices in a spreadsheet Relationships between information Product information in a file cabinet Addresses in a document Phone numbers in a card file M A Fletcher

5. Development of databases • Applications developed independently – each with its own files – no integration • Payroll often the first to be computerised – contained name and address details as well as pay data • Personnel file – also contained name and address details • Known as ‘Flat files’ M A Fletcher

6. Flat files • Data for an application held in a single file • Similar to a manual card file – one card or record for each entry in the file • Spreadsheets can be used as a flat file – a row for each record and a column for each field • Only good for simple data M A Fletcher

7. Orders (Flat File) • SPREADSHEET M A Fletcher

8. Problems with Flat file systems • Consider a customer order • Order number, customer name and address, details of goods ordered – code, description, price etc are required • When a customer makes another order, the same name and address details need to be given • When a different customer orders the same product, the product description etc will need to be entered again • Some data is Duplicated or Redundant – the same data is stored more than once – which wastes storage space and time when entering the data and slows searching, selecting/querying and sorting because the volume of data is increased. M A Fletcher

9. Problems with Flat file systems Cont • Data redundancy or duplication in the same or different files can lead to inconsistency – the same data has different values in different files or in different records in the same file. eg A product description may be spelt or abbreviated differently in different records. eg When an employee moves house the address may be changed in the payroll file but not in the personnel file or training department’s file M A Fletcher

10. Problems with Flat file systems Cont • The data files are linked to the programs using the data – if the structure of the data changes all programmes need to be changed. eg Adding a field, changing the length of a text field, changing the allowable values of a field (Dept code S, F or G – new dept A to be added) M A Fletcher

11. Solving the Problems of Flat File databases • Split the data into a series of tables – one for each entity (person, object or thing) • The tables can then be linked together and managed by the relational database software eg Access M A Fletcher

12. Customers M A Fletcher

13. Employees M A Fletcher

14. Suppliers M A Fletcher

15. Items M A Fletcher

16. New Orders M A Fletcher

17. M A Fletcher

18. Relational Database • Data stored in a number of tables which can be linked together • Primary key – field used to identify the record uniquely eg Employee number • Composite or compound key – 2 or more fields eg Employee Surname and Employee First name – this could be used as a Sort key if an alphabetical list of employees is required • Foreign key – a field in one table which is a primary key in another table eg ItemNo in New Orders table • No results of calculations are stored – these are calculated when needed M A Fletcher

19. Benefits of Relational Database v Flat File • Data is entered once only – avoids typing errors and saves time • Data redundancy/duplication is avoided – data is not stored several times in separate files or in the same file • Data is consistent – because it is not stored several times • Data is stored centrally and accessible by all those authorised – more information is available to more users M A Fletcher

20. Benefits of Relational Database v Flat File Continued • Management information is more accurate and useful because it is based on a single comprehensive set of data • Greater security – backups can be done centrally, different levels of access can be set • Data independence – the structure of the database does not affect the programs which access the data – structure can be changed easily, new reports, applications based on the data can be set up quickly M A Fletcher