项目5 项目拓展

P85创建表的练习

--- 1.图书表BOOK ---

CREATE TABLE BOOK
(
    ISBN Char(13) PRIMARY KEY,
    BOOKNO VARCHAR2(20) UNIQUE,
    BOOKNAME VARCHAR2(30) NOT NULL,
    BOOKWRITER VARCHAR2(20),
    BOOKPUBLISH VARCHAR2(20) DEFAULT('清华大学出版社'),
    PRICE Number(6,2) CONSTRAINT CK_BOOK CHECK(PRICE < 200),
    PUBLISHDATE Date,
    BOOKTYPEID Number(6) REFERENCES BOOKTYPE(BOOKTYPEID)
);

--- 2.图书类别信息表BOOKTYPE ---

CREATE TABLE BOOKTYPE
(
    BOOKTYPEID Number(6) PRIMARY KEY,
    BOOKTYPENAME VARCHAR2(30),
    DES VARCHAR2(200)
);

--- 3.读者表READER ---

CREATE TABLE READER
(
    ID Number(10) PRIMARY KEY,
    READERNAME VARCHAR2(30) NOT NULL,
    SEX CHAR(2),
    BIRTHDAY DATE,
    PHONE CHAR(11),
    DEPT VARCHAR2(30),
    MAJOR VARCHAR2(30),
    READERTYPEID NUMBER(6) REFERENCES READERTYPE(READERTYPEID)
);

--- 4.读者类别信息表READERTYPE ---

CREATE TABLE READERTYPE
(
    READERTYPEID NUMBER(6) PRIMARY KEY,
    READERTYPENAME VARCHAR2(30),
    MAXNUM NUMBER(2),
    MAXLIMIT VARCHAR2(20)
);

--- 5.借阅信息表BOOKBORROW ---

CREATE TABLE BOOKBORROW
(
    ISBN CHAR(13) REFERENCES BOOK(ISBN),
    READERID NUMBER(10) REFERENCES READER(ID),
    BORROWDATE DATE,
    RETURNDATE DATE,
    USERID NUMBER(6) REFERENCES USERS(ID),
    PRIMARY KEY(ISBN,READERID)
);

--- 6.管理员信息表USERS ---

CREATE TABLE USERS
(
    ID NUMBER(6) PRIMARY KEY,
    NAME CHAR(10) NOT NULL,
    PWD VARCHAR2(20) DEFAULT('111111')
);