$ cat hands-on/enrollment-capstone.spec HO-16

Capstone · all seven areas

Section enrollment and assessment

Rehearse a full six-hour sitting: import students from a CSV file, define courses, enroll and drop under a unit limit, and print an assessment report. Fictional school, practice fees.

Spec no.: HO-16
Level: Capstone · 6 h
Time box: 6 h
Reference: Java · C#
Grading: Self-assessed · rubric is ours, not DICT's

01 Coverage

Area	Sub-skills practised	Role
01 Data manipulation	Validation, ArrayList handling, String	Also
02 Object-oriented programming	Abstraction, Inheritance, Encapsulation	Also
03 File handling	Read, Input/output validation	Also
04 Database manipulation	Create, Read, Delete	Also
05 Data structures and algorithms	Sorting	Also
06 Control structures	Conditional, Iteration	Also
07 Number system	Hexadecimal	Also

Sign up to start coding practice

Read the task freely. An account is required before editing and running a solution. Code runs also need an internet connection.

02 Requirements

Abstract Person (surname, given) and Student (student number, program) extends it. Course validates its own code (2–4 capitals and 2–3 digits) and units (1 to 6).
SQLite tables students, courses and enrollments with primary and foreign keys; enrollments has a composite key (student_no, code).
import file.csv skips the header, validates each row (student number nnnn-nnnn, four non-blank fields), skips duplicates, and prints Imported n students, skipped k (line a, b).
course CODE|Title|units adds a course or prints Invalid course: CODE.
enroll no CODE checks that the student and course exist, that the student is not already enrolled, and that total units stay at or under 24. drop no CODE removes an enrollment.
report lists every student sorted by surname then given name with units, the assessment at a practice ₱500 per unit, and a reference code: the amount in hexadecimal.

03 Input and output

Input

students.csv (given below) is in the working folder
One command per line

Output

One line per command; report prints one line per student

Sample input · stdin

import students.csv
import missing.csv
course IT101|Introduction to Computing|3
course IT102|Computer Programming 1|3
course GE101|Purposive Communication|3
course PE1|Physical Education|30
enroll 2026-0001 IT101
enroll 2026-0001 IT102
enroll 2026-0001 IT101
enroll 2026-0002 GE101
enroll 2026-0009 IT101
drop 2026-0001 IT102
report

Expected output · stdout

Imported 3 students, skipped 2 (line 4, 6)
File not found: missing.csv
Course IT101 added
Course IT102 added
Course GE101 added
Invalid course: PE1
Enrolled 2026-0001 in IT101
Enrolled 2026-0001 in IT102
Already enrolled: 2026-0001 IT101
Enrolled 2026-0002 in GE101
No such student: 2026-0009
Dropped 2026-0001 from IT102
Abad, Lea (2026-0003) 0 units, PHP 0.00, ref 0x0
Dela Cruz, Juan (2026-0001) 3 units, PHP 1500.00, ref 0x5DC
Santos, Maria (2026-0002) 3 units, PHP 1500.00, ref 0x5DC

Given file · students.csv

student_no,surname,given,program
2026-0002,Santos,Maria,BSCS
2026-0001,Dela Cruz,Juan,BSIT
bad,line
2026-0003,Abad,Lea,BSIT
2026-0001,Dela Cruz,Juan,BSIT

Match the sample exactly first, then write at least two test inputs of your own.

04 Edge cases to test

A duplicate row in the CSV
A missing file
Units of 30
Enrolling the same course twice
A student with no courses (0 units, ref 0x0)

05 Database

Engine: SQLite (Java: org.xerial sqlite-jdbc; C#: Microsoft.Data.Sqlite). An embedded database needs no server, which suits a timed sitting.

The reference uses an in-memory database so every run starts clean.

The table definitions are in the requirements above and in the reference solution.

06 Starter outline and hints

Suggested six-hour plan

0:00–0:30 plan tables, classes, commands
0:30–2:00 schema, Student/Course, import with validation
2:00–3:30 course, enroll, drop with all checks
3:30–4:30 report with sorting, amounts and hex reference
4:30–5:30 edge cases and messages
5:30–6:00 final run against the sample

Hints

Hint 1

SUM over an empty join returns NULL; wrap it in COALESCE(..., 0).

Hint 2

INSERT OR IGNORE returns 0 affected rows for a duplicate key, which is an easy way to detect duplicates on import.

07 Practice rubric

Criterion	Sub-skills	Look for	Pts
File import	ReadInput/output validation	Header skipped; bad and duplicate rows reported by line number; missing file handled.	20
OOP design	AbstractionInheritanceEncapsulation	Person/Student hierarchy; Course validates itself.	15
Database	CreateReadDelete	Keys and foreign keys; parameterised queries; correct unit sum with a join.	25
Enrollment rules	ConditionalValidation	Existence, duplicate and unit-limit checks in the right order.	15
Report	SortingHexadecimalArrayList handling	Sorted by surname then given name; amount and hex reference correct.	15
Command loop	IterationString	One clear loop with usage messages.	10
Total			100

Our own weighting for practice. It is not the DICT scoring scheme.

08 Self-check

Tick only what you have verified by running your own program.

Lines 4 and 6 of the CSV are skipped, and I know why each one is.
A student cannot go over 24 units (I tested it with nine 3-unit courses).
Abad, Lea appears first in the report with 0 units.
1500 is 0x5DC, and I can convert it by hand.
As an extension, I added an update command for course units and kept the unit limit enforced.

Loading saved checks…

09 Reference solution

A compilable sketch of one structure for the capstone. Both versions compiled and reproduced the sample output on Java 21 and .NET 8 when this site was built. Reference solutions are in Java and C#. The 2023 FAQ copy also lists C, C++ and Visual Basic 6.0 / .NET; the same structure carries over, but you will need your language's own file and database APIs.

Try the whole brief first. The reference is one correct approach, not the only one, and reading it early removes most of the practice value.

10 Exam-day habits

Work without internet search to simulate exam conditions.
Type the sample input yourself and compare your output line by line before you look at the reference.