# Start from scratch


drop database if exists library;


create database library;


use library;





# Create the tables





create table borrowers


(  borrowerid int not null primary key auto_increment,


   name varchar(100) not null,


   address varchar(100) not null


) engine = innodb;





create table books


(  bookid int not null primary key auto_increment,


   title varchar(100) not null,


   author varchar(100) not null,


   onloan boolean,


   duedate date,


   borrowerid int,


   foreign key (borrowerid) references borrowers(borrowerid)


) engine = innodb;


 


# Populate the tables with some sample data





insert into borrowers values


  (100, 'Homer Simpson', '742 Evergreen Terrace, Springfield'),


  (101, 'John Doe', '54 High Street, Bagshot'),


  (102, 'Jane Smith', '5 Church Lane, Hambridge'),


  (103, 'Henry Higgins', '14 Mayfair');





insert into books values


  (1, 'The Goblet of Fire', 'J. K. Rowling', 1, 20130316, 102),


  (2, 'Wind in the Willows', 'Kenneth Grahame', false, null, null),


  (3, 'Great Expectations', 'Charles Dickens', false, null, null),


  (4, 'Life, the Universe and Everything', 'Douglas Adams', false, null, null),


  (5, 'A Christmas Carol', 'Charles Dickens', false, null, null);





