DATABASES: END OF TOPIC REVIEW

A field describes one attribute, such as Units. A record is all the values for one model, such as E001, Camera, Media, 3. Each row represents a model, so its Units value may describe several available units.

• Precise field definition: one mark.
• Suitable field example: one mark.
• Record definition with complete row example: one mark.
• Model-versus-unit distinction: one mark.

Writing focus: Use precise definitions, then because or therefore to link record meaning to the count.

LoanCode uses text/alphanumeric to retain its identifier representation. BorrowDate uses date/time. Returned uses Boolean. Even a valid date value may differ from the actual borrowing date, so verification against a reliable source is also needed.

• Text/alphanumeric for LoanCode: one mark.
• Date/time for BorrowDate: one mark.
• Boolean for Returned: one mark.
• Valid type does not prove real-world accuracy: one mark.

Writing focus: Name each type clearly, then connect the limitation to an example of an inaccurate but valid value.

LoanID is required and unique, so it distinguishes each loan record. StudentID repeats for a student’s different loans; EquipmentID repeats when the equipment is loaned again; BorrowDate repeats when several loans occur on the same date.

• LoanID unique and required: one mark.
• StudentID can repeat: one mark.
• EquipmentID can repeat: one mark.
• BorrowDate can repeat: one mark.

Writing focus: Link each field’s repeatability to the meaning of a loan record. Do not merely label the fields unsuitable.

AND requires both tests and returns Robot Kit only. OR requires at least one test and returns Camera, Microphone, Robot Kit and Controller. Robot Kit satisfies both but is returned once.

• AND requires both: one mark.
• Correct AND model: one mark.
• OR accepts either or both: one mark.
• Correct OR models: one mark.

Writing focus: Use a shared comparison point, then illustrate it with records. Include whereas or because to explain the changed result.

One valid query follows. The output is Camera, 3, followed by Microphone, 2. Tripod fails the availability condition.

SELECT Model, Units
FROM Equipment
WHERE Category = 'Media' AND Units >= 1
ORDER BY Units DESC;

• SELECT fields and FROM table: one mark.
• Correct category and inclusive availability condition combined with AND: one mark.
• ORDER BY Units DESC: one mark.
• Correct output and order: one mark.

Writing focus: Write the complete query, then present only the requested columns in the specified order.

COUNT(*) is 5 model records. SUM(Units) is 3 + 2 + 4 + 0 + 1 = 10 available units. Counting rows measures the number of catalogue models, whereas summing Units measures the available quantity across those models.

• Count 5: one mark.
• Correct addition: one mark.
• Sum 10: one mark.
• Distinct meaning of models and available units: one mark.

Writing focus: Show working and label the units of meaning. Explain the difference rather than only quoting two numbers.

Three passes the type and non-negative rule, but it differs from the source. Validation is useful for rejecting impossible values such as a negative count, yet it is insufficient for accuracy. Compare the entry with the inventory sheet, or use independent double entry with mismatch checking.

• Three passes the stated rule: one mark.
• Difference from source means inaccurate entry: one mark.
• Reasoned judgement about useful but insufficient validation: one mark.
• Suitable contextual verification method: one mark.

Writing focus: Give a judgement supported by the scenario. Use although and therefore to connect benefit and limitation.

LoanID can be an assigned unique required text identifier (an assigned INTEGER ID is also valid). StudentCode uses text/alphanumeric; BorrowDate uses date/time; Returned uses Boolean. Select LoanID as the primary key. The query below assumes the DBMS supports FALSE as a Boolean literal; FALSE is not quoted as text. COUNT(*) counts unreturned loan records rather than adding identifiers. StudentCode repeats when the same student borrows again.

SELECT COUNT(*)
FROM Loans
WHERE Returned = FALSE;

• Suitable LoanID type and primary-key selection with required uniqueness: one mark.
• Text StudentCode preserving zeros: one mark.
• Date/time BorrowDate and Boolean Returned: one mark.
• Correct SELECT COUNT(*) FROM Loans: one mark.
• WHERE Returned = FALSE with explanation of counting loans: one mark.
• StudentCode repeatability explained: one mark.

Writing focus: Connect each design choice to its requirement. State assumptions where SQL Boolean literal syntax depends on the database.