[SOLVED] Functional Dependencies SQL database UNIVERSITY OF LONDON

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

cRoyal 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. Dont 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 projects 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 authors 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, authors 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.