Question 7 of 8

Question 7 (10 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. The vehicle table's declared schema (Appendix A) is the specification under test — four NOT NULL columns, three of type char(20) and one int, with a composite primary key on (vehicle_name, vehicle_type) rather than a single-column key.

Find. A black-box test suite of INSERT statements, derived purely from the table's declared interface (no knowledge of the DBMS internals), that exercises every constraint the schema states: NOT NULL on each column, the CHAR(20) length boundary, the composite primary key, and the integer type of the price column.

Approach. Apply equivalence partitioning to identify one valid class and one invalid class per constraint, then apply boundary value analysis to the CHAR(20) length limit, and confirm the composite (not single-column) nature of the key with a case that repeats one key column but changes the other.

1. Test case 1 — valid insert (baseline equivalence class). All four columns populated with well-formed data and a key not yet in the table.
   insert into vehicle values ('Civic', 'Sedan', 'Honda', 24000);
   Expected result: accepted — this is the reference row every later case is compared against.
2. Test case 2 — NOT NULL violation. Omit vehicle_price (equivalence class: any column left NULL).
   insert into vehicle (vehicle_name, vehicle_type, vehicle_manufacturer) values ('F-150', 'Truck', 'Ford');
   Expected result: rejected — violates vehicle_price int NOT NULL.
3. Test case 3 — primary key violation (composite key, exact duplicate). Same (vehicle_name, vehicle_type) pair as test case 1, different non-key data.
   insert into vehicle values ('Civic', 'Sedan', 'Toyota', 26000);
   Expected result: rejected — duplicate primary key ('Civic','Sedan'), even though manufacturer and price differ.
4. Test case 4 — boundary value, name at the CHAR(20) upper edge. vehicle_name exactly 20 characters (the declared limit).
   insert into vehicle values ('AAAAAAAAAAAAAAAAAAAA', 'Coupe', 'GenericCo', 15000);
   Expected result: accepted — 20 characters is exactly the declared boundary, not beyond it.
5. Test case 5 — boundary value exceeded. vehicle_name 21 characters, one past the boundary.
   insert into vehicle values ('AAAAAAAAAAAAAAAAAAAAA', 'Coupe', 'GenericCo', 15000);
   Expected result: rejected (or silently truncated, depending on the DBMS's SQL-mode strictness — either way it must not be accepted verbatim) — exceeds char(20).
6. Test case 6 — confirms the key is composite, not single-column. The same vehicle_name as test case 1 but a different vehicle_type.
   insert into vehicle values ('Civic', 'Hatchback', 'Honda', 22000);
   Expected result: accepted — a black-box tester who assumed vehicle_name alone was the key would wrongly expect this to be rejected; this case is the one that would expose that misunderstanding.
7. Test case 7 — type-domain violation on price. A non-integer value supplied for vehicle_price.
   insert into vehicle values ('Model3', 'Sedan', 'Tesla', 39999.99);
   Expected result: rejected (or truncated to an integer) — violates the declared int type.