CREATE TABLE CUSTOMER (
    Id CHAR(9), -- Id must be 9 digits long
    Name CHAR(50),
    Address CHAR(50),
    --CHECK (0 <= Id AND Id >= 999999999),
    PRIMARY KEY(Id), -- Id acts as an identifier
    UNIQUE(Name, Address) -- Name, Address tuple is a candidate key
)

CREATE TABLE SUPPLIER (
    Id CHAR(9),
    Name CHAR(50),
    Address CHAR(50),
    --CHECK (0 <= Id AND Id >= 999999999),
    PRIMARY KEY(Id), -- Id acts as an identifier
    UNIQUE(Name, Address) -- Name, Address tuple is a candidate key
)

CREATE TABLE PRODUCT (
    PartNumber INTEGER,
    Name CHAR(50),
    CHECK (0 <= PartNumber AND PartNumber >= 999999),
    PRIMARY KEY(PartNumber)
)

CREATE TABLE CONTRACTS (
    SupplierId CHAR(9),
    CustomerId CHAR(9),
    ProductId INTEGER,
    ProductQuantity INTEGER,
    Price INTEGER,
    FOREIGN KEY (SupplierID) REFERENCES SUPPLIER(Id),
    FOREIGN KEY (CustomerId) REFERENCES CUSTOMER(Id),
    FOREIGN KEY (ProductId) REFERENCES PRODUCT(PartNumber)
)

CREATE ASSERTION ThereMustBeMoreContractsThanSuppliers
	CHECK (SELECT COUNT (*) FROM CONTRACTS >= SELECT COUNT (*) FROM SUPPLIERS)