UNIVERSITY OF LONDON
MSc EXAMINATION 2016
For Internal Students of
Royal Holloway
DO NOT TURN OVER UNTIL TOLD TO BEGIN
BI5631: Database Systems
BI5631R: Database Systems – PAPER FOR RESIT CANDIDATES
Time Allowed: 1 1/2 hours
Answer THREE questions
No credit will be given for attempting any further questions
Calculators are NOT permitted
c©Royal Holloway, University of London 2016
Page 1 of 5 2015-16
Important Copyright Notice
This exam paper has been made available in electronic form
strictly for the educational benefit of current Royal Holloway students
on the course of study in question.
No further copying, distribution or publication of this exam paper is permitted.
By printing or downloading this exam paper, you are consenting to these restrictions.
BI5631/BI5631R
1. (a) A real estate agency wishes to store their business-related information in a
more organised manner. The following has been identified as important.
• For every real estate property the agency deals with, they store an ID
code, the size in square feet, the number of rooms, the address, and the
region that the property lies in.
• Real estate properties furthermore come in two variants, flats (apart-
ments) and houses.
• For each sale that the agency has been involved in, it stores an identify-
ing number, a transaction date, the sum of money involved, the property,
the seller and the buyer.
• Customers of the agency are traced with an ID code, and have a name,
an email address and a postal address. Customers are traced over time,
so that each customer can be involved both in one or several sales and
one or several purchases.
• The agency lists current offers, which are offers for properties. Each of-
fer has a date when it was announced, an earliest date when the prop-
erty is available, a seller, and a requested price.
• Finally, each user can register an interest for one or several properties,
in which case they should be contacted when the property next goes on
the market.
Construct an E-R diagram according to these requirements. Don’t forget to
indicate the various types of constraints in your diagram. [18 marks]
(b) Translate your E-R diagram from question 1a into a collection of relational
schemas. Be careful to identify all constraints and minimise redundancy.
[16 marks]
Page 2 of 5 NEXT PAGE
No further copying, distribution or publication of this exam paper is permitted.
By printing or downloading this exam paper, you are consenting to these restrictions.
BI5631/BI5631R
2. (a) The following is a set of relational schemas. The primary keys are under-
lined.
• project(project code, dept code, budget, manager id)
• department(dept code, dept name, location)
• staff(staff id, name, dept code)
• works on(staff id, project code)
Every attribute which shares a name with the primary key of a different re-
lation is a foreign key. Additionally, manager id is a foreign key to the staff
relation.
Construct relational algebra expressions for the following tasks.
i. Find the budget of project APB300 [4 marks]
ii. Find the name and location of the department to which project ABP300
belongs [4 marks]
iii. Produce a list of all projects with a budget of more than 50,000. [4 marks]
iv. Produce a list of all projects whose manager belongs to a different de-
partment than the project’s department. [5 marks]
v. Produce a list of names of staff members who work on more than one
project. [5 marks]
(b) Write an SQL query that is equivalent to the relational algebra expression
(staff ./ department), without using any variation of the JOIN keyword in SQL.
Make sure that your query is fully equivalent! [6 marks]
(c) Describe briefly what transactions are, why they are needed, and give an
example of how to create a transaction in SQL. [6 marks]
Page 3 of 5 NEXT PAGE
No further copying, distribution or publication of this exam paper is permitted.
By printing or downloading this exam paper, you are consenting to these restrictions.
BI5631/BI5631R
3. (a) The following is a set of relational schemas for a simple online book-collection
database.
• author(author id, name, country)
• book(book id, title, author id, pages)
• user(user id, name, email, country)
• owns(user id, book id, rating); the “rating” attribute is null if the user has
not given a rating for the book.
Construct SQL queries for the following tasks:
i. A list of all books written by Stephen King, giving title and number of
pages, sorted by title. [4 marks]
ii. A list of books, giving for each the title, the author’s name, the number
of users in the database who own it, and its average rating. [4 marks]
iii. A list of users, giving for each user their name, their country, the number
of books in their collection and the number of ratings they have given.
[4 marks]
iv. A list of users, giving for each the name, the country, and the number of
books they own with a German author. [4 marks]
v. A list of books, giving for each the title, author’s name, and the average
rating it has been given by users who own at least 100 books. [5 marks]
(b) The users of the site have requested a feature allowing them to update the
rating of a book. They want this to be implemented in a way that allows them
to view the history of updated ratings they have given each particular book,
i.e., for every book in their collection, they want to be able to view a list of
their historical ratings of that book, along with the date on which each rating
was given. Suggest an edit of the database schema to support this feature.
[7 marks]
(c) What is a materialized view, and how does it differ from an ordinary view and
a table? [6 marks]
Page 4 of 5 NEXT PAGE
No further copying, distribution or publication of this exam paper is permitted.
By printing or downloading this exam paper, you are consenting to these restrictions.
BI5631/BI5631R
4. (a) Many database management systems (DBMSs) automatically create an in-
dex on a column whenever a UNIQUE constraint is added on that column.
What is the purpose of this, and why is it likely to be a good idea? [7 marks]
(b) What does a DBMS enforce in a FOREIGN KEY constraint? [6 marks]
(c) Describe in your own words what the following functional dependencies sig-
nify. [6 marks]
i. A→ BC
ii. AB → C
(d) Name a normal form other than First Normal Form, briefly describe it, and
give an example of a relation that does not meet that normal form (along
with an explanation of why this is the case). [7 marks]
(e) What is the snapshot isolation mode of transaction management, and what
guarantees does it give? How does it differ from full serializability? [8 marks]
END
Page 5 of 5 MW
No further copying, distribution or publication of this exam paper is permitted.
By printing or downloading this exam paper, you are consenting to these restrictions.