A relational database contains details about journeys from Paisley to a variety of destinations and contains the following relations: Operator (opCode, opName) Journey (opCode, destinationCode, price) Destination (destinationCode, destinationName, distance) Each operator is assigned a unique code (opCode) and the relation operator records the association between this code and the operator’s

Question

name (opName). Each destination has a unique code (destinationCode) and the relation destination records the association between this code and the destination name (destinationName), and the distance of the destination from Paisley. The relation Journey records the price of an adult fare from Paisley to the given destination by as specified operator, several operators may operate over the same route. Formulate the following queries using relational algebra, tuple relational calculus, and domain relational calculus (the answers to these queries in SQL are given in the next section):

(a) List the details of journeys less than £100.
(b) List the names of all destinations.
(c) Find the names of all destinations within 20 miles.
(d) List the names of all operators with at least one journey priced at under £5.
(e) List the names of all operators and prices of journeys to ‘Ayr’.
(f) List the names of all destinations that do not have any operators.

Answer 1

(a) RA: ? price < 100 (Journey)
TRC: {J | Journey(J) ? J.price < 100}
DRC: {opCode, destinationCode, price |Journey(opCode, destinationCode, price) ?
price < 100)}
(b) RA: ? destinationName (Destination)
TRC: {D.destinationName | Destination(D) }
DRC: {destinationName | (?destinationCode, distance)
(Destination(destinationCode, destinationName, distance))}
(c) RA: ? destinationName (? distance < 20 (Destination))
TRC: {D.destinationName | Destination(D) ? D.distance < 20}
DRC: {destinationName | (?destinationCode, distance)
(Destination(destinationCode, destinationName, distance) ? distance < 20)}
(d) RA: ? opName (? price < 5 (Journey) 3 opCode Operator)
TRC: {O.opName | Operator(O) ? (?J) (Journey (J) ? (O.opCode = J.opCode) ?
J.price < 5)}
DRC: {opName | (?opCode, opCode1, destinationCode, price)
(Operator(opCode, opName) ? Journey(opCode1, destinationCode, price) ?
(opCode = opCode1) ? price < 5)}
(e) RA: ? opName, price ( (? destinationName = ‘Ayr’ (Destination) 3 destinationCode (Journey 3 opCode Operator))
TRC: {O.opName, J.price | Operator(O) ? (?J)(?D) (Journey (J) ? Destination(D) ?
(O.opCode = J.opCode) ? (J.destinationCode = D.destinationCode) ?
D.destinationName = ‘Ayr’)}
DRC: {opName, price | (?opCode, opCode1, destinationCode, destinationCode1,
destinationName, distance) (Operator(opCode, opName) ?
Journey (opCode1, destinationCode, price) ?
Destination(destinationCode1, destinationName, distance) ?
(opCode = opCode1) ? (destinationCode = destinationCode1) ?
destinationName = ‘Ayr’)}
(f) RA: ? destinationName ?? destinationCode (Destination) – ? destinationCode (Journey)) 3 destinationCode Destination)
TRC: {D.destinationName | Destination(D) ? (~(?J) (Journey (J) ?
(J.destinationCode = D.destinationCode))) }
DRC: {destinationName | (?destinationCode, distance)
(Destination(destinationCode, destinationName, distance) ?
(~(?opC, destinationCode1, price) (Journey(opC, destinationCode1, price) ?
(destinationCode = D.destinationCode1)))}