create procedure FindPopularBooks ()
begin
	select Book.bookName, Writer.fullName, count(*)
        from Book, Writer, TakenBooks 
        where TakenBooks.bookId = Book.id and Book.authorId = Writer.id 
        group by bookId order by count(*) desc; 
end$$

drop procedure if exists createReader$$
drop procedure if exists createWriter$$
drop procedure if exists newWriter$$
drop procedure if exists newBook$$
drop procedure if exists addBooks$$
drop procedure if exists FindBookByName$$
drop procedure if exists FindBooksByWriter$$
drop procedure if exists FindReaderByName$$
drop function if exists isBookAvailable$$
drop procedure if exists ReaderTakesBook$$
drop procedure if exists ReaderReturnsBook$$
drop procedure if exists FindPopularBooks$$
drop procedure if exists FindPopularWriters$$
drop procedure if exists FindActiveReaders$$
drop procedure if exists WriteOffDamagedBook$$

create table Writer (id int(10) auto_increment,  firstName varchar(20),  lastName varchar(20), fullName varchar(60), info varchar (1000), primary key (id));

create table WriterOfLiteraryWork(id int(10) auto_increment, authorId int(10), literaryWorkId int(10), primary key (id), foreign key (authorId) references Writer(id), 
	foreign key (literaryWorkId) references LiteraryWork(id));

create table LiteraryWork (id int(10) auto_increment, literaryWorkName varchar(100), primary key (id));

create table BookInfo (id int(10) auto_increment, literaryWorkId int(10), bookId int(10), primary key (id),  foreign key (bookId) references Book(id), 
	foreign key (literaryWorkId) references LiteraryWork(id));

create table Book (id int(10) auto_increment, bookName varchar(100), numberOfBooks int(10) default 1, currentNumberOfBooks int(10) default 1, primary key (id));