Student Management System

I want you to design, implement, populate, and produce result sets from a relational database for a Student Management System. This database will store information about students, classes, semesters, instructors, departments for the University of Cincinnati. This database is what the applications pull information from when you register for courses, look up your schedule, and run your degree audit. Think about all of the pieces of information that need to be stored and added and updated while you are adding, dropping, and completing classes.

Requirements

1. You can use the attached .sql file to create the database. However, you are welcome to use your judgement and creativity and alter the .sql file or create your own. In any case, you must show your create/alter statements to create the database. You must have a student management database that you can retrieve information from that you would see when you register for classes, view your schedule, and run a degree audit. This includes

   1. Student Information

   2. Faculty Information

   3. Class Information

   4. College Information

   5. Classes per semester

   6. Semesters

   7. Grades per class

2. You must show your insert statements to insert the data into your database which includes at least the following

   1. 2 distinct students

   2. 2 distinct faculty members

   3. 2 distinct colleges

   4. 4 distinct classes

   5. 2 distinct semesters

   6. 1 class per student complete with a grade in a past semester

   7. 1 class per student currently being taken in a current semester

3. You must show your select statements to retrieve result sets for the following

   1. Classes available in the current semester.

      Class Number, Class Name, Class Description, Section, Semester, Time/day offered,

      Faculty Name teaching, College Name Offering, Credit Hours

   2. Classes each student is currently enrolled.

      Student Name, Student Program, Class Name, Class Section, Faculty Name teaching, Credit hours, Credit Level (grad or undergrad)

   3. Grades for each student for college career.

      Student Name, Student Program, Class (sophomore, junior, grad, etc), Cumulative GPA, Each Class taken with grade and semester taken

4. If you are in the IT6060 course, you must complete research into how to make a stored procedure in your database and create a stored procedure for the following.

   1. I want a stored procedure to accept a student name and if it already exists in the students table, return the student ID and if it doesn’t already exist in the students table, insert the student and return the student ID.

In your Kaltura Video for the final project, you must clearly show and explain your database design, create/alter statements to implement your database, insert statements to add the data, and select statements, result sets, and stored procedure.