Database Theory and Applications M
COMPSCI5076
Answer All Questions
This examination paper is an open book, online
assessment and is worth a total of 60 marks.
December Diet 1 Continued Overleaf/
Part A: Conceptual Modelling & Normalisation [20 Marks]
Question 1. Kelvin Hall Club in West End of Glasgow sets up a database for its members. The Club
operates various personal activities like body styling, squat, bench press, treadmill, etc. A member
(recording a surname, date of birth and a unique identifier) should be registered once (recording a
fixed registration fee and date of registration). Furthermore, a member should pay each year an annual
membership fee. For each member, we record the annual membership fee for each year. Moreover,
the Club has rooms available for booking for the members. A member can book a room for an activity
on a specific date and time. The duration for each booking is fixed to 1 hour.
(a) Draw an Entity-Relationship (ER) conceptual data model (diagram) of the above description
adding the required semantics, like roles, cardinality degrees, relationships, entities, and attributes.
Note: Use any convenient means to draw your ER diagram, e.g., freehand drawing.
[10 Marks]
(b) Map your proposed ER diagram from Question 1.(a) to a Relational model. Show your relations
along with their attributes, primary and foreign keys, if any.
[10 Marks]
December Diet 2 Continued Overleaf/
Part B: SQL [20 Marks]
Question 2. Consider the following relational schema:
MANUFACTURER(ID, Name)
VEHICLE(NumberPlate, Year, MID*)
RETAILER(RID, Name)
SALES(TID*, VID*, Price)
where, a manufacturer has a unique identifier (ID) and a name, a vehicle has a unique number
plate, year of production and is associated with a manufacturer via the foreign key MID
referencing to the ID in Manufacturer. A retailer is identified via the unique RID along with a
name. The Sales relation stores information about retailers (via the foreign key TID
referencing to RID in Retailer), who sell vehicles (via the foreign key VID referencing to
NumberPlate in Vehicle) with selling prices in GBP (￡). A manufacturer can produce more
than one vehicle. A retailer can sell many vehicles. A vehicle can only be sold by one retailer.
The primary keys are underlined, while the foreign keys have an ‘*’.
(a) Provide a SQL statement that shows the retailers (name and RID) and their total sales (in
￡), who have sold more than 1,000 vehicles.
[5 Marks]
(b) Provide a SQL statement that shows the number of the manufacturers who have produced
more than 100 vehicles since 2020
[5 Marks]
(c) Provide a SQL statement that shows the retailers (name and RID) who have sold the least
vehicles.
[10 Marks]
December Diet 3 /END
Part C: Relational Algebra & Heuristic Optimization [20 Marks]
Question 3. Consider the relations in Question 2 and assume the following query:
SELECT V.NumberPlate, V.YEAR
FROM VEHICLE AS V, MANUFACTURER AS M, SALES AS S
WHERE S.VID = V.NumberPlate AND M.ID = V.MID
AND M.ID = 3 AND S.PRICE >= 40000
We know that the manufacturer with ID = 3 has produced 500 vehicles, and 300 out of these
vehicles have been sold with selling prices ranging from ￡20000 to ￡60000. There are 500
manufacturers (number of tuples in Manufacturer), 12,000 vehicles (tuples in Vehicle) and
8,000 sales (tuples in Sales). 2,000 vehicles have been sold with price greater than ￡40,000.
(a) Provide the canonical tree of the query. Then, estimate the expected number of tuples
retrieved using the canonical tree. Note: Use any convenient means to draw your canonical
tree, e.g., freehand drawing.
[5 Marks]
(b) Optimize the query using heuristic optimization by providing the optimal tree and the
corresponding optimal relational algebra expression. Note: Use any convenient means to draw
your optimal tree, e.g., freehand drawing.
[10 Marks]
(c) Estimate the expected number of tuples retrieved at each operator in your optimal tree
from the Question 3.(b). How many vehicles do you expect in the query output
[5 Marks]
