ERD Case Study: Examination System

CS403 Database Management Systems Lecture 13 synthesizes the conceptual database design methodology by constructing a complete, fully attributed Entity-Relationship Diagram (ERD) for the University Examination System case study. Building upon requirements and functional models established in previous lectures, the lecture defines the semantic architecture, attribute sets, primary identifiers, and cardinality constraints governing institutional entities. The Program entity captures degree specifications through attributes including program code as primary key, maximum duration, total semesters, and academic program level. The Student entity models learner profiles, defining student registration number as primary key alongside name, father name, date of birth, and phone number, while identifying semester GPA and course marks as multi-valued properties requiring relational modeling. The Teacher entity maintains instructor records utilizing teacher registration number as unique identifier with qualification, teaching experience, and salary. The Course entity encapsulates curriculum offerings using course code as primary key, course title, and a recursive relationship representing prerequisite requirements, where a prerequisite for a course is itself a course. Furthermore, the Semester entity tracks academic terms via semester name, start date, and end date. The lecture distinguishes base stored attributes from derived attributes, explaining that student cumulative GPA is derived computationally from semester GPAs, which in turn are calculated from course-level grade points. In modeling inter-entity relationships, cardinality constraints are rigorously defined: Program associates with Course via a one-to-many relationship, while Student associates with Program via a many-to-one relationship, ensuring each student belongs to exactly one degree plan. To resolve many-to-many associations between Semester and Course, an associative entity termed Course Offered is introduced, utilizing a composite primary key consisting of semester code and course code. Similarly, the many-to-many relationship between Student and Course Offered is resolved through an Enrolled associative entity. The resulting conceptual ER model provides a robust, tool-independent structural specification validated against institutional reporting needs, preparing Virtual University students for CS403 midterm examinations, solved past papers by Moaaz and Waqar Siddhu, and practice MCQs.