Design a set of database tables to store library books and patrons. A book has a

Question

Design a set of database tables to store library books and patrons. A book has an ISBN (International Standard Book Number), an author, and a title. The library may have multiple copies of each book, each with a different book ID. A patron has a name, a unique ID, and an address. A book may be checked out by at most one patron, but one patron can check out multiple books.

Write a Java program that creates a library database of books and patron data as described above. Patrons should be able to check out and return books. Supply commands to print the books that a patron has checked out and to find who has checked out a particular book. You may create and populate Patron and Book tables before running the program.

Database Set up Connection Established DBC Driver com.mysqljdbc Driver Database URL jdbc:mysql://localhost/bookslibrary Username scott Password tiger Connect Create Tables close

Explanation / Answer

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statment;

public class Database {

public static void main (String args[]) throws Exception

{ //step 1 load the driver cclass class

class.forName ("com.mysql.jdbc.Driver"); // throws CNF Exception

// step2 create the connection object

Connection con = DriverManager. getConnection (" "+" jdbc:mysql://localhost/bookslibrary","scott","tiger");

// step3 create the Statment object

Statement stmt = con.createStatement();

//step4 execute qquery

string s= " create table library (ISBN int primary key,"+"Author varchar(20), title varchar(40), no. Of books int, Book IDs in int, Books IDs out int)";

stmt.executedUpdate(s);

system.out.println(" library table created");

string s1= "create table patron(User ID: int primary key"+"Name varchar(30), Address varchar(100)")";

stmt.executedUpdate(s1);

System.out.println("Patron table created");

string s2= "create table checking_details(User ID int, Book ID int, CheckOut Book int, Return Book int, CheckRental by User ID int, CheckRental by Book ID int)";

stmt.executedUpdate(s2);

System.out.println("Checking_Details table created");

//step5 insert value into tables

string vsql=" insert into library values (123455, 'John Smith', 'How to program in java', 10,6,4);

string vsql1= "inset into library(234556, 'Yen Zen', 'everything about J2EE', 10,3,7);

stmt.executedUpdate(vsql);

stmt.executedUpdate(vsql1);

string vsql2= "insert into patron values(M121,'Michael', 'Hno. 121 street zipzap');

string vsql3= "insert into patron values(S566,'Sammy', 'Hno. 678 street zoom);

stmt.executedUpdate(vsql2);

stmt.executedUpdate(vsql3);

System.out.println(" tables updated");

//step6 close the connection oobject

con.close();

}

}

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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