Linux數據庫大比拚(二)

數據庫 設計 編者按：這是涉及可自由地獲得的 Linux DBMS的系列文章的第二部分。 為了測試並比較3個數據庫管理系統，我當然需要一個數據庫來管理他們。在讀完了《SQL傻瓜書》後，我有了一些如何設計現實的數據庫的基本知識，因此我揀選了一特別無聊的真實世   數據庫設計
  編者按：這是涉及可自由地獲得的Linux DBMS的系列文章的第二部分。
  
  為了測試並比較3個數據庫管理系統，我當然需要一個數據庫來管理他們。在讀完了《SQL傻瓜書》後，我有了一些如何設計現實的數據庫的基本知識，因此我揀選了一特別無聊的真實世界情形(一個因特網書店，其他？)並且寫下了一個小型數據庫設計以便探討一點Sql的能力。
  
  在這個數據庫裡有5個表，BOOK保存可得到的書的登記信息；CUSTOMER包含登記的顧客。對每份訂單，BOOKORDER創建一行，參照其顧客。對每個定購的項目，在ORDER_POSITION裡引用它的訂單號。作為一種獎勵，針對書的排名我增加了一RATING表。
  
  下列的SQL代碼可裝入到一個數據庫系統的SQL監控程序並且被接受應該沒有任何問題。所有的表在創建前被刪除因此確保他們不會在創建之前已經存在。
  
  　
  
  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)
  );
  當然，這是一個極其簡單的數據庫。它看上去真實，但是它不適用於真實世界的應。它不保存顧客記錄或任何東西，並且它甚至沒有書的出版商的列。它只是一個測試環境。
  
  注意我不想花大氣力強制CUSTOMER.ISO_COUNTRY_CODE為今天是實際有效的編碼。我在代碼做了一點限制以測試數據庫系統是否接受他們；我沒嘗試使數據庫無懈可擊。
  
  改變設計適應PostgreSQL
  當我將遵循ANSI標准的CREATE TABLE語句裝入Postgresql的psql監控視程序是，我遇到的困難是很少的。我得到一些警告：外部關鍵字限制被接受但還沒有實現，而且我不得不裁減RATING的COMMENT字段到255個字符，因為這是PostgreSQL的CHARACTER VARYING類型的字段的最大字段寬度。系統為存儲大量數據提供BLOB數據類型，但是它們不在標准版本內，因此我決定了不使用他們。另外的問題是相當愚蠢--因為我不能找到有關PostgreSQL如何強制NUMERIC到C數據類型，也因為我不想使用float以避免捨入，我決定使得貨幣字段為分值(cent)的整數數字。
  
  我最後得到了這個略有不同的腳本：
  
  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)
  );
  　
  
  使設計適應MySQL
  MySQL象PostgreSQL一樣忽略外部關鍵字的限制，但是它搞了個UNIQUE限制。最後的腳本與PostgreSQL腳本差不多：
  
  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),
  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),
  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(255),
  CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO)
  REFERENCES BOOK (ARTICLE_NO)
  );
  使設計適應 mSQL
  因為mSQL是一個精簡的數據庫管理器(的確，有些人可能懷疑MySQL和mSQL是否是數據庫管理系統)，它放棄了大多數Sql的功能而僅僅接受SQL的一個嚴格限制的子集。這樣，mSQL的腳本看上有很大不同：
  
  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)
  )
  幾乎所有的約束都不見了，並且NUMERIC和CHARACTER VARYING分別由MONEY和TEXT代替。
  
  在mSQL的監視程序中有令人沮喪的不足：它似乎不能接受從標准輸入輸入SQL腳本，這樣, 需要剪切/粘貼代碼。mSQL也討厭分號；最終我只能一個一個地輸入命令並用\g（“go”斜槓命令)終止每條命令 。
  
  實現測試客戶
  為了比較3個數據庫管理器，我決定為執行在bookstore數據庫上的交易的目的用C寫了一個測試客戶。結果，我實現了一些操作，它們能比較API。為了性能比較，我隨後充分實現了它們，並且把一個非交互式模式加入客戶程序，因此它能自己運行，產生隨意的數據且隨機執行交易。
  
  我決定了在樣品數據庫上實現下列行動：
  
  增加一本新書： INSERT INTO BOOK (...) VALUES (...);
  刪除一本存在的書： DELETE FROM BOOK WHERE ARTICLE_NO=...;
  增加一個顧客： INSERT INTO CUSTOMER (...) VALUES (...);
  刪除一個顧客： DELETE FROM CUSTOMER WHERE CUSTOMER_NO=...;
  訂書的一個顧客： INSERT INTO BOOKORDER (...) VALUES (...); INSERT INTO ORDER_POSITION (...) VALUES (...);;
  評估一本書的一個顧客： INSERT INTO RATING (...) VALUES (...);
  改變一份訂單的狀態： UPDATE BOOKORDER SET STATUS=... WHERE ORDER_NO=...;
  然後，能生成下列報表：
  
  書籍列表： SELECT * FROM BOOK;
  顧客列表： SELECT * FROM CUSTOMER;
  正在投遞的交貨表，按狀態排序： SELECT * FROM BOOKORDER ORDER BY STATUS;
  書籍的利潤額，最後有平均值： SELECT RETAIL_PRICE-WHOLESALE_PRICE FROM BOOK; SELECT AVG(RETAIL_PRICE-WHOLESALE_PRICE) FROM BOOK;
  書評、評級和為一本書的平均評級