Databases and SQL: Question 10

Worked solution

Part (a): Why REGISTRATION needs a composite primary key

Looking at the REGISTRATION data: StudentID 1 appears in two rows (once for WorkshopID 401, once for WorkshopID 402), so StudentID alone cannot uniquely identify a row. Likewise, WorkshopID 401 appears in three rows (once for each of StudentID 1, 2 and 3), so WorkshopID alone cannot uniquely identify a row either.

Only the combination of StudentID and WorkshopID together is unique for every row - each student registers for a given workshop at most once. This is a direct consequence of the many-to-many relationship between STUDENT and WORKSHOP: one student links to many workshops, and one workshop links to many students, so only a pairing of the two identifies one specific registration. The primary key of REGISTRATION must therefore be the composite key (StudentID, WorkshopID).

[2 marks]: [1] for showing that neither StudentID nor WorkshopID alone is unique across the rows, [1] for correctly linking this to the need for the composite key (StudentID, WorkshopID).

Part (b): CREATE TABLE with a composite primary key and two foreign keys

CREATE TABLE REGISTRATION (
    StudentID INTEGER,
    WorkshopID INTEGER,
    Attended BOOLEAN,
    PRIMARY KEY (StudentID, WorkshopID),
    FOREIGN KEY (StudentID) REFERENCES STUDENT (StudentID),
    FOREIGN KEY (WorkshopID) REFERENCES WORKSHOP (WorkshopID)
);

[3 marks]: [1] for correct field names and data types, [1] for the correct composite PRIMARY KEY (StudentID, WorkshopID), [1] for both FOREIGN KEY clauses correctly referencing STUDENT (StudentID) and WORKSHOP (WorkshopID).

Part (c): INSERT INTO

INSERT INTO REGISTRATION (StudentID, WorkshopID, Attended)
VALUES (2, 403, FALSE);

This adds StudentID 2 (Freya Adams) with WorkshopID 403 (Game Design) and Attended set to FALSE, since she has not attended it yet. This is a valid new row because (2, 403) does not already appear in REGISTRATION (the existing data only has WorkshopID values 401 and 402 for StudentID 2), and WorkshopID 403 already exists in WORKSHOP, so referential integrity is satisfied.

[1 mark] for the correct INSERT INTO ... VALUES statement, matching the field list to the values given in the question.

Part (d): DELETE FROM with a compound condition

DELETE FROM REGISTRATION
WHERE WorkshopID = 401 AND Attended = FALSE;

Three rows have WorkshopID = 401: StudentID 1 (Attended = TRUE), StudentID 2 (Attended = FALSE) and StudentID 3 (Attended = TRUE). Of these, only StudentID 2’s row also satisfies Attended = FALSE, so only that one row is deleted. StudentID 1 and StudentID 3’s WorkshopID 401 rows are correctly left in the table, because although they match WorkshopID = 401, their Attended value is TRUE, not FALSE.

[2 marks]: [1] for the correct DELETE FROM ... WHERE statement, [1] for both conditions (WorkshopID = 401 and Attended = FALSE) correctly joined with AND, deleting exactly one row.

Final answers

• (a) Composite key (StudentID, WorkshopID) needed since neither field alone is unique - each repeats once per pairing on the other side of the many-to-many relationship.
• (b) CREATE TABLE REGISTRATION (...) with PRIMARY KEY (StudentID, WorkshopID) and two FOREIGN KEY clauses.
• (c) INSERT INTO REGISTRATION (...) VALUES (2, 403, FALSE);
• (d) Only StudentID 2’s WorkshopID 401 row (Attended FALSE) is deleted; StudentID 1 and 3’s WorkshopID 401 rows (Attended TRUE) are retained.