Unformatted Attachment Preview
Example questions for exam revision
Question: Entity-relationship diagrams.
[15 marks total]
a. Draw an ER diagram based upon the following description of a book
A book has a title, author, and a publisher. Books can have more than one author.
Books can come in multiple formats including hardback, paperback, audio, and
ebook. Authors may write more than one book and publishers may publish many
books; authors are also not bound to a single publisher. Books may exist in a series
of some kind (e.g., a ‘trilogy’, ‘saga’ etc.). Authors may enter into a publishing
agreement (e.g., to write X books or one book every Y months) with a publisher.
Agreements tend to have exclusivity clauses (i.e., binding author(s) to a single
Explain any assumptions you make in modelling.
b. Consider the following ER diagram, which describes a set of relationships
between driving instructors, their clients and the various lesson and tests that
are involved in learning to drive.
Identify possible problems for implementing this database using SQL.
Explain any modifications you would make.
Draw a set of tables that would represent the database; these should fit
your modified scheme.
Question: Relational Algebra
[overall 15 marks]
Consider a database with the following schema:
Person ( name, age, gender )
name is a candidate key
Frequents ( name, pizzeria )
(name, pizzeria) is a candidate key
Eats ( name, pizza )
(name, pizza) is a candidate key
Serves ( pizzeria, pizza, price )
(pizzeria, pizza) is a candidate key
a. Write a relational algebra expression that finds all pizzerias frequented by at
least one person under the age of 18
b. Write a relational algebra expression that finds all pizzerias that serve at least
one pizza that Jane eats for less than £6.00.
c. Write a relational algebra expression that, for each person, finds all pizzas the
person eats that are not served by any pizzeria the person frequents. Return
all such person (name) / pizza pairs.
c. Explain, in English, what the following equation does