Files and databases Suppose a school stores information

Files and databases Suppose a school stores information about its students on record cards. Each student has their own card; this is their record. Record Field The individual pieces of information recorded on each card, such as name and date of birth, are called fields. 1

These are the fields. There are fields in each record of the file. SEVEN Video_ID is the Key Field. This is the only field that has a different value in every record. It is used to distinguish one record from another. Some videos could have the same Title, Supplier, etc. The Video_ID is the only way a particular video can be picked out. A Keyfield uniquely identifies an individual record. This is one complete record. SEVEN There are records in this file. 2

Fixed and variable length records A fixed length record is one where the length of the fields in each record has been set to be a certain maximum number of characters long. M r D a m o B o l d 3 1 P n a r k L a n e A set amount of memory storage space is set aside for each field. If the contents of a field don’t fill the memory completely it is remains empty and is wasted. 3

The problems with fixed length records are: Fields very rarely contains the maximum number of characters allowed which wastes memory. Values sometimes can’t be entered because they are too large to fit inside the allowed memory space in a field. The advantage of fixed length records is that they make file processing less complicated because the start and end of each record is always a fixed number of characters apart. This makes it much quicker to locate both individual records and fields. 4

A variable length record is one where the length of a field can change to allow data of any size to fit. A special marker (# in this example) indicates where each field ends. The length of a field depends upon the data that is placed in it. Only the memory space needed for a field is ever used — so none is wasted. 5

The advantage of variable length records is that space is not wasted, only the space needed is ever used. The main problem with variable length records is that it is much more difficult to locate the start and end of individual records and fields. To separate variable length records each field has a special character to mark where it ends — called an ‘endof-field marker’. When records need to be located the computer must count through the end-of-field markers to locate individual records and fields. 6

Computerised databases A database is a organised collection of related files. Use a database when you need to store lots of data Key Field In this table, each column is a different field Each row is a record Data is organised into FIELDS and RECORDS. The KEY FIELD contains an item of data that is UNIQUE to that record. 7

FLAT-FILE or RELATIONAL? ·A Flat-file database has all the data organised into ONE table. ·Many modern databases are described as being relational. ·A relational database stores data in tables that are linked together using common fields. 8

Linked data tables in a relational database 9

Database packages Most databases are set-up using a database package. A typical database package will allow a user to: Create a file by entering their own field definitions. Automatic validation checks for fields. Add new or delete unwanted fields Add, edit and delete records Simple searches and complex searches Sorts (ascending and descending) Electronic data entry forms Import and export of data 10

Details of all the changes that need to be made to a master file are often collected together in a transaction file. The master file is updated by comparing it with the transaction file and making changes to any records that appear in both files. Normally at least three ‘generations’ of a master file are kept for backup purposes. If the latest version of the master file is damaged it can be recreated by re-running the previous update using the old master and transaction files. 11

Grandfather Father Son The grandfather-son method of updating 12

To Faye's Catalogue From: Mrs Thompson 3, Elm Street Bridgend Account No. 15244 Date : 12 03 98 Please supply the goods shown below: Description Stock No. Quantity Grey Skirt S 32199 1 Red Sweater S 48105 2 White Shirt S 62177 2 The sale of an item in a shop, the hire of a video, the delivery of new stock to a supermarket are all examples of transactions. A file of these transactions (transaction file) is usually kept and used to update a master file of data. Example Application : Faye's Catalogue receives an order 13

A record from the order file looks like this Account No. Date Order No. Stock No. Qty 15244 120398 1812 S 32199 1 S 48105 2 S 62177 2 14

. To produce an Invoice, two other files are needed : The Customer File : The Stock File : Accoun t No. Customer Name 15244 Mrs Thompson Stock No. Address Credit Limit Amou nt Owed Discount Level 3, Elm Street Bridgend 1000 0 5 Product Name Price No in Stock 12. 99 14 S 32199 Grey Skirt 15

Faye's Catalogue INVOICE 12, Book Road, Fayeville, Kent Please return this invoice with your payment. Date Account No. Order No. 15/03/98 15244 1812 Description Stock No. Quantity Price Charge Grey Skirt S 32199 1 £ 12. 99 Red Sweater S 48105 2 £ 24. 99 £ 49. 98 White Shirt S 62177 2 £ 18. 00 £ 36. 00 Faye's Catalogue then produces this invoice and sends it together with the goods ordered to Mrs Thompson. . Total Charge £ 98. 97 Less 5% Discount £ 94. 02 Click here to return to revision slides 16