Consider that you are part of the development team for a European University – an alliance between national institutions across EU. Your task is to develop the database to keep track of the courses offered by ATHENA, the professors teaching these courses and the students following these courses.

Requirement’s analysis

For each student, ATHENA wishes to keep information related to their unique identification code, their first name and surname. Moreover, student contact info including one or more phone numbers (a main contact phone is mandatory), two email addresses (institutional and personal) and a physical address (street, number, city, country, postal code) are recorded the first time students are registering with ATHENA. A compulsory personal email is recorded when registering the student; the ATHENA institutional email is automatically generated when the student’s unique identification code is assigned. This institutional email follows a pattern that can change over time. Currently the students email pattern is “student_identification_code@partner_id.athena_domain”. “student_identification_code” is the ATHENA institutional unique identification code and “athena_domain” is the ATHENA web domain (this domain is currently athena-eu.edu but it might change). The partner university code is determined by each partner university; for instance, for Hellenic Mediterranean University it might be “HMU”. When registering students, it might happen that they do not have yet their ATHENA institutional unique identification code assigned; in such case, the code will be updated as soon as it is available. Finally, for each student the home university and department are also required.

For each course that is offered by ATHENA we attach a unique identifier, the course name, the number of ECTS credits and a textual field with the course description in free text. A course offered by ATHENA may either be mandatory or optional depending on the program of study (which is a set of courses that a student must follow to get the corresponding degree) it is assigned to. For mandatory courses the record kept does not differ from what is noted above. For optional courses we note the scientific field of origin. For instance, students may choose to attend a course relevant to Social Sciences, Life Sciences or any other.

Teachers from the participating institutions of the ATHENA consortium are registered with a specific, unique, identifier. Moreover, information related to their first and last name, email address, academic status (Full/Associate/Assistant Professor), one major scientific field (e.g. databases, computer networks, etc.) and affiliation (current home institution; date of labor contract) are also of interest. They teach courses in specific academic years and respective semesters.  For simplicity, we call a “class” each course offered at a specific semester of a particular academic year.  For instance, Professor Daniel Bart has a class in databases during the winter semester of the academic year 2021-2022. A class, however, may be cooperatively taught by more than one professor in the same semester and academic year, i.e., Professor Maria Serra also teaches the ATHENA Joint Database class during the winter semester of the academic year 2021-2022.

Professors at the ATHENA university alliance are registered only the first time they are getting assigned a specific class. A professor may teach one or more classes. In each class, students are enrolled and get evaluated submitting electronic exams at the end of the respective semester.

For each student that is enrolled in a class, we initially create a record for their enrollment and at the end of the semester we update the respective record with the grade of their evaluation. This information must be registered in the database for, at least, five years after the date when the grade was granted to the student. Students can obviously get enrolled in and attend many different classes subject to a pre-defined maximum number of ECTS credits per semester. On the other hand, each class may have enrolled one or more students; otherwise, the respective course is not taught at all.