Hamish Burke | 2025-06-16

Related to: #databases

SWEN435 Revision Questions

Query Processing Optimisation

SELECT ArrTime
FROM Line NATURAL JOIN Service NATURAL JOIN Destination
WHERE LineName = 'Great Sights' AND From = 'Auckland Central'
AND To = 'Wellington Parliament' AND StopName = 'Tongariro';

a) Transfer the above SQL into Relational Algebra

• Good, can be more concise by combining all selections into one thing. Ensure when using join to make sure that the two sets you're combing have a attribute in common

b) Make a query tree for a.

c) Optimise query tree

Query Tree Heuristic Rules

I'm going to put selection below the joins, so I am using selection on as small a subset as possible.

Transforming ERR to RDM

EER Schema

Level 0:

Tourist(DateOfBirth, Name, Passport, {Name})
Airline(Name, Website, {Name})

Level 1:

FlightOffer(Price,Conditions,TravelDate,TravelTime,Destination,Airline,{Airline,TravelDate,TravelTime,Destination})

Level 2:

Book(TravelAgent,Date,Tourist,FlightOffer, {Tourist,Date,FlightOffer})

RD Schema:

S = {
	Tourist({Name, DoB, PassportNum},{Name}),
	Airline({Name,Website},{Name}),
	FlightOffer({Price,Conditions,TravelDate,TravelTime,Destination,AirlineName},{AirlineName,TravelDate,TravelTime,Destination}),
	Book({TravelAgent,Date,TouristName,AirlineName,TravelDate,TravelTime,Destination},{TouristName,Date,Airline,TravelDate,TravelTime,Destination})
}

I = {
FlightOffer[AirlineName] \in Airline[Name],
Book[TouristName] \in Tourist[Name],
Book[(AirlineName,Destination,TravelDate,TravelTime)] \in FlightOffer[AirlineName,Destination,TravelDate,TravelTime]
}

NOT NULL Constraints = { // Why are these the ones choosen to not be null??
	Null(Tourist, PassportNumber) = Not,
	Null(Airline, Website) = Not,
}


// these are the ones i'd expect to need to be null (ie the key attributes)
TouristName,AirlineName,TravelDate,TravelTime,Destination,Date

Extended Entity Relationship Model