Apples and Oranges, Part II: A Linux DBMS Comparison
The Database Design
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.
BOOK stores the
available books with bookkeeping information;
the registered customers. For every order, a row in
created, referencing its customer. For every item ordered, a row in
ORDER_POSITION references its order number. As a kind of
bonus, I've added a
RATING table for book ratings.
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
CUSTOMER.ISO_COUNTRY_CODE to the codes that are actually valid
today. I've got a couple of constraints in the code just to test whether the database
systems they will accept them; I did not try to make the database