4.10.4 - SQL table definitions and data changes (C#)

CREATE TABLE and constraints

A table definition establishes what each field stores and which rules new or changed records must satisfy.

CREATE TABLE names a relation and lists its fields with data types and constraints. A constraint is a rule checked by the database management system. INTEGER suits whole-number IDs and session counts; VARCHAR(n) describes variable-length text up to n characters in standard table designs. Use a suitable exact numeric type such as DECIMAL(6,2) for fixed-precision money rather than assuming every type called REAL stores decimal amounts exactly. A field definition is a design, not a sample value to insert.

Our original club database needs Member, Club and Booking. MemberID and ClubID identify parent records; Booking stores both relationships.

CREATE TABLE Member (
    MemberID INTEGER NOT NULL,
    Name VARCHAR(40) NOT NULL,
    YearGroup INTEGER NOT NULL,
    PRIMARY KEY (MemberID)
);

CREATE TABLE Club (
    ClubID INTEGER NOT NULL,
    ClubName VARCHAR(30) NOT NULL,
    Room VARCHAR(10) NOT NULL,
    PRIMARY KEY (ClubID)
);

CREATE TABLE Booking (
    BookingID INTEGER NOT NULL,
    MemberID INTEGER NOT NULL,
    ClubID INTEGER NOT NULL,
    Sessions INTEGER NOT NULL,
    FeePaid DECIMAL(6,2) NOT NULL,
    PRIMARY KEY (BookingID),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
    FOREIGN KEY (ClubID) REFERENCES Club(ClubID),
    CHECK (Sessions > 0),
    CHECK (FeePaid >= 0)
);

Commas separate definitions inside the parentheses; there is no trailing comma after the last one. The primary key uniquely identifies every booking and cannot be null in this design. NOT NULL requires a supplied value, but does not by itself forbid an empty text string. A foreign key requires a non-null referencing value to match an existing key in its parent table. CHECK restricts permitted values. The Member and Club tables must exist before defining Booking's references in this design.

One member can make several bookings for the same club, so neither MemberID nor the MemberID/ClubID pair is declared unique. Use UNIQUE only when the actual requirement forbids repeated values, not because a field looks important. A compound primary key is possible when a genuine combination identifies records, but this table deliberately uses BookingID. CREATE TABLE creates the structure; INSERT supplies its records. Database systems differ in supported types and enforcement: use the dialect and constraints given in a task, and do not infer that every engine enforces VARCHAR length or decimal precision identically.

INSERT, UPDATE and DELETE

Data-changing statements add records, alter selected fields or remove selected records from an existing table.

INSERT INTO supplies new values. An explicit field list makes their meaning clear; each value must correspond to its field's position. Suppose MemberID 3 and ClubID 20 already exist, and BookingID 106 is unused:

INSERT INTO Booking (BookingID, MemberID, ClubID, Sessions, FeePaid)
VALUES (106, 3, 20, 2, 10.00);

The new booking links Chen to Drama. INSERT does not modify Member or Club. To add a new member and that member's first booking, insert the Member parent record first, then the Booking record referring to its ID. Listing values without a field list can be valid when they match every field in declared order, but the explicit form is easier to check.

UPDATE changes fields in records selected by WHERE:

UPDATE Booking
SET Sessions = 3, FeePaid = 15.00
WHERE BookingID = 106;

Both assignments belong in SET and are separated by a comma. This changes one booking, preserving its keys. SET FeePaid = FeePaid + 5 would add 5 to the existing amount in each matched row; SET FeePaid = 5 would replace it with 5. Without WHERE, UPDATE affects every record in its target table. A valid statement can therefore cause the wrong change.

DELETE FROM removes entire selected records:

DELETE FROM Booking
WHERE BookingID = 106;

It does not mean “clear one field”. Without WHERE, DELETE removes all records from the target table; it leaves the table definition in place. DROP TABLE, which is a separate structural command, removes the table itself. A primary-key condition is useful when the requirement is one exact record. A condition on a shared name may match several people.

Static C# companion

using System;

class Program
{
    static void Main()
    {
        string change = "UPDATE Club SET Room = 'B5' WHERE ClubID = 10;";
        Console.WriteLine(change);
    }
}

The outer double quotes delimit the C# string; the inner single quotes delimit SQL text 'B5'. This program prints a command and changes no records. A real application would pass the command to a database through its database interface. The Paper 2 answer is the UPDATE statement itself.