----- create library schema
create user lib identified by lib
default tablespace users
temporary tablespace temp;

-- give library schema  grants
grant connect,create table to lib;

--- connect to the schema
connect lib/lib


------- create library schema
create table BORROWER(
 BORROWNUM                                  NUMBER NOT NULL,
 NAME                                               VARCHAR2(50),
 BORROWERTYPE                                       VARCHAR2(50));

create table BOOK(
 BOOKNUM                                    NUMBER NOT NULL,
 TITLEOFBOOK                                        VARCHAR2(50),
 PARTNUM                                            NUMBER,
 PARTNAME                                           VARCHAR2(50),
 NUMCOPY                                            NUMBER,
 PUBNUM                                             NUMBER,
 CLASSNUM                                           NUMBER,
 NUMPAGES                                           NUMBER);

create table CLASSIFICATION(
 CLASSNUM                                   NUMBER NOT NULL,
 CLASSNAME                                          VARCHAR2(50));

create table COLLEGE(
 COLLEGNUM                                  NUMBER NOT NULL,
 COLLEGNAME                                         VARCHAR2(50));

create table DEPARTMENT(
 COLLEGNUM                                          NUMBER,
 DEPNAME                                    VARCHAR2(50) NOT NULL);

create table EMPLOYEE(
 BORROWNUM                                  NUMBER NOT NULL,
 OFFICENUM                                          NUMBER,
 OFFICEPHONE                                        VARCHAR2(50),
 JOB                                                VARCHAR2(50));

create table OPERATIONONBOOK(
 NUMOFOPERATION                             NUMBER NOT NULL,
 BORROWNUM                                          NUMBER,
 BOOKNUM                                            NUMBER,
 TYPEOFOPERATION                                    VARCHAR2(50),
 NUMOFPART                                          NUMBER,
 ODATE                                              DATE,
 RDATE                                              DATE,
 DURATION                                           NUMBER);

create table PUBLISHER(
 PUBNUM                                     NUMBER NOT NULL,
 PUBNAME                                            VARCHAR2(50),
 PUBADDRESS                                         VARCHAR2(50),
 COUNTRY                                            VARCHAR2(50),
 WEB                                                VARCHAR2(50));

create table STUDENT(
 BORROWNUM                                  NUMBER NOT NULL,
 COLLEGNUM                                          NUMBER,
 STUDPHONE                                          NUMBER,
 STUDADDRESS                                        VARCHAR2(50),
 DEPNAME                                            VARCHAR2(50));

create table WRITTEN_BY(
 BOOKNUM                                    NUMBER NOT NULL,
 AUTHORNUM                                  NUMBER NOT NULL);

create table AUTHORS(
 AUTHORNUM                                  NUMBER NOT NULL,
 ANAME                                              VARCHAR2(50),
 EMAIL                                              VARCHAR2(50),
 COUNTRY                                            VARCHAR2(50));



------------  connect oracle to access throw ODBC