A-LevelComputer ScienceDatabases9618/12 · Oct/Nov 2024 · Q612 Marks

A company uses a relational database to store data about its customers, employees and the individual repair jobs that customers have booked.

(a)Explain the benefits of using a relational database instead of a file-based approach.[3]
(b)(i)The company decides which employees will work on each repair job. An employee can log into the database to access information about their repair jobs. The database is normalised and includes these tables: • CUSTOMER stores personal data about each customer • EMPLOYEE stores personal data about each employee • LOGIN DATA stores the username and password for each employee • JOB stores the data about each repair job • JOB_EMPLOYEE stores the employees that are working on each repair job. Identify each relationship between the database tables and explain how each relationship can be implemented in the normalised database.[6]
(b)(ii)The database also has the table INVOICE that stores data about each invoice that is sent to a customer. Example data from the table INVOICE is given. InvoiceID DateSent Amount Paid JobID 29262 12/12/2023 105.20 Y 221 26765 11/11/2023 200.00 Y 315 13290 02/01/2024 50.00 Y 315 34090 05/02/2024 25.95 N 569 Write a Structured Query Language (SQL) script to return the total amount of all the invoices sent in the year 2023 that have been paid.[3]

Mark scheme

Marking points from the official mark scheme (Oct/Nov 2024). The mark or mark type is shown in brackets.

[3] 1 mark each to max 3 e.g. • There is reduced data redundancy // less repeated data • ... because each item of data is only stored once • Data consistency is maintained // Data integrity is improved • ... changes in one table will automatically update in another • ... linked data cannot be entered differently in two tables • Program-data independence is ensured • ... changes to the data do not require programs to be re-written // queries are not dependent on the structure of the data • Complex queries are easier to run • Different views can be provided • ... so users can only see specific aspects of the database • Multiple concurrent access is possible • ... through record locking
[6] 1 mark each to max 6 • CUSTOMER to JOB is 1 to many • implemented by Primary Key in CUSTOMER is Foreign Key in JOB • EMPLOYEE to LOGIN_DATA is 1 to 1 • implemented by Primary Key in EMPLOYEE is Foreign Key in LOGIN_DATA • JOB to JOB_EMPLOYEE is 1 to many • implemented by Primary Key in JOB is Foreign Key in JOB_EMPLOYEE • EMPLOYEE to JOB_EMPLOYEE is 1 to many • implemented by Primary Key in EMPLOYEE is Foreign Key in JOB_EMPLOYEE
[3] 1 mark each • Select SUM of Amount • From the correct table and one correct condition • Remaining correct condition Example: SELECT SUM(Amount) FROM INVOICE WHERE Paid = "Y" AND DateSent >= #01/01/2023# AND DateSent <= #31/12/2023#

On Oracle Prep you can write or photograph your own answer and have it marked by AI against this mark scheme, with feedback on what you missed.