CREATE TABLE USERS (
    USERID           NUMBER(10) NOT NULL,
    ADDRESS          VARCHAR2(40) NOT NULL,
    ADDRESS_ZIP      VARCHAR2(10) NOT NULL,
    BIRTHDATE        DATE NOT NULL,
    FIRSTNAME        VARCHAR2(40) NOT NULL,
    NAME             VARCHAR2(40) NOT NULL,
    PHONE_HOME       VARCHAR2(30),
    PHONE_MOBIL      VARCHAR2(40),
    PHONE_WORK       VARCHAR2(40),
    PHOTO            BLOB,
    CONSTRAINT PK_USERS PRIMARY KEY(USERID));

CREATE TABLE BOOKS (
    BOOKID           NUMBER(10) NOT NULL,
    AUTHORS          VARCHAR2(400) NOT NULL,
    TITLE            VARCHAR2(4000) NOT NULL,
    SUBTITLE         VARCHAR2(4000),
    SERIES_TITLE     VARCHAR2(300),
    PUBLISHERID      NUMBER(10),
    VOLUME_NO        NUMBER(2),
    CATEGORYID       NUMBER(10),
    PUBLICATION_DATE DATE,
    IMAGE_URL        VARCHAR2(2000),
    BIC_CODE         VARCHAR2(60),
    CONSTRAINT PK_BOOK PRIMARY KEY(BOOKID));

CREATE TABLE BOOKS_OUT_ON_LOAN (
    BOOKID           NUMBER(10) NOT NULL,
    USERID           NUMBER(10) NOT NULL,
    LOAN_DATE        DATE NOT NULL,
    LOAN_DAYS        NUMBER(2) NOT NULL,
    STATUS           CHAR(1) NOT NULL,
    CONSTRAINT LOAN_PK PRIMARY KEY(BOOKID, USERID, LOAN_DATE),
    CONSTRAINT FK_LOAN_BOOKS FOREIGN KEY(BOOKID) 
      REFERENCES BOOKS (BOOKID) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_LOAN_USERS FOREIGN KEY(USERID) 
      REFERENCES USERS (USERID) ON DELETE CASCADE ON UPDATE CASCADE);