University DB

Consider the schemas of the following relational database for a university. It has four tables, contains data about 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 (courseID)

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 CREATION

Create the above 4 tables using ACCESS. Use the appropriate data type (and length) of each attribute. Construct the FK to PK relationships between tables.

(B) DATA INSERTION

Insert at least 10 tuples into the Student table, at least 5 tuples into the Department table, at least 8 tuples into the Course table, and 30 tuples into the Enrollment table. You can add more tuples if you want. Use your imagination to make up varieties of data for the attributes.

(C) QUERY EXECUTION

Design 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, if you 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 to Blackboard (single submission only). Submission via email will be ignored. Please check your syllabus for late assignment policy.

(1) List the name of students (firstName and lastName), their major (deptName), and their class who have a GPA of 3.0 or better (higher).

(2) Display a table of enrollment information. Your output table consists of studentID, student name (firstName and lastName), and course name (courseName).

(3) Find the average GPA of all the students in each class. That is, the average GPA for Freshman, the average GPA for Sophomore, the average GPA for Junior, and the average GPA 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 with a 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 GPA in descending order.