1. Insert data in the database as it appears in the above tables. 2. For each bo

Question

1. Insert data in the database as it appears in the above tables.

2. For each book, retrieve the Title, Author (only 1 author in case there are more), and Publisher.

3. Change the book with title “Development as Freedom” to “The Lost Tribe” as the new title.

4. List the titles of all books that are co-authored? c) Export the database, together with the PDF document, and upload them together as zipped file to blackboard.

JBDC and MySQL Homework Given a database schema for a library management system for Texas Tech as the following picture BOOK Bookld Title PublisherName K AUTHORS BOO Authrold Bookld. AuthorName a). Create a database that corresponds to the above schema with tables that include; BOOK, and BOOK AUTHORS b) Write a java console program that connects to the database using JBDC and performs the following queries. A screenshot of the results and the corresponding query statement should be put in a PDF document and submitted via blackboard. Bookld Title PublisherName The Three Musketeers Dorrance Publishing Co. Inc D2 Identity and Violence The illusion of Destiny Hachette Book Group 3 The Argumentative Indian HarperCollins Publishers R4 Development as Freedom Macmillan Publishers S River of Smoke Hachette Book Group Authorld Bookld AuthroName 1 1 Alexander Dumas 2 Amartya Sen Amartya Sen Amartya Sen Amitav Ghose 2 Alexander Dumas

Explanation / Answer

Creating Tables:

CREATE TABLE BOOK (BookID int not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Title varchar(100), PublisherName varchar(100))

CREATE TABLE BOOK_AUTHORS (AuthorID int not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BookID int, AuthorName varchar(500))

Inserting values into Database

Book Table

insert into Book(Title, Publishername) values('The Three Musketeers','Dorrance Publishing Co.Inc');
insert into Book(Title, Publishername) values('Identity and Violence: The Illusion of Destiny','Hachatte Book Group');
insert into Book(Title, Publishername) values('The Argumentative Indian','HarperCollins Publishers');
insert into Book(Title, Publishername) values('Development as Freedom','Macmillan publishers');
insert into Book(Title, Publishername) values('River of Smoke','Hachatte Book Group');

BOOK_AUTHORS Table

insert into BOOK_AUTHORS (BookID, AuthorName) values(1, 'Alexander Dumas')
insert into BOOK_AUTHORS (BookID, AuthorName) values(2, 'Amartya Sen')
insert into BOOK_AUTHORS (BookID, AuthorName) values(3, 'Amartya Sen')
insert into BOOK_AUTHORS (BookID, AuthorName) values(4, 'Amartya Sen')
insert into BOOK_AUTHORS (BookID, AuthorName) values(5, 'Amitav Ghose')
insert into BOOK_AUTHORS (BookID, AuthorName) values(2, 'Alexander Dumas')

List of the titles  of Books that are co authored

select distinct(book.Title) from BOOK book, BOOK_AUTHORS authors where book.BOOKID= (SELECT bookid from Book_authors group by bookID having (count(bookID)>1))

Query to update Title

UPDATE Book SET Title = 'The Lost Tribe'WHERE Title = 'Development as Freedom';

Java code for JDBC Connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestConnection {

   public static void main(String[] args) {
       createConnection();
   }
   private static void createConnection() {
       try {
           String url = "jdbc:mysql://localhost:3306/database";
           Class.forName("com.mysql.jdbc.Driver");
           Connection conn = DriverManager.getConnection(url, "root","root");
          
           Statement stmt = conn.createStatement();

           // use this for creating tables if not created.
           String createString1 = "CREATE TABLE BOOK (BookID int not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Title varchar(100), PublisherName varchar(100))";
           String createString2 = "CREATE TABLE BOOK_AUTHORS (AuthorID int not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BookID int, AuthorName varchar(500))";
          
           //System.out.println(stmt.executeUpdate(createString2));//uncomment this to create tables

           // Use to update values in MOVIE table
           PreparedStatement psInsert=null;
           psInsert = conn.prepareStatement("insert into Book(Title,PublisherName) values (?,?)");
           psInsert.setString(1, "River of Smoke");
           psInsert.setString(2, "Hachatte Book Group");
           //psInsert.executeUpdate();
           ResultSet rs = null;
           rs = stmt.executeQuery("select * from BOOK");
           while (rs.next()) {
               System.out.println(" " + rs.getString(2) + ".... " + rs.getString(3) + " ");
           }
          

          
       } catch (Exception except) {
           except.printStackTrace();
       }
   }
}

Query to Retrieve title-- Need Modification to list only 1 author---will work on it and provide you if mandatory

select book.Title, authors.AuthorName, book.Publishername from Book book, Book_Authors authors where book.BookID=authors.BookID

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.