Databases Standard Grade What is a Database? • A structured collection of similar information that can be searched and sorted. • A database is made up of files, records and fields. What is a Database? • A database is an organised, structured collection of similar information. • A database can be stored on paper (manual) or on a computer (electronic). • A program that is used for organising data on a computer is called a database package. • Data is organised in a database as files, records and fields: Files….Records…Fields? Files A file is a collection of records. Data in a database is saved on the computer as a data file and contains data on a particular topic e.g. a Films data file. Records A record is a collection of fields. A record is a collection of organised data on a particular person or item. Fields A field is a single item of information. A field is part of a record that holds a particular piece of data e.g. Surname. Sample Fields Here is a sample record from a telephone directory data file made up of different fields: Field Title Mr. Forename John Surname Smith Address 101 High Street Record Town Ayr Postcode Tel. No. KA7 3KL 01292 262626 What is a Database used for? • Databases are used by many types of business and organisations such as: •Telephone Directory • Police National Computer • Inland Revenue • Driver and Vehicle Licensing Centre • A personal Christmas card list • A mailing list • Data held in a database can be shared between other computer packages such as a word processor package – e.g. Mail Merge. Advantages of an electronic database? • Information is immediately available. • Records can be processed quickly. • Records can be processed accurately. All of this is impossible in a manual system !!!! Features of a Database? • Databases have many of the features common to a general purpose package. • There are some special features of a database: • Create fields • Add and alter records • Search • Sort Create Fields • The first step on setting up a database is deciding on the fields it requires. • Fields hold the information you want to store. • You can set the size and type of a field. • Size: You can decide how many characters a field can hold e.g. a Postcode field to hold KA18 3GH would require a minimum of 8 characters as the space counts as a character! • Type: Fields can hold different types of data: • A text field holds letters. • A number field holds only numbers. • Date and time fields hold numbers formatted into date and time. Add and Alter Records • The next step is to enter records into the empty database. A new record has to be created for each item. e.g. Record 1 Mr John Smith 1 Anytown Scotland 01929 393939 Mrs Susan Jones 5 The Town Scotland 01521 447210 Record 2 Search Records • You can run a search on a database for specific information. • You can search on a field (or fields) based on certain conditions you require. e.g. search for the forenames and surnames of people whose test mark was greater than 50. Sort Records • Sorting allows you to organise records into a particular order: • alphabetic • numeric • ascending (1,2,3,4,5…) • descending (Z, Y, X, W …) • The sort is done by choosing which field the database is to be sorted into e.g. sort alphabetically by surname. Additional features of a Database Reports • any information printed out from a database is a report. • you can search for information, then sort it into an order, and decide what fields you wish printed out in what order. • this is known as a report definition. Computed Fields • A computed field (also called a calculated field) can be created in a database or report. • It carries out a calculation on a field or fields and returns an answer (similar to a spreadsheet formula in a cell). Example: If a database has the fields <Total Pay> and <Total Deductions>, then a third called <Net Pay> could be set up with the formula: (=Total Pay – Total Deductions). This is a computed field. Altering Input/Output formats • You can decide how the information in your database is presented by altering the format. • You can change the way the input screen looks and format a report for output. • The position of fields can be rearranged and formatted into a particular order and layout. • This is useful for designing input screens and producing documents such as invoices for output. Keywords Use of Keywords • When searching for a particular record or part of it, the text that is used for the search is called a keyword. • It is also known as a search string. • However, care must be taken that the correct keywords are used.