Entity-Relation diagram

There are two schema additions from Assignment 1: author and genre fields have been added to the Works table.

Further, the trigger BI_LOANS and associated user-defined function udf_bi_loans have been added to the Events table. (No triggers implemented in Assignment 1 are present.)

Setup

Before starting work on this assignment, please create a new database in psql, connect to it with the  \c command,  and run the setup file  a2-2024-librarydb.sql with the  \i command (it may take a minute). This ensures that your work on this assignment is separate from your other work.

The setup file is available with mget as follows:

mget -O /infs2200/stor/data/assignments/2/a2-2024-librarydb.sql

Note: as of this version of the assignment, the setup script version is 2.0

Section 2 - Assignment Tasks

Q1 Views (5 marks total)

Q1.1 View A (1 mark)

The librarians would like to confirm the most popular genres.

Create a view V_POPULAR_GENRES to determine the five most popular genres, as determined by number of times an item has been borrowed or held.

Query the view (SELECT * FROM V_POPULAR_GENRES;) and report the results.

Report and explain the query plan. You should refer to the reported times, costs, the number of rows fetched, your knowledge of query planning (and anything else you feel is relevant).

Q1.2 View B (2 marks)

When a child loses an item, the cost is charged to their account but their guardian is responsible for paying it.

Create a view V_COSTS_INCURRED to identify the five patrons who are responsible for paying the most charges incurred from January through June 2024.

Hint: consider usingCommon Table Expressionsto build up your query in a semantically meaningful way.

Q1.3 Materialised view (2 marks)

1.  Create a materialised view using the same query as V_COSTS_INCURRED, called MV_COSTS_INCURRED. (0 marks)

2.  Report, compare and explain the two following queries, which select the contents of the view and materialised view, respectively. (2 marks)

Query A:

SELECT * FROM V_COSTS_INCURRED; Query B:

SELECT * FROM MV_COSTS_INCURRED;

You should refer to the reported times, costs, the number of rows fetched, your knowledge of query planning (and anything else you feel is relevant).

Q2 Indexes and performance (6 marks total)

Q2.1 Basic index (2 marks)

(1) Create an index (IDX_EVENT_ITEM) on the event_type and item_id fields of the Events table. (0.5 marks)

(2) Re-run your query from Q1.1 again (SELECT * FROM V_POPULAR_GENRES;). Report the query plan. Compare this query plan to those from Q1.1 and explain the differences, if any. (1.5 marks)

Q2.2 Function-based index (4 marks)

Due to aprevious decisionin the database design, author’s names are stored as a single field. However, for library purposes the surname of the author is relevant for deriving the

call number. For this purpose, the surname is defined as the last word (space-delimited) in the author field.

(1) Create an expression to identify each author’s surname and present the results. You may usestring processingfunctions, including theregexfunctions. (1 mark)

(2) Report and explain the query plan. You should refer to the reported times, costs, the number of rows fetched, your knowledge of query planning (and anything else you feel is relevant). (1 mark)

(3) Create a function-based index to potentially speed up queries on this expression. (0.5 marks)

(4) Report the query plan with the function-based index in place. Explain the differences to the previous plan, if any. You should refer to the reported times, costs, the number of rows fetched, your knowledge of query planning (and anything else you feel is relevant). (1.5 marks)

Bonus content: in real libraries, books are assigned a call number derived from the subject matter and authors’ surname. Books are stored on the shelves in call number order. This makes call numbers an example of a physical indexing scheme!

Q3 Indexes and query planning (4 marks)

PostgreSQL allows you to influence the query planner with runtime  configuration parameters in psql.

https://www.postgresql.org/docs/16/runtime-config-query.html

In this section we will suppress the use of several different scan types to compare queries. Report and explain the execution plan for the following two queries (A & B):

(1) with both index and sequential scans enabled;

(2) with index scans enabled and sequential scans suppressed;

(3) with index scans suppressed and sequential scans enabled.

Explain why the query planner might choose one method over another.

You should refer to the reported times and costs from the execution plan, the number of rows fetched, your knowledge of query planning (and anything else you feel is relevant).

Note: For this question, bitmap and index-only scans count as index scans too.

Query A:

SELECT * FROM Events WHERE event_id < 100; Query B:

SELECT * FROM Events WHERE event_id >= 100;

(2 marks for explaining up to 6 possible query plans; 2 marks for explaining the query planner’s choices.)

Be sure to reset both index and sequential scans back to on before proceeding to another question.

Q4 Transactions (4 marks)

In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands. For example, a banking transaction would look like:

https://www.postgresql.org/docs/current/tutorial-transactions.html

In this question we will examine what happens when transactions occur concurrently.

(1) In a psql connection, begin a transaction and then perform a query to identify an item which is currently returned. (1.5 marks)

(2) In a second psql connection, begin another transaction and attempt to record a hold on that item for some patron 14 days in the future, then commit. (0.5 marks)

(3) In your first connection, attempt to record a loan on that item for a different patron (at the present time), then commit. (0.5 marks)

Explain what happened both in database terms and in Library terms. You may include a schedule or precedence graph of the transaction events if it aids your explanation. (1.5 marks)

Hint: which event(s) were recorded for the item?

Include screenshots of both transactions.

You can use the now() function to provide a time stamp in steps 2 and 3.

Section 3 - Deliverables and Marking

There are 19 marks for functionality, understanding and explanations, and 1 mark for presentation.

Submission

Submit two documents to Blackboard by the due date (4pm, 25th October 2024).

Late submissions will be penalised unless you are approved for an extension (refer to Section 5.3 of the ECP).

(1) A  report  file  titled  4xxxxxxxx.pdf in  PDF  format  containing  relevant  code, screenshots and answers to any questions.

(2) A script file titled 4xxxxxxxx.sql (plain text with .sql extension) containing all SQL code (and any psql commands) you wrote for this assignment.

Replace 4xxxxxxxx with your student number.

You will be marked primarily on the contents of your report file. However, the teaching team must be able to reproduce the screenshots in your report file by first running a2-2024- librarydb.sql then the relevant parts of your script file.

If  code  is  not  provided  for any question such  that  your screenshots cannot be reproduced, your marks will be halved for that question.

Report File & Presentation (1 mark)

Your report file should include the following content:

•   Answers to all the questions in the Assignment Tasks section.

•    If you are asked to write SQL statements, you need to include those statements in your report.

•   After you execute a SQL statement, if PostgreSQL produces any output (e.g. query result, query execution time, query plan, etc), you should also include a screenshot of the output as well.