Honors 1000, Productive Data Manipulation in Python and SQL (Fall 2018)
Assignment 1 - MySQL Database (Due: Sunday, September 9, 2018)
Consider the faculty list of the Department of Computer Science found at
https://www.cs.gsu.edu/role/faculty/
https://www.cs.gsu.edu/role/faculty-visiting-adjunct/
and the Fall 2018 course schedule found at
https://www.cs.gsu.edu/course-descriptions-schedule/
This assignment involves creating a MySQL database with two tables described below:
teacher: this table records information about faculty with the following columns: firstname lastname rank course: this table records undergraduate courses offered in Fall 2018 with the following columns: crn - numeric 5-digit course record number cprefix cno - numeric title days starttime - numeric tfname tlnameSome remarks about the data:
- The ranks for the teachers should be one of the following: Professor, Associate Professor, Assistant Professor, Lecturer, Instructor, Part Time Instructor, and Graduate Teaching Assistant. Note: Any name not found in the faculty lists can be assumed to be Graduate Teaching Assistants.
- For courses with labs, we will record information only about the lecture.
- cprefix would correspond to "Subj" column, cno would correspond to "Crse" column, days would be in all CAPS (e.g. "MW"), and starttime would be a number representing the military time (e.g. 05:30 pm would be represented as 1730).
- Undergraduate courses would correspond to the cno values less than 4900, with the exception of 4980.
- Populate the database with data using MySQL insert statements, and
- Write SQL queries (select statements) to answer the following:
- Retrieve all 3000-level classes
- Retrieve all "Lecturer" faculty
- Retrieve cprefix, cno, and title of courses taught by Graduate Teaching Assistants.
- Retrieve cno, and title of 4000-level courses taught by Part Time Instructors.
- Retrieve firstname, lastname, and ranks of faculty who teach a course with the word "Data" appearing as a substring in the title of the class.