You have been approached by a university for the design and implementation of a relational database system that will provide information on the courses it offers, the academic departments that run the courses, the academic staff and the enrolled students. The system will be used mainly by the students and the academic staff.

The requirement collection and analysis phase of the database design process provided the following data requirements for the University Database system.

Project Requirements

Each department runs a number of courses. The university provides a set of modules used in different courses. Each course uses a number of modules but not every module is used. A course is assigned a unique course code and a module is identified by a unique module code. A module can be used in one course only, but can be studied by many students. In addition to the module code each module unique title, start date, end date, texts $($books$),$ and assessment scheme $($i$.$e$.$ coursework and exam marks percentages$)$ are also stored.

Each course is managed by a member of academic staff, and each module is coordinated by a member of academic staff also. The database should also store each course unique title, and duration $($in years$).$

A student can enroll in one course at a time. Once enrolled a student is assigned a unique matriculation number. To complete a course, each student must undertake and pass all the required modules in his$/$her course. This requires that the database store the performance $($pass or fail$)$ of each student in every module.

Additional data stored on each student includes student name $($first and last$),$ address $($town$,$ street, and post code$),$ date$-$of$-$birth, sex, and financial loan. For emergency purposes the database stores the name $($not composite$),$ address $($not composite$),$ phone, and relationship of each student next$-$of$-$kin. None of the next$-$of$-$kin's attributes is unique. Assume that every next$-$of$-$kin is a next$-$of$-$kin of one student only.

Each department is managed by a member of academic staff. The database should record the date he$/$she started managing the department. Each department has a name, phone number, fax number, and location $($e$.$g$.$ E Block$).$ Each department employs many members of academic staff.

A member of academic staff can be the leader $($i$.$e$.$ manager$)$ of at most one course, but can be the coordinator of more than one module. A member of academic staff may not be assigned any of the above mentioned roles $($coordinator$,$ course leader, department manager$).$ All members of academic staff teach modules. Every member of academic staff teaches one or more modules, and a module may be taught by more than one member of academic staff. The database should record the number of hours per week a member of academic staff spend teaching each module. Each member of academic staff is identified by a unique staff number. All members of staff and students have unique computer network user ID numbers. Additional data stored on each member of academic staff includes name $($first and last$),$ phone extension number, office number, sex, salary, post $($lecturer$,$ or senior lecturer, or Professor, etc.$),$ qualifications, and address $($not composite$).$ A member of academic staff work for one department only.