Question 5 of 8: Black-Box Test of the authorISBN Table

Question 5: Black-Box Test of the authorISBN Table (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.

Appendix A (as printed in the exam) is reproduced below for reference:

drop database Books1;
create database Books1;
use Books1;
create table publishers (
    publisherID int NOT NULL,
    publisherName varchar (30) NOT NULL,
    constraint pk_publishers primary key (publisherID)
);
create table authors (
    authorID int NOT NULL,
    firstName varchar (20) NOT NULL,
    lastName varchar (30) NOT NULL,
    constraint pk_authors primary key (authorID)
);
create table titles (
    isbn varchar (20) NOT NULL,
    title varchar (100) NOT NULL,
    editionNumber int NOT NULL,
    copyright varchar (4) NOT NULL,
    publisherID int NOT NULL,
    imageFile varchar (20) NOT NULL,
    price real NOT NULL,
    constraint fk_titles foreign key (publisherID)
        references publishers (publisherID),
    constraint pk_titles primary key (isbn)
);
create table authorISBN (
    authorID int NOT NULL,
    isbn varchar (20) NOT NULL,
    constraint fk_authorISBN_1 foreign key (authorID)
        references authors (authorID),
    constraint fk_authorISBN_2 foreign key (isbn)
        references titles (isbn)
);

Given. authorISBN has two NOT NULL columns, authorID (FK → authors.authorID) and isbn (FK → titles.isbn); the table declares NO primary key or uniqueness constraint of its own.

Find. A black-box (specification-only) test-case set of INSERT statements exercising every declared constraint on authorISBN — both foreign keys, both NOT NULLs — without reference to how the DBMS enforces them internally.

Approach. Treat each declared constraint (FK on authorID, FK on isbn, NOT NULL on each column) as its own equivalence condition — one valid class where every condition holds, and one invalid class per condition violated in isolation, holding the other columns valid so a rejection is attributable to a single cause — then add a class the schema does NOT explicitly guard against at all.

1. Valid class. Both FK targets already exist → the insert should succeed.
2. FK-violation classes. authorID not present in authors; isbn not present in titles — each should be rejected by its own named constraint (fk_authorISBN_1 / fk_authorISBN_2).
3. NOT NULL classes. authorID = NULL; isbn = NULL — each should be rejected.
4. Uncovered class. The schema declares no primary key or unique constraint on (authorID, isbn) itself, so inserting the SAME valid pair a second time is not, by the schema as written, an invalid class at all — a black-box test that only checks the four DECLARED constraints would never surface this; it is found only by explicitly testing what the schema does NOT restrict.