Tech Assignment 3

Assignment #3

Import the courses DATA SET Excel file into Access Database following the same steps you used to import the employee DATA SET file. Importing of the courses DATA SET file will create four tables in your Access database called Courses_t, Courses_Taken_t, Professor_Information_t, and Student_Information_t. These tables will be automatically populated with data from the courses DATA SET Excel file as part of the import process. You should modify the meta data of the table so that the data types and primary keys of the tables are as follows:

Courses_t

CourseID nvarchar(50)

[Course Name] nvarchar(100)

[Course Description] nvarchar(MAX)

Department nvarchar(50)

PRIMARY KEY CourseID

Courses_Taken_t

StudentID nvarchar(50)

CourseID nvarchar(50)

[Semester End Date] date

[Professor SSN] nvarchar(50)

[Percent Score] int

GPA real

Department nvarchar(50)

PRIMARY KEY (StudentID, CourseID, [Semester End Date])

Professor_Information_t

SSN nvarchar(50)

Name nvarchar(50)

Address nvarchar(MAX)

[Contact Number] nvarchar(50)

[Date Joined USD] date

PRIMARY KEY SSN

Student_Information_t

StudentID nvarchar(50)

Name nvarchar(50)

Address nvarchar(MAX)

Major nvarchar(50)

Minor nvarchar(50)

[Undergraduate Standing] nvarchar(50)

[Expected Graduation Date] date

PRIMARY KEY StudentID

1. The average GPA of all the courses that the student Victoria Hanson has taken from the Management department. The query should return student name, department, and the average GPA.

2. The average GPA of all the courses that the student Amy Earhart has taken in her major. The query should return student name, major, average GPA of all courses that she has taken in her major.

3. Student name, student’s minor, Course name, department, percent score, and GPA for the course where the student Sonia Hatfield got the lowest percent score of all the courses she took in her minor. If there is a tie, then the query should return the required information for all courses in her minor which are tied for the lowest percent score.

4. Student name, Major, Course name, department, GPA, professor name, and professor contact number of all courses taken in the student’s major where the student Bill Reilly got a 4.0.

5. The department and the “departmental average GPA” for the student John Halber where the departmental average GPA is the lowest among all the departmental average GPAs. The departmental average GPA for a given student is defined as the average GPA of all the courses the student took from that department. The final query should return the student name, department name and the departmental average GPA for the department where the student John Halber got the lowest departmental average GPA.

Submission Format: The submission format for this assignment is a Microsoft Word document and Access database. Copy and paste both the query and the result set for each question into your Word document. Submit two files (MS-Word and MS-Access) on the blackboard.