A relational database contains details about journeys from Shalala to a variety of destinations and contains the
Question:
A relational database contains details about journeys from Shalala to a variety of destinations and contains the following relations:
Operator (epode, ornate)
Journey (epode, destination Code, price)
Destination (destination Code, destination Name, distance)
Each operator is assigned a unique code (epode) and the relation operator records the association between this code and the operator's name (ornate). Each destination has a unique code (destination Code) and the relation destination records the association between this code and the destination name (destination Name), and the distance of the destination from Shalala. The relation Journey records the price of an adult fare from Shalala to the given destination by as specified operator; several operators may operate over the same route.
A) Write the SQL statements to create the preceding relations with appropriate primary key and foreign key integrity constraints [3 marks] [1 mark each]
B) Write an SQL statement to add 'Oman Travels' as an operator with epode=OP100. [1 Mark] Insert into Operator Values('OP100','Oman Travels');
C) Formulate the following queries using relational algebra and SQL
(1) List the details of journeys less than 100. [2 marks]
(2) List the names of all destinations. [2 marks]
(3) Find the names of all destinations within 20 miles. [2 marks]