Question

1. Shown below is the relational database schema for a database which contains details regarding bus journeys from Tronoh to various destinations: BusOperator (opCode, opName) Journey(QpCode,GestinationCode price) Destination (destinationCode, destinationName, distance) a. Formulate the following queries using relational algebra: . Get the name of bus operators (e.g., Plusliner, Sri Maju etc.) that provide journeys which are priced at more than RM50.00. (3 marks ii. List the names of a destinations along with their prices provided by Plusliner. 3 marks] b. Write the SQL's CREATE TABLE statements to create the structure for the 6 marks c. Let's say some records have been inserted into the database. Write an update database above statement that will change the value stored in the distance attribute of table Destination from mile to km. [Note: 1 mile is equivalent to 1.609 km]. 3 marks]

Answer 1

a)

1:

SELECT B.opName, B.opCode, J.price
FROM BusOperator B, Journey J
WHERE J.price>RM50.00;

2:

SELECT opcode
FROM BusOperator
WHERE opName='Plusliner';

SELECT price
FROM Journey
WHERE opName='<#obtained opcode#>';

b) Structure of the above DB:

CREATE TABLE BusOperator
(
opCode int,
opName varchar(255),

PRIMARY KEY(opCode)
);

CREATE TABLE Journey
(
opCode int,
destinationCode int,
price int,

FOREIGN KEY (opCode) REFERENCES BusOperator(opCode)

FOREIGN KEY (destinationCode) REFERENCES Destination(destinationCode)
);

CREATE TABLE Destination
(
destinationCode int,
destinationName varchar(255),
distance int,

PRIMARY KEY(destinationCode)
);

c)

UPDATE destination

SET distance = (distance/0.62137);

we are using the direction conversion formaula i.e, km= mi/0.62137