Design an ER diagram for a University, which manages the students, Programmes, courses and student results. You should clearly identify the entities, relationships, relationship cardinality and key constraints in your answer. You must also add proper attributes for each entity. A brief description of the University is given below. Make suitable assumptions.
A University allows students to register for its programmes offered during an Academic year. The academic year starts in July and ends in June. All the programmes of the University are of 1-year duration. Each programme consists of 5 compulsory courses of 4 credits each. A student is given an account on the University MIS, where s/he can see her/his result. University conducts the examinations at the end of the academic year. A student is declared passed in a course if s/he secures 50% marks in the examination of that course. A student must pass all the courses of the programme in which s/he has registered. The students who have passed their programme are awarded a certificate during a convocation, which is held once a year
Entities:
1. Student: This entity stores information related to the student such as student ID, name, email, phone number, and address.
2. Programme: This entity stores information related to the programme offered by the University such as programme ID, programme name, duration, start date, and end date.
3. Course: This entity stores information related to the courses associated with each programme such as course ID, course name, and course credit.
4. Result: This entity stores information related to the result of each student enrolled in a programme such as student ID, programme ID, course ID, and marks.
5. Certificate: This entity stores information related to the certificate awarded to the students who have successfully completed their programme such as certificate ID, programme ID, student ID, and convocation date.
Relationships:
1. A student can register for one or more programmes, and each programme can have many students. (M:N relationship)
2. A programme can have many courses, and each course can be associated with many programmes. (M:N relationship)
3. A student can enroll in one or more courses associated with the programme in which s/he is enrolled, and each course can have many students enrolled. (M:N relationship)
4. A student can have one or more results, and each result belongs to one student. (1:M relationship)
5. A programme can have many certificates, and each certificate belongs to one programme. (1:M relationship)
Attributes:
1. Student: ID (primary key), name, email, phone number, address
2. Programme: ID (primary key), name, duration, start date, end date
3. Course: ID (primary key), name, credit
4. Result: student ID (primary key), programme ID (primary key), course ID (primary key), marks
5. Certificate: ID (primary key), programme ID (foreign key), student ID (foreign key), convocation date
Key Constraints:
1. A student ID cannot be null in the result entity.
2. A programme ID cannot be null in the result and certificate entities.
3. A course ID cannot be null in the result entity.
4. A student must secure at least 50% marks to pass a course.
4. A student must pass all the courses of the programme in which s/he has registered to be awarded a certificate.