ER Modelling Example

SCENARIO:

This problem is concerned with modelling of a database that contains information on researchers, academic institutions, and collaborations among researchers in a graduate school.

A researcher can be employed as either a professor or a lab assistant. There are
three kinds of professors: assistant, associate and full professors.

The following should be stored:
• For each researcher, his or her name, date of birth, and current position (if any).
• For each institution, its name, country, and inauguration year.
• For each institution, the names of its schools (e.g. School of Law, School of Business). A school belongs to exactly
one institution.
• An employment history, including information on all employment (start and end dates, position, and what
school).
• Information about co-authorships: i.e., which researchers have co-authored a research paper. The titles of
common research papers should also be stored.
• For each researcher, information on his or her highest degree (BSc, MSc or PhD), including who was the main
supervisor, and at what school. The supervisor must be a professor.
• For each professor, information on what research projects (title, start and end dates) he or she is involved in,
and the total amount of grant money for which he or she was the main applicant.

QUESTION:

Draw a logical ERD in crow’s foot notation for the dataset described above. Make sure to indicate all relevant entity sets, attributes, primary keys, foreign keys, relationships and cardinalities. Following good database design principle, the ERD should not contain redundant entity sets, relationships or attributes. Use relationships whenever appropriate and include verb phrases when it is helpful. If you need to make any assumptions, state them clearly in your answer.

HINTS:

The core components of the ERD could be divided as followed:
1) Researcher – Authorship – Paper
2) Professor – Assignment – Project
3) Researcher – Employment/Position – School/Institution
4) Researcher –Qualification/Degree – School/Institution and Professor/Supervisor
5) Institution – School
6) Researcher is-a LabAssistant and Professor (or Researcher – ResearcherType) with an
attribute to differentiate among the different rank of professorship (the suggested solution uses type attribute in Employment).

The alternative thinking as long as they are reasonable:
1) It is acceptable to have a currentPosition or position attribute for the Researcher
2) It is acceptable for School to be a strong entity set, which could affect the PK of School,
Employment/Position and Qualification/Degree
3) It is acceptable if students do not rename the FK in the entity set if there is no conflict 4) It is ok if they opt for a ResearchType pattern instead of a specialization or superset/subset. The solution is not as good because LabAssistant and Professor do not share many of the relationships.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s