Assignment 2
The department of Computing wishes to maintain the following information about its structures and activities.
The department contains several research groups. Each research group is identified by a name, has a group head and has a main research area associated with it.
Each group head is identified by the combination of his/her surname and first name, has a rank and heads one group only.
The department has academics who teach courses and who can be members of research groups. Each academic has a rank and teaches at least one course. Each course is taught by one or more academics. Each course is identified by a code and has a title.
An academic is a member of at most one research group, and a research group has one or more academics as members.
All academics and research group heads are departmental staff. Each member of the departmental staff is identified by the combination of his/her surname and first name, and has a salary.
1) Represent the data above in an ER diagram. Be careful to represent all the existence constraints and the relationship cardinalities.
2) Translate the ER model into relational model, giving all the relation schemas and the primary and foreign keys. For each foreign key, indicate which relation it refers to and whether or not it can accept NULL values.