A database is to be designed for a University to monitor students' progress throughout their course of study. The students are reading for a degree (such as BTech, BTech(Hons) MCA, etc) within the framework of the modular system. The University provides a number of courses, each being characterized by its code , title, credit value, course coordinator, teaching staff and the department they come from. A course is co-ordinated by a course coordinator who shares teaching duties with one or more lecturers. A lecturer may teach (and be a course coordinator for) more than one course. Students are free to choose any course they wish but the following rules must be observed: some courses require pre-requisites courses and some degree programs have compulsory courses. The database is also to contain some information about students including their numbers, names, addresses, degrees they read for, and their past performance (i.e. courses taken and examination results).
Represent the complete scenario with the help of ER Diagram (use maximum advanced features) and design logical schema for this database. Also specify desirable constraints and justify your design approach even in a single small step.
Note: Assumptions can be made wherever necessary.