set schema db2inst1;

create distinct type borrower_id_type as char(5) with comparisons;
create distinct type call_number_type as char(10) with comparisons;
create distinct type ssn_type as char(11) with comparisons;

create table borrower (
    borrower_id borrower_id_type not null primary key,
    last_name char(20) not null,
    first_name char(20) not null,
    unique(last_name, first_name)
);

create table book (
    call_number call_number_type not null primary key,
	    check (left(char(call_number), 1) between 'A' and 'Z'),
    title char(30) not null,
    author char(20)
);

create table checked_out (
    borrower_id borrower_id_type not null references borrower,
    call_number call_number_type not null primary key references book, 
    date_due date not null
);

create table reserve_book (
    call_number call_number_type not null primary key references book, 
    course_id char(5) not null
);

insert into borrower values('12345', 'aardvark', 'anthony');
insert into borrower values('20147', 'cat', 'charlene');
insert into borrower values('89754', 'dog', 'donna');
insert into borrower values('60984', 'fox', 'frederick');
insert into borrower values('54872', 'zebra', 'zelda');

insert into book values('QA76.093', 'Wenham Zoo Guide', 'elephant');
insert into book values('RZ12.905', 'Fire Hydrants I Have Known', 'dog');
insert into book values('LM925.04', '21 Ways to Cook a Cat', 'dog');
insert into book values('AB123.40', 'Karate', 'koala');

insert into checked_out values('89754', 'RZ12.905', '2011-2-15');
insert into checked_out values('89754', 'LM925.04', '2011-2-15');
insert into checked_out values('20147', 'AB123.40', '2002-2-15');

insert into reserve_book values('QA76.093', 'BY123');
insert into reserve_book values('AB123.40', 'PE075');

grant select on borrower to public;
grant select on book to public;
grant select on checked_out to public;
grant select on reserve_book to public;
grant select on employee to public;
