
create table if not exists books 
(
  bno int not null,
  title varchar(40) not null,
  author varchar(20) not null,
  date int not null,
  edition int not null,
  primary key (bno)
);


create table if not exists publishers
(
  pno int not null,
  publisher varchar(20) not null,
  city varchar(20) not null,
  website varchar(100) not null,
  primary key (pno)
);

create table if not exists publishes 
(
  pno int not null,
  bno int not null,
  pages int not null,
  copyright int not null,
  primary key (pno,bno),
  FOREIGN KEY (pno)
    REFERENCES publishers (pno),
  FOREIGN KEY (bno)
    REFERENCES books (bno)
);

insert into books values 
 (231,'The Soul of a New Machine', 'Tracy Kidder',1981,1),
 (77,'Programming Pearls', 'Jon Bentley',2000,2),
 (23,'Programming Pearls', 'Jon Bentley',1981,1),
 (2,'Tess of the d\'Urbevilles', 'Thomas Hardy',1850,1),
 (44, 'Guide to LaTex', 'Helmut Kopka', 2004, 4),
 (88, 'Algorithmics', 'David Harel', 2004, 3);

insert into publishers values 
 (1,'Back Bay Books', 'Boston', 'backbay.com'),
 (2,'Addison Wesley', 'New York', 'addisonwesley.com'),
 (3,'Modern Library', 'London', 'randomhouse.com'),
 (4,'Penguin', 'New York', 'penguin.com');

insert into publishes values
(1,  	231,  	293,  	1981),
(2, 	77, 	235, 	2001),
(2, 	23, 	200, 	1980),
(3, 	2, 	565, 	2001),
(4, 	2, 	540, 	1990),
(2, 	44, 	336, 	2004)