1. Create a database in MySQL for representing students, departments, and classes. We have the following...

 

Transcribed Image Text:

1. Create a database in MySQL for representing students, departments, and classes. We have the following departments: Bio, Chem, CS, Eng, Math, Phys. Each department has a name, and a campus (Busch, CAC, Livi, CD). Students must have a first name, last name, and 9-digit ID number. The ID is unique among all students. Students may also have one or more majors and one or more minors in some department. A student may be currently taking some number of classes, and may have taken some number of classes. A course a student has taken will have a grade assigned (A, B, C, D, or F). Each class has a name and number of credits (3 or 4). For example, "Introduction to Whale Songs" might be 3 credits. You can create your own class names. Please use these table and field names: ● ● ● Departments (name, campus) Students (first_name, last_name, id) Classes (name, credits) Majors (sid, dname) Minors (sid, dname) ● IsTaking (sid, name) • Has Taken (sid, name, grade) 2. Populate your database with reasonable random data for 100 students. For ex- ample, you should have roughly equal numbers of students in each year (see 3(b) below). The campus for each department can be randomly selected and doesn't have to match reality (e.g., CS could be on CAC, Eng could be on CD, etc.). 3. Create a Java application that allows users to query the database. You should use JDBC to connect to the database. You should support the following queries and operations: (a) Search students by name. This should match any students where the search string is a substring of either the first or last name (case insensitive). (b) Search students by year (Fr, So, Ju, Sr), where a student's year depends on how many credits they've completed: Year Fr So Ju Sr #credits 0 - 29 30 59 60 89 90+ = (c) Search for students with a GPA equal to or above a given threshold. GPA is not stored in a table, but can be calculated based on the set of classes a student has taken. Point values for letter grades are A = 4, B 3, C = 2, D = 1, and F = 0. Then if a student took 5 3-credit classes and got grades of A, B, B, C, and D, and took 3 4-credit classes with grades of B, C, and F, their GPA would be 3(4+3+3+2+1) + 4(3+2+0) 2.19 27 where 27 is the number of credits for the classes taken. (d) Search for students with a GPA equal to or below a given threshold. (e) For a given department, report its number of students and the average of those students' GPAs. (f) For a given class, report the number of students currently taking it. Also, among students who've taken the class, show the number of students who've gotten each letter grade. (g) Execute an abitrary SQL query. You may want to also use the ResultSetMetaData class to help with printing the result. 1. Create a database in MySQL for representing students, departments, and classes. We have the following departments: Bio, Chem, CS, Eng, Math, Phys. Each department has a name, and a campus (Busch, CAC, Livi, CD). Students must have a first name, last name, and 9-digit ID number. The ID is unique among all students. Students may also have one or more majors and one or more minors in some department. A student may be currently taking some number of classes, and may have taken some number of classes. A course a student has taken will have a grade assigned (A, B, C, D, or F). Each class has a name and number of credits (3 or 4). For example, "Introduction to Whale Songs" might be 3 credits. You can create your own class names. Please use these table and field names: ● ● ● Departments (name, campus) Students (first_name, last_name, id) Classes (name, credits) Majors (sid, dname) Minors (sid, dname) ● IsTaking (sid, name) • Has Taken (sid, name, grade) 2. Populate your database with reasonable random data for 100 students. For ex- ample, you should have roughly equal numbers of students in each year (see 3(b) below). The campus for each department can be randomly selected and doesn't have to match reality (e.g., CS could be on CAC, Eng could be on CD, etc.). 3. Create a Java application that allows users to query the database. You should use JDBC to connect to the database. You should support the following queries and operations: (a) Search students by name. This should match any students where the search string is a substring of either the first or last name (case insensitive). (b) Search students by year (Fr, So, Ju, Sr), where a student's year depends on how many credits they've completed: Year Fr So Ju Sr #credits 0 - 29 30 59 60 89 90+ = (c) Search for students with a GPA equal to or above a given threshold. GPA is not stored in a table, but can be calculated based on the set of classes a student has taken. Point values for letter grades are A = 4, B 3, C = 2, D = 1, and F = 0. Then if a student took 5 3-credit classes and got grades of A, B, B, C, and D, and took 3 4-credit classes with grades of B, C, and F, their GPA would be 3(4+3+3+2+1) + 4(3+2+0) 2.19 27 where 27 is the number of credits for the classes taken. (d) Search for students with a GPA equal to or below a given threshold. (e) For a given department, report its number of students and the average of those students' GPAs. (f) For a given class, report the number of students currently taking it. Also, among students who've taken the class, show the number of students who've gotten each letter grade. (g) Execute an abitrary SQL query. You may want to also use the ResultSetMetaData class to help with printing the result.

Answer rating: 100% (QA)

Answer The code is as follow import javasql import javautilScanner public class StudentSearchApp ... View the full answer