1. Save these files into your project work directory:
   Student.txt   CoursesTaken.txt   Courses.txt   Programs.txt     Requirements.txt   Prerequisites.txt
   (These files have been tweaked to ensure one tab between each column for mysql to load into the tables correctly.)

   graddb.sql
   (This is the mysql script file that creates the database, tables and loads the data into the tables.)

2. At Command Prompt of work directory
      C:\CSIS626>mysql < graddb.sql

3. This will have created a database named GRAD_DEGREE and the following tables: Students, CoursesTaken, Courses, Programs, Requirements and Prerequisites.

Required Queries

1. Retrieving the password and degree of a particular student
   • mysql>SELECT password, degree FROM Students WHERE stud_id='0804';

2. Retrieving courses taken by a particular student sorted by course number (ascending)
   • mysql>SELECT course_num, semester, year, grade FROM CoursesTaken WHERE stud_id='9923' ORDER BY course_num ASC;

3. Retrieving courses taken by a particular student sorted by date taken (ascending)
   • mysql>SELECT course_num, semester, year, grade FROM CoursesTaken WHERE stud_id='9923' ORDER BY year ASC;

4. Retrieving required courses for program a particular student is in
   • mysql>SELECT R.course_num, R.alt_req, C.title FROM Requirements R, Students S, Courses C WHERE S.stud_id='0804' AND R.degree=S.degree AND R.type='R' AND R.course_num=C.course_num;
     OR
   • mysql>SELECT R.course_num, R.alt_req, C.title FROM Requirements R, Courses C WHERE R.degree='MS' AND R.type='R' AND R.course_num=C.course_num;
     (This matches the required course with it's title.)

   • mysql>SELECT title FROM Courses WHERE course_num='CSIS 531'
     (This is to match the alternate requirement with it's title.)

5. Retrieving optional courses for program a particular student is in
   • mysql>SELECT R.course_num, R.alt_req, C.title FROM Requirements R, Students S, Courses C WHERE S.stud_id='0804' AND R.degree=S.degree AND R.type='O' AND R.course_num=C.course_num;
     OR
   • mysql>SELECT R.course_num, R.alt_req, C.title FROM Requirements R, Courses C WHERE degree='MS' AND type='O' AND R.course_num=C.course_num;

6. Retrieving number of optional courses required for program a particular student is in
   • mysql>SELECT P.num_opt FROM Programs P, Students S WHERE S.stud_id='0804' AND P.degree=S.degree;
     OR
   • mysql>SELECT num_opt FROM Programs WHERE degree='MS';

7. Retrieving required courses not taken by a particular student
   • mysql>SELECT R.course_num, R.alt_req FROM Requirements R LEFT JOIN CoursesTaken C ON R.course_num=C.course_num AND C.stud_id='9923' WHERE C.semester IS NULL AND R.degree='MS' AND R.type='R';

8. Retrieving optional courses not taken by a particular student
   • mysql>SELECT R.course_num, R.alt_req FROM Requirements R LEFT JOIN CoursesTaken C ON R.course_num=C.course_num AND C.stud_id='9923' WHERE C.semester IS NULL AND R.degree='MS' AND R.type='O';

9. Retrieving number of optional course taken by a particular student
   • mysql>SELECT COUNT(*) FROM CoursesTaken C, Requirements R WHERE C.stud_id='9923' AND C.course_num=R.course_num AND R.type='O' AND R.degree='MS';

10. Retrieving prerequisites for a particular course
    • mysql>SELECT course_num, prereq, alt_prereq FROM Prerequisites WHERE course_num='CSIS 520T';

11. Retrieving planned courses for a particular student
    • mysql>SELECT course_num, semester, year FROM CoursesPlanned WHERE stud_id='0804' ORDER BY course_num ASC;