create table Book (
  isbn		varchar(17),
  title		varchar(100) not null,
  author	varchar(100),
  year0		integer,
  pages		integer,
  constraint	Book_PK primary key (isbn)
);

create table library_User (
  username	varchar(20),
  password	varchar(20) not null,
  realname	varchar(40),
  email		varchar(40),
  constraint	libraryUser_PK primary key (username)
);

create table Book_Copy (
  id		integer generated by default as IDENTITY,
  isbn		varchar(17),
  borrower	varchar(20),
  constraint	BookCopy_PK primary key (id),
  constraint	BookCopy_isbn_FK foreign key (isbn) references Book(isbn),
  constraint	BookCopy_borrower_FK foreign key (borrower) references library_User(username)
);


create table Reservation (
  id		integer generated by default as IDENTITY,
  bookcopy_id	integer,
  username	varchar(20),
  reserved_from		date not null,
  reserved_to		date not null,
  returned	date,
  constraint	Reservation_PK primary key (id),
  constraint	Reservation_bookcopy_FK foreign key (bookcopy_id) references Book_Copy(id),
  constraint	Reservation_username_FK foreign key (username) references library_User(username)
);

insert into Book (isbn, title, author, year0, pages) values ('0345391802', 'The Hitchhiker''s Guide to the Galaxy', 'Douglas Adams', 1995, 320);
insert into Book (isbn, title, author, year0, pages) values ('0345418921', 'The Restaurant at the End of the Universe', 'Douglas Adams', 2005, 256);
insert into Book (isbn, title, author, year0, pages) values ('0345418905', 'Life, the Universe and Everything', 'Douglas Adams', 2005, 240);
insert into Book (isbn, title, author, year0, pages) values ('0345479963', 'So Long, and Thanks for All the Fish', 'Douglas Adams', 2005, 224);
insert into Book (isbn, title, author, year0, pages) values ('0345418778', 'Mostly Harmless', 'Douglas Adams', 2000, 240);

insert into Book_Copy (id, isbn) values (1, '0345391802');
insert into Book_Copy (id, isbn) values (2, '0345391802');
insert into Book_Copy (id, isbn) values (3, '0345391802');

insert into Book_Copy (id, isbn) values (4, '0345418921');
insert into Book_Copy (id, isbn) values (5, '0345418921');

insert into Book_Copy (id, isbn) values (6, '0345418905');

insert into Book_Copy (id, isbn) values (7, '0345479963');
insert into Book_Copy (id, isbn) values (8, '0345479963');
insert into Book_Copy (id, isbn) values (9, '0345479963');

insert into library_User (username, password) values ('Tom','tom');
insert into library_User (username, password) values ('Steve', 'steve');

insert into Reservation (bookcopy_id, username, reserved_from, reserved_to) values (1, 'Tom', '2005-07-01', '2005-07-10');
insert into Reservation (bookcopy_id, username, reserved_from, reserved_to) values (2, 'Steve', '2005-07-01', '2005-07-20');
insert into Reservation (bookcopy_id, username, reserved_from, reserved_to) values (6, 'Steve', '2005-08-01', '2005-08-10');
insert into Reservation (bookcopy_id, username, reserved_from, reserved_to) values (6, 'Steve', '2005-09-01', '2005-09-10');