Skip to content
Cambridge IGCSE0417

Create a database structure

Information Communication Technology 0417 Chapter Notes

What this chapter covers

Create a database structureManipulate dataPresent data
ShareWhatsAppPost
Create a database structure notes

Read online free. Download a watermarked copy with a free account.

Read the notes

The full Create a database structure notes as text: skim, search, and jump between subtopics.

~27 min read

In this chapter

  1. 1Importing data and setting data types
  2. 2Display formats and sub-types
  3. 3Primary and foreign keys
  4. 4Creating relationships between tables
  5. 5Flat file and relational databases
  6. 6Creating a data entry form
  7. 7Characteristics of good form design
  8. 8Calculated fields
  9. 9Functions and calculated controls
  10. 10Sorting on one or more criteria
  11. 11Selecting records with operators
  12. 12Wildcards and NOT
  13. 13Report titles
  14. 14Headers and footers
  15. 15Fields in full, in the right order
  16. 16Page layout: orientation and width
  17. 17Number formats and alignment
  18. 18A total at the end of the report
  19. 19Labels

1. Importing data and setting data types

Import data from an existing file, such as a .csv or .txt file, as a new table. Use the field names the question gives, exactly as written, and import only the fields it asks for.

Set the data type given for each field: text, numeric (integer, decimal or currency), date/time or Boolean/logical.

A telephone number is stored as text (alphanumeric), because no calculation will be performed on it. Do not add an ID field the question did not ask for.

Examiner insight

“Some candidates imported all the fields from the source file and did not restrict the import to only the seven fields specified on the question paper.”

Worked example 15 marks

Using database software, import the file j2621models.csv as a new table. Use these field names and data types. Set Model_Code as the primary key.

Import the file j2621manufacturers.csv as a new table in your database. Use these field names and data types: Manufacturer_Name, Text; Manufacturer_ID, Text; Country_Of_Origin, Text; Incorporated, Number, stored as Integer; Annual_Revenue, Number/Currency; Employees, Number, stored as Integer. Set Manufacturer_ID as the primary key.

Field nameData typeStoreDisplay
Model_NameText
Manufacturer_CodeText
Cash_PriceNumber/CurrencyDecimalCurrency symbol with 0 decimal places
Model_CodeText
Charge_TimeNumberIntegerInteger
Estimated_RangeNumberIntegerInteger
PowerNumberIntegerInteger
Launch_YearNumberIntegerInteger
SUVBoolean/LogicalYes/No
Gov_SubsidyNumberPercentage symbol with 1 decimal place
  1. 1

    models table: all 10 field names and data types as given [1]

  2. 2

    manufacturers table: all 6 field names and data types as given [1]

  3. 3

    primary keys set: Model_Code (models), Manufacturer_ID (manufacturers) [1]

  4. 4

    Cash_Price displays the currency symbol with 0 decimal places [1]

  5. 5

    SUV displays Yes/No; Gov_Subsidy displays % to 1 decimal place [1]

2. Display formats and sub-types

Numeric data has a sub-type: integer, decimal, currency or percentage. Store a decimal value as a decimal, or it is rounded. Then set the number of decimal places to display.

Currency displays a currency symbol and a percentage displays the percentage symbol. A Boolean/logical field displays as Yes/No, true/false or a checkbox. Date/time data also has a display format, for example dd-MMM-yyyy.

Examiner insight

“Many candidates did not set the display of the decimal values to 1 decimal place with most accepting the default display of the software.” “Those that did set this as a Boolean field did not always change the display to Yes/No from the default tick box.”

Worked example 13 marks

A products table has three fields. In_stock holds yes or no and must display those words. Price holds amounts of money such as 12.5 and must display with a currency symbol and 2 decimal places. Date_added must display dates in the form 05-Mar-2026.

State the data type and the display format for each field.

  1. 1

    In_stock: Boolean/logical, displayed as Yes/No [1]

  2. 2

    Price: number/currency, stored as a decimal, with the currency symbol and 2 decimal places [1]

  3. 3

    Date_added: date/time, displayed as dd-MMM-yyyy [1]

3. Primary and foreign keys

A primary key identifies a record in a table. Its data is unique, only one primary key field can be in a table, and it contains a value. It acts as a link with other tables.

A foreign key in one table is linked to the primary key in another table.

Choose a field whose data is unique. A field with duplicates, such as a title, is not a primary key.

Examiner insight

“Most candidates correctly selected the Game_Code field as the primary field but a number of candidates set this on Game_Title which was the first field in the table and contained duplicate data, included an ID field and set this as the primary key or did not set a primary field at all.”

Worked example 14 marks

A database contains a primary key. State four characteristics of a primary key.

  1. 1

    The data in a primary key is unique [1]

  2. 2

    Only one primary key field can be in a table [1]

  3. 3

    A primary key identifies a record in a table [1]

  4. 4

    It contains a value / acts as a link with other tables [1]

4. Creating relationships between tables

A relationship links the primary key in one table to a field in another table. That field is the foreign key.

The papers ask for a one-to-many relationship, for example between Manufacturer_ID in the manufacturers table and Manufacturer_Code in the models table.

The evidence must show the outcome, not the process of creating the relationship. A screenshot of the relationship dialogue box will evidence the relationship type.

Examiner insight

“The relationship diagram can only be credited if it shows the single and one-to-many infinity symbols confirming the relationship type. Some relationships were set as one-to-one instead of one-to-many.”

Worked example 11 mark

Create one-to-many relationships as links between:

the Supplier_code in the suppliers table and the Supplier_code in the parts table

the Category_code in the categories table and the Part_category in the parts table.

Place in your Evidence Document screenshots showing the one-to-many relationships between the three tables.

  1. 1

    One-to-many relationship Supplier_code (suppliers) to Supplier_code (parts), and Category_code (categories) to Part_category (parts) [1]

5. Flat file and relational databases

A flat file database is on one table. Its data is delimited by commas or tabs. It is easy to set up and is a simple database. It contains a primary key.

A relational database uses separate tables linked to form a relationship, with primary and foreign keys.

Say two tables are connected, not two databases or files.

Examiner insight

“Using the correct terminology is very important in questions of this type; in a relational database two tables are connected together rather than two databases or files for example.” “Responses were too vague with statements such as ‘quicker’ and ‘easier’ without providing detail of what was quicker or easier.”

Worked example 14 marks

State the characteristics of a flat file database.

  1. 1

    A flat file is on one table [1]

  2. 2

    Delimited by commas / tabs [1]

  3. 3

    Easy to set up [1]

  4. 4

    Simple database / contains a primary key [1]

6. Creating a data entry form

A data entry form is a screen form for entering one record. It holds the specified fields with sensible spacing between them and different size boxes to suit the data.

Where appropriate, let the user click rather than type: radio buttons, check boxes, drop-down menus or calendar selection. Add navigation, save, exit and help buttons.

Use appropriate font styles and sizes, and white space. A data entry form is not a table, a table design view or a report layout.

Examiner insight

“A few candidates designed a report form layout rather than a data entry form.” “Often the form produced was the unedited result of using a form ‘wizard’. Some evidence for making the form ‘user friendly’ was expected, such as a meaningful title, user explanations, or clear field titles.”

Worked example 16 marks

Tawara Books sells books either as an online version or a physical version. Each book has a five-digit book number and each customer has an eight-digit customer number. Tawara Books uses a database system which contains information about the books it sells.

Each record contains the following data: Book title, Author, Book number, Type of book (online/physical), Date book sold, Customer number.

Design an appropriate online data entry form for one book. Clearly indicate the amount of space required for each field. Your design must represent a tablet screen and, where appropriate, allow the user to enter data by clicking the mouse button rather than typing in text.

  1. 1

    All 6 fields included [1]

  2. 2

    Sensible spacing; a long title gets a wider box than the other fields [1]

  3. 3

    Fills the screen: at least 75% of the screen [1]

  4. 4

    Radio buttons / check boxes / calendar selection, for example for type of book [1]

  5. 5

    Drop-down menus [1]

  6. 6

    Navigation / save / exit / help buttons [1]

7. Characteristics of good form design

Good form design has a meaningful title, clear field titles and user explanations. The title is not the source file name.

Field headings are aligned and spaced consistently. Each field's size is appropriate for its data: make it obvious that an eight-digit number has 8 digits.

Drop-down menus and radio buttons let the user click rather than type. The unedited result of a form wizard is not enough.

Examiner insight

“The field headings were not always aligned or spaced consistently.” “Some attempted to change some field lengths but the size was not always appropriate for the data.”

Worked example 13 marks

A clinic uses a data entry form to record patient details. State three characteristics of good form design.

  1. 1

    A meaningful title and clear field titles [1]

  2. 2

    Headings aligned and spaced consistently, with each field a size appropriate for the data [1]

  3. 3

    Drop-down menus / radio buttons / check boxes so the user clicks rather than types [1]

Recap

  • Import a .csv or .txt file with the field names the question gives. Import only the fields asked for.
  • Set the data type: text, number, currency, date/time or Boolean/logical. Telephone numbers are text.
  • Store decimals as decimals, then set the display: decimal places, currency symbol, %, Yes/No or a date format.
  • A primary key is unique, identifies a record and there is only one per table.
  • A foreign key links to the primary key in another table.
  • The papers ask for a one-to-many relationship from a primary key. Show the outcome with its relationship type.
  • A flat file is one table, delimited by commas or tabs. A relational database links tables with keys.
  • A data entry form has the given fields, sensible spacing, different size boxes, clicking tools and buttons.

8. Calculated fields

A calculated field is a new field worked out at run-time from other fields. Use the arithmetic operations: addition, subtraction, multiplication and division.

The heading must be 100% accurate: spelt and capitalised as the question gives it, with any underscore. Then format the field as asked, for example currency to 2 decimal places, with the same currency symbol as the field it is worked from.

Purchase_Price = Cash_Price - (Cash_Price × Gov_Subsidy)

Overdue_cost = Cost × 1.05

Examiner insight

“The new field heading was usually entered accurately with only a few omitting the underscore, or having data entry or capitalisation errors.” “Most used the correct calculation although this was not always displayed with the same currency symbol as the ASP field.”

Worked example 12 marks

Using fields from both tables, produce a tabular report that contains a new field called Purchase_Price which is calculated at run-time where Purchase_Price = Cash_Price – (Cash_Price × Gov_Subsidy) Format this field to display currency to 2 decimal places.

  1. 1

    Add a calculated field with the heading `Purchase_Price`, 100% accurate [1]

  2. 2

    Enter `Cash_Price - (Cash_Price * Gov_Subsidy)` so the values are correct, shown as currency with the same symbol as `Cash_Price` and 2 decimal places [1]

9. Functions and calculated controls

Use a function to summarise a column: sum adds the values, average gives the mean, maximum and minimum give the largest and smallest, and count counts the records.

Put the function in a calculated control in the report footer, at the end of the report. The page footer is at the bottom of each page, and a calculation placed there gives an error. Place the value below the column it summarises, with a label to its left. Format it to the decimal places asked.

The Evidence Document needs a screenshot of the database formula, fully visible.

Examiner insight

“The average calculation on the Length column was not always done correctly with some using count or sum instead. This figure was not always displayed under the Length column or to 1 decimal place.” “Some candidates did not provide screenshot evidence of a database formula used. A few candidates evidenced a spreadsheet formula or keyed the formula into their Evidence document instead of taking a screenshot of the formula in the database.”

Worked example 13 marks

Produce a tabular report that calculates the average charge time and places this below the Charge_Time column at the end of the report formatted to 0 decimal places. The report has the label Average Charge Time to the left of this value. Place in your Evidence Document a screenshot showing the database formula used to calculate the average charge time. Make sure that the formula is fully visible.

  1. 1

    Use the average function on `Charge_Time`, in the report footer below the `Charge_Time` column, to 0 decimal places [1]

  2. 2

    Label `Average Charge Time` to the left of the value, 100% accurate [1]

  3. 3

    Screenshot of the database formula, fully visible [1]

10. Sorting on one or more criteria

Sort data into ascending order (lowest to highest, or A to Z) or descending order (highest to lowest, or Z to A).

With multiple criteria, the first field sorts the records first. The second field then orders any records that tie. Each criterion has its own direction, so one can be ascending and the other descending.

A sort field can jump to the front of the report. The fields must still appear in the order the question lists.

Examiner insight

“Some candidates sorted both fields into ascending order where the First_name field should have been in descending order.” “Some candidates did not display the fields in the correct order, particularly where the sort field was displayed first. This can be avoided by setting the sort order in the report structure rather than in the query or during the creation of the report if a report wizard is used.”

Worked example 12 marks

Using fields from both tables, produce a tabular report that: shows only the fields Model_Name, Charge_Time, Manufacturer_Name, Cash_Price, Launch_Year, SUV, Gov_Subsidy, Purchase_Price, in this order, with data and labels displayed in full. Do not group the data sorts the data into ascending order of Manufacturer_Name and ascending order of Charge_Time

  1. 1

    Sort ascending on `Manufacturer_Name` first, then ascending on `Charge_Time` [1]

  2. 2

    Fields and headings in the listed order, so `Model_Name` is first even though `Manufacturer_Name` is the first sort field [1]

11. Selecting records with operators

A query selects a subset of the data. Each criterion uses an operator: `=`, `<>`, `<`, `>`, `<=` or `>=`.

`>=` means "or more" or "or later", as in `Available_year >= 2027`. `<=` means "or less", as in `Charge_Time <= 68`. `>` and `<` leave out the number itself. `<>` means not equal to.

With multiple criteria, AND needs every criterion to be true. OR needs only one, so "INT or EXT or DL" lists three values joined by OR.

Examiner insight

“Some searched for the records greater than 2010 instead of 2010 and later, and a few confused the greater than (>) and less than (<) operators.” “For the volume search some candidates incorrectly used the = operator to find records that were greater than or equal to 6000.”

Worked example 12 marks

Using fields from all tables, produce a tabular report that selects the records where Part_category is INT or EXT or DL, and the Available_year is 2027 or later.

  1. 1

    `Part_category` is INT or EXT or DL, joined by OR [1]

  2. 2

    `Available_year` `>= 2027` [1]

12. Wildcards and NOT

A wildcard search uses LIKE to match part of a text value. A wildcard at both ends finds text that contains a word. A wildcard at one end finds only text that begins or ends with it.

NOT, or `<>`, leaves out records. "Does not include the text Part or Motor" leaves out every record that contains Part and every record that contains Motor.

Every condition in the question's list must be true for a record to be selected.

Examiner insight

“The wildcard search often only found records beginning and/or ending with 'night' and did not find those where the title contained 'night'.” “The search criterion did not always find the correct records with a number of candidates incorrectly searching for console types containing 'Cyrus' instead of those beginning with 'Cyrus'.”

Worked example 12 marks

Using fields from only the suppliers table, produce labels which select only the records where: Supplier_name does not include the text Part or Motor Payment_terms is 30 days

  1. 1

    `Supplier_name` does not include the text Part or Motor: NOT, with a wildcard at both ends of each word [1]

  2. 2

    `Payment_terms` is 30 [1]

Recap

  • A calculated field is worked out at run-time from other fields, its heading 100% accurate, formatted as asked.
  • Sum adds, average gives the mean, maximum and minimum give the extremes, count counts records.
  • Put a calculated control in the report footer, not the page footer, below its column, label to the left.
  • Screenshot the database formula, fully visible.
  • Sort each criterion in its own direction; keep the fields in the listed order.
  • Operators: =, <>, <, >, <=, >=. Greater than or equal to means 'or more'.
  • AND needs every criterion true; OR needs one; NOT leaves records out.
  • A wildcard at both ends finds text that contains the word.

13. Report titles

A report needs a title. Type only the words the question gives, exactly as written, at the top of the report.

The title is in a larger font size. Make the text box big enough that every letter, including the tails of g and y, is fully visible. Centre it when the question says so.

Examiner insight

“Occasionally this title contained data entry or capitalisation errors or displayed additional text such as ‘Query 1’ in the title area.” “The ‘g’ descender on the title was not always fully visible if the text box had not been adjusted to accommodate a larger font size.”

Worked example 12 marks

Produce a tabular report that includes only the title Fastest Charging Electric Vehicles displayed in a larger font size, centred at the top of the page.

  1. 1

    Title text Fastest Charging Electric Vehicles only, at the top of the page, typed exactly [1]

  2. 2

    Title in a larger font size, centred and fully visible [1]

14. Headers and footers

A database report has a report header, a report footer, a page header and a page footer. The page footer prints at the bottom of every page. The report footer appears only on the last page.

Your name, centre number and candidate number go in the page footer when the question says they must appear on every page. Remove anything else the page footer holds, such as the date and the page number.

Examiner insight

“Identification details were often entered in the report footer so they only appeared on the last page of the report rather than in the page footer so they printed at the bottom of every page.” “A common error was not removing any other fields such as date and page number from the page footer area.”

Worked example 11 mark

Produce a tabular report that has your name, centre number and candidate number in the footer of the report so it appears in the same position on every page.

  1. 1

    Name, centre number and candidate number in the page footer, appearing on every page [1]

15. Fields in full, in the right order

Show only the fields the question lists, in the order given. Leave out any field the question does not list. Make each heading match its data.

Display all the data and labels in full. Widen a field until nothing is cut off.

A report wizard puts the sort fields first. Set the sort in the report structure after the report is built.

Examiner insight

“Occasionally data in one or more fields was truncated (most commonly in the Game_Title or Genre fields) and required some manipulation to ensure all data was fully visible.” “This can be avoided by setting the sort order in the report structure rather than in the query or during the creation of the report if a report wizard is used.”

Worked example 11 mark

Using fields from both tables, produce a tabular report that shows only the fields Model_Name, Charge_Time, Manufacturer_Name, Cash_Price, Launch_Year, SUV, Gov_Subsidy, Purchase_Price, in this order, with data and labels displayed in full. Do not group the data.

  1. 1

    Specified base fields: headings and fields in the correct order, headings match data [1]

16. Page layout: orientation and width

Set the page orientation the question asks for, landscape or portrait.

Fits a single page wide means every field fits across the width of the page. The records may run on to further pages. Do not force the report on to one page long: records are lost.

When the question says the report fits on a single page, the whole report must print on one page, still with no data truncated.

Examiner insight

“Most presented the report in portrait orientation but this did not always fit on a single page.” “The fields and data usually fitted to a single page wide but some candidates interpreted this as forcing it to print on a single page long and in doing so lost some of the records that matched the search criteria.”

Worked example 11 mark

Produce a tabular report that has a page orientation of landscape and fits a single page wide.

  1. 1

    Landscape, one page wide, all base fields present, no truncation [1]

17. Number formats and alignment

Control the display format of numeric data: the number of decimal places, the currency symbol or a percentage. A calculated field uses the same currency symbol as the field it comes from.

Align data and labels appropriately. Right align numeric data, and use decimal alignment where the question asks for it.

Examiner insight

“Most used the correct calculation although this was not always displayed with the same currency symbol as the Berth_Fee field or with 0 decimal places.” “Few candidates accurately right aligned this with the race times in the Race_Time column.”

Worked example 13 marks

Produce a tabular report that contains a new field called Overdue_cost which is calculated at run-time. This field must display the cost price increased by 5%. Use Overdue_cost = Cost × 1.05. Format this field to display currency to 1 decimal place.

  1. 1

    Calculated field heading Overdue_cost, typed exactly [1]

  2. 2

    Calculated field: correct values, Cost × 1.05 [1]

  3. 3

    Values with the same currency symbol and 1 dp [1]

18. A total at the end of the report

A value for the whole report, such as an average or a total, goes in the report footer, below the column it summarises. Right align it with the data in that column. In the page footer it repeats on every page and can display an error.

Put the label to the left of the value. Type the label exactly, with no colon or extra word. Use the function the question needs and format it as asked.

Examiner insight

“Some candidates put this calculation in the page footer instead of the report footer, so the value or error message appeared incorrectly on every page.” “The label was usually entered to the left of this value but often contained capitalisation errors and/or a superfluous colon.”

Worked example 12 marks

Produce a tabular report that calculates the average charge time and places this below the Charge_Time column at the end of the report formatted to 0 decimal places, and has the label Average Charge Time to the left of this value.

  1. 1

    Correct average charge time, below the Charge_Time column, 0 dp, in the report footer [1]

  2. 2

    Label Average Charge Time typed exactly, to the left of the value [1]

19. Labels

Reports come in different output layouts, tabular or columnar. When the question asks for labels, produce labels, not a tabular report.

Choose a label size that gives the columns, rows and labels to the page the question states. Put the fields on the lines given, with the spaces and punctuation between them.

Identification details go at the top left of each label, not in a page header or footer. Format only the field the question names, and make its box big enough for the larger text.

Examiner insight

“Candidate identification details were often placed in the page header or footer so did not appear on every label and where they were on every label, they occasionally were not left aligned.” “A few candidates presented the labels as a tabular report and therefore could only achieve the search and sort marks.”

Worked example 13 marks

Produce labels that have your name, centre number and candidate number on the left at the top of each label. Modify the format of the label so that only the Supplier_name is displayed in a larger font size, centre-aligned and bold.

  1. 1

    Name, centre number, candidate number top left of each label [1]

  2. 2

    Supplier_name centred, larger font size, bold [1]

  3. 3

    Formatting applied to Supplier_name only [1]

Recap

  • Title: the words given, only, in a larger font size, fully visible.
  • Page footer prints on every page. Report footer appears on the last page only.
  • Fields: only those listed, in the order given, none truncated.
  • Set the sort in the report structure so the sort fields do not jump to the front.
  • Landscape or portrait as asked. Single page wide limits the width only. Single page means the whole report on one page.
  • Numbers: set decimal places and the same currency symbol. Right align numeric data.
  • Totals go in the report footer with the label to the left. Labels are not a tabular report, and your details go inside each label.

End-of-chapter exercise

Test yourself on the whole chapter. Work through these before moving on.

  1. State one characteristic of a primary key.1 mark
  2. State two characteristics of a flat file database.2 marks
  3. A table has a field called Telephone. State the data type for this field and give a reason.2 marks
  4. A games table has the fields Game_Title, Game_Code, Platform and Year. Some games share a title. State the most appropriate primary key and give a reason.2 marks
  5. A Cost field holds values such as 4.5. It must display with a currency symbol and 2 decimal places. An Online field is Boolean and displays as a tick box. It must display Yes or No. State what must be set for each field.3 marks
  6. A Students table has the fields Student_ID, Name and Form_code. A Forms table has the fields Form_code and Tutor. Each form has many students. State the primary key of the Forms table, the foreign key in the Students table, and the type of relationship between the tables.3 marks
  7. A student creates a one-to-many relationship, then places a screenshot of the process of creating it in the Evidence Document. Explain why this may not gain the mark, and state what the screenshot should show.2 marks
  8. A shop keeps all its stock data in one .csv file. It moves the data into database software as a Products table and a Suppliers table. Both tables have a Supplier_code field. No two suppliers share a Supplier_code, and one supplier supplies many products. State the type of database before and after the change. Describe how the data is connected after the change.4 marks
  9. A vet has two .csv files. A student imports them as the two tables below. Several pets share a name. No two pets share a Pet_tag and no two owners share an Owner_ref. One owner can have several pets. Weight holds values such as 4.6 and must display to 1 decimal place. The student sets Pet_name as the primary key of the pets table, adds an ID field as the primary key of the owners table, sets Phone as numeric, stores Weight as an integer displayed to 1 decimal place, and creates a one-to-one relationship between the tables. Identify the five mistakes and state the correction for each. | Table | Fields in the file | | --- | --- | | owners | Owner_name, Owner_ref, Phone | | pets | Pet_name, Pet_tag, Owner_ref, Weight |5 marks
  10. A sports club enters each new member on a tablet. A record holds Member_name, Member_number (six digits), Membership_type (junior, adult or senior), Date_joined and Coaching (yes or no). A student's design is a table with one row for each member. Its title is the file name j26members.csv, its five boxes are all the same size and every value is typed in. Describe six changes that would make this a good data entry form.6 marks
  11. Name the function that adds all the values in a numeric field.1 mark
  12. A Price field is sorted into descending order. State whether the first record shows the highest or the lowest price.1 mark
  13. Write the operator and value that select records where Age is 16 or more.1 mark
  14. A report must show a field called Total_Cost, worked out for each record from the Price and Quantity fields at run-time. State the type of field needed and write its calculation.2 marks
  15. Records (Name, Team, Points): Ali, Red, 12; Bea, Blue, 15; Cal, Red, 20; Dee, Blue, 9. They are sorted into ascending order of Team, then descending order of Points. Give the Name of each record in the order they appear.2 marks
  16. Records are selected where Seats >= 5 AND Price < 20000. The records (Model, Seats, Price) are A, 4, 15000; B, 5, 18000; C, 7, 22000; D, 5, 20000; E, 6, 19999. State which records are selected.2 marks
  17. A student searches for records where Country = Spain AND Country = France and finds none. Explain why, and give the correct criteria.2 marks
  18. The Title field holds: Night Fever, Silent Nights, Dawn Patrol, Day and Night, Midnight Run. A search must find every title that contains the text night. Ignore capital letters. State where the wildcards go and list the titles found.3 marks
  19. A ferry report must select the sailings where Route includes the text port or bay, and Fare is 12.50 or less. Ignore capital letters. A student uses the criteria: Route begins with port AND Route begins with bay AND Fare < 12.50. The sailings (Route, Fare) are: Port Ellen, 12.50; North Bay, 9.00; Bayview, 14.00; Newport, 11.00; Castle Rock, 8.00. Explain why the student's search finds no sailings. Give the three corrections needed and the sailings the correct search finds.5 marks
  20. A garden centre's Plants table holds the records below. A report selects the records where Plant includes the text fern and Stock is 20 or more. Ignore capital letters. It has a calculated field Sale_Price = Pot_Price – (Pot_Price × Discount). It is sorted into ascending order of Supplier, then descending order of Sale_Price. The report footer holds the average Sale_Price. List the plants in report order with each Sale_Price, and give the average displayed as currency in pounds to 2 decimal places. | Plant | Supplier | Pot_Price | Discount | Stock | | --- | --- | --- | --- | --- | | Tree fern | Moss | 12.00 | 0.25 | 20 | | Fernleaf dill | Arbor | 4.00 | 0.10 | 35 | | Silver fern | Arbor | 8.00 | 0.50 | 40 | | Boston fern | Moss | 10.00 | 0.20 | 19 | | Fennel | Arbor | 3.00 | 0.10 | 50 | | Lady ferns | Moss | 6.00 | 0.10 | 25 |6 marks
  21. State where your name should be placed so that it prints on every page of a report.1 mark
  22. A question asks for only your name, centre number and candidate number in the footer, on every page of a report. A candidate's page footer holds their name, the date and the page number. State two corrections.2 marks
  23. A candidate sets the title Staff Directory in a larger font size, but the tail of the letter y is cut off. Explain how to correct this.2 marks
  24. A question asks for the fields Forename, Surname and Year in that order, sorted on Year. A candidate sets the sort in the report wizard and the report has Year as its first field. Explain why, and how to avoid it.3 marks
  25. One field of a printed report displays Mountain Bik where the data is Mountain Bike. State what is wrong and what must be done.2 marks
  26. In a report, Hire_Fee displays as £45. A new field Hire_Total = Hire_Fee × 1.1 displays 49.5. The question asks for currency to 2 decimal places. Give the value that should display and describe the format.3 marks
  27. A report ends with the total of the Fee column. The numbers in the Fee column are right aligned. State how the total should be aligned, and name the section of the report that holds it.2 marks
  28. A report of 120 records must fit a single page wide. A candidate shrinks it to print on one page long and only 80 records appear. Explain the mistake and the correct setting.3 marks
  29. A swimming club needs a tabular report of the fields Swimmer, Squad, Lane_Fee and Gala_Fee. Gala_Fee is calculated at run-time as Lane_Fee × 1.25 and must display currency to 2 decimal places. The total of Gala_Fee must be placed below that column at the end of the report, with the label Total Gala Fees to the left of it. On a candidate's three-page printout, the first record has Lane_Fee as €12.00 and Gala_Fee as 15. The text #Error prints at the bottom of every page. The text Total Gala Fees: is to the right of the error. (a) Identify each fault and state how to correct it. (b) In the last record Lane_Fee is €18.60. Give Gala_Fee exactly as it should display.6 marks
  30. A seed shop needs labels arranged in two columns and seven rows, with 14 labels to an A4 page (21 cm wide by 29.7 cm high) in portrait orientation. Only Seed_name is to be in a larger font size, centre-aligned and bold. Your name, centre number and candidate number must be on the left at the top of each label. (a) A candidate chooses a label size 105 mm wide and 74 mm high. Work out how many labels this gives to the page. (b) Use a calculation to check that a label size 99.1 mm wide and 38.1 mm high gives the layout asked for. (c) On the candidate's printout every line of each label is bold, the lower part of each Seed_name is cut off, and the candidate's details print once at the bottom of the page. State three corrections.6 marks

Go deeper

Practise and revise with member-only material for this chapter.

Workbook

Exam-style practice questions with full worked solutions.

Unlock with a plan

Chapter at a Glance

A single-page summary of every key idea for fast revision.

Unlock with a plan

Free notes are just the start.

Unlock every Workbook and Chapter at a Glance, and generate your own worksheets and predicted papers.

Explore plans

Related chapters

Hardware and softwareInput devices and their usesStorage devices and mediaNetworksMicroprocessor-controlled devicesCommunication