CMP-370, Data Base Systems
Due date day of final exam
The academic world is an interesting example of international cooperation and exchange.
This problem is concerned with modelling of a database that contains information on researchers, academic institutions, and collaborations among researchers.
A researcher can either be employed as 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/her name, year 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, School of Computer Science,. . . ). A school belongs to exactly one
• An employment history, including information on all employments (start and end
date, 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/her highest degree (BSc, MSc or PhD),
Including who was the main supervisor, and at what school.
• For each professor, information on what research projects (title, start date, and
end date) he/she is involved in, and the total amount of grant money for which
he/she was the main applicant.
Note: You can make needed assumptions that do not contradict with the problem rules.
1) Define a complete ER diagram for this system.
2) Define the corresponding relational Data base, which you are supposed to put in the 3rd NF
3) Write the SQL commands to formulate the following queries:
a. The list of projects where Full professor “xxx” is involved
b. The list of schools located at country “morocco”.
c. The list of papers written by the assistant professors in “sss” school
d. The list of all projects that end next year.
e. For each paper, the list of all authors with their names, degree and position (if any).
f. For each project, all professors who are involved with their personal information including the current school and institution where they are working.