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

The Database Design

The difficulties I encountered when I fed the ANSI-compliant CREATE TABLE statements into PostgreSQL's psql monitor program were only minor. I received some warnings that foreign key constraints were accepted but not yet implemented, and I had to cut the COMMENT field in RATING back to 255 characters since this is the maximum field width for a CHARACTER VARYING field in PostgreSQL. The system provides BLOB data types for storing larger amounts of data, but those aren't in the standard version so I decided not to use them. Another issue is rather ugly--since I couldn't find information about how PostgreSQL casts NUMERICs from and into C data types and since I didn't want to use floats anyway to avoid round-off errors, I decided to make the monetary field integer numbers of cents.

I ended up with this slightly different 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)  UNIQUE, 
	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)
	    CHECK (STATUS IN ('ACCEPTED',
		                  'DELAYED',
						  'SHIPPED',
						  'RETURNED',
						  'PAID')),
	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                 SMALLINT,
	COMMENT               CHARACTER VARYING(255),
	CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
	    REFERENCES BOOK (ARTICLE_NO)
);