O Level Paper 2 database All topics of chapter

O level Paper2 database covers all topics primary key field record sql query function count sum and average all Definition of database.covers all the past paper question marking scheme examiner report.also worksheets with their answers.th slid is made according to the new syllabus 2023 of cambridge.

1. DATABASES IGCSE Computer Science Chapter 9 START

2. A database is an organised collection of data. In a database, you can organise data in a table format . ID Name Age House Students Info

3. BRAINSTORM What are some usages of databases?

4. Student and examination records at schools USAGE ID Name Age House 1 2 3 A B C 10 11 10 Blue Red Red

5. Patient records at hospitals USAGE ID Name Disease 1 2 3 A B C Diabetes Headache Stroke

6. Customer records at banks USAGE ID Name Saving($) 1 2 3 A B C 500 1000 300

7. Booking and reservation records at railways and airports Employee records at offices OthER USAGES

8. 9.1 CHAPTER OUTLINE Important terms related to databases 9.2 Types of databases 9.3 Creating a database 9.4 Using query

9. 9.1 CHAPTER OUTLINE Important terms related to databases 9.2 Types of databases 9.3 Creating a database 9.4 Using query

10. Important terms related to database Table F R Q D ID First Name Age House Last Name In a database, data is entered in a tabular format organised as rows and columns. 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson Student Data

11. Important terms related to database T Field R Q D ID First Name Age House Last Name A field is a column in a database. It is also a title given to a piece of data. 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson AGE field Student Data

12. Important terms related to database T F Record Q D ID First Name Age House Last Name A record is a row in a database table. A collection of fields in the table for a specific person. 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson A record about Jane Watson Student Data

13. Important terms related to database T F R Query D ID First Name Age House Last Name 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson A query allows the user to find full records or records with specific fields in a database using certain criteria. Student Data

14. ID First Name Age House Last Name 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson Query: Can you show me only the records of students who are 11 years old. Student Data Structured Query Language (Learn in C9.4) ID First Name Age House Last Name 001 Jay 11 Yellow Chou 005 Anna 11 Yellow Maria

15. Important terms related to database T F R Q Data Type ID First Name Age House Last Name 001 Jay 11 Y Chou 002 Jane 10 R 003 Will 13 B Smith 004 Max 12 G Verst 005 Anna 11 Y Maria Watson A data type helps computer to identify similar data for the purpose of sorting, searching and calculation. INTEGER TEXT/ALPHANUMERIC Numeric Char

16. Important terms related to database T F R Q Data Type Syllabus Data Type Description Microsoft Access data type Text/Alphanumeric Character Boolean Integer Real Date/Time A number of characters A single character Yes or No, True or False Whole Number A decimal number Date/Time Short/Long Text Short text with field size 1 Yes/No Number formatted as fixed with zero decimal places Number formatted as decimal Date/Time

17. 9.1 CHAPTER OUTLINE Important terms related to databases 9.2 Types of databases 9.3 Creating a database 9.4 Using query

18. DATABASE MANAGEMENT SYSTEM (DBMS) Microsoft Access Oracle Database An interface which acts as an interface between the user and the database

19. TWO TYPES OF DATABASES FLAT-FILE DATABASE Store only a single table of data The database is not related to any other table or database. Disadvantages: Data duplication A process of creating an exact copy of data Data redundancy The storing of the same data in multiple locations Input errors ID First Name Age House Last Name 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson Student Data

20. TWO TYPES OF DATABASES RELATIONAL DATABASE A database that has multiple tables When the table are linked, the user can work on complex data structures having multiple tables A relational database does not allow data duplication. It is more efficient and consistent than a flat-file database

21. ID First Name Age House Last Name 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson Student Data ID ENG 001 65 002 70 003 69 004 56 005 43 Student Result Math HIS SCIENCE 88 45 45 77 67 75 38 66 90 48 44 55 13 42 54 RELATIONAL DATABASE Database that can have more than 1 table. Instead of placing all the fields into one table, fields are categorized into their types (student information & student result). It makes the database more organized.

22. ID First Name Age House Last Name 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson ID ENG 001 65 002 70 003 69 004 56 005 43 Student Result Math HIS SCIENCE 88 45 45 77 67 75 38 66 90 48 44 55 13 42 54 PRIMARY KEY In both tables, student ID will be unique for each record. It thus defines the record of each student. This specific field is defined as the primary key for that table. A primary key is a unique field that allows the user to identify a record. Every record in the primary key field must be unique. Student Data

23. 9.1 CHAPTER OUTLINE Important terms related to databases 9.2 Types of databases 9.3 Creating a database 9.4 Using query

24. THE FOLLOWING TUTORIALS ARE MADE USING MICROSOFT ACCESS

25. Step 1: Create a blank database and name it

26. Step 2: Select Design View and you will be prompted to create a table.

27. Step 3: Create field in your table and assign each field a datatype. *Phone Number is given a short text data type as phone number can contain symbol (eg. +).

28. Step 4: Add validation to your fields - Age. Restrict age to be between 7 to 18.

29. Restrict House to contain only one character. Step 4: Add validation to your fields - House.

30. Step 5: Select table and enter data into it.

31. Testing out validation Try to enter a value >18 into the Age field.

32. 9.1 CHAPTER OUTLINE Important terms related to databases 9.2 Types of databases 9.3 Creating a database 9.4 Using query

33. Queries Queries are used for working with data in a database. You can search data by certain criteria such as "searching the records in which students are aged 11 or older". ID First Name Age House Last Name 001 Jay 11 Yellow Chou 002 Jane 10 Red 003 Will 13 Blue Smith 004 Max 12 Green Verst 005 Anna 11 Yellow Maria Watson Query: Can you show me only the records of students who are 11 years old. Student Data Structured Query Language ID First Name Age House Last Name 001 Jay 11 Yellow Chou 005 Anna 11 Yellow Maria

34. Method 1: Using Query-by Step 1: Select Query Design Step 2: Select the table in which you want to query on

35. Method 1: Using Query-by Step 3: Select fields that we want to view. Also select field that we want to set criteria on. Only show > 13

36. Method 1: Using Query-by Step 4: Click Run Step 5: Result!

37. Method 2: SQL (Structured Query Language) SQL is mainly used for relational databases. It contains statements which are used to retrieve or modify data from the database.

38. Step 2: Select SQL View Using SQL in Microsoft Access Step 1: Select Query Design

39. Using the * for the field name This will retrieve all fields of a table SELECT [field(s)] All the fields FROM [Table]

40. Where clause Used to specify a condition. You will get only records for which the condition is true.

41. Microsoft Access Let's type in the SQL code into Microsoft Access

42. Microsoft Access Result - only records which have age > 13 are selected

43. ORDER BY CLAUSE Used along with the SELECT statement to retrieve information in ascending order.

44. ORDER BY CLAUSE A TABLE SHOWN THAT IS ORDERED BY AGE.

45. ORDER BY CLAUSE If information needs to be arranged in descending order, the DESC is used along with the Order By clause.

46. ORDER BY CLAUSE RESULT WITH DESCENDING ORDER.

47. FUNCTIONS Aggregate functions are used to perform mathematical operations - counting and addition, respectively on the data values of a given field. COUNT FUNCTION SUM FUNCTION

48. COUNT FUNCTION It is used for counting the number of rows in a field. You can add a condition using WHERE clause. It calculate the number of records which matches House = "G"

49. COUNT FUNCTION Illustration ID First Name Age House Last Name 001 Jay 11 G Chou 002 Jane 10 R 003 Will 13 B Smith 004 Max 12 G Verst 005 Anna 11 G Maria Watson Query: Can you COUNT for me how many of these students are from the Green ('G') House? Student Info Table Structured Query Language Answer: 3 (Try it out in Access!

50. SUM FUNCTION It is used for adding the values in a given field. ID First Name Age House Last Name 001 Jay 11 G Chou 002 Jane 10 R 003 Will 13 B Smith 004 Max 12 G Verst 005 Anna 11 G Maria Watson Student Info Table Money Paid 100 100 200 300 200 Query: Can you calculate for me how much in total these students have paid?

51. SUM FUNCTION It is used for adding the values in a given field. ID First Name Age House Last Name 001 Jay 11 G Chou 002 Jane 10 R 003 Will 13 B Smith 004 Max 12 G Verst 005 Anna 11 G Maria Watson Student Info Table Money Paid 100 100 200 300 200 Query: Can you calculate for me how much in total these students have paid? Answer: 900 (Try it out in Access!

52. USEFUL OPERATORS OPERATOR DESCRIPTION = > < >= <= <> BETWEEN LIKE IN AND OR NOT EQUAL TO GREATER THAN LESS THAN GREATER THAN OR EQUAL TO LESS THAN OR EQUAL TO NOT EQUAL TO BETWEEN A RANGE OF TWO VALUES SEARCH FOR A PATTERN SPECIFY MULTIPLE VALUES SPECIFY MULTIPLE CONDITIONS THAT MUST ALL BE TRUE SPECIFY MULTIPLE CONDITIONS WHERE ON OR MORE CONDITIONS MUST BE TRUE SPECIFY A CONDITION THAT MUST BE FALSE

53. Projects to practice SQL Instagram Followers Sales Reports