Implementation of the Students and Student Courses Tables in the Database

[J3SSY-ANDU]

Objective: Implement the students and student_courses tables from the provided schema in the database. The implementation should ensure proper integration with the rest of the database schema and provide support for CRUD (Create, Read, Update, Delete) operations using Python.

Description: This task involves implementing the students and student_courses tables as shown in the provided database schema. The students table will store student information such as student_id, name, email, birthdate, and other personal details, while the student_courses table will handle the many-to-many relationship between students and courses. This table will link students to specific courses and sections with an additional status field to track their enrollment status. The implementation should include setting up both tables in the database and ensuring that they integrate well with existing tables.

Furthermore, Python scripts will be implemented to facilitate CRUD operations (Create, Read, Update, Delete) for these two tables.

Requirements:

Set up the students table with the following fields:

• student_id (primary key)
• name
• email (ensure no duplicates, not null)
• birthdate
• ssn (Social Security Number, int)
• password (for authentication, hashed, int or varchar depending on security needs)

• Set up the student_courses table to establish the relationship between students and courses:

• student_id (foreign key to students)
• course_code (foreign key to courses)
• section_id (foreign key to sections)
• status (varchar, to track enrollment status)

• Ensure foreign key constraints are properly implemented to maintain referential integrity between students, student_courses, courses, and sections.

Implement basic CRUD operations using Python scripts to interact with both tables:

• Create: Add new students and their course enrollments.
• Read: Retrieve student information and their enrolled courses.
• Update: Modify student details or update course enrollments.
• Delete: Remove students or drop their enrollments from courses.

• Ensure that the implementation is secure, especially regarding the password field, and that no sensitive information is exposed.

Time Constraints: This task will begin on Monday, Sept. 9, and should be completed by Wednesday, Sept. 11.

Completion Criteria:

1. The students and student_courses tables are successfully set up in the database with all fields and relationships correctly configured.
2. Python scripts for CRUD operations are fully functional and tested.
3. Documentation of the setup process, table relationships, and the CRUD operations is provided.

Difficulty: 4 - The task involves moderate complexity due to table relationships and secure handling of sensitive data.

Priority: 8 - This task is important for student data management and future integration within the project.

Recommended Assignees: 1x Developer to handle the database setup and the Python CRUD implementation.

[J3SSY-ANDU]

CRUD Functions for Students and Student_Courses Tables

I have implemented the CRUD (Create, Read, Update, Delete) functions for both the students and student_courses tables using the MySQL Connector/Python library. The connection to the MySQL database is managed via a .ini configuration file using the ConfigParser module, ensuring secure handling of credentials.

Additionally, I’ve added a comprehensive tester to validate each CRUD operation on both tables to ensure correct functionality and data integrity.

Let me know if any adjustments are needed.

[nelson-escalona]

Pulled code into local machine and checked functions. Looks good to me. Great work @J3SSY-ANDU