CREATE TABLE BOOK (
ARTICLE_NO INTEGER PRIMARY
AUTHOR_FIRST_NAMES CHARACTER(30),
AUTHOR_LAST_NAMES CHARACTER(30),
TITLE CHARACTER(30),
ISBN CHARACTER(13) UNIQUE,
WHOLESALE_PRICE NUMERIC(4,2),
RETAIL_PRICE NUMERIC(4,2),
COPIES_AVAILABLE INTEGER
);
CREATE TABLE CUSTOMER (
CUSTOMER_NO INTEGER PRIMARY
FIRST_NAMES CHARACTER(30),
LAST_NAMES CHARACTER(30),
STREET CHARACTER(30),
HOUSE_NO SMALLINT,
POSTCODE CHARACTER(7),
TOWN CHARACTER(30),
ISO_COUNTRY_CODE CHARACTER(2)
);

CREATE TABLE BOOKORDER (
ORDER_NO INTEGER PRIMARY KEY,
CUSTOMER_NO INTEGER NOT NULL,
ORDERED DATE,
DELIVERY DATE,
STATUS CHARACTER(8)
CHECK (STATUS IN ('ACCEPTED',
'DELAYED',
'SHIPPED',
'RETURNED',
'PAID')),
CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO)
REFERENCES KUNDE (KUNDENNAME)
);
CREATE TABLE ORDER_POSITION (
POSITION_NO INTEGER PRIMARY KEY,
ORDER_NO INTEGER NOT NULL,
ARTICLE_NO INTEGER NOT NULL,
NUMBER SMALLINT,
CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO)
REFERENCES BOOKORDER (ORDER_NO),
CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
REFERENCES BOOK (ARTICLE_NO)
);