Question 1
14 marks
College CourseTable data types, validation checks, and database queries
- Databases
(a)
2 marks
- Data consists of raw, unprocessed facts or figures without context (for example, the number `105` or string `'CS1201'`).
- Information is data that has been processed, organized, and interpreted within a meaningful context so it is useful to a human user (for example, knowing that course `CS1201` is `'Java Programming'` and lasts `105 hours`).
Mentor tip
Contrast raw, unorganized values (data) with processed, contextualized meaning (information).
(b)
3 marks
- `CourseID`: `String` / `VARCHAR` (alphanumeric text, e.g., `'CS1200'`).
- `Course_Name`: `String` / `VARCHAR` (text string).
- `Duration (Hours)`: `Integer` (or `Float`, representing whole hours like `10`, `105`).
- `Cost`: `Float` / `Real` / `Decimal` / `Currency` (numeric value with decimal places, e.g., `30.00`).
Mentor tip
Avoid generic terms like 'number'—specify `Integer` for `Duration` and `Float`/`Decimal`/`Currency` for `Cost`.
(c)
2 marks
- Data validation automatically checks data at the point of entry to ensure it is sensible, reasonable, and conforms to predefined format, type, and range rules.
- This prevents corrupt or out-of-bounds values from entering the database, maintaining data integrity and preventing processing errors in downstream queries.
Mentor tip
Emphasize automated checking of input against rules (type/range/format) to maintain database integrity.
(d)(i)
1 mark
Format check (or length check): Ensure `CourseID` consists of exactly 6 characters starting with 2 uppercase letters followed by 4 digits (e.g., `LL9999`).
Mentor tip
A format mask (2 letters + 4 digits) or exact length check (6 characters) is ideal for `CourseID`.
(d)(ii)
1 mark
Range check (or type check): Ensure `Cost` is a non-negative numeric value within a sensible range (e.g., `Cost >= 0.00` and `Cost <= 1000.00`).
Mentor tip
Specify a non-negative range check (`>= 0`) for monetary attributes.
(e)(i)
3 marks
- Step 1 (Projection): `SELECT Course_Name`.
- Step 2 (Source Table): `FROM CourseTable`.
- Step 3 (Filter Condition): `WHERE Duration >= 40 AND Duration <= 80` (which matches `'Java Programming – Advanced'` at 50 hours and `'Python Programming'` at 75 hours).
Mentor tip
Include all three query clauses clearly: the field to select (`Course_Name`), the table (`CourseTable`), and the range condition (`Duration >= 40 AND Duration <= 80`).
(e)(ii)
2 marks
Insertion (or Updating / Deleting): Adding new course records (`INSERT`) to `CourseTable` when the college introduces new classes, or modifying (`UPDATE`) existing records when course fees or durations change.
Mentor tip
Name any core data manipulation operation (`INSERT`, `UPDATE`, or `DELETE`) and briefly describe how it modifies the table.

