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

The Database Design

As mSQL is a scaled-down database manager (indeed, some may question whether MySQL and mSQL are database-management systems at all), it has jettisoned most of SQL's functionality and accepts only a very restricted subset of SQL. Thus, mSQL's script looks a lot different:

DROP TABLE BOOK
CREATE TABLE BOOK (
    ARTICLE_NO            INTEGER    NOT NULL,
	AUTHOR_FIRST_NAMES    CHARACTER(30),
	AUTHOR_LAST_NAMES     CHARACTER(30),
	TITLE                 CHARACTER(30),
	ISBN                  CHARACTER(13), 
	WHOLESALE_PRICE       MONEY,
	RETAIL_PRICE          MONEY,
	COPIES_AVAILABLE      INTEGER
)

DROP TABLE CUSTOMER
CREATE TABLE CUSTOMER (
    CUSTOMER_NO           INTEGER    NOT NULL,
	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    NOT NULL,
	CUSTOMER_NO           INTEGER    NOT NULL,
	ORDERED               DATE,
	DELIVERY              DATE,
	STATUS                CHARACTER(1)
)

DROP TABLE ORDER_POSITION
CREATE TABLE ORDER_POSITION (
    POSITION_NO           INTEGER    NOT NULL,
    ORDER_NO              INTEGER    NOT NULL,
	ARTICLE_NO            INTEGER    NOT NULL,
	NUMBER                SMALLINT
)

DROP TABLE RATING
CREATE TABLE RATING (
    RATING_NO             INTEGER    NOT NULL,
    ARTICLE_NO            INTEGER    NOT NULL,
	SCORE                 SMALLINT,
	COMMENT               TEXT(255)
)

Nearly all of the constraints have disappeared, and NUMERIC and CHARACTER VARYING have been replaced by MONEY and TEXT, respectively.

There is an annoying deficiency in mSQL's monitor program: it doesn't seem to accept SQL scripts fed into it from standard input. Thus, one needs to cut and paste the code. mSQL will complain about semicolons, too; I ended up feeding the commands into it one by one and terminating each with \g (the "go" 'slash command').