Lab Cycle 1 — DDL Commands, Schema Management & Constraints

-- 1 & 3. Create Supplier table with Primary Key, Default, and Check constraints
CREATE TABLE Supplier (
    SID INT PRIMARY KEY,
    Sname VARCHAR(50) NOT NULL,
    branch VARCHAR(50) DEFAULT 'Main',
    city VARCHAR(50) NOT NULL,
    phone VARCHAR(15) CHECK (LENGTH(phone) >= 10)
);

-- 2. Create Part table with Primary Key, Unique, and Check constraints
CREATE TABLE Part (
    PID INT PRIMARY KEY,
    Pname VARCHAR(50) NOT NULL UNIQUE,
    color VARCHAR(20) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0)
);

-- 4. Create Supplies table with Composite Primary Key and Foreign Key constraints
CREATE TABLE Supplies (
    SID INT,
    PID INT,
    qty INT NOT NULL CHECK (qty > 0),
    date_supplied DATE NOT NULL,
    CONSTRAINT pk_supplies PRIMARY KEY (SID, PID, date_supplied),
    CONSTRAINT fk_supplier FOREIGN KEY (SID) REFERENCES Supplier(SID) ON DELETE CASCADE,
    CONSTRAINT fk_part FOREIGN KEY (PID) REFERENCES Part(PID) ON DELETE CASCADE
);