Apples and Oranges, Part II: A Linux DBMS Comparison - page 3

The Database Design

MySQL ignores foreign key constraints like PostgreSQL, but it will barf on UNIQUE constraints. The resulting script is not much different from the PostgreSQL script:

DROP TABLE BOOK;
CREATE TABLE BOOK (
    ARTICLE_NO            INTEGER        PRIMARY KEY,
	AUTHOR_FIRST_NAMES    CHARACTER(30),
	AUTHOR_LAST_NAMES     CHARACTER(30),
	TITLE                 CHARACTER(30),
	ISBN                  CHARACTER(13), 
	WHOLESALE_PRICE       INTEGER,
	RETAIL_PRICE          INTEGER,
	COPIES_AVAILABLE      INTEGER
);

DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER (
    CUSTOMER_NO           INTEGER        PRIMARY KEY,
	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)
);

DROP TABLE BOOKORDER;
CREATE TABLE BOOKORDER (
    ORDER_NO              INTEGER        PRIMARY KEY,
	CUSTOMER_NO           INTEGER        NOT NULL,
	ORDERED               DATE,
	DELIVERY              DATE,
	STATUS                CHARACTER(8),
	CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO)
	    REFERENCES KUNDE (KUNDENNAME)
);

DROP TABLE ORDER_POSITION;
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)
);

DROP TABLE RATING;
CREATE TABLE RATING (
    RATING_NO             INTEGER        PRIMARY KEY,
    ARTICLE_NO            INTEGER        NOT NULL,
	SCORE                 NUMERIC(1,0),
	COMMENT               CHARACTER VARYING(255),
	CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
	    REFERENCES BOOK (ARTICLE_NO)
);