Assignment: You will develop a database application for transaction management f

Question

Assignment: You will develop a database application for transaction management for the database you developed in assignment 3. Description: a. Use the SQL schema that you created in assignment 3 for the car rental company. b. Create a JAVA class “CarCompanyQuery.java” to interact with database. c. Run the queries in assignment 3 using this java class and provide sample output. d. Create a java test class CarCompanyQueryTest.java to test your code.

Assignment 3:

CREATE TABLE branches

(

branchID int,

bname varchar(255),

location varchar(255),

PRIMARY KEY (branchID)

);

CREATE TABLE cars

(

carID int,

homebranch varchar(255),

type varchar(255),

rent int,

branchID int,

PRIMARY KEY (carID),

FOREIGN KEY (branchID) REFERENCES branches(branchId)

);

CREATE TABLE customer

(

customerID int,

CName varchar(255),

City varchar(255),

carID int,

PRIMARY KEY (customerID),

FOREIGN KEY (carID) REFERENCES cars(carId)

);

CREATE TABLE carRent

(

rentID int,

datefrom date,

dateto date,

returnbranch varchar(255),

customerID int,

PRIMARY KEY (rentID),

FOREIGN KEY (customerID) REFERENCES customer(customerID)

);

CREATE TABLE telNo

(

customerID int,

office int,

mobile int,

home int,

PRIMARY KEY (customerID),

FOREIGN KEY (customerID) REFERENCES customer(customerID)

);

CREATE TABLE members

(

memberID int,

discount int,

customerID int,

PRIMARY KEY (memberID),

FOREIGN KEY (customerID) REFERENCES customer(customerID)

);

Insertion into tables (populating tables):-

Table branches:-

INSERT into branches values(1, ‘NYbranch’, NY);

INSERT into branches values(2, ‘WDCbranch’, WDC);

INSERT into branches values(3, ‘LAbranch’, LA);

INSERT into branches values(4, ‘chicagobranch’, chicago);

INSERT into branches values(5, ‘texasbranch’, texas);

Table cars:-

INSERT into cars values(1,'texasbranch','subcompact','5000','5');

INSERT into cars values(2,'LAbranch','compact','10000','3');

INSERT into cars values(3,'WDCbranch','Midsized','15000','2');

INSERT into cars values(4,'Chicagobranch','fullsized','20000','4');

INSERT into cars values(5,'NYbranch','luxury','25000','1');

INSERT into cars values(6,'texasbranch','Midsized','5000','5');

INSERT into cars values(7,'LAbranch','luxury','10000','3');

INSERT into cars values(8,'WDCbranch','subcompact','15000','2');

INSERT into cars values(9,'Chicagobranch','compact','20000','4');

INSERT into cars values(10,'NYbranch','compact','25000','1');

Table customer:

INSERT into customer values(1,'Adam','SF','3');

INSERT into customer values(2,'Eve','LA','2');

INSERT into customer values(3,'John','Boston','10');

INSERT into customer values(4,'Peter','Seattle','7');

INSERT into customer values(5,'Michael','Atlanta','8');

INSERT into customer values(6,'Angella','Detroit','9');

INSERT into customer values(7,'Scarlett','Oakland','7');

Table carRent:

INSERT into carRent values(1,'2015-01-01','2015-02-01','NY',3);

INSERT into carRent values(2,'2015-01-10','2015-02-01','WDC',1);

INSERT into carRent values(3,'2015-01-12','2015-02-01','Chicago',3);

INSERT into carRent values(4,'2015-02-01','2015-03-01','LA',4);

INSERT into carRent values(5,'2015-03-07','2015-04-01','NY',2);

INSERT into carRent values(6,'2015-04-25','2015-05-01','WDC',5);

INSERT into carRent values(7,'2015-06-23','2015-07-01','texas',2);

INSERT into carRent values(8,'2015-07-22','2015-08-01','NY',1);

INSERT into carRent values(9,'2015-07-25','2015-08-01','LA',3);

INSERT into carRent values(10,'2015-08-01','2015-09-01','WDC',4);

INSERT into carRent values(11,'2015-09-10','2015-10-01','Chicago',5);

INSERT into carRent values(12,'2015-09-11','2015-10-01','LA',6);

INSERT into carRent values(13,'2015-10-21','2015-11-01','WDC',7);

INSERT into carRent values(14,'2015-11-25','2015-12-01','texas',6);

INSERT into carRent values(15,'2015-12-01','2015-12-10','Chicago',7);

Table telNo:

INSERT into telNo values(1,87644598,78633563,3379353);

INSERT into telNo values(2,90644598,77654583,6579368);

INSERT into telNo values(3,67524598,98654873,8865353);

INSERT into telNo values(4,87644598,90654567,9979378);

INSERT into telNo values(5,85647898,78654123,6545356);

INSERT into telNo values(6,87644546,45654563,1273435);

INSERT into telNo values(7,87644592,78654545,6556367);

Table members:

INSERT into member values(1,90,7);

INSERT into member values(2,10,3);

INSERT into member values(3,05,4);

Explanation / Answer

JAVA class “CarCompanyQuery.java” to interact with database

import java.sql.*;
class CarCompanyQuery{
public static void main(String args[]){
try{

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","Usename","Password");   Statement stmt=con.createStatement();   

ResultSet rs=stmt.executeQuery("select * from branches");

while(rs.next()) {
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
}
con.close();
}
catch(Exception e){
System.out.println(e);
}    
}
}

import java.sql.*;
class CarQuery1{
public static void main(String args[]){
try{

Class.forName("oracle.jdbc.driver.OracleDriver");
  
Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","Usename","Password");   

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery("select * from cars group by type,homebranch");
while(rs.next()) {

int id = rs.getInt("carID");
String branch = rs.getString("homebranch");
String type= rs.getString("type");

//Display values
System.out.print("ID: " + id);
System.out.print(", First: " +branch);
System.out.println(", Last: " +type);

}
con.close();
}
catch(Exception e){
System.out.println(e);
}   
}
}

------------------

Output:

import java.sql.*;
class CarCompanyQueryTest{
public static void main(String args[]){
try{

Class.forName("oracle.jdbc.driver.OracleDriver");
  
Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","Usename","Password");   

Statement stmt=con.createStatement();

ResultSet rs=stmt.executeQuery("SELECT carID, bname FROM cars, branches WHERE branches.bname='chicagobranch' and cars.branchID=branches.branchID and cars.type='compact'");
while(rs.next()) {

int id = rs.getInt("carID");
String branch = rs.getString("BNAME");

//Display values
System.out.print("ID: " + id);
System.out.print(", Bname: " +branch);
}
con.close();
}
catch(Exception e){
System.out.println(e);
}   
}
}

------------------

Output:
CARID   BNAME

CARID HOMEBRANCH TYPE RENT BRANCHID 3 WDCbranch Midsized 15000 2 6 texasbranch Midsized 5000 5 9 Chicagobranch compact 20000 4 2 LAbranch compact 10000 3 10 NYbranch compact 25000 1 4 Chicagobranch fullsized 20000 4 7 LAbranch luxury 10000 3 5 NYbranch luxury 25000 1 1 texasbranch subcompact 5000 5 8 WDCbranch subcompact 15000 2

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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