Databases

1. Databases

2. Lesson 1 - Introduction LI: Explain the difference between Data and Information . Describe what a database is.

3. Data & Information • Data is raw, unprocessed facts and figures. • Data is collected, stored and processed by computers. • Information is processed data with structure or meaning. • Information is useful to humans.

4. Data & Information Task: Add meaning or structure to the following data to change it to information. Compare your resulting information with the person next to you. Did you get the same answers?

5. What is a Database? A database is a structured collection of similar information that can be searched and sorted to find out answers to particular questions. Databases can either be electronic or manual but both share the definition above. Some examples of manual databases include: Phone books, encyclopaedias, filing cabinets.

6. What is a Database? Task: Find out about 3 different examples of an electronic database and the information stored in them. Discuss your findings.

7. Manual Vs Electronic

8. Lesson 1 – Success Criteria • I can explain the difference between data and information. • I can describe what a database is and give examples of manual and electronic databases.

9. Lesson 2 – Database Structure LI: Describe the elements that make up the structure of a database. I can identify different types of data that is stored in a database.

10. Name: Miss Watt Address1: 4 West Wood Address2: Kirkland Post Code: KI2 9QU Name: Mrs Brown Address1: 4 West Wood Address2: Kirkland Post Code: KI2 9QU Name: Mr Love Address1: 4 West Wood Address2: Kirkland Post Code: KI2 9QU Name: Mr Smith Address1: 4 West Wood Address2: Kirkland Post Code: KI2 9QU Database Structure File: A collection of structured data on a particular topic. A file is made up of records. Record: Information held about one person or thing. A record is made up of fields. Field: A single item of information. 10 A FILE contains RECORDS, RECORDS contain FIELDS, FIELDS contain CHARACTERS

11. Database Structure – Field (data) Types Fields in a database contain different types of data formats. They are important so that memory is used efficiently and validation checks can be carried out when the data is entered. The most common types include: TEXT: This can be letters only or numbers only [ but no arithmetic can be performed on them ] or a mixture of letters and numbers i. e. ALPHANUMERIC. Examples include JANE , Glasgow, J462 LBH (car reg), 0141- 123-4567, AB11 22 33 A

12. Database Structure – Field (data) Types • NUMBERS: • INTEGER - A positive or negative whole number and zero e.g. 1, 45, 0, -21, -234. These use less computer memory than… • REAL i.e. fractional decimal numbers e.g. 2.34, 77.898 • DATE/TIME: Allows a variety of formats for dates and times to be displayed • e.g.12/12/2003; 12th December 2003; 6.24pm or 18:24 • CURRENCY:Displays the £ sign e.g. £9.99 • BOOLEAN: Allows one of two values e.g. True/ false; Yes / No • GRAPHIC: Holds a picture. Some database types allow for storing multimedia types like a movie.

13. Database Structure – Field (data) Types Calculated Field: It is possible to use the database to perform calculations which involve other fields. These are know as computed or calculated fields. To make a field a calculated field you type the expression in a blank field in a query. e.g.

14. Database Structure – Field (data) Types Key Field: is a field which is unique to the topic or individual and so can be used for identification purposes. e.g. A car reg, a serial number, an employee number…

15. Database Structure – Task FILE : PATIENTSNAMEJohn SmithADDRESS 32 Oak RdDate of Birth 01.04.1940 AGE 72GENDER MALLERGYPenicillinNational Ins No 23456 H From the database record opposite identify the following: The fields in the record. The data types used for each field The key field

16. Lesson 2 – Success Criteria • I can accurately describe the elements that make up the structure of a database: File, Record, Field. • I can identify different types of data and field types that are stored in a database, including key fields and calculated fields.

17. Lesson 3 – Calculating the size of DB File. LI: I will be able to calculate the size of a database file using information about the database.

18. Calculating Database Size – Key Factors When designing a database it is important to know how large the database file will be in advance. This will help us decide what sort of backing store to use. What factors affect the size of a database file?1. Field length 2. Size of 1 record3. Number of records in the file.

19. Calculating Database Size – Step 1 Step 1 - Field Length: This is the total number of characters, including spaces, needed to hold the information in a field. It is good practice to design the length of some fields to ensure that they can accommodate future data entries that are larger than existing entries. 20 20 10 1 Which fields would be designed to accommodate larger data entries? What would you suggest are suitable field sizes for each field?

20. Calculating Database Size – Step 2 Step 2 - Size of 1 record: This is calculated by adding up all of the field sizes. 20 + 20 + 10 + 1 = 51 One character requires 1 byte of storage. Therefore: One record requires 51bytes of storage.

21. Calculating Database Size – Units of Data Storage (revision)

22. Calculating Database Size – Step 3 Step 3 - Size of DB file: This is calculated by multiplying the size of 1 record by the number of records in the database. 20 + 20 + 10 + 1 = 51bytes 51x 6 = 306 bytes If necessary you should show your final answer in appropriate units.

23. Calculating Database Size - Task Using the database below suggest a suitable field size for each field and calculate how many bytes are required for one record and how many kilobytes are required to store the details of 100 employees. 20 + 20 + 4 + 15 + 2 + 2 + 3 = 66 bytes x 100 (records) = 6600 bytes / 1024 = 6.45 Kb

24. Lesson 3 – Success Criteria • I can determine the size of each field in a database and using this information and the number of records, I can calculate the size of a database file. • I can express the answer to my calculations in appropriate units.

25. Lesson 4 – Searching & Sorting LI: I can describe and exemplify the process of searching and sorting a database.

26. Searching & Sorting Apart from storing data and information, databases are excellent at searching for answers to questions about the information and once found perhaps sorting the data alphabetically or numerically.These searches and sorts are performed much faster than a human possibly could.

27. Searching SEARCHING on the computer may require the user to build a query using the database software or a “language” called SQL (Structured Query Language) e.g…. SELECT Make, Model, Colour, [Cost (£)], Comments FROM Table1 WHERE (((Colour)="red") AND (( [Cost (£)])<10000)); =

28. Searching In written tasks the type of search SIMPLE or COMPLEX must be specified and the field and the rule must be written out in full. EXAMPLES To look for pupils in a school database who are in 3H1 would require the following written solution. Perform a simple searchon the field classfor data ”3H1” How would you find pupils in a third year database who failed their Word Processing exam ? Perform a simple search on the field WordProcessing Scorefor data “ < 50 “

29. Complex Searching A COMPLEX SEARCH is a search on more than one field. It involves the Boolean operators AND and OR. How would you find all red Ford cars in a car database Perform a complex search on the fields make for data “Ford” ANDfield colour for data “red “.

30. Searching – Wildcards (*) The use of the wildcard (*)feature is useful if you are just searching for one word (or part of a word) in a phrase. For example in the aircraft database, find the pilot from Aberdeen who had between 10 and 15 years service and can fly long haul flights. Perform a complex search on the field airport for data “ABZ” AND on the field service for data “>10 “ and “<15” AND on the field Flight Type for data *Long*

31. Sorting SORTING involves arranging lists into alphabetic or numerical order. This can be set when designing a query or using the or commands. Data can be sorted in ascending (0…9, A…Z) or descending (9…0, Z…A) order. How would you put the computing class results into alphabetical order? Perform a simple sort on the field surname in ascending order [A to Z]. A COMPLEX SORT is a sort on more than one field. How would you arrange pupils in 3G into alphabetical order and class order ? Perform a complex sort on the field surname in ascending [ A to Z ] order AND on the field class in ascending order [ 1 to 10 ].

32. Lesson 4 – Success Criteria • I can build a complex database query to search a database. • I can use Boolean operators, including the wildcard. • I can explain the process of searching and sorting databases and structure my answers appropriately.

33. Lesson 5 – User Interface LI: I understand the importance of user interface design and can recognise and create user friendly interfaces.

34. User Interface - Forms • Forms are used to display the records in a table in a more user-friendly way. By using a form you can enter and edit records more easily. • Forms can be changed to meet the needs of the user by adding buttons and editing the appearance to include images.

35. User Interface - Reports • Reports are used to print information from your database. They provide professional looking output from a table or query. • Reports can be changed to meet the needs of the user by changing the format of data and editing the appearance to include images. • They can also be altered to include summary data from the database.

36. User Interface – Report Design • The look and feel of the reports you design will vary depending on the type of information you want to present. • If you’re creating a simple report that lists book sales in a particular region of the United Kingdom, you can use the Report Wizard to create a basic two-column report that lists only regions and sales. • Plan to spend some time thinking through what data your report needs to display and how the data should be presented. This will save you editing and revision time later on.

37. User Interface – Report Design • As you think about what you want to accomplish with the report you’re creating, consider these questions: • Who will be using this report? What information will users expect to see, and how do they want the information to be laid out? • What report format is best suited to your needs? For example, will you use multiple columns to present your data? How many columns do you need? • Should the report include all the data in the record source? • Should you use headings and graphic elements (such as lines and boxes) to help readers understand the report?

38. Lesson 5 – Success Criteria • I can create user a user form in a database • I can create a custom report in a database • I understand what makes an interface user-friendly

39. Lesson 6 – Linked Tables LI: I can… Describe the terms ‘flat file’ and ‘relational’ in the context of databases and give advantages and disadvantages of each Describe the three types of data relationships Describe the terms primary key and foreign key Create a relational database

40. Flat File Database • A flat-file database contains only 1 table which stores all of the information.

41. Flat File Database Data Inconsistency If Fred’s phone number changes it would need change everywhere it appears. If Notting Hill and American Pie DVDs are deleted then all of Fred’s details are deleted too. • Insertion Anomaly • In order to enter a new member’s details you would need to add DVD information too.

42. Linked Table Database • A relational database stores data in more than one table. • The idea is to ensure that data is only entered and stored once, so removing the possibility of data duplication and inconsistency.

43. Linked Table Database Consistent Data If Fred’s phone number changes it only needs to change in the member table. • If Notting Hill DVD is deleted then Fred’s details are still stored in the member table. Data Insertion A new member’s details can be added without having to enter DVD details.

44. Linked Table - Task The DVD table still has the problem of data anomalies. Create another table containing the fields and how they are linked to prevent the data anomalies.

45. Linked Table Database Task: Using the DVD example, create another table which solve the data anomalies in the DVD table.

46. Data Relationships • One-to-one • One-to-many • Many-to-many

47. Key Fields • As you already know, a primary key is used to uniquely identify a record in a table • In a relational database the field that the primary key links to (in the other table) is called the foreign key PK PK FK FK

48. Lesson 6 – Success Criteria I have… Created a relational database and linked primary keys and foreign keys Set-up relationship types Understood the advantages of relational databases

49. Lesson 7 – Verification & Validation LI: I can… Explain the difference between verification and validation with examples of each. I can apply verification and validation checks to fields in a database.

50. Verification This is a pre-input check of any information coming into computer system. This means that data is checked BEFORE it enters the computer system. It mainly deals with transcription errors i.e typing numbers or letters in the wrong order. For example 3773 is entered as 7337.