IBDP Computer Science A3.3 Database programming HL Paper 2 - New Syllabus

(a)

(i)
For the correct answer:

StaffID

(ii)
For the correct answer, either foreign key is acceptable:

RoleID
DepartmentID

Explanation: StaffID uniquely identifies each staff member and is therefore the primary key. RoleID links each staff member to a record in the ROLE table, while DepartmentID links the staff member to a department.

(b)
For the correct answer:

• ROLE and STAFF have a one-to-many relationship.
• STAFF and DEPARTMENT have a one-to-many relationship, or many-to-many may be accepted depending on how the relationship is represented.

Explanation: One role can be assigned to many staff members, while each staff member has only one role. A department can contain many staff members, and the question states that staff can work in several departments.

(c)
For the correct answer, any two suitable points from the same markscheme cluster may be awarded:

• A query provides a virtual representation or filtered view of the database.
• A query searches or filters the database according to specified criteria.
• A query can manipulate data in the database, for example using INSERT, DELETE, or UPDATE.
• A query can aggregate or summarize data using functions such as SUM(), AVG(), or COUNT().
• A query can group or sort data according to specified criteria.

Explanation: A query allows a user to retrieve or manipulate selected data from one or more tables without necessarily displaying the entire database.

(d)
For the correct answer:

• Select FirstName, Surname, and PayGrade.
• Join the STAFF and ROLE tables.
• Use the joining condition STAFF.RoleID = ROLE.RoleID.
• Apply both conditions: surname is Waters and pay grade is 17.

Example 1:

SELECT STAFF.FirstName, STAFF.Surname, ROLE.PayGrade
FROM STAFF INNER JOIN ROLE
ON STAFF.RoleID = ROLE.RoleID
WHERE STAFF.Surname = 'Waters'
AND ROLE.PayGrade = 17;

Example 2:

SELECT STAFF.FirstName, STAFF.Surname, ROLE.PayGrade
FROM STAFF, ROLE
WHERE STAFF.RoleID = ROLE.RoleID
AND STAFF.Surname = 'Waters'
AND ROLE.PayGrade = 17;

Equivalent Structured English is also acceptable.

Explanation: The STAFF table contains the staff member’s name and RoleID, while the ROLE table contains the corresponding PayGrade. The tables must therefore be joined using their common RoleID field before the surname and pay-grade conditions are applied.

(e)
For the correct answer:

• Pay grade values are whole numbers, so an integer is an appropriate data type.
• An integer uses less storage than a more general numeric type and allows PayGrade values to be sorted and used in mathematical operations.

Explanation: Pay grades such as \(17\) do not require decimal places. Using an integer ensures that only whole-number values are stored and allows values to be compared, sorted, and calculated efficiently.

(f)
Award up to [3] for each suitable method: identification of the method, explanation of how it ensures privacy, and application to the hospital staff data.

Method 1: Access control / authorization

• Use different levels of access or authorization.
• Only authorized users are permitted to access sensitive information.
• For example, restrict access to the ROLE table containing staff pay grades so that only authorized hospital administrators can view it.

Method 2: Encryption

• Encrypt the stored data in the database, or encrypt specific sensitive fields.
• The data is converted into ciphertext and cannot be understood without the appropriate key.
• For example, only authorized hospital employees with the decryption key can access encrypted staff information.

Other acceptable methods include:

• Data anonymisation, masking, or obfuscation: remove or transform personally identifiable information, for example by masking parts of sensitive staff data.
• Database views: expose only selected rows or columns to a user. For example, a department manager could be given a view containing only the staff data for their own department.
• Separating sensitive data: store sensitive information such as pay grade or date of birth in a separate table and restrict access to that table.

Explanation: Database privacy can be maintained by ensuring that sensitive staff information is available only to users who require it. This can be achieved through access controls, encryption, anonymisation, or restricted database views.