Back to article
Apples and Oranges, Part II: A Linux DBMS Comparison
The Database Design
November 18, 1999
Editor's Note: This is Part II of a series covering freely available Linux DBMSes. You can find the first part of the series here.
To test and compare three database management systems, I naturally needed a database to manage with them. After having read SQL for Dummies, I had some basic knowledge of how realistic databases are designed, so I picked a particularly unimaginative real-world case (an Internet bookstore, what else?) and wrote up a little database design exploiting a couple of SQL's capabilities.
There are five tables in this database.
The following is SQL code that can be fed into a DBMS's SQL monitor and should be accepted without any problem. All the tables are dropped before creation so the script won't bail out if some of them already exist.
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 NUMERIC(4,2), RETAIL_PRICE NUMERIC(4,2), 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 NUMERIC(1,0), COMMENT CHARACTER VARYING(300), CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO) REFERENCES BOOK (ARTICLE_NO) );
Of course, this is an extremely simple-minded database. It tries to look realistic, but it's not suited for real-world application. It doesn't save customer records or anything, and it hasn't even got a column for a book's publisher. It's just a test case.
Note that I didn't want to undertake the effort of constraining