drop table borrowed;
drop table author_book;
drop table book;
drop table publisher;
drop table member;
drop table author;

create table publisher(
     id serial primary key,
     name varchar(50) not null
);

create table book (
    id serial primary key,
    isbn varchar(15),
    name varchar(50) not null,
    year int2 not null check(year > 1970 and year < 2050),
    publisher integer references publisher on delete restrict on update cascade
);

create table author (
    id serial primary key,
    name varchar(20) not null,
    lastname varchar(20) not null
);

create table author_book (
   book integer references book,
   author integer references author,
   primary key(book, author)
);

create table member (
   id serial primary key,
   name varchar(20) not null,
   lastname varchar(20) not null,
   address varchar(50) not null
);

create table borrowed (
   member integer references member,
   date_borrowed date,
   date_returned date,
   book integer references book,
   primary key(member, date_borrowed, book)
);