manufacturer_number INTEGER NOT NULL,

name TEXT NOT NULL,

--

CONSTRAINT manufacturer_PK PRIMARY KEY (manufacturer_number),

CONSTRAINT manufacturer_AK UNIQUE (name)

);

CREATE TABLE product (

sku TEXT NOT NULL,

name TEXT NOT NULL,

price NUMERIC NOT NULL,

quantity NUMERIC NOT NULL,

--

CONSTRAINT product_PK PRIMARY KEY (sku),

CONSTRAINT product_AK UNIQUE (name),

CONSTRAINT valid_price_CK CHECK (price > 0),

CONSTRAINT valid_quantity_CK CHECK (quantity > 0)

);

CREATE TABLE product_item_number (

sku TEXT NOT NULL,

manufacturer_number INTEGER NOT NULL,

item_number TEXT NOT NULL,

--

CONSTRAINT product_item_number_PK PRIMARY KEY (sku, manufacturer_number),

CONSTRAINT product_item_number_AK UNIQUE (manufacturer_number, item_number),

CONSTRAINT product_item_number_TO_product_FK FOREIGN KEY (sku)

REFERENCES product (sku),

CONSTRAINT product_item_number_TO_manufacturer_FK FOREIGN KEY (manufacturer_number)

REFERENCES manufacturer (manufacturer_number)

);