-Person (Name, ID, Address, DOB - Instructor (InstructorID, Rank, Salary) - Stud
ID: 3794649 • Letter: #
Question
-Person (Name, ID, Address, DOB
- Instructor (InstructorID, Rank, Salary)
- Student (StudentID, Classification, GPA, MentorID, CreditHours)
-Course (CourseCode, CourseName, PreReq)
-Offering (CourseCode, SectionNo, InstructorID)
- Enrollment (CourseCode, SectionNo, StudentID, Grade)
Write a JDBC program P3.java. For each student, update the credit hours, classification, and the GPA, taking into account the current GPA and grades in the courses the student is currently enrolled in. All the courses are 3 credit courses.
The classification should be updated according to the following criteria:
Freshman: 0-29; Sophomore: 30-59; Junior: 60-89; and Senior: 90 and higher.
The grades are usual letter grades and the points assigned to the grade are as follows:
A = 4.00; A- = 3.66; B+ = 3.33; B = 3.00; B- = 2.66; C+ = 2.33; C = 2.00; C- = 1.66; D+ = 1.33; D = 1.00; F = 0.00
As an example, suppose that the current GPA of a student is 3.5, she is a freshman, has taken 27 credit hours, currently she is enrolled in two courses, and has A and B+ in those courses. Then the new credit hours is 27 + 3 + 3 = 33. Her new classification is Sophomore. The new GPA is ((3.5 * 27) + (3 * 4.00) + (3 * 3.33)) /33 = 3.53. You should calculate and store GPA as a float. Note that rounding of GPA must be done only for printing
Explanation / Answer
jdbc.java
//Import required packages
import java.sql.*;
public class jdbc {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
// Database credentials
static final String USERNAME = "username";
static final String PASSWORD = "password";
/*
* Function which returns point value based on the grades
*/
public static float getGradeValue(String c) {
if (c.equalsIgnoreCase("A")) {
return (float) 4.0;
} else if (c.equalsIgnoreCase("A-")) {
return (float) 3.66;
} else if (c.equalsIgnoreCase("B+")) {
return (float) 3.33;
} else if (c.equalsIgnoreCase("B")) {
return (float) 3.00;
} else if (c.equalsIgnoreCase("B-")) {
return (float) 2.66;
} else if (c.equalsIgnoreCase("C+")) {
return (float) 2.33;
} else if (c.equalsIgnoreCase("C")) {
return (float) 2.00;
} else if (c.equalsIgnoreCase("C-")) {
return (float) 1.66;
} else if (c.equalsIgnoreCase("D+")) {
return (float) 1.33;
} else if (c.equalsIgnoreCase("D")) {
return (float) 1.00;
} else {
return 0;
}
}
/*
* Function which returns classification value based on the total credits of student
*/
public static String getClassification(int creditHour) {
if (creditHour < 30) {
return "Freshman";
} else if (creditHour < 60) {
return "Sophomore";
} else if (creditHour < 90) {
return "Junior";
} else {
return "Senior";
}
}
/**
* Driver program. Please update the database setting such as driver, username password etc.
* @param args
*/
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
try {
// Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// Open a connection
System.out.println("Connecting to a selected database...");
connection = DriverManager
.getConnection(DB_URL, USERNAME, PASSWORD);
System.out.println("Connected database successfully...");
// Execute a query
System.out.println("Creating statement...");
statement = connection.createStatement();
String sql = "SELECT StudentID, Classification, GPA, CreditHours FROM Student";
ResultSet outerResultSet = statement.executeQuery(sql);
// Extract data from result set.. executing once for each student
while (outerResultSet.next()) {
// Retrieve Each Student record's individual columns
int studentId = outerResultSet.getInt("StudentID");
String classification = outerResultSet.getString("Classification");
float gpa = outerResultSet.getFloat("GPA");
int creditHour = outerResultSet.getInt("CreditHours");
// Display values before updating
System.out.println(" Before updating:");
System.out.print("Student Id: " + studentId);
System.out.print(", GPA: " + gpa);
System.out.print(", Classification: " + classification);
System.out.println(", CreditHour: " + creditHour);
// select enrollment record for this particualr student
sql = "SELECT StudentID, Grade FROM Enrollment where StudentID="
+ studentId;
ResultSet innerResultSet = statement.executeQuery(sql);
int totalCreditHours = creditHour;
float gpaSum = gpa * creditHour;
while (innerResultSet.next()) {
String grade = innerResultSet.getString("Grade");
totalCreditHours += 3;
gpaSum += getGradeValue(grade) * 3;
}
// calculate final gpa by dividing the sum by total credits
float gpaToBeUpdated = gpaSum / totalCreditHours;
// update the new values for this student into the database
sql = "UPDATE Student " +
"SET Classification = '" + getClassification(totalCreditHours) +
"', GPA = " + gpaToBeUpdated +
", CreditHours = " + totalCreditHours +
"WHERE StudentID=" + studentId;
statement.executeUpdate(sql);
// again select
sql = "SELECT StudentID, Classification, GPA, CreditHours FROM Student where StudentID="
+ studentId;
innerResultSet = statement.executeQuery(sql);
// Retrieve by column name
studentId = innerResultSet.getInt("StudentID");
classification = innerResultSet.getString("Classification");
gpa = innerResultSet.getFloat("GPA");
creditHour = innerResultSet.getInt("CreditHours");
System.out.println(" After update:");
System.out.print("Student Id: " + studentId);
System.out.print(", GPA: " + gpa);
System.out.print(", Classification: " + classification);
System.out.println(", CreditHour: " + creditHour);
System.out
.println("****************************************************************");
}
outerResultSet.close();
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (statement != null)
connection.close();
} catch (SQLException se) {
}// do nothing
try {
if (connection != null)
connection.close();
} catch (SQLException se) {
se.printStackTrace();
}// end finally try
}// end try
System.out.println("Finishing program!");
}
}
I have given you the code but could not test it as i dont have a working environment for now. So, to test the code, you need to change the database connection, username and password. Also, you must ensure that the required table for stdent and enrollment are exising in the database along with the values in corresponding tables.
Let me know in case of any concren.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.