This project requires you to design a record management system for a university. The university has the following entities:
1. Students:
o Attributes: Student ID, name, date of birth, contact information, program enrolled in, year of study, current grades, graduation status, disciplinary records.
Relationships:
o Enrolled in (many-to-many) courses with lecturers.
o Advised by (one-to-one) a faculty advisor (lecturer).
o Registered for (one-to-many) student organizations.
2. Lecturers:
o Attributes: Lecturer ID, name, department, academic qualifications, areas of expertise, course load, research interests, publications.
Relationships:
o Teach (many-to-many) courses with students.
o Advise (one-to-many) students.
o Head (one-to-one) a research group.
o Member of (one-to-many) committees.
3. Non-Academic Staff:
o Attributes: Staff ID, name, job title, department, employment type, contract details, salary information, emergency contact information.
4. Courses:
o Attributes: Course code, name, description, department, level, credits, prerequisites, schedule, lecturers, enrolled students, materials.
5. Departments:
o Attributes: Department name, faculty, research areas, courses offered, staff members.
6. Programs:
o Attributes: name, degree awarded, duration, course requirements, enrolment details.
7. Research projects:
o Project title, principal investigator, funding sources, team members, publications, outcomes.
• Construct a database in mysql.
• Populate the database with dummy data
• Build a simple interface from which the users can query the underlying database
• Allow the user to execute at least 5 different queries of the database. The query must be executed within the Python code. The following are some of the suggested queries:
o Find all students enrolled in a specific course taught by a particular lecturer.
o Retrieve the contact information for the faculty advisor of a specific student.
o Search for lecturers with expertise in a particular research area.
o List all courses taught by lecturers in a specific department.
o Identify lecturers who have supervised the most student research projects.
o Generate a report on the publications of lecturers in the past year.
o Find all staff members employed in a specific department.