Few IT managers have the luxury of looking ahead more than a year or two. Nicholas Carr, the author of "Does IT Matter?", takes readers on a trip one decade into the future to see how IT will change and what those changes mean for IT professionals, hardware and software vendors, and corporations of all sizes. Register now for your free Internet.com membership to download your complimentary eBook. Membership will also give you access to: eBook library Whitepapers Webcasts Newsletters WinDrivers

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; CUSTOMER contains the registered customers. For every order, a row in BOOKORDER is 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 bullet-proof.

Next: Adapting the Design to PostgreSQL »