INFS2200/7903 PROJECT ASSIGNMENT 2
Total Marks:
Due Date:
What to Submit: Where to Submit:
Semester 2 2023
70 marks (15% of the course) 4:00PM Friday 27-October-2023 SQL script file + short report Electronic submission via Blackboard
The goal of the project assignments is to gain practical experience in applying several database management concepts and techniques using Oracle DBMS. In particular, this assignment mainly focuses on improving database efficiency with views, indexing, and query planning.
Your main task is to first populate your database with appropriate data, then design, implement, and test the appropriate queries to perform the tasks explained in the next sections.
You must work on this project individually. Academic integrity policies apply. Please refer to 3.60.04 Student Integrity and Misconduct of the University Policy for more information.
Roadmap: Section A describes the database schema for the assignment and provides instructions on downloading the script file needed to create and populate the database. Section B describes the tasks to be completed for this assignment. Finally, Section C explains the submission guidelines and marking scheme.
Enjoy the project!
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.
Please go to Blackboard and download the supplementary script file for this assignment, TravelDB.sql
Note: this file has been updated since Assignment 1. The first few lines of the updated file will contain a comment confirming it is for Assignment 2.
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.Astation is defined 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.1querytoincludeonlyrevenue from users 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. WriteanSQLstatementtocreateamaterializedview MV_STATION_BOARDINGS that lists the same information as in Task 1.3.
5. ExecutethefollowingtwoSQLstatements.Reportboththeirqueryexecution time and 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-basedindex could be created on the STOPS table. Write an SQL statement to create an index IDX_PINK that best fits this task.
3. ReporttheexecutiontimeandexecutionplanoftheSQLqueryyouwrotein Task 2.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,bitmapindexescouldbe created on 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. ReporttheexecutiontimeandexecutionplanoftheSQLqueryyouwrotein Task 2.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, what are the main differences between the plans you obtained in Task 3.3 and Task 3.5, based on the statistics from Task 3.1 and your calculation?
SECTION C — Deliverables & Marking Scheme
The project is due by 4:00PM, 27 October 2023. Late submissions will be penalized
unless you are approved for an extension (refer to Section 5.3 of the ECP). You are required to turn in two files (use StudentID to name your files):
1. StudentID.pdf:(replacingStudentID)–SubmitonBlackboardviatheTurnitin link “Report Submission”
A report that answers all the questions in Section 2 including all the necessary SQL statements and the screenshots of their outputs.
2. StudentID.sql:(replacingStudentID)–SubmitonBlackboardviatheupload link “SQL Script Submission”
A plain-text script file that includes all your SQL statements.
Your report file should include the following content:
• Answers to all the questions in Section B.
• If you are asked to write SQL statements, you need to include those statements in your report.
• After you execute a SQL statement, if Oracle produces any output (e.g. query result, query execution time, query plan, etc), you should also include a screenshot of the output as well.
Note: Please be sensible when including query output. Any output close to the size of one page can be shown by just including the first 10 lines and the last 10 lines. A report that includes multiple pages of a query output will lose presentation marks. You may find some helpful instructions for formatting query output in Practical 1 or the following Oracle documentation.
Your script file must be in plain text (.sql) format. You must make sure that your script file can be executed on the ITEE lab computers by the “@” command. The same SQL statements in your script file should also be copied and pasted into your report file (as explained above). Even though the script file does not introduce any new information compared to the report, it is intended to help the marking tutors to quickly check the correctness of your SQL statements before checking the details in your report file.
Your Assignment 2 mark will be halved if you omit one of the script file or the report. Enjoy the project! Good luck!
Marking Scheme
Task Marks Criteria
Write only one SQL query and generate the correct result.
Write only one SQL query and generate the correct result.
View is created with the correct name and semantics. The correctness of the view will be tested by
SELECT * FROM V_STATION_BOARDINGS;
Materialized view is created with the correct name and semantics. The correctness of the view will be tested by SELECT * FROM MV_STATION_BOARDINGS;
Query execution times and query plans are reported for both view and materialized view.
Answer the Questions correctly.
Presentation 2
Write only one SQL query and generate the correct result.
Function-based index is created with the correct name and semantics
Write only one SQL statement and generate the correct result.
Bitmap indexes are created with the correct name and semantics.
Rule-based execution plan is generated. Answer the Question correctly.
Cost-based execution plan is generated. Answer the Question correctly.
Cost-based execution plan is generated. Answer the Question correctly.
Cost-based execution plan is generated. Answer the Question correctly.
No query result exceeds one page in the screenshot. No tuple is broken into multiple lines in the screenshot.
END OF ASSIGNMENT 7
Query execution time and query plans are reported for both before and after index.
Answer the Question correctly.
Query execution time and query plans are reported for both before and after index.
Answer the Question correctly.
Write the correct SQL statements to generate index statistics and table statistics respectively.
Answer the Questions correctly.
