C). 12345 Consider the schemas of the following relational database for a university. It has four tables, contains dataabout students, courses, department, and enrollment information:Student (studentID, firstName, lastName, gender, majorDept, class, GPA)Department (deptId, deptName, college)Course (courseID, courseName)Enrollment (studentID, courseID)FK – PK RELATIONSHIPS:Student (majorDept) →Department (deptID)Enrollment (studentID) → Student (studentID)Enrollment (courseID) → Course (courselD)Domain:gender = {'F', 'M'}class = {'Freshman', "Sophomore', 'Junior", "Senior'}GPA has value between 0.00 to 4.00 (values outside the range are invalid)A student belongs to one department only. If a student's major is undecided, then majorDept =NULL.A student can enroll many courses and a course is enrolled by many students.(A)TABLE CREATIONCreate the above 4 tables using ACCESS. Use the appropriate data type (and length) of eachattribute. Construct the FK to PK relationships between tables.(B)DATA INSERTIONInsert at least 10 tuples into the Student table, at least 5 tuples into the Department table, at least8 tuples into the Course table, and 30 tuples into the Enrollment table. You can add more tuplesif you want. Use your imagination to make up varieties of data for the attributes. (C)QUERY EXECUTIONDesign and run the following queries. Save each query as Query1, Query2, .., Query5.NOTE: You must write your SQL using the syntax you learned in class and in your textbook(lecture notes). If you generate your SQL from QBE, you will get zero point. For example, ifyou use INNER JOIN for JOIN operation, you will get zero point.Please use your last name as your file name and submit (upload) your .accdb file toBlackboard (single submission only). Submission via email will be ignored. Please check yoursyllabus for late assignment policy.(1) List the name of students (firstName and lastName), their major (deptName), and theirclass who have a GPA of 3.0 or better (higher).(2) Display a table of enrollment information. Your output table consists of studentID, studentname (firstName and lastName), and course name (courseName).(3) Find the average GPA of all the students in each class. That is, the average GPA forFreshman, the average GPA for Sophomore, the average GPA for Junior, and the averageGPA for Senior. Your resulting table consists of 2 columns: Class and Average GPA.(4) Count the total number of students in this university. Your query should display a table witha column heading called “Total_Number_of_Students".(5) Display a table consists of studentID, student name (firstName and lastName), gender,major department name (deptName), and GPA. The table should be sorted by student's GPAin descending order.

Question

C). 12345 Consider the schemas of the following relational database for a university. It has four tables, contains dataabout students, courses, department, and enrollment information:Student (studentID, firstName, lastName, gender, majorDept, class, GPA)Department (deptId, deptName, college)Course (courseID, courseName)Enrollment (studentID, courseID)FK – PK RELATIONSHIPS:Student (majorDept) →Department (deptID)Enrollment (studentID) → Student (studentID)Enrollment (courseID) → Course (courselD)Domain:gender = {'F', 'M'}class = {'Freshman', &#34;Sophomore', 'Junior&#34;, &#34;Senior'}GPA has value between 0.00 to 4.00 (values outside the range are invalid)A student belongs to one department only. If a student's major is undecided, then majorDept =NULL.A student can enroll many courses and a course is enrolled by many students.(A)TABLE CREATIONCreate the above 4 tables using ACCESS. Use the appropriate data type (and length) of eachattribute. Construct the FK to PK relationships between tables.(B)DATA INSERTIONInsert at least 10 tuples into the Student table, at least 5 tuples into the Department table, at least8 tuples into the Course table, and 30 tuples into the Enrollment table. You can add more tuplesif you want. Use your imagination to make up varieties of data for the attributes. (C)QUERY EXECUTIONDesign and run the following queries. Save each query as Query1, Query2, .., Query5.NOTE: You must write your SQL using the syntax you learned in class and in your textbook(lecture notes). If you generate your SQL from QBE, you will get zero point. For example, ifyou use INNER JOIN for JOIN operation, you will get zero point.Please use your last name as your file name and submit (upload) your .accdb file toBlackboard (single submission only). Submission via email will be ignored. Please check yoursyllabus for late assignment policy.(1) List the name of students (firstName and lastName), their major (deptName), and theirclass who have a GPA of 3.0 or better (higher).(2) Display a table of enrollment information. Your output table consists of studentID, studentname (firstName and lastName), and course name (courseName).(3) Find the average GPA of all the students in each class. That is, the average GPA forFreshman, the average GPA for Sophomore, the average GPA for Junior, and the averageGPA for Senior. Your resulting table consists of 2 columns: Class and Average GPA.(4) Count the total number of students in this university. Your query should display a table witha column heading called “Total_Number_of_Students&#34;.(5) Display a table consists of studentID, student name (firstName and lastName), gender,major department name (deptName), and GPA. The table should be sorted by student's GPAin descending order.

Accepted Answer

I have answered C no. question as per the question.