Section A — The TRAVEL database
The TRAVEL database captures information about public transport users’ journeys and their fares. The database includes four tables: CUSTOMERS, FARES, STOPS and TAPS. Users travel around the network, “tapping on” with their fare card when they enter a station or vehicle, and “tapping off” when they exit a station or vehicle.
• CUSTOMERS stores each customer’s fare class (e.g., ‘Adult’ or ‘Concession’) and their fare card ID.
• FARES stores the price in cents, for each combination of class and the number of zones travelled.
• STOPS stores information about each stop on the network, particularly its name and fare zone.
– Some stops are grouped together via the parent_station relation. • TAPS stores the travel history of each customer, in the form of the stops they
have tapped on or off at, and the fares they were charged along the way.
All fares are calculated with the “basic” method described in Assignment 1: a tap-on is free, and a tap-off incurs a (positive) charge determined by the inclusive count of zones travelled as well as the customer’s fare class.
Section B — Assignment Tasks
You will first need to execute the script file TravelDB.sql to create and populate your database before working on the following tasks. Wait until you see the message “DONE! All data has been inserted.” This may take a couple of minutes. Tables and any triggers or sequences from Assignment 1 will also be dropped.
Tip: For faster reloading, you can export the database to a .dmp file. Task 1 – Views
1. WriteanSQLstatementtofindthetopten(10)stationsbyrevenue.Astationisdefined as the stops sharing a parent_station, and is named by the stop_name of the entry referred to by parent_station. Revenue for a trip is allocated to the tap- off stop. Your query should display the names of the stations and the corresponding revenues, largest to smallest.
2. WriteanSQLstatementtorefineyourTask1.1querytoincludeonlyrevenuefromusers with the ‘Concession’ fare class who have travelled more than one zone. Your query should display the names of the stations and the corresponding revenues.
3. WriteanSQLstatementtocreatea(virtual)viewV_STATION_BOARDINGS which shows boardings (tap-ons) at every station, aggregated by days of the week and by fare class. Your view should have a column for station name, fare class, day of the week and boardings; and be sorted by the station name, day of the week (Sunday or Monday first) and fare class.
Hints: you may find the following document from Oracle useful for task 1.3.
4. WriteanSQLstatementtocreateamaterializedviewMV_STATION_BOARDINGSthat lists the same information as in Task 1.3.
5. ExecutethefollowingtwoSQLstatements.Reportboththeirqueryexecutiontimeand query execution plan.
SELECT * FROM V_STATION_BOARDINGS;
SELECT * FROM MV_STATION_BOARDINGS;
Question: Did the materialized view improve the query efficiency? Explain your answer. Hint: Refer to both the elapsed time, and the cost in the execution plan.
Task 2 – Indexes
1. TheMarketingdepartmenthasaskedyoutoidentifyanystopswhichare named similarly to characters from a recently-released movie. These stop names should contain a word starting with either of the following:
• “A capital B, then zero or more vowels in any combination, then a single or double R, then zero or more vowels in any combination again, then a B.”
• “A capital K, then one or more vowels in any combination, then an N.”
(For this purpose, a vowel is any of A, E, I, O, U or Y, and letters not explicitly described as capitals may be uppercase or lowercase.)
Write an SQL statement to find every stop matching the criteria, sorted alphabetically by stop_name.
Hint: A “regex” is an expression compactly describing patterns in text. The following documentation may be helpful:
•
2. InordertopotentiallyspeedupthequeryinTask2.1,afunction-basedindexcouldbe created on the STOPS table. Write an SQL statement to create an index IDX_PINK that best fits this task.
3. ReporttheexecutiontimeandexecutionplanoftheSQLqueryyouwroteinTask2.1 before and after creating the index in Task 2.2.
Question: Did the index improve the query efficiency? Explain your answer. Hint: You should look at both the elapsed time and the cost in the query execution plan.
4. TheTransportdepartmentwouldliketoinvestigatetheirmostconsistentusers.Write an SQL statement to count the number of tap-offs for which there are at least 200 other tap-offs with the same combination of customer_id, stop_id and charge values.
5. InordertopotentiallyspeedupthequeryinTask2.4,bitmapindexescouldbecreatedon the TAPS table. Write the SQL statements to create indexes BIDX_CUST_ID, BIDX_STOP_ID and BIDX_CHARGE that best fit this task.
6. ReporttheexecutiontimeandexecutionplanoftheSQLqueryyouwroteinTask2.4 before and after creating the indexes in Task 2.5.
Question: Did the indexes improve query efficiency? Explain your answer. Hint: You should look at both the elapsed time and the cost in the query execution plan.
Note: there’s at least two ways to do Task 2.4. One is initially slow, but better demonstrates the effect of the bitmap indexes.
Task 3 – Execution Plan
1. AB+treeindexPK_TAPIDhasbeengeneratedautomaticallyfortheprimarykey column tap_id of the TAPS table. Write the SQL statements to answer the following questions:
a. WhatistheheightoftheB+treeindex?
b. WhatisthenumberofleafblocksintheB+treeindex?
c. What is the number of block access needed for a full table scan of the tap table?
Hint: You may find the following documents from Oracle helpful for Task 3.1:
•
2. ThefollowingSQLstatementlistsallthetapswithatap_idlargerthan100:
SELECT * FROM TAP WHERE TAP_ID > 100;
Report the rule-based execution plan chosen by the Oracle optimizer for executing this query.
Question: Explain the query processing steps taking place in this plan.
3. Reportthecost-basedexecutionplanchosenbytheOracleoptimizerfor executing the query in Task 3.2.
Question: Explain the query processing steps taking place in this plan. In your opinion, what are the main differences between the plans you obtained in Task 3.2 and Task 3.3, based on the statistics from Task 3.1 and your calculation? Hint: You need to estimate the number of block accesses with and without index. You can assume the tap table is sorted by tap_id.
4. ThefollowingSQLstatementlistsallthetapswithatap_idlargerthan73,900: SELECT * FROM TAP WHERE TAP_ID > 73900;
Report the cost-based execution plan chosen by the Oracle optimizer for executing this query.
Question: Explain the query processing steps taking place in this plan. In your opinion, what are the main differences between the plans you obtained in Task 3.3 and Task 3.4, based on the statistics from Task 3.1 and your calculation?
5. ThefollowingSQLstatementlistsallinformationforthetapwithatap_idof 10,000: SELECT * FROM TAP WHERE TAP_ID = 10000;
Report the cost-based execution plan chosen by the Oracle optimizer for executing this query.
Question: Explain the query processing steps taking place in this plan. In your opinion,
