Group work:

Both individual and group work are allowed in this project. Each group can include at most 3 students. All the names of group members should be indicated in the project design report.

Design a database system for the university, which contains the information of students (identified by student ID) and courses (identified by course ID). Students also a have student names. Courses have a course name and the number of credits. Students enroll courses.

Task 1: Database Design (10 points)

Create a design.pdf file which includes the following information:

Provide your relational schema definitions in text form, including the attributes for each relation and the types of all the attributes. Make sure to clearly indicate your chosen keys (including primary and foreign). You are allowed flexibility on the exact attribute types you use for your schema, as long as they reasonably match the specification above (e.g., in terms of number types, string types).

Provide an Entity-Relation (ER) diagram that describes your schemas.

Task 2: Database Application (40 points)

Application Description:

The Course Management System is designed to help educational institutions manage student enrollments in various courses. It provides a user-friendly web interface for students to browse available courses, enroll in them, and for administrators to manage course offerings and student enrollments. The system will be built using Python with the Flask web framework and MySQL as the database management system.

Simple version: (Colab notebook templateLinks to an external site.)

Requirements:

The application must have a command-line interface menu that allows the user to select one option as below. Once that menu function is completed, the program must return to the main menu. For each menu option, you are allowed (and even recommended, if needed) to have multiple steps to complete the tasks.

Student Menu:

Application starts by requesting students ID. If (-1) is introduced, a new student is created, and the user is prompted for all necessary information. The main menu is the following:

L List: lists all records in the course table

E Enroll: enrolls the active student in a course; user is prompted for course ID; check for conflicts, i.e., student cannot enroll twice in same course

W Withdraw: deletes an entry in the Enrolled table corresponding to active student; student is prompted for course ID to be withdrawn from

S Search: search course based on substring of course name which is given by user; list all matching courses

M My Classes: lists all classes enrolled in by the active student. X Exit: exit application

Practical version: (Github repoLinks to an external site.) (10 points bonus)

Requirements:

1.User Roles:

Student: Students can:

View a list of available courses.

Enroll in courses.

View their enrolled courses.

Administrator: Administrators can:

Add, edit, or delete courses.

View a list of students enrolled in each course.

2.Database Schema:

Create a MySQL database with the following tables:

students: This table contains information about students, including their unique student ID, name, email, and other relevant details.

courses: This table stores information about courses, including course ID, name, description, and maximum enrollment capacity.

enrollments: This table tracks student enrollments in courses, linking students to the courses they've enrolled in.

3.User Separation :

Students and administrators should have separate view pages (html files).

Authentication: Implement user authentication for both students and administrators with login information handled by database.

4.Student Features:

Students can log in and view a list of available courses.

They can enroll in a course if there are available slots.

Students can view their enrolled courses and drop courses if needed.

Include a field for the student's GPA, which should be calculated and updated automatically based on the courses they've enrolled in.

GPA should be between 0 and 4 and calculated from course grades.

5.Administrator Features:

Administrators can view all three tables.

Populate the students table with at least 10 student records on first loading.

They can add new students with details such as student id, name, username, email, phone number, date of birth, GPA.

They can add new courses with details such as course id, name, description, and maximum enrollment capacity.

Administrators can view a list of students enrolled in each course and manage enrollments via enrollments table.

(Optional) Provide other possible options for course/enrollment management.

6.Course Enrollment Logic:

Ensure that a course cannot be over-enrolled beyond its maximum capacity.

Implement validation to prevent students from enrolling in the same course twice.

7.User Interface:

Create a user-friendly web pages for both students and administrators using Flask's templates and Jinja2.

Design pages for login (optional), course listing, enrollment, and admin features.

8.Data Validation:

Implement data validation to ensure that user inputs are accurate and safe, such as phone number, emails.

9.Error Handling:

Handle errors gracefully with user-friendly error messages.

10.Testing:

Write unit tests for critical parts of the application, including course enrollment logic and user authentication.

11.Documentation:

Provide clear documentation on how to set up and run the application.

Include documentation for database schema and API endpoints (if applicable).

12.Database Constraints:

Add constraints to the courses table to ensure that the class capacity is less than or equal to 25 students.

Use at least 5 course names provided by Computer Science (CS) department at MSU.

Submission and Evaluation:

The project will be evaluated based on the completeness of the requirements and bonus points for user authentication.

Partial credits will be given based on coverage and quality of completion of all requirements.

Submit your project source code by the checkpoint dates and be prepared to demonstrate it to the Teaching Assistant (TA) during the project demo by appointment with TA.

This Course Management System project provides valuable experience in building a web application using Flask, MySQL, and user authentication. It's a practical project that can be expanded upon with additional features and enhancements.