CREATE TABLE CUSTOMER(
CUSTOMER_CODE VARCHAR(5) NOT NULL, /* Unique code of customer */
COMPANY_NAME VARCHAR(40) NOT NULL, /* Company name */
CITY VARCHAR(15) NULL, /* City component of address */
COUNTRY VARCHAR(15) NULL, /* Country component of address */
CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_CODE));
CREATE TABLE PRODUCT(
PRODUCT_NAME VARCHAR(40) NOT NULL, /* Product name */
SUPPLIER_NAME VARCHAR(40) NOT NULL, /* Supplier name */
CATEGORY_NAME VARCHAR(30) NOT NULL, /* Product category */
UNIT_PRICE DECIMAL(6,2) NOT NULL, /* Unit price */
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME));
CREATE TABLE ORDERS (
ORDER_ID DECIMAL(9) NOT NULL, /* Unique order identifier */
CUSTOMER_CODE VARCHAR(5) NOT NULL, /* Code of customer */
ORDER_DATE DATE NOT NULL, /* Order date */
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE)
REFERENCES CUSTOMER (CUSTOMER_CODE));
CREATE TABLE ORDER_DETAIL(
ORDER_ID DECIMAL(9) NOT NULL, /* Order identifier */
PRODUCT_NAME VARCHAR(40) NOT NULL, /* Name of ordered product */
QUANTITY DECIMAL(9) NOT NULL, /* Ordered quantity */
DISCOUNT DECIMAL(4,2) NULL, /* Discount applied */
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_ID, PRODUCT_NAME),
CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID)
REFERENCES ORDERS (ORDER_ID),
CONSTRAINT FK_PRODUCT_NAME FOREIGN KEY (PRODUCT_NAME)
REFERENCES PRODUCT (PRODUCT_NAME),
CONSTRAINT CK_ORDER_DETAIL_QUANTITY CHECK (QUANTITY > 0),
CONSTRAINT CK_ORDER_DETAIL_DISCOUNT CHECK (DISCOUNT between 0 and 1));
