2024 CCEA A-Level Software Systems Development CL4 模擬試題連答案詳解

A feasibility study conducted during the initiation phase examines a proposed system across five key dimensions, often referred to by the acronym TELOS, each of which contributes something distinct to the decision of whether GreenLeaf's project should proceed, and typically involves different personnel.

Technical feasibility considers whether the required technology exists and can realistically be implemented — for example, whether a suitable database and booking application can be built and whether it can work with any hardware GreenLeaf already has. This is normally assessed by a systems analyst or IT specialist with the technical expertise to judge what is achievable.

Economic feasibility considers whether the likely benefits of the new system (for example, eliminating the kind of manual calculation errors seen in Document 3, and saving administrative time) justify its costs (development, hardware, staff training). This is typically assessed jointly by the business owner/manager, who understands the business's finances and priorities, working with a systems analyst who can help quantify the likely costs and savings through a cost-benefit analysis.

Legal feasibility considers whether the proposed system would comply with relevant law — significant here because the system will store clients' personal data (names and home addresses), so data protection requirements must be met. This is generally assessed by the owner/manager, often informed by legal or data protection compliance advice.

Operational feasibility considers whether the system will actually work in day-to-day practice — crucially, whether GreenLeaf's field employees, currently used to filling in paper timesheets, will be willing and able to adopt a new computerised system reliably. This is best assessed by the owner/manager in direct consultation with the field staff who will actually use the system.

Schedule feasibility considers whether the system can realistically be analysed, designed, built and rolled out within the timeframe the business needs it. This is typically assessed by a project manager or systems analyst, who can realistically estimate development timescales.

Together, these five assessments, drawing on the combined judgement of the owner/manager, systems analyst/IT specialist, project manager and end users (field staff), allow GreenLeaf to make a well-rounded, evidence-based decision on whether to proceed with the new system.
Final answer: Technical feasibility (can the required hardware/software/database be built and integrated with existing systems? — assessed by a systems analyst/IT specialist); Economic feasibility (do the projected benefits, e.g. reduced errors and admin time, outweigh the development, hardware and training costs? — assessed by the business owner/manager alongside a systems analyst, via cost-benefit analysis); Legal feasibility (does the system comply with data protection law given it stores client personal/address data? — assessed by the owner/manager, possibly with legal/compliance advice); Operational feasibility (will staff, such as field employees used to paper timesheets, actually be willing and able to use the new system day to day? — assessed by the owner/manager in consultation with field staff/users); Schedule feasibility (can the system realistically be designed, built and rolled out within the business's required timeframe? — assessed by the project manager/systems analyst)

Level 1 (1-2 marks): basic knowledge of feasibility with limited linkage to the case study, lacks clarity/coherence, little specialist vocabulary.
Level 2 (3-4 marks): good knowledge covering several TELOS components with clear case-study linkage and reasonable identification of personnel; good use of specialist vocabulary and coherence.
Level 3 (5-6 marks): comprehensive, accurate coverage of ALL FIVE TELOS components, each clearly and correctly linked to GreenLeaf's specific situation (e.g. client data/legal, field staff adoption/operational, manual error costs/economic), with correctly identified personnel for each; excellent scenario integration, high clarity, specialist diction throughout.

1. The business owner/manager of GreenLeaf is a key stakeholder as the project sponsor: they have commissioned the new system, will make major decisions about its scope and budget, and are ultimately financially responsible for the business, so they have a direct interest in the system succeeding and reducing the kind of costly manual errors seen in the case study.

2. Field employees, such as John Murphy and Aoife Ryan named in Document 3, are stakeholders as the system's end users: they are the people who will actually enter job and hours data into the new system on a daily basis, replacing the paper timesheet shown in Document 3. They are directly affected by data integrity issues, such as the miscalculated £25 total recorded for John Murphy's hedge trimming job (which should be £27.00), since an error of this kind could mean an employee is paid incorrectly.

3. Clients, such as Sarah Kelly and Tom Boyd named in Document 3, are stakeholders because their personal data (name and home address) will be captured and stored within the new system, and the accuracy of the jobs and hours recorded against their account directly affects the accuracy of any invoice or charge they receive, even though they have no direct involvement in operating the system itself.
Final answer: 1. The business owner/manager of GreenLeaf — the project sponsor who commissioned the system and will make key decisions about it, since they are financially responsible for the business and stand to benefit from (or be affected by the cost of) the new system; 2. Field employees such as John Murphy and Aoife Ryan — the end users who will use the system daily to record jobs completed, and who are directly affected by data integrity issues such as the miscalculated £25 total in Document 3, since errors like this can affect how much they are paid; 3. Clients such as Sarah Kelly and Tom Boyd — indirectly affected stakeholders whose personal data (name, address) will be stored in the new system and whose bills/invoices depend on the data being recorded accurately

[1] mark for correctly identifying each of three distinct, valid stakeholders (owner/manager; field employee/user; client), to a maximum of [3]; [1] further mark for each stakeholder for a valid, case-study-evidenced justification of their role/interest, to a maximum of [3]. Total [6].

The Agile Manifesto expresses four core value statements, each contrasting an Agile priority with a more traditional priority. 'Working software' is valued over comprehensive documentation (1-B), because Agile prioritises delivering a functioning product over producing extensive upfront paperwork. 'Customer collaboration' is valued over contract negotiation (2-C), because Agile prioritises ongoing, close engagement with the customer over rigidly following a fixed, pre-agreed contract. 'Responding to change' is valued over following a (fixed) plan (3-A), because Agile prioritises flexibility and adapting to changing requirements over sticking rigidly to an initial plan.
Final answer: 1-B, 2-C, 3-A

[1] mark for each correct match (1-B, 2-C, 3-A), to a maximum of [3].

The Waterfall approach is a traditional, sequential systems development methodology in which the project passes through a fixed sequence of distinct stages — typically requirements analysis, design, implementation, testing and deployment — with each stage completed in full and formally signed off before the next stage begins. Requirements are gathered and agreed once, at the very start of the project, and are not normally expected to change significantly afterwards. This structured, linear approach works well where requirements are clear, stable and well understood from the outset, and provides a clear, predictable project structure that is straightforward to plan and manage.

SCRUM, in contrast, is an Agile, iterative methodology in which the system is developed through a series of short, fixed-length development cycles known as sprints (commonly two to four weeks each). At the end of each sprint, a working increment of the system is typically produced, and the customer or product owner reviews this progress and helps reprioritise the requirements to be tackled in the next sprint. This allows the development team to respond to feedback and changing or newly discovered requirements throughout the project, rather than being committed to a single fixed set of requirements agreed only at the start.

For GreenLeaf's project, a SCRUM/Agile approach is likely to be more suitable than Waterfall. As a small business only now moving from a paper-based system, GreenLeaf's owner and field staff may not yet fully know every requirement the finished system should meet — for example, further reporting needs or additional validation rules (such as the pay-tier boundaries in Table 1) may only become clear once an initial version of the system is actually being used day to day. An Agile/SCRUM approach would allow GreenLeaf to receive a working version of the system early, start using it, and feed back real experience (including catching data issues such as the kind of manual calculation error seen in Document 3) into refining later sprints, rather than committing entirely to a single, fixed design based only on requirements understood before any part of the system has actually been used. A Waterfall approach would instead require GreenLeaf's full requirements to be correctly and completely specified right at the start, which carries more risk for a smaller business whose needs may only fully emerge through hands-on use of an early working system.
Final answer: Waterfall is a traditional, sequential methodology in which distinct stages (requirements, design, implementation, testing, deployment) are completed fully, one after another, with the full set of requirements fixed and agreed before design begins, and each stage normally must be signed off before the next starts, making it well suited to projects with clear, stable, well-understood requirements; SCRUM is an Agile, iterative methodology in which the system is developed in a series of short, fixed-length cycles ('sprints'), typically producing a working increment of the system at the end of each sprint, with the customer/product owner reviewing progress and reprioritising requirements between sprints, making it well suited to projects where requirements may evolve or are not fully known at the outset; for GreenLeaf, a small business whose exact requirements may become clearer once staff start using an initial version of the system (e.g. discovering further data or reporting needs), a SCRUM/Agile approach is likely more suitable than Waterfall, since it allows the system to be delivered incrementally, with early working versions used to correct issues (such as the kind of calculation error seen in Document 3) and refine requirements as real usage reveals them, rather than committing fully to a fixed design based on requirements gathered only once at the very start

Level 1 (1-3 marks): basic knowledge of Waterfall and/or SCRUM, limited linkage to the case study, lacks clarity/coherence, little specialist vocabulary.
Level 2 (4-6 marks): good knowledge contrasting Waterfall and SCRUM's key features with clear linkage to GreenLeaf's case study, and a reasonable suitability judgement; good use of specialist vocabulary and coherence.
Level 3 (7-8 marks): comprehensive, accurate contrast of Waterfall's sequential/fixed-requirements nature against SCRUM's iterative/sprint-based, feedback-driven nature, with a well-justified, specifically evidenced suitability judgement for GreenLeaf's situation; excellent scenario integration, high clarity, specialist diction throughout.

1. A Lifeline is the vertical dashed line beneath an object's box, representing that object's existence throughout the interaction (1-B). 2. An Activation bar (focus of control) is the narrow rectangle drawn on the lifeline showing when the object is actively processing or waiting for a reply (2-D). 3. A Synchronous message is a solid arrow with a filled arrowhead, representing a call where the sender waits for a response, such as BookingForm calling validateBooking on BookingController (3-C). 4. A Return message is the dashed arrow showing a value passed back to the calling object once its request has been processed, such as Database returning confirmationID to BookingController (4-A).
Final answer: 1-B, 2-D, 3-C, 4-A

[1] mark for each correct match (1-B, 2-D, 3-C, 4-A), to a maximum of [4].

Following the 'not available' branch of the sequence diagram: the Client first submits a booking request (containing the client ID, job type, date and start time) to the BookingForm. The BookingForm passes these details to the BookingController by calling validateBooking. The BookingController then calls checkAvailability on the Database, passing the requested date and start time. The Database processes this check and returns an availability result to the BookingController — in this branch, the result indicates the requested slot is NOT available. Because of this result, the BookingController does not proceed to call saveBooking; instead, it sends a bookingRejected return message, including a reason, back to the BookingForm. The BookingForm then calls displayRejection to show this rejection message (with its reason) to the Client. At no point in this branch is a new booking record saved to the Database, since the availability check failed before the save step was reached.
Final answer: the Client submits a booking request to the BookingForm, which passes it to the BookingController for validation; the BookingController asks the Database to check availability for the requested date/time; the Database returns an availability result indicating the slot is not available; because it is not available, the BookingController sends a 'bookingRejected' return message (with a reason) back to the BookingForm instead of saving the booking; the BookingForm then displays this rejection (with the reason) to the Client, and no booking record is saved to the Database

[1] correctly traces the initial request/validation/availability-check steps (messages 1-4); [1] correctly identifies that the 'not available' result leads to a bookingRejected message being returned instead of saveBooking being called; [1] correctly traces the rejection being displayed to the Client and that no record is saved. Total [3].

1. The diagram shown is a UML sequence diagram, identifiable by its lifelines and time-ordered messages between objects.

2. A sequence diagram is primarily used by systems developers and designers during analysis and design to show and verify the precise order of interactions (messages) between objects for a particular scenario. It can also be shared with clients or users to confirm that the intended behaviour of the system for that scenario matches their expectations, before development work begins.

3. (i) A Class diagram shows the STATIC structure of a system: the classes that exist, their attributes and operations, and the fixed relationships between them, without showing how the system behaves over time. (ii) A Sequence diagram, in contrast, shows the DYNAMIC behaviour of a system: how a specific set of objects interact and exchange messages, in a particular time order, for a given scenario.
Final answer: 1. a (UML) sequence diagram; 2. it is used by systems developers/designers (and can be shared with clients/users) to show and check the order of interactions/messages between objects for a specific scenario, helping confirm the design behaves as intended before it is built; 3. (i) Class diagram = STATIC (shows the fixed structure of classes and their relationships, not how they behave over time); (ii) Sequence diagram = DYNAMIC (shows how objects interact and messages are exchanged over time for a specific scenario)

1. [1] 'sequence diagram'.
2. [1] valid statement of purpose/audience (e.g. developers verifying/communicating the order of object interactions for a scenario).
3. [1] Class diagram correctly categorised as STATIC; [1] Sequence diagram correctly categorised as DYNAMIC. [2]
Total [4].

The Iron Triangle model states that a project's scope, time and cost are three interdependent constraints: changing any one of them, without adjusting at least one of the other two, will affect the quality (or feasibility) of the finished project.

In this case, GreenLeaf's request for an SMS reminder feature increases the project's SCOPE, since it adds new functionality (SMS integration, associated design work, coding and testing) that was not part of the original 8-week plan. Because this additional scope requires real additional work, it cannot simply be absorbed into the existing schedule and budget without some consequence elsewhere.

The project manager has, broadly, three options for managing this change. First, they could extend TIME: push back the original 8-week deadline to allow enough time to properly design, build and test the new SMS feature alongside the rest of the system. Second, they could increase COST: keep the original 8-week deadline, but add budget to bring in additional developer resource (or pay for overtime) so the extra scope can be completed within the same timeframe. Third, if both time and cost are genuinely fixed and cannot be changed, the project manager would need to reduce scope elsewhere — for example, by delaying or dropping a lower-priority feature originally planned for this release — to make room for the new SMS request without affecting the deadline or budget.

Rather than attempting to simply add the SMS feature on top of the existing plan without adjusting any other constraint (which would most likely reduce the quality of the delivered system, for example through rushed development or inadequate testing), the project manager should clearly present these trade-offs to GreenLeaf's owner, explaining the implications of each option, so that an informed, agreed decision can be made about which constraint(s) to adjust in response to the change request.
Final answer: the Iron Triangle states that scope, time and cost are interdependent constraints, so changing one without adjusting at least one other will affect quality; adding the SMS reminder feature increases the project's SCOPE, and since the SMS feature requires additional design, coding, integration with an SMS service and testing work, this extra scope cannot be absorbed for free — the project manager must either increase TIME (extend the 8-week deadline to accommodate the extra work), increase COST (add budget to bring in extra developer resource to keep the original deadline), or, if time and cost are truly fixed, reduce scope elsewhere (e.g. delay or drop a lower-priority feature) to make room for the new request without extending the deadline or budget; the project manager should discuss these trade-offs directly and transparently with GreenLeaf's owner so an informed decision can be made, rather than trying to silently absorb the extra scope, which would otherwise most likely reduce the quality or completeness of the final delivered system

Level 1 (1-2 marks): basic knowledge of the Iron Triangle, limited linkage to the case study, lacks clarity/coherence, little specialist vocabulary.
Level 2 (3-4 marks): good knowledge identifying that scope has increased and that time and/or cost may need to adjust, with reasonable case-study linkage; good use of specialist vocabulary and coherence.
Level 3 (5-6 marks): comprehensive, accurate discussion of all three constraints (scope increase, and the time/cost/scope-elsewhere trade-off options), correctly identifying the quality consequence of failing to adjust, and a well-reasoned discussion of how the project manager should manage this (e.g. transparent discussion with the client); excellent scenario integration, high clarity, specialist diction throughout.

Normal (typical) data is a value that would be expected in everyday, ordinary use, well within a valid range: for example, 10:30, a normal mid-morning booking time. Since 10:30 falls within 08:00-17:59, the system should accept it and apply the Standard tier rate of £15.00/hour.

Boundary data tests the exact edges of valid ranges, where errors are most likely to occur: for example, 18:00, which is the exact first minute of the Evening tier window. The system should accept this and correctly apply the Evening tier rate of £18.00/hour (not the Standard tier), confirming the boundary between tiers is handled correctly. (17:59, 08:00 or 20:59 would be equally valid boundary choices.)

Erroneous (invalid) data is data that does not conform to the expected format or valid range at all: for example, '25:99', which is not a valid time at all (hours cannot exceed 23, minutes cannot exceed 59). The system should reject this input entirely as an invalid time value/format, rather than attempting to assign a pay tier to it.

Extreme data tests values at the very outer limits of what the system must handle: for example, 20:59, the very last valid minute before the business's operating hours end. The system should still accept this and correctly apply the Evening tier rate, confirming the system behaves correctly right up to the edge of its valid operating window (00:00, just outside the valid range, testing correct rejection at the opposite extreme, would be an equally valid alternative answer).
Final answer: Normal: 10:30, accepted, Standard tier (£15.00/hour) applied; Boundary: 18:00 (or 17:59/08:00/20:59), accepted, Evening tier (£18.00/hour) applied at the 18:00 boundary; Erroneous: 25:99 (or '8am'), rejected — invalid time format/value; Extreme: 20:59 (or 00:00), accepted at 20:59 as the last valid moment before the business closes (Evening tier), OR rejected at 00:00 as outside operating hours

[1] Normal: valid typical time value given with a plausible correct expected outcome; [1] Boundary: a value sitting exactly on a tier/valid-range edge, with the correct tier/outcome for that exact edge; [1] Erroneous: an invalid time format/value given, with rejection as the expected outcome; [1] Extreme: a value at the very outer limit of the valid (or invalid) range, with a correct, consistent expected outcome. A further [1] mark each is awarded across the four rows for full technical correctness/consistency between the test value chosen and the tier/rejection stated, to a maximum of [6] overall.

Testing with all four types of test data is important because each type is designed to reveal a different category of potential fault, and relying on only one type would leave the system significantly under-tested.

Normal (typical) data, such as a Job Start Time of 10:30, confirms that the system behaves correctly under everyday, expected conditions. This matters because the overwhelming majority of real bookings will be normal, unremarkable times, so the system must handle these reliably as a baseline.

Boundary data, such as a Job Start Time of exactly 18:00 (the precise edge between the Standard and Evening pay tiers), is particularly important because programming errors very commonly occur exactly at the boundaries of a range — for example, a developer might mistakenly write a comparison as 'greater than 18:00' instead of 'greater than or equal to 18:00', which would incorrectly place a 18:00 booking in the wrong tier. Such an error would not be revealed by normal data alone, so boundary testing is essential to catch this class of fault.

Erroneous data, such as an invalid value like '25:99', confirms that the system correctly identifies and rejects input that does not represent a valid time at all, rather than crashing or, worse, silently accepting and processing nonsensical data. This is especially important for GreenLeaf given that Document 3 already shows a real-world example of a data entry/calculation error going unnoticed on paper; robust rejection of erroneous input in the new system helps prevent this kind of data integrity problem from recurring.

Extreme data, such as a Job Start Time of 20:59 (the very last valid minute before the business's operating hours end), tests the system's behaviour at the very outer limit of its valid range. This gives confidence that genuinely valid, but unusual, real-world bookings right at the edge of what is permitted will still be handled correctly, rather than being unexpectedly rejected or mishandled.

Together, testing all four types gives much stronger overall confidence that the Job Start Time validation, and by extension the wider system, will behave correctly across the full range of situations it may actually encounter in real use.
Final answer: normal data (e.g. 10:30) confirms the system behaves correctly under everyday, expected conditions, which is essential since this represents the vast majority of real-world use; boundary data (e.g. 18:00, the exact edge between the Standard and Evening tiers) is critical because off-by-one errors in comparison logic (e.g. using > instead of >=) are a very common source of bugs, and these errors would otherwise go completely undetected if only normal data were tested; erroneous data (e.g. '25:99') confirms the system correctly rejects invalid input rather than crashing or silently accepting nonsensical data, which is important for protecting data integrity given the kind of manual error already seen in Document 3; extreme data (e.g. 20:59, the very last valid moment) confirms the system correctly handles values at the very outer edge of its valid operating range, giving confidence the system will not fail unexpectedly for genuinely valid but unusual real-world bookings

Level 1 (1-2 marks): basic knowledge of test data types, limited linkage to the case study, lacks clarity/coherence, little specialist vocabulary.
Level 2 (3-4 marks): good knowledge covering most of the four test data types with a relevant example for each, reasonable case-study linkage; good use of specialist vocabulary and coherence.
Level 3 (5-6 marks): comprehensive, accurate discussion of ALL FOUR test data types, each with a specific, correct example drawn from the Job Start Time context, and a clear explanation of the distinct fault each type is designed to reveal (including the off-by-one boundary risk and the data integrity link to Document 3); excellent scenario integration, high clarity, specialist diction throughout.

1. relational — GreenLeaf's system organises data using the relational model.
2. table (relation) — data is organised into two-dimensional tables (relations) of rows and columns.
3. record (tuple) — a single row within a table, representing one occurrence of data.
4. hierarchical — a model organising data into a tree structure of parent and child records.
5. network — a model allowing a child record to have more than one parent, forming a web of links.
6. entity relationship (ER) — the model showing entities and the relationships between them, drawn before building the database.
7. primary key — the column(s) that uniquely identify each record in a table.
8. foreign key — a column that holds another table's primary key value, linking the two tables.
9. DBMS — the Database Management System software used to create, maintain, secure and control access to the database.
10. SQL — Structured Query Language, the standard language used to create tables and insert, update and query data.
Final answer: 1. relational, 2. table (relation), 3. record (tuple), 4. hierarchical, 5. network, 6. entity relationship (ER), 7. primary key, 8. foreign key, 9. DBMS, 10. SQL

[1] mark for each correctly completed gap (1-10), to a maximum of [10]. Accept minor equivalent wording (e.g. 'relation' for 'table', 'tuple' for 'record').

Document 3 repeats the same information across multiple rows unnecessarily. For example, 'John Murphy' is written out as EmployeeName on three separate rows, and Sarah Kelly's ClientName and full ClientAddress are written out twice, once for each job she had done. This kind of redundant, repeated data is inefficient in terms of storage, but more importantly creates a genuine risk of inconsistency: if a client later changes address, every single row mentioning that client would need to be found and updated correctly, and if even one row were missed or entered differently, the data would become contradictory.

This lack of structure is also directly connected to the data integrity issue already visible in Document 3: because there is no structured, automatic validation checking that Total = HoursWorked × Rate, the miscalculated total of £25 (instead of the correct £27.00) for John Murphy's hedge trimming job went unnoticed on the paper record. A properly structured database, by contrast, could calculate such totals automatically and consistently, rather than relying on error-prone manual arithmetic recorded directly into the record.
Final answer: the same EmployeeName is repeated across multiple rows for the same employee (e.g. 'John Murphy' appears three times), and the same ClientName and ClientAddress are repeated across rows for the same client (e.g. Sarah Kelly's address is written out twice) — this redundant, repeated data wastes storage and, more importantly, creates a real risk of inconsistency (e.g. if a client's address changes, every repeated row must be updated correctly, or different rows could end up disagreeing); this exact kind of unstructured, redundant record-keeping is also what allowed the miscalculated £25 total to go unnoticed, since there is no structured validation to check HoursWorked x Rate automatically

[1] correctly identifies specific repeated/redundant data in Document 3 (e.g. EmployeeName or ClientName/Address repeated across rows); [1] correctly explains the resulting risk of inconsistency/update anomaly; [1] links this lack of structure to the data integrity error (miscalculated total) already visible in Document 3. Total [3].

A relation is in second normal form (2NF) if two conditions are both satisfied. First, the relation must already be in first normal form (1NF): all attribute values must be atomic (no repeating groups), and the table must have a defined primary key. Second, every non-key attribute in the relation must be fully functionally dependent on the entire primary key — meaning that, where the primary key is made up of more than one column (a composite key), no non-key attribute is allowed to depend on only part of that key. If a non-key attribute does depend on only part of a composite key (a 'partial dependency'), the relation is not in 2NF, and that attribute (along with the part of the key it depends on) should be moved into a separate table.
Final answer: a relation (table) is in second normal form if it is already in first normal form, AND every non-key attribute is fully functionally dependent on the WHOLE of the primary key, not on only part of it — i.e. there is no partial dependency of any non-key attribute on just part of a composite (multi-column) primary key

[1] correctly states the relation must first be in 1NF; [1] correctly states every non-key attribute must depend on the whole primary key; [1] correctly explains this in terms of avoiding partial dependency on part of a composite key. Total [3].

1NF (First Normal Form): to reach 1NF, all attributes must hold atomic (single, indivisible) values, with no repeating groups, and the relation must have a primary key that uniquely identifies each row. Document 3's data, once each job entry is treated as one row (as shown), already has atomic values in every column; a suitable primary key is the composite of (EmployeeID, ClientID, JobType, JobDate), since together these uniquely identify each timesheet entry:
Timesheet(EmployeeID, EmployeeName, ClientID, ClientName, ClientAddress, JobType, JobDate, HoursWorked, Rate, Total)
PK = (EmployeeID, ClientID, JobType, JobDate)

2NF (Second Normal Form): to reach 2NF, every non-key attribute must depend on the WHOLE of this composite primary key, not just part of it. Here, EmployeeName depends only on EmployeeID (a partial dependency on only part of the key), and ClientName and ClientAddress depend only on ClientID (also a partial dependency). These attributes must be removed into their own tables, keyed on just the part of the key they actually depend on:
Employee(EmployeeID [PK], EmployeeName)
Client(ClientID [PK], ClientName, ClientAddress)
Timesheet(EmployeeID [PK, FK to Employee], ClientID [PK, FK to Client], JobType [PK], JobDate [PK], HoursWorked, Rate, Total)

3NF (Third Normal Form): to reach 3NF, there must be no transitive dependency — no non-key attribute depending on another non-key attribute rather than directly on the primary key. Here, Rate depends only on JobType (every job of a given type has the same standard rate, e.g. every 'Hedge Trimming' job is charged at £18/hour, regardless of which employee or client), not on the full Timesheet key — a transitive dependency via JobType. JobType (with its associated Rate) must therefore be moved into its own table. In addition, Total is a derived attribute (it can always be recalculated as HoursWorked × StandardRate) and should not be stored at all, since storing it risks exactly the kind of manual calculation error seen in Document 3 (where £25 was recorded instead of the correct £27.00):
Employee(EmployeeID [PK], EmployeeName)
Client(ClientID [PK], ClientName, ClientAddress)
JobType(JobTypeID [PK], JobTypeName, StandardRate)
Timesheet(EmployeeID [PK, FK to Employee], ClientID [PK, FK to Client], JobTypeID [PK, FK to JobType], JobDate [PK], HoursWorked)
Final answer: 1NF: Timesheet(EmployeeID, EmployeeName, ClientID, ClientName, ClientAddress, JobType, JobDate, HoursWorked, Rate, Total), PK = (EmployeeID, ClientID, JobType, JobDate); 2NF: Employee(EmployeeID [PK], EmployeeName), Client(ClientID [PK], ClientName, ClientAddress), Timesheet(EmployeeID [PK,FK], ClientID [PK,FK], JobType [PK], JobDate [PK], HoursWorked, Rate, Total); 3NF: Employee(EmployeeID [PK], EmployeeName), Client(ClientID [PK], ClientName, ClientAddress), JobType(JobTypeID [PK], JobTypeName, StandardRate), Timesheet(EmployeeID [PK,FK], ClientID [PK,FK], JobTypeID [PK,FK], JobDate [PK], HoursWorked) — with Total removed since it is a derived value (HoursWorked x StandardRate) that would otherwise risk the kind of manual calculation error seen in Document 3

1NF: [1] correct single Timesheet relation shown with all original attributes and atomic values; [1] correct composite primary key identified (EmployeeID, ClientID, JobType, JobDate). [2]
2NF: [1] EmployeeName and ClientName/ClientAddress correctly identified as partially dependent and removed; [1] correct resulting Employee and Client tables with correct PKs, and Timesheet correctly retains FKs to both. [2]
3NF: [1] Rate correctly identified as transitively dependent on JobType (not the full key) and JobType/Rate correctly removed into its own table with correct PK; [1] correct final 4-table structure with correctly labelled PKs and FKs throughout; [1] correct identification and removal of the derived Total attribute, with a valid reason linked to the Document 3 calculation error. [3]
Total [7].

An INSERT statement adds a new row to a table. The statement names the table (Client), lists the columns being populated in brackets, and supplies the corresponding values, in the same order, after the VALUES keyword. Text values must be enclosed in single quotes.

INSERT INTO Client (ClientID, ClientName, ClientAddress)
VALUES ('C03', 'Niamh Walsh', '8 Birch Row, Lisburn');
Final answer: INSERT INTO Client (ClientID, ClientName, ClientAddress) VALUES ('C03', 'Niamh Walsh', '8 Birch Row, Lisburn');

[1] correct 'INSERT INTO Client' clause with column list matching the three columns named; [1] correct 'VALUES' clause with all three values in matching order; [1] all text values correctly enclosed in single quotes; [1] statement correctly terminated with a semicolon and fully executable/syntactically correct as a whole. Total [4].

An UPDATE statement modifies existing rows. Since Timesheet's primary key is composite (EmployeeID, ClientID, JobTypeID, JobDate), all four fields must be specified in the WHERE clause, combined with AND, to uniquely identify this single record and avoid accidentally updating any other row.

UPDATE Timesheet
SET HoursWorked = 1.75
WHERE EmployeeID = 'E01' AND ClientID = 'C01' AND JobTypeID = 'JT02' AND JobDate = '2024-06-03';
Final answer: UPDATE Timesheet SET HoursWorked = 1.75 WHERE EmployeeID = 'E01' AND ClientID = 'C01' AND JobTypeID = 'JT02' AND JobDate = '2024-06-03';

[1] correct 'UPDATE Timesheet' clause; [1] correct 'SET HoursWorked = 1.75' clause; [1] WHERE clause correctly identifies at least three of the four composite key fields with correct values; [1] all four composite key fields correctly included, combined with AND, uniquely identifying the single target row. Total [4].

An ALTER TABLE statement modifies the structure of an existing table. The ADD clause names the new column and its data type, and the DEFAULT clause specifies the value that should be used for this column when no other value is explicitly provided (including, typically, for rows that already exist in the table).

ALTER TABLE Employee
ADD IsActive BOOLEAN DEFAULT TRUE;
Final answer: ALTER TABLE Employee ADD IsActive BOOLEAN DEFAULT TRUE;

[1] correct 'ALTER TABLE Employee' clause; [1] correct 'ADD IsActive' with a valid boolean-type keyword (e.g. BOOLEAN/BOOL/BIT); [1] correct 'DEFAULT TRUE' (or equivalent, e.g. DEFAULT 1) constraint; [1] statement correctly terminated and fully syntactically correct as a whole. Total [4].

When a DEFAULT constraint is specified as part of adding a new column to an existing table, the database automatically populates that default value into the new column for every row that already exists in the table, since those existing rows have no other value supplied for the new column. Here, that default value is TRUE, so every existing Employee record will have IsActive set to TRUE.
Final answer: TRUE (the default value specified in the ALTER TABLE statement is automatically applied to every existing row)

[1] correct answer 'TRUE' (or equivalent, e.g. 1).

The query must join three tables (Timesheet, Employee and JobType), filter to June 2024 only, aggregate hours × rate per employee, and sort the result.

SELECT e.EmployeeName, SUM(t.HoursWorked * j.StandardRate) AS TotalEarned
FROM Timesheet t
JOIN Employee e ON t.EmployeeID = e.EmployeeID
JOIN JobType j ON t.JobTypeID = j.JobTypeID
WHERE t.JobDate BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY e.EmployeeName
ORDER BY TotalEarned DESC;

Checking this against the sample data: John Murphy's jobs are (2 hrs × £15) + (1.5 hrs × £18) + (2 hrs × £15) = 30 + 27 + 30 = £87.00; Aoife Ryan's job is (3 hrs × £18) = £54.00. Both jobs fall within June 2024, so both employees appear in the result, correctly ordered with the higher total (John Murphy, £87.00) listed before the lower total (Aoife Ryan, £54.00), confirming the query's logic produces the expected output: John Murphy | 87.00, then Aoife Ryan | 54.00.
Final answer: SELECT e.EmployeeName, SUM(t.HoursWorked * j.StandardRate) AS TotalEarned FROM Timesheet t JOIN Employee e ON t.EmployeeID = e.EmployeeID JOIN JobType j ON t.JobTypeID = j.JobTypeID WHERE t.JobDate BETWEEN '2024-06-01' AND '2024-06-30' GROUP BY e.EmployeeName ORDER BY TotalEarned DESC;

[1] correct SELECT of e.EmployeeName; [1] correct use of SUM() aggregate function; [1] correct multiplication of HoursWorked * StandardRate inside the SUM; [1] correct AS TotalEarned alias; [1] correct FROM Timesheet (with alias); [1] correct JOIN to Employee; [1] correct ON condition linking Timesheet.EmployeeID = Employee.EmployeeID; [1] correct JOIN to JobType; [1] correct ON condition linking Timesheet.JobTypeID = JobType.JobTypeID; [1] correct WHERE clause filtering JobDate to June 2024 (BETWEEN or equivalent date range condition); [1] correct GROUP BY e.EmployeeName; [1] correct ORDER BY TotalEarned DESC. Total [12]. Award credit for any syntactically valid equivalent (e.g. MONTH()/YEAR() functions instead of BETWEEN) that correctly achieves the same filtering.