4.10.4 - SQL table definitions and data changes (Java)

CREATE TABLE and constraints

A table definition specifies the fields, permitted values and key relationships that every stored row must obey.

Choose types for the meaning and range of the data: INTEGER for whole numbers, VARCHAR(40) for variable-length text up to 40 characters, CHAR(1) for a one-character value, DATE for a date and DECIMAL(6,2) for an exact fixed-point number with six total digits, two after the decimal point. Text and date literals use quotes in the SQL shown; numeric literals do not. Real DBMSs have dialect differences, so a task supplies any special convention it requires.

CREATE TABLE Member (
    MemberID INTEGER PRIMARY KEY,
    MemberName VARCHAR(40) NOT NULL,
    YearGroup INTEGER NOT NULL CHECK (YearGroup IN (12, 13))
);

CREATE TABLE Workshop (
    WorkshopID INTEGER PRIMARY KEY,
    WorkshopTitle VARCHAR(60) NOT NULL,
    Fee DECIMAL(6,2) NOT NULL CHECK (Fee >= 0)
);

CREATE TABLE Booking (
    BookingID INTEGER PRIMARY KEY,
    MemberID INTEGER NOT NULL,
    WorkshopID INTEGER NOT NULL,
    Status VARCHAR(12) NOT NULL,
    UNIQUE (MemberID, WorkshopID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
    FOREIGN KEY (WorkshopID) REFERENCES Workshop(WorkshopID)
);

The primary key makes each row identifiable and is unique and non-NULL. A foreign key requires a reference to match an existing parent key, unless a permitted NULL represents no reference. Here NOT NULL forbids that missing reference. UNIQUE(MemberID, WorkshopID) forbids a second booking for the same member–workshop pair; it does not prevent one member booking several different workshops. A composite primary key could identify the pair instead if the design did not need BookingID.

NOT NULL prevents an absent value. CHECK restricts a row's values, such as a non-negative fee. Neither proves that the real-world fact is true: an incorrect but permitted YearGroup can still be stored. A CHECK comparison alone does not necessarily reject NULL, so use NOT NULL when absence is forbidden. Referential integrity prevents orphan references; it does not mean every member must have a booking.

Create parent tables before defining the tables that reference them. Use commas between field/constraint definitions, and name the referenced field rather than hoping the DBMS guesses the relationship.

INSERT, UPDATE and DELETE

A change statement acts on its target table, and a WHERE condition determines which existing rows an update or deletion affects.

For the definitions above, this statement adds a member, not a column:

INSERT INTO Member (MemberID, MemberName, YearGroup)
VALUES (4, 'Dara', 13);

Listing fields makes the correspondence clear: each value matches the field at the same position. Provide every required field without a default. A new member does not automatically create a booking; inserting a booking requires valid member and workshop keys as well.

INSERT INTO Booking (BookingID, MemberID, WorkshopID, Status)
VALUES (107, 4, 20, 'Waiting');

UPDATE Workshop
SET Fee = Fee + 2
WHERE WorkshopID = 20;

DELETE FROM Booking
WHERE BookingID = 107;

The insert requires Member 4 and Workshop 20 to exist and that pair not to be booked already. The update increases only workshop 20's current fee by 2; it does not set every workshop's fee to 2. DELETE removes the entire matching row. Removing the values in one field would instead be an UPDATE, subject to its constraints. Without WHERE, UPDATE changes every target row and DELETE removes every target row. Selecting the intended keys first is a useful independent check.

A condition can use related data in another table. A subquery is a query nested in another statement; IN tests membership of its result:

UPDATE Booking
SET Status = 'Cancelled'
WHERE Status = 'Waiting'
  AND WorkshopID IN (
      SELECT WorkshopID
      FROM Workshop
      WHERE Fee = 0
  );

Only Waiting bookings for free workshops change. A Confirmed free booking and a Waiting paid booking remain unchanged. Likewise, DELETE FROM Booking WHERE WorkshopID IN (SELECT WorkshopID FROM Workshop WHERE Fee = 0); deletes bookings identified through Workshop while leaving the workshop definitions intact. Do not assume DELETE removes the joined parent records too. The SQL shown uses one target table per change statement; multi-table relationships guide its condition.