Faculty of Foreign Languages – Timetable Management System

This project implements a database management system for a Faculty of Foreign Languages, including both SQL and Python implementations for managing courses, teachers, student groups, classrooms, and timetables.

---

Database Design

The database was designed using an Entity-Relationship Diagram (ERD):

It contains the following tables:

1. Materii – courses
2. Profesori – teachers
3. Sali – classrooms
4. Grupe – student groups

The ERD shows the relationships and keys ensuring entity, domain, and referential integrity, as well as normalization.

---

End Users

1. Faculty Administrators – Responsible for overall database management, including adding, modifying, and deleting groups, courses, rooms, and teachers. Generate statistical reports such as student numbers, course distribution, room occupancy, and timetable summaries.
2. Teachers – Can view course information, classroom details, and student lists for the groups they coordinate.
3. Students – Can consult their course timetable, room locations, course credits, and teacher information.

---

Queries Implemented

1. List of Courses and Associated Teachers
   Description: Returns the list of courses along with the names of the teachers who teach them.
   Motivation: Useful for students and teachers to check who teaches each course.

sql SELECT Materii.nume_materie, Profesori.nume_profesor FROM Materii JOIN Profesori ON Materii.nume_materie = Profesori.nume_materie;

2. Classroom Schedules by Courses
   Description: Returns the list of courses and the classrooms where they take place, including building and floor.
   Motivation: Useful for students, teachers, and administrators to know the schedule and room assignments.

sql SELECT Materii.nume_materie, Sali.cladire, Sali.sala, Sali.etaj FROM Materii JOIN Sali ON Materii.nume_materie = Sali.nume_materie;

3. Students Distribution by Year of Study
   Description: Returns the total number of students enrolled in each year.
   Motivation: Useful for administrators to understand student distribution and plan resources efficiently.

sql SELECT an, SUM(nr_studenti) AS total_studenti FROM Grupe GROUP BY an;

4. Advanced Courses with More Than 3 Credits
   Description: Returns the list of advanced-level courses that have more than 3 credits.
   Motivation: Useful for students who want to enroll in advanced courses and for administrators to plan the curriculum.

sql SELECT nume_materie, nr_credite FROM Materii WHERE nivel = 'avansat' AND nr_credite > 3;

5. Average Number of Students per Group
   Description: Calculates the average number of students per group for each year of study.
   Motivation: Useful for administrators to understand group sizes and optimize student distribution.

sql SELECT an, AVG(nr_studenti) AS media_studenti FROM Grupe GROUP BY an;