DECEMBER 2021
8 HOUR TAKE HOME EXAM
SCHOOL OF COMPUTER SCIENCE

MODULE CODE:

IS5102
MODULE TITLE:

Database Management Systems
TIME TO HAND IN: 8 hours

EXAM
INSTRUCTIONS
a. Answer all three questions
b. Each question carries 20 marks

This assessment consists of exam-style questions and you should answer as you
would in an exam. As such, citations of sources are not expected, but your answers
should be from your own memory and understanding and significant stretches of text
should not be taken verbatim from sources. Any illustrations or diagrams you include
should be original (hand or computer drawn). You may word-process your answers,
or hand-write and scan them. In either case, please return your answers as a single
PDF. If you handwrite, please make sure the pages are legible, the right way up and
in the right order. Your submission should be your own unaided work. While you are
encouraged to work with your peers to understand the content of the course while
revising, once you have seen the questions you should avoid any further discussion
until you have submitted your results. You must submit your completed assessment
on MMS within 8 hours of you downloading the exam. Assuming you have revised the
module contents beforehand, answering the questions should take no more than three
hours.
Some question may have word limits. These will be stated at the start of the question
(or part question). An advisory word limit is a guide to the level of detail and amount
of information expected in an answer. Longer answers may lose marks for including
large amounts of irrelevant material, or for failing to state arguments clearly and
concisely.

Page 2 of 4

1. Database modelling:
(a) Draw an E-R diagram to model the following scenario:
Our research society plans to run several academic journals. We need a
database, shared between all these journals, to store information about the
authors, reviewers, and editors, and keep track of the peer review process.
Each paper is written by one or more authors, has a title, and is submitted to a
specific journal. To help the authors, for each journal we will store a brief
description of its scope, and the names of its editors, together with their area(s)
of expertise, to be displayed on the journal’s website.
When the paper is submitted to a journal, we will be looking for opinions by at
least two reviewers. For such invitations, we will match area(s) of expertise of
the reviewers with the list of keywords provided by the authors. To help the
editors to track the progress of the review, send reminders, or respond to
declined review requests and invite new reviewers, we will record the dates of
a review invitation being sent and accepted. When a review is completed,
reviewers submit their recommendation (accept/reject) and a textual comment
with its justification. We also record the date of receiving their review.
If the editors agree to accept the paper, we will publish it in one of the next
issues of the journal. In addition to the title, author names, emails and
affiliations, the paper will also show the journal volume and number, the year
of publication, page numbers for the first and last pages of the publication, and
the dates it was submitted and accepted.
The user should be able to use their account to act in multiple roles as an author,
editor, or reviewer for different journals and/or papers.
Show clearly any cardinality or participation constraints for relationships in your
diagram. State any assumptions you need to make. [8 marks]
(b) Derive a relational schema for your E-R diagram from part (a). Be sure to
specify sensible attribute types, and any necessary primary key, foreign
key, and non-null constraints. [6 marks]
(c) Give SQL queries over SQL tables corresponding to your relational schema
from part (b) for the following queries (minor syntax errors will not be
penalised, provided it is clear what you meant):
(i) List names and descriptions for all journals in the database, ordered
alphabetically by name. [2 marks]
(ii) List names, affiliations, and areas of expertise of all editors of the “Big
Data Newsletter”. [2 marks]
(iii) For all review invitations, accepted in 2020 but still not completed, list
reviewer’s name and email address, and the title of the paper to
review (this is needed to send them a reminder). [2 marks]
[Total marks 20]
Page 3 of 4

2. SQL:
A village community centre needs to organise their room booking system. The
following relational schemas have been defined:
person (person_id, name, phone, email)
club (club_id, club_name, leader_id)
club_member (club_id, person_id, join_date)
room (room_no, capacity)
club_time (club_id, day_of_week, room_no, time_start,
time_end)
It is assumed that each club has a unique leader responsible for running it, and
no club has more than one meeting each day.
For questions 2(a), 2(b) and 2(c) below minor syntax errors will not be penalised,
provided it is clear what you meant.
(a) Write suitable SQL DDL statements to create the tables as above. Include
attribute types, primary and foreign key constraints. [8 marks]
(b) Write SQL DML queries over the tables defined in part (a) to list:
(i) Names of clubs and their leaders, ordered by the club name.
[2 marks]
(ii) Club names and the number of their members, listed in descending
order by the number of members. [2 marks]
(iii) List of email addresses of all people having some activity at room 3B
on a Wednesday. [2 marks]
(c) Write SQL statement(s) to modify the room table to change the capacity of
each room suitable for two or more people from n to n/2 (rounding down
if necessary). [3 marks]
(d) How would you use triggers to detect and alert the user about scheduling
conflicts? Describe two different scenarios where the use of triggers may be
helpful, and give an outline of your solution. You do not have to write SQL
code, but may name relevant SQL commands. (Advisory word limit of the
answer for 2(d) is 200 words.) [3 marks]
[Total marks 20]

Page 4 of 4

3. Normalisation:
The research funding office of a university maintains information about research
projects supported by various funding bodies, and research staff employed to
work on these projects. Each project has a unique acronym and project ID. It also
has its funding body and budget. Projects employ staff on an FTE (full time
equivalent) basis. Each project is divided into tasks. Staff members need to report
the number of days they have worked on each task in each month. A fragment
of their records is given in the following table:

Acro-
nym
ID Fun
der
Bud-
get
Staff
name
Staff
ID
FTE
Ta
sk
Task
name
Month
Da
ys
COD 62 EU €9500
Smith 2311 20%
T1 Lead Dec-20 5
T2 Code Jan-21 5
Lopez 5732 50%
T2 Code Dec-20 7
T3 Docs Dec-20 3
T2 Code Jan-21 5
T2 Docs Jan-21 5
BUG 41 UK
RI
£8000
Müller 4318 1.0 T1 Site Jan-21 20
Lopez 5732 0.5 T2 DB Jan-21 10
RAY 29 RSE £4000 Melnyk 3821 25% T2 Tests Jan-21 10

(a) Explain why this data is unnormalized. Give two reasons why leaving it in
this form is not useful, and convert the data to 1NF. (Advisory word limit
of the answer for 3(a) is 200 words.) [3 marks]
(b) Briefly explain what is meant by a functional dependency. Identify the
minimal set of functional dependencies that hold on your answer table
from part (a). [6 marks]
(c) Are there any update anomalies in your answer to part (a)? If so, give an
example. If not, add another row to make a table that would have an update
anomaly. [2 marks]
(d) Convert the data from part (a) to 2NF, and then to 3NF, making clear each
step. [7 marks]
(e) How would the answer to (d) change if it used the general definition of 3NF
(no transitive dependencies on any candidate key)? (Advisory word limit
of the answer for 3(e) is 200 words.) [2 marks]

[Total marks 20]

*** END OF PAPER ***