Databases

1. Databases

2. What is a database? A database is a collection of information on a particular topic that is structured in some way. For example you could have a database on football teams, CD collection or books.

3. Why do we use a database? To access information quickly and easily. To sort information in different ways e.g. alphabetical or ascending or descending. We can also search the database for specific pieces of data e.g. a persons name or the author of a book etc.

4. How are databases organised?  They are made up of FILES, RECORDS and FIELDS.  A FILE is a collection of structured information on a specific topic.  A RECORD is a collection of structured information on a particular person or thing.  A FIELD is a single piece of data which is contained in each record. Records are made up of, usually, 2 or more fields.

5. School Database First Name: Last Name: D.O.B Address: Tel No: Class: Guidance: File Third Year Pupils First Name: Last Name: D.O.B Address: Tel No: Class: Guidance: Record First Name: Last Name: D.O.B Address: Tel No: Class: Guidance: Fields

6. Types of Field • Text – only letters eg abc, ABC • Numeric – only numbers eg 123, 890 • Date – a formatted date eg 12/12/00 • Time – a formatted time eg 1340 hours • Graphic /image – stores pictures • Calculated field: used to hold a formula and do a calculation

7. Types of Field • Link – links to files on your computer, a network or the internet • Boolean- This field is designed to store a value of 1 or 0. Users are commonly presented with a yes / nooption

8. Database Structure Flat file structure • A simple database structure has one table containing all the fields and the data they hold.

9. Linked Tables • A database with linked tables has a more complex structure. It has several tables which are linked together to allow the user to navigate between different sets of data.

10. Primary key A primary key uniquely identifies each record in the database e.g. an ID number an account number. Primary Key

11. Primary key Other examples of primary keys: • Car Registration Number, SA300486 • National Insurance Number, YBSONIIT • Account number, 078654321 • Order Number, EX7P8432

12. Foreign key • A foreign key is used to set up links between tables in a database by using e.g. the ID number to link two tables

13. Validation • When designing a database it is vital that all the data being entered is validated. • Validation = checking to make sure that the correct type of data is being entered in the right place.

14. Validation: field length • Field Length validation • This type of validation checks that the correct number of either numbers or characters have been entered into a field • E.g. minimum number of characters for a password

15. Validation: field type • This type of validation checks that the correct type of data is being entered e.g. characters in a text field, numbers into a numeric field

16. Validation: presence check • This validation check will make sure that data is entered into a field. • It is often marked with an asterisk *

17. Validation: restricted choice • This type of validation uses a drop down menu that restricts the type of data being entered.

18. Sorting When you sort the database you are putting the information into some kind of order. The most common way to sort text in a database is to put it into alphabetical order. When we sort we are either putting data into ascending or descending order. Ascending means smallest to biggest i.e. a to z or 1 to 100. Descending means biggest to smallest i.e. z to a or 100 to 1.

19. Sorting • When you are sorting records in a file you can sort on one field e.g. the Surname field. • Or • You can sort on more than one field • e.g. sort on Surnamefield, the address field and the age field

20. Searching The main reason people create databases is so that they can search it for specific information. When the computer searches the database it looks at the contents of particular fields. There are 2 ways of searching: Simple search – searching on one field Complex search –searching on more than one field

21. Answering Database Questions • When answering a database question, go through the following points: • Is it a simple search or complex search? • What is the first field you want to search on? • In a complex search, what is the second field you want to search on? • Does the info have to be in a certain order? • Do you need to sort on multiple fields? • Does the info have to be printed?

22. Database questions: • Look at the following database extract: • First Name Lauren • Surname Duffy • Class 4E • Date of Birth 24th Feb • Questions: • Teacher wants a list of all the pupils in 4A, how would she do this? • Teacher wants a list of all pupils in 4C whose surname is QUINN,how would she do this? • Teacher wants an alphabetical list of pupils with surname Jones born on 30th July, how would she do this?. Fields

23. Answers: Field Names: Sample Data: First Name Lauren Surname Duffy Class 4E Date of Birth 24th Feb • Do a simple search on the field class for 4A. • 2. Do a complex search first on the field class for 4C then on the field surname for Quinn. • 3. Do a complex search first on the field surname for Jones then on the field date of birth for 30th July then SORT into alphabetical order.

24. Searching A Database (Example) 1 2

25. Search 1 • Find an accountant from Glasgow: • Complex Search • On Occupation = Accountant • AND • On Area = Glasgow

26. Search 2 • Find Male who likes to ski and is taller than 1.65m: • Complex Search • On Interests =Skiing • AND • On Height > 1.65 • AND • Sex = Male

27. More Database Questions: • Look at the following database extract: • Field Names Sample Data • Make Vauxhall • Model Astra • Colour Silver • Year 1998 • Questions • How would you get a list of all the cars made by MAZDA? • How would you get a list of all the cars made by FORD in 1999? • How would you get an alphabetical list of all the cars that are BLACK and made by ROVER.

28. Answers: Field Names Sample Data Make Vauxhall Model Astra Colour Silver Year 1998 • Do a simple search on the make field for Mazda. • Do a complex search first on the makefield for Ford and on the field year for 1999. • Do a complex search first on the colourfield for black and on the makefield for Rover then SORT the MAKE field into alphabetical order.