Array of Records

Creating a record structure:

class MyClass:
    def __init__(self):
        self.variable = 0
        self.variable1 = ""
        self.variable2 = 0.0
        self.variable3 = false

Creating an array:

myArray = []
myClass = MyClass()
#do stuff with the instance then add to the array
myArray.append(myClass)

Or..

myArray = [MyClass() for x in range(15)]

Accessing a value:

myArray[x].variable1 = 5;

if myArray[x].variable2 == 7:
    print(myArray[x].variable3)

Parallel Arrays

Creating the arrays:

names = []
ages = []
formClass = []
pineapples = []

# Then append to use..
names.append(subLine[0])

Or creating fixed size:

names = [""] * 15
ages = [0] * 15
formClass = [""] * 15
pineapples = [0.0] * 15

Accessing a value:

names[0] = "Jacob"
ages[x] = 73
print(pineapples[i])

Finding Maximum

In Python code:

def findMax(array):
    maximum = array[0]
    maxIndex = 0
    for x in range(1, len(array)):
        if array[x] > maximum:
            maximum = array[x]
            maxIndex = x
    return maximum

In pseudocode:

FUNCTION findMax(ARRAY OF INTEGER array) RETURN maximum
    DECLARE maximum INITIALLY array[0]
    DECLARE maxIndex INITIALLY 0
    FOR x FROM 1 TO LENGTH OF array - 1
        IF array[x] > maximum THEN
            SET maximum TO array[x]
            SET maxIndex TO x
        END IF
    END FOR
    RETURN maximum
END FUNCTION

Count Occurrence

In Python code:

def count(array):
    count = 0
    for x in range(len(array)):
        if array[x] == searchKey:
            count += 1
    return count

In pseudocode:

FUNCTION count(ARRAY OF INTEGER array) RETURN count
    DECLARE count AS INTEGER INITIALLY 0
    FOR x FROM 0 TO LENGTH OF array - 1
        IF array[x] = searchKey THEN
            ADD 1 TO count
        END IF
    END FOR
    RETURN count
END FUNCTION

Linear Search

In Python code:

def search(array, searchKey):
    found = false
    position = -1
    index = 0
    while found == false and position < len(array):
        if array[x] == searchKey:
            found = true
            position = index
        else:
            index += 1
    if found == false:
        print("Thing not found")
    else:
        print("Found the thing at position: ", position)
    return position

In pseudocode:

FUNCTION search(ARRAY OF TYPE array, TYPE searchKey) RETURN position
    DECLARE found AS BOOLEAN INITIALLY false
    DECLARE position AS INTEGER INITIALLY -1
    DECLARE index AS INTEGER INITIALLY 0
    REPEAT WHILE found = false AND position < LENGTH OF array - 1
        IF array[x] = searchKey THEN
            SET found TO true
            SET position TO index
        ELSE
            ADD 1 TO index
        END IF
    END REPEAT
    IF found = false THEN
        SEND "Thing not found" TO DISPLAY
    ELSE
        SEND "Found the thing at position: " & position TO DISPLAY
    END IF
    RETURN position
END FUNCTION

Pseudocode bible

• Declaring a variable

DECLARE <name> AS <type> INITIALLY <value>

DECLARE count AS INTEGER INITIALLY 0

• Setting a variables value

SET <variable> TO <value>

SET surname TO "Headley"

• Changing a variables value

ADD <value> TO <variable>

ADD 10 TO score

• Getting a value (input)

GET <variable> FROM USER

GET username FROM USER

• Displaying a value (output)

SEND <value> TO DISPLAY

SEND "Total score is: " & total TO DISPLAY

• Basic logic

IF <condition> THEN
    DO STUFF
ELSE IF <condition> THEN
    DO STUFF
ELSE
    DO STUFF
END IF

IF age > 18 THEN
    DO STUFF
ELSE IF age < 12 THEN
    DO STUFF
ELSE
    DO STUFF
END IF

• Fixed loop

FOR <variable> FROM <start> TO <end>
    DO STUFF
END FOR

FOR x FROM 0 TO 50
    DO STUFF
END FOR

• Conditional loop

REPEAT WHILE <variable> = <value>
    DO STUFF
END REPEAT

REPEAT WHILE found = false
    DO STUFF
END REPEAT

• Arrays

When referencing arrays of records in pseudocode, use them as you would in Python. e.g. IF array[x].variable = value THEN

DECLARE <name> AS ARRAY OF <type> INITIALLY []

DECLARE pupils AS ARRAY OF Pupil INITIALLY []

DECLARE marks AS ARRAY OF INTEGER INITIALLY [90, 75, 15, 45]

DECLARE pupils AS ARRAY OF Pupil OF SIZE 15

• Functions

FUNCTION <name> (<type> <variable>, ...) RETURNS <type>
    ...
    RETURN <variable>
END FUNCTION

FUNCTION doNothing (INTEGER counter, STRING name) RETURNS INTEGER
    ...
    RETURN age
END FUNCTION

• Procedures

PROCEDURE <name> (<type> <variable>, ...)
    ...
END PROCEDURE

PROCEDURE doNothing (INTEGER counter, ARRAY OF STRING names)
    ...
END PROCEDURE

• Records

RECORD <name> IS (<type> <variable>, ...)

RECORD Pupil IS (INTEGER pupilID, STRING pupilName, INTEGER age)

• Reading from a CSV file

OPEN FILE "<fileName>.csv" FOR READING
    SET lines TO FILE.readLines()
    DO SOMETHING
CLOSE FILE

OPEN FILE "pupils.csv" FOR READING
    SET lines TO FILE.readLines()
    DO SOMETHING
CLOSE FILE

• Writing to a CSV file

OPEN FILE "<fileName>.csv" FOR WRITING
    WRITE LINE <value>
CLOSE FILE

OPEN FILE "pupils.csv" FOR WRITING
    WRITE LINE "Pupil name: " & pupilName
CLOSE FILE

SELECT query

SELECT <field1>, <field2>, ...
FROM <table1>
WHERE <field1> = <value1>
ORDER BY <field1> ASC/DESC;

SELECT id, name, age
FROM Pupils
WHERE age >= 15
ORDER BY id ASC;

INSERT query

INSERT INTO <table1> (<field1>, <field2>, ...)
VALUES (<value1>, <value2>, ...);

INSERT INTO Pupils (id, name, age)
(1, "Ross", 16);

UPDATE query

Remember if using multiple tables in WHERE clause,
include both in the UPDATE line and include an equi-join.

UPDATE <table1>
SET <field1> = <value1>, <field2> = <value2>, ...
WHERE <field1> = <value1>;

UPDATE Pupils
SET grade = 'A', mark = 100
WHERE id = 5 AND name = "Ross";

UPDATE <table1>, <table2>
SET <table1.field1> = <value>
WHERE <table1.primaryKey> = <table2.foreignKey> 
    AND <table1.field1> = <value>;

DELETE query

DELETE FROM <table1>
WHERE <field1> = <value1>, ...;

DELETE FROM Pupils
WHERE id = 5 AND name = "Ross";

COUNT

Remember COUNT will count the number of records returned by your query
It will not add up any integer values.

SELECT <field1>, COUNT(*)
FROM <table1>
WHERE <condition>
GROUP BY <field1>;

SELECT name, COUNT(*)
FROM Pupils
WHERE grade = "A"
GROUP BY name;

SUM

Remember SUM will add up the values of the chosen field.

SELECT <field1>, SUM(<field2>)
FROM <table1>
WHERE <condition>
GROUP BY <field1>;

SELECT name, SUM(marks)
FROM Pupils
WHERE exam = "Higher"
GROUP BY name;

AVG

SELECT <field1>, AVG(<field2>)
FROM <table1>
WHERE <condition>
GROUP BY <field1>;

SELECT name, AVG(marks)
FROM Pupils
WHERE exam = "Higher"
GROUP BY name;

MIN/MAX

SELECT <field1>, MIN(<field2>)
FROM <table1>
WHERE <condition>
GROUP BY <field1>;

SELECT name, MIN(marks)
FROM Pupils
WHERE exam = "Higher"
GROUP BY name;

Computed values

Computed values are when you carry out a calculation involving multiple fields. They are different from aggregate functions because they involve multiple values from ONE record. Aggregate functions use values from a GROUP of records.

SELECT (<field1> * <field2>), (<field1> / <value>), ...

SELECT (quantity * price), (cost / 10)

Wildcards

A wildcard is used when searching for part of a value. For example to search for January 2010: "*/01/2010" or "??/01/2010". Wildcards can be useful for date fields and text fields. An asterisk is used to search for ANY number of characters, a ? is used when only one character should be present.

WHERE <field1> LIKE "*<value>??"

WHERE date LIKE "??/12/2025"

WHERE film LIKE "*Pirate*"

Alias

An alias is used to give a name to a field or computed value. This is usually either to produce a better looking report (query result) or to use the value in another query. Remember that you CANNOT order by an alias unless it is from another query.

SELECT (<field1> - <field2>) AS [<name>]

SELECT (income - tax) AS [Net income]

SELECT MAX(iq) AS [Smartest]

Equi-joins

Remember if you EVER use more than one table, regardless of the type of query, you MUST include an equi-join in the WHERE clause.

WHERE <table1>.<primaryKey> = <table2>.<foreignKey>, ...

WHERE Pupils.pupilId = Exams.pupilId

WHERE Book.authorId = Author.authorId
    AND Book.settingId = Setting.settingId

Entity relationship diagrams

An ERD shows the relationships between entities within a database. At Higher level you should be showing: Name of entities, relationship between entities, cardinality of relationship (1:1, 1:M), and although not usually required, you should be prepared to show the attributes of each entity.

Entity Occurrence diagrams

Entity occurrence diagrams show the relationship between occurrences of entities. These help to establish the relationship between the entities (1:1) or (1:M).

Custom computers written answers

Custom computers python solution

class Computer:
    def __init__(self):
        self.id = 0
        self.brand = ""
        self.model = ""
        self.ram = ""
        self.storage = ""
        self.speed = ""
        self.cost = 0.0
        self.date = ""

#Read from file into array of records.
def readFile():
    computers = []
    with open("computers_data.csv", "r") as file:
        lines = file.readlines()
        for line in lines:
            line = line.replace("\n", "")
            subline = line.split(",")
            # Create a record
            computer = Computer()
            computer.id = int(subline[0])
            computer.brand = subline[1]
            computer.model = subline[2]
            computer.ram = subline[3]
            computer.storage = subline[4]
            computer.speed = subline[5]
            computer.cost = float(subline[6])
            computer.date = subline[7]
            # Add to the array of records
            computers.append(computer)
    return computers

#Find maximum in the correct year
def findDearest(computers, year):
    maximum = 0
    for i in range(len(computers)):
        if computers[i].date[7:9] == year and computers[i].cost > maximum:
            maximum = computers[i].cost
    return maximum

#Find the difference between the most expensive computer in 2015 and the most expensive computer in 2024.
def findDifference(computers):
    expensive15 = findDearest(computers, "15")
    expensive24 = findDearest(computers, "24")
    costDifference = expensive24 - expensive15
    return costDifference

#Search for computers made in the month and year entered by the user.
def searchComputer(computers, month, year):
    position = -1
    index = 0
    while position == -1 and index < len(computers):
        if computers[index].date[3:6] == month and computers[index].date[7:9] == year:
            position = index
        index = index + 1
    return position

#Display the total number of computers created by Samsung and the total number of computers created by HP.
def countBrand(computers, brand):
    count = 0
    for i in range(len(computers)):
        if(computers[i].brand == brand):
            count = count + 1
    print(brand, "computers:", count)

#Write the findings to a text file.
def writeFile(costDifference, id):
    with open("output.txt", "w") as file:
        file.write(str(costDifference))
        if id > -1:
            file.write(str(id))
        else:
            file.write("Not found")

# Main program
computers = readFile()
costDifference = findDifference(computers)
month = input("Enter the month that you want to search: ")
year = input("Enter the year that you want to search: ")
computerId = searchComputer(computers, month, year)
countBrand(computers, "Samsung")
countBrand(computers, "HP")
writeFile(costDifference, computerId)

SQA 2024 Assignment SDD + DDD Answers

The program code and queries are further down the page.

Program code

#module with parameters passed or returned
def readFile():
    company = []
    numEmployees = []
    ceoSalary = []
    with open("companies.csv") as file:
        lines = file.readlines()
        for line in lines:
            line = line.replace("\n", "")
            subLine = line.split(",")
            #assigned to three parallel arrays
            company.append(subLine[0])
            numEmployees.append(int(subLine[1]))
            ceoSalary.append(int(subLine[2]))
    return company, numEmployees, ceoSalary

#module with single formal parameter (0 marks for using global variables)
def findMaxPos(myArray):
    maximum = myArray[0]
    maxIndex = 0
    #loop to traverse to end of array with if statement to find correct max position
    for index in range(1, len(myArray)):
        if myArray[index] > maximum:
            maximum = myArray[index]
            maxIndex = index
    #maximum position returned 
    return maxIndex

#module with correct parameters passed in (company, ceoSalary), nothing returned
def findDifference(company, ceoSalary):
    chosenCompany = input("Enter name of a company to search for: ")
    
    found = False
    position = 0
    
    maxSalaryPos = findMaxPos(ceoSalary)
    
    for i in range(len(company)):
        #array searched for selected company position
        if company[i] == chosenCompany:
            found = True
            position = i
            
    #use of flag variable to implement not found message
    if found:
        #use of max position and found position to calculate difference
        difference = ceoSalary[maxSalaryPos] - ceoSalary[position]
        print("Company with highest salary:", company[maxSalaryPos])
        print("Chosen company:", chosenCompany)
        print("Difference in salaries:", difference)
    else:
        print("Company not found.")

#module with correct parameter passed in and nothing passed out
def findHighestEmployees(numEmployees):
    mostEmployeesPos = findMaxPos(numEmployees)

    #count initialised and incremented
    count = 0
    
    for i in range(len(numEmployees)):
        #Use of max position in condition to find those within 10%
        if numEmployees[i] >= (numEmployees[mostEmployeesPos] * 0.9):
            count += 1
            
    print("Companies within 10% of highest employees:", count)

#Main program
companies, numEmployees, ceoSalary = readFile()
findDifference(company, ceoSalary)
findHighestEmployees(numEmployees)

SQL queries

Question 2c

SELECT initial, surname, swimCategroy, teamName, COUNT(*) AS [Races won]
FROM Result, Swimmer, Team
WHERE Result.swimmerID = Swimmer.swimmerID AND
 Swimmer.teamRef = Team.teamRef AND
 position = 1
GROUP BY initial, surname, swimCategory, teamName

Question 2d

SELECT MIN(raceTime) AS fastestTime
FROM Result
WHERE (lane = 1) OR (lane = 8);

SELECT initial, surname, teamName, city, eventDate
FROM Event, Race, Result, Swimmer, Team, [previousQuery]
WHERE Event.eventID = Race.eventID AND
 Race.raceNumber = Result.raceNumber AND
 Result.swimmerID = Swimmer.swimmerID AND
 Swimmer.teamRef = Team.teamRef AND
 time = fastestTime AND (lane = 1 OR lane = 8);

Question 2e

SELECT teamName, COUNT(*) AS [Total medals won]
FROM Result, Swimmer, Team
WHERE Result.swimmerID = Swimmer.swimmerID
 AND Swimmer.teamRef = Team.teamRef
 AND Result.position >= 1 AND Result.position <= 3
GROUP BY teamName
ORDER BY COUNT(*) DESC;

SQA 2023 Assignment SDD + DDD Answers

The program code and queries are further down the page.

Program code

# read data from file and store into parallel arrays
def readFile():
    attraction = []
    category = []
    visitors = []
    daysOpen = []
    height = []
    with open ("attractions.csv", "r") as file:
        lines = file.readlines()
        for line in lines:
            line = line.replace("\n", "")
            subline = line.split(",")
            attraction.append(subline[0])
            category.append(subline[1])
            visitors.append(int(subline[2]))
            daysOpen.append(int(subline[3]))
            height.append(subline[4])
    return attraction, category, visitors, daysOpen, height

# find and display the name(s) of the least / most visited attractions
def findMostLeast(attraction, visitors, mode):
    minmax = visitors[0]
    position = 0
    if mode == "min":
        for i in range(len(visitors)):
            if visitors[i] < minmax:
                minmax = visitors[i]
                position = i
    elif mode == "max":
        for i in range(len(visitors)):
            if visitors[i] > minmax:
                minmax = visitors[i]
                position = i
    attractions = []
    for i in range(len(visitors)):
        if visitors[i] == minmax:
            attractions.append(attraction[i])
    return attractions
        
# Write the names of roller coasters that require service to service.csv
def findServices(attraction, category, daysOpen):
    with open("service.csv", "w") as file:
        for i in range(len(attraction)):
            if category[i] == "Roller Coaster":
                days = daysOpen[i] % 90;
                if(90 - days) <= 7:
                    file.write(attraction[i] + "\n");

# Question 1cii
def displayHeight(height):
    count = 0
    for i in range(len(height)):
        if height[i][0] == "1":
            count += 1
    print("Number of rollercoasters with 1.0m and above height restriction:", count)

attraction, category, visitors, daysOpen, height = readFile()
maximum = findMostLeast(attraction, visitors, "max")
print("Maximum:", maximum)
minimum = findMostLeast(attraction, visitors, "min")
print("Minimum:", minimum)
findServices(attraction, category, daysOpen)
displayHeight(height)

SQL queries

Question 2b

SELECT gnomeName, SUM(quantity) AS [Total Gnomes Sold]
FROM Gnome, GnomePurchase
WHERE Gnome.gnomeID = GnomePurchase.gnomeID 
 AND Description Like "*solar*"
GROUP BY gnomeName
ORDER BY SUM(quantity) DESC;

Question 2c

SELECT max(unitPrice) AS [Expensive]
FROM Gnome;


SELECT emailaddress, Orders.orderID, quantity
FROM Customer, GnomePurchase, Gnome, CustOrder, MaxGnomePrice
WHERE Customer.customerId = Orders.customerID 
 AND Orders.orderID = GnomePurchase.orderID 
 AND Gnome.gnomeID = GnomePurchase.gnomeID 
 And unitPrice = [Expensive] 
 And quantity >= 3;

Question 2d

SELECT forename, surname, SUM(unitPrice*1.2*quantity) AS [Total to Pay £]
FROM Customer, Gnome, GnomePurchase, CustOrder
WHERE CustOrder.orderID = "ord0024" 
 AND Customer.customerID = CustOrder.customerID 
 AND CustOrder.orderID = GnomePurchase.orderID 
 AND gnome.gnomeID = GnomePurchase.gnomeID
GROUP BY forename, surname;