Protect Workshop Registration Rules

Introduction

REPAIR CIRCLE runs practical repair workshops. Its desk can already record a booking and display the real roster. However, the database does not yet reject duplicate registrations or broken references. The goal is to keep this working desk usable while making the database enforce the registration relationships.

You need basic terminal navigation and basic MySQL SELECT, INSERT and UPDATE knowledge. Constraints, transactions used for reversible trials, and ALTER TABLE are explained here. MySQL and synthetic workshop data are prepared. Use Terminal throughout; the supplied clients connect to this course's MySQL database.

You may add the required registration and alter the necessary structure of registrations, saving your SQL in rules.sql. Preserve original IDs, people, session titles, group sizes, notes and supplied clients. One person may attend different sessions; different people may share a name. This project does not limit capacity or handle concurrent seat allocation.

Use the Desk and Expose a Duplicate

In this step, you will record a normal booking, read its actual relationships, and demonstrate the missing rule without leaving a bad record behind. Begin in Terminal. cd changes directory; use this absolute path again if you enter the wrong directory:

cd /home/labex/project/sign-up-desk

./desk is the supplied registration client in this directory. ./ means the current directory. roster reads the real MySQL tables; it does not display a precomputed answer:

./desk roster

Expect two original registrations. Registration 501 connects person 101, Mara Lin, to session 11, Lamp Repair, with one seat and the note Window seat. Registration 502 connects person 102, Alex Kim, to session 12, Bike Patch, with two seats and Bring partner. Keep these original relationships and values.

A new request asks for person 104, Sam Reed, in session 11 with one seat. book takes the person ID, event ID and group size in that order. Save this legitimate booking:

./desk book 104 11 1

./desk roster

The third registration belongs to Sam, not Mara, even though both attend Lamp Repair. The client returns the database's saved ID. You do not need to choose or memorize that new ID.

./db opens this course's MySQL client connection. Use it instead of the image's unrelated mysql command. --table requests readable tabular output; -e executes the quoted SQL and closes the connection. Quotes keep spaces and semicolons together as one argument. Read the two parent tables:

./db --table -e "SELECT id,name FROM participants ORDER BY id; SELECT id,title FROM sessions ORDER BY id;"

People 102 and 103 are both named Alex Kim. They are different people because their IDs differ. A name must not become a unique registration identity. The three tables have these relationships:

The business permits only one registration per (participant_id,session_id) pair, with a positive seat count and existing parent IDs. An existing booking must prevent deletion or renaming of its referenced person/session ID. Unused sessions can still be removed when needed.

Before protecting the schema, test the currently missing duplicate rule. A transaction groups data changes on one connection. START TRANSACTION begins the trial; ROLLBACK undoes its INSERT rather than saving it. The following single command deliberately tries a second booking for Mara in Lamp Repair, counts that pair, then rolls it back. The multi-line quotes still form one shell argument:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,11,1);
SELECT COUNT(*) AS matching_registrations FROM registrations WHERE participant_id=101 AND session_id=11;
ROLLBACK;"

The count is 2: the database accepted a duplicate. This is a real weakness, not a connection failure. The trial uses INSERT, not an ALTER. Its rollback leaves Mara's original booking and Sam's new request intact:

./desk roster

There should still be three registrations. If an ordinary command has a wrong path, return with the absolute cd. Use ls to list this directory's files. Do not remove a person, clear the registration table or reset the database to recover from a typing error.

Enforce the Relationships and Keep Legitimate Bookings

In this step, you will save one schema change, apply it to the existing table, and prove that useful bookings still work while invalid requests are rejected. Begin in the same Terminal directory.

A constraint is a database rule applied to writes, including writes from a client other than ./desk. A composite UNIQUE constraint rejects a repeated pair without forbidding the same person in another session. A FOREIGN KEY connects a reference column to a parent's primary key. RESTRICT refuses deletion or an ID change while a booking references that parent; it does not delete the booking automatically.

CHECK (seats > 0) rejects zero and negative values. SQL NULL means a missing value; a CHECK whose result is unknown does not reject NULL by itself. NOT NULL is therefore also necessary for seats and both references. MODIFY restates the column type and adds that requirement. All three supplied tables already use InnoDB, the storage engine that enforces these relationships.

ALTER TABLE changes an existing table. MySQL implicitly commits schema changes: do not wrap this ALTER in a trial transaction and expect ROLLBACK to undo it. We use a single ALTER statement against already valid records, preserving their IDs and notes.

Save only ordinary, unqualified ALTER TABLE statements for registrations in rules.sql; USE, shell commands and changes to other objects are outside this file’s scope. Do not add triggers or change the parent-table definitions. In the Linux shell, > replaces the destination file and <<'SQL' begins a literal here-document. Paste the complete block, including its final SQL alone on a line. Do not enter this cat command at a MySQL prompt:

cat > rules.sql <<'SQL'
ALTER TABLE registrations
  MODIFY participant_id INT NOT NULL,
  MODIFY session_id INT NOT NULL,
  MODIFY seats INT NOT NULL,
  ADD CONSTRAINT one_person_event UNIQUE (participant_id, session_id),
  ADD CONSTRAINT known_person FOREIGN KEY (participant_id)
    REFERENCES participants(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT known_event FOREIGN KEY (session_id)
    REFERENCES sessions(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT positive_group CHECK (seats > 0);
SQL

If the shell keeps waiting, enter the missing final SQL line. If you mistyped the file, repeat the whole block to replace it. Constraint names identify these rules for MySQL's error messages; their exact names are not the business outcome.

< supplies the saved file as the client's SQL input. Apply it once:

./db < rules.sql

No output is expected on success. If the filename was wrong, use ls, correct the path and try again. If this single ALTER has a syntax error, MySQL rejects it; correct the saved complete block before retrying. Once the ALTER succeeds, do not apply it again: its constraints already exist. A duplicate-constraint error on a second run does not mean your data was lost.

Read the actual definition. SHOW CREATE TABLE reports the current table; \G displays its result vertically so the full definition is easier to read:

./db -e "SHOW CREATE TABLE registrations\G"

Look for the three NOT NULL reference/quantity columns, the two-column UNIQUE key, both FOREIGN KEY relationships and the positive-seat CHECK. Names or formatting may differ; the relationships must match.

Now repeat the duplicate request using the normal client. This command is expected to fail with a database refusal containing ERROR 1062. It must not create another row:

./desk book 101 11 1

The failure is a duplicate-key refusal, not a disconnected server. The roster remains usable:

./desk roster

Prove both permitted identity cases in a disposable data transaction on the real desk. Mara may attend Bike Patch, and the other Alex Kim (103) may attend the same session as Alex 102. A positive group size greater than one is also legitimate:

./db --table -e "
START TRANSACTION;
INSERT INTO registrations(participant_id,session_id,seats) VALUES(101,12,3),(103,12,5);
SELECT participant_id,session_id,seats FROM registrations ORDER BY participant_id,session_id;
ROLLBACK;"

The trial shows five rows with separate identities. Rollback removes only its two new trial rows. It does not undo the earlier schema change or Sam's already committed request.

Check three representative rejected writes directly. Each command below is expected to fail; these commands do not contain a transaction to repair or a successful change to undo. MySQL rejects the individual invalid statement. 9999 is absent from the supplied parent table:

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(9999,11,1);"

Expect ERROR 1452, an unknown parent reference.

./db -e "INSERT INTO registrations(participant_id,session_id,seats) VALUES(103,11,NULL);"

Expect ERROR 1048, a missing seat count. Replacing NULL with 0 would violate the CHECK rather than becoming a valid booking.

./db -e "DELETE FROM sessions WHERE id=11;"

Expect ERROR 1451: Lamp Repair still has bookings, so the session remains. This demonstrates why deleting the parent or cascading away registrations would be a wrong shortcut.

Finally, prove that a session with no bookings can still be removed in a reversible trial:

./db --table -e "
START TRANSACTION;
DELETE FROM sessions WHERE id=13;
SELECT id,title FROM sessions ORDER BY id;
ROLLBACK;"

Only sessions 11 and 12 appear inside that transaction. Rollback restores the unused session. Inspect the final handover:

./db --table -e "SELECT id,title FROM sessions ORDER BY id;"

./desk roster

All three original sessions remain, and exactly three real registrations remain: the two originals and Sam's new request. Keep rules.sql for the same structural change on another compatible desk. It is a one-time schema migration, not an idempotent import. Default connections enforce foreign keys; turning their checks off is outside this task. The constraints protect ordinary application writes, not an administrator who deliberately disables enforcement.

Summary

You kept the registration desk usable while moving its relationship rules into real MySQL constraints. You preserved original bookings and notes, accepted legitimate same-name and cross-session combinations, rejected duplicates and invalid references, and kept referenced records from being removed.