USA: +1 (725) 712-1730 
Sign in
Based on the following Sql script in Music Library data base, you need to write the following.
1. documented source code, (back end code in Java to connect to the user interface with sql) :- and based given following data SQL Script Music Liabrary;
2. A well written readme which explains how to install/run the code.
3. test cases.
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS MusicGroup;
DROP TABLE IF EXISTS ReleaseAlbum;
DROP TABLE IF EXISTS Employee;
/*CRUD action (Create, Retrieve, Update, Delete)*/
/* Customer Table */
CREATE TABLE Customer (
Membership_Id int,
Name varchar(255),
Phone_Number int,
Address varchar(255),
Email varchar(255),
product_rented varchar(45)
product_purchesed varchar(45)
);
/*Artist Table or products */
CREATE TABLE Artist (
firstName VARCHAR(30)
, lastName VARCHAR(30)
, dateOfBirth VARCHAR(30)
, genre VARCHAR(30)
, address VARCHAR(30)
, city VARCHAR(20)
, state VARCHAR(20)
, phoneNumber BIGINT
, email VARCHAR(50)
, PRIMARY KEY(firstName, lastName, dateOfBirth)
);
/*Create*/
/*MusicGroup Table*/
CREATE TABLE MusicGroup (
groupName VARCHAR(30)
, groupGenre VARCHAR(30)
, numberOfMembers INT
, groupAddress VARCHAR(30)
, city VARCHAR(20)
, state VARCHAR(20)
, phoneNumber BIGINT
, email VARCHAR(50)
, memberFirstName VARCHAR(30)
, memberLastName VARCHAR(30)
, memberDateOfBirth VARCHAR(30)
, memberRole VARCHAR(30)
, memberId INT
, PRIMARY KEY AUTO_INCREMENT(groupName, memberId)
, FOREIGN KEY(memberFirstName, memberLastName, memberDateOfBirth)
REFERENCES Artist(firstName, lastName, dateOfBirth)
);
/*ReleaseAlbum Table*/
CREATE TABLE ReleaseAlbum (
title VARCHAR(30) PRIMARY KEY
, musicLength VARCHAR(30)
, releaseYear VARCHAR(10)
, playSequence VARCHAR(10)
, copiesSold BIGINT
, artistFirstName VARCHAR(30)
, artistLastName VARCHAR(30)
, groupName VARCHAR(30)
, albumGenre VARCHAR(30)
, FOREIGN KEY(artistFirstName, artistLastName) REFERENCES Artist(firstName, lastName)
, FOREIGN KEY(groupName) REFERENCES MusicGroup(groupName)
);
CREATE TABLE Employee(
EmployeeId INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(1),
ManagerId INT,
EmailAddress VARCHAR(50));
Expert Answer
We just have to follow simple steps to execute the query, import the required packages and then registering the JDBC driver. In case you are wondering what is JDBC, it works with Java to get access of any kind of RDBMS or tabular data. So once we register the JDBC, we open a connection the database server and execute our query.
Here is the code to connect :
import java.sql.*; //Importing the packages required
public class JDBCExample {
static final String JDBC_DRIVER = “com.mysql.jdbc.Driver”;
static final String DB_URL = “jdbc:mysql://localhost/”;
static final String USER = “root”; //put database username and password
static final String PASS = “root”;
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(“com.mysql.jdbc.Driver”); //registering the JDBC
conn = DriverManager.getConnection(DB_URL, USER, PASS); //opening a connection
stmt = conn.createStatement(); //create a statement
String sql = “DROP TABLE IF EXISTS Customer”; //Put in your query
stmt.executeUpdate(sql); //execute the query
sql = “CREATE TABLE Customer” +
“Membership_Id int,” +
“Name varchar(255),” +
“Phone_Number int,” +
“Address varchar(255),” +
“Email varchar(255),” +
“product_rented varchar(45)” +
“product_purchesed varchar(45))”; //put in the next query
stmt.executeUpdate(sql); //exqthe query
}catch(SQLException se){ //rest all is just catching exceptions
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{ //this is closing the connection
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
}}
I am assuming that you are familiar with java, it not then install jdk and create a .java file put the code in it and run. If you still have any doubts, let me know in the comments.
