Linux数据库大比拚(中)

[Page: ]

使设计适应MySQL

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)
);