-- phpMyAdmin SQL Dump -- version 4.8.0.1 -- https://www.phpmyadmin.net/ -- -- Host:…
- User
-- phpMyAdmin SQL Dump -- version 4.8.0.1 -- https://www.phpmyadmin.net/
-- Host: localhost -- Generation Time: May 23, 2018 at 10:40 PM -- Server version: 10.2.14-MariaDB -- PHP Version: 7.2.5
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -- Database:
mostreadbooksDROP SCHEMA IF EXISTS
mostreadbooks; CREATE SCHEMA IF NOT EXISTSmostreadbooksDEFAULT CHARACTER SET latin1 ; USEmostreadbooks;
-- -- Table structure for table
AuthorCREATE TABLE IF NOT EXISTS
mostreadbooks.Author(AuthorIDint(10) UNSIGNED NOT NULL,Namevarchar(30) NOT NULL,Surnamevarchar(30) NOT NULL,Nationalityvarchar(30) NOT NULL,BirthYearint(4) UNSIGNED NOT NULL,DeathYearint(4) UNSIGNED DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- -- Dumping data for table
AuthorINSERT INTO
Author(AuthorID,Name,Surname,Nationality,BirthYear,DeathYear) VALUES (1, 'Miguel', 'de Cervantes Saavedra', 'Spanish', 1547, 1616), (2, 'Charles', 'Dickens', 'British', 1812, 1870), (3, 'J.R.R.', 'Tolkien', 'British', 1892, 1973), (4, 'Antoine', 'de Saint-Exupery', 'French', 1900, 1944), (5, 'J.K.', 'Rowling', 'British', 1965, NULL), (6, 'Agatha', 'Christie', 'British', 1890, 1976), (7, 'Cao', 'Xueqin', 'Chinese', 1715, 1763), (8, 'Henry', ' Rider Haggard', 'British', 1856, 1925), (9, 'C.S.', 'Lewis', 'British', 1898, 1963);
-- -- Table structure for table
BookCREATE TABLE IF NOT EXISTS
mostreadbooks.Book(BookIDint(10) UNSIGNED NOT NULL,BookTitlevarchar(255) NOT NULL,OriginalTitlevarchar(255) DEFAULT NULL,YearofPublicationint(4) NOT NULL DEFAULT 0,Genrevarchar(30) NOT NULL,MillionsSoldint(10) UNSIGNED NOT NULL,LanguageWrittenvarchar(30) NOT NULL,AuthorIDint(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- -- Dumping data for table
BookINSERT INTO
Book(BookID,BookTitle,OriginalTitle,YearofPublication,Genre,MillionsSold,LanguageWritten,AuthorID) VALUES (1, 'Don Quixote', 'El Ingenioso Hidalgo Don Quixote de la Mancha', 1605, 'Novel', 500, 'Spanish', 1), (2, 'A Tale of Two Cities', 'A Tale of Two Cities', 1859, 'Historical Fiction', 200, 'English', 2), (3, 'The Lord of the Rings', 'The Lord of the Rings', 1954, 'Fantasy/Adventure', 150, 'English', 3), (4, 'The Litle Prince', 'Le Petit Prince', 1943, 'Fable', 142, 'French', 4), (5, 'Harry Potter and the Sorcerer's Stone', 'Harry Potter and the Sorcerer's Stone', 1997, 'Fantasy Fiction', 107, 'English', 5), (6, 'And Then There Were None', 'Ten Little Niggers', 1939, 'Mystery', 100, 'English', 6), (7, 'The Dream of the Red Chamber', 'The Story of the Stone', 1792, 'Novel', 100, 'Chinese', 7), (8, 'The Hobbit ', 'There and Back Again', 1937, 'High Fantasy', 100, 'English', 3), (9, 'She: A History of Adventure', 'She', 1886, 'FIction', 100, 'English', 8), (10, 'The Lion, the Witch and the Wardrobe', 'The Lion, the Witch and the Wardrobe', 1950, 'Fantasy', 85, 'English ', 9);
-- -- Table structure for table
BookPlotCREATE TABLE IF NOT EXISTS
mostreadbooks.BookPlot(BookPlotIDint(10) UNSIGNED NOT NULL,Plottext NOT NULL,PlotSourcevarchar(255) NOT NULL,BookIDint(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- -- Dumping data for table
BookPlotINSERT INTO
BookPlot(BookPlotID,Plot,PlotSource,BookID) VALUES (1, 'The story follows the adventures of a hidalgo named Mr. Alonso Quixano who reads so many chivalric romances that he loses his sanity and decides to set out to revive chivalry, undo wrongs, and bring justice to the world, under the name Don Quixote de la Mancha. He recruits a simple farmer, Sancho Panza, as his squire, who often employs a unique, earthy wit in dealing with Don Quixote's rhetorical orations on antiquated knighthood.', 'https://en.wikipedia.org/wiki/Don_Quixote ', 1), (2, 'In A Tale of Two Cities, Charles Darnay tries to escape his heritage as a French aristocrat in the years leading up to the French Revolution. On the eve of the Revolution, he's captured, but Sydney Carton, a man who looks like Darnay, takes his place and dies on the guillotine.', 'https://www.enotes.com/topics/tale-of-two-cities', 2), (3, 'The title of the novel refers to the story's main antagonist, the Dark Lord Sauron, who had in an earlier age created the One Ring to rule the other Rings of Power as the ultimate weapon in his campaign to conquer and rule all of Middle-earth. From quiet beginnings in the Shire, a hobbit land not unlike the English countryside, the story ranges across Middle-earth, following the course of the War of the Ring through the eyes of its characters, not only the hobbits Frodo Baggins, Samwise "Sam" Gamgee, Meriadoc "Merry" Brandybuck and Peregrin "Pippin" Took, but also the hobbits' chief allies and travelling companions: the Men Aragorn son of Arathorn, a Ranger of the North, and Boromir, a Captain of Gondor; Gimli son of Glóin, a Dwarf warrior; Legolas Greenleaf, an Elven prince; and Gandalf, a Wizard.', 'https://en.wikipedia.org/wiki/The_Lord_of_the_Rings', 3), (4, 'f Saint-Exupéry is to be believed The Little Prince is a book for children written for grown-ups. It can be read on many different levels to provide pleasure and food for thought for readers of all ages.\r\n\r\nThe author, an aviator, crashes with his aeroplane in the middle of the Sahara desert. While he is trying to repair his aeroplane, a little boy appears and asks him to draw a sheep. The author learns that The Little Prince comes from asteroid B-612 where he has left behind three volcanoes and a rose.\r\n\r\nBefore reaching Earth, he has visited other planets and met some very odd people: a king, a conceited man, a drunkard, a lamplighter, a geographer… Since arriving on Earth, he has spoken to a fox who has taught him that to know someone or something, you must « tame » them, and that makes them unique. « What is essential is invisible to the eye, says the fox. »\r\n\r\nIn order to return to his planet and his rose, The Little Prince allows himself to be bitten by a poisonous snake: his planet is too far away, he cannot take his « shell ». The aviator, who has succeeded in repairing his plane, also quits the desert. He still hopes The Little Prince will return one day and asks us to write and tell him if ever we should meet his friend.', 'http://www.thelittleprince.com/work/the-story/', 4), (5, 'Harry Potter is not a normal boy. Raised by his cruel Aunt and Uncle, and tormented by his bully of a cousin, Dudley, he has resigned to a life of neglect. On his eleventh birthday, however, a half-giant called Hagrid comes crashing-–quite literally-–into his life, and announces that Harry is a wizard. Together they journey to London to get school supplies for Harry’s first year at Hogwarts School of Witchcraft and Wizardry. On 1st September Harry takes a train from King’s Cross station, Platform 9 ¾, to Hogwarts school, where he meets Ron Weasley and Hermione Granger. The three are sorted into the same House, Gryffindor, and although Harry and Ron find Hermione bossy and annoying at first, the three soon become best friends.\r\n\r\nAfter accidentally stumbling into a forbidden corridor and finding a three-headed dog guarding a mysterious trapdoor, the trio attempt to find out what’s concealed within. Some useful slip-ups from Hagrid lead them to research a man called Nicholas Flamel. When Harry uses his father’s Invisibility Cloak to search at night, he happens upon a curious mirror which shows him his dead parents and family. Headmaster Albus Dumbledore reveals to him that this Mirror of Erised shows the beholder their deepest desires, and that what Harry wants most in the world is a family.\r\n\r\nLater in the year, Hagrid obtains a dragon egg, and Harry, Ron and Hermione know that once it has hatched he can’t keep it, because it is illegal. Harry and Hermione arrange to give it away one night, but are caught, along with Harry’s enemy Draco Malfoy and their fellow Gryffindor, Neville Longbottom. The four are given detentions, which they carry out with Hagrid in the Forbidden Forest. Harry and Draco come across a slain Unicorn, and crouching over it is the dark wizard, Lord Voldemort, the man who killed Harry’s parents and who Harry supposedly defeated as a baby.\r\n\r\nHermione realises that what is hidden behind the trapdoor is the Philosopher’s Stone, which makes the person who possesses it immortal. The three guess that Voldemort wants the Stone, and that the suspicious Professor Snape is helping him. They journey through the many obstacles that lie behind the trapdoor to get to the Stone, but Harry finds that it is Professor Quirrell who was helping Voldemort all along. He manages to defeat Voldemort and save the Stone, but not Quirrell’s life. After Dumbledore explains all to him, Harry leaves Hogwarts at the end of the year, returning to his Aunt and Uncle.', 'http://www.hypable.com/harry-potter/sorcerers-stone-book-plot/', 5), (6, 'And Then There Were None is a detective fiction novel by Agatha Christie, first published in the United Kingdom by the Collins Crime Club on 6 November 1939 under the title Ten Little Niggers and in the United States by Dodd, Mead and Company in January 1940 under the title And Then There Were None. In the novel, ten people, who have previously been complicit in the deaths of others but have escaped notice and/or punishment, are tricked into coming onto an island. Even though the guests are the only people on the island, they are all mysteriously murdered one by one, in a manner paralleling, inexorably and sometimes grotesquely, the old nursery rhyme, "Ten Little Indians". The UK edition retailed at seven shillings and sixpence (7/6) and the US edition at $2.00. The novel has also been published and filmed under the title Ten Little Indians.', 'http://agathachristie.wikia.com/wiki/And_Then_There_Were_None', 6), (7, 'The novel provides a detailed, episodic record of the two branches of the Jia Clan, the Ning-guo and Rong-guo Houses, who reside in two large adjacent family compounds in the capital. Their ancestors were made Dukes, and as the novel begins the two houses remain among the most illustrious families in the capital. The novel describes the Jias' wealth and influence in great naturalistic detail, and charts the Jias' fall from the height of their prestige, following some thirty main characters and over four hundred minor ones. Eventually the Jia Clan falls into disfavor with the Emperor, and their mansions are raided and confiscated.\r\n\r\nThe story's preface has supernatural Taoist and Buddhist overtones. In the novel's frame story, a sentient Stone, abandoned by the goddess Nüwa when she mended the heavens aeons ago, begs a Taoist priest and a Buddhist monk to bring it with them to see the world. The Stone, along with a companion (while in Cheng-Gao versions they are merged into the same character), was then given a chance to learn from the human existence, and enters the mortal realm.\r\n\r\nThe main character of the novel is the carefree adolescent male heir of the family, Jia Baoyu. He was born with a magical piece of "jade" in his mouth. In this life he has a special bond with his sickly cousin Lin Daiyu, who shares his love of music and poetry. Baoyu, however, is predestined to marry another cousin, Xue Baochai, whose grace and intelligence exemplifies an ideal woman, but with whom he lacks an emotional connection. The romantic rivalry and friendship among the three characters against the backdrop of the family's declining fortunes forms the main story in the novel.', 'http://www.foreignercn.com/index.php?option=com_content&id=698:dream-of-the-red-chamber-&catid=34:chinese-literature&Itemid=117', 7), (8, 'The Hobbit is the story of Bilbo Baggins, a hobbit who lives in Hobbiton. He enjoys a peaceful and pastoral life but his life is interrupted by a surprise visit by the wizard Gandalf. Before Bilbo is really able to improve upon the situation, Gandalf has invited himself to tea and when he arrives, he comes with a company of dwarves led by Thorin. They are embarking on a journey to recover lost treasure that is guarded by the dragon Smaug, at the Lonely Mountain. Gandalf has decided, much to Bilbo's chagrin, that the hobbit will make an excellent addition to the team and Bilbo is going to play the role of a burglar. As they start on their way, Bilbo has serious misgivings.\r\n\r\nOn the journey, Bilbo and the dwarves encounter all sorts of villains and obstacles. They have not traveled very far when they stumble upon a group of trolls. A combination of Gandalf's wizardry and Bilbo's burglary land the group out of harm's way. They are able to replenish their supplies with the food that they find in the trolls' lodging place and they soon move on, pensive and fearful.\r\n\r\nGandalf leads the group to a place where they can get a short rest and they pass through the country of the elves. It is June and all of the joys and songs of midsummer are on display. Bilbo and the dwarves stay at the house of Elrond, an old friend of Gandalf. As they continue on their journey, Bilbo is depressed and nostalgic. The group passes through a mountain chain and two of the dwarves, Fili and Kili, locate a cave for the group to sleep for the night?but the cave turns out to be inhabited by Goblins. Bilbo's startled yell helps to save the group.\r\n\r\n\r\nAnd after these difficulties, there is trouble with giant spiders and with elves before the group actually arrives at Smaug's mountain. In these travels though, Bilbo has outwitted a creature called Gollum, and gained a ring that makes him invisible. This is certainly helpful in battling the Giant Spiders and getting the dwarves out of the Elvenking's prison. But the ring is especially needed when the group arrives at Smaug's mountain. Once there, the dwarves argue about who will enter Smaug's cave and since Bilbo is the burglar, Bilbo must go ahead and face the challenge. He follows the treacherous course into the heart of the cave and though he is sure he is in danger, he is attracted by a red glow that compels him to approach. This is the glow of Smaug. Bilbo manages to steal a cup and hurriedly exits but Smaug awakens and begins to rage. The lake-town of Esgaroth is the victim of Smaug's terror, for the information that he learns from Bilbo gives him reason to believe that they are involved in the theft of his cup. Bard, the grim-voiced, grim-faced man, is willing to challenge Snaug and he has help from a messenger bird, called a thrush. The thrush relays information that Bilbo discovered while in Smaug's lair: the hollow of Smaug's left breast is not plated with his red-gold armor. When Bard strikes this spot, Smaug falls dead, his massive body crushing the city of Esgaroth. The survivors seek Bard as their new king but Bard provisionally declines the offer, though he intends to establish his own city. As the news of Smaug's death spreads, various groups advance towards the mountain?for there is treasure to be had.\r\n\r\nThorin continues to speak of the Arkenstone because it means so much to him, as it is an heirloom and he threatens to take revenge on whoever has prevented him from getting it. In spite of this warning, Bilbo decides that he will leave the mountain and offer the Arkenstone to Bard; then, Bard can offer the Arkenstone to Thorin in exchange for a fair portion of the treasure. Thorin is thinking of ways to avoid a fair bargain and when battle erupts, it includes men, elves and dwarves. They are ready to attack one another until Gandalf announces the approach of the Goblins, bats, wolves and Wargs. The armies re-align themselves and conduct what became known as the "Battle of Five Armies." The Goblins and Wild Wolves battle against the Elves, Men and Dwarves. Gandalf has expected some sort of assault but it did come swifter than he had expected. The Goblins are initially repelled and ambushed by the Elves, but a reinforcement of Goblin troops tilts the balance of the battle. It is only with the final arrival of the Eagles, that the forces of good are sustained. Unfortunately, Bilbo is "smote" with a "stone hurtling from above...and he fell with a crash and knew no more."\r\n\r\nWhen Bilbo regains consciousness, he finds that he is alone and he has to take his ring off so that the individuals who were sent for him can find him. After recovering in the company of Gandalf, Bilbo makes his way back home and their journey?though covering the same perilous terrain?is far more pleasant and mild than it was the first time. As Bilbo says, "So comes snow after fire, and even dragons have their ending." Gandalf and Bilbo pass through Rivendell and eventually make their way to Hobbiton. It is summer and Bilbo is disappointed to learn that he is legally dead. Greedy cousins, the Sackville-Bagginses are auctioning his property because he is "Presumed Dead." They are more than a little displeased at his arrival and it takes several years for Bilbo to sort out the legalisms. In fact, Bilbo had to buy back a good deal of his own furniture?his reputation, for better or worse, was harder to reclaim. But as for Bilbo, son of Belladonna Took, "for ever after he remained an elf-friend, and had the honour of dwarves, wizards, and all such folk as ever passed that way.', 'http://www.gradesaver.com/the-hobbit/study-guide/summary', 8), (9, 'She is the story of Cambridge professor Horace Holly and his ward Leo Vincey, and their journey to a lost kingdom in the African interior. The journey is triggered by a mysterious package left to Leo by his father, to be opened on his 25th birthday; the package contains an ancient shard of pottery and several documents, suggesting an ancient mystery about the Vincey family. Holly and Leo eventually arrive in eastern Africa where they encounter a primitive race of natives and a mysterious white queen, Ayesha, who reigns as the all-powerful "She" or "She-who-must-be-obeyed" and who has a mysterious connection to young Leo. \r\n\r\nThe story expresses numerous racial and evolutionary conceptions of the late Victorians, especially notions of degeneration and racial decline prominent during the fin de siècle. In the figure of She, the novel notably explored themes of female authority and feminine behaviour. It has received praise and criticism alike for its representation of womanhood. ', 'http://www.goodreads.com/book/show/682681.She', 9), (10, 'When the Pevensie children, Peter, Susan, Edmund, and Lucy are sent out of London during World War II, they have no idea of the magical journey they are beginning. In the darkness of the old country house where they are sent, the children stumble through an old wardrobe to the land of Narnia, where animals talk and magic exists. This is the first story of Narnia written by C.S. Lewis and it tells the story of how these four children with the help of Aslan, the Great Lion, help defeat the White Witch who holds Narnia. The first of the children to make their way into Narnia is Lucy, the youngest. There she meets Mr. Tumnus the faun who confesses to her that he is an agent of the White Witch and he is supposed to capture any humans he meets. He explains that the Witch has held Narnia under an enchantment which makes it always winter and never Christmas. The only way the Witch can be defeated is to have four humans sit on the throne at the castle of Cair Paravel. When Lucy returns home, her brothers and sister think she is either lying or crazy, but soon Edmund follows Lucy into the world and meets the White Witch who plies him with Turkish Delight extracting a promise from him that he will bring his siblings to her. Finally, all of the children go through the wardrobe into Narnia. There they go on a journey to rescue Tumnus who has been arrested, find Aslan the Great Lion and defeat the White Witch forever. While a wonderful adventure, the story is also allegorical in nature telling symbolically the story of Christ's sacrifice on the cross. Aslan the Great Lion is a Christ figure who sacrifices his life to save Edmund's. During the journey to find Aslan, Edmund betrays his siblings and goes to join the White Witch becoming her prisoner. After his rescue, the witch approaches Aslan claiming the right to Edmund's life because of his traitorous act. Aslan later goes willingly to the Witch in Edmund's place, letting her kill him. As the girls, Lucy and Susan secretly watch he is shorn of his mane, tied up and killed. As they dispair, he suddenly appears to them alive again and leads them to the aid of Peter's army defeating the Witch forever. The children spend years in Narnia where they grow up to be Kings and Queens having many adventures until one day they are hunting in the woods and find their way back to their own world through the Wardrobe. There they are children again and find that no time at all has passed.', 'http://www.wikisummaries.org/wiki/The_Lion,_The_Witch_and_The_Wardrobe', 10);
-- -- Table structure for table
BookRankingCREATE TABLE IF NOT EXISTS
mostreadbooks.BookRanking(RankingIDint(10) UNSIGNED NOT NULL,RankingScoreint(10) UNSIGNED NOT NULL,BookIDint(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- -- Dumping data for table
BookRankingINSERT INTO
BookRanking(RankingID,RankingScore,BookID) VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8), (9, 9, 9), (10, 10, 10);-- -- Indexes for dumped tables
-- -- Indexes for table
AuthorALTER TABLE
AuthorADD PRIMARY KEY (AuthorID);-- -- Indexes for table
BookALTER TABLE
BookADD PRIMARY KEY (BookID), ADD KEYfk_authorID(AuthorID);-- -- Indexes for table
BookPlotALTER TABLE
BookPlotADD PRIMARY KEY (BookPlotID), ADD KEYfk_bookID2(BookID);-- -- Indexes for table
BookRankingALTER TABLE
BookRankingADD PRIMARY KEY (RankingID), ADD KEYfk_bookID(BookID);-- -- AUTO_INCREMENT for dumped tables
-- -- AUTO_INCREMENT for table
AuthorALTER TABLE
AuthorMODIFYAuthorIDint(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;-- -- AUTO_INCREMENT for table
BookALTER TABLE
BookMODIFYBookIDint(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;-- -- AUTO_INCREMENT for table
BookPlotALTER TABLE
BookPlotMODIFYBookPlotIDint(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;-- -- AUTO_INCREMENT for table
BookRankingALTER TABLE
BookRankingMODIFYRankingIDint(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;-- -- Constraints for dumped tables
-- -- Constraints for table
BookALTER TABLE
BookADD CONSTRAINTfk_authorIDFOREIGN KEY (AuthorID) REFERENCESAuthor(AuthorID) ON DELETE CASCADE ON UPDATE CASCADE;-- -- Constraints for table
BookPlotALTER TABLE
BookPlotADD CONSTRAINTfk_bookID2FOREIGN KEY (BookID) REFERENCESBook(BookID) ON DELETE CASCADE ON UPDATE CASCADE;-- -- Constraints for table
BookRankingALTER TABLE
BookRankingADD CONSTRAINTfk_bookIDFOREIGN KEY (BookID) REFERENCESBook(BookID) ON DELETE CASCADE ON UPDATE CASCADE; COMMIT;SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
- Assistant
The SQL dump you've provided contains the schema and data for a database named
mostreadbooks. This database is designed to store information about the most read books, their authors, plots, and rankings. Below is an overview of the database structure, relationships, and some guidance on how to work with it.
Database Overview
Tables
- Author
- Book
- BookPlot
- BookRanking
1. Author
Stores information about authors.
-
Columns:
AuthorID(INT, Primary Key, Auto Increment)Name(VARCHAR(30), Not Null)Surname(VARCHAR(30), Not Null)Nationality(VARCHAR(30), Not Null)BirthYear(INT, Not Null)DeathYear(INT, Nullable)
-
Sample Data:
AuthorID Name Surname Nationality BirthYear DeathYear 1 Miguel de Cervantes Saavedra Spanish 1547 1616 2 Charles Dickens British 1812 1870 ... ... ... ... ... ...
2. Book
Stores information about books.
-
Columns:
BookID(INT, Primary Key, Auto Increment)BookTitle(VARCHAR(255), Not Null)OriginalTitle(VARCHAR(255))YearofPublication(INT, Not Null)Genre(VARCHAR(30), Not Null)MillionsSold(INT, Not Null)LanguageWritten(VARCHAR(30), Not Null)AuthorID(INT, Foreign Key ReferencesAuthor(AuthorID))
-
Sample Data:
BookID BookTitle YearofPublication Genre MillionsSold LanguageWritten AuthorID 1 Don Quixote 1605 Novel 500 Spanish 1 2 A Tale of Two Cities 1859 Historical Fiction 200 English 2 ... ... ... ... ... ... ...
3. BookPlot
Stores plot summaries of books.
- Columns:
BookPlotID(INT, Primary Key, Auto Increment)Plot(TEXT, Not Null)PlotSource(VARCHAR(255), Not Null)BookID(INT, Foreign Key ReferencesBook(BookID))
4. BookRanking
Stores ranking scores for books.
- Columns:
RankingID(INT, Primary Key, Auto Increment)RankingScore(INT, Not Null)BookID(INT, Foreign Key ReferencesBook(BookID))
Relationships
- Author to Book: One-to-Many
- One author can have multiple books.
- Book to BookPlot: One-to-One
- Each book has one plot summary.
- Book to BookRanking: One-to-One
- Each book has one ranking score.
Importing the Database
To use this database, you need to import the SQL dump into your MySQL or MariaDB server.
Using Command Line
-
Save the SQL Dump:
Save the SQL script you've provided into a file named
mostreadbooks.sql. -
Run the MySQL Import Command:
mysql -u your_username -p < mostreadbooks.sql- Replace
your_usernamewith your MySQL username. - You will be prompted to enter your MySQL password.
- Replace
Using phpMyAdmin
-
Access phpMyAdmin:
Open phpMyAdmin in your web browser.
-
Create a New Database:
- Click on "Databases".
- Enter
mostreadbooksas the database name. - Choose a collation (e.g.,
utf8_general_ci). - Click "Create".
-
Import the SQL Dump:
- Select the
mostreadbooksdatabase from the sidebar. - Click on the "Import" tab.
- Click "Choose File" and select the
mostreadbooks.sqlfile. - Click "Go" to start the import process.
- Select the
Working with the Database
Once the database is imported, you can perform various queries to interact with the data.
Example Queries
1. List All Books with Their Authors
SELECT b.BookTitle, a.Name AS AuthorName, a.Surname AS AuthorSurname FROM Book b JOIN Author a ON b.AuthorID = a.AuthorID;2. Find Books by a Specific Author
SELECT b.BookTitle, b.YearofPublication, b.Genre FROM Book b JOIN Author a ON b.AuthorID = a.AuthorID WHERE a.Name = 'J.K.' AND a.Surname = 'Rowling';3. Get the Plot Summary for a Specific Book
SELECT b.BookTitle, bp.Plot FROM Book b JOIN BookPlot bp ON b.BookID = bp.BookID WHERE b.BookTitle = 'The Lord of the Rings';4. List Books Ranked in the Top 5
SELECT b.BookTitle, br.RankingScore FROM Book b JOIN BookRanking br ON b.BookID = br.BookID WHERE br.RankingScore <= 5 ORDER BY br.RankingScore ASC;5. Find All Books Published After the Year 1950
SELECT b.BookTitle, b.YearofPublication FROM Book b WHERE b.YearofPublication > 1950;6. Count the Number of Books per Genre
SELECT Genre, COUNT(*) AS NumberOfBooks FROM Book GROUP BY Genre;
Understanding the Schema
Creating Tables with Foreign Keys
- The
Booktable has a foreign keyAuthorIDthat references theAuthortable. - The
BookPlotandBookRankingtables have foreign keysBookIDthat reference theBooktable. - The foreign key constraints ensure referential integrity between tables.
Indices and Performance
- Indexes are created on foreign keys to optimize join operations.
- Primary keys are defined on the
IDfields of each table. - The
AUTO_INCREMENTattribute allows automatic generation of unique IDs.
Modifying the Database
Adding a New Author
INSERT INTO Author (Name, Surname, Nationality, BirthYear, DeathYear) VALUES ('Ernest', 'Hemingway', 'American', 1899, 1961);Adding a New Book for an Existing Author
First, find the
AuthorIDfor the author.SELECT AuthorID FROM Author WHERE Name = 'Ernest' AND Surname = 'Hemingway';Assuming the
AuthorIDis 10.INSERT INTO Book (BookTitle, OriginalTitle, YearofPublication, Genre, MillionsSold, LanguageWritten, AuthorID) VALUES ('The Old Man and the Sea', 'The Old Man and the Sea', 1952, 'Novel', 50, 'English', 10);Updating a Book's Ranking
UPDATE BookRanking SET RankingScore = 2 WHERE BookID = ( SELECT BookID FROM Book WHERE BookTitle = 'The Hobbit' );
Troubleshooting Import Errors
-
Foreign Key Constraint Fails:
- Ensure that tables are created before inserting data.
- Check the order of table creation and data insertion.
-
Character Set Issues:
- The database uses
latin1character set. If you encounter encoding issues, consider changing toutf8.
ALTER DATABASE mostreadbooks CHARACTER SET utf8 COLLATE utf8_general_ci; - The database uses
-
Auto-Increment Issues:
- Ensure
AUTO_INCREMENTis set on primary key fields after data import.
- Ensure
Best Practices
- Backup Before Making Changes: Always backup your database before performing insert, update, or delete operations.
- Use Transactions: For operations that affect multiple tables, use transactions to maintain data integrity.
- Validate Data: Ensure data types and constraints are considered when inserting or updating records.
Conclusion
The
mostreadbooksdatabase is a rich resource for managing and querying information about popular books and their authors. Feel free to explore the data and modify the database as needed for your purposes.If you have any specific questions or need further assistance with queries or database management, please let me know!