CSIS 2300 SQL Queries and Report Design

Learning Objectives

• Demonstrate ability to apply structured query language (SQL) statements from the basic commands to more advanced commands.
• Demonstrate ability to apply simple report design techniques using MSAccess to present query results for users.

Overview ofAssignment

In this assignment, you are provided a database that tracks adult literacy program. Tutors complete a certification class provided by the agency. Students complete an assessment interview that results in a report for the tutor and a recorded read score. When matched with a student, a tutor meets with the student for one to fours hours per week. Some students work with the same tutor for years, some for less than a month. Other students change tutors if their learning style does not match the tutors tutoring style. Many tutors are retired and are available to tutor only part of the year. Tutor status is record asActive, Temp Stop, or Dropped. The ERD for this database is shown in the figure below. see image.

Instructions

Create a folder for this assignment and name it as Yourusername_CSIS2300_AS4. For example, in my case, the name of the project would be: Otims_CSIS2300_AS4.All the files you create for this assignment should be placed in this folder

Part 1: Database Implementation

You are provided a SQL dump file forAssignment 4. Open PHPMyAdmin and import the provided SQL script to create the database.

Part 2: Database Manipulation

Please implement the following queries (hint: Most of these queries will require using nested or subqueries).

1. Write a query to display StudentID, first name, last name, number of hours tutored, and number of lessons completed for students who are active.

2. Write a query to display TutorID, first name, and last name of tutors who are currently ` tutoring more than one student.

3. Write a query to display TutorID, first name, and last name of tutors who have not yet tutored someone.

4. Write a query to display StudentID, first name, last name, and read score of students who were ever taught by tutors whose status is Dropped.

5. Write a query to display StudentID, first name, and last name of students whose read score is greater than the average read score ofALL the students.

6. Write a query to display TutorID, first name, and last name of tutors that are available to tutor, assuming a tutor is available only if currently unassigned a student.

7. Write a query to display TutorID, first name, and last name of tutors that need to be reminded to turn in reports.

Part 3: Report Design Using Access

Using Views (i.e., virtual tables) you created for queries in Part 2 above, design a report for each query using MicrosoftAccess. To do so, please integrate MicrosoftAccess with MySQL. To integrateAccess with MySQL, use to driver uploaded to Blackboard for the assignment to create an ODBC connection. For further information on how to create an ODBC connection for MySQL database, please refer to the following link: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-linked- 3 tables.html

Please create reports using Report Wizard feature ofAccess (i.e., you do not need to create reports from scratch using report Designer). However, you can always toggle to Design View to modify/format your report.After you create each report, please export it as a PDF file with appropriate (i.e., descriptive name). In order to be able to export the report, you need to toggle to Print Preview.