GCE A Level 2023 – Computer Science Paper 3 (Practical), 0795

The paper

General Certificate of Education Board – Advanced Level, June 2023. Subject: Computer Science (code 0795), Paper 3 – Practical. Duration: two hours.

Carry out ALL the tasks given. For your guidance, the approximate mark for each part of a task is indicated in brackets.

Great importance is attached to the accuracy, layout and labelling of drawings and computer-generated outputs. You are reminded of the necessity for good English and orderly presentation of your answers.

Write algorithms in the answer booklet provided. Also record in your answer booklet any information requested or that you believe would make it easier to understand how you carried out tasks or answered questions.

When an imperative programming language is required to write program code, either Standard (ISO) Pascal or the (ANSI) C programming language may be used.

If need be, supervisors will assist you in recording details of intermediate work carried out on the computer.

Section A: A plotting program (program development) – 30 marks

Describing the task

Many interesting programming problems involving nested loops can be encountered when creating plots using a printer. Your task is to create the plot in Figure 1 (pyramid) in a general manner, with the size of the base entered at the beginning of program execution. Arrays are not used to do this. (The grid lines are just to ease working with the figure; the lines are not directly needed to answer any question.) A pyramid has a number of stars at its base (MaxNoOfStars), and is made up of rows of spaces and stars. Each line of the pyramid is made up of an odd number of leading spaces (NoOfSpaces) followed by a number of stars (NoOfStars). There are blanks after the stars, but they are not important for this exercise.

Figure 1. Pyramid of stars (the base is the bottom row; the first line is the top row)

     *
    ***
   *****
  *******
 *********
***********

Task 1: Problem understanding (8 marks)

1. A solution to this problem is outlined in the following algorithm:

Initialise number of spaces and stars
Repeat
    For the current line:
        Output leading spaces
        Output line of stars
        Adjust number of spaces and stars for the next line
Until number of stars is the number required

Mindful of the task description above, consider the pyramid of stars in Figure 1 and carry out the following:

(i) State the initial values for MaxNoOfStars. (1 mark)

(ii) State the number of leading spaces (NoOfSpaces) and stars (NoOfStars) of the first line. (2 marks)

(iii) State the number of leading spaces (NoOfSpaces) and stars (NoOfStars) for the second line. (2 marks)

(iv) Between successive lines, the number of stars reduces while the number of leading spaces increases. Hence, by what amounts should you modify NoOfSpaces and NoOfStars before printing the next line in the pyramid, if we print successive rows from the top of the pyramid? (3 marks)

Task 2: Program development (12 marks)

Choose EITHER the Pascal OR the C programming language (PL) to implement your plot of the pyramid of stars.

2. Using a suitable editor of your choice, write the following functions and/or procedures:

(i) The procedure Initialise (MaxNoOfStars, NoOfSpaces, NoOfStars) to initialise the variables MaxNoOfStars, NoOfSpaces and NoOfStars for the first line. (2 marks)

(ii) The procedure OutputLeadingSpaces (nSpaces), which takes the number nSpaces and outputs that number of leading spaces for a line on the screen. (2 marks)

(iii) The procedure OutputLineOfStars (nStars), which takes the number nStars and outputs that number of stars for a line on the screen. (2 marks)

(iv) The procedure Adjust (NoOfSpaces, NoOfStars), which adjusts the variables NoOfSpaces and NoOfStars to the values used to print the next row of blanks and stars. (2 marks)

3. Develop the algorithm given as pseudocode in Task 1 (1) as a main program that calls the procedures (or functions) you implemented in Task 2 (2). For it, the NoOfSpaces, NoOfStars and MaxNoOfStars presented under the task description are stored in global variables. Hint: the condition "number of stars is the number required" in the algorithm could be coded as "NoOfStars > MaxNoOfStars". (4 marks)

Task 3: Executing your program (4 marks)

4. (i) Run your program for a pyramid of base 9, and test it to make sure that it works correctly. Screenshot the output and save it in a file named task3. Print the screenshot. (3 marks)

(ii) Print a copy of your PROGRAM SOURCE CODE and attach it in your answer booklet. (1 mark)

Task 4: Modifying your program (6 marks)

5. (i) Modify your program so that it asks the user to enter an odd positive integer, and then displays a diamond of stars based on that number. For example, the value 7 would produce: (4 marks)

   *
  ***
 *****
*******
 *****
  ***
   *

(i) Run your program for a pyramid of base 7, and test it to make sure that it works correctly. Screenshot the output of your program and save it in the file named task3. Also print the screenshot. (1 mark)

(ii) Print a copy of your PROGRAM SOURCE CODE and attach it in your answer booklet. (1 mark)

Section B: School trip (databases) – 20 marks

Task 5: School trips (20 marks)

A school keeps records of school trips, teachers who supervise these trips and pupils who attend trips in a database, in a way that will allow information about these details to be extracted. The data requirements are defined as follows:

• Each trip is assigned a trip id (unique), start date, end date and destination.
• Each teacher has a teacher id (unique), a title, first name and surname.
• Each pupil has an id (unique), first name and a surname.
• Each pupil may attend more than one trip.
• A trip can be attended by more than one pupil.
• Each trip is supervised by only one teacher at any given time.
• A teacher can supervise more than one trip.

The relational database has four tables, TEACHER, TRIP, PUPIL and PUPILTRIP, whose meanings are obvious, with the following attributes (the primary key is listed first and marked "PK"):

• TEACHER (TeacherID PK, Title, FirstName, Surname)
• TRIP (TripID PK, StartDate, EndDate, Destination, TeacherID)
• PUPIL (PupilID PK, PupilSurname, PupilFirstName)
• PUPILTRIP (PupilID, TripID), the primary key being the pair (PupilID, TripID)

(i) In your answer booklet, draw an Entity-Relationship diagram that shows entities, relationships and the degree of each relationship for the school trip database. (3 marks)

Note: for the following subtasks, first write in your answer booklet the SQL queries (or statements) used to implement its activities in your DBMS.

(ii) Using SQL statements, do the following using your favourite database management system (DBMS):

(a) Create a database called TripsDB. (1 mark)

(b) In TripsDB, create all the relations given above. (4 marks)

(iii) Using the SQL insert command:

(a) populate the TEACHER relation with the following 3 rows of data: (2 marks)

(b) populate the TRIP relation of your database with 3–5 rows of trip data. The data for each trip should be consistent with data for the TEACHER relation. (2 marks)

(c) populate the PUPIL relation with the following data: (2 marks)

(d) populate the relation PUPILTRIP such that its data is consistent with the relations PUPIL and TRIP. (2 marks)

(iv) Using SQL commands, write an SQL statement to query the database tables for each of the following:

(a) The TripID, StartDate, EndDate and Destination of all trips supervised by the teacher with teacher id T1. (2 marks)

(b) The TripID and the name of pupils who attended a trip supervised by the teacher with teacher id T1. (2 marks)

NB: You must print to show proof of the work done in (i), (ii), (iii) and (iv) above.

Our tips

• Section A is worth 30 marks and Section B 20 marks: in two hours, keep at least 45 minutes for the database section.
• Task 1 is the key to the whole section: count the spaces and stars of the first two lines of Figure 1 carefully, then deduce how they change from one line to the next. The procedures of Task 2 simply translate those answers into code.
• Test your program with small values (1, 3, 5) before the values asked, and keep a screenshot of every run requested.
• For the diamond, think of it as a pyramid followed by an upside-down pyramid without repeating the middle line.
• In Section B, create the tables in the right order: TEACHER and PUPIL before TRIP, and PUPILTRIP last, because of the foreign keys. Check that every TeacherID used in TRIP exists in TEACHER.
• The last query needs a join between PUPIL, PUPILTRIP and TRIP.

Beware of scams

Past GCE papers are free. Never pay, by Mobile Money or otherwise, for a "leaked" or "probable" paper before the exam: it is a well-known scam.