4 A library system has two tables; one for borrowers and one for books. Extracts from these tables are shown. Borrower_ID Borrower_name Contact_telephone Contact_email 5404 Susan Stranks 01632 960321 rockit@cambridgeinternational.org 9867 Fei Hong Zhao 07700 900222 tawara@cambridgeinternational.org Borrowers table (c) The data type for Contact_telephone has been set as text. Explain why this field has been set to text rather than integer. … … … … [2] (d) At the end of each day the tables are searched to issue reminders to borrowers about books that are overdue. Describe the automatic processing involved in searching and issuing of reminders for overdue books. … … … … … … … … … … … … [6]

4(a) ISBN: Check digit Date_due_back: Format check 2 4(b)(i) Book_ID 1 4(b)(ii) Borrower_ID 1 4(c) Two from: The field is not needed for calculation Number starts with a zero/+ symbol May include a space 2 4(d) Six from: Books table opened Borrowers table opened First record is read from the books table First record is read from the borrowers table Date_due_back in books table is compared with the current date If the Date_due_back is after the current date then… …Move to next record If the Date_due_back is before or equal to the current date then… …Contact_email is located A reminder is generated automatically The reminder is sent to the borrower Process continues with next record 6 For part (c), you need to explain that the Contact_telephone field is text because it's not used for calculations, might start with a zero or include symbols, or could contain spaces. For part (d), you must describe a sequence of at least six steps, including opening tables, reading records, comparing dates, locating email, generating, and sending reminders, and continuing the process for all records.

Petr has set up a database for a science project on planets in the solar system. Part of the database is shown. 5 First_observed Type_of_planet Gravity Rings Orbital_period Number_of_moons Name_of_planet ID 1610 Regular 0.38 N 1.88 2 Mars 4 1801 Dwarf 0.27 N 4.61 0 Ceres 5 1610 Gas Giant 1.16 Y 29 62 Saturn 7 1846 Gas Giant 1.21 Y 165 14 Neptune 9 1930 Dwarf 0.62 N 248 5 Pluto 10 2004 Dwarf 0.401 N 309 1 Haumea 11 When Petr was designing his database, he had to set the data types for each field. Complete the design table below by filling in the data types for each named field. Use the most appropriate data type. Each data type must be different. For any numeric field, specify the type of number. (a) Data type Field name Number_of_moons Orbital_period Rings Name_of_planet [4] Operators such as AND, OR, NOT, LIKE, >, >=, can be used to search Petr’s planet database. The search criteria for all the gas giant planets with more than 20 moons would look like this: Type_of_planet = ''Gas Giant'' AND Number_of_moons > 20 Write down the search criteria that will produce a list of planets that are not gas giants but have at least one moon and were first observed after 1800. (b) … … … … [7] Write down the names of the planets that are not gas giants but have at least one moon and were first observed after 1800. (c) … … … … [2] Petr has copied the data from the Orbital_period field into a spreadsheet. The data for the orbital period for Mars, 1.88, is stored in cell A2. In cell B2 he has entered a function of: ROUND(A2,0) Explain in detail the function used in cell B2. (d) … … … … … … [2]

5(a) Field name Data type Number_of_moons Numeric: Integer Orbital_period Numeric: Decimal Rings Boolean Name_of_planet Text 4 5(b) Type_of_planet<>"Gas Giant" AND Number_of_moons >=1 AND First_observed >1800 Only two ANDs correctly placed – 1 mark Type_of_planet – 1 mark <> "Gas Giant" – 1 mark Number_of _moons – 1 mark >=1 – 1 mark First_observed – 1 mark >1800 – 1 mark 7 5(c) Pluto Haumea 2 5(d) It would take the value of A2 – 1 mark Changes the value into an integer/zero decimal places – 1 mark 2 (a) To earn marks, you must correctly identify a different, appropriate data type for each of the four fields: Integer for 'Number_of_moons', Decimal for 'Orbital_period', Boolean for 'Rings', and Text for 'Name_of_planet'. (b) To earn marks, you must construct the search criteria using correct field names, operators, and values, specifically: Type_of_planet<>"Gas Giant" AND Number_of_moons >=1 AND First_observed >1800, with partial marks for correct ANDs, field names, and operator/value pairs.

A systems analyst has created a new computer system to keep records in a medical centre. She has created a relational database to store the medical records of patients. 7 The database uses primary and foreign keys. Describe what is meant by a relational database. (a) … … … … … … [3] Explain the difference between a primary key and a foreign key. (b) … … … … … … … … [4] A dentist works at the medical centre. The dentist stores the medical records of his patients. Describe four other uses of the database software that the dentist can use to help him in his work. (c) 1… … 2… … 3… … 4… … [4] The data stored on the computer system needs to be protected from unauthorised access. Discuss the effectiveness of different methods of increasing security of this data. (d) … … … … … … … … … … … … … … … … [8]

7(a) Three from: Contains more than one table Tables are linked It uses relationships Removes redundancy of data Saves storage space 3 7(b) Four from: Primary key holds unique data Primary key identifies the record Primary key can be automatically indexed Each table has one primary key whereas a table can contain a number of foreign keys Foreign key is used to link with the primary key of another table 4 7(c) Four from: Searches/queries can be used to search details of patients Reports can be created about treatment carried out Formulas can be created to calculate the cost of treatment Charts can be created showing missed appointments by patients Images are stored of the x-rays to identify the patient/determine treatment Mail merge appointments that have been missed Any other appropriate use plus explanation 4 7(d) Eight from: Factors increasing effectiveness Strong passwords are difficult to crack Biometric passwords are harder to crack Regularly changing passwords increases security Use of two-factor authentication Using different passwords for parts of the computer system makes it more difficult to gain access to the full system Firewall required to stop attacks from computers Firewall stops fraudulent sites attacking the computer Anti-spyware stops passwords being seen when typed in Factors reducing effectiveness Too complex a password can be easily forgotten Passworded files may not be backed up Using a number of different passwords can become cumbersome Regularly changing passwords means that passwords may be forgotten May be difficult to choose unique password if it is changed every few weeks Passwords may become more easy to guess if regularly changed Hackers can breach most passwords The firewall can affect the operation of the computer and stop some uploads The firewall may need to be shut down at times to upload files therefore making computer unsafe Some legitimate software can be blocked by the firewall To gain full marks both sides of the discussion are needed 8 For part (a), students must state three characteristics of a relational database, such as containing multiple linked tables or reducing data redundancy. For part (b), students must explain four differences between a primary key and a foreign key, focusing on uniqueness, record identification, and linking tables.

Tawara school uses an optical mark recognition (OMR) system for its school register. An example of the register is shown. For each session of the school there are two lozenges to be shaded in. 9 At the start of the registration period the teacher shades in a lozenge [P] on the register to show that a pupil is present and shades in a lozenge [A] for each pupil who is absent. If a pupil is late then the teacher shades in both the [P] and the [A] lozenges. The register sheets are sent to the school office to be processed and the data from them is stored in the computer’s database. Describe the inputs and computer processing of the registers when they are sent to the school office, prior to them being stored on the computer’s database. (a) … … … … … … … … … … … … [6] The headteacher of the school is considering modernising the registration system so that pupils can register using a facial recognition system. Discuss the advantages and disadvantages of using a facial recognition registration system rather than the OMR system they currently use. (b) … … … … … … … … … … … … … … … … [8] Biometric data, like facial recognition, is an example of personal data. Give two other examples of personal data that could be stored on the school registration database. (c) 1… … 2… … [2]

9(a) Six from: OMR sheets are scanned in using an optical mark reader//Reader reads the lozenge Data is sent to the computer A template of the register is loaded into the computer A value is assigned to each lozenge A light passes through the scanner and shows position of each filled in lozenge//Comparing dark spots with lighter areas//Use X Y coordinates The position is compared to the template/pre-determined data If the lozenge shaded is [P] then the pupil is recorded as present If the lozenge shaded is [A] then the pupil is recorded as absent If both lozenges [A] and [P] are shaded then the pupil is recorded as late. If no lozenge on a line is shaded the result is discarded 6 9(b) Eight from: Advantages Faces are unique therefore greater security Data entry is faster as it does not need to be sent to the office for entry Data entry is more accurate the teacher could enter the wrong data on OMR sheet Greater security as the pupil needs to be present to register More difficult to fake biometrics OMR data sheets can be forgotten or lost Disadvantages Facial injuries/facial hair/glasses/masks can affect the data entry The cost of setting up a new system is higher as extra hardware and software needs to be purchased//Setting up the system is more expensive Time consuming to set up all the pupils The data will need to be updated regularly due to facial changes as pupils get older Higher error rate System is more complex therefore may cost more to maintain//Maintaining the system is more expensive The environment and usage may affect the reader Biometric systems are not 100% accurate Less flexibility as the OMR sheets can be used anywhere in the school Biometric data harder be reset if incorrect/if change needed Greater invasion of privacy as biometric data has to be stored on the computer To gain full marks there must be at least one advantage and one disadvantage 8 9(c) Two from: Name Address Gender Date of birth Personal contact details, such as Email address and phone number ID card/roll number Race//Ethnic origin 2

Aimi works for a shop that sells toys. She is setting up a relational database of the stock in the shop. She has started designing the database. Aimi has written down the questions that are asked 10 about each toy and needs to produce appropriate field names from them. An example of the type of data that is to be stored is shown. What is the toy? Tawara Doll Who manufactured the toy? Tinky What year was it manufactured? 2020 What price was it bought for? $12.99 Complete the design table below by filling in an appropriate field name for each question. Each field name must be different. Field names must not include spaces. (a) Field name What is the toy? Who manufactured the toy? What year was it manufactured? What price was it bought for? [4] When Aimi created the whole database one of the fields was set as integer. However, as data is entered Aimi realises that the field should have been set to two decimal places. (b) Describe the steps that need to be taken, by Aimi, to change the field from an integer to a decimal. This field will be set to two decimal places. … … … … … … … … [4] Aimi created a relational database but could have created a flat file database. (c) Discuss the advantages and disadvantages of using a relational database rather than a flat file database. … … … … … … … … … … … … [6]

10(a) Field name (Examples) What is the toy? Name_of_toy Who manufactured the toy? Manufacturer What year was it manufactured? Year What price was it bought for? Buying_price 4 10(b) Four from: Open the table Select Design View Select the field Select field type Select from the drop down list – double Click decimal places Type in 2 4 10(c) Six from Advantages Data does not need to be entered a second time as tables are used Records cannot be duplicated but a flat file cannot stop this Saves time when entering data More efficient storage as data is only stored once Simpler to delete/modify details Complex queries can be carried out Complex reports can be created Better security as some tables can be made confidential Avoids inconsistent records More data independence Less inconsistency of data More ability to cater for future requirements Disadvantages More complex than a flat file database as more tables are required Takes more time to set up More of a reduction in performance if many tables are needed Slower extraction of data Less robust due to broken keys and records//Each table requires a key field and relationships to other tables Needs specialist personnel to setup the database More processing power needed for complex queries A relational database is more complex to understand that a flat file database To gain full marks there needs to be advantages and disadvantages 6 (a) To earn marks, a student must provide a different, appropriate field name for each of the four questions, ensuring no spaces are included in the field names. (b) To earn marks, a student must describe four distinct steps required to change a field's data type from integer to a decimal with two decimal places, including opening the table in design view, selecting the field, changing the field type, and specifying the decimal places.

Cambridge IGCSE ICT · 0417

IGCSE ICT (0417) Databases past paper questions

Revise IGCSE ICT (0417) — Databases with real Cambridge past-paper questions grouped by topic, each shown with its marking scheme. Working one topic at a time targets exactly the skill each question tests.

46 free questions from 2016–2021, each with its marking scheme. Open the full topical bank to filter by subtopic, paper, and year.