Exercise 13: Culinary School

erDiagram

    student {
        string name
        string contact
        int key PK "id_student"
    }

    instructor {
        string name
        string specialty
        int key PK "id_instructor"
    }

    course {
        string title
        string description
        decimal fee
        int key PK "id_course"
        int id_instructor FK "id_instructor"
        int id_course_state FK "id_course_state"
    }

    course_state {
        string name
        string label
        string uuid
        int key PK "id_course_state"
    }

    course_registration {
        datetime enrollment_date
        int key PK "id_course_registration"
        int id_student FK "id_student"
        int id_course FK "id_course"
    }

    course_state one--many course : has
    student one--many course_registration : enrolls
    instructor one--many course : teaches
    course one--many course_registration : includes

Description #

• The property “name” in the table “course_state” can have the values: “Upcoming”, “Ongoing”, “Completed”, “Canceled”.

• This app manages course enrollment for students. Students can register for courses, which are taught by instructors and have a specific status (e.g., ongoing, completed).

• In entity “course_state”, name represents the state that will be displayed on the screen, label represents the value that programmer will use to filter the states, and the uuid represents a value to distinguish the states.

• OBS: The value of the field “uuid” is generated automatically.

Tasks #

• student/instructor/course/course_state/course_registration: Add, Edit, Delete.

  • When the course is created, it will automatically become “Upcoming”.
  • The field “Course state” from entity “course” will be read-only in edit view.

• Create a catalog for entities: “course_state”, “course”, “instructor” and “student”.

• Create event listeners for tables that needs validations (e.g., adding a student without a name, course without title, etc., should not be allowed).

  • Check if the course registration date is available, if it is not → validation message.
  • The course the student is enrolling in must be in the “Upcoming” status, if not → validation message.
  • A student should not be enrolled twice in the same course → validation message.

• Add a new entity action at the course level “Initiate course”:

  • First check if the course has alredy been initiated (it has the status “Ongoing”), if it has → validation message;
  • A course should have at least one student, if not → validation message;
  • After the initiation of the course, the state will change from “Upcoming” → “Ongoing”;
  • A course cannot be initiated if it is in the “Completed” or “Canceled” state.

• Add a new entity action at the course level “Cancel course”:

  • It will change the course status to “Canceled”;
  • A course cannot be canceled if it is in the “Ongoing” state → validation message;
  • A course cannot be canceled if it is in the “Completed” state → validation message;
  • A course cannot be canceled if it is already in the “Canceled” state → validation message.

• Add a new entity action at the course level “Complete course”:

  • It can’t be completed if it is “Upcoming” → validation message;
  • It can’t be completed if it is “Canceled” → validation message;
  • If it is already in “Completed” state → validation message.

• Student report: course situation at student level (total number of courses enrolled, fee details).

• Instructor report: course situation at instructor level (total number of courses taught, fee details).

• Course report: students situation at course level (total number of students, total fee collected).