Databases

1. Computing Science Level - National 4 / 5 Databases St Andrew’s High School Computing Science

2. What is a Database • A database is a structured collection of similar information on one topic. • Examples: • Phone book, library catalogue, criminal records, dictionary • A database can be ordered either in ascending (A to Z) or descending (Z to A) order and on one or more fields. • Example: • A phone book can be sorted by last name and first name in ascending order (A to Z) St Andrew’s High School Computing Science

3. Important Elements • A database contains 3 important elements: • Fields • Records • Files St Andrew’s High School Computing Science

4. Field • A field holds one piece of information • Example: • Forename Helen • Date of Birth 12/12/95 • Town Coatbridge St Andrew’s High School Computing Science

5. Record • A record is a collection of fields on one person or thing. • Example: • Your record in school would contain: • your name; • date of birth; • your address. St Andrew’s High School Computing Science

6. File • A file is a collection of records on the same topic. • Examples: • - The Police National Computer • - Customer records in a bank • - Pupil files held on school computers St Andrew’s High School Computing Science

7. Create and Add Records • Firstly the basic record structure is created by deciding on the fields names and field types. • Secondly you must add new records • You can add records through a form or just entering data straight to the table St Andrew’s High School Computing Science

8. Alter Records • Once you have created your database, you must ensure the data is correct. • You can alter the records through a form, or through the table. • You can also alter the record format. St Andrew’s High School Computing Science

9. Field • A field is one single piece of information • Example: • - “name”, is one field this would be a text field. • - “date of birth”, is another field and this would be a date field. St Andrew’s High School Computing Science

10. Types of Fields • Text holds letters, numbers and symbols • Numeric hold numbers for calculations • Date holds a date • Time holds a time • Graphic holds a picture • Calculated field performs a calculation on the contents of one or more fields St Andrew’s High School Computing Science

11. Types of Fields (contd.) • Link Stores a reference to an external media file or a connection to a related database table • Boolean Only allows one of two values: • yes/no • true/false • male/female St Andrew’s High School Computing Science

12. Field Validation • Validation ensures data entered is allowable and sensible St Andrew’s High School Computing Science

13. Field Validation (cont’d) St Andrew’s High School Computing Science

14. Creating a new Field • Fields can be added at any time. • When on the table view, select the design view option • This view will allow you to enter a new field. St Andrew’s High School Computing Science

15. Searching • The search facility allows you to look for information in the database. • A search may be: • Simple Look for records with a match on one field ( They have one thing in common.) • Eg Hair = “Brown” • Complex Look for records with a match on more than one item in one or more fields. • Eg Hair = “Brown” • AND • Eyes = “Blue” St Andrew’s High School Computing Science

16. Comparison operators < Less than < = Less than or equal to = Equal to > = Equal to or greater than > Greater than < > Not equal to Contains Eg. To find all records for 1st to 3rd year in a school database you could search for: Year <= 3 St Andrew’s High School Computing Science

17. Sorting Sorting allows you to arrange the records in a database in alphabetic or numeric order. This can be ascending (A to Z or 1 to 9) or descending (Z to A or 9 to 1) Sorting on More than one field When two items are the same in one field they can be separated using a second field for sorting. For example, it is common to sort lists of names first by surname and then by first name St Andrew’s High School Computing Science

18. Question Time • Complete the questions below from the Standard Grade Computing J Walsh book chapter 4, pages 63 and 64. • NAT 4: • Foundation KU 1-3 and PS 1-5 • General KU 1-3 and PS 1 • OR • NAT 4: • Complete the booklet Exercise 1 - 4 • NAT 5: • Complete the booklet Exercise 5 • Finish the questions above for next day. St Andrew’s High School Computing Science

19. Calculated Field/ Computed Field A calculated field allows you to carry out a calculation on another field or fields and return the answer in the calculated field (similar to formulae in a spreadsheet). Example: Field 1: Date of birth Field 2: Today's date Field 3: Age Field 3 is a calculated field and contains the formula: Today’s date - Date of birth Other examples of calculated fields often used in reports include totals and sub-totals. St Andrew’s High School Computing Science

20. Report • Any information on your database that you print out is a report. You would normally do a search and / or a sort, and then select which fields you want to print. St Andrew’s High School Computing Science

21. Size of a field This is the total number of characters, including spaces, needed to hold the information in a field. Eg. A Field containing the data ‘Computing Department’ Would have a field size of 20. Examples of databases include:- Telephone directory Police National Computer A personal Christmas card list. St Andrew’s High School Computing Science

22. Calculating the storage requirements of a database file Field Size of field 1 30 2 4 3 25 4 24 5 8 6 4 7 4 8 8 St Andrew’s High School Computing Science

23. Field Size Bytes required • 1 30 30 • 2 4 4 • 3 25 25 • 4 24 24 • 5 8 8 • 6 4 4 • 7 4 4 • 8 8 8 • Total for one record= 107 bytes • If a database has 50 records the storage space required= • 107 X 50 = 5350 bytes • 5350 / 1024 = 5.22 Kilobytes St Andrew’s High School Computing Science

24. Keywords • This is the text used to search a file for a particular entry. • Key Field • This is a field which contains unique information for each record. That is, each record has a different number or text in the key field. Doing a search for an item on a unique field will only give one record. • Example: SQA has a database of all pupils attempting Standard Grade Exam. Each pupil has a unique candidate number because there will be more than one pupil with the same name and date of birth. St Andrew’s High School Computing Science

25. Types of Database St Andrew’s High School Computing Science

26. Linked/Relational Database ExamplePupils Database St Andrew’s High School Computing Science

27. Data Protection Act • Definitions: • Data User is a person who holds and uses personal data about others or controls the use of it. • Data Subject is a person about whom personal data is stored by a data user. St Andrew’s High School Computing Science

28. The Data subjects have the following rights: • • to know if data is held about them on a computer • • to see a copy of this personal data • • to make corrections if necessary • • to ask for compensation if data is inaccurate or access given to an unauthorised person. St Andrew’s High School Computing Science

29. Under the Data Protection Act (1984) data users must: • • get and process the information fairly and lawfully • • register what reason they hold it for • • hold only relevant information • • hold only accurate and up to date information • • not keep information any longer than needed • • give individuals access to information about themselves and, where necessary, correct or remove wrong information • • take appropriate security measures. St Andrew’s High School Computing Science

30. Exceptions to the Act • There are exceptions to people’s right to see data held about them. The public are denied access to data held by the Police or security forces. St Andrew’s High School Computing Science

31. Misuse of Computers The Computer Misuse Act is intended to protect all types of information (not just personal) stored on computer systems. Hacking This is the act of trying to gain unauthorised entry to files. This is done by using a wide area network and passwords. St Andrew’s High School Computing Science

32. Viruses Some people enjoy writing and distributing computer viruses which destroy data and cause computers to crash or take up processor time in meaningless calculations. Viruses are usually spread by copying files (from unofficial sources). To prevent viruses spreading: • Don’t share disks. • Don’t copy software. • Use an anti-virus program to check disks regularly. St Andrew’s High School Computing Science

33. Mail Merge • A database is the second general purpose package (along with a word processor) required to produce a mail merged document. Having studied both these packages, we are now in a better position to understand how a mail merge works. • Mail merging is the process of combining details from a database with a standard letter in a word processing package, to produce personalised letters - as many letters as there are records in the database. St Andrew’s High School Computing Science

34. Word Processed Standard Letter Name Flossie Year S1 Name Josie Year S5 Name Phyllis Year S4 Dear Parent, I am pleased to inform you that your child ___________ has won a prize for the best Computing student in __________ Head Teacher. Database Having created your database and your standard letter, you are ready to combine the two, filling the gaps in the standard letter with information from the database. St Andrew’s High School Computing Science

35. Word Processed Standard Letter with database fields inserted ready for mail merge. Dear Parent, I am pleased to inform you that your child <<Name>> has won a prize for the best Computing student in <<Year>> Head Teacher. The database field names are used to mark where in the standard letter information from the database will be inserted. These are shown in brackets like so << >> to mark them. When the mail merge is performed the field names in brackets are replaced with the appropriate fields from the database. This is done for every record in the database. St Andrew’s High School Computing Science

36. Question Time • Complete the questions below from the Standard Grade Computing J Walsh book chapter 4, pages 63 and 64. • NAT 4 and 5: • Credit KU 1 and PS 1-2 • and • Exercise 6 and 7 from the Database Booklet • NAT 5: • Exercise 8 from the Database Booklet • Complete questions for next day. • Copy key points into your jotter. St Andrew’s High School Computing Science