CREATE TABLE BESTSELLER
(
BS_ID INT (4) NOT NULL AUTO_INCREMENT,
AUTHOR INT,
GENRE CHAR(6),
PUBLISHER CHAR(3),
TITLE VARCHAR (150),
YEAR INT (4),
SOLD INT (12),
PRIMARY KEY (BOOK_ID),
FOREIGN KEY (AUTHOR) REFERENCES AUTHOR ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (GENRE) REFERENCES GENRE ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (PUBLISHER) REFERENCES PUBLISHER ON DELETE RESTRICT ON UPDATE CASCADE
);



CREATE TABLE AUTHOR
(
AUTHOR_ID INT (4) NOT NULL AUTO_INCREMENT,
LAST_NAME VARCHAR (150),
FIRST_NAME VARCHAR (150),
NATIONALITY INT (3),
YEAR_BORN INT(4),
YEAR_DIED INT(4),
PRIMARY KEY (AUTHOR_ID),
FOREIGN KEY (NATIONALITY) REFERENCES COUNTRY ON DELETE RESTRICT ON UPDATE CASCADE
);



CREATE TABLE GENRE
(
GENRE_ID INT (4) NOT NULL AUTO_INCREMENT,
GENRE VARCHAR (150) ,
PRIMARY KEY (GENRE_ID)
);


CREATE TABLE PUBLISHER
(
PUBL_ID INT (4) NOT NULL AUTO_INCREMENT,
NAME VARCHAR (150),
PRIMARY KEY (PUBL_ID)
);

CREATE TABLE COUNTRY
(
COUNTRY_ID CHAR(2) NOT NULL,
NAME VARCHAR(100) NOT NULL,
PRIMARY KEY ( COUNTRY_ID )
) ;

CREATE TABLE CITY
(
CITY_ID CHAR(2) NOT NULL,
NAME VARCHAR(100) NOT NULL,
COUNTRY CHAR(2) NOT NULL,
PRIMARY KEY ( CITY_ID ),
FOREIGN KEY (COUNTRY) REFERENCES COUNTRY ON DELETE RESTRICT ON UPDATE CASCADE
) ;



INSERT INTO PUBLISHER VALUES
( 1,'Corgi Books' ) ,
( 2,'Bloomsbury Publishing PLC' ) ,
( 3,'Arrow Books' ) ,
( 4,'ATOM' ) ,
( 5,'Quercus' ) ,
( 6,'Bantam Press' ) ,
( 7,'Picador' ) ,
( 8,'Vintage' ) ,
( 9,'Black Swan' ) ,
( 10,'Quercus Publishing Plc' ) ,
( 11,'Puffin Books' ) ,
( 12,'Macmillan Childrens Books' ) ,
( 13,'Michael Joseph' ) ,
( 14,'Hodder Paperback' ) ,
( 15,'Penguin Books' ) ,
( 16,'Minerva' ) ,
( 17,'Century' ) ,
( 18,'Canongate Books' ) ,
( 19,'HarperCollins Entertainment' ) ,
( 20,'Orion'  ) ,
( 21,'Flamingo' ) ,
( 22,'Point' ) ,
( 23,'HarperCollins Publishers' ) ,
( 24,'Abacus' ) ,
( 25,'Headline Review' ) ,
( 26,'Phoenix' ) ,
( 27,'Vermilion' ) ,
( 28,'Profile Books' ) ,
( 29,'Random House' ) ,
( 30,'Red Fox' ) ,
( 31,'Hodder & Stoughton Ltd' ) ,
( 32,'Thorsons' ) ,
( 33,'Time Warner Books' ) ,
( 34,'Chatto & Windus' ) ,
( 35,'D.C. Thomson & Co.,' ) ,
( 36,'Pan Books' ) ,
( 37,'Scholastic' )

INSERT INTO GENRE VALUES
( 1,'Crime, Thriller & Adventure' ) ,
( 2,'Children\'s Fiction' ) ,
( 3,'Romance & Sagas' ) ,
( 4,'Young Adult Fiction' ) ,
( 5,'General & Literary Fiction' ) ,
( 6,'Popular Science' ) ,
( 7,'Picture Books' ) ,
( 8,'Food & Drink: General' ) ,
( 9,'Humour: Collections & General' ) ,
( 10,'Autobiography: General' ) ,
( 11,'Biography: The Arts' ) ,
( 12,'Science Fiction & Fantasy' ) ,
( 13,'Fitness & Diet' ) ,
( 14,'Usage & Writing Guides' ) ,
( 15,'Popular Culture & Media: General Interest' ) ,
( 16,'Autobiography:  The Arts' ) ,
( 17,'Current Affairs & Issues' ) ,
( 18,'Travel Writing' ) ,
( 19,'National & Regional Cuisine' ) ,
( 20,'Children\'s Annuals' ) ,
( 21,'Biography: General' ) ;

INSERT INTO COUNTRY VALUES
( 'NL' , 'Netherlands' ) ,
( 'UK' , 'United Kingdom' ) ,
( 'US' , 'United States' ) ,
( 'FR' , 'France' ) ,
( 'AT' , 'Austria' ) ,
( 'AU' , 'Australia' ) ,
( 'DE' , 'Germany' ) ,
( 'SE' , 'Sweden' ) ,
( 'IE' , 'Ireland' ) ;

INSERT INTO AUTHOR VALUES
( 1,'Brown','Dan' , 'US' , '' , '' ) ,
( 2,'Rowling','J.K.' , 'UK' , '' , '' ) ,
( 3,'James','E.L.' , 'UK' , '1963' , '' ) ,
( 4,'Meyer','Stephenie' , 'US' , '1973' , '' ) ,
( 5,'Larsson','Stieg' , 'SE' , '1954' , '2004' ) ,
( 6,'Sebold','Alice' , '' , '' , '' ) ,
( 7,'Haddon','Mark' , 'UK' , '' , '' ) ,
( 8,'Bryson','Bill' , 'US' , '' , '' ) ,
( 9,'Carle','Eric' , '' , '' , '' ) ,
( 10,'Donaldson','Julia' , '' , '' , '' ) ,
( 11,'Oliver','Jamie' , 'UK' , '' , '' ) ,
( 12,'Hosseini','Khaled' , '' , '' , '' ) ,
( 13,'Nicholls','David' , 'UK' , '' , '' ) ,
( 14,'Niffenegger','Audrey' , '' , '' , '' ) ,
( 15,'McEwan','Ian' , 'UK' , '' , '' ) ,
( 16,'Fielding','Helen' , 'UK' , '' , '' ) ,
( 17,'Clarkson','Jeremy' , 'UK' , '' , '' ) ,
( 18,'Bernieres','Louis de' , '' , '' , '' ) ,
( 19,'Kay','Peter' , '' , '' , '' ) ,
( 20,'Martel','Yann' , '' , '' , '' ) ,
( 21,'Stephenson','Pamela' , '' , '' , '' ) ,
( 22,'Pelzer','Dave' , '' , '' , '' ) ,
( 23,'McCourt','Frank' , 'IE' , '' , '' ) ,
( 24,'Faulks','Sebastian' , '' , '' , '' ) ,
( 25,'Pullman','Philip' , '' , '' , '' ) ,
( 26,'Mosse','Kate' , '' , '' , '' ) ,
( 27,'Stockett','Kathryn' , '' , '' , '' ) ,
( 28,'Parsons','Tony' , '' , '' , '' ) ,
( 29,'Golden','Arthur' , '' , '' , '' ) ,
( 30,'McCall Smith','Alexander' , '' , '' , '' ) ,
( 31,'Hislop','Victoria' , '' , '' , '' ) ,
( 32,'Ahern','Cecelia' , '' , '' , '' ) ,
( 33,'McKeith','Gillian' , '' , '' , '' ) ,
( 34,'Zafon','Carlos Ruiz' , '' , '' , '' ) ,
( 35,'Grisham','John' , '' , '' , '' ) ,
( 36,'Atkins','Robert C.' , '' , '' , '' ) ,
( 37,'Truss','Lynne' , '' , '' , '' ) ,
( 38,'Smith','Delia' , 'UK' , '' , '' ) ,
( 39,'Harris','Joanne' , '' , '' , '' ) ,
( 40,'Boyne','John' , 'UK' , '' , '' ) ,
( 41,'Picoult','Jodi' , '' , '' , '' ) ,
( 42,'Lee','Harper' , 'US' , '' , '' ) ,
( 43,'Gray','John' , '' , '' , '' ) ,
( 44,'French','Dawn' , 'UK' , '' , '' ) ,
( 45,'Lewycka','Marina' , '' , '' , '' ) ,
( 46,'Harris','Thomas' , '' , '' , '' ) ,
( 47,'Tolkien','J. R. R.' , 'UK' , '' , '' ) ,
( 48,'Moore','Michael' , 'US' , '' , '' ) ,
( 49,'Rubenfeld','Jed' , '' , '' , '' ) ,
( 50,'Osbourne','Sharon' , 'UK' , '' , '' ) ,
( 51,'Coelho','Paulo' , '' , '' , '' ) ,
( 52,'O\'Grady','Paul' , '' , '' , '' ) ,
( 53,'McKenna','Paul' , '' , '' , '' ) ,
( 54,'Levy','Andrea' , '' , '' , '' ) ,
( 55,'Lawson','Nigella' , '' , '' , '' ) ,
( 56,'Ali','Monica' , '' , '' , '' ) ,
( 57,'Edwards','Kim' , '' , '' , '' ) ,
( 58,'Hornby','Nick' , 'UK' , '' , '' ) ,
( 59,'Brand','Russell' , 'UK' , '' , '' ) ,
( 60,'Dawkins','Richard' , 'UK' , '' , '' ) ,
( 61,'0','' , '' , '' , '' ) ,
( 62,'Smith','Zadie' , 'UK' , '' , '' ) ,
( 63,'Morton','Kate' , '' , '' , '' ) ,
( 64,'Zusak','Markus' , 'AU' , '1975' , '' ) ,
( 65,'Binchy','Maeve' , 'IE' , '1940' , '2012' ) ,
( 66,'Harris','Robert' , 'UK' , '1957' , '' ) ,
( 67,'Collins','Suzanne' , 'US' , '1962' , '' ) ;

INSERT INTO BOOK VALUES
( '0','1','1','1','Da Vinci Code\, The', '2004', '5094805' ) ,
( '0','2','2','2','Harry Potter and the Deathly Hallows', '2007', '4475152' ) ,
( '0','2','2','2','Harry Potter and the Philosopher\'s Stone', '1997', '4200654' ) ,
( '0','2','2','2','Harry Potter and the Order of the Phoenix', '2003', '4179479' ) ,
( '0','3','3','3','Fifty Shades of Grey', '2012', '3758936' ) ,
( '0','2','2','2','Harry Potter and the Goblet of Fire', '2001', '3583215' ) ,
( '0','2','2','2','Harry Potter and the Chamber of Secrets', '1999', '3484047' ) ,
( '0','2','2','2','Harry Potter and the Prisoner of Azkaban', '2000', '3377906' ) ,
( '0','1','1','1','Angels and Demons', '2003', '3193946' ) ,
( '0','2','2','2','Harry Potter and the Half-blood Prince Children\'s Edition', '2005', '2950264' ) ,
( '0','3','3','3','Fifty Shades Darker', '2012', '2479784' ) ,
( '0','4','4','4','Twilight', '2006', '2315405' ) ,
( '0','5','1','5','Girl with the Dragon Tattoo\, The: Millennium Trilogy', '2008', '2233570' ) ,
( '0','3','3','3','Fifty Shades Freed', '2012', '2193928' ) ,
( '0','1','1','6','Lost Symbol\, The', '2009', '2183031' ) ,
( '0','4','4','4','New Moon', '2007', '2152737' ) ,
( '0','1','1','1','Deception Point', '2004', '2062145' ) ,
( '0','4','4','4','Eclipse', '2008', '2052876' ) ,
( '0','6','5','7','Lovely Bones\, The', '2003', '2005598' ) ,
( '0','7','5','8','Curious Incident of the Dog in the Night-time\, The', '2004', '1979552' ) ,
( '0','1','1','1','Digital Fortress', '2004', '1928900' ) ,
( '0','8','6','9','Short History of Nearly Everything\, A', '2004', '1852919' ) ,
( '0','5','1','10','Girl Who Played with Fire\, The:Millennium Trilogy', '2009', '1814784' ) ,
( '0','4','4','4','Breaking Dawn', '2008', '1787118' ) ,
( '0','9','7','11','Very Hungry Caterpillar\, The:The Very Hungry Caterpillar', '1994', '1783535' ) ,
( '0','10','7','12','Gruffalo\, The', '1999', '1781269' ) ,
( '0','11','8','13','Jamie\'s 30-Minute Meals', '2010', '1743266' ) ,
( '0','12','5','2','Kite Runner\, The', '2004', '1629119' ) ,
( '0','13','5','14','One Day', '2010', '1616068' ) ,
( '0','12','5','2','Thousand Splendid Suns\, A', '2007', '1583992' ) ,
( '0','5','1','10','Girl Who Kicked the Hornets\' Nest\, The:Millennium Trilogy', '2010', '1555135' ) ,
( '0','14','5','8','Time Traveler\'s Wife\, The', '2004', '1546886' ) ,
( '0','15','5','8','Atonement', '2002', '1539428' ) ,
( '0','16','5','7','Bridget Jones\'s Diary:A Novel', '1997', '1508205' ) ,
( '0','17','9','15','World According to Clarkson\, The', '2005', '1489403' ) ,
( '0','18','5','16','Captain Corelli\'s Mandolin', '1995', '1352318' ) ,
( '0','19','10','17','Sound of Laughter\, The', '2006', '1310207' ) ,
( '0','20','5','18','Life of Pi', '2003', '1310176' ) ,
( '0','21','11','19','Billy Connolly', '2002', '1231957' ) ,
( '0','22','10','20','Child Called It\, A', '2001', '1217712' ) ,
( '0','10','7','12','Gruffalo\'s Child\, The', '2005', '1208711' ) ,
( '0','23','10','21','Angela\'s Ashes: A Memoir of a Childhood', '1997', '1204058' ) ,
( '0','24','5','8','Birdsong', '1994', '1184967' ) ,
( '0','25','4','22','Northern Lights: His Dark Materials S.', '1998', '1181503' ) ,
( '0','26','5','20','Labyrinth', '2005', '1181093' ) ,
( '0','2','12','2','Harry Potter and the Half-blood Prince', '2005', '1153181' ) ,
( '0','27','5','15','Help\, The', '2010', '1132336' ) ,
( '0','28','5','23','Man and Boy', '2000', '1130802' ) ,
( '0','29','5','8','Memoirs of a Geisha', '1998', '1126337' ) ,
( '0','30','1','24','No.1 Ladies\' Detective Agency', '2003', '1115549' ) ,
( '0','31','5','25','Island\, The', '2006', '1108328' ) ,
( '0','32','5','23','PS\,  I Love You', '2004', '1107379' ) ,
( '0','33','13','13','You are What You Eat: The Plan That Will Change Your Life', '2004', '1104403' ) ,
( '0','34','5','26','Shadow of the Wind\, The', '2005', '1092349' ) ,
( '0','2','2','2','Tales of Beedle the Bard\, The', '2008', '1090847' ) ,
( '0','35','1','3','Broker\, The', '2005', '1087262' ) ,
( '0','36','13','27','Dr. Atkins\' New Diet Revolution:The No-hunger\,  Luxurious Weight Loss P', '2003', '1054196' ) ,
( '0','25','4','22','Subtle Knife\, The:His Dark Materials S.', '1998', '1037160' ) ,
( '0','37','14','28','Eats\,  Shoots and Leaves: The Zero Tolerance Approach to Punctuation', '2003', '1023688' ) ,
( '0','38','8','29','Delia\'s How to Cook (Bk.1)', '1998', '1015956' ) ,
( '0','39','5','9','Chocolat', '2000', '1009873' ) ,
( '0','40','4','30','Boy in the Striped Pyjamas\, The', '2008', '1004414' ) ,
( '0','41','5','31','My Sister\'s Keeper', '2004', '1003780' ) ,
( '0','25','4','22','Amber Spyglass\, The: His Dark Materials S.', '2001', '1002314' ) ,
( '0','42','5','3','To Kill a Mockingbird', '1989', '998213' ) ,
( '0','43','15','32','Men are from Mars\,  Women are from Venus', '1993', '992846' ) ,
( '0','44','16','17','Dear Fatty', '2008', '986753' ) ,
( '0','45','5','15','Short History of Tractors in Ukrainian\, A', '2006', '986115' ) ,
( '0','46','1','3','Hannibal', '2000', '970509' ) ,
( '0','47','12','23','Lord of the Rings\, The', '1995', '967466' ) ,
( '0','48','17','15','Stupid White Men', '2002', '963353' ) ,
( '0','49','1','25','Interpretation of Murder\, The', '2007', '962515' ) ,
( '0','50','16','33','Sharon Osbourne Extreme: My Autobiography', '2005', '959496' ) ,
( '0','51','5','32','Alchemist\, The: A Fable About Following Your Dream', '1998', '956114' ) ,
( '0','52','16','6','At My Mother\'s Knee and Other Low Joints', '2008', '945640' ) ,
( '0','8','18','9','Notes from a Small Island', '1996', '931312' ) ,
( '0','11','8','13','Return of the Naked Chef\, The', '2000', '925425' ) ,
( '0','16','5','7','Bridget Jones: The Edge of Reason', '2000', '924695' ) ,
( '0','11','19','13','Jamie\'s Italy', '2005', '906968' ) ,
( '0','53','13','6','I Can Make You Thin', '2005', '905086' ) ,
( '0','8','18','9','Down Under', '2001', '890847' ) ,
( '0','35','1','3','Summons\, The', '2002', '869671' ) ,
( '0','54','5','25','Small Island', '2004', '869659' ) ,
( '0','55','8','34','Nigella Express', '2007', '862602' ) ,
( '0','56','5','9','Brick Lane', '2004', '856540' ) ,
( '0','57','5','15','Memory Keeper\'s Daughter\, The', '2007', '845858' ) ,
( '0','10','7','12','Room on the Broom', '2002', '842535' ) ,
( '0','58','5','15','About a Boy', '2002', '828215' ) ,
( '0','59','16','31','My Booky Wook', '2007', '820563' ) ,
( '0','60','6','9','God Delusion\, The', '2007', '816907' ) ,
( '0','61','20','35','Beano Annual\, The', '2004', '816585' ) ,
( '0','62','5','15','White Teeth', '2001', '815586' ) ,
( '0','63','5','36','House at Riverton\, The', '2007', '814370' ) ,
( '0','64','5','9','Book Thief\, The', '2007', '809641' ) ,
( '0','65','5','20','Nights of Rain and Stars', '2005', '808900' ) ,
( '0','66','5','3','Ghost\, The', '2008', '807311' ) ,
( '0','11','8','13','Happy Days with the Naked Chef', '2001', '794201' ) ,
( '0','67','4','37','Hunger Games\, The:Hunger Games Trilogy', '2011', '792187' ) ,
( '0','22','21','20','Lost Boy\, The: A Foster Child\'s Search for the Love of a Family', '2001', '791507' ) ,
( '0','11','8','13','Jamie\'s Ministry of Food:Anyone Can Learn to Cook in 24 Hours', '2008', '791095' ) ;