Question:

(a) Prepare an ERD for the scenario given below.

(b) Convert the ER diagram produced in (a) above into its physical design. You have to choose an appropriate Primary Key for each table identified in the scenario and underline it to differentiate it with other non key attributes. You also have to show any identified Foreign Key in the physical designed.

The Department of Computing and Mathematical Sciences of Imperial College has a set of courses provided for the undergraduate level. For each course a well defined set of admission criteria is specified; for instance, for the "Computer Science" course, 3 A-levels are required, minimum B for each, an A level in Mathematics being compulsory. Each course has a name, Type and duration. Each course consists of a set of modules, some compulsory and some optional. A certain module is described by its name, a short syllabus, type (lectured, seminar-like, project) and year (Level) of study for which it was intended. Each student enrolled with the department, is registered for one particular course. The student must attend the compulsory modules. The system must also store the grade obtained by each student for each module taken. For every year of study, from the set of optional modules, the student must attend a certain number which is specified in the course's description. Each module is taught by a single lecturer. However, there might be modules, from the optional ones, that are not taught at all (for instance, because no students have actually registered for them). There also can exist lecturers who do not teach at all, being only involved in research. Contact information (address, telephone, ...) plus personal details (date of birth, male/female, ...) is needed for both lecturers and students.