/* This script will drop, recreate and populate tables for the Library
database */
/* */
/* Save this file as makelib.sql */
/* Save this file as makelib.sql */
/* Save this file as makelib.sql */
/* */
commit;
set autocommit on;


drop author;

create table author    (ISBNX varchar(8)NOT NULL,
                        author_name varchar(20)NOT NULL);

modify author to btree unique on ISBNX, author_name;

copy table author( ISBNX = char(0) ,
                   author_name = char(0) nl)
                                             from '/usr1/ingres/public/ing_jb/aut.txt';


select * from author;


help author;



drop book;

create table book       (ISBNX varchar(8) NOT NULL,
                         pub_code varchar(4),
                         title varchar(40),
                         pub_date varchar(4),
                         now_price money);


modify book to btree unique on ISBNX;


copy table book(ISBNX = char(0), pub_code = char(0), title = char(0),
pub_date = char(0), now_price = char(0) nl) from '/usr1/ingres/public/ing_jb/book.txt';


select * from book;



drop borrower;

create table borrower   (bor_no                 varchar(8) NOT NULL,
                         bor_name 	        varchar(20),
                         bor_state	        varchar(1),
                         addr1		        varchar(20),
                         addr2	                varchar(20),
                         town                   varchar(20),
                         post_code              varchar(8));

modify borrower to btree unique on bor_no;

copy table borrower     (bor_no		  = char(0),
                         bor_name	  = char(0),
                         bor_state	  = char(0),
                         addr1	          = char(0)tab,
                         addr2	          = char(0),
                         town 		  = char(0),
                         post_code 	  = char(0) nl) from '/usr1/ingres/public/ing_jb/bor.txt';

select * from borrower;



drop copies;

create table copies     (access_no      varchar(10) NOT NULL,
                         ISBNX          varchar(8),
                         buy_price      money,
                         buy_date       date);


modify copies to btree unique on access_no;




copy table copies (access_no	= char(0),
                     ISBNX	= char(0),
                     buy_price	= char(0),
                     buy_date	= char(0) nl) from '/usr1/ingres/public/ing_jb/cop.txt';


select * from copies;

help copies;



drop limit;

create table limit      (bor_state      varchar(1) NOT NULL,
                         loan_limit     integer1);



modify  limit to btree unique on bor_state;




copy table limit  (bor_state = char(0)tab, loan_limit = char(0) nl)
                   from '/usr1/ingres/public/ing_jb/lim.txt';


select * from limit;



help limit;


drop loan;


create table loan       (access_no      varchar(10)NOT NULL,
                         bor_no         varchar(8),
                         loan_date      date);

modify loan to btree unique on access_no;



copy table loan (access_no	= char(0) tab,
                 bor_no		= char(0)tab,
                 loan_date	= char(0) nl)
                                          from '/usr1/ingres/public/ing_jb/loa.txt';


select * from loan;


help loan;



drop publisher;


create table publisher  (pub_code varchar(4) NOT NULL,
                         pub_name varchar(20));


modify publisher to btree unique on pub_code;



copy table publisher    (pub_code = char(0),
                         pub_name = char(0) nl)
                                        from '/usr1/ingres/public/ing_jb/pub.txt';


select * from publisher;


help publisher;




drop reservation;


create table reservation (bor_no        varchar(8) ,
                          ISBNX         varchar(8) ,
                          reserve_date  date );


modify reservation to btree unique on bor_no, ISBNX;



copy table reservation (bor_no		= char(0) tab,
                        ISBNX		= char(0) tab,
                        reserve_date	= char(0) nl)
                                                    from '/usr1/ingres/public/ing_jb/res.txt';

 select * from reservation;